MySQL分区表技术：管理海量数据的利器

分区表概述分区表是将一张逻辑上的大表按照某种规则分割成多个物理上的小表存储。分区的优势1. 提升查询性能分区裁剪只扫描相关分区2. 便于数据管理直接删除旧分区代替DELETE3. 分区数据可以分散到不同磁盘4. 提升维护效率只需对单个分区做OPTIMIZE等操作适用场景单表数据量超过千万行且有明显的分区键时间、地区等。注意分区表不是万能的对于随机写入的OLTP场景效果有限更适合OLAP/归档场景。RANGE分区RANGE分区按照列值的范围来分区最常用于时间字段。-- 按年份分区CREATE TABLE orders (id BIGINT NOT NULL AUTO_INCREMENT,user_id INT NOT NULL,amount DECIMAL(10,2),create_time DATETIME NOT NULL,PRIMARY KEY (id, create_time) -- 分区键必须包含在主键中) PARTITION BY RANGE (YEAR(create_time)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025),PARTITION p_future VALUES LESS THAN MAXVALUE);-- 查询时自动分区裁剪SELECT * FROM orders WHERE YEAR(create_time)2023; -- 只扫描p2023分区LIST分区LIST分区按照列值的离散集合来分区适合按类别、地区等分区。-- 按地区分区CREATE TABLE sales (id BIGINT NOT NULL,region_id INT NOT NULL,amount DECIMAL(10,2),PRIMARY KEY (id, region_id)) PARTITION BY LIST (region_id) (PARTITION p_north VALUES IN (1, 2, 3, 4), -- 华北地区PARTITION p_south VALUES IN (5, 6, 7, 8), -- 华南地区PARTITION p_east VALUES IN (9, 10, 11), -- 华东地区PARTITION p_west VALUES IN (12, 13, 14) -- 华西地区);注意插入的值必须在某个LIST中否则报错。使用LIST COLUMNS可以支持多列和字符串。HASH分区和KEY分区HASH分区通过对分区键取模将数据均匀分散到指定数量的分区。-- HASH分区基于表达式CREATE TABLE users (id INT NOT NULL,name VARCHAR(50),PRIMARY KEY (id)) PARTITION BY HASH(id)PARTITIONS 8;-- KEY分区MySQL内部HASH函数支持多列CREATE TABLE sessions (session_id VARCHAR(32) NOT NULL,user_id INT NOT NULL,data TEXT,PRIMARY KEY (session_id)) PARTITION BY KEY(session_id)PARTITIONS 16;适用场景数据无明显范围特征需要均匀分布。缺点无法进行分区裁剪每次查询需扫描所有分区。分区管理操作-- 添加分区ALTER TABLE orders ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));-- 删除分区同时删除该分区数据ALTER TABLE orders DROP PARTITION p2022;-- 清空分区数据保留分区结构ALTER TABLE orders TRUNCATE PARTITION p2022;-- 重组分区合并ALTER TABLE orders REORGANIZE PARTITION p2024, p_future INTO (PARTITION p2024 VALUES LESS THAN (2025),PARTITION p2025 VALUES LESS THAN (2026),PARTITION p_future VALUES LESS THAN MAXVALUE);-- 查看分区信息SELECT * FROM information_schema.PARTITIONSWHERE TABLE_NAMEorders;分区表的限制与注意事项MySQL分区表的主要限制1. 分区键必须包含在所有唯一键包括主键中2. 分区表不支持外键3. 全文索引在分区表上不可用4. 最多支持1024个分区MySQL 8.0提升至81925. 分区表的查询必须包含分区键才能利用分区裁剪常见误区- 分区不等于索引两者结合使用效果更佳- 分区过多反而影响性能DDL操作更慢- 查询条件不包含分区键时会全分区扫描建议分区数量控制在100以内结合业务查询模式设计分区策略。分区表最佳实践实践1时间分区自动轮转-- 每月创建新分区删除12个月前的分区-- 可通过定时任务自动执行实现数据自动老化实践2利用分区做数据归档-- 业务数据在主表分区表定期将旧分区数据-- 交换EXCHANGE PARTITION到归档表ALTER TABLE orders EXCHANGE PARTITION p2022 WITH TABLE orders_archive;实践3分区覆盖索引-- 在分区表的常用查询列上建立覆盖索引-- 查询既可利用分区裁剪又可利用索引实践4监控分区大小SELECT PARTITION_NAME, TABLE_ROWS,DATA_LENGTH/1024/1024 AS data_mbFROM information_schema.PARTITIONSWHERE TABLE_NAMEordersORDER BY PARTITION_ORDINAL_POSITION;