【运维】分区

矫情吗；* 2022-09-25 15:20 167阅读 0赞

# 前言 #

Github：[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]

# 概述 #

MySql 从 5.1 版本开始支持分区功能，分区的原则就是把一个数据库表分界为多个小的，容易管理的部分。

就访问数据库而言，逻辑上只有一个表或一个索引，实际上可能由多个物理分区对象组成，每一个分区

的对象都是一个独立的对象，可以单独处理，也可以作为表的一部分处理。不管我们怎么分区，不影响

应用的业务逻辑。

**为什么要使用分区，分区的优点如下？**

1）和单个磁盘或系统文件相比，分区可以存储更多数据；

2）优化查询，准确定位；

3）对于某些过去或一类不需要的数据，通过删除和这些数据相关的分区实现快速删除数据；

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

通过 SHOW VARIABLES LIKE '%partition%' 命令确定当前 MySql 版本是否支持分区的功能。

如果我们看到了have\_partitioning 对应的是YES，说明该 MySql 版本支持分区功能。

使用 show engines 命令输出信息，不包含是否支持分区的信息，只能通过 show variables 命令判断是否支持分区功能。

MySql 支持使用大部分存储引擎创建分区表，比如：MyISAM，InnoDB，Memory 等存储引擎可以创建分区表,

但是不支持 MERGE 和 CSV 存储引擎创建分区表。同时注意的是一个表不能出现多种存储引擎创建的分区。

# 一 分区类型介绍 #

**MySql 主要分区类型：**

1）RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区；

2）LIST分区：基于枚举出的列表值进行分区，与RANGE不同的是前者为枚举列表，后者基于区间范围；

3）HASH分区：基于给定的分区个数，把数据分配到不同的分区；

4）KEY分区：与HASH分区类似，都是先给定分区个数，然后把数据分配到不同的分区，只是算法有区别；

**注意：**

MySql 无论使用哪种分区类型，分区字段的原则是：如果表上有主键或唯一键，就必须使用主键或唯一键作为

分区字段，否则，通过其他字段创建分区表，返回错误；如果表上没有主键或唯一键，可以使用表的任意字段

作为分区字段（一般为整数字段作为分区键，与分区类型有关）。

**eg1，**有主键时，用其他字段分区报错:

mysql> create table t_user_main(f_id int(11),f_bankid int(11),f_userName varchar(50),primary key(f_id))
        -> partition by range(f_bankid)(
        ->   partition p0 values less than(100),
        ->   partition p1 values less than(200),
        ->   partition p2 values less than(300)
        -> );
    1503 - A PRIMARY KEY must include all columns in the table's partitioning function

**eg2，**有唯一键时，用其他字段分区报错:

mysql> create table t_user_main(f_id int(11) not null, f_userId int(11) not null, f_userName varchar(50),unique key(f_id))
        -> partition by range(f_userId)(
        ->   partition p0 values less than(100),
        ->   partition p1 values less than(200),
        ->   partition p2 values less than(300)
        -> );
    1503 - A PRIMARY KEY must include all columns in the table's partitioning function

**eg3，**没有主键或唯一键时可以使用表中的任何字段分区,把上面的主键去掉:

mysql> create table t_user_main(f_id int(11),f_bankid int(11),f_userName varchar(50))
        -> partition by range(f_id)(
        ->   partition p0 values less than(100),
        ->   partition p1 values less than(200),
        ->   partition p2 values less than(300)
        -> );

通过show create table t\_user\_main或者图形界面查看表的定义语句：

