数据库

mysql 5.7.28

需求背景

按id增序 导出t_order_detail表数据，由于数据量过多，防止一次查询数据量大多导致异常，批量查询数据，每次查询200条数据，数据量50万，查询出的数据量5万多条。

-- 表结构
CREATE TABLE `t_order_detail` (
   `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
   `business_type` int(11) NOT NULL COMMENT '业务类型',
   `business_no` varchar(50) NOT NULL COMMENT '业务号',
   `institution_id` bigint(20) NOT NULL COMMENT '机构ID',
   `institution_name` varchar(50) DEFAULT NULL COMMENT '机构名称',
   `institution_type` int(11) DEFAULT NULL COMMENT '机构类型',
   `province_code` varchar(6) DEFAULT NULL COMMENT '所属省',
   `city_code` varchar(6) DEFAULT NULL COMMENT '所属市',
   `area_code` varchar(6) DEFAULT NULL COMMENT '所属区',
   `full_area_desc` varchar(2000) DEFAULT NULL COMMENT '所属地区全称',
   `statement_id` bigint(20) DEFAULT NULL COMMENT '账单ID',
   `business_order_no` varchar(50) DEFAULT NULL COMMENT '业务订单号',
   `business_end_time` datetime DEFAULT NULL COMMENT '业务完成时间',
   `order_status` varchar(50) DEFAULT NULL COMMENT '业务订单状态',
   `remark` varchar(300) DEFAULT NULL COMMENT '备注',
   `create_time` datetime NOT NULL COMMENT '创建时间',
   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人id',
   `create_order_time` datetime DEFAULT NULL COMMENT '下单时间',
   PRIMARY KEY (`order_id`),
   KEY `key_order_no` (`business_order_no`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=765728 DEFAULT CHARSET=utf8mb4 COMMENT='订单详情'

SQL如下

 select * from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200

Explain结果

explain select * from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200

id	select_type	table	parittions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	t_order_detail		index		PRIMARY	8		50200	33.3	Using where

优化方法

方法1

《高性能MySql第三版》章节6.7.5 优化Limit分页中提到，在偏移量非常大的时候，例如可能是LIMIT 1000,20 这样的查询，这时候MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化此种查询，要么在页面中限制分页数量，要么是优化大偏移量的性能。使用“延迟关联”，它让MySQL扫描尽可能少的页面，获取需要要访问的记录后再根据关联列回原表查询需要的所有列。

 select  * from t_order_detail T1
 inner join ( select order_id from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200)T2
 on T1.order_id = T2.order_id

Explain结果

id	select_type	table	parittions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	PRIMARY	<derived2>		ALL					50200	100.00
1	PRIMARY	T1		eq_ref	PRIMARY	PRIMARY	8	T2.order_id	1	100.00
2	DERIVED	t_order_detail		index		PRIMARY	8		50200	33.3	Using where

也没看不出来区别，直接用SQL执行看消耗的时间

select SQL_NO_CACHE * from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 10000,200
-- cost 0.093 s
 select SQL_NO_CACHE * from t_order_detail T1
 inner join ( select  order_id from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 10000,200)T2
 on T1.order_id = T2.order_id
-- cost 0.078 s，好像没怎么优化

select SQL_NO_CACHE * from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200
-- cost 0.140 s

 select SQL_NO_CACHE * from t_order_detail T1
 inner join ( select  order_id from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200)T2
 on T1.order_id = T2.order_id
-- cost 0.093 s 在后面的数据优化效果出来了

疑问

这个延迟关联蛮简单的(自我感觉)，为啥MySQL不直接内部实现优化呢？

延迟关联到底节省了哪部分动作消耗的时间，如果只是如下的SQL,那就根本没必要关联，在查询了其他的字段后，才需要延迟关联。所以是节省了获取其他字段的消耗的时间？还是排序时多个字段后更加耗时？

select order_id from t_order_detail where create_order_time >='2021-08-26' order by order_id desc limit 50000,200

方法2

当前SQL使用id排序，可以直接使用上一页数据最后一条数据的Id做筛选，这样直接筛选出需要的数据，查询查第49999条数据的order_id为707352，SQL如下

select  SQL_NO_CACHE * from t_order_detail where create_order_time >='2021-08-26' and order_id <751382 order by order_id desc limit 200
-- cost 0.078 s

Explain结果

id	select_type	table	parittions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	t_order_detail		range	PRIMARY	PRIMARY	8		90760	33.3	Using where

此种优化方法要求 使用唯一的字段排序。

参考

高性能MySql
MySQL ORDER BY _ LIMIT performance_ late row lookups at EXPLAIN EXTENDED