mysql集群分区

ゝ一世哀愁。 2022-12-22 04:56 105阅读 0赞

1.  **MySQL分区**

### **本章****学习目标** ###

1.  理解分区的概念
2.  了解分区的类型
3.  了解分区管理

MySQL从5.1版本开始支持分区的功能，分区是一种物理数据库设计技术，其主要目的是在特定的SQL操作中，通过减少数据读写的总量来缩减SQL语句的响应时间，同时对于应用来说分区完全是透明的，本章将对MySQL分区详细讲解。

1.  **分区概述**

1.  分区的概念

MySQL数据库中的数据是以文件的形式存在磁盘上，默认放在/mysql/data（可以通过my.cnf中的datadir来查看）目录下面，一张表主要对应着三个文件，一个是.frm文件，用于存放表结构，一个是.myd文件，用于存放表数据，还有一个是.myi文件，用于存放表索引。

如果一张表的数据量过大，那么.myd和.myi文件会很大，查询数据就会变的很慢，这时可以利用MySQL的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样在查询一条记录时，就不需要全表查找了，只需要知道这条记录在哪一块，然后在具体数据块中查询即可。如果表中数据过大，可能一个磁盘存放不下，这时可以把数据分配到不同的磁盘中去。

分区有两种方式，分别是横向分区和纵向分区，接下来举例说明横向分区和纵向分区的含义，具体如下所示。

1.  横向分区：例如一张表有100万条数据，可以分成十份，第一个10万条数据放到第一个分区，第二个10万条数据放到第二个分区，依此类推。也就是把表分成了十份，与水平分表类似。取出一条数据时，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。
2.  纵向分区：例如在设计用户表的时候，起初没有考虑周全，把个人的所有信息都放到了一张表中，这样表中就会有比较大 的字段，如个人简介，而这些简介可能不需要经常用到，可以需要用到时再去查询，可以利用纵向分区将大字段对应的数据进行分块存放，从而提高磁盘IO，与垂直分表类似。

从MySQL横向分区和纵向分区的原理来看，这与MySQL水平分表和垂直分表类似，但它们是有区别的，分表注重的是存取数据时，如何提高MySQL的并发能力，而分区注重的是如何突破磁盘的IO能力，从而达到提高MySQL性能的目的，分表会把一张数据表真正地拆分为多个表，而分区是把表的数据文件和索引文件进行分割，达到分而治之的效果。

1.  分区的优点

MySQL分区的优点非常多，这里只强调重要的两点，具体如下所示。

1.  性能的提升：在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，就能直接去扫描具体分区的数据，而不用浪费很多时间扫描不相关的数据。
2.  对数据管理的简化：MySQL分区技术可以让DBA对数据的管理能力提升，通过分区，DBA可以简化特定数据操作的执行方式。另外，分区是由MySQL直接管理的，DBA不需要手动去划分和维护。

1.  **分区类型详解**

在学习分区类型前，首先要查看数据库是否支持分区，SQL语句如下所示。

mysql> SHOW VARIABLES LIKE '%part%';

\+-------------------+-------+

| Variable\_name     | Value |

\+-------------------+-------+

| have\_partitioning | YES   |

\+-------------------+-------+

1 row in set (0.04 sec)

从以上执行结果可看出，have\_partitioning的值为YES，说明当前MySQL数据库支持分区，并且默认是开启的状态。

MySQL提供的分区属于横向分区，通过运用不同算法和规则，将数据分配到不同的区块，MySQL分区类型主要有RANGE分区、LIST分区、HASH分区、KEY分区和子分区，接下来将详细讲解这些类型的分区。

1.  RANGE分区

按照RANGE分区的表是利用取值范围将数据分区，区间要连续并且不能互相重叠，MySQL中使用VALUES LESS THAN操作符进行分区定义，接下来通过具体案例演示RANGE分区的使用。

1.  创建员工表emp，按照员工工资进行RANGE分区，范围为1000元以下、1000~2000元和2000元以上，表结构如表12.1所示。

1.  emp表

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> 字段 </td> 
 <td style="vertical-align:top;width:139.7pt;"> 字段类型 </td> 
 <td style="vertical-align:top;width:145.95pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> id </td> 
 <td style="vertical-align:top;width:139.7pt;"> int </td> 
 <td style="vertical-align:top;width:145.95pt;"> 员工编号 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> name </td> 
 <td style="vertical-align:top;width:139.7pt;"> varchar(30) </td> 
 <td style="vertical-align:top;width:145.95pt;"> 员工姓名 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> deptno </td> 
 <td style="vertical-align:top;width:139.7pt;"> int </td> 
 <td style="vertical-align:top;width:145.95pt;"> 部门编号 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> birthdate </td> 
 <td style="vertical-align:top;width:139.7pt;"> date </td> 
 <td style="vertical-align:top;width:145.95pt;"> 员工生日 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:140.45pt;"> salary </td> 
 <td style="vertical-align:top;width:139.7pt;"> int </td> 
 <td style="vertical-align:top;width:145.95pt;"> 员工工资 </td> 
 </tr> 
 </tbody> 
