MySQL面试题第三弹

沈小洋 2021-04-25

325

第三弹来了兄弟们，这一篇是加锁和索引相关的，本来计划分两篇发，但是发现内容不是特别多，就放在一篇发了哈。

加锁规则这块的内容看起来不多但是需要细品，推荐看下这篇文章：为什么我只改一行的语句，锁这么多？（链接在文章底部）

加锁相关

加锁规则？

加锁的基本单位是Next-Key Lock，前开后闭区间
所有访问到的数据都会加锁
唯一索引等值匹配会退化成行锁
索引上的等值查询，遍历到最后一个不满足条件的值的时候，Next-Key Lock退化为间隙锁
二级索引上的范围查询会访问到第一个不满足条件的值为止

SELECT语句是怎么加锁的？

SELECT语句的加锁情况要根据语句所处的隔离级别来分析，在读未提交级别下，SELECT语句每次都直接读取最新数据，不会进行加锁，但是存在脏读、不可重复读和幻读问题。在读提交级别下，SELECT语句不加锁，每次查询都生成read view来读取数据，存在不可重复读和幻读问题。在可重复读级别下，SELECT语句也不加锁，在第一次SELECT的情况下生成read view来读取数据，存在幻读问题。在可串行化级别下，要分两种情况来说，如果开启自动提交的话，因为每个事务里面只包含一条SQL语句，所以不存在不可重复读和幻读问题，因此不会加锁，而是利用MVCC生成read view来读取数据；如果关闭自动提交的话，就可能会存在不可重复读和幻读问题，所以每次SELECT的时候都会加读锁。

使用MDL锁有哪些注意事项？

MDL全称是metadata lock，表级锁，虽然我们不能直接操作MDL锁，但是每次执行DML操作时都会自动加上MDL读锁，每次执行DDL操作时，都会自动加上MDL写锁。

需要注意的是，如果系统中存在长事务，在变更表结构时，可能导致长时间获取不到MDL写锁，因为长事务会一直占用MDL读锁。但是获取MDL写锁的动作会阻塞后续事务获取MDL读锁，如果客户端存在超时重试机制，就可能导致系统中的连接被大量占用，进而导致服务不可用。所以变更表结构的时候要很小心。

意向锁是什么？

为了避免在加表级S/X锁时去逐个确认是否存在行级S/X锁，InnoDB引入了意向锁的概念。意向锁又分为意向共享锁（IS）和意向独占锁（IX），当准备在记录上加S锁时，需要先加IS锁，当准备在记录上加X锁时，需要先加IX锁。这样设计的话，之后需要加表级锁时只需要判断一下是否存在相应的意向锁即可。

自增锁是什么？

自增锁（AUTO-INC Locks）是表级锁，主要用于分配自增主键id。自增锁底层有两种工作模式，语句模式和轻量级锁模式。轻量级锁会在语句获取到本次插入所需要的自增值后立刻释放锁，而语句锁则会持有锁直到语句执行完毕。

MySQL在5.1.22版本引入了innodb_autoinc_lock_mode参数，用于配置自增锁的工作模式。该参数的可选配置为0、1、2，配置为0时表示使用语句模式，配置为1时表示使用混合模式（默认是这种），配置为2时表示使用轻量级模式。

混合模式下，如果插入语句需要插入的数据条数可以提前获知，就会使用轻量级模式。如果插入语句是形如INSERT...SELECT这样的批量插入语句，由于需要插入的数据条数无法提前获知，就会使用语句模式来执行。

哪些原因可能导致死锁？

不同事务中多条语句对记录的加锁顺序相反可能会导致死锁，加锁的动作是逐步进行的，也需要时间。当不同事务加锁顺序相反的时候，就可能存在互相持有对方所需要的锁的情况，这时候就会发生死锁。

