数据库-分区表

分区表

分区表就是把大表按条件单独存储到不同的"物理小表"中，在构建出的完整"逻辑表"。

分区表的优点

• 更少的数据检索范围
• 拆分超级大的表，将部分数据加载至内存
• 分区表的数据更容易维护
• 分区表数据文件可以分布在不同的硬盘上，并发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个分区

综合来看，分区表适合字段少的，并且分类明显的数据。