• 一个查询优化


    公司有个养猪业务,猪是按批次养,有个批次表biz_pigbatch,有个猪只表(biz_pig)。一个批次下有多头猪,猪可以再不同批次间流转。

    biz_pigbatch表

    RecordID主键ID
    BatchID批次ID
    DataDate猪流转进当前批次的时间
    PigID猪id

    biz_pig表

    PigID猪id

    有个查询,查猪(biz_pig)及猪在历史某个时刻所在批次(biz_pigbatch)。下面是经过简化的sql

    一开始写的sql。@strDate是查这个日期时猪所在的批次。
    SET @strDate='2022-10-1';

    select 
     p.PigID ,
     pb.BatchID 

    from biz_pig p
         left join (    select  BatchID  from (
            SELECT  BatchID  FROM `biz_pigbatch`  
            where datadate<=@strDate order by datadate desc) AA  
    group by AA.pigid)  pb ON p.PigID = pb.PigID 

    逻辑大致讲一下。临时表AA,是查出某个时间之前的批次数据,并按时间降序,将后面的数据排在前面。
    pb表 是AA表经过pigid分组,每组中取到第一行的BatchID。得到这个pigid在@strDate之前,最后一个批次。也就是pigid在@strDate时,所在的批次。

    为什么要加一个临时表AA,是因为AA是经过排序的。如果不要这个临时表,直接在一个表上先排序,后分组是有语法问题的。

    看执行分析,biz_pigbatch表会查出上千条数据(总共上万条),Non-Unique Key Lookup

    同事改了一版
     SET @strDate='2022-10-1';

    SELECT
     p.PigID ,
     pb.BatchID 

    FROM biz_pig P
    LEFT JOIN biz_pigbatch PB ON PB.RecordID = (
        SELECT PB_T.RecordID
        FROM biz_pigbatch PB_T  
        WHERE  PB_T.PigID = P.PigID AND PB_T.DataDate <= @strDate ORDER BY PB_T.DataDate DESC,PB_T.RecordID DESC LIMIT 1
    )

    如果用变量,查询直接超时,看执行分析,biz_pigbatch(PB)表会Full Table Scan。几乎查出所有数据。


    但是如果把变量直接写死,查询就会很快。执行计划如下。

     

    第一点:变量会导致执行计划不同。猜测是如果用常量,mysql会针对这个常量查询出来的数据优化执行计划。但是如果用变量,mysql认为这个值是会变化的,就不用根据当前值分析执行计划。而是考虑所有情况,给出最佳执行方案。


    第二点:同事写的版本是我不知道的查询方式,left join的连接条件不再是主表的字段。而是一个子查询。这样就能将主表的 p.PigID条件直接作用在子查询上。而且子查询还是查要连接的表biz_pigbatch,直接定位到主键。biz_pigbatch 表就只需要查一条数据。之前因为多层嵌套,条件只能用在最外层,导致内部会查询出很多数据。

    突破点是连接条件的一端可以是子查询。思路过程可能是这样的,我的第一版sql关联需要查出很多数据。那么怎么关联才能一对一呢,肯定无法直接拼关联条件。如果连接条件的一端是子查询,子查询能找到biz_pigbatch的主键RecordID就ok。

    而且严谨,考虑到了biz_pigbatch表DataDate相同的情况。

  • 相关阅读:
    iPhone15就要到来,iPhone14可能会铺天盖地地降价
    Hadoop3教程(二十三):Yarn的三大调度器
    写年度总结报告的注意事项
    【SpringBoot】请求与响应参数 IoC与DI 总结
    一维数组笔试题及其解析
    LM358运放电路参数设计-运算放大器-单位增益带宽及反馈并联电容
    模拟计划准备
    Docker 安装MySQL(主从复制)
    【EasyRL学习笔记】第四章 Policy Gradient 策略梯度
    Java面试八股之myBatis动态SQL的作用
  • 原文地址:https://blog.csdn.net/yangguosheng/article/details/127097346