SQL深分页的建议优化方案

SQL深分页的建议优化方案

以为MySQL为例

假如有 t_order表 数据量有1000w，现在想分页查询第80w页的数据，每页10条，最基本的sql如下

select * from t_order  limit  7999990 , 10;

上面查询 SELECT * FROM t_order LIMIT 7999990, 10; 的问题在于，MySQL 必须扫描前 7999999 行（注意这里应该是 7999999，因为 LIMIT 子句中的偏移量是基于 0 的索引，但实际上它包括了这一行）来定位到第 7999990 行之后的 10 行数据。这种全表扫描（或至少是大部分表的扫描）在大数据集上是非常耗时的。

优化方案

方案有挺多的，但是比较通用且改造起来比较简单的还是延迟关联的查询方式。
改造如下：

SELECT * FROM t_order t1 INNER JOIN 
  (SELECT id FROM t_order  ORDER BY id  LIMIT 7999990 , 10) t2 ON t1.id = t2.id;

改造后的优势：

• 减少扫描的数据量：子查询 SELECT id FROM t_order ORDER BY id LIMIT 7999990, 10 仅仅扫描足够的数据以找到所需的 10 个 id。这通常比扫描整个表要快得多，特别是当表很大且 id 字段被索引时。
• 利用索引：如果 id 字段上有索引（通常是主键或唯一键），MySQL 可以非常高效地找到这些特定的 id。索引使得数据检索操作能够迅速跳转到数据表中的特定位置，而不是顺序扫描整个表。
• 减少数据查询量：子查询只检索了 id 字段，这通常比检索整个行要轻量得多。

注意点： 上面第一个查询由于没有显示的排序字段 （通常情况下会隐式的使用id排序），所以 改造后的子查询 需要加一个显示的 ORDER BY id 这样才能保证查询结果的一致。

经过测试 改造后的SQL在大数据量深度分页情况下性能可以提升很多倍，通常在5倍以上，数据量越大分页越深提升的效果越明显。