NYT词云MySQL查询优化：Group By提速至5秒方案咨询

场景背景 #

NYT词云项目中，AWS托管的MySQL 8.0.33（InnoDB）存储了645万+条分词数据，核心需求是查询指定日期范围内所有文章的前100个高频分词及总数量。现有单字段索引（publish_date、token.name）已将速度提升一倍，但当前查询仍耗时约100秒，瓶颈集中在GROUP BY操作，常规索引扫描优化无效。

具体优化方案 #

1. 预聚合表重构（最推荐，直接降维解决分组瓶颈） #

当前Token表为单分词单记录结构，全量分组聚合必然耗时。新增预聚合表按维度提前统计词频：

-- 创建预聚合表
CREATE TABLE TokenAgg (
    aggID INT AUTO_INCREMENT PRIMARY KEY,
    tokenName CHAR(100) NOT NULL,
    dateID INT NOT NULL,
    totalCount INT NOT NULL DEFAULT 0,
    FOREIGN KEY (dateID) REFERENCES Date(dateID),
    UNIQUE KEY idx_token_date (tokenName, dateID)
);

-- 初始化数据（首次执行）
INSERT INTO TokenAgg (tokenName, dateID, totalCount)
SELECT t.name, a.dateID, COUNT(*)
FROM Token t
JOIN Article a ON t.articleID = a.articleID
GROUP BY t.name, a.dateID
ON DUPLICATE KEY UPDATE totalCount = VALUES(totalCount);

-- 新增分词时同步更新（业务代码/触发器）
INSERT INTO TokenAgg (tokenName, dateID, totalCount)
VALUES ('example_token', 123, 1)
ON DUPLICATE KEY UPDATE totalCount = totalCount + 1;

查询时直接基于预聚合表统计，避免全量分组：

SELECT ta.tokenName, SUM(ta.totalCount) AS total
FROM TokenAgg ta
JOIN Date d ON ta.dateID = d.dateID
WHERE d.publish_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY ta.tokenName
ORDER BY total DESC
LIMIT 100;

该方案可将查询耗时直接压缩至秒级，完全满足5秒以内的要求。

2. 窗口函数+CTE预过滤（无需改表的折中方案） #

利用MySQL 8.0的窗口函数，先过滤目标日期范围内的分词，再分组排序取前100，减少分组数据量：

WITH filtered_tokens AS (
    SELECT t.name
    FROM Token t
    JOIN Article a ON t.articleID = a.articleID
    JOIN Date d ON a.dateID = d.dateID
    WHERE d.publish_date BETWEEN '2023-01-01' AND '2023-12-31'
),
token_ranked AS (
    SELECT name, COUNT(*) AS total,
           RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM filtered_tokens
    GROUP BY name
)
SELECT name, total
FROM token_ranked
WHERE rnk <= 100;

关键配套索引：

CREATE INDEX idx_article_date ON Article(dateID, articleID);
CREATE INDEX idx_token_article ON Token(articleID, name);

3. 联合覆盖索引优化（无侵入性索引调整） #

替换现有单字段索引为联合覆盖索引，让MySQL无需回表即可完成查询：

-- 覆盖Date表的日期查询
CREATE INDEX idx_date_publish_id ON Date(publish_date, dateID);
-- 覆盖Article表的关联查询
CREATE INDEX idx_article_date_id ON Article(dateID, articleID);
-- 覆盖Token表的分词获取
CREATE INDEX idx_token_article_name ON Token(articleID, name);

该索引组合可让查询全程通过索引完成，大幅降低磁盘IO开销。

4. 启用并行查询（利用多核CPU加速） #

MySQL 8.0支持并行查询，AWS托管实例默认可能未开启，手动开启后可利用多核资源加速分组：

-- 全局开启（需权限）
SET GLOBAL parallel_execution_enabled = ON;
-- 会话级开启（临时生效）
SET SESSION parallel_execution_enabled = ON;

注意：需确保实例CPU核心数≥2，且查询数据量满足并行触发条件。

5. 日期分区优化（大表场景补充方案） #

按publish_date对Date表分区，让查询仅扫描目标日期范围内的分区：

ALTER TABLE Date PARTITION BY RANGE (TO_DAYS(publish_date)) (
    PARTITION p2023Q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p2023Q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p2023Q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION p2023Q4 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_default VALUES LESS THAN (MAXVALUE)
);

配合Article表的dateID关联，可进一步减少扫描的数据范围。

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