CREATE TABLE `t_user_main` (
      `f_id` int(11) DEFAULT NULL,
      `f_bankid` int(11) DEFAULT NULL,
      `f_userName` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE (f_id)
    (PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB) */

我们会奇怪的发现表关于分区部分的定义被注释掉了，

这个地方关于/\*\*/的含义是表示如果使用的mysql版本支持分区，那么创建表的时候自然会按照分区创建表，

如果mysql不支持分区功能，哪就真的是注释掉了，自动的不使用分区部分的语句创建分区表，

而是注释掉分区语句，创建一个不分区的表，这是一种智能的结构。

# 二 关于RANGE、LIST、HASH、KEY四种类型的分区使用 #

## 1、RANGE分区 ##

RANGE分区是使用取值范围将数据分区，区间联系并且不能互相重复，使用 VALUES LESS THAN 进行分区定义。

**eg：**

创建一个t\_user\_main表，按照RANGE进行分区：

mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
        -> partition by range(f_id)(
        ->   partition p0 values less than(5),
        ->   partition p1 values less than(10),
        ->   partition p2 values less than(15)
        -> );

插入一条数据

insert into t_user_main values(1,'one');

这条数据根据f\_id为1，被保存到 p0 区，如果我们插入一个 f\_id 为 16 的数据

mysql> insert into t_user_main values(16,'sixteen');
    1526 - Table has no partition for value 16

提示错误信息告诉我们分区中没有 16 这个值的容身之地，插入时分区的时候 mysql 不知道给分到哪里去，

自然报错了，这个地方有个临界值15, 如果插入15，也会报错，因为超出了范围，range给定的范围不包括最右边的值，

也就是说范围(5)表示......-1,0,1,2,3,4,而范围(10)表示5,6,7,8,9，范围(15)表示10,11,12,13,14

不包括最右边的值，说以插入15也会报找不到分区范围。

但是，我们可以通过values less than maxvalue设置分区的最大值，避免局限于这几个数：

通过partition p\_max values less than (maxvalue)指定分区的最大插入值，

maxvalue表示最大整数来确定分区的上限值，修改完后再重新插入16的数据，显示插入成功，保存到p\_max区。

RANGE分区最适合的两种场景：

（1）当我们需要删除过期，某些一类的数据时，通过ALTER TABLE t\_user\_main DROP PARTITION p0直接删除掉p0区的数据，

也即是f\_id为......-1,0,1,2,3,4的数据被删除。如果是百万级别的数据，分区删除比通过delete删除效率高。

（2）当我们查询数据时，为了提高查询效率，分区可以让我们的sql减少全表扫描，直接定位分区扫描，提高查询效率。

比如select \* from t\_user\_main where f\_id > 12,直接扫描p2区扫描，查询数据。

## 2、LIST分区 ##

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，通过values in(value\_list)方式定义分区，

value\_list是整数值列，多个值可以用逗号隔开，在5.5中可以通过非整数值字段定义分区。

mysql> create table t_user_main (f_id int(11), f_userName varchar(200))
        -> partition by list(f_id)(
        ->   partition p0 values in(2,5),
        ->   partition p1 values in(1,10),
        ->   partition p2 values in(6,8),
        ->   partition p3 values in(9)
        -> );

插入数据mysql> insert into t\_user\_main values (1,'one')保存在p1区；

插入数据mysql> insert into t\_user\_main values (2,'two')保存在p0区；

插入数据mysql> insert into t\_user\_main values (100,'hundred')报错为

1526 - Table has no partition for value 100，说明没有找到对应的分区，

这个地方与RANGE分区不同，LIST分区没有values less than maxvalue这些说法，

从枚举数值集合中如果找不到分区值，就报错，没有最大值之说。

需要注意的是每个枚举列表集合中的值不能重复，否则报错：

mysql> create table t_user_main3(f_id int(11) not null, f_userName varchar(50),primary key(f_id))
        -> partition by list(f_id)(
        ->   partition p0 values in (1,2),
        ->   partition p1 values in (1,2),
        ->   partition p2 values in (2,3)
        -> );
    1495 - Multiple definition of same constant in list partitioning

## 3、HASH分区 ##

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

当一个表执行HASH分区时，MySql会对分区键应用一个散列函数，以确保数据应当存放在N个分区

中的哪个分区中。

MySql支持两种HASH分区，分别为常规HASH分区和线性HASH分区(LINEAR HASH分区)，

其中常规HASH分区使用的是取模算法，线性HASH分区使用的是一个线性的2的幂运算法则。

先创建一个常规HASH分区，使用PARTITION BY HASH(expr) PARTITIONS num字句定义分区的类型，

分区键和分区个数，其中expr是某个列或基于某个列返回整数值的表达式，num是一个非负整数，

表示分割的分区数量，默认值为1。

区数N的计算公式N = MOD(expr,num),求出的N是几，数据就保存到第几分区。

mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
        -> partition by hash(f_id) partitions 4;
    
    mysql> insert into t_user_main values(5,'five');

通过取模算法可以算出该数据应该被保存到哪个区，N=MOD(expr,num)=MOD(5,4)=1,所以该数据被保存到第一区。

这个时候如果我们执行查询 select \* from t\_user\_main where f\_id = 5会确定到第一个区查询数据。

使用常规HASH分区会有一个问题，一开始N=MOD(expr,num)，如果我们增加分区，将t\_user\_main表从4个分区

变成5个分区，取模就会发生变化，先前的数据合并的时候就会出问题。

为了解决这种常规HASH中出现的分区增减困难问题，降低管理的代价，MySql提供了线性HASH分区。

mysql> create table t_user_main(f_id int(11), f_userName varchar(200))
        -> partition by linear hash(f_id) partitions 4;
    
    mysql> insert into t_user_main values (234,'testLH')

下面开始计算保存到哪个分区，

与常规HASH分区比，指定类型时多了个linear，假设将要保存的记录分区编号为N,

num是一个非负的整数，也就是分区数量，那么得到N的算法如下：

1. 找到下一个大于或等于num的2的幂，我们把这个值称为V ，V可以通过下面的公式得到：

V = POWER(2, CEILING(LOG(2, num)))

例如，咱们创建的t\_user\_main分区数num是4。

那么LOG(2,4)就是2，

CEILING(2)就是2，

则V = POWER(2,2),

即V=4

2. 设置 N = F(column\_list) & (V - 1) = 234&(4-1) = 2

3. 当 N >= num;

设置 V = CEIL(V / 2)

设置 N = N & (V - 1)

当N < num时，N就是保存的区数，所以，刚才插入的数据被保存到2分区。  
  
总结线性HASH分区：

优点： 分区维护时，处理速度相对常规分区较快。

缺点： 相对于常规分区取模算法，线性分区计算的数据保存到各个分区不均匀。

## 4、KEY分区 ##

按照KEY进行分区类似于HASH分区，只是这个地方使用的是mysql服务提供的HASH()函数，

HASH分区只支持整数分区，5.5也支持了其他类型分区，而KEY分区支持除了BLOG和TEXT类型外的列作为分区键,

分区算法使用的是线性HASH分区时2的幂算法得到数据保存的区。

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
        -> partition by key(f_id) partitions 4;

与HASH分区不同的是，KEY分区可以不指定分区键，如果有主键时，自动使用主键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200),primary key(f_id))
        -> partition by key() partitions 4;

