高性能MySQL(4)——查询性能优化

2022-12-01 21:08:28 浏览数 (1)

査询优化、索引优化、库表结构优化需要齐头并进，一个不落。

一、为什么查询速度为变慢

在尝试编写快速的查询之前,需要清楚一点,真正重要是响应时间。如果把查询看作是一个任务，那么他由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行的次数，要么让子任务运行得更快。

MySQL在执行查询的时候有哪些子任务。哪些子任务运行的速度很慢,这里很难给出完整的列表，通常来说查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间，根据引擎不同，可能还会产生大量的上下文切换以及系统调用。

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

二、慢查询基础:优化数据访问

査询性能低下最基本的原因是访问的数据太多。

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

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

有些査询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销,另外也会消耗应用服务器的CPU 和内存资源。

2.2、MySQL是否在扫描额外的记录
最简单衡量查询开销的三个指标
  • 响应时间：分为服务时间和排队时间
    • 服务时间：是指数据库处理这个查询真正花了多长时间
    • 排队时间：是指服务器因为等待某些资源而没有真正执行查询的时间——坑内是等I/O操作完成，也可能使行锁等等
  • 扫描的行数
  • 返回的行数

在EXPLAIN语句中的type列反应了访问的类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是小到大。你不需要记住这些访问类型，但是要明白扫描表，扫描索引，范围访问和单值访问的概念。如果查询没有办法找到合适的访问类型，那么最好的办法通常就是增加一个合适的索引。

一般MySQL能够使用如下三种应用WHERE条件，从好到坏依次为：
  • 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
  • 使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须在回表查询记录。
  • 从数据表中返回数据，然后过滤不满足条件的记录。这是在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
如果说发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
  • 使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果
  • 改变库表结构。例如使用单独的汇总表
  • 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询

三、重构查询方式

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端 就慢得多了。在其他条件都相同的时候，使用尽可能少的査询当然是更好的。但是有时候, 将一个大査询分解为多个小査询是很有必要的。

3.2 切分查询：将大查询切分成小查询，每个查询完全一样，只完成一小部分，每次只返回一小部分查询结果

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

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

3.3 分解关联查询

对每一个表进行一次单表查询，然后再应用程序中进行关联，例如

代码语言：javascript复制
mysql> 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';

可以分解成下面的语句来代替

代码语言：javascript复制
mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234；
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904)；
使用分解关联查询的方式重构查询有如下的优化：
  • 让缓存的效率更高。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易的对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。
  • 可以减少冗余记录的查询。
  • 这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

四、查询执行的基础

查询执行路径

步骤：

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

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

4.2.2、查询状态：对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，表示MySQL当前在做什么。我们使用最简单的SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态）来查询。下面将这些状态列出来，并做一个简单的解释：

  • Sleep：线程正在等待客户端发送新的请求。
  • Query：线程正在执行查询或者正在将结果发送给客户端。
  • Locked：在MySQL服务器层，该线程正在等待表锁。
  • Analyzing and statistics : 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
  • Coping to tmp table [on disk]：线程正在执行查询，并且将其结果都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有"on disk"标记，那表示MySQL正在讲一个内存临时表放到磁盘上。
  • Sorting result：线程正在对结果集进行排序。
  • Sending data：这表示多种情况：线程可能在对多个状态之间传输数据，或者而在生成结果集，或者在向客户端返回数据。
4.2 查询缓存

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

这个检查是通过一个对大小写敏感的哈希查找的。查询和缓存中的查询即使只有一个不同，也不会匹配缓存结果。如果命中缓存，那么在但会结果前MySQL会检查一次用户权限，有权限则跳过其他步骤直接返回数据

4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成执行计划，MySQL再依照这个执行计划和存储引擎进行交互。

4.3.1 语法解析器和预处理

MySQL解析器将使用MySQL语法规则验证和解析查询。例如验证是否使用错误的关键字、关键字顺序、引号前后是否匹配等，预处理器则根据一些MySQL 规则进一步解析树是否合法，例如检查数据表和数据列是否存在，解析名字和别名是否有歧义等

4.3.2 查询优化器

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

有很多中原因导致MySQL优化器选择错误的计划，如下所示：

统计信息不准确：MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息偏差有点大，例如InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息

  • 执行计划中的成本估算不等于实际的操作成本
  • MySQL的最优可能和你想的最优不一样
  • MySQL从不考虑其他并发执行的查询
  • MySQL也并不是任何时候都是基于成本的优化
  • MySQL不会考虑不受其控制的操作成本。例如执行存储过程或者用户自定义函数的成本
  • 优化器有时间无法估算所有可能的执行计划

MySQL的查询优化器使用很多策略来生成一个最优的执行计划。

优化策略可以简单的分为两种
  • 静态优化： 静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过简单的代数变化将WHERE条件转换成另外一种等价形式，静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会变化。可以认为是一种”编译时优化“
  • 动态优化：和查询的上下文有关，也可能和其他因素有关，例如WHERE中取值、索引中条目对应的数据行数等。这需要在每次查询的时候重新评估，可以让那位u是”运行时优化“。
MySQL能够处理的优化类型（部分）：
  • 重新定义关联表顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化COUNT() 、MIN() 、 MAX()
  • 预估并转换为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较
4.3.3 数据和索引的统计信息

在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息，有的引擎根本不存储任何统计信息，例如Archive引擎。

