MySQL超300GB大表最新数据查询报错及优化方案求助

一、修正语法错误的CTE查询 #

原SQL核心错误是WHERE子句位置违规（MySQL中WHERE必须放在GROUP BY之前），同时分组逻辑不符合需求（不应将TOTAL_SHARES纳入分组条件，否则会破坏同一(INDEX_RIC, RIC)组合的最新条目筛选逻辑）。修正后的语句如下：

WITH tbl AS (
    SELECT INDEX_RIC, RIC, MAX(DATA_SET_ID) AS max_data_set_id
    FROM DB.MASSIVE_TABLE
    -- 提前过滤条件，减少分组数据量
    WHERE TOTAL_SHARES > 0 AND RIC IN ('ABC.DE')
    GROUP BY INDEX_RIC, RIC
)
SELECT t.INDEX_RIC, t.RIC
FROM DB.MASSIVE_TABLE t
JOIN tbl 
    ON t.INDEX_RIC = tbl.INDEX_RIC 
    AND t.RIC = tbl.RIC 
    AND t.DATA_SET_ID = tbl.max_data_set_id
WHERE t.TOTAL_SHARES > 0;

二、针对大表的高效查询方案 #

由于表中DATA_SET_ID每日递增，最新条目全部集中在最大的DATA_SET_ID批次中，无需全表分组，直接定位该批次查询性能会大幅提升：

方式1：分步查询（可读性高） #

-- 获取最新批次的DATA_SET_ID
SELECT @max_id := MAX(DATA_SET_ID) FROM DB.MASSIVE_TABLE;

-- 查询最新批次中符合条件的数据
SELECT INDEX_RIC, RIC
FROM DB.MASSIVE_TABLE
WHERE DATA_SET_ID = @max_id 
    AND TOTAL_SHARES > 0 
    AND RIC IN ('ABC.DE');

方式2：单条语句执行 #

SELECT INDEX_RIC, RIC
FROM DB.MASSIVE_TABLE
WHERE DATA_SET_ID = (SELECT MAX(DATA_SET_ID) FROM DB.MASSIVE_TABLE)
    AND TOTAL_SHARES > 0 
    AND RIC IN ('ABC.DE');

三、性能优化建议 #

为进一步提速，建议给表创建复合索引，让查询直接通过索引过滤数据，避免全表扫描：

CREATE INDEX idx_data_set_ric_shares ON DB.MASSIVE_TABLE (DATA_SET_ID, RIC, TOTAL_SHARES);

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