在对大表进行分页时,如果在服务端实现分页,大多数情况采用SQL的limit语法来实现。但是当页数越来越大时,性能很可能成为问题,尤其是需要查询表的所有字段。
1. 索引与非索引字段
在查询的结果集中,如果只包含索引字段,性能相比于包含非索引字段,差别很大。下面是一个简单的例子,在大约50w行的表上操作:
只查询索引id字段
SELECT id FROM test.bas_table
limit 400000,1000;
-- 0.094 second
查询所有字段
SELECT * FROM test.bas_table
limit 400000,1000;
-- 0.547 second
可以看到大概是6倍的性能差异,如果表行数更大,字段更多,这种差异会更加悬殊。
因此在优化时的原则之一就是尽可能只查询索引字段。
2. 分页优化的2种方式
根据上面的原则,我们尽量避免在全表扫描的时候查找非索引字段,下面是2种优化方式,核心都是采用id过滤。
1) id比较分页
先查找出当前页的最小id,然后根据id比较查询。
SELECT
*
FROM
bas_table a
WHERE
id >= (SELECT
id
FROM
test.bas_table
LIMIT 400000 , 1)
LIMIT 1000;
-- 0.078 second
2) 过滤id后join
先查找出当前页的所有id,然后关联主表操作。
SELECT
*
FROM
bas_table a
INNER JOIN
(SELECT
id
FROM
test.bas_table
LIMIT 400000 , 1000) b ON a.id = b.id;
-- 0.094
可以看到,性能上得到比较大的提升。
(完)