因为服务器层没有任何统计信息，所有MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息，优化器根据这些信息来选择一个最优的执行计划。

4.3.4 MySQL如何执行关联查询

MySQL中“关联”认为任何一个查询都是一次“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联。素以在MySQL中，每一个查询，每一个片段（包括子查询，甚至于单表的SELECT）都可能是关联。

MySQL关联查询的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在要给表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表的行，返回查询中需要的各个列。

4.3.5 执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一颗指令树，然后通过存储引擎执行完成这颗树并返回结果

4.3.6 关联查询优化器

如果优化器给出的并不是最优的关联顺序，这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为最优的关联顺序执行——不过老实说，人的判断很难那么精准。绝大多数时候，优化器做出的选择都比普通人的判断更精准。

如果超过N个表的关联，那么需要检查N的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间‘，当搜索空间非常大的时候，优化器选择使用”贪婪“搜索方式查找”最优’的关联顺序。当关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了。

4.3.7 排序优化
  • 排序优化：无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。尽量通过索引进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己
  • 进行排序，如果数据量小则在内存中进行，如果数量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序，即使完全是内存排序不需要任何磁盘文件时也是如此。
MySQL有如下两种排序算法：
  • 两次传输排序(旧版本使用)：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。需要进行两次传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读 取排序列进行排序后的所有记录。这回产生大量的随机IO。
  • 单次传输排序(新版本使用)：先读取查询所需要的所有列，然后在根据给定列进行排序，最后直接返回排序结果。效率更高，但占用内存更大。 如果查询中有LIMIT的话，LIMIT也会在排序之后应用的，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然后非常大。貌似5.6版本有所改进，会先抛弃不满足条件的记录，然后再进行排序。
4.4 查询执行引擎

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

4.5 返回结果给客户端

即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如查询影响到的行数。如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到缓存中。

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

五、 优化特定类型的查询

5.1、优化COUNT()查询

​ 1). COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值的时候要求列值是非空的(不统计NULL)。如果COUNT()的括号中指定了列或者列的表达式，则

​ 统计的就是这个表达式有值的结果数。最简单的就是我们使用count(*)的时候，这种情况下通配符*并不会向我们猜想的那样扩展所有的行，实际上，它会忽略所有的值而直接统计所有的行数。

​ 2). 使用近似值：有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。

​ 3). 更复杂的优化：覆盖索引，增加汇总表等。**

5.2 优化关联查询：

​ 1). 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用到列C关联的时候，如果优化器关联顺序是B、A，那就不需要在B表的对应列上建立索引。没有用到的索引只会

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

5.3 优化子查询：

关于优化子查询我们给出的最重要的优化建议就是尽可能使用关联查询代替，至少当前MySQL版本需要这样。

5.4 优化GROUP BY和DISTINCT：

​ 1). 它们都可以使用索引来优化，这也是最有效的方法。

​ 2). 在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT和

​ SQL_SMALL_RESULT来让优化器按你希望的方式运行。

​ 3). 如果需要对关联查询分组(GROUP BY)，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率比其他列更高。**

​ 4). 如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY 子句的时候，结果集会自动按照分组的列进行排序。如果不关心结果集的顺序，而这中默认排序又导致了需要文件排序，则可以使用

​ ORDER BY NULL，让MySQL文件不再进行排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按照需要的方向排序。

​ 5). 优化GROUP BY WITH ROLLUP：分组查询的一个变种思想就是要求MySQL对返回的分组结果再做一次超级聚合。最好的办法尽可能的将WITH ROLLUP 功能转移到应用程序中处理。**

5.5 优化LIMIT分页：

​ 1). 使用索引

​ 2). 要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

​ 3). 尽肯能的使用索引覆盖

​ 4). 延迟关联

​ 5). 有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描找到对应的结果。

​ 6). 其他优化办法还包括使用预先计算的汇总表，或者关联一个冗余表，冗余表只包含主键列和需要做排序的数据列。

5.6 优化SQL_CALC_FOUND_ROWS：

分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示(hint)，这样就可以获得去掉LIMIT以满足条件的行数，因此可以作为分页的总数。

​ 用业务的手段解决：下一页，获取更多数据等。

5.7 优化UNION查询：

​ 1). MySQL总是通过创建填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各个子查询中，以

​ 便优化器可以充分利用这些条件进行优化。

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

​ 即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是经结果放入临时表，然后再读出，再返回给客户端。**

5.8 静态查询分析：

Percona Toolkit中的pt-query-advisor 能够解析查询日志、分析查询模式，然后再给出所有可能存在的潜在问题的查询，并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康

​ 检查，它能检测出很多问题。

六、总结

如果把创建高性能应用程序比作是一个环环相扣的“难题”，除了前面介绍的schema. 索引和査询语句设计之外，査询优化应该是解开“难题”的最后一步了。要想写一个好 的査询，你必须要理解schema设计、索引设计等，反之亦然。

理解査询是如何被执行的以及时间都消耗在哪些地方，这依然是前面我们介绍的响应时 间的一部分。再加上一些诸如解析和优化过程的知识，就可以更进一步地理解上一章讨 论的MySQL如何访问表和索引的内容了。这也从另一个维度帮助读者理解MySQL在 访问表和索引时査询和索引的关系。

参考：

《高性能 MySQL 第三版》

云数据库SQLServer 存储 数据库 sql 缓存

0 人点赞