在MySQL数据库优化中，分区表是处理海量数据的核心手段——通过将大表按分区键（如时间、地域、ID范围）分割为多个独立的小分区，可大幅提升数据查询、归档与维护效率，尤其适合千万级以上数据量的场景[1]。但很多开发者在给表设置分区键后，都会陷入一个核心疑问：分区键之外的其他索引（如普通索引、联合索引）还能正常生效吗？会不会因为分区导致原有索引失效，反而降低查询性能？

答案很明确：MySQL设置分区键后，其他索引并非必然失效，其生效与否取决于索引类型、分区策略、查询条件的搭配，以及索引与分区键的关联关系。多数情况下，合理设计的非分区键索引依然能正常发挥作用，但如果忽视分区与索引的适配规则，极易出现索引失效、全分区扫描等性能问题[1][3]。

本文将从MySQL分区与索引的核心关联入手，拆解不同索引类型在分区表中的生效逻辑，结合实操案例说明生效条件与失效场景，补充分区表索引设计的最佳实践，帮你彻底搞懂“分区键与其他索引”的协同逻辑，避免踩坑。

一、先理清：MySQL分区与索引的核心关联

要搞懂其他索引是否生效，首先要明确MySQL分区的本质——分区是对表数据的“物理分割”，即将一张大表的物理文件拆分为多个小文件，每个分区独立存储数据；而索引是对数据的“逻辑索引”，用于快速定位数据位置，分为“本地索引”和“全局索引”两种，二者的协同逻辑的是索引生效的关键[1][3]。

1. 分区的核心作用（与索引无关，却影响索引效率）

MySQL分区的核心价值是“简化海量数据管理”，而非直接加速查询：

• 数据归档高效：比如按时间分区的表，删除历史数据时，可通过DROP PARTITION毫秒级完成，无需逐行删除，大幅优于DELETE语句[1]；

• 减少扫描范围：当查询条件包含分区键时，MySQL会触发“分区剪枝”，跳过无关分区，仅扫描目标分区，提升查询效率[1]；

• 降低运维成本：分区表可按分区单独备份、优化，避免对全表操作导致的性能压力[1]。

注意：分区本身不具备“加速查询”的能力，若查询条件不包含分区键，会触发全分区扫描，性能甚至不如普通表[1]。而索引的核心作用是“加速数据定位”，二者相辅相成，而非相互替代[3]。

2. 分区表中索引的两种类型（决定其他索引是否生效）

MySQL分区表中的索引分为“本地索引”和“全局索引”，二者的生效规则完全不同，其中非分区键索引默认属于本地索引[1]：

• 本地索引（LOCAL）：索引与分区一一对应，每个分区独立维护自己的索引，索引的分区规则与表的分区规则一致。非分区键的普通索引、联合索引，默认都会创建为本地索引，也是MySQL分区表中最常用的索引类型[1]；

• 全局索引（GLOBAL）：索引不与分区绑定，是基于全表数据构建的统一索引，仅支持主键和唯一索引（非主键/唯一索引不支持全局索引）[1]。

关键结论：分区键之外的其他索引（普通索引、联合索引），只要是本地索引且设计合理，就能正常生效；若强行创建不支持的全局索引，会直接报错[1]。

二、分场景解析：分区键后，其他索引的生效情况

结合MySQL最常用的分区类型（RANGE分区、LIST分区、HASH分区），分4种核心场景，拆解非分区键索引的生效逻辑，每个场景搭配实操案例，让你直观理解[1][2][3]。

场景1：非分区键普通索引（最常用）—— 正常生效，需配合分区剪枝提升效率

非分区键的普通索引（如订单表中，按“创建时间”分区，给“用户ID”创建普通索引），默认会作为本地索引，每个分区独立维护该索引，查询时只要命中索引条件，就能正常生效[1]。

实操案例

创建按时间分区的订单表，给非分区键“user_id”创建普通索引：

-- 1. 创建按时间（创建时间）分区的订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    create_time DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    -- 给非分区键user_id创建普通索引
    INDEX idx_user_id (user_id)
)
-- 按create_time的年份分区（RANGE分区）
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

