SQL性能如何提升

JiangJiangYo · 2024 年7 月 19 日 08:55

 SELECT tvv.id,
               tvv.VIN,
               tvv.as_code,
               tvv.storage_time           AS storageTime,
               tvv.checkout_date          AS checkoutDate,
               tvv.tm_bas_route_id,
               tvv.gearbox_no,
               tvv.engine_no,
               too.FC04,
               too.MATERIAL_NO,
               too.sap_order_no           AS batchNo,
               too.is_allocation,
               topb.target_country,
               topb.plan_batch_code,
               tbmr.material_desc,
               tbmr.vehicle_type,
               tbmr.bs_material_no,
               tbmr.bs_material_desc,
               tbmr.ps_material_no,
               tbmr.ps_material_desc,
               tbc.clxh,
               tsr.engTime,
               tsr.apTime,
               toif.sap_feature_code      AS fc05,
               toif.sap_feature_code_desc AS fc05Desc,
               tvvm.*
        FROM tm_vhc_vehicle tvv
            INNER JOIN (SELECT tm_vhc_vehicle_id,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_HZ10' THEN input_time END)   AS hz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_HZ20' THEN input_time END)   AS hz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZBDCR' THEN input_time END) AS hzWBSRTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZBDCC' THEN input_time END) AS hzWBSCTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZ10' THEN input_time END)   AS tz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZ20' THEN input_time END)   AS tz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZBDCR' THEN input_time END) AS tzPBSRTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZBDCC' THEN input_time END) AS tzPBSCTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ10' THEN input_time END)   AS zz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ20' THEN input_time END)   AS zz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ30' THEN input_time END)   AS zz30Time
                        FROM tm_vhc_vehicle_movement
                        WHERE is_deleted = 0
                          AND input_time &gt;= #{param.inputTimeStart}
                          AND input_time &lt;= #{param.inputTimeEnd}
                          AND tm_bas_plant_id = #{param.tmBasPlantId}
                        <if test="null != param.tmBasWorkShopId and param.tmBasWorkShopId != ''">
                            AND tm_bas_work_shop_id = #{param.tmBasWorkShopId}
                        </if>
                        <if test="null != param.tmBasLineId and param.tmBasLineId != ''">
                            AND tm_bas_line_id = #{param.tmBasLineId}
                        </if>
                        <if test="null != param.nodeNature and param.nodeNature != ''">
                            AND node_nature = #{param.nodeNature}
                        </if>
                        GROUP BY tm_vhc_vehicle_id
            )                                   tvvm ON tvv.id = tvvm.tm_vhc_vehicle_id
            INNER JOIN tm_ofm_order             too ON too.id = tvv.tm_ofm_order_id AND too.is_deleted = 0
            INNER JOIN te_ofm_plan_batch        topb ON too.sap_order_no = topb.batch_no AND topb.is_deleted = 0
            LEFT JOIN  tm_bas_material_checkout tbmc ON tbmc.material_no = too.material_no AND tbmc.is_deleted = 0
            LEFT JOIN  tm_bas_checkout          tbc ON tbc.link_id = tbmc.link_id AND tbc.is_deleted = 0
            LEFT JOIN  tm_ofm_imes_feature      toif ON toif.tm_ofm_order_id = too.id AND toif.is_deleted = 0 AND toif.sap_feature_id = #{param.code}
            LEFT JOIN  (
                           SELECT vin,
                                  MAX(CASE WHEN scan_type = 'Key_Uloc_Function_10' THEN scan_time END) AS engTime,
                                  MAX(CASE WHEN scan_type = 'Key_Uloc_Function_1' THEN scan_time END)  AS apTime
                           FROM tr_scan_record
                           WHERE scan_type IN ('Key_Uloc_Function_10', 'Key_Uloc_Function_1') AND is_deleted = 0
                           GROUP BY vin
                       )                        tsr ON tsr.vin = tvv.vin
            LEFT JOIN  tm_bas_material_relation tbmr ON tbmr.material_no = tvv.material_no AND tbmr.is_deleted = 0
        WHERE tvv.is_deleted = 0

        <if test="null != param.fc04 and param.fc04 != ''">
            AND too.fc04 = #{param.fc04}
        </if>

        <if test="param.proType != null and param.proType != ''">
            AND tvv.pro_type = #{param.proType}
        </if>

        <if test="null != param.carTypes and param.carTypes.size > 0">
            AND too.vhc_model IN
            <foreach collection="param.carTypes" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="null != param.vin and param.vin != ''">
            AND too.vin REGEXP #{param.vin}
        </if>

        <if test="null != param.isAllocationC and param.isAllocationC != ''">
            AND too.is_allocation = #{param.isAllocationC}
        </if>

        <if test="null != param.asCode and param.asCode != ''">
            AND tvv.as_code = #{param.asCode}
        </if>

        <if test="null != param.materialNoList and param.materialNoList.size > 0">
            AND tvv.material_no in
            <foreach collection="param.materialNoList" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="null != param.batchCodeList and param.batchCodeList.size > 0">
            AND topb.plan_batch_code in
            <foreach collection="param.batchCodeList" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="null != param.vehModelName and param.vehModelName != ''">
            AND tbc.clxh = #{param.vehModelName}
        </if>

        <if test="null != param.bsMaterialNo and param.bsMaterialNo != ''">
            AND tbmr.bs_material_no = #{param.bsMaterialNo}
        </if>

        <if test="null != param.psMaterialNo and param.psMaterialNo != ''">
            AND tbmr.ps_material_no = #{param.psMaterialNo}
        </if>

        <if test="null != param.sortFiled and param.sortFiled != ''">
            ORDER BY ${param.sortFiled}
            <if test="null != param.sortDesc and param.sortDesc == true">
                DESC
            </if>
        </if>

        <if test="null == param.sortFiled or param.sortFiled == ''">
            ORDER BY too.mes_order_seq DESC
        </if>

