SQL优化实战

田维常

发布于 2020-02-13 15:54:36

4570

发布于 2020-02-13 15:54:36

文章被收录于专栏：Java后端技术栈cwnait

正文

今天遇到一个SQL优化的问题，记录下来

测试部门反映，有个功能的查询很缓慢，需要支持排查下

首先描述下具体的问题

数据库中一张表的大概有700多条记录，业务需要模拟一个类似属性树形的数据结构，需要查询所有父类的列表数据，查询出来符合记录的大概有400多条，目前测试点击查询，所需要的时间大概在4s多，这个效率实在太慢了；

下面是SQL的内容

SELECT
      (SELECT
        MAX(f.url)
      FROM
        order_definnition o
         JOIN order_def_form_relation d
          ON o.id = d.pid
          AND d.is_interflow = 1
          AND o.is_deleted = 0
          AND d.is_deleted = 0
         JOIN form f
          ON f.id = d.form_id
          AND f.is_deleted = 0
      WHERE o.resource_code = t.from_oddf_class_no) AS url,
      (SELECT
        MAX(f.url)
      FROM
        order_definnition o
         JOIN order_def_form_relation d
          ON o.id = d.pid
          AND d.is_audit = 1
          AND o.is_deleted = 0
          AND d.is_deleted = 0
         JOIN form f
          ON f.id = d.form_id
          AND f.is_deleted = 0
      WHERE o.resource_code = t.from_oddf_class_no) AS source_url,
      id
    FROM
      unicom_send_recv_order t
    WHERE is_deleted = 0
      AND t.id IN
      (SELECT
        (SELECT
          b.id
        FROM
          unicom_send_recv_order b
        WHERE b.is_deleted = 0
          AND b.unicom_first_id = a.unicom_first_id
          AND (
            b.data_direction = 0
            OR (
              b.data_direction = 1
              AND (
                b.order_status = '1001516'
                OR b.order_status = '1001515'
              )
            )
            OR b.is_start_line = 1
          )
        ORDER BY b.send_recv_count DESC
        LIMIT 1)
      FROM
        unicom_send_recv_order a
      WHERE a.is_deleted = 0
      GROUP BY a.unicom_first_id)
    ORDER BY t.created_time DESC

这个sql的逻辑，在满足一定的条件下，选择send_recv_count最大的那条记录的id来展示。

添加索引

在不影响业务的条件下，我们通过添加索引来试试，查看表中的关联字段，我们需要对如下的字段添加索引

      ALTER TABLE `sceo`.`unicom_send_recv_order`   
      ADD  INDEX `index_unicom_first_id` (`unicom_first_id`),
      ADD  INDEX `index_from_oddf_class_no` (`from_oddf_class_no`),
      ADD  INDEX `index_send_recv_count` (`send_recv_count`);
      ALTER TABLE `sceo`.`order_def_form_relation`   
      ADD  INDEX `index_pid` (`pid`),
      ADD  INDEX `index_form_id` (`form_id`);

再次执行该上面的sql语句,发现查询的效率变快了了，耗时2700+ms数，这个时间还是满足不了业务的需要

重构SQL

分析上述的sql，发现在子查询中通过排序来获取send_recv_count最大的记录，然后再分组查询，这个是主要导致查询效率缓慢的原因，我们可以改写sql语句来减少分组和排序

我们利用变量来为排序标记，然后获取变量为1的记录，利用这种方式来避免排序分组, 重构的sql如下：

SELECT
      (SELECT
        MAX(f.url)
      FROM
        order_definnition o
         JOIN order_def_form_relation d
          ON o.id = d.pid
          AND d.is_interflow = 1
          AND o.is_deleted = 0
          AND d.is_deleted = 0
         JOIN form f
          ON f.id = d.form_id
          AND f.is_deleted = 0
      WHERE o.resource_code = t.from_oddf_class_no) AS url,
      (SELECT
        MAX(f.url)
      FROM
        order_definnition o
         JOIN order_def_form_relation d
          ON o.id = d.pid
          AND d.is_audit = 1
          AND o.is_deleted = 0
          AND d.is_deleted = 0
         JOIN form f
          ON f.id = d.form_id
          AND f.is_deleted = 0
      WHERE o.resource_code = t.from_oddf_class_no) AS source_url, id
    FROM
      unicom_send_recv_order t
    WHERE is_deleted = 0
       AND EXISTS
      (SELECT tt.id from (SELECT
        b.id,
        b.unicom_first_id,
        send_recv_count,
    IF (
        @pre_course_id = b.unicom_first_id,
        @cur_rank := @cur_rank + 1,
        @cur_rank := 1
    ) ranking,
     @pre_course_id := b.unicom_first_id
    FROM
        unicom_send_recv_order b,
        (SELECT
                @cur_rank := 0,
                @pre_course_id := NULL
        ) r
    where b.is_deleted = 0 
          AND (b.data_direction = 0
           OR (b.data_direction = 1
              AND (b.order_status = '1001516'
                OR b.order_status = '1001515'))
            OR b.is_start_line = 1)
    ORDER BY
        unicom_first_id,
        send_recv_count DESC) tt where tt.ranking = 1
        AND tt.id = t.id)
    ORDER BY t.created_time DESC

在重构后的sql中，可以看到我们排序之后的记录添加一个序列，然后获取序列的第一条来作为我们现实的记录，然后将in改为exists,这样优化后的查询就变得效率很高了，经过测试，耗时降到了200ms，这个速度暂时是可以接受的了

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-01-05，如有侵权请联系 cloudcommunity@tencent.com 删除

sql