优化分页
select title,content from product_comment where status=1 and product_id=1111 limit 10,5;
- 第一步优化在status和product_id上构建联合索引
- 联合索引的顺序依据选择性决定
select count(distinct status)/count(*),count(distinct product_id)/count(*) from product_comment
- 但现在依然存在问题,如果limit的偏移较大的情况下会很耗时
- 第二步优化:在覆盖索引上做分页,减少二次查询的数据量
- status和product_id上构建联合索引,在comment_id上建立主键
select title,content from
(select comment_id from product_comment status=1 and product_id=1111 limit 10,5) a
join product_comment b on a.comment_id=b.comment_id;
删除重复数据
- 用链接来代替使用in
- 获取重复的评论数量
select product_id,order_id,count(comment_id) from comment group by product_id,order_id having count(comment_id)>1;
- 获取重复评论的comment_id
select comment_id from (select product_id,order_id from comment group by product_id,order_id having count(comment_id)>1) a join comment b on a.product_id=b.product_id and a.order_id=b.order_id;
- 保留最小的comment_id,删除其他的
delete from comment b join
(select product_id,order_id,min(comment_id) as comment_id from comment group by product_id,order_id having count(comment_id)>1) a
on a.product_id=b.product_id and a.order_id=b.order_id and b.comment_id>a.comment_id;
使用join来代替in
SELECT count(*)
FROM emp
WHERE depid not IN( SELECT depid
FROM dep
WHERE dname ='bLByMr' or dname = 'pyuDKO')
- 会先查询子表,然后外层表会依次去同子表的结果集进行比较。如果外层表很大会消耗很多的io。改为只用inner join
EXPLAIN
SELECT count(*)
FROM emp join dep on emp.depid=dep.depid
WHERE dname !='bLByMr' and dname != 'pyuDKO'
循环取出外层表的每一条记录与子表进行比较: https://www.jianshu.com/p/b723372ccb57
对表过分范式化
- 将一些信息存在表中,避免过多的表连接影响效率
索引覆盖
- 在一些常用的查询上尽量使用到索引覆盖,减少二次查询的机会