首先官方文档是个好东西:https://blog.csdn.net/n88Lpo/article/details/78099094
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
带着问题阅读本文吧
题设:
求解:
1、当执行
begin;
update t_t set c = 'c' where a > 3;
后,下面哪个sql会被阻塞:
A select * from t_t where a = 3 for update;
B select * from t_t where a = 4 for update;
C select * from t_t where b = 3 for update;
D select * from t_t where b = 4 for update;
E select * from t_t where c = 3 for update;
F select * from t_t where c = 4 for update;
答案:B D E F
答题解析:
考的知识点:主键索引,二级索引,表锁,行所,desc命令,sys.innodb_lock_waits
本题使用主键索引,锁住了7行,4-10行
A 检查主键索引的时候发现第三行没有锁,所以可以执行不阻塞
B 检查主键索引的时候发现第4行有锁,阻塞
trx_rows_locked:事务锁住的记录数。包含标记为 DELETED,并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified:事务更改的行数。
C select * from t_t where b = 3 for update,通过二级索引发现对应的主键索引未上锁,所以不阻塞
D select * from t_t where b = 4 for update; 通过二级索引发现对应的主键索引上锁,所以阻塞
E select * from t_t where c = 3 for update; C没有索引,全表扫描,阻塞
F select * from t_t where c = 4 for update; 同上面的E
2、当执行
begin;
update t_t set c = 'c' where b > 3;
后,下面哪个sql会被阻塞:
A select * from t_t where a = 3 for update;
B select * from t_t where a = 4 for update;
C select * from t_t where b = 3 for update;
D select * from t_t where b = 4 for update;
E select * from t_t where c = 3 for update;
F select * from t_t where c = 4 for update;
答案:A B C D E F
答题解析:
考的知识点:主键索引,二级索引,表锁,行所,desc命令,sys.innodb_lock_waits
本题使用二级索引,锁住了所有行,1-10行
3、当执行
begin;
update t_t set c = 'c' where b > 7;
后,下面哪个sql会被阻塞:
A select * from t_t where a = 7 for update;
B select * from t_t where a = 8 for update;
C select * from t_t where b = 7 for update;
D select * from t_t where b = 8 for update;
E select * from t_t where c = 7 for update;
F select * from t_t where c = 8 for update;
答案:B D E F
答题解析:
考的知识点:主键索引,二级索引,表锁,行所,desc命令,sys.innodb_lock_waits
本题使用二级索引,和第二题相比只是由原来的b>3改成了b>7
不过此时发现,锁了3行,所以A和C不被阻塞,而不被阻塞的原因就是写在前文的话,innodb根据当前情况做了索引优化了,使用了索引。
总结:
1、在写sql的时候能用索引尽量使用索引,这样锁的行少一些
2、当SACN的数据量超过一定的量后,INNODB可能不再使用索引,所以尽量SACN的范围小一些