使用索引覆盖查询优化limit分页查询

公众号，欢迎关注

我们有分页查询订单详细信息的需求，订单表目前有300多万的数据，而且order表在orderNo列上有索引，传统的写法可能使用limit offset, pageSize的写法来实现，但是有一个问题，在offset非常大的时候，查询会很慢，因为会使Mysql扫描大量的无用行，然后再扔掉，例如下面这样的语句

select orderNo, state, payType, ctime 
from `order` 
order by orderNo limit 2000000, 20;

查询结果如下

+----------------------+-------+---------+---------------+
| orderNo              | state | payType | ctime         |
+----------------------+-------+---------+---------------+
| KC312049Dn0fwL6S67pr |     1 |       2 | 1477496232335 |
| KC312049dOcs7Vbhb0Xw |     1 |       2 | 1477495563416 |
| KC312049DSKeJDYoJj4l |     1 |       2 | 1477496110295 |
| KC312049Dxhcd8yDinjH |     1 |       2 | 1476836872392 |
| KC312049dZvq0FCFgIi4 |     1 |       2 | 1479480408504 |
| KC312049e5AnVZxQ9x2L |     1 |       2 | 1480523251937 |
| KC312049E5MDWi86KpmS |     1 |       2 | 1477495599973 |
| KC312049E7B55XdT0c1q |     1 |       2 | 1477495660296 |
| KC312049Et9RajZgYIHl |     1 |       2 | 1477495538095 |
| KC312049F7gry3ah4QUl |     1 |       2 | 1477495553585 |
| KC312049Ff2diSRvMcAN |     1 |       2 | 1477496484309 |
| KC312049FNLa2qRmrYh0 |     1 |       2 | 1475764142929 |
| KC312049FVHbVyu9peoO |     1 |       2 | 1477496221733 |
| KC312049g2AHmW0hUYbh |     1 |       2 | 1479480264818 |
| KC312049GbCPr2ZKN07d |     1 |       2 | 1477496403856 |
| KC312049gckVBMX77pot |     1 |       2 | 1477495852336 |
| KC312049GeLTHsQf7D6v |     1 |       2 | 1475764268969 |
| KC312049gfMyBax7ipwv |     1 |       2 | 1479480227645 |
| KC312049ggnHiJMtszL3 |     1 |       2 | 1477496584497 |
| KC312049GpaqqGnFGrjy |     1 |       2 | 1475764171923 |
+----------------------+-------+---------+---------------+
20 rows in set (13.26 sec)

结果用了13秒才查出来，性能上是无法接受的，我们可以优化一下

由于上面的问题最重要的原因是扫描了大量无用的行，所以我们就从这里入手，我们知道orderNo列是索引的，我们可以想过办法通过索引覆盖查询来查出必要的需要扫描的行，然后再去扫描实际的数据行

例如，可以改成下面这样

select orderNo, state, payType, ctime 
from `order` inner join (
  select orderNo 
  from `order`
  order by orderNo limit 2000000, 20
) as o using(orderNo);

查询结果如下

+----------------------+-------+---------+---------------+
| orderNo              | state | payType | ctime         |
+----------------------+-------+---------+---------------+
| KC312049Dn0fwL6S67pr |     1 |       2 | 1477496232335 |
| KC312049dOcs7Vbhb0Xw |     1 |       2 | 1477495563416 |
| KC312049DSKeJDYoJj4l |     1 |       2 | 1477496110295 |
| KC312049Dxhcd8yDinjH |     1 |       2 | 1476836872392 |
| KC312049dZvq0FCFgIi4 |     1 |       2 | 1479480408504 |
| KC312049e5AnVZxQ9x2L |     1 |       2 | 1480523251937 |
| KC312049E5MDWi86KpmS |     1 |       2 | 1477495599973 |
| KC312049E7B55XdT0c1q |     1 |       2 | 1477495660296 |
| KC312049Et9RajZgYIHl |     1 |       2 | 1477495538095 |
| KC312049F7gry3ah4QUl |     1 |       2 | 1477495553585 |
| KC312049Ff2diSRvMcAN |     1 |       2 | 1477496484309 |
| KC312049FNLa2qRmrYh0 |     1 |       2 | 1475764142929 |
| KC312049FVHbVyu9peoO |     1 |       2 | 1477496221733 |
| KC312049g2AHmW0hUYbh |     1 |       2 | 1479480264818 |
| KC312049GbCPr2ZKN07d |     1 |       2 | 1477496403856 |
| KC312049gckVBMX77pot |     1 |       2 | 1477495852336 |
| KC312049GeLTHsQf7D6v |     1 |       2 | 1475764268969 |
| KC312049gfMyBax7ipwv |     1 |       2 | 1479480227645 |
| KC312049ggnHiJMtszL3 |     1 |       2 | 1477496584497 |
| KC312049GpaqqGnFGrjy |     1 |       2 | 1475764171923 |
+----------------------+-------+---------+---------------+
20 rows in set (0.54 sec)

通过上面的改写，原本13秒的查询只需要0.5秒便实现了，效果相当明显

这是因为inner join的表通过索引覆盖查询直接通过索引找到了需要返回的数据行，然后order表通过与这个派生表进行关联，只扫描20行数据就可以了