一次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，这个速度暂时是可以接受的了
欢迎在留言区留下你的观点，一起讨论提高。如果今天的文章让你有新的启发，学习能力的提升上有新的认识，欢迎转发分享给更多人。
欢迎各位读者加入程序员小乐技术群，在公众号后台回复“加群”或者“学习”即可。
猜你还想看
\