问 MySQL 非唯一索引等值查询加什么锁？-低调大师

问 MySQL 非唯一索引等值查询加什么锁？

2024-10-25 196

可重复读、读已提交两种隔离级别下，非唯一索引的等值查询会加什么锁？为什么这么加锁？

> 作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 MySQL 和 OceanBase 源码。 > 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 > 本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。

1. 准备工作

创建测试表：

CREATE TABLE `t2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `i2` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入测试数据：

INSERT INTO `t2` (`id`, `i1`, `i2`) VALUES
(1, 11, 21), (2, 12, 22),(3, 13, 23),
(4, 14, 24),(5, 15, 25),(6, 16, 26);

2. 可重复读

把事务隔离级别设置为 REPEATABLE-READ（如已设置，忽略此步骤）：

SET transaction_isolation = 'REPEATABLE-READ';

-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

执行以下 select 语句：

begin;
select * from t2 where i1 = 13 for share;

查看加锁情况：

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't2'
 and lock_type = 'RECORD'\G

***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 13, 3
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 3
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S,GAP
lock_status           | GRANTED
lock_data             | 14, 4

lock_data = 13,3、lock_mode = S 表示对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享 Next-Key 锁。

lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 <id = 3> 的记录加了共享普通记录锁。

lock_data = 14,4、lock_mode = S,GAP 表示对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录加了共享间隙锁。

大家对这样的加锁情况是否有疑问呢？

如果你也有疑问，就让我们一起来看看 InnoDB 为什么会这样加锁吧。

可重复读隔离级别下：

对于 select 语句中 where 条件覆盖范围内的记录，默认加共享 Next-Key 锁。
对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他 Next-Key 锁。

示例 SQL 对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享 Next-Key 锁，这属于默认行为，不多解释。

示例 SQL 执行过程中，从二级索引 idx_i1 中读取 <id = 13, id="3"> 的记录之后，需要根据其中的主键字段 <id = 13> 回表查询主键记录。

主键索引字段等值查询，读取记录之后，只需要对这条记录加普通记录锁，防止其它事务修改或者删除这条记录，就能保证可重复读。

这就是示例 SQL 对主键索引中 <id = 3> 的记录加共享普通记录锁的原因。

InnoDB 从二级索引 idx_i1 中读取 <i1 = 13, id="3"> 的记录之后，再回表找到主键索引中 <id = 3> 的记录，返回给 server 层。

where 条件命中的二级索引 idx_i1 是非唯一索引，server 层不能确定刚刚读取到的就是满足 where 条件的最后一条记录，所以会要求 InnoDB 继续读取下一条记录。

InnoDB 从二级索引 idx_i1 中读取下一条记录，得到 <i1 = 14, id="4"> 的记录，发现这条记录不匹配 server 层下推到 InnoDB 的 where 条件（i1 = 13），不需要锁定这条记录。

为了保证可重复读，要防止其它事务往 <i1 = 14, id="4"> 这条记录前面的间隙插入 <i1 = 13> 的记录，InnoDB 需要锁定这条记录前面的间隙，所以，对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录加共享间隙锁。

InnoDB 已经根据下推条件判断出 <i1 = 14, id="4"> 的记录不匹配 where 条件，不需要回表读取主键索引记录，也就不会对主键索引中 <id = 4> 的记录加锁了。

3. 读已提交

把事务隔离级别设置为 READ-COMMITTED（如已设置，忽略此步骤）：

SET transaction_isolation = 'READ-COMMITTED';

-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

执行以下 select 语句：

begin;
select * from t2 where i1 = 13 for share;

查看加锁情况：

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't2'
 and lock_type = 'RECORD'\G
 
***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 13, 3
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 3

lock_data = 13,3、lock_mode = S,REC_NOT_GAP 表示对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享普通记录锁。

lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 <id = 3> 的记录加了共享普通记录锁。

读已提交隔离级别下：

对于 select 语句中 where 条件覆盖范围内的记录，默认加共享普通记录锁。
对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他普通记录锁。

示例 SQL 对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加共享普通记录锁，属于默认行为，不多解释。

示例 SQL 从二级索引 idx_i1 中读取 <i1 = 13, id="3"> 的记录之后，根据主键字段值回表查询主键索引记录，因为读已提交隔离级别不需要保证可重复读，只需要防止其它事务修改或者删除主键索引中 <id = 3> 的记录，加共享普通记录锁就可以了。

回表读取到主键索引中 <id = 3> 的记录之后，InnoDB 会把记录返回给 server 层。

where 条件命中的二级索引 idx_i1 是非唯一索引，server 层不能确定刚刚读取到的就是满足 where 条件的最后一条记录，所以会要求 InnoDB 继续读取下一条记录。

InnoDB 从二级索引 idx_i1 中读取下一条记录，得到 <i1 = 14, id="4"> 的记录，发现这条记录不匹配 server 层下推到 InnoDB 的 where 条件（i1 = 13），不需要锁定这条记录。

读已提交隔离级别不需要保证可重复读，也就不需要对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录前面的间隙加共享间隙锁了。

4. 总结

没有需要总结的内容。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle</i1></i1></id></id></i1></i1></id></i1></id></i1></i1></i1></i1></i1></id></i1></id></id></id></i1></i1></id></i1>

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/16491536

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Redis对象共享池，性能优化小细节

如果你仔细研究过 Redis 中各种实现细节，你会发现为了性能，Redis 真的是不遗余力。作为一种高性能的键值存储系统，Redis 广泛用于缓存、会话管理、消息队列等多种场景。为了提高 Redis 在处理大量数据时的性能和效率，Redis 设计并实现了对象共享池（Shared Object Pool）这一内部机制。那么接下来松哥就和大家详细说一说 Redis 中的对象共享池。一设计目的 Redis 的对象共享池主要用于复用一些常用的数据对象，以减少内存的开销。在 Redis 中，一些常用的数据对象，主要是小整数（如 0 到 9999）等，是不会被改变的，因此可以安全地共享使用而无需重复创建。例如你设置 set k1 99 和 set k2 99，这时 k1 和 k2 其实指向的是同一个对象。通过共享这些对象，Redis 能够显著降低内存的使用量，并减少对象的创建和销毁时间，从而提升整体性能。二工作原理在 Redis 服务器启动时，会预先创建并存储一些常用的对象到一个全局的哈希表中，这个哈希表就是对象共享池。当 Redis 需要处理一个键值对时，会首先检查这个键...

2024-10-25

229

摘要：本文主要分析了MySQL 8.0 EXPLAIN ANALYZE命令的使用，并结合源码介绍其实现思路，帮助数据库使用者和开发者更好的使用、理解该功能。本文分享自华为云社区《【华为云MySQL技术专栏】MySQL 8.0 EXPLAIN ANALYZE 工具介绍》，作者：GaussDB 数据库。 1. EXPLAIN ANALYZE可以解决什么问题 MySQL 8.0.18 版本开始支持查询分析工具EXPLAIN ANALYZE，该工具不仅会实际执行SQL语句，还会展示SQL语句详细的执行信息，包含执行算子（Iterator）粒度的扫描行数、执行耗时、迭代次数等信息。 EXPLAIN ANALYZE工具是MySQL EXPLAIN FORMAT=TREE 功能的扩展，除了展示执行计划和代价估算之外，还提供了细粒度执行算子的耗时等信息。这使得DBA和开发人员能够基于代价估算和算子实际执行耗时信息，判断执行计划是否合理，并识别出后续的优化点。 2. EXPLAIN ANALYZE如何使用以TPC-H基准测试中的Q14 查询为例，该SQL为两个表的连接及GROUP BY聚合操作，用于...

2024-10-28

222

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。