1.问题
前几天老大在群里抛出这个问题说:问个问题,千万级的表查询分页,怎么优化查询
select * from user limit 10000000,10
select * from user where name="a" limit 10000000,10
2.实验
经过验证发现以上语句在数据库中执行 (100万条数据时候)至少需要2.7s ,这简直不符合规范,当然为了完成这个难题所以就简单测了一下(现实场景中很少用到查几百万分页后的数据)。
2.1 自己的第一反应就是建立索引,或者是根据索引查询。所以采用临时表的方式,先查出主键id ,然后在去查找所有值,
SELECT * FROM USER a JOIN (SELECT id FROM USER LIMIT 3225190, 10) b ON a.id = b.id 0.5s左右
SELECT * FROMUSER a JOIN (SELECT id FROM USER WHERE NAME ="池恒" LIMIT 3225190,10) b ON a.id = b.id 1.7s左右
结论:当临时表没有where查询时,其实并不会快多少,但是没有where查询的临时表 ,速度快了5倍左右。
其实还是不符合自己想要的规范。
2.2 终极杀招
为了能更加优化查询,自己经过查找发现,建立联合索引是一个好的方法,where 的条件 和主键id 作为索引 serach(name,id)
第一次建立索引时候 是id 在前 name在后,这样确实也解决这样的问题,都变成0.5s左右 ,但是还不是我想要的 ,最后上网搜索答案,发现建立联合索引顺序不同,性能也就大大提高,于是把建立索引顺序变成是id 在后 name在前。结果打出意料 ,速度变成了0.05秒 。性能大幅度提升,当然具体原因还没有搞清楚,后续继续分析 特此记录。
3.结果
建立联合索引,性能提升。当然这只是一种实现方式,显示场景中最好不要查询几百万数据之后数据 给用户展示也不需要这么多,当然还有的就是分表查询(没有尝试过,后期尝试继续更新)
4.19年再回头看自己写的博客发现自己真的好蠢 这个具体原因是 联合索引匹配最左原则,如果还有不理解的请加微信lx13146592025 (真实个人微信 ,有问题可以留言一起探讨)