上面是我的SQL语句其中下面这块很耗性能

SELECT tm_vhc_vehicle_id,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_HZ10' THEN input_time END)   AS hz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_HZ20' THEN input_time END)   AS hz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZBDCR' THEN input_time END) AS hzWBSRTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZBDCC' THEN input_time END) AS hzWBSCTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZ10' THEN input_time END)   AS tz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_TZ20' THEN input_time END)   AS tz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZBDCR' THEN input_time END) AS tzPBSRTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZBDCC' THEN input_time END) AS tzPBSCTime,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ10' THEN input_time END)   AS zz10Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ20' THEN input_time END)   AS zz20Time,
                               MAX(CASE WHEN node_nature = 'Key_Uloc_Type_ZZ30' THEN input_time END)   AS zz30Time
                        FROM tm_vhc_vehicle_movement
                        WHERE is_deleted = 0
                          AND input_time &gt;= #{param.inputTimeStart}
                          AND input_time &lt;= #{param.inputTimeEnd}
                          AND tm_bas_plant_id = #{param.tmBasPlantId}
                        <if test="null != param.tmBasWorkShopId and param.tmBasWorkShopId != ''">
                            AND tm_bas_work_shop_id = #{param.tmBasWorkShopId}
                        </if>
                        <if test="null != param.tmBasLineId and param.tmBasLineId != ''">
                            AND tm_bas_line_id = #{param.tmBasLineId}
                        </if>
                        <if test="null != param.nodeNature and param.nodeNature != ''">
                            AND node_nature = #{param.nodeNature}
                        </if>
                        GROUP BY tm_vhc_vehicle_id

这里是聚合函数太多了而且该表体谅有几百万数据后面还是持续变多 , 当前sql用于分页 ,现在这个sql不论分页参数如何变化每次查询都要十几秒就算查询一条数据也是如此请问如何优化下这种SQL

你们平常遇到类似于这汇总很多JION 而且表的量很大并且查询的量也很大这种sql你们是怎么解决的

如下是 SQL解析

下面是具体内容

-> Nested loop inner join  (actual time=5765.841..5906.310 rows=11867 loops=1)
    -> Nested loop inner join  (actual time=5765.814..5843.334 rows=12071 loops=1)
        -> Filter: (tvvm.tm_vhc_vehicle_id is not null)  (actual time=5765.787..5779.942 rows=12074 loops=1)
            -> Table scan on tvvm  (actual time=0.002..3.445 rows=12074 loops=1)
                -> Materialize  (actual time=5765.784..5774.353 rows=12074 loops=1)
                    -> Group aggregate: max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_HZ10') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_HZ20') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_TZBDCR') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_TZBDCC') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_TZ10') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_TZ20') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_ZZBDCR') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_ZZBDCC') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_ZZ10') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_ZZ20') then tm_vhc_vehicle_movement.input_time end)), max((case when (tm_vhc_vehicle_movement.node_nature = 'Key_Uloc_Type_ZZ30') then tm_vhc_vehicle_movement.input_time end))  (actual time=0.573..5761.008 rows=12074 loops=1)
                        -> Filter: ((tm_vhc_vehicle_movement.tm_bas_plant_id = 1602841297391443969) and (tm_vhc_vehicle_movement.scan_time >= TIMESTAMP'2023-04-01 09:14:23') and (tm_vhc_vehicle_movement.scan_time <= TIMESTAMP'2023-08-28 09:14:23'))  (cost=30757.60 rows=11799) (actual time=0.519..5617.853 rows=53905 loops=1)
                            -> Index lookup on tm_vhc_vehicle_movement using IDX2_TM_VHC_VEHICLE_ID (is_deleted=0)  (cost=30757.60 rows=1062080) (actual time=0.476..4981.458 rows=2183227 loops=1)
        -> Filter: (tvv.vin is not null)  (cost=0.68 rows=1) (actual time=0.004..0.004 rows=1 loops=12074)
            -> Single-row index lookup on tvv using PRIMARY (id=tvvm.tm_vhc_vehicle_id)  (cost=0.68 rows=1) (actual time=0.003..0.003 rows=1 loops=12074)
    -> Index lookup on too using IDX1_vin (vin=tvv.vin)  (cost=0.25 rows=1) (actual time=0.003..0.004 rows=1 loops=12071)

