文章

mysql 深分页优化

mysql 深分页优化

数据库表

CREATE TABLE `t_test2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

首先向数据库中插入1000万条数据

一、查询列为主键

1、直接使用limit

select t.id,t.key1,t.common_field from t_test2 t  ORDER BY id limit 9000000,10

image-20240508151034227

2、使用延迟关联

SELECT t.id,t.key1,t.common_field FROM `t_test2` t join (select id from t_test2 ORDER BY id limit 9000000,10) tmp on tmp.id = t.id

image-20240508151051090

3、让接口查询时携带上次查询结果的最大id

SELECT t.id,t.key1,t.common_field FROM `t_test2` t WHERE id >9000000 limit 10

image-20240508151111076

但这种方式仅限查询列为数字且有索引

二、查询列为字符

1、直接使用limit

SELECT * FROM t_test2 ORDER BY key1 LIMIT 9000000, 10;

image-20240508151314784

image-20240508151516221

MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求,所以如果使用二级索引执行上述查询的话,意味着要进行5001次回表操作。

server层在进行执行计划分析的时候会觉得执行这么多次回表的成本太大了,还不如直接全表扫描+filesort快呢,所以就选择了后者执行查询。

2、使用延迟关联

SELECT * FROM t_test2 as t, (SELECT id FROM t_test2 ORDER BY key1 LIMIT 9000000, 10) AS d WHERE t.id = d.id;

image-20240508151349570

image-20240508151724900

License:  CC BY 4.0