Mysql 千万级数据分页优化

来源：互联网发布：php报名源码编辑：程序博客网时间：2024/05/17 04:26

在使用mysql数据库查询分页时，常用查询分页 limit 在小数据量查询ok熟读非常快，但是在大表数据查询时候性能非常有限这和limit本身设计相关好了下面介绍几种常用limit分页查询优化方案。

首先使用储存过程向数据库中插入1000w数据，脚本如下

Java代码  
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执行过程

Sql代码  
EXPLAIN SELECT SQL_NO_CACHE * from student   LIMIT 90000,10;  

分析：查询过程没有使用到索引

执行时间：0.038s

优化方案一：

Sql代码  
SELECT  * from student where id>=120000  LIMIT 10;  

Sql代码  
SELECT * from student where id>=(  
    SELECT id from student LIMIT 130000,1  
 ) LIMIT 10  

使用到主键索引

查询时间：

0.001s

优化方案二：

第一步：

Sql代码  
SELECT id from student where id>=120001 LIMIT 10  

第二步：

Sql代码  
SELECT * from student WHERE id in(120001,120002,120003,120004,120005,120006,120007,120008,120009,120010);  

分析：

第一步：

第二步：

同样使用到主键索引

查询时间：0.001s+0.000s

和方案二性能差不多，实测千万数据性能最优

优化方案三

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

0 0