MySQL datetime列查询的最优分区方案咨询

针对你的5亿行InnoDB表查询性能问题，结合当前的查询模式，以下是具体的优化方案：

1. 替换分区策略：使用RANGE COLUMNS(date_added)分区 #

当前按MONTH(date_added)的RANGE分区无法触发有效分区裁剪，因为你的查询条件是date_added的范围而非月份值，MySQL无法直接将日期范围映射到月份分区，导致需要扫描多个无关分区。

改用基于date_added列本身的RANGE COLUMNS分区，让查询的日期范围条件直接匹配分区规则，实现精准的分区裁剪：

ALTER TABLE foo PARTITION BY RANGE COLUMNS(date_added) (
  PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
  PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
  PARTITION p202303 VALUES LESS THAN ('2023-04-01'),
  PARTITION p202304 VALUES LESS THAN ('2023-05-01'),
  PARTITION p202305 VALUES LESS THAN ('2023-06-01'),
  PARTITION p202306 VALUES LESS THAN ('2023-07-01'),
  PARTITION p202307 VALUES LESS THAN ('2023-08-01'),
  PARTITION p202308 VALUES LESS THAN ('2023-09-01'),
  PARTITION p202309 VALUES LESS THAN ('2023-10-01'),
  PARTITION p202310 VALUES LESS THAN ('2023-11-01'),
  PARTITION p202311 VALUES LESS THAN ('2023-12-01'),
  PARTITION p202312 VALUES LESS THAN ('2024-01-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

这种分区方式下，当执行date_added >= '2023-06-01'的查询时，MySQL会直接跳过p202301到p202305的分区，只扫描符合条件的后续分区，大幅减少数据扫描量。

2. 创建覆盖索引，消除回表开销 #

你的查询需要name过滤、date_added排序，同时返回date_added和height，创建覆盖索引可以让查询直接从索引获取所有需要的数据，无需回表查询主表：

CREATE INDEX idx_name_date_height ON foo(name, date_added, height);

这个索引的结构是：

• 前缀name用于快速定位匹配的行
• 中间date_added满足排序需求，避免额外的排序操作
• 末尾height作为包含列，直接返回结果，无需访问主表

结合分区裁剪，这个索引能让查询性能得到质的提升。

3. 额外注意事项 #

• 分区粒度：如果数据增长速度极快，可根据实际情况调整分区粒度（比如按季度分区），但月份分区的裁剪精度更高，适合你的查询模式。
• 分区维护：定期归档或删除旧数据分区（比如超过1年的历史数据），进一步减少查询需要扫描的分区数量。
• 验证效果：使用EXPLAIN PARTITIONS SELECT ...查看查询计划，确认是否只扫描了目标分区，同时检查是否使用了创建的覆盖索引。

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