COUNT()和MAX()优化
COUNT(*)包含为空的
COUNT(id)不包含为空的列
同时查出2006和2007年的定影数量。
SELECT COUNT(release_year=’2006’ OR NULL) AS ‘2006年电影数量’,
COUNT(release_year=’2007’ OR NULL) AS ‘2007年电数量
FROM file’
子查询优化
子查询优化为join查询,但是要注意关联键是否为一对多的关系,会出现重复的记录.使用distinct即可解决。
查询sandra出演的所有影片
Select title,relases_year,length
From file where film_id(
Select film_id from film_actor where actoer_id in(
SELECT actor_id from actor where first_name =’sandra’
)
)
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
优化后的
Select actor.first_nam,actor.last_name,c,cnt from sakila.actor innor join (
SELECT actor_id,count(*) as cnt from sakila.film_actor group by actor_id
) AS c using(actor_id);
LIMIT优化
Limit往往会出现filesort
1 使用索引的列或主键进行order by操作。
2 sql的执行顺序是from->join->where->group by ->having->select ->order by ->limit,limit在select后面,比如取偏移量为10000、10条数据,这样就会导致回磁盘取了10010条数据,实际只需要10条数据。偏移量越大,性能就越差。
-- 12秒
-- 常规分页方法,mysql会取出1~10000000和10000000~10000010行数据,然后去掉10000000行数据返回剩余的10条数据,
-- 首先会回磁盘取数据,IO会占用时间。
select * from user order by id DESC limit 10000000,10
最佳优化
-- 0.02秒
-- 通过上一页的最小id,过滤掉之前的数据,就不存在获取偏移量数据,直接获取当前页面的数据,
-- 又因为id是主键,默认存在索引,效率较高。
explain select * from user where id < 7471176 order by id DESC limit 10
-- 9秒
-- 耗时主要在遍历b+树的叶子节点链表,还有就是关联连接
explain select * from user AS u INNER JOIN (select id from user order by id desc limit 10000000,10) AS t ON t.id=u.id
参考:https://www.cnblogs.com/scotth/p/7995856.html
https://mp.weixin.qq.com/s/AFTj-iAUTVqhwgDvRzS2oA