MySQL：如何为DATETIME列创建低粒度索引以缩减索引大小？

以下几种方案可以帮你在小幅牺牲查询性能的前提下，大幅缩减索引体积：

1. 新增计算列并创建索引 #

通过新增存储分钟/小时级粒度的计算列，直接对该列建立索引，兼容性好，适用于大多数数据库版本。

示例（MySQL）： #

• 新增小时级粒度列：

  ALTER TABLE log_table ADD COLUMN ts_hour INT AS (FLOOR(UNIX_TIMESTAMP(ts)/3600)) STORED;
  

• 为该列创建索引：

  CREATE INDEX idx_log_ts_hour ON log_table(ts_hour);
  

• 查询时使用该列过滤（搭配原DATETIME列做精确范围补全）：

  SELECT * FROM log_table 
  WHERE ts_hour BETWEEN FLOOR(UNIX_TIMESTAMP('2024-05-01 00:00:00')/3600) 
                    AND FLOOR(UNIX_TIMESTAMP('2024-05-01 23:59:59')/3600)
    AND ts >= '2024-05-01 00:00:00' 
    AND ts <= '2024-05-01 23:59:59';
  

优缺点：索引体积大幅减小，查询性能损耗极低；但需要额外存储计算列，占用少量表空间。

2. 使用表达式索引（数据库版本需支持） #

如果你的数据库支持表达式索引（如MySQL 8.0+、PostgreSQL、SQL Server），可以直接基于DATETIME的粒度转换表达式创建索引，无需新增列。

示例（MySQL 8.0+，分钟级粒度）： #

• 创建表达式索引：

  CREATE INDEX idx_log_ts_minute ON log_table(FLOOR(UNIX_TIMESTAMP(ts)/60));
  

• 查询时复用相同表达式过滤：

  SELECT * FROM log_table 
  WHERE FLOOR(UNIX_TIMESTAMP(ts)/60) BETWEEN FLOOR(UNIX_TIMESTAMP('2024-05-01 10:00:00')/60)
                                          AND FLOOR(UNIX_TIMESTAMP('2024-05-01 10:59:59')/60);
  

优缺点：无需额外存储列，索引体积小；但查询时需要计算表达式，会有小幅性能损耗，且依赖数据库版本。

3. 按日期粒度创建索引（适合仅按日期查询的场景） #

如果你的查询大多只按日期范围（而非小时/分钟）过滤，可以直接基于DATE(ts)创建表达式索引或新增DATE类型列。

示例（PostgreSQL）： #

• 创建日期表达式索引：

  CREATE INDEX idx_log_date ON log_table(DATE(ts));
  

• 查询时使用：

  SELECT * FROM log_table WHERE DATE(ts) BETWEEN '2024-05-01' AND '2024-05-03';
  

优缺点：索引体积最小，适合按天查询的场景；如果需要更细粒度的查询，该方案不适用。

4. 分区表（适合超大规模数据） #

将表按日期/小时进行分区，每个分区拥有独立的索引，既能缩减单索引的体积，还能实现查询时的分区裁剪。

示例（MySQL，按天分区）： #

• 创建分区表（假设ts为DATETIME列）：

  CREATE TABLE log_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts DATETIME,
    content TEXT
  )
  PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) (
    PARTITION p20240501 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-02 00:00:00')),
    PARTITION p20240502 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-03 00:00:00')),
    PARTITION p20240503 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-04 00:00:00'))
  );
  

• 为每个分区的ts列创建索引：

  CREATE INDEX idx_log_ts ON log_table(ts);
  

优缺点：单分区索引体积小，查询时仅扫描目标分区；但分区管理复杂度较高，适合数据量千万级以上的场景。

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