关于数据库深分页的思考

背景描述：

数据库中表的数据量（1000W+）过大，进行全表批量查询时，使用limit offset size形式查找数据速度较慢。

查询例子：

select * from page order by user_name limit 600000,10;
select * from page order by id limit 600000,10;(id为主键)

问题分析：

1、查询可以使用到的索引可以分为主键索引和非主键索引(需要回表查询)。

2、数据库有优化器，如果使用非主键索引，非主键索引查找的速度＋回表的速度不如全表扫描，则走全表扫描。

3、对于大表的全表扫描磁盘IO会被大量占用，磁盘读取是一个耗时操作，如果所有的数据都是需要的，这是没有问题的；如果为了得到正确的查询结果，需要将无用的数据也要加载到内存中，这些无需的耗时则需要优化。

4、对于b+树，非叶子节点没有记录当前左右节点拥有的行数，即使使用索引，也是帮助排序，不能通过log(n)的复杂度定位到具体数据。

5、使用limit子句，会在索引树上对叶子节点进行扫描，以以上sql为例，会扫描到第600001个记录。因此使用limit子句，扫描offset个节点无法避免，区别在于非主键索引的叶子节点数据比主键索引的叶子节点少，一次可读取更多叶子节点进入内存，扫描速度更快。但是非主键索引最终获取的10条数据是需要回表的，成本等于二级索引树600010次遍历(顺序IO)+600010次回表(随机IO)，在数据库的优化器看来，价值不如对主键的索引树进行全表扫描(顺序IO)。

6、为什么例子中的sql不能将代价计算为二级索引600010次遍历(顺序IO)+10次回表(随机IO)。现实场景更为复杂，在有where条件的情况下，只从user_name索引判断是否符合条件具有局限性。所以依然需要回表查询当前数据是否符合条件，这是一种sql标准。因此最终成本还是需要进行600010次回表。

优化思路：

1、索引是需要的，对于部分数据可以从索引树上获取的尽量从索引树中获取，避免回表；

2、将offset移到where条件中，对于非主键索引，以小的代价遍历user_name索引，获取符合条件的主键，再回表查询详细数据。实现方式覆盖索引。

SELECT * FROM page 
JOIN (SELECT id FROM page ORDER BY user_name LIMIT 600000,10) AS tmp 
ON page.id = tmp.id

3、将offset移到where条件中，当场景是循环遍历全量数据时，可以记录上一次遍历到的值，并再次基础上查询下一批数据。实现方式游标。(缺点：无法灵活切换页面)

SELECT * FROM page 
WHERE user_name > 'last_value_from_previous_page' 
ORDER BY user_name 
LIMIT 10

（如有不同的见解，可随时联系我进行讨论）