[MySQL][6][查询性能优化]

2020-03-02 MySQL MySQL Comments

第 6 章查询性能优化

第 6 章查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进，一个不落。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

6.1 为什么查询速度会慢

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

査询的生命周期大致可以按照顺序来看

从客户端，到服务器
然后在服务器上进行解析
生成执行计划
执行
并返回结果给客户端。

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

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作。

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

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

大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

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

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

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。

这里有几种典型情况

查询不需要的记录：例如在新闻网站中取出100条记录但是只是在页面上显示前面10条，这时要使用limit
多表关联时返回全部列：我们应该只取出需要的列
总是取出全部列
- 每次看到SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？取出全部列，会让优化器无法完成索引覆盖扫描这类优化，
- 查询返回超过需要的数据也不总是坏事，这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性。
重复查询相同的数据
- 例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

6.2.2 MySQL是否在扫描额外的记录

对于MySQL，最简单的衡量查询开销的三个指标如下

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

这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

6.2.2.1 响应时间

响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。

6.2.2.2 扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大

6.2.2.3 扫描的行数和访问类型

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

如果査询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
改变库表结构。例如使用单独的汇总表
重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询

6.3 重构查询的方式

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

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

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、査询解析和优化是一件代价很高的事情。

但是这样的想法对于MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。

6.3.2 切分耗时的大查询

有时候对于一个大查询我们需要分而治之，将大查询切分成小查询，每个查询功能完全一样，只扫描一小部分行，每次只返回一小部分查询结果。

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

例如下面的方法采用一次删除10000行来切分删除操作

