mysql表分区，mysql分区表
【0】分区介绍
（0.1）概念及其分区后性质
mysql分区后每个分区成了独⽴的⽂件，虽然从逻辑上还是⼀张表其实已经分成了多张独⽴的表；
  但从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独⽴的TABLE_ID；
  由于Innodb数据和索引都是保存在".ibd"⽂件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各⾃的分区(primary key和unique也不例外）），所以分区表的索引也是随着各个分区单独存储。
在INNODB_SYS_INDEXES系统表中type代表索引的类型;0:⼀般的索引,
  1:(GEN_CLUST_INDEX)不存在主键索引的表，会⾃动⽣成⼀个6个字节的标⽰值
  2:unique索引,3:primary索引；所以当我们在分区表中创建索引时其实也是在每个分区中创建索引，每个分区维护各⾃的索引（其实也就是local index）；
    对于⼀般的索引(⾮主键或者唯⼀)没什么问题由于索引树中只保留了索引key和主键key(如果存在主键则是主键的key否则就是系统⾃动⽣成的6个的key)不受分区的影响；
    但是如果表中存在主键就不⼀样了，虽然在每个分区⽂件中都存在主键索引但是主键索引需要保证全局的唯⼀性就是所有分区中的主键的值都必须唯⼀（唯⼀键也是⼀样的道理）；
    所以在创建分区时如果表中存在主键或者唯⼀键那么分区列必须包含主键或者唯⼀键的部分或者全部列（全部列还好理解，部分列也可以个⼈猜测是为了各个分区和主键建⽴关系）；
    由于需要保证全局性⼜要保证插⼊数据更新数据到具体的分区所以就需要将分区和主键建⽴关系，由于通过⼀般的索引进⾏查其它⾮索引字段需要通过主键如果主键不能保证全局唯⼀性的话那么就需要去每个分区查了，这样性能可想⽽知。
To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.
（0.2）索引⽅式
性能依次降低
1.主键分区
  主键分区即字段是主键同时也是分区字段，性能最好
2. 部分主键+分区索引
  使⽤组合主键⾥⾯的部分字段作为分区字段，同时将分区字段建索引
3.分区索引
  没有主键，只有分区字段且分区字段建索引
4.分区+分区字段没有索引
  只建了分区，但是分区字段没有建索引
分区系列⽂章：
【1】表分区的场景和类型
1、对表分区的原因
  数据库数据越来越⼤，导致单个表中数据太多。以⾄于增删改查询速度变慢，⽽且由于表的锁机制导致应⽤操作也受到严重影响，出现了数据库性能瓶颈。
2、表分区
  表分区就是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储，这个规则就叫做分区函数，可以有不同的分区规则，通过”show plugins”语句可以查看当前MySQL是否⽀持表分区功能，mysql5.7社区版默认开启了表分区功能。
3、进⾏表分区的优势
（1）可以允许在⼀个表⾥存储更多的数据，突破磁盘限制或者⽂件系统限制
（2）对于从表⾥将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可
（3）对某些查询和修改语句来说，可以⾃动将数据范围缩⼩到⼀个或⼏个表分区上，优化语句执⾏效率。⽽且可以通过显⽰指定表分区来执⾏语句
4、表分区的类型
（1）RANGE表分区：范围表分区，按照⼀定的范围值来确定每个分区包含的数据
（2）LIST表分区：列表表分区，按照⼀个确定的值来确定每个分区包含的数据
（3）HASH表分区：哈希表分区，按照⼀个⾃定义的函数返回值来确定每个分区包含的数据
（4）KEY表分区：key表分区，与哈希表分区类似，只是⽤MySQL⾃⼰的HASH函数来确定每个分区包含的数据
【2】各种类型表分区的使⽤
2.0 进⾏表分区时需要注意的事项
当表中含有主键或唯⼀键时，每个被⽤作分区函数的字段必须是表中唯⼀键和主键的全部或⼀部分，否则就⽆法创建分区表。
有主键时必须⽤主键，⽆主键时可以⽤唯⼀键
# 由于唯⼀键和主键没有相同的字段，所以⽆法创建表分区
mysql>create table pattern_test01(
-> id int primary key,
-> name varchar(12) unique,
-> age int) partition by range (id)
-> (partition p0 values less than(10),
-> partition p1 values less than(20));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
# 不使⽤唯⼀键可创建成功
mysql>create table pattern_test02(
-> id int primary key,
-> name varchar(12),
-> age int) partition by range (id)
-> (partition p0 values less than(10),
-
> partition p1 values less than(20));
Query OK, 0 rows affected (0.16 sec)
# 同时将主键设置为唯⼀键
mysql>create table pattern_test03(
-> id int,name varchar(12),age int,
->primary key (id,name),
->unique key (id)) partition by range (id)
-> (partition p0 values less than(10),
-> partition p1 values less than(20));
Query OK, 0 rows affected (0.14 sec)
2.1、RANGE表分区的使⽤
RANG表分区(范围表分区)，按照⼀定的范围值来确定每个分区包含的数据，分区函数使⽤的字段必须只能是整数类型，分区的定义范围必须是连续的，且不能有重叠部分，通
过使⽤VALUES LESS THAN来定义分区范围，表分区的范围定义是从⼩到⼤定义的。
mysql>create table range_test(id int not null,
name1 varchar(30),name2 varchar(30),
hired date not null default'1970-01-01',
separated date not null default'2999-12-31',
job_code int not null, store_id int not null)
partition by range (store_id)
(partition p0 values less than(6),partition p1 values less than(12),
partition p2 values less than(18),partition p3 values less than(24));
# 插⼊数据
mysql>insert into range_test values(1,'dayi','dayi123',now(),now(),2,6),(2,'dy','liu',now(),now(),5,10);
# 查询时可以指定具体的分区表查询
mysql>select*from range_test partition (p1);
+----+-------+---------+------------+------------+----------+----------+
| id | name1 | name2 | hired | separated | job_code | store_id |
+----+-------+---------+------------+------------+----------+----------+
|1| dayi | dayi123 |2018-09-30|2018-09-30|2|6|
|2| dy | liu |2018-09-30|2018-09-30|5|10|
+----+-------+---------+------------+------------+----------+----------+
# 由于分区表p0中没有数据所以查不到
mysql>select*from range_test partition (p0);
Empty set (0.00 sec)
# 查看数据⽂件时每个分区表都会⽣成⼀个独⽴的数据⽂件
]# ls /data/mysql/data/test/range_test*
range_test.frm
range_test#P#p0.ibd
range_test#P#p1.ibd
range_test#P#p2.ibd
range_test#P#p3.ibd
当插⼊的数据的分区字段的值不在分区表指定的范围时会报错。这是需要修改下表的定义，可以使⽤MAXVALUE关键词表⽰可能的最⼤值。
对timestamp字段类型可以使⽤的表达式⽬前仅有unix_timestamp（作⽤是将时间转化为时间戳），其他的表达式都不允许。对于date及datetime类型的数据可以将”year”提取出
来。
# 依据timestamp类型字段表分区时将该字段转化成时间戳
mysql>CREATE TABLE range_test02 ( id INT NOT NULL,name VARCHAR(20) NOT NULL,update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )PARTITION BY RANGE ( UNIX_TIM ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-01-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-10-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE) );
mysql>insert into range_test02 values(1,'dayi123',now()),(2,'testname','2030-01-02 12:00:00');
Query OK, 2 rows affected (2.34 sec)
# 使⽤date类型的数据表分区时可将”year”提取出来利⽤”year”进⾏表分区
mysql>create table range_test04(tstamp date)
partition by range(year(tstamp))(
partition p0 values less than(2018));
2.2、list表分区的使⽤
使⽤list表分区时，PARTITION BY LIST(expr)分区函数表达式必须返回整数，取值范围通过VALUES IN (value_list)定义。
查看mysql索引# 创建基于list表分区的表
mysql>create table list_test01(id int not null,
name varchar(30),hired date not null default'1970-01-01',
separated date not null default'9999-12-31',
job_code int,store_id int)
partition by list(store_id)
(partition p_north values in (1,4,7),
partition p_east values in (2,5,8),
partition p_west values in (3,6,9));
基于list表分区的表创建注意项：
（1）对List表分区来说，没有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有定义的取值则会报错.
（2）当有主键或者唯⼀键存在的情况下，分区函数字段需要包含在主键或唯⼀键中。
2.3、hash表分区的使⽤
哈希表分区是按照⼀个⾃定义的函数返回值来确定每个分区包含的数据，这个⾃定义函数也可以仅仅是⼀个字段名字
  通过PARTITION BY HASH (expr)⼦句来表达哈希表分区，其中的expr表达式必须返回⼀个整数，基于