查询分析：

• 查询1（含分区键+索引条件）：SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31' AND user_id = 1001; 生效逻辑：MySQL先通过分区键create_time触发分区剪枝，仅扫描p2025分区；再通过idx_user_id索引，在p2025分区内快速定位user_id=1001的数据，索引正常生效，效率最高[1]。

• 查询2（不含分区键，仅含索引条件）：SELECT * FROM orders WHERE user_id = 1001; 生效逻辑：索引依然生效，但会扫描所有分区（无分区剪枝），每个分区内都会通过idx_user_id索引查询数据，效率低于含分区键的查询[1]。

场景2：非分区键联合索引—— 生效与否，看最左前缀与分区键的关联

联合索引的生效遵循“最左前缀原则”，在分区表中，若联合索引的最左前缀不包含分区键，依然可以生效，但无法触发分区剪枝；若最左前缀包含分区键，则能同时享受分区剪枝和索引加速的双重优势[1]。

实操案例

基于上述orders表，新增联合索引：

-- 联合索引1（最左前缀不含分区键）：user_id + amount
CREATE INDEX idx_user_amount ON orders(user_id, amount);
-- 联合索引2（最左前缀含分区键）：create_time + user_id
CREATE INDEX idx_create_user ON orders(create_time, user_id);

查询分析：

• 查询1（命中联合索引1，不含分区键）：SELECT * FROM orders WHERE user_id = 1001 AND amount > 100; 生效逻辑：idx_user_amount索引正常生效，但无分区剪枝，扫描所有分区，每个分区内通过联合索引过滤数据[1]。

• 查询2（命中联合索引2，含分区键）：SELECT * FROM orders WHERE create_time > '2025-01-01' AND user_id = 1001; 生效逻辑：先通过分区键create_time触发分区剪枝，仅扫描p2025、p2026分区；再通过idx_create_user联合索引，快速定位符合条件的数据，索引生效且效率极高[1]。

场景3：主键/唯一索引—— 必须包含分区键，否则创建失败（特殊生效规则）

MySQL分区表的主键和唯一索引，有一个强制规则：主键/唯一索引必须包含分区键，否则无法创建分区表（除HASH分区、KEY分区外，部分场景可例外，但不推荐）[1][2]。此时，主键/唯一索引（含分区键）会正常生效，且支持全局索引（默认全局）。

实操案例（正确 vs 错误）

-- 错误案例：主键不含分区键，创建分区表失败
CREATE TABLE orders_error (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id不含分区键create_time
    create_time DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2025 VALUES LESS THAN (2026)
);
-- 报错：A PRIMARY KEY must include all columns in the table's partitioning function

-- 正确案例：主键包含分区键，创建成功
CREATE TABLE orders_correct (
    id INT AUTO_INCREMENT,
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id, create_time) -- 主键包含分区键create_time
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

生效逻辑：主键（id, create_time）正常生效，查询时若包含主键条件（如id=100 AND create_time='2025-05-01'），会先触发分区剪枝，再通过主键索引快速定位数据[1]。

场景4：特殊分区类型（HASH/KEY）—— 非分区键索引生效，但无分区剪枝

HASH分区、KEY分区是按“哈希值”分配数据，无法通过查询条件触发分区剪枝（除非查询条件包含分区键），但非分区键的索引依然可以正常生效，只是查询时会扫描所有分区，效率相对较低[1][2]。

注意：KEY分区中，分区键不支持TEXT、BLOB类型，其他类型均可作为分区键；但无论分区键是什么类型，非分区键索引的生效逻辑与RANGE、LIST分区一致[2]。

三、重点：非分区键索引失效的3种常见场景（必避坑）

虽然分区键后其他索引大多能正常生效，但以下3种场景会导致索引失效，很多开发者都会踩坑，需重点规避[1][3]：

失效场景1：索引列使用函数或表达式，导致索引失效

与普通表一样，分区表中若查询条件的索引列使用了函数（如YEAR(user_id)、CONCAT(order_no, 'test')），会导致索引失效，无论是否包含分区键，都会触发全分区扫描[1]。

