mysql超大分页处理方法及分析

数据库表

首先创建了test表，主键为id字段，为score字段建立了索引，并为test表生成了100w条数据

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` int NOT NULL,
  `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `s2`(`score` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

问题分析

当数据库数据量很大的时候，我们直接使用limit分页时，性能会很差。比如下面这条语句：

SELECT * FROM test ORDER BY score LIMIT 900000,10;
-- 平均1.1s

首先，mysql在使用limit时，会加载全部900010条数据并丢弃前900000条，并且，在score字段上建立的索引为二级索引，在获取全部数据的过程中一直会进行回表查询，因此我们需要对其进行优化：

SELECT * FROM test t1,(SELECT id FROM test ORDER BY score LIMIT 900000,10) t2 WHERE t1.id=t2.id;
-- 平均0.18s

在优化后的sql语句中，首先获取所需数据的id,再根据id查询整行数据，在这两段过程中全部为覆盖索引，不会发生回表查询，速度较快

启发思考

如果排序的字段为主键，同样的操作是否能够提升性能

SELECT * FROM test order by id LIMIT 900000,10;
-- 平均0.28s
SELECT * FROM test t1,(SELECT id FROM test order by id LIMIT 900000,10) t2 WHERE t1.id=t2.id;
-- 平均0.18s

可以看到，同样提升了部分性能，我认为，虽然两种方式都不会发生回表查询，但是select*在查询前900000条数据时也会返回整行的数据，第二种种方式在查询前900000条数据时只会返回id，因此相较于第一种方式依然提升了性能