背景

开发的过程发现公司的mysql数据库的建表语句和我们常见的设置字段然后设置InnoDB引擎不同。有DISTRIBUTE BY HASH(id)这些东西。一时间搞不清楚是什么意思，发现可能跟表Mysql表分区有关。就开始了新的学习，当然后面也找到了阿里云ADB的文档。

知识整理

1.什么是表分区

什么是表分区？通俗地讲表分区是将一个大表，根据条件分割成若干个小表。mysql.5.1开始支持数据表分区

如：某用户表的记录超过了600万条仓储信息，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

2.为什么要对表进行分区

为什么要对表进行分区？为了改善大型表以及具有各种访问模式的表的可伸缩性可管理性和提高数据库效率。

分区的一些优点包括：

与单个磁盘或文件系统分区相比，可以存储更多的数据。（分开磁盘存储）
通过删除与增加那些数据有关的分区，很容易地删除或增加那些数据。（整个分区删除）
一些查询可以得到极大的优化。（通过分区字段直接找到分区，缩小范围）
通过跨多个磁盘甚至服务器来分散数据查询，来获得更大的查询吞吐量

3.基本分区类型

分区可以分为下面几种

RANGE分区：

基于属于一个给定连续区间的列值，把多行分配给分区。
LIST分区：

类以于按RANGE分区，区别在于LST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入至到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负数整数值的任何表达式
KEY分区：

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列。且MYSQL服务器提供其自身的哈希函数，必须有一列或多列包含整数

4.基本分区的使用例子

4.1 RANGE分区

基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

例子一：修改表按Range分区

如下：修改表titles之后，当插入一行数据，年份字段from_date小于1985则存入到第一个表分区，若大于1985小于1986则存入第二个分区

ALTER TABLE titles
partition by range (year(from_date))
(
    partition p01 values less than (1985),
    partition p02 values less than (1986),
    partition p03 values less than (1987),
    partition p04 values less than (1988),
    partition p15 values less than (1999),
    partition p16 values less than (MAXVALUE)
);

例子二：创建表同时直接创建分区

在创建表的过程中也可以直接创建分区。例如：
将用户表按照年龄每隔10岁进行分区。
注意！分区的名字基本上遵循其他MySQL标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当主意，分区的名字是不区分大小写的。

mysql>create table t1(id int,name varchar(20),age int)
->partition by range (age)
->(
->partition p01 values less than (10),
->partition p02 values less than(20),
->partition p03 values less than (30),
->partition p04 values less than (maxvalue)
->)；

可以查看数据库数据的表对应的文件,多出了par文件（用于分区信息），和四个分区对应的ibd文件

# cd /var/lib/mysql/tube
# ls
db.opt    t1.frm    t1.par    t1#P#p01.ibd    t1#P#p02.ibd    t1#P#p03.ibd    t1#P#p04.ibd

4.2 LIST分区

类以于按RANGE分区，区别在于LIST分区是基于列值匹配个离散值集合中的某个值来进行选择。 LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“xpr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过"VALUES IN(value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。（第一次看不懂很正常，先看下面的回头看）

例子一：

对于一个综合性的网店来说，商品分为诸多种类。我们可以按照商品ID进行range分区，也可以按照商品的类型（cid）分区。在这个例子中，LIST分区给了我们更多的选择。

如下当插入一条数据之后根据cid判断是属于哪个分区，cid为1、2、3其中一个的时候则存入到第一个分区，同理其它分区也一样。

mysql>create table t2(id int,cid int,name varchar(20),pos_date
datetime)
->partition by list (cid)
->(
->partition p01 values in (1,2,3),
->partition p02 values in (4,5,6),
->partition p03 values in (7,8,9));

4.3 HASH分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。（这里不深入mysql的HASH的过程算法，只是简单的了解分区）

根据某个字段，进行hash运算分配到4个分区中的一个

mysql>create table t2(id int,cid int,name varchar(20),pos_date datetime)
->partition by hash (cid)
->partitions 4;

4.4 LINEAR HASH

与普通hash的运算不同，使得各种操作效率更高。缺点就是hash运算导致数据存放不均匀，可能会出现某些节点数据特别多而某些节点数据特别少的情况。简单知道了解有这么个东西。