分区个数的取模（%）运算。
  根据余数插⼊到指定的分区，对哈希表分区来说只需要通过” PARTITIONS”定义分区的个数，其他的事情由内部完成；如果没有写明PARTITIONS字段，则默认为1。
# 创建基于hash表分区的表
mysql>create table hash_test01(id int not null,name varchar(30),
hired date not null default'1970-01-01',
separated date not null default'2999-12-31',
job_code int,store_id INT) partition by hash(store_id)
partitions 5;
# 查看创建的hash表分区的分区表
mysql> select table_name,partition_name from information_schema.partitions where table_name='hash_test01';
+-------------+----------------+
| table_name | partition_name |
+-------------+----------------+
| hash_test01 | p0 |
| hash_test01 | p1 |
| hash_test01 | p2 |
| hash_test01 | p3 |
| hash_test01 | p4 |
+-------------+----------------+
2.4、key表分区的使⽤
key表分区与哈希表分区类似，只不过哈希表分区依赖于⾃定义的函数，⽽key表分区的哈希算法是依赖MySQL本⾝。
  Key表分区通过CREATE TABLE ... PARTITION BY KEY ()语句创建，括号⾥⾯可以包含0个或者多个字段，所引⽤的字段必须是主键或者主键的⼀部分;
  如果括号⾥⾯没有字段，则代表使⽤主键，如果表中没有主键但有唯⼀键，则使⽤唯⼀键，但唯⼀键字段必须定义为not null，否则报错。Key表分区所引⽤的字段未必是整数类型，其他的类型也可以使⽤。
