《丁奇-MySQL45讲-18/19》之归纳总结

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

• 对索引字段做函数操作，可能会破坏索引的有序性，导致优化器不会走树搜索，最终会走全索引扫描。

• 对索引字段做值操作，比如 select * from t where id + 1 = 10000，MySQL仍然不会走树搜索。

• 字符串和数字做比较的时候，会将字符串转换成数字。有一个字符串类型的字段是tradeid，比如select * from tradelog where tradeid=100，由于tradeid是字符串类型，所以这条语句就相当于是select * from tradelog where CAST(tradeid AS signed int)=100；看另外一条语句select * from tradelog where id="110"，由于110是字符串，所以这条语句就相当于是select * from tradelog where id=110，始终都没有对id索引做函数操作或值操作，所以在执行过程中自然是会走索引搜索。

19 | 为什么我只差一行的语句，也执行这么慢？

• show processlist语句是显示正在运行的线程，root用户能看到所有正在运行的线程，其他用户都只能看到自己正在运行的线程。

• 查看是哪个线程造成了阻塞相关命令：

/*查看是哪个线程造成了阻塞，结果对应到show processlist上id字段*/
select blocking_pid from sys.schema_table_lock_waits;

show processlist;

/*查看相关配置，只有Enabled='YES', TIMED='YES'的情况下才能看到查询sys.schema_table_lock_waits的结果*/
select * from `performance_schema`.setup_instruments where name='wait/lock/metadata/sql/mdl'

/*更新配置*/
update `performance_schema`.setup_instruments set Enabled='YES', TIMED='YES' where name='wait/lock/metadata/sql/mdl'

• 先执行flush tables with read lock，然后在其他线程在执行增删改时会阻塞，查询不会发生阻塞；先执行增删改，然后在其他线程执行flush tables with read lock，两边都是可以正常执行的，但是在后续的增删改操作将会被阻塞，而查询却不会。注意，文章中的flush tables t是不会加锁的，通常情况下执行的很快，所以如果被阻塞的话应该是其他线程被阻塞了，导致该语句也会被阻塞，继而影响后面的语句都会发生阻塞现象。

• MySQL针对不用的隔离级别使用不同的锁策略：

1. RR级别下：

   使用唯一索引进行搜索，RR级别下只会使用记录锁（Record Lock），没有间隙锁（Gap Lock）。

   使用二级索引、非索引等搜索，RR级别下会使用间隙锁（Gap Lock）或next-key lock来阻塞其他线程的插入，非索引情况下会锁住所有行。

2. RC级别下：

   非索引下搜索，会先锁住所有行，然后对每一行进行条件判断，不符合要求的将会直接释放锁，而那些锁住的行等到事务结束后才会释放。如果某一行已经锁定了，其他线程在执行update语句的时候会执行当前读，也就是锁住的线程还没有提交的情况也是可以看到，然后再次判断是否满足条件，如果满足则阻塞，如果不满足则不会阻塞（直接跳过），这叫做"semi-consistent"，注意，只有update语句才可以，delete没有这种情况，也就是说delete语句下执行将会直接阻塞，注意一下，有一种场景下，比如说(id=1, k = 1)、(id=2, k=2)，假设事务A更新k=2这一条记录，而事务B删除k=1这一条记录，在没有索引的情况下，事务B将会直接阻塞，其实如果一开始没有事务B的话，按道理来说事务A只会阻塞k=2这一条记录，但因为加入了事务B，且还是个delete语句，导致了事务A将这条记录也阻塞了，你可以在加一个事务C，执行update ... where k=1语句就可以知道，模拟加事务B和不加事务B的情况下场景如何，这个结论我也很疑惑，完全是个人实验的结果...

   有索引下搜索，只会考虑对应索引的记录锁（Record Lock），不存在"semi-consistent"。

3. RU级别是没有锁策略的。

4. Serializable级别的锁策略类似RR级别。

• 通过show engine innodb status命令查看锁类型，其中rec表示record：

1. lock_mode X locks rec but not gap：表示记录锁。

2. lock_mode X locks gap before rec：表示间隙锁。

3. lock_mode X：表示next-key lock，极端情况下也有可能是间隙锁，假如说某一行记录已经是最小边界或者是最大边界，那是不是就没有间隙了，在加上next-key lock是由间隙锁 + 记录锁组合而成，所以最终只会显示记录锁层面。

4. lock_mode X locks gap before rec insert intention：表示插入意向锁。

• 在不同的锁场景下分别执行select * from sys.schema_table_lock_waits 和 sys.innodb_lock_waits 有时候前者会显示后者不显示，有时候前者不显示后者可以显示，所以去官网大概看了下，大致意思就是sys.schema_table_lock_waits主要用来显示有关MDL锁，属于会话层面；而sys.innodb_lock_waits主要用来显示innodb锁，比如X锁、S锁之类的，属于事务级别的。

• 有趣的题目：select * from table_a where b='1234567890abcd'，其中b的数据类型是varchar(10)，MySQL执行流程如下：

1. 在传给引擎执行的时候，做了字符截断，因为引擎里面这个行只定义了长度是10，所以只截了前10 个字节，就是'1234567890'进去做匹配。

2. 这样满足条件的数据有 10 万行。

3. 因为是select *，所以要做 10 万次回表。

4. 但是每次回表以后查出整行，到server层一判断，b的值都不是'1234567890abcd'。

5. 返回结果是空。