《高性能MySQL》---- 查询性能优化

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

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

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

二、重构查询的方式

1. 一个复杂查询还是多个简单查询：设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。
2. 切分查询：有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
3. 分解关联查询：很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

三、SQL查询执行的流程

根据图6-1，我们可以看到当向MySQL发送一个请求的时候，MysQL到底做了些什么：

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

四、优化特定类型的查询

1、优化COUNT()查询

COUNT ()聚合函数，在网上随便搜索一下就能看到很多错误的理解，可能比我们想象的多得多。

COUNT()的作用：COUNT ()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。

• 在统计列值时要求列值是非空的(不统计NULL)，如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数，因为很多人对NULL理解有问题，所以这里很容易产生误解。

• COUNT ()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候，这种情况下通配符 * 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。

2、优化关联查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。

3、优化子查询IN()

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

例如，我们希望找到Sakila数据库中，演员Penelope Guiness (他的actor-id为1)参演过的所有影片信息。很自然的，我们会按照下面的方式用子查询实现：

因为MySQL对IN()列表中的选项有专门的优化策略，我们会认为上面的查询会这样执行：

很不幸， MySQL不是这样做的。通过EXPLAIN我们可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY) (可以使用EXPLAIN EXTENDED 和 在命令行中执行 show warnings; 来查看这个查询被改写成了什么样子，具体可以百度一下)：

根据EXPLAIN的输出我们可以看到， MySQL先选择对film表进行全表扫描，然后根据返回的flm-id逐个执行子查询。如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，但是如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕。

解决方法：

1. 改使用inner join 关联查询
   

2. 使用函数GROUP-CONCAT()在IN()中构造一个由逗号分隔的列表。有时这比上面的使用关联改写更快。

3. 改用EXISTS()查询，建议使用此方法。
   

4、优化GROUP BY 和 DISTINCT

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

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

在分组查询的SELECT中直接使用非分组列通常都不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。

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

5、优化LIMIT分页

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候生，例如可能是LIMIT10000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。

• 使用索引覆盖解决：
  优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。

  考虑下面的查询：
  
  如果这个表非常大，那么这个查询最好改写成下面的样子：
  
  这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

• 先确定ID范围解决
  有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。

  例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为:
  
  LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

6、取结果集总数

有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值(实际上Google的搜索结果，总数也是个近似值)。当需要精确结果的时候，再单独使用COUNT(*)来满足需求。

7、优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。

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