当MySQL单表数据量增长到千万甚至上亿级别时，全表扫描的查询效率会明显下降，这时候表分区就是一种有效的优化方案。表分区可以让数据库按照预设规则把大表的数据拆分到多个独立的存储单元中，查询时只需要访问对应分区即可，减少不必要的IO消耗。
什么是MySQL表分区
MySQL表分区是指按照一定规则，将一张逻辑上的大表拆分成多个物理上的小存储单元，这些小单元就是分区。对应用层来说，操作的还是同一张表，不需要修改业务代码，数据库会自动根据分区规则定位到对应的分区执行操作。
常见的MySQL分区类型
1. RANGE分区
按照某个列的取值范围来划分分区，最常用在按时间字段分区的场景，比如按订单创建时间把数据拆分到不同分区。
创建RANGE分区表的示例：
-- 创建按订单创建时间范围分区的订单表
CREATE TABLE order_info (
order_id INT NOT NULL,
order_amount DECIMAL(10,2),
create_time DATE NOT NULL
)
PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p_max VALUES LESS THAN MAXVALUE
);2. LIST分区
按照某个列的离散值来划分分区，适合字段值是固定几个选项的场景，比如按地区编码分区。
创建LIST分区表的示例：
-- 创建按地区编码列表分区的用户表
CREATE TABLE user_info (
user_id INT NOT NULL,
user_name VARCHAR(50),
region_code INT NOT NULL
)
PARTITION BY LIST (region_code) (
PARTITION p_east VALUES IN (1,2,3),
PARTITION p_west VALUES IN (4,5,6),
PARTITION p_south VALUES IN (7,8,9),
PARTITION p_north VALUES IN (10,11,12)
);3. HASH分区
按照某个列的哈希值来划分分区，适合需要把数据均匀分布到多个分区的场景，不需要手动指定每个分区的范围或值。
创建HASH分区表的示例：
-- 创建按用户ID哈希分区的4个分区的日志表
CREATE TABLE user_log (
log_id INT NOT NULL,
user_id INT NOT NULL,
log_content TEXT,
create_time DATETIME
)
PARTITION BY HASH (user_id)
PARTITIONS 4;4. KEY分区
和HASH分区类似，但是哈希计算由MySQL内部完成，不需要用户指定哈希函数，默认使用主键作为分区键，如果没有主键则使用唯一键。
-- 创建按主键KEY分区的3个分区的商品表
CREATE TABLE goods_info (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(100),
price DECIMAL(10,2)
)
PARTITION BY KEY ()
PARTITIONS 3;分区管理操作
添加分区
RANGE分区可以在末尾添加新的分区，LIST分区可以新增包含对应值的分区：
-- 给order_info表添加新的2024年分区 ALTER TABLE order_info ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));
删除分区
删除分区会同时删除分区内的所有数据，操作前需要确认数据是否需要备份：
-- 删除order_info表的2020年分区 ALTER TABLE order_info DROP PARTITION p2020;
合并分区
可以把多个相邻的分区合并成一个分区：
-- 把order_info表的2021和2022分区合并成p2021_2022分区
ALTER TABLE order_info REORGANIZE PARTITION p2021,p2022 INTO (
PARTITION p2021_2022 VALUES LESS THAN (2023)
);使用表分区的注意事项
- 分区键必须是主键或者唯一索引的一部分，否则创建分区表会失败
- 分区表的索引都是本地索引，每个分区有自己的索引，全局索引在MySQL中不支持
- 分区数量不是越多越好，过多的分区会增加元数据管理的开销，一般建议单表分区数不超过100个
- 如果查询条件没有用到分区键，数据库还是会扫描所有分区，无法发挥分区的性能优势
- MySQL 5.7及之前版本，分区表不支持外键约束，使用分区表时需要注意关联表的设计
适用场景判断
表分区适合以下场景：
- 单表数据量超过千万，且查询大多会用到分区键作为过滤条件
- 需要定期清理历史数据，比如按时间分区后，可以直接删除过期时间对应的分区，效率远高于DELETE语句
- 数据有明显的访问热点，比如最近3个月的数据访问频率远高于 older 数据，按时间分区后可以提升热点数据的查询效率
如果单表数据量不大，或者查询很少用到分区键，就不建议使用表分区，避免额外的管理开销。