优化按手机号分组获取最新对话的MAX()慢查询请求

原查询问题分析 #

原查询先对全表按phone分组取最大id，再在外层过滤chat、status、seller_id，相当于先处理全表数据再筛选，数据量过大导致查询缓慢。

优化方案 #

1. 提前过滤条件，缩小分组范围

将外层的过滤条件移到子查询中，让分组仅针对符合条件的数据，而非全表：

SELECT * 
FROM messages 
WHERE id IN (
    SELECT max(id) 
    FROM messages 
    WHERE chat = :ch AND status = :st AND seller_id = :seller
    GROUP BY phone
) 
ORDER BY created_at DESC

2. 创建针对性复合索引

创建包含过滤字段、分组字段、聚合字段的复合索引，让数据库无需全表扫描即可完成分组和筛选：

-- 基础优化索引
CREATE INDEX idx_messages_seller_chat_status_phone_id ON messages (seller_id, chat, status, phone, id);

索引顺序逻辑：先按高频过滤字段（seller_id、chat、status）快速筛选数据，再按phone分组，最后直接取最大id，避免回表查询。

如果created_at与id自增顺序完全一致（新消息id越大，created_at越晚），可追加created_at到索引末尾，消除排序阶段的文件排序：

-- 带排序优化的索引
CREATE INDEX idx_messages_seller_chat_status_phone_id_created ON messages (seller_id, chat, status, phone, id, created_at);

3. 改用JOIN替代IN子查询

MariaDB对IN子查询的优化效率通常不如JOIN，改用关联查询可提升执行效率：

SELECT m.*
FROM messages m
JOIN (
    SELECT max(id) AS max_id
    FROM messages
    WHERE chat = :ch AND status = :st AND seller_id = :seller
    GROUP BY phone
) t ON m.id = t.max_id
ORDER BY m.created_at DESC

4. 简化排序逻辑（可选）

若id为自增主键，且created_at随id递增，可直接按id DESC排序，避免对created_at的额外排序操作：

SELECT m.*
FROM messages m
JOIN (
    SELECT max(id) AS max_id
    FROM messages
    WHERE chat = :ch AND status = :st AND seller_id = :seller
    GROUP BY phone
) t ON m.id = t.max_id
ORDER BY m.id DESC

内容的提问来源于stack exchange，提问作者Ebrahim P. Leite