记一次sql查询优化

记一次sql查询优化

前言
这是我在这个网站整理的笔记,有错误的地方请指出，关注我，接下来还会持续更新。

作者：神的孩子都在歌唱

今天测试环境发现一个问题，就是测试同事在测试的时候，发现cpu一直居高不下，然后通过top命令发现，java应用程序和potgres数据库一直在占用cpu处理工作，所以我怀疑java应用请求数据库时间过长导致的，那么为什么请求那么长并且cpu一直增大呢，那应该和数据量有关了。

果不其然，看了一眼数据库，发现有一张表里面有15万条数据，这是一张告警消息和内容的关联表warn_message_content,存储的是告警的消息内容。可是这点数据量也不应该出现这种情况的，然后我去开了一眼代码。

没优化前的sql写法如下

    <select id="query"resultMap="Results">SELECT t.*, (SELECT COUNT(*) FROM warn_message_content WHERE message_id =  #{query.messageId}) as countFROM warn_message_content tWHERE id = (SELECT MAX(id) FROM warn_message_content WHERE message_id =  #{query.messageId});</select>

根据上面sql，我们可以大概知道需求是什么，它是需要根据告警消息的messageId去关联表里面查找总数和最新一条告警内容。

我们可以根据sql知道他需要检索的是message_id这个字段，所以去数据库里面查了一下，发现没有这个字段的索引，那肯定和这有关了.

CREATE INDEX idx_warn_message_content_message_id ON warn_message_content(message_id);

通过以上命令添加索引后，查询效率直接升到毫秒

这样问题就解决了。

可是我们可以发现这条sql写的有些问题：

• 多个子查询：查询中使用了多个子查询。首先是用于获取最大 id 的子查询，然后是用于计算总数的子查询。每次执行时，这些子查询可能会重复扫描 warn_message_content 表，导致性能问题。
• 效率低：嵌套子查询通常会导致查询性能降低，特别是在数据量很大的情况下。数据库需要执行多个子查询并将结果合并，这会增加计算负担。

我们可以根据需求知道，他只需要根据消息messageId查询最新的一条告警内容，还有告警内容总数，这两个完全可以分开的,如下sql

-- 获取最大 ID
SELECT t.*
FROM warn_message_content t
WHERE message_id = #{query.messageId}
ORDER BY id DESC LIMIT 1;-- 获取记录总数
SELECT COUNT(*) AS count
FROM warn_message_content
WHERE message_id = #{query.messageId};

这样子分开不但能够避免多个子查询，还能够提高代码的可读性。

可是这样子还有个缺点，如果 warn_message_content 表在高频率写入时，没办法保证数据一致性。意思就是如果两个查询在不同的时间点执行，可能会导致 最新的告警内容 和 COUNT(*) 查询结果不一致。解决方法是使用事务，可以确保查询的结果在同一个事务内保持一致。

作者：神的孩子都在歌唱

本人博客：https://blog.csdn.net/weixin_46654114

转载说明：务必注明来源，附带本人博客连接。