MySQL-查询性能优化

吹梦到西洲

发布于：Aug 3, 2020

MySQL-查询性能优化

在《高性能MySQL》一书中，本章首先介绍了查询设计的一些基本原则，然后介绍一些更深的查询优化的技巧，也会介绍一些 MySQL 优化器内部机制。

之所以选择以这种方式记录，是为了以后需要时可以通过手机看到，而不用再去找书，再去看大量的内容选择想要的几句话。

查询为什么会变慢？

査询中真正重要的是响应时间。如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化査询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。优化查询的目的就是减少和消除这些操作所花费的时间。

通常来说，查询的生命周期大致可以按照顺序来看：

从客户端，到服务器，然后在服务器上进行解析；
生成执行计划，执行，并返回结果给客户端。
其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，可以通过下面两个步骤来分析：

确认应用程序是否在检索大量超过需要的数据。这通常意味着访向了太多的行，但有时候也可能是访问了太多的列。
确认 MySQL 服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后又丢弃掉。这样会给 MySQL 服务器带来负担，增加网络开销，消耗服务器的 CPU 和内存资源。

下面是一些典型的案例：

查询不需要的记录：MySQL 并不只是会返回我们需要的数据，而是先返回全部结果集再进行计算，然后再丢弃大量数据。
多表关联时返回全部列：只选择需要使用的表中的列，而不要全部返回。
总是取出全部列：也就是 SELECT * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会消耗服务器资源。
重复查询相同的数据：记得用缓存。

MySQL 是否在扫描额外的记录

在确定查询只返回需要的数据后，接下来应该看看查询为了返回结果是否扫描了过多的数据。主要有三个指标，这三个指标会记录在 MySQL 的慢日志中：

响应时间
扫描的行数
返回的行数

在 EXPLAIN 语句中的 type 列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。列出的这些访问速度从慢到快，扫描的行数从小到大。

一般 MySQL 使用如下三种方式应用 WHERE 条件，从好到坏依次为：

在索引中使用 WHERE 条件来过滤不匹配的记录。在存储引擎层完成。
使用索引覆盖扫描（在 Extra 列中出现了 Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。在 MySQL 服务器层完成的，无须再回表査询记录。
从数据表中返回数据，然后过滤不满足条件的记录（在 Extra 列中出现 Using Where）。在 MySQL 服务器层完成，MySQL 需要先从数据表读出记录然后过滤。

如果发现査询需要扫描大量的数据，但只返回少数的行，那么可以：

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表就可以返回结果。

改变库表结构。例如使用单独的汇总表。
重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个査询。

重构查询的方式

在优化查询时，目标是找到一个更优的方法获得实际需要的结果，而不一定同时需要从 MySQL 获取一模一样的结果。

复杂查询还是简单查询：是否需要将一个复杂查询分成多个简单查询。
切分查询：分而治之，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次返回一小部分查询结果。
分解关联查询：将关联查询分解成单个查询。

查询执行的基础

《高性能MySQL》的图有点糊，正在 ProcessOn 开画的时候想起了我还有英文版的《High Performance MySQL》，英文原版的就清楚多了。

根据上图，我们可以看到在执行一条查询时，MySQL 做了些什么：

客户端发送一条查询给服务器。
服务器先查询缓存，命中缓存则直接返回，否则进入下一阶段。
服务器进行 SQL 解析、预处理、再由查询优化器生成对应的执行计划。
MySQL 根据优化器生成的执行计划，调用存储引擎 API 执行查询。
将结果返回给客户端。

MySQL客户端/服务器通信协议是半双工，在任何一个时刻，要么是服务器向客户端发送数据，要么相反，不能同时发生。

查询优化处理

查询的生命周期最重要的一步就是将 SQL 转换成执行计划，MySQL 再依照执行计划和存储引擎进行交互。这包括：解析 SQL、预处理、优化 SQL 执行计划。这个过程中任何错误都可能终止查询。

语法解析器与预处理

解析：MySQL 通过关键字将 SQL 语句解析，生成对应的“解析树”。解析器使用 MySQL 语法规则验证和解析查询。

预处理：根据一些 MySQL 规则进一步检查解析树是否合法。如，检查数据表、列是否存在。

查询优化器

当语法树合法时，由优化器将其转化为执行计划。一条查询可以有多种执行方式并且返回相同的结果，优化器的作用就是找出其中最好的那个。优化策略包括两种，一种是静态优化，一种是动态优化。

下面是一些 MySQL 能够处理的优化类型：