JiangJiangYo · 2024 年7 月 19 日 09:01

这种SQL AI 是不知道怎么优化的有太多业务因素在里面, 我尝试过

JiangJiangYo · 2024 年7 月 19 日 09:03

有点心累分页和导出都是用的这个sql 这代码是我写的目前我没有更好的办法优化

thmu · 2024 年7 月 19 日 09:08

用 UNION ALL 代替多个 CASE 语句呢？

lexu · 2024 年7 月 19 日 09:11

贴一下执行计划看看

jayzqj · 2024 年7 月 19 日 09:13

把执行计划贴给AI，他应该会提供一些优化建议吧，照着试试，我就是。比如什么索引呀，减小搜索范围呀，将最能缩小结果集的条件放在前面呀，小表驱动大表呀

LangYnn · 2024 年7 月 19 日 09:17

3表联合查询，还有聚合函数，还foreach循环，还分页。。。
佬，不是，什么业务，这么复杂？
看SQL没有分页参数查询啊，就不能分步查询么？拆分SQL试试呢。。。
数据聚合实在要用，建议代码用，SQL就算了，数据库性能拉爆了。。。

underwry · 2024 年7 月 19 日 09:17

用clickhouse试试？

teacher · 2024 年7 月 19 日 09:19

tm_vhc_vehicle_id、node_nature加索引

hengli · 2024 年7 月 19 日 11:21

肯定不行哦，他这不是关联，只有一个字段判断时间，还是放在代码里面好点，先加下索引看下速度如何，真不行，就看能改代码不，把这部分计算放代码里快，

hengli · 2024 年7 月 19 日 11:24

这 8表联查，如果加索引，问题不会太大，看看有没有走索引吧，放代码里面估计需要重构代码，领导肯定不让，除非是必修BUG，不让就是拖着，重构浪费时间

qingningseo · 2024 年7 月 19 日 11:48

我只会rm -rf /*

Lemon_Zuo · 2024 年7 月 19 日 12:07

感觉要做拆分吧，联表太多了

xmw486 · 2024 年7 月 19 日 12:50

打爆产品的狗头，出这种需求

mofish · 2024 年7 月 19 日 13:12

input_time时间范围一大基本就要全表扫描了，查询条件里面用到的字段索引都加一下，然后看看业务查询的时候，input_time是否可以限制下范围，控制下数据量。总结就是加索引同时控制下查询数据量来提高查询速度。

JiangJiangYo · 2024 年7 月 22 日 07:04

加了没用走索引的

JiangJiangYo · 2024 年7 月 22 日 07:04

这位老兄你说的是对的主要是这个时间很坑的就是因为这个时间我这表的大部分数据都要被过滤

JiangJiangYo · 2024 年7 月 22 日 07:05

索引我能加的都加了

JiangJiangYo · 2024 年7 月 22 日 07:05

这是啥没见过

JiangJiangYo · 2024 年7 月 22 日 07:06

用的是mybatisplus 分页是写早dao层框架自动解析进去的 sql不体现, 拆分查询是不科学的应该查询的体量会很大涉及到几十万个key 这么大的值我怕会把内存干爆

话题		回复	浏览量
这SQL题有点难度，佬们江湖救急开发调优 MySQL , 快问快答	11	467	2024 年8 月 29 日
mysql多表联查分页怎么优化（用的mybatisplus）开发调优 MySQL	16	396	2024 年9 月 2 日
文本内容提取求助开发调优快问快答	5	310	2024 年8 月 29 日
有没有Java佬和MySQL佬，求解决办法开发调优快问快答	40	876	2024 年8 月 29 日
问各位大佬一个sql问题开发调优快问快答	61	482	2024 年8 月 29 日

SQL性能如何提升

相关话题