查询性能优化

设计优秀的库表结构及索引对于高性能来说必不可少，但这还不够。糟糕的查询往往不能发挥索引的功效，也就达不到所谓的高性能。

要想实现高性能，库表结构优化，索引优化，查询优化需要齐头并进。

为什么查询速度会很慢？

查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器进行解析，生成执行计划，执行，并返回结果给客户端。

其中“执行”可以认为是整个周期里最重要的一个阶段，其中包含了大量为了检索数据到存储引擎的调用以及调用后的处理，包括排序，分组等。

查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划，锁等待….。

了解查询的生命周期，清楚时间消耗在哪里，对于查询优化意义重大。

慢查询基础

优化数据访问

查询性能低下最基本的原因是：访问的数据太多，有些查询虽然返回的数据不多，但是底层可能需要访问大量的数据来做筛选。

大部分性能低下的查询可以通过减少数据访问量来进行优化。

确认应用程序是否检索了大量的数据行。
确认 MySQL 服务器是否在分析大量超过需要的数据行。

只请求有用的数据

有些查询会请求多余的数据，在应用程序中这些数据会被丢弃，这不仅给 MySQL 服务器带来不必要的负担，还增加了网络开销，CPU 和内存开销。

要想达到高性能的查询，应该时常检查是否犯以下错误：

查询不需要的记录只查询需要用到的数据行，例如分页查询时，请求 MySQL 时就应该分页，而不是在应用程序中做分页。
多表关联时返回全部列
取出所有的列虽然 SELECT _ 写起来很简单，但是每次使用时都需要好好思考，是否真的需要取出所有列？取出所有列会让覆盖索引失效，给服务器带来不必要的 I/O，内存和 CPU 消耗。一些 DBA 是严格禁止使用 SELECT _ 的。
重复查询相同的数据如果一些查询总是返回相同的数据，那么应该考虑使用数据缓存。

是否扫描了额外的记录

对于 MySQL，衡量查询开销的三个指标：

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

响应时间

响应时间包括服务时间和等待时间，等待时间是不确定的，包括磁盘 I/O，等待行锁等。在高并发下，等待时间可能就会比较长，但是服务时间和查询设计的好坏有直接关系。

扫描和返回的行数

分析查询时，查看扫描的行数是非常有帮助的，可以在一定程度上说明该查询的效率是否高效。

理想情况下，扫描的行数 = 返回的行数。实际情况下，能做到这一点的查询并不多。

应该最大程度的减少存储引擎扫描的数据行。

访问类型

扫描的行数应该越少越好，访问类型也应该越高效越好。

同样的行，不同的访问类型，扫描的代价是不一样的。

全表扫描 < 范围扫描 < 唯一索引查询 < 常数引用等。

应该尽量避免全表扫描，性能是最差的，最简单的办法就是建立索引。

如果发现查询需要扫描大量的行，可以尝试这样优化：

使用索引覆盖扫描
改变表结构，使用汇总表
重写这个复杂的查询

重构查询的方式

查询一个结果集可以有很多种方式，我们应该尽力找到性能更好的方式。

一个复杂查询还是多个简单查询

如果一个复杂查询效率很低时，应该考虑是否可以拆分成多个简单的查询。

传统实现中，人们总是强调让数据库层做尽可能做多的工作，因为网络通信和数据库连接是一件代价很高的事情。

MySQL 已经让连接和断开连接都很轻量级了，返回一个小的结果集也很高效。某些 MySQL 版本在一个通用服务器上可以运行每秒 10 万次的查询。现在网络的带宽和延迟也比以前好了很多，所以现在运行多个小查询已经不是问题。

不建议过量使用小查询，但如果拆分成小查询性能比一个复杂查询更好时，就大胆的使用。

切分查询

对于一个很大的查询，有时可以考虑切分成多个小查询。每个小查询功能一样，只是负责返回部分数据。

删除数据就是一个很好的例子，如果直接 DELETE 一张大表，可能会一次性锁住很多数据，占满事务日志，耗尽系统资源，阻塞其他查询等。将一个大的 DELETE 切分，每次只删除一万条，是一个比较高效且对 MySQL 影响很小的做法，可以大大减少删除时锁的持有时间。

分解关联查询

很多高性能的应用会对关联查询进行分解。

可以对每张表进行一次单表查询，然后在应用程序中进行关联。

让缓存效率更高
查询分解后，减少锁的竞争
在应用层做关联，可以更好的对数据库进行拆分
查询本身效率可能会有所提升
减少冗余记录的查询

查询执行的基础

