索引失效行锁变成表锁

以你之姓@ 2022-09-12 12:44 130阅读 0赞

# 一 实战 #

# 第一个客户端，对 a = 4 执行更新操作，操作成功
    mysql> update test_innodb_lock set b='4000' where a = 4;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 第一个客户端，对 a = 9 执行更新操作，操作成功
    mysql> update test_innodb_lock set b='9000' where a = 9;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    # 第一个客户端：执行 commit
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 第二个客户端：执行 commi
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 第一个客户端：查询 a = 4 和 a = 9
    mysql> select * from test_innodb_lock where a in (4,9);
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4000 |
    |    9 | 9000 |
    +------+------+
    2 rows in set (0.00 sec)
    
    # 第二个客户端：查询 a = 4 和 a = 9
    mysql> select * from test_innodb_lock where a in (4,9);
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4000 |
    |    9 | 9000 |
    +------+------+
    2 rows in set (0.00 sec)
    
    # 查询 from test_innodb_lock 的索引，这里有两个索引： test_innodb_a_ind 和 test_innodb_lock_b_ind
    mysql> show index from test_innodb_lock;
    +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | test_innodb_lock |          1 | test_innodb_a_ind      |            1 | a           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
    | test_innodb_lock |          1 | test_innodb_lock_b_ind |            1 | b           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
    +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)
    
    # 第一个客户端：这里 b 是 varchar 类型，写成 b = 4000，会使得索引 b 失效，使得锁行变成了锁表
    mysql> update test_innodb_lock set a=41 where b = 4000;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 第二个客户端：发生阻塞了，这就是上一句惹的祸，以为此时 test_innodb_lock 表已被锁上 
    mysql> update test_innodb_lock set b='9001' where a = 9;
    
    # 第一个客户端：执行commit
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 第二个客户端：阻塞解除
    mysql> update test_innodb_lock set b='9001' where a = 9;
    Query OK, 1 row affected (4.22 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 第二个客户端：执行commit
    
    # 分别在两个客户端查表，结果正常了
    mysql> select * from test_innodb_lock;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | b1   |
    |    3 | 3    |
    |   41 | 4000 |
    |    5 | 5000 |
    |    6 | 6000 |
    |    7 | 7000 |
    |    8 | 8000 |
    |    9 | 9001 |
    |    1 | b1   |
    +------+------+
    9 rows in set (0.00 sec)

# 二 结论 #

索引失效行锁变成表锁。