反例（失效）：SELECT * FROM orders WHERE YEAR(create_time) = 2025 AND user_id = 1001;（create_time是分区键，使用YEAR函数后，分区剪枝失效，idx_user_id索引也失效）

正例（生效）：SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31' AND user_id = 1001;

失效场景2：分区键与索引条件不匹配，导致全分区扫描（索引虽生效但效率极低）

非分区键索引生效，但如果查询条件不含分区键，会扫描所有分区，每个分区内的索引都会生效，但整体效率相当于“全表扫描+索引”，海量数据场景下性能极差[1]。

示例：orders表按create_time分区，查询SELECT * FROM orders WHERE user_id = 1001;，idx_user_id索引生效，但会扫描p2024、p2025、p2026所有分区，效率低下。

失效场景3：冗余索引或索引设计不合理，导致索引失效

① 给分区键创建冗余索引：比如已按create_time分区，再创建INDEX idx_create_time(create_time)，毫无意义，反而会增加数据写入时的索引维护成本，甚至导致索引冲突[1]；

② 联合索引最左前缀未命中：比如联合索引idx_user_amount(user_id, amount)，查询SELECT * FROM orders WHERE amount > 100;，未命中最左前缀user_id，索引失效[1]；

③ 索引列存在隐式转换：比如user_id是INT类型，查询时使用WHERE user_id = '1001'（字符串类型），会触发隐式转换，导致索引失效[1]。

四、分区表索引设计最佳实践（让其他索引高效生效）

结合参考资料与实操经验，总结4个核心最佳实践，既能确保非分区键索引正常生效，又能最大化发挥分区与索引的协同优势[1][3]：

1. 分区键选择：优先选择高频过滤字段，且值分布均匀

分区键应是查询中高频出现的过滤条件（如时间、地域），且值分布均匀，避免某一分区数据量过大（数据倾斜），这样才能有效触发分区剪枝，配合非分区键索引提升效率[1]。

2. 非分区键索引：优先创建本地索引，避免全局索引（除主键/唯一键）

非分区键的普通索引、联合索引，默认创建为本地索引即可，无需显式声明LOCAL；全局索引仅适用于主键和唯一索引，非主键/唯一索引创建全局索引会报错[1]。

3. 联合索引设计：尽量将分区键作为最左前缀

若需创建联合索引，优先将分区键作为最左前缀（如idx_create_user(create_time, user_id)），这样查询时既能触发分区剪枝，又能命中联合索引，效率最高[1]。

4. 定期维护：排查分区倾斜与索引失效，优化性能

① 定期查询INFORMATION_SCHEMA.PARTITIONS系统表，排查分区数据倾斜（某分区行数远超其他），及时通过REORGANIZE PARTITION合并或拆分分区[1]；

② 使用EXPLAIN PARTITIONS语句，查看查询是否触发分区剪枝、索引是否生效，排查索引失效问题[1]；

③ 避免在分区键上使用函数、隐式转换，避免创建冗余索引，减少索引维护成本[1][3]。

五、总结：分区键与其他索引——协同而非对立

MySQL设置分区键后，其他索引（普通索引、联合索引）并非失效，核心规律可总结为3点：

• 非分区键的普通索引、联合索引，默认作为本地索引，每个分区独立维护，只要查询条件命中索引，就能正常生效；

• 主键/唯一索引必须包含分区键，否则无法创建分区表，包含分区键后可正常生效（支持全局索引）；

• 索引生效与否，关键在于“查询条件是否命中索引”“是否触发分区剪枝”，不合理的查询条件或索引设计，会导致索引失效或效率低下[1][3]。

最后需要强调：分区的核心是“管理数据”，索引的核心是“加速查询”，二者相辅相成。很多开发者误以为“分区可以替代索引”，实则不然——一个没建对索引的分区表，不仅无法提升性能，反而会因全分区扫描、索引维护成本增加，导致性能比普通表更差[1]。

对于海量数据场景，正确的做法是：先根据业务场景选择合适的分区键（如时间），再为非分区键的高频查询字段创建合理的索引（普通索引或联合索引），配合分区剪枝，才能实现“数据管理高效+查询速度快捷”的双重目标。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ !

免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0