公众号,欢迎关注
我们有分页查询订单详细信息的需求,订单表目前有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行数据就可以了