Mysql 千万级数据分页优化

     在使用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 排序，方案三比较通用性能较低。