Limit分页原理-如何优化？

limit分页原理

当我们翻到最后几页时，查询的sql通常是：select * from table where column=xxx order by xxx limit 1000000,20。查询非常慢。但是我们查看前几页的时候，速度并不慢。这是因为limit的偏移量太大导致的。MySql使用limit时的原理是（用上面的例子举例）：

• MySql将查询出1000020条记录。
• 然后舍掉前面的1000000条记录。
• 返回剩下的20条记录。

问题：对于有大数据量的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

子查询优化

解决的方法就是尽量使用索引覆盖扫描，就是我们select后面检出的是索引列，而不是所有的列，而且这个索引的列最好是id。然后再做一次关联查询返回所有的列。上述的sql可以写成：

SELECT * FROM table t
INNER JOIN (
    SELECT  id FROM table
    WHERE
        xxx_id = 143381
    LIMIT 800000,20
) t1 ON t.id = t1.id

Mysql的分页查询语句的性能分析
　 MySql分页sql语句，如果和MSSQL的TOP语法相比，那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了

JOIN分页方式优化

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize; 

PS: 经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。

explain SQL语句：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index