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.1RANGE分区
按照RANGE分区的表是利用取值范围将数据分区,区间要连续并且不能互相重叠,MySQL中使用VALUES LESS THAN操作符进行分区定义,接下来通过具体案例演示RANGE分区的使用。
例12-1创建员工表emp,按照员工工资进行RANGE分区,范围为1000元以下、1000~2000元和2000元以上,表结构如表12.1所示。
表12.1emp表
字段 字段类型 说明
id int 员工编号
name varchar(30) 员工姓名
deptno int 部门编号
birthdate date 员工生日
salary int 员工工资
创建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.2LIST分区
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.3HASH分区
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.4KEY分区
KEY分区主要用来确保数据在预先确定数目的分区中平均分布,在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中,而在HASH分区中,MySQL会自动完成这些工作,只需基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量即可。

12.2.5子分区

12.2.6MySQL分区处理NULL值的方式

12.3分区管理

12.3.1RANGE分区和LIST分区管理

12.3.2HASH分区和KEY分区管理

12.4本章小结
本章首先介绍了数据的备份与还原,这是非常实用且必须的技能,读者需要掌握,然后介绍了权限管理,权限管理一般由数据库管理员操作,最后讲解了如何实现MySQL集群,以及集群的应用,实现了MySQL主从复制以及双主互备,对于初学者来说,了解即可。
12.5习题
1.思考题
(1) 请简述
(2) 请简述
(3) 请简述
(4) 请简述
(5) 请简述

mysql的分区

上一篇:Oracle静默安装模板


下一篇:yum方式安装ARM架构PostgreSQL10数据库