mysql hash分区 数目_MySQL 分区

MySQL 分区

分区是指根据一定的规则，把一个表分解成多个更小更易管理的部分，逻辑上只有一个表或一个索引，

但是实际上该表可能由数个物理分区对象组成，每个分区都是一个独立的对象，每个分区可以独自处理，也可以作为表的一部分处理。分区对应用是完全透明的。

分区的优点体现在以下方面：

和单个磁盘或者文件系统相比，可以存储更多数据

优化查询，在Where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；像sum和count这类聚集函数的查询时，可以很容易地在每个分区上并行地处理，最终汇总所有分区的结果

对于已经过期的数据，可以通过删除与这些数据有关的分区来快速删除数据

跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

MySQL中分区有如下几种类型：

Range分区：指定区间的范围，把数据分配到不同的分区

List分区：类似于Range分区，但是List是基于枚举出的值列表分区

Hash分区：基于给定的分区个数，把数据分配到不同的分区

Key分区：类似于Hash分区

在MySQL5.1版本中，Range、List、Hash分区都要求分区键必须是INT类型，或者通过表达式返回一个INT类型的数值，也就是说5.1只支持整数分区，唯一的例外就是分区类型为Key分区时，可以使用其它类型的列(BLOB和TEXT除外)作为分区键。

如果一个表有主键/非空唯一键，那么分区键必须使用该主键/非空唯一键；如果没有主键/非空唯一键，那只要该列类型被分区类型允许，就可以使用。

Range分区

Range分区利用取值范围将数据分区，区间连续且区间之间不得有重叠，使用VALUES LESS THAN操作符进行定义：

create table emp (

-> id int not null primary key,

-> name varchar(100) ,

-> hired date )

-> partition by range (id) (

-> partition p0 values less than (5),

-> partition p1 values less than (10),

-> partition p2 values less than MAXVALUE);

emp表中id在0-4之间的记录将保存在 p0分区，5-9的在p1分区，10 - int的最大值之间的在p2分区。

注意，按上面的语法paitition by range (id)中，括号内的字段必须为整数类型，但是如果是时间类型的怎么办？

1.可以用函数YEAR()和TO_DAYS()来转换，例如：

create table emp (

-> id int(11),

-> name varchar(100),

-> hired date)

-> partition by range (YEAR(hired)) (

-> partition p0 values less than (2017),

-> partition p1 values less than (2018),

-> partition p2 values less than MAXVALUE);

2.如果MySQL版本是5.5及之后的版本，那么可以用partition by range columns ：

create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> )

-> partition by range columns (hired) (

-> partition p0 values less than ('2017-01-01'),

-> partition p1 values less than ('2018-01-01'),

-> partition p2 values less than MAXVALUE

-> )

-> ;

当你要删除一个分区的数据时，只需

ALTER TABLE table_name DROP PARTITION partition_name

即可删除该分区的数据，比delete高效

查询时，MySQL会根据条件判断需要扫描的分区，而不是全表扫描：

List分区

List分区时根据离散的值列表告诉数据库特定的值属于哪个分区，SQL定义语法：

create table good (

-> id int,

-> name varchar(255),

-> category int

-> )

-> partition by list (category) (

-> partition p0 values in (1,3,5),

-> partition p1 values in (2,4,6)

-> );

按上述定义后，若插入category=7的记录，那么是会报错的，你必须把所有可能值都枚举完，不像range分区有MAXVALUE可以用。

MySQL5.5及其之后的版本中List分区也支持非整数列

Columns分区

Columns分区是MySQL5.5引入的新的分区类型，解决了Range分区和List分区只支持整数列的问题，Columns分区支持的分区列类型为：

整数：tinyint、smallint、mediumint、int和bigint

日期时间：date和datetime

字符串类型：char、varchar、binary和varbinary

Columns分区仅支持一个或多个字段名作为分区键，不再支持表达式作为分区键。

Columns分区支持多列分区：

create table aa (

-> a int,

-> b int

-> )

-> partition by range columns(a,b) (

-> partition p0 values less than (0,10),

-> partition p1 values less than (10,10),

-> partition p2 values less than (10,20),

-> partition p3 values less than (10,35),

-> partition p4 values less than (10,MAXVALUE),

-> partition p5 values less than (MAXVALUE,MAXVALUE)

-> );

分区键包含多列时，其比较是基于元组的比较，也就是基于字段组的比较，假设有两个元组(A1, B1)和(A2, B2)，那么其比较规则为：

(A1 < A2) OR ((A1 == A2) && (B1 < B2))

例如：

Hash分区

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行Hash分区时，MySQL会对分区键应用一个散列函数，依=以此确定数据应当放在分区中的哪个分区中。

MySQL支持两种Hash分区：

常规Hash分区：使用取模算法

线性Hash分区(Linear Hash)：使用一个线性的2 的幂的运算规则

常规Hash分区语法：

create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> ) engine = innodb

-> partition by hash(id) partitions 3;

id为0的将在p0分区，id为1的将在p1分区....以此类推：

常规Hash不适合需要灵活变动分区的需求，因为分区数增加或减少后都得重新计算分区，否则数据就错位了。

为此我们可以用线性Hash分区：

create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> ) engine = innodb

-> partition by linear hash(id) partitions 3;

看一下数据分布：

