MySQL查询性能优化

优化查询、索引优化、库表结构设计三者其实需要齐头并进，一个不能落下。在获得编写MySQL查询经验的同时，也应该学习如何设计为高效的查询设计表和索引。同样的，也可以学习到在优化库表结构时会影响到哪些类型的查询。

01、为什么查询速度会慢？

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

2、查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中，“执行”可以认为是整个生命周期中最重要的阶段，其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

3、很多查询之所以会消耗大量时间，可能是基于以下原因：不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是要减少和消除这些操作所花费的时间。

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

1、查询性能低下最根本的原因是访问的数据太多。

2、确认是否向数据库请求了不需要的数据。比如：

（1）查询不需要的记录（最好在查询后面加上LIMIT）；

（2）多表关联时返回全列（不要SELECT *，只取需要的列）；

（3）总是取出全部列（与简化开发做一个平衡性思考）；

（4）查询重复相同的数据（可以缓存起来）。

3、确认MySQL是否在扫描额外的记录。

4、对于MySQL，最简单的衡量查询开销的三个指标如下：响应时间、扫描的行数、返回的行数。

5、如果发现查询需要扫描大量的数据但只返回了少数的行，可以尝试以下技巧：

（1）使用索引覆盖扫描，把所有需要用的列都放在索引中。

（2）改变库表结构。比如使用单独的汇总表。

（3）重写这个复杂的查询。

03、MySQL查询优化的局限性

1、优化关联子查询：（1）使用内连接代替IN()；（2）使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表。

2、不需要听取哪些关于子查询的“绝对真理”，比如别用关联子查询。应该用测试来验证子查询的执行计划和响应时间的假设。

3、如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果的话，就需要在UNION的各个子句中分别使用这些子句。

4、MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

5、MySQL无法利用多核特性并行执行查询。

6、MySQL不支持哈希关联，MySQL所有关联都是嵌套循环关联。

7、MySQL并不支持松散索引扫描。

04、优化特定类型的查询

1、COUNT()的作用：可以统计某个列值的数量或行数；统计结果集的行数。

2、COUNT(*)会忽略所有的列而直接统计所有的行数。

3、对于MyISAM，没有任何WHERE条件的COUNT(*)才会非常快。

4、COUNT(*)可以使用近似值。

5、“快速、精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

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

7、确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列。

8、子查询优化最重要的优化建议就是尽可能使用关联查询代替。

9、可以通过提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。

10、如果需要对关联查询做分组（GROUP BY），并且是按照查询找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

11、尽可能地将WITH ROLLUP功能转移到应用程序中处理。

12、优化分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描。

13、优化SQL_CALC_FOUND_ROWS：（1）将具体的页数换成“下一页”按钮，（2）先获取并缓存较多的数据，（3）使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值。

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