重新定义关联表的顺序
将外连接转化成内连接
使用等价变换规则，如( 5 = 5 And a > 5 ==> a > 5)
优化 COUNT()、MIN()、MAX()：要 MIN()，只需查询 B-Tree 索引最左端的记录，MAX() 则是最右端记录。
预估并转化为常数表达式：当 MySQL 检测到表达式可以转为常数时，就会一直把该表达式作为常数进行优化处理。有时候甚至查询也能够转化为常数，如在索引上执行 MIN()，甚至主键或者唯一键查找语句。
覆盖索引扫描：当索引中的列包含查询中所有需要使用的列时，使用索引返回，而无需查询对应的行。
子查询优化
提前终止查询：发现已满足查询需求时，立刻终止查询。如使用 LIMIT 子句或者发不成立条件(id = -1)。
等值传播：如果两列的值通过等式关联，MySQL 把其中一列的 WHERE 条件传递到另一列上。
列表 IN() 的比较：在一些 DBS 中，IN() 完全等同于多个 OR 子句($\mathcal{O}(n)$)，在 MySQL 中则不然，MySQL 将 IN() 列表中的数据先排序，然后通过二分查找($\mathcal{O}(\lg n)$)来确定列表中的值是否满足条件。
$\dots$

MySQL 如何执行关联查询

MySQL 关联策略很简单：MySQL 对任何关联都执行嵌套循环关联操作，即 MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联无法找到更多的行，MySQL 返回到上一层次关联表(回溯?)，看能否找到更多的匹配记录，以此类推迭代执行。

执行计划

和很多其他关系数据库不同，MySQL 并不会生成査询字节码来执行查询。MySQL 生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行 EPLAIN EXTENDED 后，再执行 SHOW WARNINGS，就可以看到重构出的查询。

任何多表查询都可以使用一棵树表示，例如下图为执行一个四表的关联操作。

上图属于平衡树，但是 MySQL 的查询并不是如此，正如前面所说的嵌套循环加回溯，MySQL 的的执行计划则是一棵左侧深度优先树。

关联查询优化器

通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

查询执行引擎

在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL 的查询执行引擎根据这个执行计划来完成查询。执行计划是一种数据结构，而不是和很多其他关系型数据库那样的字节码。

为了执行查询，MySQL 只需要重复执行计划中的各个操作，直到完成所有的数据查询。

返回结果

即使查询不需要返回结果集给客户端，MySQL 仍然会返回这个查询的一些信息，如该查询影响到的行数。

如果查询可以被缓存，那么 MySQL 在这个阶段也会将结果存放到查询缓存中。

MySQL 将结果集返回客户端是一个增量、逐步返回的过程。当开始生成第一条结果时，MySQL 就可以开始向客户端逐步返回结果集。

这样处理有两个好处：

服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。
让 MySQL 客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足 MySQL 客户端/服务器通信协议的封包发送，再通过 TCP 协议进行传输，在 TCP 传输的过程中，可能对 MySQL 的封包进行缓存然后批量传输。

优化特定类型的查询

优化 COUNT() 查询

COUNT() 有两种非常不同的作用：

它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的(不统计 NULL)。如果在 COUNT() 的括号中指定了列或列表达式，统计的就是这个表达式有值的结果数。
统计结果集的行数。当 MySQL 确认括号内的表达式值不可能为空时，其实就是在统计行数。当使用 COUNT(*) 时，通配符 * 实际上实际上并不会扩展成所有列，而是会忽略所有列而直接统计所有行数。

MyISAM 的 COUNT() 函数虽然非常快，但是是有前提条件的，即只有没有任何 WHERE 条件的COUNT(*) 才非常快，因为此时无序实际计算表的行数，如果包含了 WHERE，那速度就没什么过人之处了。

优化关联查询

确保 ON 或者 USING 子句中的列上有索引。在创建索引时候就要考虑到关联的顺序。一般来说，只需要在关联顺序中的第二个表的相应列上创建索引。

确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化。

当升级 MySQL 的时候注意：关联语法、运算符优先级等其他可能会发生变化的地方。以前普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

优化 GROUP BY 和 DISTINCT

MySQL 优化器会在内部处理时相互转化这两类查询。当无法使用索引时，GROUP BY 使用临时表或文件排序来做分组。

优化 LIMIT 分页

在 LIMIT 1000,20 这样偏移量非常大的查询时，尽可能地使用索引覆盖。

LIMIT 和 OFFSET 的问题，实际上是 OFFSET 的问题，它会导致 MySQL 扫描大量不需要的数据然后再抛弃。可以使用书签记录，或者使用预先计算的汇总表，或者关联到冗余表，冗余表只包含主键列和需要排序的列。

优化 SQL_CALC_FOUND_ROWS

分页时，在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS 可以获得去掉 LIMIT 以后满足条件的行数，因此可以作为分页的总数。

或者缓存较多数据，再从缓存中获取。

或者考虑 EXPLAIN 中的 rows 列的值作为结果集总数的近似值。

优化 UNION 查询

除非确实需要服务器消除重复行，否则一定要使用 UNION ALL。如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT，会对整个临时表的数据做唯一性检查。

静态分析查询

$\dots$

使用用户自定义变量

$\dots$

慢查询优化

MySQL 中 InnoDB 与 MyISAM 存储引擎实现索引的数据结构是 B-Tree 早都已经是让人耳熟能详的话了。那 B+Tree 的性质对于索引有什么有用之处呢？

为什么索引长度要尽量短？

