MySQL 分区分库分表必看教程之一（mysql分区分表原理）

第12章 MySQL分区

本章学习目标

· 理解分区的概念

· 了解分区的类型

· 了解分区管理

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

12.1 分区概述

12.1.1 分区的概念

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

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

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

· 横向分区：例如一张表有100万条数据，可以分成十份，第一个10万条数据放到第一个分区，第二个10万条数据放到第二个分区，依此类推。也就是把表分成了十份，与水平分表类似。取出一条数据时，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

· 纵向分区：例如在设计用户表的时候，起初没有考虑周全，把个人的所有信息都放到了一张表中，这样表中就会有比较大 的字段，如个人简介，而这些简介可能不需要经常用到，可以需要用到时再去查询，可以利用纵向分区将大字段对应的数据进行分块存放，从而提高磁盘IO，与垂直分表类似。

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

12.1.2 分区的优点

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

· 性能的提升：在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，就能直接去扫描具体分区的数据，而不用浪费很多时间扫描不相关的数据。

· 对数据管理的简化：MySQL分区技术可以让DBA对数据的管理能力提升，通过分区，DBA可以简化特定数据操作的执行方式。另外，分区是由MySQL直接管理的，DBA不需要手动去划分和维护。

12.2 分区类型详解

在学习分区类型前，首先要查看数据库是否支持分区，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分区和子分区，接下来将详细讲解这些类型的分区。

12.2.1 RANGE分区

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

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

表12.1 emp表

创建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元以上的范围。

例12-2 创建员工表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分区的使用。

例12-3 创建员工表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 分区名；

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

例12-4 删除表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中没有数据。

12.2.2 LIST分区

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

例12-5 创建员工表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分区的使用。

例12-6 创建员工表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后的枚举值也必须是字符串类型，否则会报出错误。

12.2.3 HASH分区

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

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

例12-7 创建员工表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的使用。

例12-8 创建员工表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)

从以上执行结果可看出，表emp7创建完成并创建了三个分区，使用PARTITION BY LINEAR HASH创建了线性HASH分区，比前面的常规HASH分区更适合需求多变的应用场景。

12.2.4 KEY分区

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

12.2.5 子分区

12.2.6 MySQL分区处理NULL值的方式

12.3 分区管理

12.3.1 RANGE分区和LIST分区管理

12.3.2 HASH分区和KEY分区管理

12.4 本章小结

本章首先介绍了数据的备份与还原，这是非常实用且必须的技能，读者需要掌握，然后介绍了权限管理，权限管理一般由数据库管理员操作，最后讲解了如何实现MySQL集群，以及集群的应用，实现了MySQL主从复制以及双主互备，对于初学者来说，了解即可。

12.5 习题

1.思考题

(1) 请简述

(2) 请简述

(3) 请简述

(4) 请简述

(5) 请简述