基于wordpress多商户上海何鹏seo
记一次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
转载说明：务必注明来源，附带本人博客连接。