MySQL基础教程（17）分区表的使用
一、MySQL分区表介绍
由于MySQL默认是以文件的形式将表中数据存储在磁盘中，所以当一张表存放大量数据后文件体积会非常大，这样会影响查询效率和数据安全。为了解决这种大表性能问题MySQL提供了分区表功能，通过分区表可以把一张大表拆分为多张逻辑小表，这些小表虽然在磁盘上也被拆分成了多个物理文件（文件名格式为表名+分区名），但是对应用来说是透明的，看上去仍然是一张表（一个表名）。虽然分区表可以解决大表问题，但是它会给维护带来一定的麻烦，比如在对某一个分区做DDL的时候，会导致其它分区出现MDL锁。所以并不推荐因为数据量大而使用分区表，因为MySQL是索引组织表，数据量即便再大，定位记录也只需要3、4 次 I/O。它的本质更多是为了方便OLAP场景下的数据管理，可以考虑分区表的应用场景是需要定期清理历史流水类数据。
下面的一个执行计划可以看出一个原本在普通表上不管记录数再多，最多也只有4次IO的SQL，通过分区后需要访问 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
二、MySQL分区表优缺点与使用建议
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 掉
三、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、为已有表创建分区，已有数据会按照规则自动分配到分区中
CREATE TABLE `ad_unit_cost` (
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联所属用户',
`plan_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联推广计划 id',
`unit_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联推广单元 id',
`cost` bigint(20) NOT NULL DEFAULT '0' COMMENT '推广单元花费金额',
`date_` date NOT NULL COMMENT '数据日期，精确到天，yyyy-MM-dd'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE ad_unit_cost PARTITION BY RANGE (TO_DAYS(date_)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2019-02-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2019-03-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2019-04-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2019-05-01')),
PARTITION p5 VALUES LESS THAN (TO_DAYS('2019-06-01')),
PARTITION p6 VALUES LESS THAN (MAXVALUE) );
3、执行结果中可以看到分区表信息，即便在一个分区中进行了全表扫描，影响的行数也比不分区的要小很多
4、通过分区表删除历史数据
#传统删除方式会产生大量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
5、对已分区的表增加分区
show create table student;
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) #再创建一个新的最后分区
五、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)
);
六、MySQL表分区示例——HASH分区
用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条数据
评论