MySQL update操作使用到了索引但还会锁全表

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%时,即便有二级索引,也会直接升级为全表扫描

发表评论

电子邮件地址不会被公开。 必填项已用*标注