文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >MySQL通过非主索引列更新数据需要对无关记录进行主索引锁。

问MySQL通过非主索引列更新数据需要对无关记录进行主索引锁。
EN

Stack Overflow用户
提问于 2020-07-23 15:58:14
回答 1查看 367关注 0票数 0

MySQL通过非主索引列更新数据需要对无关记录进行主索引锁。

表信息.

代码语言：javascript
复制
mysql> SHOW TABLE STATUS WHERE Name = 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 15
 Avg_row_length: 1092
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 6291456
 Auto_increment: 18058
    Create_time: 2020-07-23 13:09:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)

索引信息.

代码语言：javascript
复制
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | index_a  |            1 | a           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | index_b  |            1 | b           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

数据信息.

​

​

为什么update test set a = 400 where b = 4; step 2 需要锁定主1 ?

为什么update test set a = 300 where b = 3; step 1 会锁定主6 ?

这一切为什么会发生?

​

​

代码语言：javascript
复制
mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 21001
Purge done for trx's n:o < 21000 undo n:o < 0 state: running but idle
History list length 82
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7fa0292a6700, query id 65 192.168.79.1 root
---TRANSACTION 0, not started
MySQL thread id 4, OS thread handle 0x7fa029264700, query id 33 192.168.79.1 root
---TRANSACTION 21000, ACTIVE 13749 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7fa0292e8700, query id 67 localhost root updating
update test set a = 400 where b = 4
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 3 n bits 592 index `PRIMARY` of table `test`.`test` trx id 21000 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000005203; asc     R ;;
 2: len 7; hex 03000002910110; asc        ;;
 3: len 4; hex 8000012c; asc    ,;;
 4: len 4; hex 80000003; asc     ;;

------------------
---TRANSACTION 20995, ACTIVE 13764 sec
2 lock struct(s), heap size 360, 16 row lock(s), undo log entries 5
MySQL thread id 1, OS thread handle 0x7fa02932a700, query id 68 localhost root init
show engine innodb status

result.txt

*更新三天后发布*

先生回答了我的问题后，我又做了一次测试。在测试中，我修改了列b中的数据，将原来的两个不同的数字'3‘和'4’改为更明显的数字'3 4 5 6 7‘。当时，锁就像我之前所预期的那样工作，即可以在两个事务中同时执行update test set a = 300 where b = 3 和 update test set a = 400 where b = 4 。我想，当列中的数据量太小时，innodb似乎没有完全构建索引。抱歉，你的时间，也许其他人永远不会有问题的现实生活。对我来说有点奇怪。

​

​

代码语言：javascript
复制
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_a` (`a`) USING BTREE,
  KEY `index_b` (`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

更新7月29日：--我现在还没有理解Next-Key Lock。为了直观地显示锁，我这次使用了mysql 8。因为mysql 8有一个表performance_schema.data_locks，我们可以从这个表中看到所有的锁，而不仅仅是等待的锁。我在mysql 5.6和mysql 8上使用相同的数据进行了另一次测试。更新查询(b = 3)和(b= 4)可以在mysql 8上的不同事务中同时执行，但不能在mysql 5.6上执行。我真的很困惑。

data：test.sql

select * from performance_schema.data_locks locks.csv

代码语言：javascript
复制
null    140235916803776 TABLE   IX  GRANTED null
idx_b   140235916800864 RECORD  X   GRANTED supremum pseudo-record
idx_b   140235916800864 RECORD  X   GRANTED 4, 7
idx_b   140235916800864 RECORD  X   GRANTED 4, 8
idx_b   140235916800864 RECORD  X   GRANTED 4, 9
idx_b   140235916800864 RECORD  X   GRANTED 4, 10
idx_b   140235916800864 RECORD  X   GRANTED 4, 11
idx_b   140235916800864 RECORD  X   GRANTED 4, 12
idx_b   140235916800864 RECORD  X   GRANTED 4, 13
idx_b   140235916800864 RECORD  X   GRANTED 4, 14
idx_b   140235916800864 RECORD  X   GRANTED 4, 15
idx_b   140235916800864 RECORD  X   GRANTED 4, 16
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 7
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 8
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 9
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 10
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 11
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 12
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 13
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 14
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 15
PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 16
    140235916797744 TABLE   IX  GRANTED 
idx_b   140235916794832 RECORD  X   GRANTED 3, 1
idx_b   140235916794832 RECORD  X   GRANTED 3, 2
idx_b   140235916794832 RECORD  X   GRANTED 3, 3
idx_b   140235916794832 RECORD  X   GRANTED 3, 4
idx_b   140235916794832 RECORD  X   GRANTED 3, 5
idx_b   140235916794832 RECORD  X   GRANTED 3, 6
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 1
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 2
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 3
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 4
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 5
PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 6
idx_b   140235916795520 RECORD  X,GAP   GRANTED 4, 7
sql-update
mysql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-07-27 02:19:59

从下面的解释结果中我们可以看到，此更新查询将使用主索引，因为使用idx_b将返回表并再次访问主索引。因此，mysql选择使用主索引而不是idx_b。

代码语言：javascript
复制
mysql> explain update test set a = 300 where b = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | index | idx_b         | PRIMARY | 4       | NULL |   15 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

根据您的show engine innodb status结果可以证明这一点，该结果显示了16行锁。这么说，所有的记录都被锁上了。

代码语言：javascript
复制
---TRANSACTION 20995, ACTIVE 13764 sec
2 lock struct(s), heap size 360, 16 row lock(s), undo log entries 5
MySQL thread id 1, OS thread handle 0x7fa02932a700, query id 68 localhost root init
show engine innodb status

mysql官方文档中的以下内容展示了innodb锁的工作原理：

更新..。其中.在每个记录上设置一个独占的下键锁，搜索遇到。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。

update test set a = 400 where b = 4和explain update test set a = 400 where b = 4 and id > 5的原因是一样的。这两个查询都将使用主索引。

代码语言：javascript
复制
mysql> explain update test set a = 400 where b = 4;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | index | idx_b         | PRIMARY | 4       | NULL |   15 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain update test set a = 400 where b = 4 and id > 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | range | PRIMARY,idx_b | PRIMARY | 4       | const |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

总之，mysql选择了primay索引而不是idx_b，这就是为什么所有这些都会发生的原因。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/63058378

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档