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

1. 查询性能低下最基本的原因是访问数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。通过下面两个步骤来分析总是有效的：

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

2. 取出全部列（SELECT *）操作，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。

3. 对于MySQL，最简单的衡量查询开销的三个指标是：

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

4. “Using where” 表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。

5. 一般MySQL能够使用如下两种方式应用WHERE条件，从好到坏依次是：

   • 在索引中使用where条件来过滤不匹配的记录（extra列显示Using index）。这是存储引擎层完成的。

   • 从数据表中返回数据，然后过滤不满足条件的记录（在extra列中出现Using where）。这在MySQL服务层完成的，MySQL需要先从数据表中读出记录然后过滤。

6. 如果发现查询需要扫描大量的数据，但只返回少数的行，那么通常可以尝试如下方式进行优化：

   • 使用索引覆盖扫描。即，把所有需要用的列都放到索引中，这样存储引擎无需回表即可获取对应行的数据。
   • 改变库表结构。如添加汇总表等。
   • 优化SQL语句。

二、重构查询的方式

1. 很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。将关联查询进行分解会有如下优势：

   • 让MySQL缓存更高效
   • 将查询分解后，执行单个查询可以减少锁的竞争。
   • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
   • 查询本身效率也会提升。
   • 可以减少冗余记录的查询。

三、查询执行的基础

1. 执行路径：

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

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

3. 【关注点】很多数据库系统中，IN语句完全等同于多个OR条件的子句。但是在MySQL中这点是不成立的。MySQL将IN()列表中的数据先进行排序，然后通过将数据行中对应的列在排好序的列表中进行二分查找的方式来确定值是否满足条件。这是一个O(logn)复杂度的操作。若等价的转换成OR查询的复杂度为O(n)，因此对于IN列表中有大量取值的时候，MySQL的处理速度将会更快。

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

5. 对于Union查询，MySQL先将一系列的单表查询结果放到一个临时表中，然后再重新读出临时表数据来完成Union查询。（注意，临时表是没有索引的，所以后续对临时表的操作都是全表扫描）

6. MySQL对于任何关联都执行嵌套循环关联操作。即，多个for循环嵌套（回顾第五章最后的转载）。

7. MySQL总是从一个表开始一直嵌套循环、回溯完成所有表的关联，所以MySQL的执行计划总是如下所示，是一个左侧深度优先的树。
   

   image.png

8. 无论如何，排序都是一个成本很高的操作，所以从性能的角度讲，应该尽量避免排序或者尽可能避免对大量数据进行排序。

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

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

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

    • 如果order by 子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就会进行文件排序。此时通过explain在extra列将会看到“Using filesort”。
    • 此外的其他情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。此时通过explain在extra列中将会看到“Using temporary; Using filesort”。【Using temporary表示使用了临时表】

12. 在MySQL5.6以后的版本中，MySQL对只需要返回部分排序结果的查询进行了优化，如果使用了limit子句，那么MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

13. 一个容易产生的误解是：MyISAM的count()函数总是非常快，不过这是有前提条件的：

    • 只有没有任何where条件的count(*)才非常快，因此此时无需实际去去计算表的行数，可以直接利用存储引擎的特性直接获取这个值。
    • 当存在where子句的时候，MyISAM的count()和其他存储引擎没有任何不同，就不再有神话版的速度了。

14. 优化关联查询：

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

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

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

15. 优化group by和distinct。MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的方式。

16. 当无法使用索引的时候，group by使用两种策略来完成：使用临时表或者使用文件排序来做分组。

17. 如果需要对关联查询做分组，并且是按照某个查找表中的某个列进行分组，那么通常 采用查找表的标识列分组的效率会比其他列更高。（参考第五章转载部分）

18. 不是所有的关联语句的分组查询都可以写成在select中直接使用非分组列的形式的。可以在服务器上设置SQL_MODE来禁止这样的写法。但一定要清楚，select后面出现的非分组列一定是直接依赖分组列，并且在每个分组内的值唯一。

19.单纯使用 limit 10000,20 这样的查询，MySQL在执行时需要扫描10020条记录，然后只返回最后20条。因此偏移量非常大的时候，性能很低。因此在查询条件中尽量使用索引列来减少扫描的行数。

20. MySQL总是通过创建并填充临时表的方式来执行union查询。（[关于union查询]https://www.w3school.com.cn/sql/sql_union.asp
    ）