MySQL关联查询匹配首条记录即终止的替代方案咨询

问题背景 #

需要获取聊天室列表并按每个聊天室的最新聊天记录时间排序，但因chat表数据量过大，直接关联查询性能严重下降。以下针对你的问题给出具体优化方案：

问题1：有没有其他反向查询字段的方法？ #

除了反向扫描chat表找最新记录，推荐以下几种更高效的替代方案：

1. 联合索引+分组子查询

先给chat表创建联合索引：

CREATE INDEX idx_chat_room_created_at ON chat(chat_room_id, created_at DESC);

通过分组子查询快速获取每个聊天室的最新聊天时间，再关联chat表拿到对应记录：

SELECT cr.*, c.content, c.created_at
FROM chat_room cr
LEFT JOIN (
    SELECT chat_room_id, MAX(created_at) AS latest_created_at
    FROM chat
    GROUP BY chat_room_id
) AS latest_chat_time ON cr.id = latest_chat_time.chat_room_id
LEFT JOIN chat c 
    ON cr.id = c.chat_room_id 
    AND c.created_at = latest_chat_time.latest_created_at
ORDER BY latest_chat_time.latest_created_at DESC NULLS LAST;

2. 窗口函数筛选最新记录

利用ROW_NUMBER()窗口函数给每个聊天室的聊天记录按时间降序编号，直接取编号为1的最新记录：

SELECT cr.*, latest_chat.content, latest_chat.created_at
FROM chat_room cr
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY chat_room_id ORDER BY created_at DESC) AS rn
    FROM chat
) AS latest_chat ON cr.id = latest_chat.chat_room_id AND latest_chat.rn = 1
ORDER BY latest_chat.created_at DESC NULLS LAST;

注：该方案需MySQL 8.0+版本支持，配合上述联合索引可大幅提升效率

3. 冗余最新消息字段（性能最优）

在chat_room表中新增冗余字段存储最新聊天记录的关键信息：

ALTER TABLE chat_room 
ADD COLUMN last_chat_created_at datetime(6),
ADD COLUMN last_chat_content varchar(255);

每次插入chat记录时，通过触发器或业务代码更新对应chat_room的冗余字段：

-- 触发器示例
DELIMITER //
CREATE TRIGGER update_chat_room_last_chat
AFTER INSERT ON chat
FOR EACH ROW
BEGIN
    UPDATE chat_room 
    SET last_chat_created_at = NEW.created_at,
        last_chat_content = NEW.content
    WHERE id = NEW.chat_room_id;
END //
DELIMITER ;

之后查询聊天室列表时无需关联chat表，直接用冗余字段排序：

SELECT * FROM chat_room ORDER BY last_chat_created_at DESC NULLS LAST;

问题2：有没有其他方法可在关联两张表时，匹配到指定字段就终止查询？ #

以下方法可实现"找到每个聊天室的第一条匹配记录就终止查询"：

1. 关联子查询+LIMIT 1

对每个chat_room单独关联chat表并限制只取1条最新记录，MySQL会在找到第一条匹配记录后终止该聊天室的查询：

SELECT cr.*, latest_chat.content, latest_chat.created_at
FROM chat_room cr
LEFT JOIN (
    SELECT * FROM chat c 
    WHERE c.chat_room_id = cr.id 
    ORDER BY created_at DESC 
    LIMIT 1
) AS latest_chat ON 1=1
ORDER BY latest_chat.created_at DESC NULLS LAST;

配合idx_chat_room_created_at索引，该方案的查询效率接近冗余字段方案

2. 用EXISTS子查询判断最新时间（仅需排序时）

如果只需要按最新聊天时间排序，不需要获取聊天内容，可直接用EXISTS子查询获取时间：

SELECT cr.*,
    (SELECT created_at FROM chat c WHERE c.chat_room_id = cr.id ORDER BY created_at DESC LIMIT 1) AS latest_created_at
FROM chat_room cr
ORDER BY latest_created_at DESC NULLS LAST;

附表结构 #

Chat表 #

CREATE TABLE `chat` (
  `chat_room_id` bigint DEFAULT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `member_id` bigint DEFAULT NULL,
  `subject_id` bigint DEFAULT NULL,
  `content` varchar(255) NOT NULL,
  `message_type` enum('POST','IMAGE','TEXT') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK44b6elhh512d2722l09i6qdku` (`chat_room_id`),
  KEY `FKgvc5hrt0h18xk63qosss3ti30` (`member_id`),
  CONSTRAINT `FK44b6elhh512d2722l09i6qdku` FOREIGN KEY (`chat_room_id`) REFERENCES `chat_room` (`id`),
  CONSTRAINT `FKgvc5hrt0h18xk63qosss3ti30` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
);

Chat Room表 #

CREATE TABLE `chat_room` (
  `member_cnt` int DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `room_type` enum('GROUP','PERSONAL') DEFAULT NULL,
  PRIMARY KEY (`id`)
);

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