数据库
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
网友评论