分区表
分区表就是把大表按条件单独存储到不同的"物理小表"中,在构建出的完整"逻辑表"。
分区表的优点
- 更少的数据检索范围
- 拆分超级大的表,将部分数据加载至内存
- 分区表的数据更容易维护
- 分区表数据文件可以分布在不同的硬盘上,并发IO
- 减少锁的范围,避免大表锁表
- 可独立备份,恢复分区数据
案例:
下面我们进行表的分区,就比如我们这张表的功能经常需要按照cyear 进行查询,并且cyear的数据量很大,这个时候可以考虑分区表。
分区前:
drop table if EXISTS test_partition;
CREATE TABLE test_partition (
id int(11) NOT NULL,
create_time datetime NOT NULL,
cyear int,
PRIMARY KEY (id,create_time , cyear)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_partition values (1,"20130722000000",2013);
insert into test_partition values (2,"20140722000000",2014);
insert into test_partition values (3,"20150722000000",2015);
insert into test_partition values (4,"20160722000000",2016);
insert into test_partition values (5,"20170722000000",2017);
insert into test_partition values (6,"20180722000000",2018);
insert into test_partition values (7,"20190722000000",2019);
insert into test_partition values (8,"20200722000000",2020);
insert into test_partition values (9,"20210722000000",2021);
insert into test_partition values (10,"20220722000000",2022);
查看执行计划:
explain select * from test_partition where cyear > 2016;
drop table if EXISTS test_partition;
CREATE TABLE test_partition (
id int(11) NOT NULL,
create_time datetime NOT NULL,
cyear int,
PRIMARY KEY (id,create_time , cyear)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (cyear)
(
PARTITION y14before VALUES LESS THAN (2014) ,
PARTITION y14_15 VALUES LESS THAN (2015) ,
PARTITION y15_16 VALUES LESS THAN (2016) ,
PARTITION y16_17 VALUES LESS THAN (2017) ,
PARTITION y17_18 VALUES LESS THAN (2018) ,
PARTITION y18_19 VALUES LESS THAN (2019) ,
PARTITION y19_20 VALUES LESS THAN (2020) ,
PARTITION y20after VALUES LESS THAN maxvalue );
insert into test_partition values (1,"20130722000000",2013);
insert into test_partition values (2,"20140722000000",2014);
insert into test_partition values (3,"20150722000000",2015);
insert into test_partition values (4,"20160722000000",2016);
insert into test_partition values (5,"20170722000000",2017);
insert into test_partition values (6,"20180722000000",2018);
insert into test_partition values (7,"20190722000000",2019);
insert into test_partition values (8,"20200722000000",2020);
insert into test_partition values (9,"20210722000000",2021);
insert into test_partition values (10,"20220722000000",2022);
对比执行计划可以看到数据筛选数量减少了很多,如果设计到数据量大,且为大块的拆分可以考虑用分区表。
查看分区:
select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="testdb" and table_name="test_partition";
分区表的使用限制
1 查询必须包含分区列,不允许对分区列进行计算
2 分区列必须是数字类型
3 分区表不支持建立外键索引
4 建表时主键必须包含所有的列
5 最多1024个分区
综合来看,分区表适合字段少的,并且分类明显的数据。