【PS:本地环境不存在网络影响下测试,且多次调用sql语句,取出现次数最多的 ‘查询时间’作为参考】
首先我们有个表里面有1千万以上的数据
因为表字段少,且结构简单所以实际查询时间对人感官不是很慢,但是我们将问题扩大考虑。实际情况的表肯定不止这些字段,查询时间肯定也不知我测试时查出来这么快,所以我们只比较更优化的方案,而不单纯看所耗时间。
A=> 简单的查询从10w条数据 从0开始 耗时
B=>从第100W开始查询 耗时
C=> 从第700w开始查询出来10w条数据 耗时
数据查询数量一致,但是唯一的区别在于limit的起始位置变化。即在limit中第一个参数,也就是偏移量大到一定程度时会降低查询效率。
但是在平时使用中,我们使用limit对mysql数据查询直接进行分页其实是非常快捷的一种使用方式,但要针对大数据量时这种简单的写法反而会减缓效率
问题:针对千万级别的数据分页如何提高性能
解决方法:
一 、利用主键索引
若是我们能确认limit字段的第一个参数,偏移量的取值 我们可以使用主键索引来进行优化
上诉黄色字的操作,我们可以优化为
10w数据,从700w开始查起 耗时
于A语句耗时几乎相同甚至更少
二、BETWEEN......AND 或者> and <= 的使用
耗时
耗时
于A语句耗时相同
三、使用limit进行分页时 谨慎使用order by(ps善意小提醒:妥善使用那些执行顺序在limit前的数据你可有在分页时提高你的查询速度。)
添加上order by 的查询:
当我们只是查询id这个字段时使用order by => 耗时
查询全部字段,但从0开始到10w数据截取时查询使用order by=>耗时
查询全部字段,但从700w开始往后查询10w条数据时候 使用order by 耗时
那我们只查询id,从700w开始往后查询10w条数据时 使用order by呢耗时
从上面可以看什么?id 为主键索引,但是在大量数据里查询时 使用了order by 耗时也慢,特别是在查询的字段多的时候,这是因为mysql里面有一个执行顺序mysql执行顺序 (请记住很基础但很重要)
FROM ==> on ==> join ==> where ==> group by ==> having加聚合函数 ==> select ==> order by ==> limit
我们能看出来order by 执行顺序在limit前面,order by 是将id的顺序排了一遍,引文我这张表有1000w条数据,所以order by的时候将所有数据便利了一遍,虽然我们是直接索引便利,但是因为量的影响导致我们便利了打量不必要的数据,从而导致查询效率下降,
有时候有些代码,会为了以防万一limit 前要跟一个 order by ;但大多数表都是根据id递增添加的;只有个别表可能存在数据顺序错乱的问题,这时候你可以根据你要排序的字段进行范围查询