4.5 KEY分区

和HASH分区比较类似使用的MySQL提供的函数进行分区，很少使用。

mysql>create table t2 (id int,cid int,name varchar(20),pos_date datetime)
->partition by linear key (cid)
->partitions 4;

5 多列分区

COLUMNS关键字允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区

如下，存入某一个分区的条件必须符合字段a、字段b都小于指定值

mysql>create table t3(a int,b int,c int)
-> partition by range columns(a,b)
-> (
-> partition p01 values less than (10,10),
-> partition p02 values less than (10,20),
-> partition p03 values less than (10,30),
-> partition p04 values less than(10,maxvalue),
-> partition p05 values less than (maxvalue,maxvalue));

6 子分区

子分区是分区表中每个分区的再次分割。
子分区可以用于特别大的表，在多个磁盘间分配数据和索引。
每个分区必须有相同数量的子分区。
如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。换句话说下面的语句将执行失败：

例子一：简单使用

如下，先根据年份进行Range分区，再根据日期进行hash分区

mysql>CREATE TABLE t5 (id INT,udate DATE)
->PARTITION BY RANGE(YEAR(udate))
->SUBPARTITION BY HASH(TO DAYS(udate))
->SUBPARTITIONS 2
->(
->PARTITION p0 VALUES LESS THAN(1990),
->PARTITION p1 VALUES LESS THAN(2000),
->PARTITION p2 VALUES LESS THAN MAXVALUE);

例子二：将每个子分区保存在不同的存储上，优化I/○性能。

需要提前创建好对应目录（挂载硬盘），好处是不同分区的查询压力分担到不同的磁盘上

mysql>CREATE TABLE ts (id INT,udate DATE)
->PARTITION BY RANGE(YEAR(udate))
->SUBPARTITION BY HASH(TO_DAYS(udate))
->(PARTITION p0 VALUES LESS THAN(1990)
->(
->SUBPARTITION s0
->DATA DIRECTORY ='/disko/data'
->INDEX DIRECTORY ='/disko/idx',
->SUBPARTITION s1
->DATA DIRECTORY ='/disk1/data'
->INDEX DIRECTORY ='/disk1/idx'
->),

7 分区的管理维护

7.1 简单修改分区

下面这个例子使用ALTER语句简单修改了分区。效果和先删除原表再按新的分区方式重新建表效果相同。
按照使用id列值作为建的基础，通过KEY分区把它重新分成两个分区

mysql > ALTER TABLE t3 PARTITION BY KEY(id)PARTITION 2

7.2 RANGE&LIST分区之删除指定分区

删除指定的RANG或LIST分区非常简单但是要注意删除分区也同时删除了该分区中所有的数据。如果仅仅想删除分区中的数据，应该使用TRUNCATE语句

mysql>ALTER TABLE t1 DROP PARTITION p02;
mysql>ALTER TABLE t1 TRUNCATE PARTITION p01;

7.3 RANGE分区之增加分区

要增加一个新的RANGE或LIST分区到一个前面已经分区了的表，使用"ALTER TABLE.ADD PARTITION”语句。对于使用RANGE分区的表，可以用这个语句添加新的区间到已有分区的序列的前面或后面。
主意！时于RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。

7.4 LIST分区之增加分区

对LIST分区增加分区的语去和RANGE类以。
注意！增加新分区时，不可以包含现有分区值列表中的任意值。

总结

mysql的表分区可以通过把表在mysql的层面拆分存储到不同的物理介质上而让存储的数据量更大，对于数据量大的情况下也是方便管理。但是所有的设计都是有代价的，表分区所谓的提升性能也不是我们常见应用的提升性能，而是数据量极大超过几个TB的情况下在通过分区键提前定位到指定分区文件缩小查询范围的情况下提升性能。

而对于多数应用，如果使用分区反而很可能减低性能，因为查找的方位如果涉及多个分区中的数据，你就想想查找一个文件快还是多个文件快。

而在目前分库分表的生态技术成熟的情况下，表分区更加是用更少（生态也不好和方案也不多）。本人也是因为公司用到阿里云得ADB（大数据方向）而涉及到分区技术才提前了解一下。

所以这个技术了解一下就行了。

Mysql的表分区知识

背景