MySQL大表按时间戳查询过慢的优化方案咨询

1. 创建正确的复合覆盖索引 #

这是解决当前查询慢的核心措施。你的查询逻辑是固定VarId + CurDate时间范围筛选，且仅需返回CurDate和VarValue字段，因此创建复合覆盖索引：

ALTER TABLE log_data ADD INDEX idx_varid_curdate_varvalue (VarId, CurDate, VarValue);

• 为什么之前的索引无效？
  • 单字段CurDate索引：查询时需扫描时间范围内的所有行，再过滤VarId=6，若时间范围较大，扫描行数极多，且需回表读取VarValue，IO开销远高于全表扫描。
  • 主键id：查询完全不依赖主键，添加后只会增加写入时的索引维护开销，对查询无任何帮助，反而拖慢性能。
• 复合覆盖索引的优势：先通过VarId快速定位目标变量的所有日志，再在该子集内通过CurDate筛选时间范围，且索引包含VarValue，无需回表读取原数据，直接从索引返回结果，IO开销极低。

2. 优化查询语句 #

将FROM_UNIXTIME的计算提前，避免每次查询执行函数（MySQL虽能识别常量端的函数，但提前计算更稳妥）：

SELECT
  CurDate AS "time",
  VarValue AS 'Motor Power'
FROM log_data
WHERE
  CurDate BETWEEN '2023-11-09 02:27:20' AND '2023-11-09 12:00:07' -- 提前将UNIX时间转成datetime字符串
  AND VarId = 6;

3. 表分区（针对超大数据量） #

2亿条记录属于超大规模表，可按CurDate进行RANGE分区，让查询仅扫描目标时间范围对应的分区，减少磁盘IO：

-- 先删除现有索引（若已创建），再重建表分区
ALTER TABLE log_data DROP INDEX idx_varid_curdate_varvalue;

ALTER TABLE log_data
PARTITION BY RANGE (TO_DAYS(CurDate)) (
  PARTITION p20231109 VALUES LESS THAN (TO_DAYS('2023-11-10')),
  PARTITION p20231110 VALUES LESS THAN (TO_DAYS('2023-11-11')),
  -- 按天依次添加分区，或按周/月分区
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 重新创建复合覆盖索引
ALTER TABLE log_data ADD INDEX idx_varid_curdate_varvalue (VarId, CurDate, VarValue);

• 分区建议：根据查询的时间粒度选择分区粒度，若常用天级查询则按天分区，若常用月级则按月分区。

4. 数据归档 #

日志数据的查询热点通常集中在近期，旧数据查询频率低，可将历史数据归档到单独的表：

1. 创建归档表：

CREATE TABLE log_data_hist LIKE log_data;
-- 给归档表添加同样的复合索引
ALTER TABLE log_data_hist ADD INDEX idx_varid_curdate_varvalue (VarId, CurDate, VarValue);

2. 定期归档（例如每周日归档3个月前的数据）：

INSERT INTO log_data_hist SELECT * FROM log_data WHERE CurDate < DATE_SUB(NOW(), INTERVAL 3 MONTH);
DELETE FROM log_data WHERE CurDate < DATE_SUB(NOW(), INTERVAL 3 MONTH);

• 归档后主表数据量大幅减少，查询速度会显著提升。

5. 调整MyISAM配置 #

针对MyISAM引擎优化内存配置，减少磁盘IO：

• 调大key_buffer_size：MyISAM的索引缓存，建议设置为物理内存的20%-30%（例如内存16G则设为4G），修改my.cnf：

key_buffer_size = 4G

• 关闭query_cache：对于写频繁的日志表，查询缓存会频繁失效，反而增加开销，直接关闭：

query_cache_type = 0
query_cache_size = 0

6. Grafana查询合并 #

将10个单独的VarId查询合并为一次查询，减少数据库连接和查询开销：

SELECT
  CurDate AS "time",
  CASE WHEN VarId = 6 THEN VarValue END AS 'Motor Power',
  CASE WHEN VarId = 7 THEN VarValue END AS 'Temperature',
  -- 依次添加其他VarId的CASE语句
  VarId
FROM log_data
WHERE
  CurDate BETWEEN '2023-11-09 02:27:20' AND '2023-11-09 12:00:07'
  AND VarId IN (6,7,8,...); -- 包含10个目标VarId

在Grafana中，将每个CASE列作为单独的指标系列即可，一次查询替代10次，大幅降低数据库负载。

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