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()这类函数进行优化