# 没有指定字段，则使⽤主键
  mysql> CREATE TABLE key_test01 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;
# 指定字符串作为key表分区的字段
  mysql> create table key_test02(id int,tname char(30) primary key) partition by key(tname) partitions 10;
2.5、创建⼦表分区
  ⼦表分区，是在表分区的基础上再创建表分区的概念，每个表分区下的⼦表分区个数必须⼀致，在MySQL5.7版本中，⼦表分区必须是范围/列表分区+哈希/key⼦表分区的组合。
# 创建字表分区
mysql>create table test_zb01(id int,purchased date)
partition by range(year(purchased))
subpartition by hash(to_days(purchased))
subpartitions 2
(partition p0 values less than(2000),
partition p1 values less than(2020),
partition p2 values less than maxvalue);
# 查看字表的表名
mysql> select table_name,partition_name,SUBPARTITION_NAME from information_schema.partitions where table_name='test_zb01';
+------------+----------------+-------------------+
| table_name | partition_name | SUBPARTITION_NAME |
+------------+----------------+-------------------+
| test_zb01 | p0 | p0sp0 |
| test_zb01 | p0 | p0sp1 |
| test_zb01 | p1 | p1sp0 |
| test_zb01 | p1 | p1sp1 |
| test_zb01 | p2 | p2sp0 |
| test_zb01 | p2 | p2sp1 |
+------------+----------------+-------------------+
字表在不指定表名时，表名时系统默认分配的。⼦表分区也可以显⽰的指定⼦表分区的名字。
# 创建字表时指定⼦分区表名
mysql>create table test_zb02(id int,purchased date)
partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2
(partition p0 values less than(2000)
(subpartition s1,subpartition s2),
partition p1 values less than(2020)
(subpartition s3,subpartition s4),
partition p2 values less than maxvalue
(subpartition s5,subpartition s6));
【3】表分区的管理
表分区创建完成后可以通过alter table命令可以执⾏增加，删除，重新定义，合并或者拆分表分区的管理动作。对不同类型的表分区管理⽅法也有不同。
3.1、表分区的删除，表分区数据的删除
（1）表分区删除：
对范围表分区和列表表分区来说，删除⼀个表分区时使⽤命令
  ” ALTER TABLE table_name DROP PARTITION partition_name”删除；
  多个分区⽤逗号分隔：alter table tab drop partition p1,p2;
  删除表分区的动作不光会把分区删掉，也会把表分区⾥原来的数据给删除掉。
  删除所有分区：alter table tea_prop_rich_log remove partitioning; （不会删除数据）
