前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL优化实战

SQL优化实战

作者头像
田维常
发布2020-02-13 15:54:36
4570
发布2020-02-13 15:54:36
举报
文章被收录于专栏:Java后端技术栈cwnait

正文

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

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

首先描述下具体的问题

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

下面是SQL的内容

代码语言:javascript
复制
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来展示。

添加索引

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

代码语言:javascript
复制
      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如下:

代码语言:javascript
复制
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 删除

本文分享自 Java后端技术栈 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 添加索引
  • 重构SQL
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档