Mysql---limit 分页效率逐渐降低，及解决方案

1、直接使用数据库提供的SQL语句 limit M ,N

SELECT * from user limit 0 , 10 ; -- 0.034s

SELECT * from user limit 100 , 10 ; -- 0.074s

SELECT * from user limit 1000 , 10 ; -- 0.087s

SELECT * from user limit 10000 , 10 ; -- 0.102s

SELECT * from user limit 100000 , 10 ; -- 0.236s

SELECT * from user limit 1000000 , 10 ; -- 1.684s

SELECT * from user limit 2000000 , 10 ; -- 2.971s

适用场景：可以看出，在数据量小时，使用limit M ，N 可以很快的查询出数据，但是当数据量达到十万级甚至百万级时，查询效率呈几何倍数递增；
该查询会导致全表扫描，速度会很慢，且有的数据库返回结果集不稳定(如某次返回1,2,3，另外一次返回2,1,3)，limit限制的是从结果集的M位置取出N条输出，其余抛弃；

2、基于索引排序

SELECT * from user order by id asc limit 0 , 10 ; -- 0.075

SELECT * from user order by id asc limit 100 , 10 ; -- 0.084

SELECT * from user order by id asc limit 1000 , 10 ; -- 0.088

SELECT * from user order by id asc limit 10000 , 10 ; -- 0.099

SELECT * from user order by id asc limit 100000 , 10 ; -- 0.227

SELECT * from user order by id asc limit 1000000 , 10 ; -- 1.532

适用场景：数据量小时，当数据量达到十万到百万级时，查询效率与第一种相比，仍呈现几何倍数递增
在数据量小时，会根据索引进行选择，但是数据量大时，仍旧会导致全表扫描

3、基于所以筛选再排序

SELECT * from user order by id asc limit 10 ; -- 0.043

SELECT * from user where id > 100 order by id asc limit 10 ; -- 0.085

SELECT * from user where id > 1000 order by id asc limit 10 ; -- 0.086

SELECT * from user where id > 10000 order by id asc limit 10 ; -- 0.084

SELECT * from user where id > 100000 order by id asc limit 10 ; -- 0.083

SELECT * from user where id > 1000000 order by id asc limit 10 ; -- 0.083

SELECT * from user where id > 2000000 order by id asc limit 10 ; -- 0.085

SELECT * from user where id > 2000000 order by id desc limit 10 ; -- 0.085

可以看出，该查询效率稳定，适用于数据量多是情况，最后ORDER BY后的列对象是主键或者唯一索引，使得ORDER BY操作能利用索引被消除，但是结果集是稳定的