高性能MySQL - 查询性能优化

本文来源 《高性能MySQL》

一、如何检查一个查询的好坏

查询性能低下最基本的原因是访问的数据太多。造成低效查询的原因有以下两个：

1. 检索大量不需要的数据。

2. MySQL服务层在分析大量超过需要的数据行。

1. 检索大量不需要的数据

1.1 查询不需要的记录

一个常见的错误是误以为MySQL会只返回需要的数据，实际上MySQL是先返回全部结果集再进行运算。 e.g. 如果只需要在页面显示10个数据，最简单有效的方法是加LIMIT，而不是查出整个结果集再抛弃。

1.2 返回多余的列

高性能MySQL书中把这个分成 多表关联时返回全部列 和 总是取出全部列。 个人感觉可以归成一列。多表关联时候，如果我们要的只是其中一个表的数据，就直接 ”SELECT 表名.* “ 不要直接用 * 。在使用select * 时要注意是不是真的需要全部数据。 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，而且给服务器带来额外的I/O，内存，CPU的消耗。

1.3 重复查询相同的数据

这个主要是开发人员应多多注意的事情，在开发某个模块，比如评论时，需要查询用户头像URL，用户多次评论的时候，可能会重复查询。最好是使用变量存储，对于使用频率特别高的，甚至可以用session，cookie来存放。

2. MySQL扫描额外的记录

MySQL简单衡量查询开销的三个指标：

2.1 响应时间

响应时间包括服务时间和等待时间，但这两个时间并不能细分出来，所以响应时间受影响比较大。我们可以通过估计查询的响应时间来做最初步的判断。

2.2 扫描的行数

2.3 返回的行数

二、MySQL查询执行基础

MySQL  客户端和服务端通信协议是“半双工”的，这就意味着，客户端发送给服务器和服务器发给客户端是不能同时发生，这种协议让MySQL通信简单快速，但也就无法进行流量控制，一旦一端开始了，另一端是能等它结束。所以查询语句很长的时候，参数max_allowed_packet就特别重要了。
当想MySQL发送一个请求的时候，MySLQ到底做了什么：  1. 客户端发送一个查询给服务器。 2. 服务器先检查查询缓存，如果命中，就立刻返回存储在缓存中的结果，否则进入下一个阶段。 3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。 4. 把查询的结果返回给客户端。

MySQL（优化器）能处理的优化类型：

1. 重新定义关联表的顺序。 2. 将外连接转化成内连接。 3. 使用等价变换规则。 可以移除一些恒成立和一些恒不成立的判读。例如（5 = 5 AND a > 5） => a > 5 4. 优化COUNT()、MIN() 和 MAX() 例如找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录。 5. 覆盖索引扫描 索引中的列包含所有查询中需要的列的时候，只需要使用索引返回数据，不需要搜索数据行 6. 子查询优化 MySQL可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。 7. 提前终止查询 当发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。 8. 等值传播 如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列中。 9. 列表IN()的比较 MySQL中IN()列表中的数据线进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，是O(lgn)级别的操作，等价转换成OR查询的复杂度是O(n)

MySQL如何执行关联？

MySQL中关联不仅仅是一个查询需要到两个表匹配才叫关联，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。
MySQL采用嵌套循环关联操作进行关联。
具体做法：1. MySQL先从一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到所有表中匹配的行为止。  2. 根据各个表匹配的行，返回查询中需要的各个列。 MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回上一层次关联表。看是否能够找到，依次类推。

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

1. 关联子查询

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

SELECT * FROM sakia.film WHERE film_id IN( SELECT  film_id FROM sakia.film_actor WHERE actor_d = 1)

我们希望MySQL能够先执行内层子查询，这个子查询通过索引来查找，应该会很快，事实上，MySQL 并不这么干，它会把从查询这样优化：

SELECT * FROM sakia.film WHERE EXIST (SELECT * FROM sakia.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);

MySQL把外层压人到子查询中处理，如果外层的表是非常大的表的话，这个查询的性能就会非常的糟糕。
改进方案： 1.  

SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

2. 

SELECT *FROM sakia.film WHERE EXISTS( SELECT * FROM sakia.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);

如何用好关联子查询

并不是所有的关联子查询的性能都很差，很多时候，关联子查询是一种非常合理，自然，甚至是性能最好的写法。

