关于mysql:一个慢日志问题引出的-MySQL-半一致性读的应用场景

作者通过一个慢日志问题，引出 MySQL 半一致性读的概念及理论利用场景。

作者：龚唐杰

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

本文起源：原创投稿

• 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

背景

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

剖析

查看表构造以及 UPDATE 语句的执行打算：

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2621401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain update test set name ='test' where name='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 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)

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

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

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

先增加索引

mysql> alter table test add index tt(name);
Query OK, 0 rows affected (2.74 sec)
Records: 0 Duplicates: 0 Warnings: 0

而后查看执行打算，发现可能用到的索引有 tt，然而理论状况仍然走的主键全索引扫描。

mysql> explain update test set name ='test' where name='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 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)

因为 MySQL 的优化器是基于代价来评估的，咱们能够通过 optimizer trace 来察看。

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)

能够看到值为 enabled=off，表明这个性能默认是敞开的。

如果想关上这个性能，必须⾸先把 enabled 的值改为 on。

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

而后执行该 SQL，查看具体的信息，这里咱们次要关注的是 PREPARE 阶段的成本计算。

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

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G 

具体后果如下。

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: update test set name ='test' where name='a'
TRACE: {
"steps": [
{"substitute_generated_columns": {}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test`.`name` ='a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('a', `test`.`name`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('a', `test`.`name`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('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: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

能够发现执行全表扫描的老本为 475206，走索引 tt 的老本为 664465，所以 MySQL 抉择了 全表扫描。

那么如果是这种状况改怎么解决呢？

如果 InnoDB 隔离级别是 RR，数据库层面没有太好的形式，举荐利用端进行革新。

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

什么是半一致性读呢？

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

那么对于区分度低的字段就能够应用半一致性读个性来优化，这样更新不同的值就不会相互期待，导致业务卡顿。

论断

1. 行锁机制是基于索引列实现的，若没有应用到索引，则会进行全表扫描。
2. 半一致性读是基于 RC 隔离级别的优化，能够缩小锁抵触以及锁期待，晋升并发。

对于 SQLE

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

SQLE 获取