一次SQL优化的体验

点击上方 "程序员小乐"关注, 星标或置顶一起成长

每天凌晨00点00分, 第一时间与你相约

每日英文

If you’re brave to say “good bye”, life will reward you with a new “hello”. -- Paulo Coelho 

如果你勇于对过去说“拜拜”，生活就会回赠给你一个新的“哈喽”。

每日掏心话

人的一生，没有谁是事事都是得意，真正的幸福那也是是脱离物质追求的一种心灵感受，幸福不受地位、权势、财富的约束，那就是人生路上坚强的表现。

来自：AmosZhu | 责编：乐乐

链接：jianshu.com/p/6dfc1d6ffcef

程序员小乐(ID:study_tech)第 737 次推文   图片来自 Pexels

往日回顾：2019年支付宝新增了4亿行代码堪比80个《魔兽世界》

   正文   

今天遇到一个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，这个速度暂时是可以接受的了

欢迎在留言区留下你的观点，一起讨论提高。如果今天的文章让你有新的启发，学习能力的提升上有新的认识，欢迎转发分享给更多人。

欢迎各位读者加入程序员小乐技术群，在公众号后台回复“加群”或者“学习”即可。

猜你还想看

阿里、腾讯、百度、华为、京东最新面试题汇集

Redis Sentinel 架构原理详解

面试官问：平常你是怎么对Java服务进行调优的？

程序员喜欢的 5 款最佳最牛掰代码比较工具

关注「程序员小乐」，收看更多精彩内容

嘿，你在看吗？

\