如何编写SQL查询两用户完整私信对话并避免MySQL服务卡死

问题原因 #

• 逻辑运算符优先级错误
  MySQL中AND的优先级高于OR，你编写的WHERE条件实际执行逻辑为：

(t.author_id = 37598 AND t.user_id = 74403)
OR (t.author_id = 74403 AND t.user_id = 37598 AND t.msg_id = p.msg_id AND u.user_id IN (37598, 74403))

前半部分条件没有关联其他表的约束，会直接生成三张表的笛卡尔积，数据量指数级膨胀导致数据库负载拉满卡住，并非真正的死循环。

• 表关联逻辑错误
  使用逗号做隐式连接，且用户表仅用IN做约束的写法，会导致每条消息同时匹配到发件人和收件人两条用户记录，消息重复返回。

正确SQL实现 #

SELECT 
  p.MSG_ID,
  p.MSG_TEXT,
  p.MESSAGE_TIME,
  u.USER_ID AS sender_id,
  u.username AS sender_name,
  t.USER_ID AS receiver_id
FROM phpbb3_privmsgs_to t
INNER JOIN privmsgs p 
  ON t.MSG_ID = p.MSG_ID
INNER JOIN phpbb3_users u 
  ON p.AUTHOR_ID = u.USER_ID
WHERE 
  -- 括号包裹会话匹配条件，避免优先级问题
  (t.AUTHOR_ID = 37598 AND t.USER_ID = 74403)
  OR 
  (t.AUTHOR_ID = 74403 AND t.USER_ID = 37598)
ORDER BY p.MESSAGE_TIME ASC -- 按时间正序排列，最早的消息在顶部，符合聊天页展示习惯

如果需要最新消息在前，将ASC改为DESC即可。

性能优化建议 #

• 给phpbb3_privmsgs_to表添加联合索引idx_author_user(AUTHOR_ID, USER_ID)，可大幅提升会话筛选效率
• 确保privmsgs.MSG_ID、phpbb3_users.USER_ID为主键或唯一索引，默认表结构已满足该要求

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