mysql> show create table range_test;
+------------+----------------------------------------------------+
|Tabl|CreateTable
+------------+-----------------------------------------+
| range_test |CREATE TABLE `range_test` (
`id` int(11) NOT NULL,
`name1` varchar(30) DEFAULT NULL,
`name2` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT'1970-01-01',
`separated` date NOT NULL DEFAULT'2999-12-31',
`job_code` int(11) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (12) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (18) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (24) ENGINE = InnoDB) */|
+------------+----------------------------------------------------------+
# 删除分区表p3
  mysql> alter table range_test drop partition p3;
（2）表分区数据的删除：
要从 partition 删除所有⾏p0，请使⽤以下语句：
ALTER TABLE t1 TRUNCATE PARTITION p0;
3.2、增加表分区
（1）范围表分区(range)上增加及重新组织表分区
  在原分区上增加⼀个表分区可以通过alter table … add partition语句来完成。
  对范围表分区来说，增加的表分区必须在尾部增加，在头部或者在中间增加都会失败，对此可以通过REORGANIZ增加表分区及重组表分区。
# 增加表分区
mysql>alter table range_test add partition (partition p3 values less than maxvalue);
# 使⽤REORGANIZ命令创建表分区，相当于将p0分成两个表分区
mysql>alter table range_test REORGANIZE PARTITION p0 into (partition n0 values less than (3),partition n1 values less than (6));
#使⽤REORGANIZ命令也可将拆分的表分区合并
mysql>alter table range_test REORGANIZE PARTITION n0,n1 into (partition p0 values less than(6));
（2）列表表分区（list）上增加及重新组织表分区
对列表表分区来说，只要新增加的分区对应的值在之前的表分区中没有出现过，就可以通过alter table… add partition来增加。也可以通过REORGANIZE命令将之前的多个分区合并成⼀个或⼏个分区，但要保持分区值⼀致；对列表分区重组时，重新组织的分区必须是相邻的分区。
# 增加表分区
mysql>alter table list_test01 add partition (partition pCentral values in(10,11,12));
# 重新组织表分区
mysql>
alter table list_test01
REORGANIZE partition p_north,p_east,p_west
into (
partition p0 values in (1,2,3),
partition p1 values in (4,5,6)
,partition p2 values in (7,8,9)
);
如果表⾥已有的数据在新重组的分区中没有指定的值，则数据会丢失
3.3、哈希表分区和key表分区的管理
对哈希表分区和KEY表分区的管理⼿段与范围和列表表分区完全不同，⽐如不能删除表分区；
  但可以通过ALTER TABLE ... COALESCE PARTITION语句合并表分区，其partition后⾯的数字代表缩减的个数，⽽不是缩减到的个数；如果是增加表分区，则可以使⽤add partition语句。