EXPLAIN SELECT film_id, language_id FROM sakila.film WHERE NOT EXISTS(
SELECT * FROM sakia.film_actor
WHERE film_actor.film.id = film.film_id
);
这个查询并不比左外连接的差，但这最好是要经过测试来验证的。

四、优化特定类型的查询
1. 优化COUNT()查询
COUNT 要注意的就两点：1. COUNT统计列值要求列值是非空的（不统计NULL）。2. COUNT(*)统计的是结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。
一个容易产生误解的是：MyISAM 的 COUNT()函数总是非常快的，实际上只有没有WHERE条件的COUNT(*)才非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。当统计带WHERE子局的结果集行数，可以是统计某个列值的数量时，MyISAM的COUNT()和其他存储引擎没有任何不同。
PS：使用COUNT的时候可以利用MyISAM这个特性来加快查询。比如，当查询  SELECT COUNT(*) FROM world.City WHERE ID > 5; 可以改成 SELECT (SELECT COUNT(*) FROM world.City - COUNT(*) FROM world.City WHERE ID <= 5;
使用精确值来估算，执行EXPLAIN并不需要真正去执行查询，成本很低。比如那些每30分钟统计一下大概的在线人数，就可以用这种估算的方式来优化。
当count()本身需要扫描大量的行才能获取精确的结果，优化是非常难的。在MySQL的层面上能做的就只有索引覆盖扫描了，如果还不够，则需要考虑修改应用的框架。
2. 优化关联查询
1. 确保ON和USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用c关联的时候，如果优化器的关联顺序是B、A，那么久不需要再B表的对应列上建索引了。只需要在关联顺序中第二个表的相应列上创建索引。
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
3. 优化子查询
尽可能使用关联查询代替子查询，至少当前的MySQL版本需要这样。但尽可能不是绝对，经过测试的才是硬道理。
4. 优化GROUP BY 和 DISTINCT
MySQL使用索引来优化这两种查询。无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。
SELECT actor.first_name, actor.last_name,COUNT(*)
FROM sakila.film_actor
        INNER JOIN sakia.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakia.film_actor
        INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;
使用第二段SQL效率比第一段要高。
5. 优化 LIMIT 分页
通常会使用LIMIT加上偏移量的方法实现，同时加上合适的ORDER BY的子句。如果有对应的索引，通常效率会不错。
在偏移量非常大的时候，例如，LIMIT 1000,20这样的查询，需要查找10020条记录然后返回20条，前面10000条都将被抛弃，这样的代价非常高。优化此类分页查询的一个最简单的方法就是尽可能使用索引覆盖扫描，而不是查询所有的列。例如：
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50，5；
如果这个表非常大，建立改成：
SELECT film.film_id,film.description
FROM sakia.film
        INNER JOIN (
              SELECT film_id FROM sakia.film
              ORDER BY LIMIT 50,5
        ) AS lim USING(film_id);
这里的延迟关联大大提升查询效率，让MySQL扫描尽可能少的页面。有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果一个位置列上有索引，并且预先计算出了边界值，则可以直接定位。
6. 优化UNION查询
除非需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。

以上是《高性能MySQL》一书中关于的总结，但总结的这些感觉还是不够用，在接下来我会根据网上别人的经验总结一些, 来源 http://database.51cto.com/art/201407/445934.htm。

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。这个在上面也有说过，尽量不要设置NULL值。

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4. 应尽量避免在 where 子句中使用 or 来连接条件，可以改为 union all 来查询
5. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值能用between就不用in，或者使用exists来代替，如：
SELECT num FROM a WHERE num in(SELECT num FROM b)
可以用exists来替换：
SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)
6. 模糊搜索也会导致全表搜索，可以通过建立全文索引来提高效率。
7. 如果在 where 子句中使用参数，也会导致全表扫描，可以强制查询使用索引
SELECT id FROM t wiith( index(索引名)) WHERE num = @num

8. 避免在where子句对字段进行表达式操作。
9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
SELECT id from t where datediff(day,createdate,'2015-11-30')  = 0 
应改成：
SELECT id FROM t where createdate >= '2015-11-30' and createdate < '2015-12-1'


10. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

11. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
12 .尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。