Author: 杨泽(勉仁)
MySQL查询优化分析系列过往文章：
《MySQL查询优化分析 - MySQL优化执行的基础概念》
在数据库管理中，慢查询是影响业务响应速度的主要因素之一，还往往会消耗大量CPU资源，导致整体性能下降，严重影响数据库整体响应。本文总结了MySQL中慢查询的一些常见原因，并深入分析这些原因的分析和解决方法。这些分析能够解决很多业务场景的慢查问题。当然还有一部分慢查是在社区MySQL上无法解决的，在本文及后续的文章中会介绍PolarDB MySQL在查询优化与执行提升上做了哪些事情。下面本文将常见慢查的问题分以下场景介绍：无合理索引、无法利用已有索引、优化器无法选择正确执行计划、分析场景处理数据量大。
对于查询语句，如果没有合理的索引会导致查询需要扫描大量数据，IO开销和扫描数据行CPU开销都会非常大，导致查询慢。下面是一些没有合理索引的场景。
例如下面语句，虽然该语句的执行选择了字段c1上的索引i_c1，但其过滤性远不如等值条件的字段c2。选择i_c1索引实际需要扫描1万行，而c2字段上满足c2 = 1的行仅有1行，如果在c2上建索引那么该语句可以仅扫描一行。
mysql> explain select * from t1 where c1 >= 1 and c1 <= 10000 and c2 = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: i_c1
key: i_c1
key_len: 9
ref: NULL
rows: 18512
filtered: 10.00
Extra: Using index condition; Using where
mysql> select count(*) from t1 where c1 >= 1 and c1 <= 10000;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from t1 where c2 = 1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.05 sec)
我们在c2上创建索引，可以看到语句从扫描1万行变为扫描1行。
mysql> alter table t1 add index i_c2(c2);
mysql> explain select * from t1 where c1 >= 1 and c1 <= 10000 and c2 = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: i_c1,i_c2
key: i_c2
key_len: 9
ref: const
rows: 1
filtered: 18.52
Extra: Using where
如果查询语句的谓词条件是范围条件与等值条件，存在组合索引，那么当范围条件列作为索引前缀的时候，等值条件是无法被range optimizer用来生成引擎扫描range减少扫描行数的。
例如表t2上存在组合索引i_createtime_c1(create_time, c1)，下面语句只能通过create_time条件生成引擎扫描范围，所以我们看到估算扫描行数几万行。
mysql> explain select create_time, c1 from t2 where create_time between date_sub(now(), interval 1 hour) and now() and c1 =1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: range
possible_keys: i_createtime_c1
key: i_createtime_c1
key_len: 15
ref: NULL
rows: 49901
filtered: 10.00
Extra: Using index condition; Using index
合理的索引应该将等值条件列作为索引前缀，创建(c1, create_time)的组合索引。可以看到创建等值条件列c1为前缀的索引后，
mysql> alter table t2 add index i_c1_createtime(c1, create_time);
mysql> explain select create_time, c1 from t2 where create_time between date_sub(now(), interval 1 hour) and now() and c1 =1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: range
possible_keys: i_createtime_c1,i_c1_createtime
key: i_c1_createtime
key_len: 15
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using index
如果语句中仅有区分度很低的列索引，那么语句的执行会扫描大量数据。由于索引扫描回表操作会带来大量离散IO，可能造成走索引实际开销比主表全表扫描还要大。对于这种情况应该选择区分度高的列建索引。例如下面语句，表t3有10w行数据，在字段is_deleted上建的索引。语句选择的索引i_is_deleted，需要扫描一半数据且需要回表。
mysql> explain select * from t3 where is_deleted = 0 and code = 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: i_is_deleted
key: i_is_deleted
key_len: 9
ref: const
rows: 49970
filtered: 10.00
Extra: Using where
code字段区分度非常高，如果在code上创建索引会更合适。在code字段创建索引后，语句仅需要扫描很少的数据。
mysql> alter table t3 add index i_code(code);
mysql> explain select * from t3 where is_deleted = 0 and code = 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: i_is_deleted,i_code
key: i_code
key_len: 9
ref: const
rows: 1
filtered: 50.00
Extra: Using where
部分场景，可能明明已经创建了索引，但优化器并没有选择索引，或者选择索引后依然很慢。这可能是表设计或者查询使用不当，导致索引无法被利用。
当索引列类型和常量类型不一致的时候，可能导致索引无法使用。例如下面的例子，列类型是varchar，而常量类型是Int。在MySQL中varchar类型和int类型比较是会将varchar转换为int类型去比较，这就导致下面语句需要扫描每一行数据将id转换为int类型再做比较。
CREATE TABLE t4 AS WITH RECURSIVE t(id, code, is_deleted) AS (SELECT 1, 1, 1 UNION ALL SELECT id+1, id + 1, id %2 FROM t WHERE id < 100000) SELECT id, code, is_deleted FROM t;
mysql> show create table t4\G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`id` varchar(20) DEFAULT NULL,
`code` bigint(1) DEFAULT NULL,
`is_deleted` bigint(1) DEFAULT NULL,
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> explain select * from t4 where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
partitions: NULL
type: ALL
possible_keys: idx_id
key: NULL
key_len: NULL
ref: NULL
rows: 99827
filtered: 10.00
Extra: Using where
对于这种情况，语句常量值需要使用和列相同的数据类型。
mysql> explain select * from t4 where id = '1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
partitions: NULL
type: ref
possible_keys: idx_id
key: idx_id
key_len: 63
ref: const
rows: 1
filtered: 100.00
Extra: NULL
对于JOIN操作，如果关联字段类型不一样，比如一个是Int，一个是varchar，由于比较类型是Int，那么varchar字段的关联索引就无法用来生成REF访问，减少扫描行数。 可以看到下面t6表虽然选择了索引i_c2，但是扫描了索引全部数据。
mysql> SHOW CREATE TABLE t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`c1` bigint(1) DEFAULT NULL,
`c2` bigint(1) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> SHOW CREATE TABLE t6\G
*************************** 1. row ***************************
Table: t6
Create Table: CREATE TABLE `t6` (
`c1` bigint(1) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT t5.* FROM t5 LEFT JOIN t6 ON t5.c2 = t6.c2 WHERE t5.c2 = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
partitions: NULL
type: ref
possible_keys: i_c2
key: i_c2
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t6
partitions: NULL
type: index
possible_keys: i_c2
key: i_c2
key_len: 93
ref: NULL
rows: 99918
filtered: 100.00
Extra: Using where; Using index; Using join buffer (hash join)
对于JOIN操作，如果关联字段的字符集不一致，这也会导致索引无法被有效利用，进而导致扫描大量数据，查询执行慢。例如下面的场景，t7表的字符集是utf8mb4，t8表的字符集是utf8。t7.c1字段是无法转换到utf8字符集的，因为t8表虽然走了索引但实际是全索引扫描，实际场景中如果有t8表其他字段访问就会选择全表扫描。
mysql> SHOW CREATE TABLE t7\G
*************************** 1. row ***************************
Table: t7
Create Table: CREATE TABLE `t7` (
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` varchar(20) DEFAULT NULL,
KEY `i_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> SHOW CREATE TABLE t8\G
*************************** 1. row ***************************
Table: t8
Create Table: CREATE TABLE `t8` (
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` varchar(20) DEFAULT NULL,
KEY `i_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT t7.* FROM t7 LEFT JOIN t8 ON t7.c1 = t8.c1 WHERE t7.c1 = '1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t7
partitions: NULL
type: ref
possible_keys: i_c1
key: i_c1
key_len: 83
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t8
partitions: NULL
type: index
possible_keys: NULL
key: i_c1
key_len: 63
ref: NULL
rows: 100058
filtered: 100.00
Extra: Using where; Using index; Using join buffer (hash join)
我们把t8表的关联c1字段修改为字符集utf8mb4，那么就可以看到t8选择了REF访问，访问行数从10万行变为1行。
mysql> ALTER TABLE t8 MODIFY COLUMN c1 varchar(20) CHARACTER SET utf8mb4;
mysql> EXPLAIN SELECT t7.* FROM t7 LEFT JOIN t8 ON t7.c1 = t8.c1 WHERE t7.c1 = '1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t7
partitions: NULL
type: ref
possible_keys: i_c1
key: i_c1
key_len: 83
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t8
partitions: NULL
type: ref
possible_keys: i_c1
key: i_c1
key_len: 83
ref: const
rows: 1
filtered: 100.00
Extra: Using index
对于模糊查询LIKE语句，如果是以通配符为前缀，那么就无法利用索引。例如下面的语句中LIKE ’%55’，因为通配符在前，因此无法利用索引来缩小扫描范围。
mysql> EXPLAIN SELECT * FROM t7 WHERE c1 LIKE '%55'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t7
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99964
filtered: 11.11
Extra: Using where
对于模糊查询常量作为前缀，例如 LIKE ’55%’ 或者 LIKE ’55%5’，range optimizer会抽取出[55, 55MAX]的扫描范围，可以利用索引来减少扫描行数，这个时候表访问方式是RANGE，扫描行数从10万行变为1111行。
mysql> EXPLAIN SELECT * FROM t7 WHERE c1 LIKE '55%5'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t7
partitions: NULL
type: range
possible_keys: i_c1
key: i_c1
key_len: 83
ref: NULL
rows: 1111
filtered: 100.00
Extra: Using index condition
当谓词条件中，索引列上有表达式计算的时候，优化器会无法分析抽取range。例如下面语句，c1+ 1 = 2的条件是无法利用索引的。如果是写成c1 = 2 - 1，等值右侧都是常量组成会提前计算结果值，是可以的。可以看到下面语句一个选择了全表扫描10w行，一个选择了索引REF访问1行。
mysql> SHOW CREATE TABLE t9\G
*************************** 1. row ***************************
Table: t9
Create Table: CREATE TABLE `t9` (
`c1` bigint(1) DEFAULT NULL,
`c2` bigint(1) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 + 1 = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99940
filtered: 100.00
Extra: Using where
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 = 2 - 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ref
possible_keys: i_c1
key: i_c1
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
索引的扫描范围分析是由range optimizer处理，一些场景会触发range optimizer的限制。例如in list或者or的条件数太多，在分析扫描范围区间的时候，会消耗大量内存，导致超过range_optimizer_max_mem_size的大小，这类语句EXPLAIN后，在warnings信息中会有提示’Memory capacity of xx bytes for ’range_optimizer_max_mem_size’ exceeded. Range optimization was not done for this query.’。
mysql> SET @@range_optimizer_max_mem_size=16;
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 20, 21, 22, 23, 24, 25, 31, 32, 33, 34, 35)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ALL
possible_keys: i_c1
key: NULL
key_len: NULL
ref: NULL
rows: 99940
filtered: 50.00
Extra: Using where
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3170
Message: Memory capacity of 16 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
在上面的场景中我们将range_optimizer_max_mem_size调大，可以正确选择索引，扫描行数从10w行变为27行。
mysql> SET @@range_optimizer_max_mem_size=16*1024*1024;
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 20, 21, 22, 23, 24, 25, 31, 32, 33, 34, 35)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: range
possible_keys: i_c1
key: i_c1
key_len: 9
ref: NULL
rows: 27
filtered: 100.00
Extra: Using index condition
在实际业务中，有些慢查属于优化器无法选择正确计划，其中原因有：优化器一些rule-based策略影响；场景考虑不全；实际执行数据优化阶段无法感知等。
在社区MySQL中，优化器会倾向选择索引等值访问。索引等值访问的代价估算在内核中永远小于全表扫描代价，这个Rule在20年前数据量小，统计信息可能有偏差的时候，对维护索引选择的稳定性有很大帮助。但目前业务会存储很大的数据量，而且部分SAAS场景存在大租户，或者部分分析领域存在某一单值占比极大情况，会导致这类查询因选择索引造成大量回表访问导致性能差、大量IO严重影响业务系统。
例如下面场景，表t9有167万行，有c1 = 12345的数据有157万行。这里虽然优化器估算索引扫描行数为83w行，但是按照我们在MySQL查询优化分析 - MySQL优化执行的基础概念 里介绍的，这里索引回表代价也远高于全表扫描代价的。这是因为优化器按照规则调低了索引等值访问代价。这个场景在PolarDB MySQL中已经默认解决，会识别这类场景按照代价选择到正确的计划，范围扫描或者全表扫描。
mysql> SELECT COUNT(*) FROM t9;
+----------+
| COUNT(*) |
+----------+
| 1672863 |
+----------+
mysql> SELECT COUNT(*) FROM t9 WHERE c1 = 12345;
+----------+
| COUNT(*) |
+----------+
| 1572864 |
+----------+
mysql> EXPLAIN SELECT COUNT(*) FROM t9 WHERE c1 = 12345 AND c3 = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ref
possible_keys: i_c1
key: i_c1
key_len: 9
ref: const
rows: 833629
filtered: 10.00
Extra: Using where
对于索引范围，range optimizer会通过index dive来估算实际扫描行数。由于这会带来实际的数据访问，有一定的代价开销，优化器会限制index dive的场景。对于in list等值条件特别多的场景，当超过eq_range_index_dive_limit的阈值，优化器就不会做index dive，而是基于NDV统计信息去估算扫描行数。如果in list中有倾斜的数据，那么行数估算就会错误。
例如下面的场景，仍然是t9表，我们添加索引i_c2(c2)。最后的语句虽然选择i_c1要扫描157w行，i_c2仅扫描18行，但由于in list超过eq_range_index_dive_limit，range optimizer是基于NDV估算，导致错误选择i_c1索引。
如果我们把eq_range_index_dive_limit适当调大到40，那么可以看到优化器正确的选择了i_c2索引。
mysql> SHOW CREATE TABLE t9\G
*************************** 1. row ***************************
Table: t9
Create Table: CREATE TABLE `t9` (
`c1` bigint(1) DEFAULT NULL,
`c2` bigint(1) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c2` (`c2`),
KEY `i_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> SELECT COUNT(*) FROM t9 WHERE c1 in (12345, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13);
+----------+
| COUNT(*) |
+----------+
| 1572877 |
+----------+
mysql> SELECT COUNT(*) FROM t9 WHERE c2 in (2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 20, 21, 22);
+----------+
| COUNT(*) |
+----------+
| 18 |
+----------+
mysql> set @@eq_range_index_dive_limit=10;
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (12345, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) AND c2 in (2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 20, 21, 22)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: range
possible_keys: i_c2,i_c1
key: i_c1
key_len: 9
ref: NULL
rows: 224
filtered: 0.02
Extra: Using index condition; Using where
mysql> set @@eq_range_index_dive_limit=40;
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (12345, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) AND c2 in (2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 20, 21, 22)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: range
possible_keys: i_c2,i_c1
key: i_c2
key_len: 9
ref: NULL
rows: 18
filtered: 50.00
Extra: Using index condition; Using where
MySQL优化器会对索引固定范围扫描行数做动态采样估算，我们称其为index dive。但更多的场景，MySQL优化器是没有动态采样逻辑或者执行反馈能力的，选择率、不同条件相关性、扫描行数都是基于统计学假设。这就会造成很多场景，优化器的假设可能跟实际查询执行数据分布情况产生很大偏差。
例如MySQL中常见的ORDER BY LIMIT的索引选择问题，优化器会假设满足WHERE条件的数据行在提供ORDER BY序的索引上是均匀分布的，从而可能选择到提供ORDRE BY序的索引。如果真实数据分布在索引扫描靠前的位置，那么会很快返回，如果在靠后的位置那么就会导致扫描大量数据行。
mysql> SELECT COUNT(*) FROM t9;
+----------+
| COUNT(*) |
+----------+
| 1672863 |
+----------+
--优化器认为索引仅需要扫描877行就能遇到满足条件的数据行
mysql> EXPLAIN SELECT * FROM t9 WHERE c2 > 90100 AND c2 < 92000 ORDER BY c1 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: index
possible_keys: i_c2
key: i_c1
key_len: 9
ref: NULL
rows: 877
filtered: 0.11
Extra: Using where
--查询实际执行了3.33s。
mysql> SELECT * FROM t9 WHERE c2 > 90100 AND c2 < 92000 ORDER BY c1 LIMIT 1;
+-------+-------+------+
| c1 | c2 | c3 |
+-------+-------+------+
| 90101 | 90101 | 0 |
+-------+-------+------+
1 row in set (3.33 sec)
--PolarDB中的SQL Trace功能可以看到上述查询扫描了166w行。
*************************** xx. row ***************************
TYPE: SQL
SQL_ID: 7cskgg64n3t0p
SCHEMA_NAME: test
DIGEST_TEXT: SELECT * FROM `t9` WHERE `c2` > ? AND `c2` < ? ORDER BY `c1` LIMIT ?
MIN_ROWS_EXAMINED: 1662964
MAX_ROWS_EXAMINED: 1662964
上述查询中，优化器认为扫描i_c1索引遇到满足条件c2 > 90100 AND c2 < 92000的数据行仅需要扫描877行，但实际扫描了166w行数据。如果我们强制选择范围列索引i_c2，那么实际仅扫描1899行数据，加排序取top1，耗时非常短。
mysql> SELECT * FROM t9 FORCE INDEX(i_c2) WHERE c2 > 90100 AND c2 < 92000 ORDER BY c1 LIMIT 1;
+-------+-------+------+
| c1 | c2 | c3 |
+-------+-------+------+
| 90101 | 90101 | 0 |
+-------+-------+------+
1 row in set (0.01 sec)
类似的问题在MySQL的Index Merge路径中也会遇到，因为优化器假设不同索引独立选择率。例如两个索引选择率分别是sel1、sel2，访问的数据合并优化器认为选择率是乘积sel1*sel2。而在很多客户场景中，不同索引列上where condition实际都是访问大多相同的数据，可能都是近期插入数据的分析。这就导致Index merge的代价被低估，而Index merge导致多个索引数据访问和归并，导致反而性能更差。
类似的问题在JOIN、关联查询中也很常见。在JOIN、关联查询未执行前，无法知道关联字段的具体数据，优化器也无法推算关联扫描行数，仅是从NDV去推算，这就会导致估算产生很大差异。例如下面场景中，优化器无法知道tmp真实数据行，通过NDV估算选了i_c1索引，而c1 = 1的有13w行，导致t9表总共要扫描26w行数据。如果选择i_c2索引，t9表一共仅需要扫描2行数据。
CREATE TABLE tmp(c1 int, c2 int, c3 int);
INSERT INTO tmp VALUES(1, 2, 1), (1, 3, 1);
-- 选择了t9选择了i_c1索引，而c1=1的值有13w行。而c2 = 2或3的行都仅有1行。
mysql> EXPLAIN SELECT COUNT(*) FROM tmp JOIN t9 ON t9.c1 = tmp.c1 AND t9.c2 = tmp.c2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ref
possible_keys: i_c1,i_c2
key: i_c1
key_len: 9
ref: test.tmp.c1
rows: 17
filtered: 10.00
Extra: Using index condition; Using where
mysql> SELECT COUNT(*) FROM tmp JOIN t9 ON t9.c1 = tmp.c1 AND t9.c2 = tmp.c2;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.52 sec)
mysql> SELECT COUNT(*) FROM tmp JOIN t9 FORCE INDEX (i_c2) ON t9.c1 = tmp.c1 AND t9.c2 = tmp.c2;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
这类问题在社区MySQL中没有解决，在PolarDB中会使用动态执行解决这类问题，后面介绍PolarDB查询优化与执行的文章会介绍这部分功能。
优化器有一些需要推算的场景，例如如果索引前缀列是等值条件，而后缀能够提供ORDER BY的序，那么就可以利用索引序省去filesort。但如果算法设计不得当或者场景考虑不全，会导致可以省去的操作没有被省略造成很多额外开销。下面的例子中可以看到索引i_c1_c2在c1、c2上是等值访问，但优化器依然添加了filesort，对13w行数据做了扫描并排序。
mysql> SHOW CREATE TABLE t9\G
*************************** 1. row ***************************
Table: t9
Create Table: CREATE TABLE `t9` (
`c1` bigint(1) DEFAULT NULL,
`c2` bigint(1) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> SELECT COUNT(*) FROM t9 WHERE c1 IN (1) AND c2 BETWEEN 1 AND 1;
+----------+
| COUNT(*) |
+----------+
| 131072 |
+----------+
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (1) AND c2 BETWEEN 1 AND 1 ORDER BY c1, c2 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ref
possible_keys: i_c1_c2
key: i_c1_c2
key_len: 18
ref: const,const
rows: 132012
filtered: 100.00
Extra: Using index condition; Using filesort
mysql> SELECT * FROM t9 WHERE c1 in (1) AND c2 BETWEEN 1 AND 1 ORDER BY c1, c2 LIMIT 1;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
1 row in set (0.28 sec)
在PolarDB中上述场景可以分析出索引是等值访问，可以利用索引序，不需要filesort，仅扫描一行即可返回。
mysql> EXPLAIN SELECT * FROM t9 WHERE c1 in (1) AND c2 BETWEEN 1 AND 1 ORDER BY c1, c2 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ref
possible_keys: i_c1_c2
key: i_c1_c2
key_len: 18
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition
mysql> SELECT * FROM t9 WHERE c1 in (1) AND c2 BETWEEN 1 AND 1 ORDER BY c1, c2 LIMIT 1;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
1 row in set (0.00 sec)
优化器中存在一些场景仅考虑了扫描行数等条件，而未考虑数据访问的具体代价。例如下面场景中，对REF场景需要回表的二级索引访问出现仅考虑了回表代价而未考虑索引访问代价的情况，导致选择了非覆盖索引。t10表i_c1和i_c1_c2虽然一行数据的情况下区别不大，但是驱动表t9有180w行，未选择覆盖索引会严重影响性能。
mysql> SHOW CREATE TABLE t10\G
*************************** 1. row ***************************
Table: t10
Create Table: CREATE TABLE `t10` (
`c1` bigint(1) DEFAULT NULL,
`c2` bigint(1) DEFAULT NULL,
`c3` bigint(1) DEFAULT NULL,
KEY `i_c1` (`c1`),
KEY `i_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT t9.*, t10.c1, t10.c2 FROM t9 LEFT JOIN t10 ON t10.c1 = t9.c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t9
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1797342
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t10
partitions: NULL
type: ref
possible_keys: i_c1,i_c1_c2
key: i_c1 --选择了非覆盖索引
key_len: 9
ref: test.t9.c1
rows: 1
filtered: 100.00
Extra: NULL
类似的还有MySQL对ORDER BY LIMIT场景的索引选择也仅考虑了索引扫描行数而未考虑覆盖索引和非覆盖索引访问代价区别。这些在PolarDB中都得到了修正。
优化器做Index dive很多时候能够规避数据倾斜导致的行数估算问题。但是Index dive采样估算也难以达到很高的准确性，例如两个索引估算行数都可能偏差真实行数一倍，如果一个高一倍，一个低一倍，这会导致4倍偏差。另一种是，如果采样的索引page上恰好有大量数据删除，这样会导致估算的行数和真实情况相差巨大。PolarDB中对这些场景也做了优化，来尽量避免类似常见导致非常差的计划选择问题。
有不少慢查的产生是业务需求，需要访问大量数据，对很多数据做计算。这类往往需要数据库计算能力的提升，例如PolarDB中的并行执行，列存加速。
例如：
分析全表
SELECT COUNT(*) FROM customer_table;
分析的时间段范围内数据量很大。
SELECT SUM(c1) FROM customer_table WHERE create_time > date_sub(now(), interval 1 year AND create_time < now();
关联访问大量数据行。
关联字段有索引，但驱动表每一行，被驱动表要扫描大量数据。
SELECT SUM(account) FROM t1 LEFT JOIN t2 ON t2.oid = t1.cid WHERE t1.cid in ( in_list); // oid有索引，REF 100w行。
笛卡尔积、JOIN大数据量。两个只有1w行的表，笛卡尔积JOIN会产生1亿行数据。
SELECT SUM(account) FROM t1, t2 [WHERE t1.create_time > xx]; JOIN (10000 * 10000 = 100000000)
物化大量数据。t2表中有大量数据。
SELECT SUM(account) FROM t1 JOIN (SELECT cid FROM t2 GROUP BY cid)td on td.cid = t1.cid;
产生大量数据，再执行JOIN或者关联查询 (t1\t2产生100w行，再和t3 JOIN)
SELECT SUM(account) FROM t1 JOIN t2 ON t1.cid = t2.cid JOIN t3 on t3.oid = t2.oid;
当活跃线程太多，CPU打满。DB更多开销在多线程调度上，这会导致原本简单的查询变慢。
系统冷启动或者buffer pool太小，这会导致查询需要大量访问冷数据，大量物理读IO，导致慢查多。
当有长事务，增删改的DML操作比较多的情况，ReadView中min_trx_id小于二级索引page上的事务id，这会导致索引访问无法通过自身判断可见性，从而导致大量索引回表做可见性判断，从而导致查询变慢。
如果有长查询等导致可以purge的ReadView一直无法推进，同时有大量delete的情况发生，那么查询语句可能会访问大量已经删除的数据行。
慢查对系统的影响是多方面的，主要的影响因素可以分以下几个方面：
执行太久的查询会影响系统purge，从而影响整个DB性能。我们会看到innodb_trx_history_list变的很大。数据无法purge，导致各类SQL访问可能因此变慢。
慢查消耗大量CPU资源。大量并发执行的慢查询，会导致CPU负载很高。这个时候优化慢查询，可以降低CPU负载，节省CPU资源，提升系统稳定性。
慢查的大数据量扫描会污染buffer pool。这会导致系统整体IOPS很高，响应变慢。
导致吞吐有限，QPS低。如果大量慢查，业务系统整体响应会变慢，影响DB可以支撑的业务处理能力。
本篇文章总结了各类可能导致慢查的原因，包括业务没有设计合理表结构或者索引、优化器原因、业务需求，也简要描述了可能导致慢查的其他系统原因，和慢查对系统的危害。