MySQL基础教程（17）分区表的使用
一、分区表的作用
当一张表存放了大量数据后，文件体积会逐渐变大，这样会影响查询效率和可维护性。使用分区表则可以解决这种大表问题，将大表在物理上和逻辑上都拆分为了多个小表，并且对应用来说也是无感知的，看上去还是一个单表、同一个表名（还有一种分表的方案，但是应用侧需要明确使用到的每个表名）。虽然分区表可以解决大表问题，但是它会给维护带来一定的麻烦，所以并不能盲目的将数据量多少作为是否使用分区表的标准，而是建议对需要进行定期归档或清理的业务进行表分区，如历史日志类、流水类数据。
分区表在一定情况也会带来更多的磁盘I/O，如下面的执行计划：
原本在普通表上不管记录数再多，通常只需要4次IO，但是在分区表下，需要访问 4 个分区，假设每个分区需要3次I/O，也要12 次I/O
SELECT * FROM t WHERE d = 'aaa' ******** 1. row ******** id: 1 select_type: SIMPLE table: t partitions: p2018,p2019,p2020,p2021 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 50.00 Extra: Using where
二、分区表注意事项
1、分区表对于海量数据的维护更方便，可以通过删除单个分区来清理不需要的数据
2、对于分区表上的数据在查询时不要全表扫描，只需要查询对应分区即可
3、MySQL 5.6开始每张表支持8192个分区
4、进行分区的字段必须是主键或唯一索引列的组成部分。比如要基于order_date列进行分区，那么主键必须包含order_date列（联合主键也可以）
5、不能使用外键约束，不能使用存储过程，不支持全文索引，不支持临时表
6、分区还可以建立子分区，也叫复合分区
7、MySQL在第一次打开分区表的时候需要访问所有的分区，需注意open_files_limit参数的限制
8、在server层认为分区表是同一张表，所有分区共用同一个MD 锁；在引擎层认为是不同的表，MDL锁之后的执行过程，会根据分区表规则只访问必要的分区
9、分区并不是越细越好，对于现在的硬件来说，单表数据即便在一千万行，只要没有特别大的索引，也不算大表
10、分区不要提前预留太多，在使用之前预先创建即可。比如按月分区，可以在年底再创建下一年的12个新分区
11、对于没有数据的历史分区，要及时的 drop 掉
12、MySQL 的分区是静态定义的，不能像分表那样自动按日期创建分区。如果想要实现动态分区，需要定期使用脚本添加分区
三、MySQL分区表类型
· range：范围分区，也是最常用的分区方式。适合按照月份或者编号范围进行分区，如每10万个ID为一个分区、每个月的数据为一个分区等。该分区方式会存在热点数据集中在一个分区的问题。
· hash：哈希分区，按照HASH算法将所有数据随机分到每个区，解决range热点数据集中问题
· list：列表分区，适合对字符进行分区（严格区分大小写），比如按省份分区，但是数据不会太均匀
· key：HASH分区的一种延伸方式
四、MySQL分区表的使用
1、通常在创建表的时候就要做好分区操作，已存在的表不能直接修改为分区表
#创建orders表。按年创建分区
CREATE TABLE `orders` (
`ORDERKEY` int NOT NULL,
`ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`ORDERDATE` date NOT NULL,
`ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`ORDERKEY`,`ORDERDATE`),
KEY `idx_orderdate` (`ORDERDATE`)
)
PARTITION BY RANGE COLUMNS(ORDERDATE)
(
PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
）2、通过分区表删除历史数据
#传统删除方式会产生大量binlog，导致主从延迟 DELETE FROM Orders WHERE o_orderdate >= '1998-01-01' AND o_orderdate < '1999-01-01' #通过分区进行删除只有一条 DDL 日志，TRUNCATE和DROP视情况选择 ALTER TABLE orders_par TRUNCATE PARTITION p1998
3、对已分区的表增加分区
alter table student drop partition student_2020; #先删除最后一个分区，这个过程不会清空该分区数据，只会重新分配到其它分区中 alter table student add partition (partition student_2020 values less than (2021)) #为2020新增分区 alter table student add partition (partition student_2021 values less than maxvalue) #再创建一个新的最后分区
4、MySQL列表分区
create table student ( id int not null, name varchar(20) not null, age int not null ) partition by list(age) ( partition age_1 values in (20), partition age_2 values in (30,40) );
5、MySQLHASH分区
用hash分区可以指定一个分区数量，然后数据会很平均的分配到这些分区中
create table student ( id int not null, name varchar(20) not null, age int not null ) partition by hash(id) partitions 5 #创建了5个分区，假如插入5W数据，那么每个分区大概就是1W条数据
评论