MySQL分页查询越来越慢，如何优化？

分页语法

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

比如一张订单表

CREATE TABLE `t_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`order_no` varchar(32) NOT NULL COMMENT '订单号',
`user_id` varchar(20) NOT NULL COMMENT '用户ID',
`amount` decimal(18,2) NOT NULL COMMENT '订单金额',
`order_status` tinyint(4) NOT NULL COMMENT '订单状态：0新建 1处理中 2成功 3失败',
`create_time` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_order_no` (`order_no`) USING BTREE COMMENT '订单号唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

参照网络数据1000w数据的分页查询：

select * from t_order order by id limit 0, 10;
select * from t_order order by id limit 10000, 10;
select * from t_order order by id limit 100000, 10;
select * from t_order order by id limit 1000000, 10;
select * from t_order order by id limit 10000000, 10;

时间消耗:

-- 0.002
-- 0.045
-- 0.069
-- 0.517
-- 4.134

查询原因：

这是因为查询时 MySQL 并不是跳过 OFFSET 行，而是取 OFFSET+N 行，然后放弃前 OFFSET 行，最后返回 N 行，当 OFFSET 特别大的时候，效率就非常的低下。

拿 limit 10000, 10 这条语句来说明一下， MySQL在执行这条查询的时候，需要查询 10010 (10000 + 10) 条记录，然后只返回最后 10 条，并将前面的 10000 条记录抛弃，这样当翻页越靠后时，代价就变得越来越高。

优化一：记录位置，避免使用 OFFSET

select * from t_order where id > 1000 limit 10;

优化二：计算边界值，转换为已知位置的查询

select * from t_order where id between 10000000 and 10000010;

优化三：使用索引覆盖+子查询优化

select * from t_order where id >= (select id from t_order order by id limit 1000000, 1) order by id limit 10;

优化四：使用索引覆盖+连接查询优化

select * from t_order a Join (select id from t_order order by id limit 1000000, 10) b ON a.id = b.id;