分页语句的优化

前言

经常会碰到分页语句的优化，今天在遍历网页的时候又碰到了，想想还是写篇文章，以后碰到类似的问题就可以拿这篇文章回复了。

举个例子

SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000

需要在ON hugetable (A,B)建索引，同时修改语句

SELECT i.B, k.A
   FROM ( SELECT j.A 
            FROM hugeTable j 
           WHERE j.A >  $value_of_A_from_row_520000 
           LIMIT 10000
        ) k
   JOIN hugetable i
     ON i.A = k.A
  ORDER
     BY k.A

参考：

https://stackoverflow.com/questions/17812794/why-does-mysql-innodb-creating-sort-index-when-unique-index-exists