rows_affected = 0
do {
    rows_affected = do_query("DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
}(while rows_affected > 0)

6.3.3 分解关联查询（这个思想挺厉害的）

很多高性能的应用都会对关联查询进行分解。可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

举例说明，例如下面的查询

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag_id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

那个这个复杂的关联查询可以被分解为3个单表查询，可以达到与上面的查询相同的效果

SELECT * FROM tag WHERE tag="mysql"
//此时查询出tag="mysql"的项，假设只有一个且tag_id=1234
SELECT * FROM tag_post WHERE tag_id=1234;
//tag_post是tag表和post表的关联表，从其中查找到tag_id=1234的所有项，假设这些项的post_id为(123, 456, 567, 1024, 2048)
SELECT * FROM post WHERE post_id in (123, 456, 567, 1024, 2048)

用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。但MySQL的查询缓存对关联缓存的支持并不太好，如果关联中的某个表发生了变化，那么就无法使用查询缓存了
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多

6.4 查询执行的基础

下图显示了MySQL执行一个查询的过程。

具体步骤如下

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

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

MySQL客户端和服务器之间的通信协议是半双工的，这意味着在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

这种协议让MySQL通信简单快速，但也意味着没法进行流量控制。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。

多数连接MySQL的客户端库函数都可以从服务器获得全部结果集并缓存到内存里，还可以从服务器逐行获取需要的数据。默认一般是客户端获得全部结果集并缓存到内存中。MySQL服务器通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

当使用多数连接MySQL的客户端库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个客户端库函数的缓存获取数据。

对于一个MySQL连接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。使用SHOW FULL PROCESSLIST命令能查看当前的状态

Sleep：线程正在等待客户端发送新的请求。
Query：线程正在执行查询或者正在将结果发送给客户端
Locked：在MySQL服务器层，该线程正在等待表锁。
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中
Sorting result：线程正在对结果集进行排序
Sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

6.4.2 查询缓存

MySQL会优先检查这个查询是否命中査询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。之后MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。

6.4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，这包括多个子阶段：解析SQL、预处理、优化SQL、生成执行计划。

6.4.3.1 语法解析器和处理器

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

然后，预处理器根据一些MySQL规则检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义

下一步预处理器会验证权限。

6.4.3.2 查询优化器

现在语法树被认为是合法的了，然后优化器要将语法树转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本并选择其中成本最小的一个。

优化策略可以简单地分为两种

静态优化可以直接对解析树进行分析，并完成优化。静态优化不依赖于特别的运行状况或者参数值。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种编译时优化。
动态优化则和查询的上下文有关，需要在每次查询的时候都重新评估，可以认为这是运行时优化。

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

重新定义关联表的顺序
- 数据表的关联并不总是按照在查询中指定的顺序进行。
- 优化器会试图寻找一种最好的关联顺序
将外连接转化为内连接
- 并不是所有的OUTER JOIN语句都必须以外连接的方式执行。例如WHERE条件、库表结构都可能会让外连接****等价于一个内连接。
- MySQL能够识别这点并重写查询，让其可以调整关联顺序。
使用等价变换规则
- MySQL可以使用一些等价变换来简化并规范表达式。
- 它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
优化COUNT()、MIN()和MAX()
- 索引通常可以帮助MySQL优化这类表达式。
- 要找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。
预估并转化表达式为常数
- 优化器会试图将一些表达式转化为常数
覆盖索引扫描
- 当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行
子查询优化
提前终止查询
- 在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。
- 一个典型的例子就是当使用了LIMIT子句的时候。
- 此外，假如发现了一个不成立的条件，这时MySQL可以立刻返回一个空结果。
等值传播
列表IN()的比较

最后，我们要明白一点，不要以为自己比优化器聪明

6.4.3.3 数据和索引的统计信息

服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息

6.4.3.4 MySQL如何执行关联查询

MySQL认为任何一个查询都是一次关联，并不仅仅是一个查询需要到两个表匹配才叫关联。

MySQL关联执行的策略很简单：

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

上面的描述极其拗口，难以理解，下面用伪代码来说明

假设我们要执行下面这个关联查询

1
2
3

SELECT tbl1.col1, tbl2.col2
FROM tbl1 INNER JOIN tbl2 USING(col3)
WHERE tbl1.col1 IN(5, 6)

这个查询会被以下面伪代码的方式执行

outer_iter = iterator over tbl1 where col1 IN(5, 6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    while inner_row
        output[ outer_row.col1, inner_row.col2]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

上面这种查询的泳道图如下

上面的执行计划对于单表查询和多表关联查询都适用，如果是一个单表查询，那么只需完成上面外层的基本操作。

6.4.3.5 执行计划

MySQL并不会生成查询字节码来执行查询。 MySQL生成査询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。

MySQL的执行计划总是如下图所示，是一棵左侧深度优先的树。

6.4.3.6 关联查询优化器

通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

当关联顺序的选择非常多的时候，优化器不可能逐一评估每一种关联顺序的成本。这时，优化器选择使用贪婪搜索的方式查找最优的关联顺序。

6.4.3.7 排序优化

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程统一称为文件排序（filesort）

如果需要排序的数据量小于排序缓冲区， MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。

MySQL有如下两种排序算法：

两次传输排序
- 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
- 这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的需要的其他行结构。
- 这样做的优点是，在排序的时候存储尽可能少的数据，这就让排序缓冲区中可能容纳尽可能多的行数进行排序。
单次传输排序
- 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。
- 相比两次传输排序，这个算法只需要一次顺序I/O读取所有的数据，而无须任何的随机IO。
- 缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何作用的。

两种算法都有各自最好和最糟的场景。当查询需要所有列的总长度不超过参数max_length_for_sort_data时， MySQL使用单次传输排序，可以通过调整这个参数来影响MySQL排序算法的选择。

在关联查询的时候如果需要排序， MySQL会分两种情况来处理这样的文件排序。

如果ORDER BY子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。
除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。

6.4.4 查询执行引擎

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

在执行时，大量的操作都需要调用存储引擎API。存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。这种简单的接口模式，让MySQL的存储引擎插件式架构成为可能，但是正如前面的讨论，也给优化器带来了一定的限制。

6.4.5 返回结果给客户端

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

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

MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器开始生成第一条结果时， MySQL就可以开始向客户端逐步返回结果集了。这样处理有两个好处

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

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送

6.5 MySQL查询优化器的局限性

略

6.6 查询优化器的提示

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。

6.6.1 HIGH_PRIORITY 和 LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。

这两个提示只对使用表锁的存储引擎有效，千万不要在 InnoDB或者其他有细粒度锁机制和并发控制的引擎中使用。

6.6.2 DELAYED

这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回，但不会立刻执行语句，而是将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。

6.6.3 STRAIGHT_JOIN

这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

6.6.4 SQL_SMALL_RESULT和SQL_BIG_RESULT

SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

6.6.5 SQL_CACHE和SQL_NO_CACHE

这个结果集是否应该缓存在查询缓存中

6.6.6 SQL_CALC_FOUND_ROWS

查询中加上该提示MySQL会计算除去LIMIT子句后这个查询要返回的结果集的总数

这两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合査询条件的数据行加锁。

唯一内置的支持这两个提示的引擎就是InnoDB。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描

6.6.8 USE INDEX、IGNORE INDEX和FORCE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录

6.7 优化特定类型的查询

6.7.1 优化COUNT()查询

COUNT()主要有两个用处

COUNT(*)用来统计所有符合条件的行数
COUNT(col1)用来统计所有col1不为空的行数

MyISAM在没有任何WHERE条件的COUNT()才非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。

通常情况下，要计算COUNT()都需要扫描全表

在不需要精确的COUNT()值的时候，可以使用EXPLAIN出来的优化器估算的行数来作为近似值。

或者可以参照上一章的做法使用汇总表

6.7.2 优化关联查询

要确保ON或者USING子句中的列上有索引

6.7.3 优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替。

6.7.4 优化GROUP BY和DISTINCT

在很多场景下， MySQL都使用同样的办法优化这两种查询，事实上， MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。

6.7.5 优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

在偏移量非常大的时候，可能效率会比较低。这样的查询可能需要访问大半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

下面有几种优化策略

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。
如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

6.7.6 优化SQL_CALC_FOUND_ROWS

分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FDUND_ROWS提示这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

下面有两种解决方法

一个更好的设计是将具体的页数换成下一页按钮，假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么我们就显示下一页按钮，否则就说明没有更多的数据，也就无须显示下一页按钮了。
另一种做法是先获取并缓存较多的数据，例如，缓存1000条，然后每次分页都从这个缓存中获取。
也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值

6.7.7 优化UNION查询

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字， MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。

6.7.8 使用用户自定义变量

略

本文链接： https://t0ugh.biz/2020/03/02/MySQL-6-查询性能优化/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！