# 将表hash_test01的表分区缩减到三个
mysql>alter table hash_test01 COALESCE PARTITION 3;
# 对表has_test01再增加10个表分区
mysql>alter table hash_test01 ADD PARTITION PARTITIONS 10;
3.4、分解与合并分区
分解分区：
Reorganizepartition关键字可以对表的部分分区或全部分区进⾏修改，并且不会丢失数据。分解前后分区的整体范围应该⼀致。
alter table te
reorganize partition p1 into
(
partition p1 values less than (100),
partition p3 values less than (1000)
); ----不会丢失数据
合并分区:
Merge分区：把2个分区合并为⼀个。
alter table te
reorganize partition p1,p3 into
(partition p1 values less than (1000));
3.5、重新定义 hash和range 分区表
#重新定义hash分区表:
Alter table emp partition by hash(salary) partitions 7;
----不会丢失数据
#重新定义range分区表:
Alter table emp partition by range(salary)
(
partition p1 values less than (2000),
partition p2 values less than (4000)
); ----不会丢失数据
3.6、重建/优化/分析/修补/检查分区
重建分区：
这和先删除保存在分区中的所有记录，然后重新插⼊它们，具有同样的效果。它可⽤于整理分区碎⽚。
ALTER TABLE emp rebuild partitionp1,p2;
优化分区：
如果从分区中删除了⼤量的⾏，或者对⼀个带有可变长度的⾏（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使⽤“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使⽤的空间，并整理分区数据⽂件的碎⽚。
ALTER TABLE emp optimize partition p1,p2;
分析分区：
读取并保存分区的键分布。
ALTER TABLE emp analyze partition p1,p2;
修补分区：
修补被破坏的分区。
ALTER TABLE emp repairpartition p1,p2;
检查分区：
可以使⽤⼏乎与对⾮分区表使⽤CHECK TABLE 相同的⽅式检查分区。
ALTER TABLE emp CHECK partition p1,p2;
这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发⽣了这种情况，使⽤“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
3.7、查询指定分区的数据
select count(1) from t1 partition(p9);
【4】表分区的其他管理操作
4.1、创建range和list表分区时，分区函数可以包含多个字段
  分区多字段函数所涉及的字段类型可以包括 TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER)、 BIGINT、DATE、DATETIME、CHAR、VARCHAR、BINARY、VARBINARY。
# 创建多字段表分区的rang分区表
mysql>create table columns_test(id int not null,name varchar(30),
tname varchar(30),store_id int) partition by range columns(id,tname,store_id)
(partition p0 values less than (100,'dayi123',3),
partition p1 values less than (200,'dy',6),
partition p2 values less than (MAXVALUE,MAXVALUE,MAXVALUE));
4.2、对null值得处理
（1）在范围表分区中，如果插⼊的是NULL值，则将数据放到最⼩的分区表⾥
（2）在list表分区中，⽀持NULL值的唯⼀情况就是某个分区的允许值中包含NULL
（3）对哈希表分区和Key表分区来说，NULL值会被当成0值对待
4.3、表分区数据的交换
对分区表可以通过 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 命令将⼀个分区或者是⼦分区的数据与普通的表的数据相互交换，其本⾝的表结构不会变化；  交换的分区表和⽬标表必须结构完全相同，包括字段，类型，索引，存储引擎必须完全⼀样。
# 创建⽤于数据交换分区的表
mysql>create table ext_test01 like range_test;
# 数据交换
mysql>alter table ext_test01 remove partitioning;
mysql>alter table range_test exchange partition p1 with table ext_test01;
# 查看数据交换后各表的数据
mysql>select*from ext_test01;
+----+-------+---------+------------+------------+----------+----------+
| id | name1 | name2 | hired | separated | job_code | store_id |
+----+-------+---------+------------+------------+----------+----------+
|1| dayi | dayi123 |2018-09-30|2018-09-30|2|6|

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。