两个事务持有同一个间隙锁然后尝试更新也会导致死锁，因为间隙锁的申请是互不冲突的，但是在间隙里插入数据时，如果有其他事务也持有该间隙锁，插入操作是无法执行的。举个例子，假设表中有三条记录（id分别为0,5,10），当开启一个事务A修改id为5的记录时，会在(0,5]和(5,10)的区间上加锁。此时开启一个事务B，也尝试修改id为5的记录，会先加上(0,5)的间隙锁，然后阻塞在id为5的记录锁上。此时如果事务A再尝试在(0,5)的区间内插入数据时，就会出现死锁。

死锁应对策略？

出现死锁后，MySQL有两种应对策略：超时和死锁检测。超时时间默认为50s，如果设置的过短，就有可能误伤正常的锁等待，可以通过innodb_lock_wait_timeout设置超时时间。死锁检测是指在发现死锁之后，主动检测死锁链条，找到回滚成本最小的事务进行回滚，可以通过innodb_deadlock_detect参数设置是否开启死锁检测，默认为开启状态。

注意，如果同一个热点行上并发线程数量过多，就会导致死锁检测成本变得非常高，每个新加入的线程都要执行一次O(n)级别的计算，来判断是否是由于自己的加入导致了死锁。当并发线程数达到1000个时，运算次数将达到百万级别。对于这种场景，有两种解决方案。一是分散热点行，改为多行。二是控制并发线程数，可以做在业务层面。推荐使用控制并发线程数的方案，比较好理解，也容易实现。

索引相关

使用索引有哪些建议？

利用好最左前缀原则。不论是字符串的模糊匹配还是使用联合索引进行查询，都可以利用最左前缀规则。对于字符串的模糊匹配来说，就是尽量避免全模糊匹配，而是使用半模糊匹配，例如将“%小明%”改为“小明%”。对于联合索引来说，就是尽量让查询条件包含从最左侧开始的连续的索引列。
利用好覆盖索引。如果查询列表中只包含索引列，那InnoDB通过二级索引就可以拿到所有需要的数据，也就不需要回表了，效率会比需要回表查询的语句高很多。
为经常使用范围查询或排序的列建立索引。我们知道索引数据本身就是按照键值顺序有序排列的，如果为这些列建立索引，就能大大提高语句执行的效率。
主键不要太大，绝对不要用长字符串，因为主键也会存储到二级索引中。最好是使用自增值，因为这样可以避免出现频繁的页分裂，插入效率和空间利用率都更高。
使用GROUP BY语句为数据分组时，如果存在与统计顺序一致的联合索引，那么GROUP BY语句也是可以用上索引的。

哪些场景会导致索引失效？

对索引列进行运算和函数调用很容易导致用不上索引，所以要尽量避免，单独使用索引列是最保险的。使用索引进行排序时，ASC和DESC混用、WHERE子句中包含不属于当前排序索引列的字段、在排序列上使用复杂的表达式等情况都会导致排序无法使用到索引，所以要尽量规避这些情况。

联合索引范围查询存在什么问题？

对于联合索引来说，如果在多个索引列上使用范围查询的话，只有最左侧的列可以用上索引。但是如果左侧的列是等值匹配的话，那紧挨着的右侧的列依然可以在范围查询时用上索引。利用好这个特性，可以在使用多个条件联合查询的时候建立更合适的索引。

索引条件下推是什么？

索引条件下推 index condition pushdown（ICP）是MySQL 5.6 引入的一个特性，如果开启ICP，并且WHERE语句中的部分列可以仅使用索引列来计算，MySQL服务器就会把这部分条件下推到存储引擎。ICP可以减少存储引擎访问基表的次数，也可以减少MySQL服务器访问引擎的次数，提高执行效率。

题外话，推荐两个课程，都是我刷了好几遍的。昨晚瞄了一眼，我这个号第一篇文章是去年八月发的，内容也是关于MySQL的，说起来看这块也小一年了，水真的挺深的。关注我的都是认识的人，良心推荐，想继续深入的话可以看课程哦。