</table>

创建emp表并分区，SQL语句如下所示。

mysql> CREATE TABLE emp(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno INT,

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY RANGE(salary)(

->     PARTITION p1 VALUES LESS THAN(1000),

->     PARTITION p2 VALUES LESS THAN(2000),

->     PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.18 sec)

以上执行结果证明表emp创建完成，使用PARTITION BY RANGE按照员工工资进行了RANGE分区，使用PARTITION将表中数据分为三个分区p1、p2和p3，使用VALUES LESS THAN操作符进行了分区范围的规定，分为1000元以下、1000~2000元和2000元以上，其中maxvalue表示2000元以上的范围。

1.  创建员工表emp2，按照员工生日进行RANGE分区，范围为1980年以前、1980~1990年和1990年以后，SQL语句如下所示。

mysql> CREATE TABLE emp2(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno INT,

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY RANGE(YEAR(birthdate))(

->     PARTITION p1 VALUES LESS THAN(1980),

->     PARTITION p2 VALUES LESS THAN(1990),

->     PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.2 5 sec)

以上执行结果证明表emp2创建完成，使用PARTION BY RANGE按照员工生日进行了RANGE分区，这里要注意的是，表达式YEAR(birthdate)必须有返回值，使用PARTITION将表中数据分为三个分区p1、p2和p3，使用VALUES LESS THAN操作符进行了分区范围的规定，分为1980年以前、1980~1990年和1990年以后，其中maxvalue表示1990年以后的范围。

MySQL5.1支持整数列分区，若想在日期或者字符串类型的列上进行分区，就要使用函数进行转换，否则无法利用RANGE分区来提高性能。MySQL5.5改进了RANGE分区功能，提供了RANGE COLUMNS分区支持非整数分区，这样创建日期分区就不需要通过函数进行转换，接下来通过具体案例演示RANGE COLUMNS分区的使用。

1.  创建员工表emp3，按照员工生日进行RANGE COLUMNS分区，范围为1980年1月1日以前、1980年1月1日~1990年1月1日和1990年1月1日以后，SQL语句如下所示。

mysql> CREATE TABLE emp3(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno INT,

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY RANGE COLUMNS(birthdate)(

->     PARTITION p1 VALUES LESS THAN('1980-01-01'),

->     PARTITION p2 VALUES LESS THAN('1990-01-01'),

->     PARTITION p3 VALUES LESS THAN maxvalue

-> );

Query OK, 0 rows affected (0.17 sec)

从以上执行结果可看出，创建表emp3并分区成功，SQL中使用PARTITION BY RANGE COLUMNS语句，按照birthdate进行分区，这里birthdate为日期类型，没有通过函数进行转换，原因是RANGE COLUMNS分区支持非整数分区。

当需要删除过期数据时，只需要删除具体的一个分区即可，这对于大数据量的表来说，删除分区比逐条删除数据的效率要高的多，删除分区的语法格式如下所示。

ALTER TABLE 表名 DROP PARTITION 分区名；

接下来通过具体案例演示删除分区的实现。

1.  删除表emp3中的分区p1，SQL语句如下所示。

mysql> ALTER TABLE emp3

-> DROP PARTITION p1;

Query OK, 0 rows affected (0.53 sec)

Records: 0  Duplicates: 0  Warnings: 0

从以上执行结果可看出，SQL语句执行成功，分区p1被删除，但0行数据受影响，因为此时表emp3中没有数据。

1.  LIST分区

LIST分区与RANGE分区类似，区别在于LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。MySQL中使用PARTITION BY LIST(expr)子句实现LIST分区，expr是某列值或一个基于某列值返回一个整数值的表达式，然后通过VALUES IN(value\_list)的方式来定义分区，其中value\_list是一个逗号分隔的整数列表，与RANGE分区不同的是，LIST分区不必声明任何特定的顺序。接下来通过具体案例演示LIST分区的使用。

1.  创建员工表emp4，按照部门编号进行LIST分区，范围为10号部门、20号部门和30号部门，SQL语句如下所示。

mysql> CREATE TABLE emp4(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno INT,

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY LIST(deptno)(

->     PARTITION p1 VALUES IN(10),

->     PARTITION p2 VALUES IN(20),

->     PARTITION p3 VALUES IN(30)

-> );

Query OK, 0 rows affected (0.18 sec)

以上执行结果证明表emp4创建完成，使用PARTITION BY LIST按照部门编号进行了LIST分区，使用PARTITION将表中数据分为三个分区p1、p2和p3，使用VALUES IN操作符指定了分区范围为10号部门、20号部门和30号部门。

MySQL5.1以前，LIST分区只能匹配整数列表，deptno只能是INT类型，若想在日期或者字符串类型的列上进行分区，就要使用函数进行转换，否则无法使用LIST分区。MySQL5.5改进了LIST分区功能，提供了LIST COLUMNS分区支持非整数分区，这样创建日期分区就不需要通过函数进行转换，接下来通过具体案例演示LIST COLUMNS分区的使用。

1.  创建员工表emp5，按照部门编号进行LIST分区，范围为5号部门、15号部门和25号部门，其中部门编号deptno为VARCHAR(10)类型，SQL语句如下所示。

mysql> CREATE TABLE emp5(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno VARCHAR(10),

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY LIST COLUMNS(deptno)(

->     PARTITION p1 VALUES IN('5'),

->     PARTITION p2 VALUES IN('15'),

->     PARTITION p3 VALUES IN('25')

-> );

Query OK, 0 rows affected (0.14 sec)

从以上执行结果可看出，表emp5创建成功并进行了分区，根据deptno对表中数据进行了分区，分区范围为5号部门、15号部门和25号部门，其中部门编号deptno为VARCHAR(10)类型，这里使用了LIST COLUMNS进行分区，无需进行类型转换，直接使用即可，注意VALUES IN后的枚举值也必须是字符串类型，否则会报出错误。

1.  HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布，在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL会自动完成这些工作，只需基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量即可。

MySQL支持两种HASH分区，常规HASH分区和线性HASH分区，常规HASH分区使用的是取模算法，线性HASH分区使用的是一个线性的2的幂的运算法则。MySQL中使用PARTITION BY HASH(expr) PARTITIONS num子句对分区类型、分区键和分区个数进行定义，其中expr是某列值或一个基于某列值返回一个整数值的表达式，num是一个非负的整数，表示分割成分区的数量，默认为1。接下来通过具体案例演示常规HASH分区的用法。

1.  创建员工表emp6，按照员工生日进行常规HASH分区，分为四个分区，SQL语句如下所示。

mysql> CREATE TABLE emp6(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno VARCHAR(10),

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY HASH(YEAR(birthdate))

-> PARTITIONS 4;

Query OK, 0 rows affected (0.21 sec)

以上执行结果可看出，员工表emp6创建完成，并进行了分区，使用PARTITION BY  HASH进行了HASH分区，根据员工生日分为了四个分区。其实对于一个表达式expr，即SQL中的YEAR(birthdate)，是可以计算出它会被保存在哪个分区中，假设将要保存记录的分区编号为N，那么N=MOD(expr,num)，例如本例中emp表有4个分区，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO emp6

-> VALUES(1,'zs','10','2017-12-01',1000);

Query OK, 1 row affected (0.10 sec)

以上执行结果证明数据插入成功，这条语句中birthdate为2017-12-01，那么YEAR(birthdate)为2017，可以计算出保存该条记录的分区，具体如下所示。

MOD(2017,4)=1

以上计算是取模运算，运算结果为1，所以该条数据会保存到第一个分区中，常规HASH将数据尽可能平均分布到每个分区，让每个分区管理的数据减少，提高了查询效率，但这里还存在着一个隐藏的问题，当需要增加分区或者合并分区时，假设有5个常规HASH分区，新增一个常规HASH分区，那么原来的取模算法是MOD(expr,5)，根据余数0~4分布在5个分区中，增加分区后，取模算法变为了MOD(expr,6)，分区数量增加了，所以之前所有分区中的数据要重新计算分区，这样的代价太大了，不适合需求多变的实际应用，为了降低分区管理的代价，MySQL提供了线性HASH分区，分区函数是一个线性的2的幂的运算。

线性HASH分区和常规HASH分区的语法区别在PARTITION BY子句，线性HASH需要加上LINEAR关键字，接下来通过具体案例演示线性HASH的使用。

1.  创建员工表emp7，按照员工工资进行线性HASH分区，分为三个分区，SQL语句如下所示。

mysql> CREATE TABLE emp7(

->     id INT NOT NULL,

->     name VARCHAR(30),

->     deptno VARCHAR(10),

->     birthdate DATE,

->     salary INT

-> )

-> PARTITION BY LINEAR HASH(salary)

-> PARTITIONS 3;

Query OK, 0 rows affected (0.26 sec)