MySQL update操作使用到了索引但还会锁全表
参考文章：
看了叶师傅的一片文章，在执行 UPDATE时，WHERE条件列有索引，但是还是会表所，根据文章做一下测试，记录如下
MySQL版本
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.4-m14 | +-----------+ 1 row in set (0.00 sec)
测试表 t1
mysql> 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;
Query OK, 0 rows affected (0.08 sec)
添加数据
mysql> insert t1 value(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8),(9,9,9,9);
表数据
mysql> select * from t1; +----+----+----+----+ | c1 | c2 | c3 | c4 | +----+----+----+----+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | +----+----+----+----+ 9 rows in set (0.00 sec)
实例一：
执行计划
mysql> desc update t1 set c4=321 where c2 >=7 \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: 3
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
|
session1
|
session2
|
|
mysql> begin;
mysql> update t1 set c4=321 where c2 >= 7;
|
|
|
mysql> begin;
mysql> select * from t1 where c2=6 for update;
+—-+—-+—-+—-+
| c1 | c2 | c3 | c4 |
+—-+—-+—-+—-+
| 6 | 6 | 6 | 6 |
+—-+—-+—-+—-+
1 row in set (0.00 sec)
# 可以直接查询到结果，不会被阻塞
|
实例二：
mysql> desc update t1 set c4=321 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: 9
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
本次的执行计划是跟进主键索引进行更新，且会锁全表。
|
session1
|
session2
|
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set c4=321 where c2 >=6;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
|
|
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where c2=5 for update;
# 无法查询到结果，被阻塞
|
|
|
mysql> rollback;
|
|
|
+—-+—-+—-+—-+
| c1 | c2 | c3 | c4 |
+—-+—-+—-+—-+
| 5 | 5 | 5 | 5 |
+—-+—-+—-+—-+
1 row in set (6.70 sec)
# 展示出了结果
|
查看锁情况
mysql> select * from information_schema.INNODB_LOCK_WAITS \G;
*************************** 1. row ***************************
requesting_trx_id: 733741
requested_lock_id: 733741:1589:3:6
blocking_trx_id: 733740
blocking_lock_id: 733740:1589:3:6
1 row in set (0.00 sec)
mysql> select * from information_schema.INNODB_LOCKS \G;
*************************** 1. row ***************************
lock_id: 733741:1589:3:6
lock_trx_id: 733741
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 1589
lock_page: 3
lock_rec: 6
lock_data: 5
*************************** 2. row ***************************
lock_id: 733740:1589:3:6
lock_trx_id: 733740
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 1589
lock_page: 3
lock_rec: 6
lock_data: 5
2 rows in set (0.00 sec)
结论：当 MySQL 语句扫描行数超过全表总数 20%~30%时，即便有二级索引，也会直接升级为全表扫描