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

减少不必要数据的访问

在优化查询的最先考虑的是我们是否从数据库中取出不必要的数据，也就是SELECT *是否有必要？取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。

小表驱动大表

1	SELECT * FORM A WHERE EXISTS(SELECT 1 FROM B WHERE B.id = A.id)

等价于

1 2	SELECT * FROM A SELECT * FROM B WHERE B.id = A.id

当A表的数据集小于B表的数据集时，用EXISTS优于IN。

切分查询

如果用一个大的语句一次性完成的话，则能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但很重要的查询，此时就需要把原来的查询语句切分，或者限制原本查询语句将影响的行数，并多次执行该语句，以降低对系统资源的消耗。

1	DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

可以被切分成

row_affected = 0
do{
	row_affected = do_query(
    	"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH LIMIT 		10000")
}while row_affected > 0

减少IN的使用

1
2
3

SELECT * FROM film 
WHERE film_id IN(
    SELECT film_id FROM film_actor WHERE actor_id = 1);

一般认为该条语句会先执行子查询然后返回所有actor_id为1的film_id，然后执行外层查询，但是MySQL不是这样做的。MySQL会将相关的外层查询压到子查询中，它认为这样效率更高，也就是该查询会被改写成：

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

这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询，通过EXPLAIN可以看到子查询是DEPENDENT SUBQUERY。MySQL会先对外层表进行全表扫描，然后根据返回的film_id逐个执行子查询。当外层表是个大表的时候，该查询的性能将极低。所以应该改写成如下形式：

1
2
3

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

因为IN()的效率不高，所以一般使用EXISTS()的等效改写，来获得更好的效率。

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

优化LIMIT分页

在进行分页操作的时候，通常会使用LIMIT加偏移量的方法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率不错，否则MySQL需要做大量的文件排序操作。但是在偏移量非常大的时候，比如LIMIT 10000,50这样的查询，这时MySQL需要查询10050条记录，然后返回最后的50条，前面的10000条记录将会被抛弃，这样的代价将非常高。

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

1	SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这个表非常大，那最好改写成下面的样子

SELECT film.film_id,film.description
FROM film
	INNER JOIN(
    	SELECT film_id FROM film
    	ORDER BY title LIMIT 50,5
    ) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的纪录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

优化ORDER BY

使用ORDER BY的时候尽量使用Index方式排序，避免使用FileSort方式排序，在ORDER BY语句使用索引的最左索引的时候和WHERE子句与ORDER BY子句条件列组合满足索引的最左前列时会使用Index排序。

优化UNION查询

MySQL总是通过创建并填充临时表表的方式来执行UNION查询。因此很多优化策略在UNION查询中没法很好的使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。（例如，直接将这些子句冗余的写到一份到各个子查询）。

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL就会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍会使用临时表存储结果。事实上，MySQL 总是将结果放入临时表，然后再读出，再返回给客户端，虽然这样做是没有必要的。