多数据分页列表sql优化
普通的查询方式
SELECT * FROM `user` WHERE `sex`=1 and `userName` LIKE '%明%' ORDER BY `seq` desc LIMIT 0,15
数据量不大,就几千条数据,那么这句sql速度的影响就不大;但数据量超过10万,时间上的变动就会比较明显。
问题和优化原理
问题:
查询后面的数据,分页 越多,条件 越多,点击的页码 越大,速度就会越慢
原理:MySQL需要排序去查前面的记录,直到查询到需要的那一页,再丢弃前面的记录,然后返回需要的那一页数据。使用limit分页时的MySQL要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。
这里用到的sql优化方式是:
1、子查询优化
2、延迟关联优化
3、限定id优化
第一种优化sql (子查询优化+限定id优化)
select * from `user` where `id` > (select `id` from `user` WHERE `sex`=1 and `userName` LIKE '%明%' order by `id` desc limit 169620,1 ) limit 15;
第二种优化sql(子查询优化+延迟关联优化)
select * from `user` inner join (select id from `user` WHERE `sex`=1 and `userName` LIKE '%明%' order by `id` desc limit 169620,15 ) as a using(id);
第三种优化sql(子查询优化+限定id优化)
select * from `user` a , (select id from `user` WHERE `sex`=1 and `userName` LIKE '%明%' order by `id` desc limit 169620,15 ) b where a.id=b.id;
第四种优化sql(子查询优化+限定id优化)
select * from `user` a , (select id from `user` where `id` > 169620 and `sex`=1 and `userName` LIKE '%明%' order by `id` desc limit 15 ) b where a.id=b.id;
索引优化
1、普通索引
2、组合索引
3、强制索引(特殊情况,sql涉及多条件查询,且查询的索引非最优)
题外话
总数count的效率比对:count(1)>count(*)>count(field)