MySQL 8.0分区查询读取多余子分区问题排查与优化

1. HASH子分区键设计不合理
   若子分区直接对event_datetime做HASH计算，而非提取月份值，同一月份的不同日期会被散列到不同子分区，导致查询整个12月时需扫描多个子分区。即使按MONTH(event_datetime)做HASH，若子分区数量与月份取值范围不匹配，也可能出现跨子分区扫描的情况。

2. 函数包裹分区键导致分区修剪失效
   使用YEAR(event_datetime)=2023或MONTH(event_datetime)=12这类写法时，MySQL无法直接利用event_datetime的原始值匹配分区规则，优化器无法判断哪些分区符合条件，只能扫描所有分区的子分区，同时也会阻碍索引的正常使用。

3. 索引与查询需求不匹配
   单独的sensor_id索引无法与时间范围查询条件联合生效，当查询同时过滤时间和sensor_id时，优化器无法通过索引快速缩小数据范围，只能依赖全分区扫描。

1. 修正子分区配置 #

明确将子分区的HASH键设为MONTH(event_datetime)，并将子分区数量设为12（对应1-12月），确保每个月份对应唯一子分区。示例创建语句：

CREATE TABLE events_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT NOT NULL,
    event_datetime DATETIME NOT NULL,
    event_details TEXT
)
PARTITION BY RANGE (YEAR(event_datetime))
SUBPARTITION BY HASH (MONTH(event_datetime))
SUBPARTITIONS 12 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

2. 使用原生时间范围替代函数查询 #

查询时直接用event_datetime的范围条件，避免用函数包裹分区键，让优化器能自动进行分区修剪。示例查询语句：

SELECT * FROM events_table 
WHERE event_datetime BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59'
AND sensor_id = 100;

若业务需要按年份、月份查询，可在应用层将年份和月份转换为对应的起止时间，再传入SQL执行。

3. 创建匹配查询需求的联合索引 #

针对同时按时间范围和sensor_id过滤的场景，创建(event_datetime, sensor_id)联合索引，让优化器既能通过时间范围定位分区，又能在分区内快速过滤sensor_id：

CREATE INDEX idx_event_sensor ON events_table(event_datetime, sensor_id);

4. 验证分区修剪效果 #

使用EXPLAIN PARTITIONS语句查看实际扫描的分区，确认是否仅读取目标分区和子分区：

EXPLAIN PARTITIONS
SELECT * FROM events_table 
WHERE event_datetime BETWEEN '2023-12-01 00:00:00' AND '2023-12-31 23:59:59';

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