📌 关键词： MySQL分区表 、索引优化 、局部索引 、全局索引、性能调优、高级技巧
大家好呀！我是数据库小学妹👋
上篇我们学了分区表，把大表拆成小区域，查询能只扫对应分区。但有小伙伴反馈：明明用了分区表，查询速度还是不够快？ 问题很可能出在索引设计上。
分区表的索引和普通表不一样，它分为局部索引和全局索引。选错了，不仅不加速，反而可能拖垮性能。今天我们就来搞懂这两个概念，让你的分区表查询再快一步！
一、为何分区表索引更复杂？
普通表的索引就像一本书的目录，帮你快速定位内容。分区表则像多个独立书架（每个分区一个书架），每个书架都有自己的目录。这时候索引设计就有两种思路：
- 局部索引：每个书架独立编目录（只记录自己书架上的书）
- 全局索引：给所有书架编一套总目录（记录所有书架的书）
这两种方式各有优劣，适用场景完全不同。
二、核心概念：局部索引 vs 全局索引
| 对比项 | 局部索引（Local Index） | 全局索引（Global Index） |
|---|---|---|
| 存储方式 | 每个分区独立维护自己的索引树 | 整个表共用一个索引树 |
| 分区裁剪 | ✅ 支持（扫描对应分区索引） | ❌ 不支持（必须扫描整棵树） |
| 唯一性 | 无法保证跨分区唯一 | 可以保证全局唯一 |
| 维护成本 | 低（插入只改一个分区索引） | 高（插入可能涉及多分区） |
| 适用场景 | 查询条件命中分区键 | 必须全局唯一且无法用分区键 |
🔖局部索引（推荐90%的场景）
-- 按order_date分区，索引也包含order_date
CREATE INDEX idx_date_user ON orders (order_date, user_id);
- 查询时同时分区裁剪 + 索引查找，速度极快
- 插入/更新只维护一个分区的索引，开销小
- 缺点：不同分区可能出现相同的索引值（比如不同年份订单号重复）
🔖全局索引（谨慎使用）
-- 必须在唯一索引中包含分区键，否则报错
CREATE UNIQUE INDEX idx_global_id ON orders (id) GLOBAL;
- 保证全局唯一性（如订单号全局不重复）
- 致命缺点：查询时无法分区裁剪，必须扫描整个索引树，比普通表还慢
- 维护代价高：高并发写入时锁竞争严重
💡 建议：除非业务必须全局唯一（且无法用分区键保证），否则优先用局部索引。很多场景下，跨分区唯一性可以通过应用层（如分布式ID）解决。
三、实战技巧：如何设计分区表索引？
- 优先使用局部索引，兼顾分区键和查询条件
原则：将分区键包含在索引中，或作为索引的前缀列。例如，订单表按 YEAR(order_date) 分区，则索引应包含 order_date 列，如 INDEX(order_date, user_id)。这样既能分区裁剪，又能利用索引加速查询。
示例：
-- 创建按年份 Range 分区的订单表
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL,
user_id INT,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
... -- 分区定义省略
);
-- 创建局部索引（推荐）：包含分区键 order_date
CREATE INDEX idx_order_date_user_id ON orders (order_date, user_id);
- 谨慎使用全局索引，除非必须
场景：当需要全局唯一约束，且查询条件无法使用分区键时，才考虑全局索引。例如，用户表按地区 List 分区，但用户ID需要全局唯一：
-- 用户表按地区分区
CREATE TABLE users (
id INT NOT NULL,
region INT, -- 地区ID（1: 北京，2: 上海）
name VARCHAR(50)
)
PARTITION BY LIST(region) (
PARTITION p_beijing VALUES IN (1),
PARTITION p_shanghai VALUES IN (2)
);
-- 创建全局唯一索引（需谨慎）
CREATE UNIQUE INDEX idx_id ON users (id) GLOBAL;
注意：全局索引会大幅增加维护成本，使用前需评估业务场景的读写比例和性能需求。
- 覆盖索引：减少回表，提升性能
在分区表中，覆盖索引（索引列包含查询所需的所有列）尤为重要。例如，查询订单日期和金额时，索引包含这两列：
-- 覆盖索引示例
CREATE INDEX idx_order_date_amount ON orders (order_date, amount);
-- 查询可直接从索引获取数据，无需回表
EXPLAIN SELECT order_date, amount FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
- 避免“分区键陷阱”：唯一索引必须包含分区键
分区表的唯一索引或主键必须包含分区键，否则报错。例如：
-- 错误示例（会报错）：索引未包含分区键 order_date
CREATE UNIQUE INDEX idx_user_id ON orders (user_id);
-- 正确示例：包含分区键
CREATE UNIQUE INDEX idx_order_date_user_id ON orders (order_date, user_id);
四、性能调优实战：通过索引优化分区表查询
假设有一个按年份 Range 分区的订单表 orders，业务需求是查询某用户近一年的订单总额。如何优化？
- 原始查询（未优化）：
-- 假设 user_id = 1001
EXPLAIN SELECT SUM(amount) FROM orders WHERE user_id = 1001 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
问题：未使用分区键 order_date 作为查询条件，无法分区裁剪，全表扫描。
- 优化方案：
创建联合索引，包含分区键和查询条件：
CREATE INDEX idx_order_date_user_id ON orders (order_date, user_id);
修改查询条件，包含分区键：
EXPLAIN SELECT SUM(amount) FROM orders WHERE user_id = 1001 AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
效果：执行计划显示仅扫描 p2025 分区，使用索引 idx_order_date_user_id，查询效率大幅提升。
五、避坑指南：分区表索引的常见陷阱
📌陷阱①：误用全局索引导致性能下降
不要为了“方便”而滥用全局索引，除非必须保证全局唯一性。优先使用局部索引。
📌陷阱②：索引未覆盖查询列，导致回表开销
查询的列尽量包含在索引中，避免回表扫描数据行。
📌陷阱③：分区键选择错误，索引失效
如果查询常用字段不是分区键，分区裁剪失效。需重新评估分区键设计或添加冗余字段。
📌陷阱④：高并发下全局索引的锁竞争
全局索引的维护涉及跨分区锁，高并发写入场景可能导致锁等待，需优化事务设计或改用局部索引。
六、总结与进阶思考
分区表的索引设计是性能优化的关键！记住以下几点：
- 局部索引优先：兼顾分区键和查询条件，实现分区裁剪 + 索引加速。
- 全局索引谨慎：仅用于必须保证全局唯一且无法通过分区键优化的场景。
- 覆盖索引提效：减少回表，降低IO开销。
- 分区键要合理：选择最常用的查询过滤字段作为分区键，避免索引失效。
理解了分区表的索引设计，你才能真正发挥分区的威力，让亿级数据查询也能“飞起来”！如果业务需求更复杂（例如，既要分区裁剪，又要全局唯一），可能需要结合其他技术（如分布式ID、应用层唯一性校验）来权衡性能与一致性。
👋 我是数据库小学妹 今天的技巧有没有让你对分区表索引豁然开朗？你是否有过分区表索引设计踩坑的经历？欢迎在评论区分享你的故事或疑问，我们一起探讨！ 🌟
本文示例基于 MySQL 8.0。不同版本语法略有差异，请以官方文档为准。
