UPDATE能走索引还会锁全表吗

xiaoxiao2021-02-28  10

导读

执行UPDATE时,WEHRE条件列虽已有索引,但还会锁全表,肿么回事?

问题描述

叶师傅有次上课过程中执行UPDATE测试案例时,发现虽然WHERE条件列已有索引,有时候能利用二级索引进行更新(且只锁定相应必要的几行记录),但有时候却变成了根据主键进行更新,且会锁全表。我们先来看看下面的例子。

测试表 t1

CREATE TABLE `t1` (  `c1` int(10) unsigned NOT NULL DEFAULT '0',  `c2` int(10) unsigned NOT NULL DEFAULT '0',  `c3` int(10) unsigned NOT NULL DEFAULT '0',  `c4` int(10) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`c1`),  KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据

+----+----+----+----+ | c1 | c2 | c3 | c4 | +----+----+----+----+ |  0 |  0 |  0 |  0 | |  1 |  1 |  1 |  0 | |  3 |  3 |  3 |  0 | |  4 |  2 |  2 |  0 | |  6 |  8 |  5 |  0 | |  7 |  6 |  6 | 10 | | 10 | 10 |  4 |  0 | +----+----+----+----+

case1:根据二级索引UPDATE,不锁全表

先看执行计划

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=8\G *************************** 1. row ***************************           id: 1  select_type: UPDATE        table: t1   partitions: NULL         type: range possible_keys: c2          key: c2      key_len: 4          ref: const         rows: 2     filtered: 100.00        Extra: Using where

启动两个session执行UPDATE测试

session1session 2mysql> begin; mysql> update t1 set c4=123 where c2>=8;Query OK, 2 rows affected (0.00 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> begin;mysql> select * from t1 where c2 = 7 for update;...1 row in set (0.00 sec)#直接可得到结果,不会被阻塞

case2:根据PRIMARY KEY更新,锁全表

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=6\G *************************** 1. row ***************************           id: 1  select_type: UPDATE        table: t1   partitions: NULL         type: index possible_keys: c2          key: PRIMARY      key_len: 4          ref: NULL         rows: 7     filtered: 100.00        Extra: Using where

我们能看到本次执行计划是 根据主键索引进行更新,且会锁全表

同样地,启动两个session执行UPDATE测试

session1session 2mysql> begin; mysql> update t1 set c4=123 where c2>=6;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> begin;mysql> select * from t1 where c2 = 3 for update;#无法得到结果,被阻塞了mysql> rollback; #执行rollback,释放锁... 1 row in set (4.23 sec) #session1释放锁后才能得到结果

查看行锁等待情况

yejr@imysql.com [yejr]>select * from sys.innodb_lock_waits\G *************************** 1. row ***************************                wait_started: 2017-08-15 15:20:20                    wait_age: 00:00:17               wait_age_secs: 17                locked_table: `yejr`.`t1`                locked_index: PRIMARY  <--主键上加锁                 locked_type: RECORD              waiting_trx_id: 268350         waiting_trx_started: 2017-08-15 15:20:20             waiting_trx_age: 00:00:17     waiting_trx_rows_locked: 2   waiting_trx_rows_modified: 0                 waiting_pid: 13               waiting_query: select * from t1 where c2 = 3 for update             waiting_lock_id: 268350:387:3:4           waiting_lock_mode: X             blocking_trx_id: 268349                blocking_pid: 12              blocking_query:  NULL            blocking_lock_id: 268349:387:3:4          blocking_lock_mode: X        blocking_trx_started: 2017-08-15 15:20:18            blocking_trx_age: 00:00:19    blocking_trx_rows_locked: 8  <-- 所有记录都被加锁了  blocking_trx_rows_modified: 3  <---持有锁的事务更新了3行记录     sql_kill_blocking_query: KILL QUERY 12 sql_kill_blocking_connection: KILL 12

问题分析

好了,案例说完了,也该说原因了。

肾好的同学可能记得我说过一个结论:当MySQL预估扫描行数超过全表总数约 20% ~ 30% 时,即便有二级索引,也会直接升级为全表扫描

这个结论的原因并不难理解,二级索引的顺序和主键顺序一般来说是不一样的,根据二级索引的顺序回表读数据时,实际上对于主键则很可能是随机扫描,因此当需要随机扫描的数量超过一定比例时(一般是20% ~ 30%),则优化器会决定直接改成全表扫描

上述说法出处:WHERE Clause Optimization(https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html

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.

不过,上面这个结论是针对读数据的情况,UPDATE/DELETE修改数据时是否也这样呢?

答案是肯定的,要不然上面的测试结果怎么解释......

按照官方开发者的说法,当优化器评估根据二级索引更新行数超过约50%(从上面测试结果来看,其实20% ~ 30%就已经是这样了,不过这个比例并不是固定值,会根据各种代价计算自动调整)就会改成走主键索引,并且锁全表,这么做是既定的策略,原因和上面一样,也是为了提高检索效率。

总结

老调重弹,几点建议:

不管检索还是更新,都尽可能利用索引;

不要一次性检索或更新大批量数据,建议分城多批次;

事务尽快提交,降低行锁持有时间及其影响。

本文提到的问题,我也提了个BUG#87021,有兴趣的可以去看看。

延伸阅读

UTF8字符集的表怎么直接转UTF8MB4?

细说ANALYZE TABLE

优化系列 | DELETE子查询改写优化

[MySQL优化案例]系列 — 分页优化

曝!和叶师傅**的正确姿势

知识无界限,不再加原创

喜欢就转走,铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

转载请注明原文地址: https://www.6miu.com/read-1250207.html

最新回复(0)