优化MySQL时间序列数据SQL查询性能的技术咨询

问题根源 #

你的查询全表扫描milestones（3000万+行），核心原因是跨表的连接条件（br.datetime BETWEEN ino.date AND ino.enddate + br.sensor LIKE CONCAT(ino.vessel, '%')）无法被单字段索引高效支撑，当多project条件时，优化器判断全表扫描成本更低，放弃使用索引。

一、创建针对性复合索引（优先执行） #

1. 给milestones表创建复合索引 #

针对前缀匹配+时间范围的查询模式，创建**(sensor, datetime)**的复合索引：

CREATE INDEX idx_sensor_datetime ON db.milestones(sensor, datetime);

该索引先通过sensor前缀匹配过滤数据，再在匹配结果中按datetime范围筛选，大幅减少扫描行数。

2. 优化projectdb表索引（辅助） #

虽然projectdb数据量小，但创建覆盖索引避免回表：

CREATE INDEX idx_project_vessel_dates ON db.projectdb(project, vessel, date, enddate);

过滤project后直接拿到连接所需的所有字段，无需查询主表。

二、改写查询语句，引导优化器选对执行计划 #

提前过滤projectdb的目标数据，再与milestones连接，让优化器优先使用milestones的索引：

SELECT 
    br.reading,
    CONCAT(ino.project, "_", SUBSTRING_INDEX(br.sensor, "_", -1)) AS new_metric,
    ino.vessel,
    ADDTIME(DATE('2023-01-01'), -TIMEDIFF(ino.date, br.datetime)) AS norm_date,
    br.sensor
FROM (
    -- 先过滤出目标项目的核心数据
    SELECT project, vessel, date, enddate
    FROM db.projectdb
    WHERE project IN ('project1','project2','project3','project4')
) AS ino
INNER JOIN db.milestones br 
    -- 先匹配sensor前缀，再过滤时间范围，提升索引命中率
    ON br.sensor LIKE CONCAT(ino.vessel, '%')
    AND br.datetime BETWEEN ino.date AND ino.enddate

三、彻底消除LIKE匹配（长期最优方案） #

由于sensor格式固定为[vessel]_[sensor_code]，新增计算字段存储vessel前缀，用等值匹配替代LIKE：

1. 添加存储字段并创建索引 #

-- 新增自动计算的vessel字段
ALTER TABLE db.milestones ADD COLUMN vessel VARCHAR(45) AS (SUBSTRING_INDEX(sensor, "_", 1)) STORED;
-- 创建等值+范围的复合索引
CREATE INDEX idx_vessel_datetime ON db.milestones(vessel, datetime);

2. 修改查询语句 #

SELECT 
    br.reading,
    CONCAT(ino.project, "_", SUBSTRING_INDEX(br.sensor, "_", -1)) AS new_metric,
    ino.vessel,
    ADDTIME(DATE('2023-01-01'), -TIMEDIFF(ino.date, br.datetime)) AS norm_date,
    br.sensor
FROM (
    SELECT project, vessel, date, enddate
    FROM db.projectdb
    WHERE project IN ('project1','project2','project3','project4')
) AS ino
INNER JOIN db.milestones br 
    -- 等值匹配替代LIKE，完全利用索引
    ON br.vessel = ino.vessel
    AND br.datetime BETWEEN ino.date AND ino.enddate

此方案能让索引利用率达到100%，性能提升最显著。

四、强制索引的正确用法 #

如果创建复合索引后优化器仍未选择，可强制指定索引：

INNER JOIN db.milestones br FORCE INDEX (idx_sensor_datetime)
    ON br.sensor LIKE CONCAT(ino.vessel, '%')
    AND br.datetime BETWEEN ino.date AND ino.enddate

五、其他辅助优化 #

• 更新表统计信息：执行ANALYZE TABLE db.milestones;让优化器准确评估索引成本。
• 分区表优化：若仅需查询近期数据，可按datetime对milestones做分区，进一步缩小扫描范围。
• 清理历史数据：定期归档超过保留期限的监控数据，减少表的总数据量。

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