mysql分区表

一、分区表的原理:

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

MySQL支持5种分区方式：RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有自己的使用场景。

二、分区表的限制：

1.一个表最多只能有1024个分区（mysql5.6之后支持8192个分区） 2.在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。 3.如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引 4.分区表中无法使用外键约束 5.mysql数据库支持的分区类型为水平分区，并不支持垂直分区，因此，mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中 6.目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

三、分区表的使用语法：

创建分区表：

range分区

CREATE TABLE table_name (

id INT AUTO_INCREMENT,

fieldX VARCHAR(100) NOT NULL,

order_day DATETIME NOT NULL,

PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

PARTITION p_2015 VALUES LESS THAN (2015),

PARTITION p_2016 VALUES LESS THAN (2016),

PARTITION p_2017 VALUES LESS THAN (2017),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

这段语句表示将表内数据按照order_day的年份范围进行分区，2015年一个区，2016一个，2017一个，剩下的一个；注:MAXVALUE为最大值

注意:如果要这么做，则order_day必须包含在主键中，如果没有设置MAXVALUE 这个分区，会产生一个问题，就是当年份超过了最大值，到了2018、2019时，需要手动创建这些分区，否则插入数据时会报错。

如果你不愿意这么做，可以尝试使用HASH，如：

CREATE TABLE table_name (
    
id INT PRIMARY KEY AUTO_INCREMENT,
    
order_day DATETIME NOT NULL,
    
PRIMARY KEY(id)
    
)ENGINE=Innodb

PARTITION BY HASH (id DIV 1000000);

这种分区表示每 100W 条数据建立一个分区，且没有阈值范围的影响

新增分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_2018 VALUES LESS THAN (2018));

删除分区：

ALTER TABLE table_name DROP PARTITION p_2018;

这里需要注意的是，一旦删除了一个分区，该分区的所有数据也将被删除

分区的合并：

ALTER TABLE table_name

REORGANIZE PARTITION p_201701,p_201702,p_201703 INTO

(PARTITION p_2017Q1 VALUES LESS THAN (201704));

分区的分解：

ALTER TABLE table_name

REORGANIZE PARTITION p_2017Q1 INTO(
    
PARTITION p_201701 VALUES LESS THAN (201702),
    
PARTITION p_201702 VALUES LESS THAN (201703),
    
PARTITION p_201703 VALUES LESS THAN (201704))

重建分区：

ALTER TABLE table_name rebuild PARTITION p_2017;

重建分区的操作和先删除保存在分区中的所有记录，然后重新插入它们具有同样的效果，它可用于整理分区的碎片

优化分区：

ALTER TABLE table_name OPTIMIZE PARTITION p_2017;

如果从分区中删除了大量的行，或者对一个带有可变长度的行（有VARCHAR、BLOB、TEXT类型的列）做了许多修改，可以使用 "ALTER TABLE ... OPTIMIZE PARTITION" 来回收没有使用的空间，并整理分区数据文件的碎片。

分析分区：

ALTER TABLE table_name ANALYZE PARTITION p_2017;

此操作可以读取并保存分区的键分布

修补分区：

ALTER TABLE table_name REPAIR PARTITION p_2017;

用来修补被破坏的分区

检查分区：

ALTER TABLE table_name CHECK PARTITION p_2017;

这个命令可以告诉你表table_name的分区p_2017中的数据或索引是否已经被破坏。如果发生了这种情况，可使用 "ALTER TABLE ... REPAIR PARTITION" 来修补该分区。

对已有表进行分区

一般的，我们可以用如下两种方式办到：

1.可以使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。

ALTER TABLE table_name PARTITION BY RANGE (YEAR(order_day)) (
PARTITION p_2018 VALUES LESS THAN (2018))

2.新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表。

查看数据在每个分区中的分布情况

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='table_name';(不是很准确)

注意事项:

1.创建分区之后 想要新增分区 分区的value不能小于原来的分区value 否则报错 VALUES LESS THAN value must be strictly increasing for each partition

2.如果想要在分区中间插入一个分区 可以分解分区 3.对于 RANGE 分区的查询，优化器只能对 YEAR(), TO_DAYS(),TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化