MySQL面试必知：10道高频题通关秘籍
索引相关
聚集索引与非聚集索引的奥秘
在 MySQL 的索引体系中，聚集索引和非聚集索引是非常重要的概念。聚集索引基于主键创建，它不仅仅是一种索引类型，更代表着一种数据的存储方式。在 InnoDB 存储引擎里，一张表的数据对应的物理文件是按照 B + 树来组织的索引结构，而聚集索引就是按照每张表的主键构建的 B + 树，叶子节点存储了表的每一行数据记录 。这就意味着每个表必须有一个主键，如果没有，InnoDB 会默认选择或添加一个隐藏列作为主键索引来存储数据行。通常建议使用自增 id 作为主键，因为自增 id 具有连续性，能使对应的数据按顺序存储在磁盘上，从而提升写入和检索性能。而像 uuid 这种随机 id，在频繁插入数据时，会导致随机磁盘 IO，性能较低。
InnoDB 中只能存在一个聚集索引，这是因为若存在多个聚集索引，就意味着表中的数据存在多个副本，这不仅会浪费磁盘空间，还会增加数据维护的难度。
除了主键索引外的其他索引，被称为非聚集索引，也叫二级索引。在 InnoDB 中，如果通过非聚集索引查询一条完整记录，最终还是需要访问主键索引来检索。例如，有一张用户表，包含用户 id（主键）、用户名、年龄等字段。用户 id 上的索引是聚集索引，若在用户名上创建索引，这就是非聚集索引。当我们通过用户名查询用户信息时，先通过用户名索引（非聚集索引）找到对应的主键值，再通过主键值在聚集索引中找到完整的用户记录。
B+Tree 索引为何称霸 MySQL
B 树是一种多路平衡搜索树，它的出现是为了减少磁盘 IO 次数，提升查询性能。在数据库中，索引存储在磁盘上，当数据量较大时，无法将整个索引加载到内存，只能逐一加载磁盘页，而磁盘页对应索引树的节点。二叉搜索树的查询效率虽然是 O（log2^N），但树深度过大时，磁盘 IO 读写过于频繁，效率低下。B 树通过让树变得 “矮胖”，降低树的深度，从而减少磁盘 IO 次数。例如，一棵 M 阶的 B 树，根节点至少有两个孩子，每个非根节点有【M/2,M】个孩子，每个非根节点有【M/2-1,M-1】个关键字且升序排列 ，所有叶子节点都在同一层等特性，使得它在查询时能更高效地定位数据。
而 InnoDB 存储引擎使用 B + 树作为索引和数据存储结构，是因为 B + 树具有诸多优势。B + 树的中间节点不存储数据，只用来索引，所有数据都保存在叶子节点，这使得同样大小的磁盘可以容纳更多的节点元素，树的结构更加 “矮胖”，查询时 IO 次数更少。B + 树的所有叶子结点包含了全部元素的信息及指向含这些元素记录的指针，且叶子结点本身依关键字大小自小而大顺序链接，这使得范围查询和排序操作更加高效。B + 树的查询必须查找到叶子节点，查询性能稳定，不像 B 树的查找性能不稳定（最好情况查找到根节点，最坏情况查找到叶子节点）。
最左前缀原则的深度剖析
最左前缀原则在联合索引的使用中至关重要。假设有一张学生表，创建了一个联合索引 idx_name_age_school (name, age, school)。这个联合索引的排序规则是：首先按照 name 字段从小到大排序；当 name 字段值相同时，按照 age 字段从小到大排序；当 age 字段值也相同时，按照 school 字段从小到大排序。
当我们使用 where 条件查询时，如果查询语句是 SELECT * FROM students WHERE name > 'n_18';，由于 name 字段是联合索引最左边的字段，所以会命中索引。但如果查询语句是 SELECT * FROM students WHERE age = 18;，age 字段不是联合索引的最左边字段，且在索引中是乱序的，所以不使用索引，需要全表扫描。
再比如 SELECT * FROM students WHERE name = 'n_18' AND age = 20;，name 字段和 age 字段都会命中索引，因为当 name 字段相同时，age 字段是有序的，所以 age 此时也能命中索引。即使查询语句写成 SELECT * FROM students WHERE age =20 AND NAME = 'n_18';，name 和 age 也都会用到索引，因为 MySQL 的查询优化器会自动调整位置，将语句改为 name = "n_18" and age =20 。
但对于 SELECT * FROM students WHERE name > 'n_18' and age = 20;，只有 name 字段用到索引，age 不会用到索引。因为此时 mysql 的查询逻辑是定位到 name=n_18 最右边的一条数据，然后通过叶子节点的指针向右扫描遍历，所以索引对 age 字段没有影响。不过如果查询语句是 SELECT * FROM students WHERE name >= 'n_18' and age = 20;，name 和 age 都会被索引。我们可以把这个 SQL 改成 (name = 'n_18' and age =20) or (name > 'n_18' and age = 20); 这样的写法来理解，对于查询条件 name = ‘n_18’和 age =20，name 和 age 都能用到索引；对于查询条件 name > ‘n_18’和 age =20，只有 name 用到索引，当两个查询条件组合时，两个字段都会被索引。
事务与并发
事务隔离级别大揭秘
事务隔离级别是为了解决多个并行事务竞争导致的数据安全问题而设立的一种规范。在并发环境下，多个事务同时执行时，可能会产生脏读、不可重复读和幻读等现象。
脏读是指一个事务读取了另一个未提交事务的数据 。例如，事务 T1 修改了某条数据但未提交，此时事务 T2 读取到了这条被修改但未提交的数据，如果事务 T1 随后回滚，那么事务 T2 读取到的数据就是无效的。
不可重复读是指事务 T1 在不同时刻读取同一行数据时，由于其他事务（如事务 T2）对该行数据进行了修改并提交，导致事务 T1 两次读取的结果不一致。
幻读则是指事务 T1 执行范围查询或范围修改时，事务 T2 在该范围内插入了新的数据并提交，当事务 T1 再次查询时，会发现多了一些之前不存在的数据，就好像产生了幻觉一样。
为了解决这些问题，SQL 标准中定义了四种隔离级别：
- 读未提交（Read Uncommitted）：在这种隔离级别下，一个事务可以读取到另一个未提交事务的数据，所以可能会产生脏读、不可重复读和幻读。例如，事务 A 未提交对数据的修改，事务 B 就可以读取到这些未提交的修改。这种隔离级别很少用于实际应用，因为它的安全性较低，但它的优点是并发性能较高，系统开销小。
- 读提交（Read Committed）：一个事务只能读取到其他已提交事务的数据，避免了脏读。但在事务执行过程中，由于其他事务的提交，同一事务的多次查询可能会得到不同结果，所以会出现不可重复读和幻读问题。许多数据库系统的默认隔离级别是读提交，它在一定程度上保证了数据的一致性，同时也有较好的并发性能。
- 可重复读（Repeatable Read）：这是 MySQL 的默认事务隔离级别。它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，避免了脏读和不可重复读。但在理论上，可能会出现幻读问题，不过 InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC）机制解决了该问题。在可重复读隔离级别下，事务在开始时读取的数据，在整个事务过程中保持不变，即使其他事务对这些数据进行了修改并提交。
- 串行化（Serializable）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读问题。在这个级别下，事务是串行执行的，一个事务执行完后另一个事务才能执行，就像在单线程环境中一样。但这也导致了大量的超时现象和锁竞争，因为在一个事务进行读操作时，会对读取的数据行加上共享锁，其他事务不能对这些数据进行修改，直到该事务提交；而在进行写操作时，会对数据行加上排他锁，其他事务不能对这些数据进行读或写操作。这种隔离级别虽然保证了数据的高度一致性，但性能较低，一般用于对数据一致性要求极高的场景。
MVCC：无锁并发控制的传奇
在数据库操作中，存在多种并发场景，包括读和读并发、读写并发以及写和写并发。读和读并发不会产生并发问题，就像多个用户同时查询商品信息，他们的查询操作不会相互干扰 。而读写并发和写和写并发则可能引发一系列问题。例如，读写并发时，可能会破坏数据库的事务隔离性，出现脏读、幻读和不可重复读等问题；写和写并发时，可能会存在数据更新丢失的问题，比如两个用户同时修改同一条商品库存信息，可能导致其中一个修改丢失。
为了解决事务操作中并发安全问题，多版本并发控制（MVCC，Multiversion Concurrency Control）技术应运而生。MVCC 是一种无锁并发控制技术，它通过数据库记录中的隐式字段、undo 日志和 Read View 来实现。在 MySQL 的 InnoDB 存储引擎中，每条记录都包含一些隐式字段，如创建版本号（DB_TRX_ID）和删除版本号（DB_ROLL_PTR）。创建版本号用来标识最近一次对本行记录做修改（insert、update）的事务的标识符，删除版本号则是回滚指针，指向该行的 undo log 。undo 日志用于记录数据的旧版本，以便在需要时进行回滚或构建 Read View。Read View 用于在读取数据时判断哪些版本的数据是可见的。
MVCC 主要解决了三个问题：一是解决了读写并发阻塞问题，使得读操作和写操作可以并发执行，无需互相等待，大大提高了数据的并发处理能力，比如在一个高并发的电商系统中，大量的查询和少量的更新操作可以同时进行，不会因为锁的存在而互相阻塞；二是降低了死锁概率，因为 MVCC 采用的是类似乐观锁的方式，不像传统的锁机制那样容易造成死锁，在复杂的数据库事务环境中，提高了系统的稳定性；三是解决了一致性读问题，事务启动时根据某个条件读取到的数据，在事务结束前再次根据相同条件读取，能保证读到的是同一份数据，不会发生变化，这对于需要保证数据一致性的业务场景非常重要，比如金融交易系统中的数据读取。
在实际使用中，MVCC 通常会根据业务场景来选择组合搭配乐观锁或者悲观锁。MVCC 主要用于解决读写冲突，而乐观锁或者悲观锁则用于解决写和写的冲突，这样可以最大程度地提高数据库的并发性能。
锁机制
行锁、GAP 锁、临键锁的江湖恩怨
在 MySQL 的 InnoDB 存储引擎中，行锁、GAP 锁（间隙锁）和临键锁是行级锁的重要组成部分，它们在并发控制中发挥着关键作用。
行锁是对某一行数据进行锁定，以防止其他事务同时修改这一行数据。例如，假设有一张订单表 orders，包含订单 id（主键）、客户 id、订单金额等字段。当执行以下 SQL 语句：
START TRANSACTION;
UPDATE orders SET order_amount = order_amount + 100 WHERE order_id = 1;
COMMIT;在这个事务中，会对 order_id 为 1 的这一行数据加上行锁，确保在事务执行期间，其他事务不能对这一行数据进行修改，避免了数据的不一致性。行锁的加锁单位是行，它能精确地控制对某一行数据的并发访问，大大提高了并发性能。
GAP 锁则是锁定一个范围，但不包含记录本身，它主要用于防止幻读。例如，还是订单表 orders，假设表中已经存在订单 id 为 1、3、5 的记录 。当执行以下查询：
SELECT * FROM orders WHERE order_id BETWEEN 2 AND 4 FOR UPDATE;由于 order_id 为 2 和 4 的记录不存在，InnoDB 会在 order_id 索引上的 (1, 3) 间隙上放置一个 GAP 锁。这就防止了其他事务在 (1, 3) 之间插入新的记录，避免了在当前事务中再次查询时出现新的订单记录（幻读）。GAP 锁是共享的，多个事务可以在同一间隙上持有 GAP 锁，但如果有事务在某个间隙上持有 GAP 锁，其他事务就不能在这个间隙中插入新的记录。
临键锁是 GAP 锁和记录锁的组合，它锁定一个范围，并且锁定记录本身。在可重复读隔离级别下，InnoDB 默认使用临键锁来防止幻读。例如，对于订单表 orders，当执行以下查询：
SELECT * FROM orders WHERE order_id >= 3 AND order_id <= 5 FOR UPDATE;会对 order_id 为 3、5 的记录加上记录锁，同时对 (1, 3) 和 (3, 5) 以及 (5, 无穷大) 这些间隙加上 GAP 锁 。这样一来，就完全锁定了查询范围内的记录和间隙，防止其他事务在这个范围内插入、更新或删除数据，确保了事务的一致性和隔离性。如果此时有另一个事务尝试插入 order_id 为 4 的记录，就会被阻塞，直到当前事务提交或回滚。
乐观锁与悲观锁的过招
在 MySQL 的并发控制中，乐观锁和悲观锁是两种不同的锁策略，它们有着各自的特点和适用场景。
悲观锁认为在数据处理过程中，冲突是大概率事件，所以在每次获取数据时，都会先获取锁，以防止其他事务对数据进行修改。在 MySQL 中，常用 select...for update 语句来实现悲观锁。例如，有一个账户表 accounts，包含账户 id、余额等字段。当要对某个账户的余额进行修改时，可以使用以下 SQL 语句：
START TRANSACTION;SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 进行一些业务逻辑处理，比如扣除一定金额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;在这个事务中，select...for update 语句会对 account_id 为 1 的记录加上排他锁，其他事务如果要对这条记录进行读取或修改操作，都需要等待当前事务提交或回滚。悲观锁的优点是能够确保数据的一致性和完整性，适用于写操作频繁、数据竞争激烈的场景 。但它的缺点也很明显，由于每次操作都需要获取锁，会增加系统的开销，降低并发性能，并且容易产生死锁。
乐观锁则认为在数据处理过程中，冲突是小概率事件，所以在获取数据时不会加锁，只有在更新数据时，才会去判断数据是否被其他事务修改过。在 MySQL 中，乐观锁通常通过版本号机制或时间戳机制来实现。以版本号机制为例，在账户表 accounts 中增加一个 version 字段，每次更新数据时，version 字段的值会加 1。当执行更新操作时，会先读取数据的版本号，然后在更新语句中带上版本号进行条件判断。例如：
-- 读取账户信息及版本号
SELECT balance, version FROM accounts WHERE account_id = 1;
-- 假设读取到的balance为1000，version为1
-- 进行一些业务逻辑处理，比如扣除一定金额
UPDATE accounts SET balance = balance - 100, version = version + 1 WHERE account_id = 1 AND version = 1;如果在执行更新操作时，其他事务已经修改了这条记录，version 字段的值就会发生变化，那么当前的更新操作就不会成功，因为条件 version = 1 不满足。乐观锁的优点是并发性能高，因为它在读取数据时不加锁，允许多个事务同时读取数据 。但它的缺点是如果冲突频繁发生，会导致大量的更新操作失败，需要进行重试，从而增加系统的开销，适用于读操作频繁、写操作较少、数据冲突概率较低的场景。
其他高频考点
SQL 优化的实用兵法
在实际的数据库应用中，SQL 优化是提升系统性能的关键一环。当面对复杂的业务场景和大量的数据时，优化 SQL 语句能够显著提高查询效率，减少系统响应时间。
加索引是一种简单高效的优化手段，但需要选择合适的列，同时避免导致索引失效的操作，比如 like、函数等等。在一张用户表中，如果经常根据用户名查询用户信息，那么在用户名字段上添加索引，能够大大加快查询速度。但如果使用 like '% xxx' 这种方式进行模糊查询，由于无法利用索引的有序性，会导致索引失效，从而进行全表扫描。
本文系作者在时代Java发表，未经许可，不得转载。
如有侵权，请联系nowjava@qq.com删除。
编辑于
关注时代Java