如何优化超大的分页查询？

在你开发程序的时候，使用 LIMIT 子句做分页查询可能是非常频繁的，这是一个非常常见的业务场景。

那你在使用 limit 子句的时候有没有遇到过什么问题呢，比如说性能不好？

在我做 DBA 的时候，曾经遇到过不少由于分页查询性能差，需要优化的案例。

那常见的原因基本上也都是分页过多。

过大的分页查询为什么会慢？

下面我们先构造一个例子：

CREATE TABLE `limit_optimize_tbl` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`account` VARCHAR ( 50 ) NOT NULL,
	`order_id` VARCHAR ( 100 ) NOT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 2000002 DEFAULT CHARSET = utf8;


CREATE INDEX idx_account ON limit_optimize_tbl ( account );
CREATE INDEX idx_order_id ON limit_optimize_tbl ( order_id );


CREATE PROCEDURE limit_optimize_test () BEGIN
	DECLARE i INT;
	SET i = 1;
	
  WHILE i <= 3000000 DO
    INSERT INTO limit_optimize_tbl ( account, order_id ) VALUES('test_123',concat( 'order', i ));
		SET i = i + 1;
	END WHILE;
	
END ;

		
CALL limit_optimize_test ();

下面的这行 SQL 是一个常见的分页查询的语句：

select * from limit_optimize_tbl order by order_id limit 500000,10;

这种写法是最简单的，但同时也是最容易出问题的。

曾经有人做过调查，用户在浏览前端页面时，假如这个页面是分页浏览的（例如淘宝），用户只会浏览前面几页，一般翻页超过 10 页很多人就开始表现的不耐烦了。

在翻页比较少的情况下，LIMIT 子句并不会表现出性能问题。

但是假如用户要直接跳到最后一页呢？

通常情况下，由于要保证所有的页面都可以正常跳转，因此可能不会使用如下这种语句：

select * from limit_optimize_tbl order by order_id desc limit 0,10;

而是继续采用正序顺序做分页查询：

select * from limit_optimize_tbl order by order_id limit 500000,10;

采用这种 SQL 查询的话，此时从 MySQL 中取出这 10 行数据的代价是非常大的，需要先排序出前面1000010 条记录，然后抛弃前面的 1000000 条。查询数据和排序的代价非常高

我们再来看一下上面这个 SQL 语句的执行计划：

从执行计划中你可以看到，在大分页的时候，MySQL 并没有走索引扫描，而是使用了全表扫描的方式。
那这又是为什么呢？

MySQL 数据库采用了基于代价的查询优化器，而查询代价的估算是基于 CPU 代价和 IO 代价。

由于现在机械硬盘还没有被完全淘汰掉，因此在类似这种局部扫描的动作中，随机 IO 的代价，仍然被MySQL 的查询优化器认为是非常高的。

对于局部扫描，MySQL 会根据数据量的情况和数据获取的条件，去做代价估算，决定是采用顺序扫描还是随机读取存储系统。

如果 MySQL 在查询代价估算中，认为采取顺序扫描方式比局部随机扫描的效率更高的话，就会放弃索引，转向顺序扫描的方式。

这就是为什么在大分页中 MySQL 数据库走了全表扫描的原因。

下面我们还是使用刚刚的 SQL 语句，再来实验一下：

从上面的实验你可以看到，在这个临界点上，MySQL 分别采用了索引扫描和全表扫描的查询优化方式。

由于 MySQL 的查询优化器的算法核心我们是无法人工干预的，因此我们的优化思路应该着眼于如何让分页维持在最佳的效率区间。

下面我们就来看下如何优化：优化可以从两个角度进行分析：SQL 改写优化与业务角度优化。

SQL改写优化

索引覆盖

由于索引是有序的，因此这种优化方式的思路是直接在索引上完成排序和分页的操作。

先来说说什么是覆盖索引。

如果一个 SQL 语句，通过索引即可直接获取查询结果，而不再需要回表查询，就称这个索引覆盖了这条SQL 语句。也就是平时所说的不需要回表操作。在 MySQL 数据库中使用执行计划查看，如果 extra 这一列中显示 Using index，就表示这条 SQL 语句使用了覆盖索引。

你也可以对比一下使用了覆盖索引的大分页和和没有使用覆盖索引的性能差异。（这个差异和你的数据有很大关系，当每条数据大小很小时其实时间差距不大的，当单条数据的大小超过1KB是现象比较明显）

子查询优化

由于在 SELECT 语句中我们很少会只查询某一两个列，因此上述覆盖索引的适用范围就比较有限。

可以通过将分页的 SQL 语句改写成子查询的方法获得性能的提升：

select * from limit_optimize_tbl where id > (select id from limit_optimize_tbl order by order_id limit 500000,1) limit 10; -- 0.090s

这种优化方法也有其局限性：

首先，分页的数据必须是连续的。
其次，WHERE 子句里面不能再添加别的条件（当然，你可以在子查询中增加where条件）。

延迟关联

和上述子查询的做法类似，我们也可以使用 JOIN 的语法，先在索引上完成分页的操作，然后再回表获取需要的数据列。

select * from limit_optimize_tbl t1 join (select id from limit_optimize_tbl order by order_id limit 500000,10 ) t2 on t1.id=t2.id;-- 0.068s

和上一小节的子查询优化的方式相比，在采用了 JOIN 语法改写之后，上面的两个限制都解除了，并且 SQL 的执行效率没有损失。

反向查找

select * from limit_optimize_tbl order by order_id desc limit 0,10;

这种优化的思路来自于二分查找，也就是说，当偏移量超过记录数的一半时，就可以使用这种写法来获得性能的提升。

不过这种方法需要在分页前知道符合条件的总的记录条数，但是在 InnoDB 存储引擎中，COUNT (*) 的开销其实也不小。因此建议你仅在一些特殊情况下选用，例如直接跳到尾页这种操作。

业务角度优化

其实这个优化思路要看具体的业务内容，业务是千变万化的，因此本文中提到的这几个案例，不一定就适合你遇到的业务场景，只是起个抛砖引玉的作用。

翻页限制

不允许翻过多的页

一言以蔽之，就是不给你查了。把 LIMIT 分页的偏移量做一个限制，超过某个阈值就停止。我们以淘宝网为例，使用比较热门的 “连衣裙” 的关键词进行搜索，网站仅仅提供了 100 个数据页。很多大型互联网公司由于数据量巨大，都有使用这种方法。粗暴又有效。

流式分页

这种分页方式比较适用于移动端，即只能一页一页的向前或向后加载，不提供跳转的功能。

可以在上一级入口中提供业务列表给用户选择，从而减少分页。

这种分页方式在电商和新闻类 APP 上应用的非常广泛，你也可以试试。