mysql使用表格分区与索引的搭配提升性能

分区表需配合查询条件中的分区键才能生效，否则全分区扫描性能更差；分区键须为高频过滤字段且分布均匀；非分区键字段仍需建索引；主键或唯一索引必须包含分区键；时间分区适合快速归档。

分区表必须配合查询条件中的分区键使用

MySQL 的 PARTITION BY RANGE 或 PARTITION BY LIST 本身不加速查询，只有当 WHERE 条件中包含分区键（如 created_at、region_id）时，优化器才能执行 partition pruning（分区剪枝），跳过无关分区。否则会全分区扫描，性能可能比普通表更差。

常见错误是建了按 order_date 分区的表，却总查 user_id = 123 ——这时分区完全无效，还额外增加了元数据开销。

• 分区键应是高频过滤字段，且值分布较均匀（避免某一分区过大）
• 联合索引的最左前缀若不包含分区键，无法触发剪枝
• EXPLAIN PARTITIONS SELECT …… 中的 partitions 列能确认实际访问了哪些分区

分区表上仍需在非分区键字段建普通索引

分区只解决“扫哪些分区”，不解决“分区内部怎么查”。比如按 year(created_at) 分区后，查 status = 'paid' 仍需索引加速，否则每个被选中的分区内都是全表扫描。

注意：MySQL 5.7+ 支持 local index（每个分区独立维护的索引），创建时加 LOCAL 关键字；全局索引（GLOBAL）在分区表中不支持（除主键 / 唯一键外）。

• 主键或唯一索引必须包含分区键（否则建表失败）
• 非唯一二级索引默认为 LOCAL，无需显式声明
• 避免在分区键上建冗余索引（如已按 dt 分区，再建 INDEX(dt) 无意义）

时间范围分区 + 按月归档时，用 DROP PARTITION 比 DELETE 快得多

删除历史数据是分区最直接的收益点。用 ALTER TABLE t DROP PARTITION p202301 是元数据操作，毫秒级完成；而 DELETE FROM t WHERE dt 会逐行标记、写 binlog、触发索引更新，可能锁表数分钟。

但要注意：DROP PARTITION 不走事务，不可回滚；且仅适用于 RANGE 和 LIST 分区（HASH / KEY 不支持）。

• 归档前确保该分区无未提交事务或长事务持有其行锁
• 若需保留备份，先 COPY 对应分区数据（如用 SELECT …… INTO OUTFILE 或逻辑导出）
• 定期用 ALTER TABLE t REORGANIZE PARTITION 合并空闲小分区，减少管理开销

INFORMATION_SCHEMA.PARTITIONS 是排查分区问题的第一入口

当发现查询没走预期分区，或 SHOW CREATE TABLE 看不出分区细节时，直接查系统表最可靠：

SELECT    partition_name,    table_rows,    avg_row_length,   data_length  FROM INFORMATION_SCHEMA.PARTITIONS  WHERE table_schema = 'db_name' AND table_name = 't_order';

重点关注 table_rows 是否严重倾斜（某分区行数远超其他），以及 data_length 是否异常（可能因大量删除未触发 OPTIMIZE PARTITION）。

另外，SHOW WARNINGS 在执行带分区的 DML 后常提示“Found a row not matching the given partition set”——这说明插入数据的分区键值超出所有定义范围，需及时 REORGANIZE 或 ADD PARTITION。

分区不是银弹。它解决的是数据规模和生命周期管理问题，而不是替代索引的设计。一个没建对索引的分区表，只会让慢查询更难定位。