可以看到，数据往哪个分区的计算方法稍有区别：假如有N个分区，要插入的数据的id为Q，那么找到下一个大于等于N的2的幂，记为M，那么分区下标X = Q & (M - 1)。其实说简单点，原来常规Hash是直接对分区数取模，但是线性Hash就不是这样，而是找出最小的大于等于分区数的2的幂，对该数进行取模，比如分区数为6，那么模数为8，要插入的记录的d为10的话，那么分区下标就为 10 % 8 = 2。这样的好处就是，分区数增加或减少不一定要重新计算分区，因为改变的分区数可能并没有使模数发生变化，因此一条记录原来往哪个分区存，还是往哪个分区存，但是如果分区数变化使得模数变化(比如分区数从7变成9，模数从8变为16)，那么是依然需要重新计算分区的。

线性hash分区的优点是，在分区维护(增加、删除、合并和拆分分区)时，MySQL能够处理得更加迅速；缺点是，对比常规Hash分区，线性Hash分区各个分区数据分布并不是很均匀。

Key分区

Key分区类似于Hash分区，只不过Hash分区允许使用用户自定义得表达式，而Key分区不允许，需要使用MySQL服务器提供的Hash函数；同时Hash分区只支持整数分区，而Key分区支持除了Blob和Text类型外的所有类型作为分区键。在有主键或者非空唯一键的情况下，创建Key分区时可以不指定分区键，MySQL会默认使用主键作为分区键，若没有主键则使用非空唯一键作为分区键。

create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> ) engine = innodb

-> partition by key (id) partitions 4;

对于所有分区，MySQL都允许使用Null值，再Range分区中，Null值被当作最小值来处理；再List分区中，Null值必须被枚举出来，否则就不允许使用插入Null值；再Hash/Key分区中，Null值被当作零值来处理。

分区管理命令

MySQL提供了添加、删除、重定义、合并和拆分分区的命令，这些操作都可以用ALTER TABLE命令来实现

Range分区

create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> )

-> partition by range columns (hired) (

-> partition p0 values less than ('2017-01-01'),

-> partition p1 values less than ('2018-01-01'),

-> partition p2 values less than ('2019-01-01')

-> );

添加分区：

mysql> alter table emp add partition (partition p3 values less than ('2020-01-01'));

添加分区时不能从中间添加，只能从最大的那个分区后面添加：

mysql> alter table emp add partition (partition p4 values less than ('2019-07-01'));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

拆分p0为p00和p01：

mysql> alter table emp reorganize partition p0 into (

-> partition p00 values less than ('2016-07-01'),

-> partition p01 values less than ('2017-01-01')

-> );

合并p00和p01为p0：

mysql> alter table emp reorganize partition p00,p01 into (

-> partition p0 values less than ('2017-01-01')

-> );

删除p2分区：

mysql> alter table emp drop partition p2;

List分区

create table good (

id int,

name varchar(255),

category int

)

partition by list (category) (

partition p0 values in (1,2),

partition p1 values in (3,4)

);

添加分区：

mysql> alter table good add partition (partition p2 values in (5,6));

拆分分区p0为p00和p01：

mysql> alter table good reorganize partition p0 into (

-> partition p00 values in (1),

-> partition p01 values in (2)

-> );

合并分区：

mysql> alter table good reorganize partition p00,p01 into (

-> partition p0 values in (1,2)

-> );

删除分区：

mysql> alter table good drop partition p2;

Hash分区

mysql> create table emp (

-> id int,

-> name varchar(100),

-> hired date

-> ) engine = innodb

-> partition by hash (id) partitions 3;

减少分区的数量：

mysql> alter table emp coalesce partition 2;

增加分区的数量

mysql> alter table emp add partition partitions 4; # 该数字为增加的数量而不是增加后的数量

Key分区

key分区和Hash一致.

无论是什么分区，都可以通过information_schema.partitions表来查看其详细信息：

mysql> desc partitions;

+-------------------------------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG | varchar(512) | NO | | | |

| TABLE_SCHEMA | varchar(64) | NO | | | |

| TABLE_NAME | varchar(64) | NO | | | |

| PARTITION_NAME | varchar(64) | YES | | NULL | |

| SUBPARTITION_NAME | varchar(64) | YES | | NULL | |

| PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | |

| SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | |

| PARTITION_METHOD | varchar(18) | YES | | NULL | |

| SUBPARTITION_METHOD | varchar(12) | YES | | NULL | |

| PARTITION_EXPRESSION | longtext | YES | | NULL | |

| SUBPARTITION_EXPRESSION | longtext | YES | | NULL | |

| PARTITION_DESCRIPTION | longtext | YES | | NULL | |

| TABLE_ROWS | bigint(21) unsigned | NO | | 0 | |

| AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | |

| DATA_LENGTH | bigint(21) unsigned | NO | | 0 | |

| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |

| INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | |

| DATA_FREE | bigint(21) unsigned | NO | | 0 | |

| CREATE_TIME | datetime | YES | | NULL | |

| UPDATE_TIME | datetime | YES | | NULL | |

| CHECK_TIME | datetime | YES | | NULL | |

| CHECKSUM | bigint(21) unsigned | YES | | NULL | |

| PARTITION_COMMENT | varchar(80) | NO | | | |

| NODEGROUP | varchar(12) | NO | | | |

| TABLESPACE_NAME | varchar(64) | YES | | NULL | |

+-------------------------------+---------------------+------+-----+---------+-------+

25 rows in set (0.03 sec)

取消分区