《高性能MySQL》第6章 查询性能优化

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

6.2.1　是否向数据库请求了不需要的数据

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

• 查询不需要的记录
  最简单有效的解决方法就是在这样的查询后面加上LIMIT
• 总是取出全部列
  每次看到SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗

6.2.2　MySQL是否在扫描额外的记录

6.3　重构查询的方式

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

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。

6.4　查询执行的基础

6.4.1　MySQL客户端/服务器通信协议

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

• MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

6.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。

6.4.3 查询优化处理

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

• MySQL如何执行关联查询
  MySQL中“关联” (14) 一词所包含的意义比一般意义上理解的要更广泛。总的来说，MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。
  当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。
  
  伪代码
  
  查询语句及对应图解
  
  

• 执行计划
  和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。MySQL的执行计划总是如图6-4所示，是一棵左测深度优先的树。
  

6.5　MySQL查询优化器的局限性

6.5.1　关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE 条件
中包含IN() 的子查询语句。MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行。

6.5.2　UNION的限制

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

这条查询将会把actor 中的200条记录和customer 表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION 的两个子查询中分别加上一个LIMIT 20 来减少临时表中的数据：

6.5.3　索引合并优化

在前面的章节已经讨论过，在5.0和更新的版本中，当WHERE 子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

6.7　优化特定类型的查询

6.7.1　优化COUNT()查询

当我们使用COUNT（） 的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

• 简单的优化
  一个容易产生的误解就是：MyISAM的COUNT() 函数总是非常快，不过这是有前提条件的，即只有没有任何WHERE 条件的COUNT（ * ） 才非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列col 不可能为NULL值，那么MySQL内部会将COUNT（col） 表达式优化为COUNT( * )。
  有时候可以使用MyISAM在COUNT（*） 全表非常快的这个特性，来加速一些特定条件的COUNT() 的查询。
• 使用近似值
  有时候某些业务场景并不要求完全精确的COUNT 值，此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN 并不需要真正地去执行查询，所以成本很低。

6.7.2　优化关联查询

6.7.3　优化子查询

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

6.7.4　优化GROUP BY 和DISTINCT

6.7.5　优化LIMIT分页

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

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列

有时候也可以将LIMIT 查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：