对数据的查找过程通常会伴随着不止一次的 IO，而 IO 次数与 B+Tree 的高度密切相关。如果当前当数据表的数据项为 N，每个磁盘块的数据项的数量为 m，B+Tree高度为 h，则有 $h = \log_{m+1}N$。当数据量 N 一定时，$m \uparrow$，则 $h \downarrow$；而 $m = $ 磁盘块大小 / 数据项大小，磁盘块的大小也就是一个数据页的大小，是固定的。如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 $4$ 字节，要比 bigint $8$ 字节少一半。这也是为什么 B+Tree 要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当每个磁盘块只包含 1 个数据项时将会退化成线性表。
最左匹配：在 B+Tree 中叶子结点中的数据都是有序的，在查找时从最左边开始匹配，联合索引中前面的列匹配之后才匹配后面的列。搜索树是左侧深度优先树。

慢查询优化基本步骤

观察分析状态

观察分析服务器状态 show status;

mysql> show status like 'Queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Queries       | 7     |
+---------------+-------+
1 row in set (0.00 sec)

定位慢查询 SQL

检查是否开启慢查询

mysql> show variables like '%query%';
+------------------------------+------------------------------------------+
| Variable_name                | Value                                    |
+------------------------------+------------------------------------------+
| binlog_rows_query_log_events | OFF                                      |
| ft_query_expansion_limit     | 20                                       |
| have_query_cache             | YES                                      |
| long_query_time              | 10.000000                                |
| query_alloc_block_size       | 8192                                     |
| query_cache_limit            | 1048576                                  |
| query_cache_min_res_unit     | 4096                                     |
| query_cache_size             | 1048576                                  |
| query_cache_type             | OFF                                      |
| query_cache_wlock_invalidate | OFF                                      |
| query_prealloc_size          | 8192                                     |
| slow_query_log               | OFF                                      |
| slow_query_log_file          | /usr/local/var/mysql/iMacs-iMac-slow.log |
+------------------------------+------------------------------------------+
13 rows in set (0.01 sec)

# 需要注意的是
# long_query_time	10.000000
# slow_query_log	OFF
# slow_query_log_file	/usr/local/var/mysql/iMacs-iMac-slow.log

查询慢查询的数量

mysql> show status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

打开慢查询日志

set global slow_query_log = on;

# 添加索引
alter table t_name add index idx_name(name);

# MySQL 默认查询并不一定是 聚集索引(主键)， 而是由 MySQL查询优化器来决定，消除尽可能多的重复
select count(id) from t_name;
# 强行指定查询索引
select count(id) from t_name force index (primary);

设置慢查询阈值，超过 1s 即为慢查询(模拟)

1	set long_query_time = 1;

EXPLAIN 查看执行计划

1 2	# explain + 查询语句可以解释查询信息查询结果关键字段 type、extra explain select * from emp;

关于 EXPLAIN 结果列的含义，详见：MySQL-Explain 中的列。

效率	type 列中项	解释
$\rightarrow$	`const/system`	常量，表只有一行(用于 MyISAM、Memory)。
$\uparrow$	`eq_ref`	搜索时使用 PK 或 UNIQUE KEY，常用于多表联查。
$\uparrow$	`ref`	根据索引查找一个或多个值。
$\uparrow$	`index_merge`	合并索引，使用多个单列索引查找。
$\uparrow$	`range`	按索引进行范围查找。
$\uparrow$	`index`	按索引全表扫描。
$\uparrow$	`all`	所谓的全表扫描。

Extra 列中项(部分)	解释
“Using index”	MySQL 将使用覆盖索引，以避免访问表。不要把覆盖索引和 index 访问类型弄混。
“Using where”	MySQL 服务器将在存储引擎检索行后再进行过滤。在 WHERE 条件里涉及索引列时，就能被存储引擎检验，不是所有带 WHERE 子句的查询都会显示 “Using where”。
“Using temporary”	MySQL 在对查询结果排序时会使用一个临时表。
“Using filesort”	MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。
“Range checked for each record (index map: N)”	没有好用的索引，新的索引将在联接的每一行上重新估算。N 是显示在 possible_keys 列中索引的位图，并且是冗余的。

SHOW PROFILE 查看执行时间

查看是否开启

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

查看当前会话

1 2	mysql> show profiles; Empty set, 1 warning (0.00 sec)

查看某 Query_ID 开销

1 2	mysql> show profile for query xxx; Empty set, 1 warning (0.00 sec)

扩展阅读MySQL索引原理及慢查询优化

参考

《高性能 MySQL》

顺便记录刚刚看到的 ProcessOn 上几个特别骚的图：

更新于：Jun 20, 2021

慢查询优化

JVM-垃圾收集器与内存分配策略

垃圾收集器与内存分配策略如何判断对象已死？引用计数法在对象中添加一个引用计数器，每当有一个地方引用它时，计数器值就加一；当引用失效时，计数器值就减一；任何时刻计数器为零的对象就是不可能再被使用的...

MySQL-Explain中的列

EXPLAIN 中的列123456789101112131415MySQL> explain select 1\G*************************** 1. row **...