• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

mysql集群分区

mysql技术 china_zyb 30次浏览 已收录 0个评论
  1. MySQL分区

本章学习目标

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

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

  1. 分区概述
  2. 分区的概念

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不需要手动去划分和维护。
  3. 分区类型详解

    在学习分区类型前,首先要查看数据库是否支持分区,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所示。
  2. emp表

字段

字段类型

说明

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

  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)

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

  1. KEY分区

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

 

 

 

 

  1. 子分区

 

 

 

  1. MySQL分区处理NULL值的方式

 

 

 

  1. 分区管理

 

  1. RANGE分区和LIST分区管理

 

 

 

 

 

  1. HASH分区和KEY分区管理

 

 

 

 

 

  1. 本章小结

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

  1. 习题

1.思考题

(1) 请简述

(2) 请简述

(3) 请简述

(4) 请简述

(5) 请简述


VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明mysql集群分区
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址