使用子查询提高MySQL分页效率

今天在老王的博客中看到了一篇《验证使用子查询提高MySQL分页效率》的文章，很有收获，总结分享之~

对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题。例如老王做的测试，拿一个接近一千万行记录的表，进行查询从第1000000之后的30条记录：

 

SQL代码1：平均用时6.6秒
SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代码2：平均用时0.6秒
SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。

可以看出，越往后分页，LIMIT语句的偏移量就会越大，两者速度差距也会越明显。实际应用中，可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。