查询性能优化

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

查询性能低下最基本的原因是访问的数据太多。确定应用程序是否在检索大量超过需要的数据，确认MySQL服务器层是否在分析大量超过需求的数据行。

1.1是否请求不需要的数据

查询不需要的记录：最常用的优化，查询后面加上limit；

多表关联时返回全部列；

总是取出全部列；

重复查询相同的数据；

1.2MySQL是否扫描额外的记录

衡量查询开销的三个指标：响应时间，扫描的行数，返回的行数；

索引让MySQL以最高效、扫描最少行数的方式找到需要的记录；

二、重构查询的方式

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

MySQL从设计上，让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现在网络速度比以前快很多，所以运行多个小查询已经不是大问题了。

2.2切分查询

对于一个大查询，我们需要“分而治之”，将大查询切分成小查询。例如，定期清除就数据，通过limit行数，拆分多次进行删除，避免一次锁很多数据，占满整个事务日志，耗尽系统资源，阻塞其他查询。

2.3分解关联查询

为什么要这么做：

①缓存更加高效：对于关联查询而言，只要关联的任何一个表发生变化，缓存失效；单个查询可以更多的避免这种情况；

②单个查询可以减少锁的竞争；

③查询本身效率也可能提升：使用in()查询比按照id查询会更高效；

④可以减少冗余记录的查询：关联查询时，可能需要重复的访问一部分数据；

三、查询执行的基础

①客户端发送一条查询给服务器；

②服务器先检查缓存，如果命中了缓存，则直接返回缓存数据，否则进入下一流程；

③服务器进行SQL解析和预处理，再由优化器生成对应的执行计划；

④MySQL根据执行计划， 调用存储引擎的API来执行查询；

⑤将结果返回客户端；

3.1MySQL服务器与客户端的通讯协议

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

客户端从服务器取数据时，看起来是一个拉的过程，实际上是MySQL向客户端推送数据的过程。多数连接MySQL的库函数都可以获取全部结果集并缓存在内存中，还可以逐行获取数据。默认是全部缓存，所以获取数据其实是从缓存中。这通常没有问题，但如果是一个特别大的结果集，就会占用很大内存。可以通过设置mysql_use_result的值为1，使得结果不缓存。

对于每一个MySQL连接，或者说一个线程，任何时刻都有一个状态，通过简单的命令show full processList，可以很快了解。

3.2查询缓存

3.3查询优化处理

查询生命周期的下一步是将一个SQL转化成一个执行计划，MySQL在根据这个计划和存储引擎交互。

3.3.1语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成对应的解析树。

3.3.2查询优化器

通过优化器，将解析树转化为执行计划，并选择最优。

优化类型：

①重新定义关联表的顺序；

②将外连接转化为内连接；

③使用等价变换规则；

④优化count(),max(),min()；

⑤预估并转化为常数表达式；

⑥覆盖索引扫描：直接索引数据返回，不需要查表；

⑦子查询优化；

⑧提前终止查询：使用limit时；

⑨等值传播；

⑩列表in()的比较：MySQL将in()列表中的数据先进行排序，然后通过二分法查找，这是一个O(log n)的时间复杂度；

MySQL如何执行关联查询

MySQL认为任何一个查询都是一次“关联”，并不单指表跟表，每一个查询、每一个字段都可能是关联。

MySQL执行关联的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次执行。然后根据各个表匹配的行，寻找所需字段，如果没有则返回上一张表，以此类推。

执行计划

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

关联查询优化器

选择更少嵌套循环和回溯操作的关联表顺序。

排序优化

排序是一个成本很高的操作，应该避免大量数据排序。

当不同引用索引进行排序时，MySQL需要自己排序。如果数据量小，可以在内存中进行，称为快速排序；如果数据量大，则需要使用磁盘（将数据切割成小块，小块使用快速排序，结果保存至磁盘后，merge），称为文件排序（filesort）。

MySQL的两种排序算法：

两次传输排序（旧版本使用）

读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行；

单次传输排序（新版本使用）

先读取查询所需要的所有列，然后再根据定列进行排序，最后直接返回排序结果；

3.4查询执行引擎

根据执行计划，完成整个查询。

3.5返回结果给客户端

MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联，生成第一个结果，MySQL就开始xiang客户端逐步返回结果集。这样的好处是，客户端无需存储太多的结果，消耗太多内存。客户端也能第一时间获得结果。

四、MySQL查询优化器的局限性

4.1关联子查询

慎用关联子查询，特别是in()+子查询，MySQL查询优化器可能会将外层查询条件压入子查询中，导致外层表的全表扫描。当然，也不是说关联子查询效率就一定低，用测试结果说话。

4.2UNION的限制

有时，MySQL无法将限制条件从外层内推到内层，导致能够限制部分返回结果的条件无法应用。

4.3索引合并优化

当WHERE子句中包含多个复杂条件的时候，MySQL能够访问当个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4.4等值传递

如果等值传递的列表过大，会导致优化和执行变慢。

五、查询优化器的提示

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

HIGH_PRIORITY和LOW_PRIORITY
#HIGH_PRIORITY会将select语句重新调度到所有正在等待表锁以变修改数据的语句之前；
#LOW_PRIORITY则正好相反，会让语句一直处于等待状态。

DELAYED
#这个提示对insert和replace有效。MySQL使用该题是会立刻返回，并将插入的语句放入缓存区，在表空闲时，将数据批量写入；

六、优化特定类型的查询

6.1优化count()查询

count()是一个特殊的函数，有两个作用：可以统计某个列值的数量，也可以统计行数。在统计时，要求列值是非空的。

如果是统计行数，使用count(*)，性能最佳。他会忽略所有的列而直接统计所有的行。

6.2优化关联查询

①确保关联条件上有索引。一般而言，除非有其他理由，只需要在关联顺序中的第二个表的相应列上创建索引；

②order by和group by只涉及一个表中的列，才可能使用索引来优化这个过程；

6.3优化子查询

尽可能使用关联

6.4优化group by和distinct

使用索引，是最有效的优化方法，无法使用索引时，group by使用两种策略来完成：使用临时表或者文件排序来分组；

6.5优化limit分页

在偏移量非常大时，例如limit 1000，20这样的操作，MySQL需要查询1020条记录，只返回最后20条。要优化这种查询，要么限制分页的数量，要么优化大偏移量的性能。

select film_id,description from film order by title limit 50,5;
#通过“延迟关联”，让MySQL排序时减少扫描的页面，然后再回表查询所需要的列，这也可以使用再limit上
select film_id,description from film inner join (select file_id from film order by title limit 50,5) as lim using(film_id);

select * from rental order by rental_id desc limit 20;
#通过记录上一次分页的id，来减少扫描大量无用的数据
select * from rental where id <16000 order by rental_id desc limit 20;

6.6优化union查询

MySQL总是通过创建临时表的方式来执行UNION查询，通过使用UNION ALL，减少去重操作。

七、总结

如果把创建高性能应用程序比作一个环环相扣的“难题”，除了前面介绍的schema，索引和查询语句设计外，查询优化应该是解开难题的最后一步。优化通常需要三管齐下：不做、少做、快速地做。