MySQL limit 分页优化

2020-12-10   174 次阅读


MySQL limit 优化

延迟关联

对于数据量很大的分页查询时,很可能出现需要查询大量数据,但实际只使用了少量,造成资源浪费,性能低

# 表结构
CREATE TABLE `t_demo_4` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `test` int(11) DEFAULT '0' COMMENT 'c_test',
  `test_aks` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'c_test_aks',
  `test_2` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_2',
  `test_2_aks` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_2_aks',
  `test_3` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_3',
  `test_3_aks` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_3_aks',
  `test_4` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_4',
  `test_4_aks` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_4_aks',
  `test_5` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_5',
  `test_5_aks` varchar(100) COLLATE utf8_bin DEFAULT '' COMMENT 'test_5_aks',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='aks test demo';


# 查询了9999999 + 2000条数据,只用了2000条
select *
from t_demo_4
where id >=1 ORDER BY id limit 9999999, 2000

尝试用 explain 查看执行计划

image-20201210112349753

因为表中只有主键索引,所以直接使用limit不会走索引,消耗很大

优化此类分页查询的一个最简单的方法就是尽可能地使用覆盖索引扫描,而不是查询所有的列,然后根据一次关联操作再返回所需的列。对于大数据分页,提升效率会很高

使用延迟关联分页查询:

select *
from t_demo_4
	inner join (
		select id FROM t_demo_4 
        where id >= 1 
        ORDER BY id limit 9999999, 2000
	) AS b USING(id);

image-20201210155050661

这里的关联子查询只选取了id主键,所以可通过覆盖索引查询返回需要的主键,再根据这些主键关联原表得到需要的行。这样可以减少数据库扫描那些需要丢弃的行。

另一种优化写法:

select *
from t_demo_4
where id >= (select id from t_demo_4 ORDER BY id limit 9999999, 1) limit 2000

本质上没有区别,都是先用覆盖索引查询,只不过这里是先定位书签

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

Live in the future, then build what's missing.