在我的index_innodb数据库表中有一千万条数据(可以用存储过程添加数据)
表结构如下:
部分数据如下:
现在进行分页查询:
sql语句 | 总耗时(秒) |
---|---|
(从第10页查询)SELECT * FROM index_innodb LIMIT 100,10 | 0.018 sec |
(从第5万页查询)SELECT * FROM index_innodb LIMIT 5000000,10 | 3.940 sec |
(查询最后一页)SELECT * FROM index_innodb LIMIT 9999990,10 | 8.752 sec |
数据表明:当我执行分页查询的时候,越往后查询的页执行的越慢
原因:在执行limit a,b 时候,系统会扫描a行之前所有的数据,虽然这些数据是没有用的,接着扫描a行之后的b行,只有这些数据游泳。但是这就导致系统越往后扫描,占用的时间越长,如果数据很多的话,数据库压力就很大了。
优化
按照上面所说,每次分页查询都要扫描a行之前的所有数据,那么有没有办法让数据库跳过这一步,直接扫到a行。答案是索引或者复合索引(主键默认加索引),索引可以直接找到第a行数据,所以解决办法如下。
sql语句 | 总耗时(秒) |
---|---|
SELECT * FROM index_innodb WHERE id>100 LIMIT 10 | 0.002 sec |
SELECT * FROM index_innodb WHERE id>5000000 LIMIT 10 | 0.002 sec |
SELECT * FROM index_innodb WHERE id>9999990 LIMIT 10 | 0.004 sec |
注意:运用此方法,数据id必须是连续的
其实最快的sql语句是下面这句
SELECT * FROM index_innodb WHERE id BETWEEN 101 AND 110;
SELECT * FROM index_innodb WHERE id BETWEEN 5000001 AND 5000010;
SELECT * FROM index_innodb WHERE id BETWEEN 99999991 AND 10000000;
另外:如果需要查询 id 不是连续的一段,最佳的方法就是先找出 id ,然后用 in 查询