SQL优化方案

xukun2025-03-072025-03-26

车辆综合查询报表接口优化

背景：

通过日志平台对车辆综合查询接口的响应时间进行分析，发现当前接口的平均响应时间比较长，为了提高用户使用的体验，需要对接口进行优化

目的：

整理用户权限和组织权限之间的区别和涉及到的表信息
基础节点信息的表改造，添加排序字段，优化查询速度
相关接口：
- /car/queryMonitorList
- /optimize/queryFuelCarList

对接口调用链路分析：

分析发现，主要还是SQL层面的响应时间比较长，所以从SQL进行优化

流程：

首先调用车辆综合报表方法xxQueryFuelCarList
进入查询车辆车次计数xxCarAllCount
遍历车辆节点表xx_Node_CarInFo_Car找到对应节点

对应节点车辆的基础信息表xx_Node_relationship

基础节点SQL

-- 执行查询，查找根节点及其所有后代节点
SELECT id, name
FROM (
    SELECT t1.id,
           t1.pid,
           IF(FIND_IN_SET(t1.pid, @pids) > 0, @pids := CONCAT(@pids, ',', t1.id), 0) AS ischild,
           t1.name
    FROM (
         SELECT id, pid, name
         FROM rl_organize_link_relation
         WHERE del = 0
         ORDER BY pid, id
    ) AS t1
) AS da
WHERE ischild != 0 OR id = 1;

主要瓶颈：

FIND_IN_SET查询@pids中是否有t1.pid的结果，基础节点表被很多表关联查询，查询时需要对每个节点进行遍历，在数据量多的情况下效率低，多表级联查询慢问题

优化方案：

优化SQL查询：减少多表级联查询.sql拆分优化
数据库索引优化：对应索引是否命中，索引优化
表设计优化：增加组织节点排序字段舍弃递归查询，直接查询已经是排好序的数据结构；
kudu优化：位置云查询动态数据的性能优化

首先从SQL结构进行优化：

对SQL进行分析，得出结论需要对IF(find_in_set(pid, @pids)函数进行改造

思路：添加一个排序字段，将节点进行分层，减少每次查询所扫描的节点数量，观察优化后的查询时间再考虑其他方案

具体执行如下：

SQL优化（提升82%）：

优化前-查询记录28471条，查询时间8.617s

SELECT id, name
FROM (
         SELECT t1.id,
                pid,
                IF(find_in_set(pid, @pids) > 0, @pids := concat(@pids, ',',id), 0) AS ischild,
                `name`
         FROM (SELECT id, pid, `name`
               FROM rl_organize_link_relation
                        AS t
               WHERE t.del = 0
               ORDER BY pid, id) AS t1,
              (SELECT @pids := 1) AS t2) AS da
WHERE ischild != 0 OR id = 1;

优化后-查询记录28471条，查询时间1.549s

SELECT id, name, pid AS parent_id
FROM (
      SELECT id, pid, name
      FROM rl_organize_link_relation
      WHERE del = 0
      ORDER BY pid, id
) AS sorted,
(SELECT @pv := #{id}) AS init
WHERE FIND_IN_SET(pid, @pv)
  AND LENGTH(@pv := CONCAT(@pv, ',', id))
UNION ALL
-- 加上起始节点（根节点）
SELECT id, name, pid AS parent_id
FROM rl_organize_link_relation
WHERE id = #{id};