如果没有主键时，就选择非空唯一键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200),unique key(f_id))
        -> partition by key() partitions 4;

如果没有主键，没有非空唯一键，这个时候要想分区就必须制定分区键，否则报错：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
        -> partition by key() partitions 4;
    1488 - Field in list of fields for partition function not found in table

如果没有主键，没有非空唯一键，指定分区键时可以分区：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
        -> partition by key(f_id) partitions 4;

上面是一个递进关系，特别注意的是，当存在主键或唯一键时，必须用主键或唯一键作为分区键，

如果这个时候使用其他键作为分区键，将报错告诉你，必须用主键或唯一键作为分区键：

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200), primary key(f_id))
        -> partition by key(f_userName) partitions 4;
    1503 - A PRIMARY KEY must include all columns in the table's partitioning function

# 三 分区管理 #

分区管理通过alter table命令实现对分区的添加，删除，重定义，合并，拆分。

## 1、RANGE和LIST分区管理 ##

mysql> create table t_user_main(f_id int(11) not null, f_userName varchar(200))
        -> partition by range(f_id)(
        ->   partition p0 values less than(5),
        ->   partition p1 values less than(10),
        ->   partition p2 values less than(15)
        -> );

**删除分区：**

mysql> alter table t_user_main drop partition p2;

**删除分区后的表结构:**

CREATE TABLE `t_user_main` (
      `f_id` int(11) NOT NULL,
      `f_userName` varchar(200) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE (f_id)
    (PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */;

**添加分区:**

mysql> alter table t_user_main add partition(partition p2 values less than(100));

**添加分区后的表结构:**

**拆分区:**

mysql> alter table t_user_main reorganize partition p2 into(
        ->   partition p2 values less than(50),
        ->   partition p3 values less than(100)
        -> );

**拆完分区之后的表结构：**

**分区合并:**

mysql> alter table t_user_main reorganize partition p1,p2,p3 into(partition p1 values less than(100));

**合并分区后的表结构:**

## 2、HASH和KEY分区管理 ##

**创建一个HASH类型4分区表：**

CREATE TABLE `t_user_main` (
      `f_id` int(11) NOT NULL,
      `f_userName` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`f_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY HASH (f_id)
    PARTITIONS 4 */;

**减少2个分区：**

mysql> alter table t_user_main coalesce partition 2;

**减少后的表结构：**

**增加10个分区：**

mysql> alter table t_user_main add partition partitions 10;

**增加10个分区后变成12个分区的表结构:**

[https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql