美文网首页
MySQL的limit 优化

MySQL的limit 优化

作者: Always_July | 来源:发表于2021-09-27 20:50 被阅读0次

    数据库

    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

    相关文章

      网友评论

          本文标题:MySQL的limit 优化

          本文链接:https://www.haomeiwen.com/subject/ivuqtqtx.html