如何优化统计网站发往商家消息的慢SQL查询语句

问题1：把JOIN替换为LEFT JOIN能不能减少返回行数？ #

不能。
INNER JOIN只会返回两张表匹配上关联条件的行，LEFT JOIN会保留左表（此处是messages表）的所有行，哪怕没有匹配到右表的行也会返回空值填充，返回行数只会和原来持平甚至更多，不会减少。你EXPLAIN结果无差异是因为当前数据中所有messages的from_to都能匹配到users表记录、所有users都能匹配到businesses表记录，所以两种写法的执行逻辑暂时一致，但和你要减少行数的需求完全背离。

问题2：返回行数异常大、查询慢的核心原因 #

你当前的SQL没有加任何WHERE过滤条件、也没有分页限制，会直接查询messages表的所有历史消息关联数据，相当于每次查询都要拉取全量消息明细，这就是慢查询的核心原因。慢日志中单次查询平均扫描38万行、累计扫描7000多万行也验证了这一点。

具体优化方案 #

方案1：如果需求是统计消息数量，直接用聚合查询，不要返回全量明细 #

你要统计数量不需要拉取每一条消息的内容、发送时间等字段，直接用COUNT聚合即可，查询性能会提升几十上百倍：

-- 示例：统计每个商家的总消息量
SELECT
  businesses.name AS BusinessName,
  COUNT(messages.id) AS message_total
FROM
  messages
  JOIN users ON messages.from_to = users.id
  JOIN businesses ON users.business_id = businesses.id
-- 如需按时间范围统计，一定要加时间过滤，能直接砍掉大部分扫描行数
WHERE messages.created >= '2024-01-01'
GROUP BY businesses.id, businesses.name

该查询返回的行数仅和商家数量一致，远低于原SQL的全量消息行数。

方案2：如果确实需要查询消息明细，必须加过滤和分页 #

• 优先加时间范围过滤，比如只查询近30天的消息，避免全表扫描
• 加LIMIT分页，每次查询固定条数，比如LIMIT 0, 100只返回前100条
• 去掉无用字段：你当前SELECT里有两个字段别名都是BusinessName，重复冗余，不需要的字段不要查询，比如不需要消息内容就去掉strip_tags(messages.message)，减少函数计算和数据传输消耗

方案3：添加索引降低扫描行数 #

给关联和过滤字段加索引，能进一步降低查询扫描的行数：

• 给messages表添加联合索引：ALTER TABLE messages ADD INDEX idx_fromto_created(from_to, created)
• 给users表的business_id字段添加普通索引：ALTER TABLE users ADD INDEX idx_business_id(business_id)

内容的提问来源于stack exchange，提问作者jdk