SQL语句优化技巧

环境

数据库使用的是MySQL5.7，数据是官方提供的sample database:sakila，如有需要，可以自行导入尝试，关于优化效果可以使用explain关键字查看。

limit查询语句优化

limit是一个常用于分页比较费io的一个语句，
以一个常用LIMIT查询语句为例

1	SELECT * FROM film LIMIT 100,10

首先可以使用主键order by操作

1	SELECT * FROM film ORDER BY film_id LIMIT 100,10

为了进一步优化，可以使用where过滤主键范围

1	SELECT * FROM film WHERE film_id > 90 and film_id <= 100 ORDER BY film_id LIMIT 1,10

经过优化后每次扫描的行数都固定为10列。如果主键并非连续，可以建一列辅助列并添加索引，保证其实自增的即可。

group by查询语句优化

查询演员的电影数量，原语句：

SELECT actor.first_name, actor.last_name, count(*)
FROM sakila.film_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

这样的写法会创建一个临时表，并且会使用filesort，造成大量io，改写：

1
2
3

SELECT actor.first_name, actor.last_name,c.cnt FROM actor 
INNER JOIN (select actor_id, count(*) AS cnt FROM film_actor GROUP BY actor_id) AS c 
USING (actor_id)

这种写法好处在于子查询根据主键索引进行查询，减少了io

子查询优化

要使子查询效率提高，改成join方式能有效提高效率，因为MySQL不需要在内存中创建临时表来完成逻辑上的需要两个步骤的查询工作。