在使用mysql数据库查询分页时,常用查询分页 limit 在小数据量查询ok熟读非常快 ,但是在大表数据查询时候性能非常有限这和limit本身设计相关好了下面介绍几种常用limit分页查询优化方案。
首先使用储存过程向数据库中插入1000w数据,脚本如下
- DROP PROCEDURE if exists insertdata;
- CREATE PROCEDURE insertdata()
- begin
- declare yourid int;
- set yourid = 1;
- while yourid<1000001 do
- insert into student(name,age) VALUES ('ly',10);
- set yourid=yourid+1;
- end while;
- end
- call insertdata();
1000w数据脚本:
链接:http://pan.baidu.com/s/1kU9ro0Z 密码:vfqe
执行时间非常长,慢慢等待吧。
常用方案:
使用explan 差sql执行过程
- EXPLAIN SELECT SQL_NO_CACHE * from student LIMIT 90000,10;
分析:查询过程没有使用到索引
执行时间:0.038s
优化方案一:
- SELECT * from student where id>=120000 LIMIT 10;
or
- SELECT * from student where id>=(
- SELECT id from student LIMIT 130000,1
- ) LIMIT 10
or
使用到主键索引
查询时间:
0.001s
优化方案二:
第一步:
- SELECT id from student where id>=120001 LIMIT 10
第二步:
- SELECT * from student WHERE id in(120001,120002,120003,120004,120005,120006,120007,120008,120009,120010);
分析:
第一步:
第二步:
同样使用到主键索引
查询时间:0.001s+0.000s
和方案二性能差不多, 实测千万数据性能最优
优化方案三
- EXPLAIN SELECT SQL_NO_CACHE * from student INNER JOIN(SELECT id from student LIMIT 100000,10) as b using(id);
分析:
在查询id范围使用到索引
查询时间:0.026s
总结:方案一、方案二需要传递上一页最后一条数据id并且id是有序、可排序 如果id不是顺序增长可以使用 order by id 排序,方案三比较通用性能较低。