作者：龚唐杰

爱可生 DBA 团队成员，主要负责 MySQL 技术支持，擅长 MySQL、PG、国产数据库。

(相关资料图)

本文来源：原创投稿

* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1背景

某系统执行更新操作发现很慢，发现有大量慢日志，其中 Lock time 时间占比很高，MySQL 版本为 5.7.25，隔离级别为 RR。

2分析

查看表结构以及 UPDATE 语句的执行计划：

mysql>showcreatetabletest;+-------+---------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+---------------------------------------------------------------------------------------------------------------------+|test|CREATETABLE`test`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(30)COLLATEutf8mb4_binDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2621401DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_bin|+-------+----------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>explainupdatetestsetname="test"wherename="a";+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|1 |UPDATE |test |NULL |index |NULL |PRIMARY|4|NULL|2355988|100.00|Usingwhere|+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+1rowinset(0.00sec)

通过执行计划发现，该 SQL 是走的主键全索引扫描，并且对于 name 列未加索引，当多个事务同时执行时，就会观察到有阻塞出现。

* 建议用电脑端查看。

事务1事务2

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set name ="test" where name="a"; Query OK, 262144 rows affected (4.67 sec) Rows matched: 262144 Changed: 262144 Warnings: 0

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update test set name ="test1" where name="b";

若 name 列的重复值不多，那么可以对 name 列添加索引即可解决该问题。因为 InnoDB 的行锁机制是基于索引列来实现的，如果 UPDATE 语句能使用到 name 列的索引，那么就不会产生阻塞，导致业务卡顿。

但若是 name 列的值的区分度很低，就会导致 SQL 不会走 name 列的索引，示例如下：

先添加索引

mysql>altertabletestaddindextt(name);QueryOK,0rowsaffected(2.74sec)Records:0Duplicates:0Warnings:0

然后查看执行计划，发现可能用到的索引有 tt，但是实际情况依然走主键全索引扫描。

mysql>explainupdatetestsetname="test"wherename="a";+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|1 |UPDATE |test |NULL |index |tt |PRIMARY|4|NULL|2355988|100.00|Usingwhere|+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+1rowinset(0.00sec)

因为 MySQL 的优化器是基于代价来评估的，我们可以通过 optimizer trace 来观察。

mysql>showvariableslike"optimizer_trace";+-----------------+--------------------------+|Variable_name|Value|+-----------------+--------------------------+|optimizer_trace|enabled=off,one_line=off|+-----------------+--------------------------+1rowinset(0.01sec)

可以看到值为 enabled=off，表明这个功能默认是关闭的。

如果想打开这个功能，必须⾸先把 enabled 的值改为 on。

mysql>setoptimizer_trace="enabled=on";QueryOK,0rowsaffected(0.00sec)

然后执行该 SQL，查看详细的信息，这里我们主要关注的是 PREPARE阶段的成本计算。

mysql>updatetestsetname="test"wherename="a";QueryOK,262144rowsaffected(5.97sec)Rowsmatched:262144Changed:262144Warnings:0mysql>SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G

详细结果如下。

mysql>SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G***************************1.row***************************QUERY:updatetestsetname="test"wherename="a"TRACE:{"steps":[{"substitute_generated_columns":{}},{"condition_processing":{"condition":"WHERE","original_condition":"(`test`.`name`="a")","steps":[{"transformation":"equality_propagation","resulting_condition":"multipleequal("a",`test`.`name`)"},{"transformation":"constant_propagation","resulting_condition":"multipleequal("a",`test`.`name`)"},{"transformation":"trivial_condition_removal","resulting_condition":"multipleequal("a",`test`.`name`)"}]}},{"table":"`test`","range_analysis":{"table_scan":{"rows":2355988,"cost":475206},"potential_range_indexes":[{"index":"PRIMARY","usable":true,"key_parts":["id"]},{"index":"tt","usable":true,"key_parts":["name","id"]}],"setup_range_conditions":[],"group_index_range":{"chosen":false,"cause":"no_join"},"analyzing_range_alternatives":{"range_scan_alternatives":[{"index":"tt","ranges":["0x0100610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000<=name<=0x0100610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"],"index_dives_for_eq_ranges":true,"rowid_ordered":true,"using_mrr":false,"index_only":false,"rows":553720,"cost":664465,"chosen":false,"cause":"cost"}],"analyzing_roworder_intersect":{"usable":false,"cause":"too_few_roworder_scans"}}}}]}MISSING_BYTES_BEYOND_MAX_MEM_SIZE:0INSUFFICIENT_PRIVILEGES:01rowinset(0.00sec)

可以发现执行全表扫描的成本为 475206，走索引 tt 的成本为 664465，所以 MySQL 选择了全表扫描。

那么如果是这种情况该怎么处理呢？

如果 InnoDB 隔离级别是 RR，数据库层面没有太好的方式，推荐应用端进行改造。

如果数据库隔离级别可以更改，那么可以改为 RC 来解决阻塞的问题。因为 RC 模式下支持半一致性读。

什么是半一致性读呢？

简单来说就是当要对行进行加锁时，会多一步判断该行是不是真的需要上锁。比如全表扫描更新的时候，我们只需要更新 WHERE 匹配到的行，如果是没有半一致性读就会把所有数据进行加锁，但是有了半一致性读，那么会判断是否满足 WHERE 条件，若不满足则不会加锁（提前释放锁）。

那么对于区分度低的字段就可以使用半一致性读特性来优化，这样更新不同的值就不会互相等待，导致业务卡顿。

* 建议用电脑端查看。

事务1事务2

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test set name ="test" where name="a";Query OK, 262144 rows affected (9.30 sec)Rows matched: 262144 Changed: 262144 Warnings: 0

mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> update test set name ="test1" where name="b";Query OK, 262144 rows affected (8.46 sec)Rows matched: 262144 Changed: 262144 Warnings: 0

3结论

1. 行锁机制是基于索引列实现的，若没有使用到索引，则会进行全表扫描。

2. 半一致性读是基于 RC 隔离级别的优化，可以减少锁冲突以及锁等待，提升并发。

本文关键字：#MySQL# #InnoDB# #事务##锁#

---

文章推荐：

技术分享 | 如何校验 MySQL&Oracle 时间字段合规性

技术分享 | ibdata1 “减肥” 记

故障分析 | TRUNCATE 到底因何而慢？

技术分享 | DBA 抓包神器 tshark 测评

源码分析 | MySQL 的 commit 是怎么 commit 的？

技术分享 | 一文了解 MySQL Optimizer Trace 的神奇功效

故障分析 | MySQL 升级到 8.0 变慢问题分析

技术分享 | 一招解决 MySQL 中 DDL 被阻塞的问题

故障分析 | 一条本该记录到慢日志的 SQL 是如何被漏掉的

关于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

SQLE 获取

类型地址

版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_development.html

提交有效 pr，高质量 issue，将获赠面值 200-500 元（具体面额依据质量而定）京东卡以及爱可生开源社区精美周边！

更多关于 SQLE 的信息和交流，请加入官方QQ交流群：637150065

关键词：