mysql分区类型及操作

一，什么是分区？

---

• 1，通俗点说：分区就是将表格里面的数据横向切分，同一个区的数据会放在一起，然后在查询的时候只查某个或某些区的数据。但是分区对于用户是透明的，因为数据库的分区表还是一张表。这和分表是有区别的，分表是拆分为了几张表，分区还只是一张表。
• 2，mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

二，分区的类型。

---

• 1，range分区。
  按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。一般使用这种分区方式大都是对连续的值进行分区，常见的如：按年份，日期进行分区。

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
partition BY 
range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
              PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB,
              PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB );

按照这种方式进行分区，那么如果创建日期在2017年2月1号之前的数据会放在p201701的这个分区；如果创建日期在2017年3月1号之前的数据会放在p201702的这个分区；如果创建日期在2017年4月1号之前的数据会放在p201703的这个分区；如果创建日期都不在前面的几个区间，那么会放在p2018的这个分区。

• 2，List分区
  类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (1,5,6,9,17),
PARTITION pEast VALUES IN (2,4,10,11,19,20),
PARTITION pWest VALUES IN (3,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；

store_id在1,5,6,9,17这些值中时候会被分配到pNorth这个分区中，其他的也一样。
注意：如果插入一行值时，里面的store_id不在上面的这列value_list时会出现插入失败并报错。而且。List分区没有range分区那样，range分区的“VALUES LESS THAN MAXVALUE”会将其他的值包含在内的定义。

• 3，Hash分区
  hash分区主要用来分散热点读取，确保数据在预定确定个数分区中尽可能的平均分布。一个表执行hash分区，mysql会对分区键应用一个散列函数，以此确定数据应该放在n个分区中的哪一个分区。hash分区支持两种散列函数（分区方式）：取模算法（默认hash分区方式）和线性的2的幂的运算法则（liner hash 分区）。
  常规hash分区：

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY HASH(id)
PARTITIONS 5;

线性hash分区：

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LINEAR HASH(id)
PARTITIONS 5;

---

线性hash的计算方式：
假设一个表达式expr, 当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到：

1.找到下一个大于num的2的幂，我们把这个值称为V ，它可以通过下面的公式得到：
2. V = POWER(2, CEILING(LOG(2, num)))（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。
3. 设置 N = F(column_list) & (V - 1).
4. 当 N >= num:
设置 V = CEIL(V / 2)
设置 N = N & (V - 1)
例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插入两行记录到表t1中，其中一条记录col3列值为’2003-04-14’，另一条记录col3列值为’1998-10-19’。第一条记录将要保存到的分区确定如下：
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR(‘2003-04-14’) & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下：
V = 8
N = YEAR(‘1998-10-19’) & (8-1)
= 1998 & 7
= 6
(6 >= 4 为真（TRUE）: 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
= 6 & 3
= 2
(2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

• 4，key分区
  key分区和hash分区的区别：
  
  • 1）hash分区允许用户自定义的表达式，而key分区不允许使用用户自定义的表达式。
  • 2）hash分区只支持整数分区，key分区支持除了blob或text类型之外的其他数据类型分区。
  • 3）与hash分区不同，创建key分区表的时候，可以不指定分区键，默认会选择使用主键/唯一键作为分区键，没有主键/唯一键,必须指定分区键。

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
email VARCHAR(30) NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
PARTITION BY LINEAR Key(email)
PARTITIONS 5;

三，分区的操作：

• 1，在创建表结构的时候实现分区：

CREATE TABLE employees (
id INT NOT NULL,
first_name VARCHAR(30),
last_name VARCHAR(30),
store_id INT NOT NULL,
create_time DATE NOT NULL DEFAULT '9999-12-31',
)
partition BY 
range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
              PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB);

• 2,在创建完表结构的时候进行分区。

alter table employees  partition by range(to_days(create_time))
            (PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,
             PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,
             PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB);

• 3，增加分区。

alter table employees add partition(PARTITION p2018 VALUES LESS THAN MAXVALUE);  

• 4,删除分区。

alter table employees drop partition p201702; 

• 5,重新分区。

ALTER TABLE employees REORGANIZE PARTITION p201701,p201702,p201703,p2018 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  

注意：

• 1）hash和key分区不能用REORGANIZE。
• 2）只能从range分区列表最大端增加分区。
• 3）增加list分区，不能添加一个包含现有分区值列表中的任意值分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区。
• 4）重新定义range分区，只能够重新定义相邻的分区，同时重新定义的分区区间必须和原分区区间覆盖相同的区间。

四，分区的优缺点。

---

优点：

• 1，分区可以分在多个磁盘，存储更大一点。
• 2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了。
• 3，进行大数据搜索时可以进行并行处理。
• 4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
• 5，分区表的数据某些情况更容易维护。如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化，检查，修复等操作。

限制：

• 1，一个表最多只能有1024个分区。
• 2，在Mysql5.1中，分区表达式必须是整数，或者是返回整数的表达式，在Mysql5.5中，某些场景中可以直接使用列来进行分区。
• 3，如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
• 4，分区表中无法使用外键约束。

分区策略基于两个非常重要的假设：查询都能够过滤掉很多额外的分区，分区本身不会带来很多的额外的代价。在分区会在以下的场景中会有问题。

• 1，NULL值会使分区过滤无效。
• 2，分区列和索引列不匹配。
• 3，选择分区的成本可能很高。对于范围分区来说，回答“这一行属于哪个分区”，“这些符合查询条件的行在哪些分区”这样问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。对于大多数系统来说，100个左右的分区是没有问题的。
• 4，打开并锁住所有底层表的成本可能很高。

• 5，维护分区的成本可能很高。新增或者删除分区时，可能会很快。但是重组分区或者类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的分区，然后将数据复制到其中，最后删除原分区。
  如上所述：分区表不是什么“银弹”。下面是目前分区中一些其他的限制：

• 1，所有分区都必须使用相同的存储引擎。

• 2，分区函数中可以使用的函数和表达式也有一些限制。
• 3，某些存储引擎不支持分区。
• 4，对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作。
• 5，对于MYISAM表，使用分区表时需要打开更多的文件描述符。