FIND_IN_SET性能优化之列转行

最新推荐文章于 2024-10-15 23:14:04 发布

xiaoxiao树

最新推荐文章于 2024-10-15 23:14:04 发布

阅读量1w

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/u010719917/article/details/78492937

版权

本文探讨了FIND_IN_SET函数在SQL查询中的性能问题，并提出通过将列转换为行来优化查询效率。举例说明了一个优化过程，原本查询耗时8秒，经过转换后，查询时间降低到0.04秒，显著提升了执行速度。

摘要由CSDN通过智能技术生成

FIND_IN_SET 性能优化

select v_data.*, GROUP_CONCAT(t_fa.Staff_Name) Staff_Name
  from (select REC.Riqi_Date,
               REC.Vehicle_Id,
               t_vc.Vehicle_No,
               REC.DeliveryLoading_Hdr_Id,
               t_hd.DeliveryLoading_No,
               REC.Operate_staff,
               REC.Cart_check_State,
               (select a.Value_Desc
                  from Fd_Field_Dtl a
                 where a.Field_Name = 'Cart_check_State'
                   and a.Value_Data = REC.Cart_check_State) Cart_check_StateName,
               REC.ElectricalEquipment_State,
               REC.Chassis_State,
               REC.Engine_state,
               REC.Exterior_State,
               REC.Created_Time,
               REC.Submit_Time,
               REC.Repair_Item
          from Rec_a           REC,
               Fd_Vb               t_vc,
               Bill_Dc t_hd
         where REC.Vehicle_Id = t_vc.Vehicle_Id
           and t_hd.Operator_Id = '174660684877367'
           and REC.DeliveryLoading_Hdr_Id = t_hd.DeliveryLoading_Hdr_Id
           and REC.Cart_check_State < 4
         order by REC.Riqi_Date desc) v_data
  LEFT JOIN Fd_Staff t_fa
    on FIND_IN_SET(t_fa.Staff_Id, v_data.Operate_staff) > 0
 GROUP BY v_data.DeliveryLoading_Hdr_Id
 order by v_data.Riqi_Date;

查看执行计划

+----+--------------------+------------+--------+-----------------------------------------------------------+---------------------------+---------+--------------------------------+-------+-----------------------------------------------------------------+
| id | select_type        | table      | type   | possible_keys                                             | key                       | key_len | ref                            | rows  | Extra                                                           |
+----+--------------------+------------+--------+-----------------------------------------------------------+---------------------------+---------+--------------------------------+-------+-----------------------------------------------------------------+
|  1 | PRIMARY            | <derived2> | ALL    | NULL                                                      | NULL                      | NULL    |

最低0.47元/天解锁文章