如果想优化查询，那么前提必须弄清楚 MySQL 是如何优化和执行查询的。

当我们向 MySQL 发出一个请求时，MySQL 到底做了些什么？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Gepjek6-1573983731344)(https://i.niupic.com/images/2019/10/04/_27.png)]

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

这只是一个大概的流程，实际上每一步都比想象中要复杂，查询优化器是特别复杂和难以理解的。

MySQL 客户端/服务器通信协议

不需要去理解 MySQL 内部通信的细节，了解其大致是如何工作的即可。

MySQL 客户端和服务端之间的通信是“半双工”的。在任何一个时刻，要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，两者不能同时进行。

这种协议让 MySQL 通信变得简单快速，但是也有一些限制。任何一端，都必须接收完对方发送的完整消息才能进行响应。

客户端用一个单独的数据包将查询发送给服务器，所以当查询的语句很长时，参数 MAX_ALLOWED_PACKET 就特别重要了。一旦客户端发出请求，它能做的只有等待结果响应。

查询状态

对于一个连接或者说一个线程，任何时候都有一个状态，该状态表示 MySQL 当前正在做什么。

使用 SHOW FULL PROCESSLIST 命令可以查看当前状态，Command 列表示状态。

MySQL 官方手册中对状态的解释：

Sleep 线程正在等待客户端发出新的请求。
Query 线程正在执行查询或者正在将结果返回给客户端。
Locked 在 MySQL 服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，不会体现在状态中。
Analyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table[on disk] 线程正在执行查询，并将结果集复制到一个临时表中。这种状态一般是在做 GROUP BY、文件排序、UNION。
Sorting result 线程正在对结果集排序。
Sending data 有多种情况：线程在多个状态之间传送数据、在生成结果集、在向客户端发送数据。

在一个繁忙的服务器上，可能看到大量的不正常状态，这通常表示某个地方有异常了。

查询缓存

如果查询缓存是打开的，那么 MySQL 在解析一个查询语句之前，会先检查是否命中缓存中的数据。

检查是通过一对大小写敏感的哈希查找实现的。查询和缓存中的内容即使只有一个字节不同，那也会进入下一阶段处理。

如果当前查询恰好命中了缓存，在返回结果前 MySQL 会检查一次用户权限，如果权限没问题，MySQL 就会跳过所有其他阶段，直接从缓存中读取数据返回给客户端。这种情况下，查询不会被解析，也不会生成执行计划，不会被执行，也不用和存储引擎 API 交互。

查询优化处理

如果没有查询到缓存，下一步 MySQL 将会将 SQL 转换成一个执行计划，MySQL 再依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析 SQL、预处理、优化 SQL 执行计划。

这个过程发生任何错误都可能终止查询，例如：SQL 语法错误。

语法解析器和预处理

MySQL 首先通过关键字将 SQL 语句进行解析，并生成一棵对应的“解析树”。

MySQL 解析器校验 SQL 语法是否正确，包括：使用错误的关键字、关键字顺序不对、表名列名错误冲突…。

SQL 语法校验完毕后，下一步预处理器会验证权限。

查询优化器

如果 SQL 语法和用户权限都没问题，那么将由优化器来生成执行计划。

一条查询可以有多种执行计划，返回的结果都相同，优化器的作用是找到最好的执行计划。

MySQL 使用基于成本的优化器，它会预测某种执行计划的成本，然后使用成本最低的一个。

可以通过查询当前会话的 Last_query_cost 值来得知 MySQL 计算的查询成本。

SELECT SQL_CACHE count(*) FROM person;

SHOW STATUS LIKE 'Last_query_cost';
-- Last_query_cost = 1.199000

表示 MySQL 的优化器认为大概需要做 1.199 个数据页的随机查找才能完成查询。这是根据一系列的统计信息计算得来的，包括：表或索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况。

优化器在评估成本的时候，不会考虑任何缓存，它假设读取任何数据都需要一次 I/O。

有多种原因导致优化器选择错误的执行计划：

统计信息不准确。优化器依赖于存储引擎提供的统计信息来评估成本，有的引擎提供的准确，有的则偏差很大。InnoDB 由于 MVCC 导致不能维护一个表的行数的精确统计信息。
执行计划中的成本估算不等于实际成本。即使统计信息准确，优化器给出的执行计划也可能不是最优的。例如：虽然有的计划需要读取更多的数据页，但是如果数据页是顺序读或者已经存在于内存中了，那么其实它的访问成本更小。
优化器的最优和你想的最优可能不一样。你心中的最优可能是指执行时间最短，但优化器只基于模型选择最优执行计划，有时这并不是执行最快的方式。
优化器从不考虑并发查询，这可能会影响当前查询的速度。
如果存在全文搜索的 MATCH()子句，那么优化器就会使用全文索引，即使其他索引可能执行成本更低。
优化器不会考虑不受其控制的成本。例如：执行存储过程或用户自定义函数。
优化器有时无法去估算所有可能的执行计划，所以可能会错过最优的执行计划。

MySQL 的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成最优的执行计划。

简单可分为两种：

静态优化直接对解析树进行分析，并完成优化。例如：可以通过一些简单的代数变换将 WHERE 条件转换成另一种等价形式。静态优化在第一次完成后就一直有效，可以认为是一种“编译时优化”。
动态优化与查询的上下文有关，也可能和其他因素有关。例如：WHERE 条件中的取值、索引条目对应的数据行数等。需要在每次查询时重新评估，可以认为是“运行时优化”。

MySQL 能够处理的优化类型：

重新定义表的顺序
将外连接转换成内连接
使用等价变换规则
优化 COUNT()、MIN()、MAX() 有些存储引擎如 MyISAM 会维护一个变量来记录数据行数，就不用再查询 count(*)了。B-Tree 索引由于顺序存放，首节点就是 MIN()，尾节点是 MAX()。
预估并转换成常数表达式
覆盖索引扫描
子查询优化
提前终止查询在发现已经满足查询需求时，MySQL 总是能够立刻终止查询。例如：WHERE 条件中不可能成立时。

EXPLAIN
SELECT SQL_CACHE count(*) FROM person WHERE id = '1' AND id = '2';
-- Extra = Impossible WHERE

等值传播如果两个列的值通过等式关联，MySQL 会把其中一个列的 WHERE 条件传递到另一个列上。
列表 IN()的比较在很多数据库中，IN()等同于多个 OR 子句。在 MySQL 中这点是不成立的，MySQL 会将 IN()里的数据先排序，然后通过二分法查找来判断列表中的值是否满足条件。

除此之外 MySQL 优化器还会做大量的优化，大多数时候让优化器自己去工作就可以了。

当优化器不能给出最优解时，可以再查询中添加 hint 提示，干扰优化器的行为。

数据和索引的统计信息

服务器层没有任何统计信息，MySQL 优化器在生成执行计划时，需要向存储引擎获取相应的统计信息。统计信息包括：表或索引有多少页面、索引的基数、索引的长度、索引的分布情况、数据行数等。

优化器根据这些信息来选择一个最优的执行计划。

执行计划

MySQL 生成查询的一棵指令树，然后通过存储引擎完成这棵树并返回结果。

最终的查询计划包含了重构查询的全部信息。

查看最终的执行计划

通过对查询执行 EXPLAIN EXTENDED 后，再执行 SHOW WARNINGS 可以看到优化后的具体查询：

EXPLAIN EXTENDED
SELECT count(*) FROM person;

SHOW WARNINGS;
-- /* select#1 */ select count(0) AS `count(*)` from `test`.`person`

关联查询优化器

关联查询优化是 MySQL 查询优化器很重要的一部分，它决定了多个表关联时的顺序。

不同的关联顺序查询的性能是不一样的，优化器旨在找出性能最好的顺序。

如果优化器给出的顺序不好，可以通过关键字 STRAIGHT_JOIN 重写查询。

排序优化

排序是一个代价很高的操作，应尽可能的避免对大量数据排序。

当不能使用索引生成排序结果时，MySQL 就要自己排序了。数据量小直接在内存中进行，数据量太大就只能在磁盘中进行。MySQL 将这个过程统称为：文件排序（filesort）。

如果需要排序的量小于“排序缓冲区”，MySQL 就使用内存进行快速排序。如果内存不够排序，就先将数据分块，对独立的块进行排序，然后放到磁盘中，最终将各个排好序的结果进行合并返回。

MySQL 有两种排序算法：

两次传输排序（旧版本）
单词传输排序（新版本）

当查询需要的列长度不超过参数 max_length_for_sort_data 时，使用单词传输排序，否则使用两次传输排序。

查询执行引擎

相较于查询优化阶段，查询执行阶段不是那么复杂。

MySQL 只是简单的根据执行计划给出的指令逐步执行。在执行的过程中，有大量的操作需要调用存储引擎实现的接口来完成。

返回结果

查询执行的最后一个阶段就是将结果返回给客户端。

如果查询可以被缓存，那么 MySQL 会把结果放到查询缓存中。

MySQL 将结果集返回给客户端是一个增量，逐步返回的过程。一旦 MySQL 开始生成第一条结果时，就可以开始逐步返回给客户端了。无需全部生成再一次性返回，使得服务器无需存储太多的结果，也可以让客户端第一时间获取到结果。

MySQL 查询优化器的局限性

MySQL 的“嵌套循环”并不是对每种查询都是最优的。

查询优化器的提示（hint）

如果对查询优化器的执行计划不满意，可以使用几个提示来控制最终的执行计划。

HIGH_PRIORITY 和 LOW_PRIORITY 当多个语句同时访问某一个表时，可以设置语句的优先级。
DELAYED 对于 INSERT 和 REPLACE 有效。MySQL 会将使用该提示的语句立即返回给客户端，并将插入的数据缓冲到缓冲区，然后在表空闲时批量插入。日志系统使用该提示十分有效。

INSERT DELAYED INTO person VALUES ('1','2');

STRAIGHT_JOIN 适用于 SELECT，放在两张关联表之间，用于固定关联表的顺序。
SQL_SMALL_RESULT 和 SQL_BIG_RESULT 适用于 SELECT，告诉 MySQL 返回的结果集会很小或很大。针对分组或排序时，分别在内存或磁盘中进行。
SQL_BUFFER_RESULT 告诉优化器将查询结果放入临时表，并尽快释放表锁。
SQL_CACHE 和 SQL_NO_CACHE 告诉优化器查询结果是否需要放到查询缓存中。
SQL_CALC_FOUND_ROWS 让 MySQL 返回的结果集包含更多的信息。
FOR_UPDATE 和 LOCK IN SHARE MODE 给 SELECT 语句加上排它锁和共享锁。
USE INDEX、IGNORE INDEX、FORCE INDEX USE INDEX 和 FORCE INDEX 作用基本相同，都是告诉优化器使用什么索引。FORCE INDEX 会告诉优化器全表扫描的成本会远高于索引扫描。IGNORE INDEX 会忽略索引，告诉优化器不要走索引扫描。

其他一些参数也可以控制优化器的行为：

optimizer_search_depth 控制优化器在穷举执行计划时的限度。如果查询长时间处于“Statistics”状态，可以考虑调低此参数。
optimizer_prune_level 默认是打开的，让优化器根据需要扫描的行数来决定是否跳过某些执行计划。
optimizer_switch 包含了一些开启/关闭优化器特性的标志位。

优化特性的查询

优化 COUNT()查询

COUNT()聚合函数的优化，优化之前先了解一下。

COUNT()的作用

COUNT()是一个特殊的函数，有两种不同的作用：统计某个列值的数量、统计行数。

统计列值时，要求列值不是 NULL。

为空时，就是统计行数。最简单的就是 COUNT(*)。

简单的优化

有时可以利用 MyISAM 的 COUNT(*)很快的特性，来加速一些特定条件的 COUNT()查询。

例如：使用 COUNT(*)去减去相反条件的 COUNT()查询。

使用近似值

如果不要求完全精确的 COUNT 值，可以使用近似值来代替。

EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不会真正的去执行查询，执行成本更低。

更复杂的优化

通常来说，COUNT()需要扫描大量的行才能精确获取结果，因此是很难优化的。

除了前面提到的方法，还有索引覆盖扫描。

除此之外别无他法，如果速度还不行，只能增加汇总表，或引用外部的缓存系统了。

优化关联查询

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

优化子查询

尽可能的使用关联查询代替子查询。

优化 LIMIT 分页

LIMIT 分页在数据量大时，越到后面效率越低，因为需要扫描的数据行会越来越多。

优化分页最简单的方式就是尽可能的使用覆盖索引扫描，而不是查询所有的列。

例子：

SELECT * FROM person LIMIT 10000,10;
-- 应该改成如下
SELECT a.*
FROM person a
INNER JOIN ( SELECT id FROM person LIMIT 10000,10 ) b ON a.id = b.id;

如果已知分页的边界，可以使用 BETWEEN。

使用自定义变量

用户自定义变量是一个用来存储内容的临时容器，在整个连接过程中都存在。

可以通过 SET 和 SELECT 来设置和查询：

SET @one := '1';
SELECT @one;
SELECT * FROM person WHERE id = @one;

属性和限制：

使用自定义变量查询，无法查询缓存。
不能在使用常量或标识符的地方使用，例如表名，列名。
变量只在当前连接中有效，不能和其他连接通信。
优化器可能会将变量优化掉。
赋值的顺序和时间不总是固定的。
使用未定义的变量不会有任何错误，没意识到这点容易犯错。

原文始发于微信公众号（程序员小潘）：查询性能优化

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/28620.html