开发篇-MySQL分区(一)

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数十个物理分区对象组成,每个分区都是一个独立的对象,可以独自处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。

MySQL分区的优点主要包括以下4个方面:

和单个磁盘或者文件系统分区相比,可以存储更多数据。

优化查询:在Where子句中包含分区条件时,可以只扫描必要的一个或多个分区来提高查询效率;同时在涉及SUM()和COUNT()这类聚合函数的查询的时候,可以容易的在每个分区上并行处理,最终结果只需要汇总所有分区得到的结果。

对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来容易的删除数据。

跨多个磁盘来分散数据查询,获得更大的查询吞吐量。

注意:在MySQL5.1版本中分区的实现仍然是pre-alpha版本,该版本中分区特性不是特别适用于生产环境,部分本章中描述的一些功能在MySQL5.1版本中没有实现和提供支持,部分在新发布的MySQL5.5中已经实现,本章基于MySQL5.1版本对分区特性进行描述,如果没有特别标注,描述的特性都适用于MySQL5.1版本,MySQL5.1版本不支持的功能或者在MySQL5.5新增加支持的特性将会特别标注,请读者注意。

6.1分区概述

分区有利于管理非常大的表,它采用了“分而治之”的逻辑,分区引入了分区键(partition key)的概念,分区键用于根据某个区间值(或者范围值)、特定值列表、或者HASH函数值执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象变成一些小对象。

可以通过使用SHOW VARIABLES命令来确定当前的MySQL是否支持分区,例如:

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

Variable_name Value

+-----------------------+-------+

have_partition_engine YES

+-----------------------+-------+

1 row in set (0.00 sec)

在如上列出的SHOW VARIABLES 命令所产生的输出中,如果没有看到变量have_partition_engine的值为YES,那么MySQL的版本就不支持分区。

注意:使用命令SHOW ENGINES的输出中,不会显示任何有关分区支持的信息,必须使用SHOW VARIABLES 来判断当前版本是否支持分区。使用命令 SHOW PLUGINS 的输出中,能够检查到当前版本是否安装了分区插件。

MySQL支持使用大部分存储引擎(比如MyISAM、INNODB、MEMORY等存储引擎)创建分区表;MySQL不支持使用MERGE或CSV存储引擎来创建分区表,更多的内容可以参考MySQL最新的官方文档。在MySQL 5.1版本中,同一个分区表的所有分区必须使用同一个存储引擎;即同一个表上,不能对一个分区使用MyISAM引擎,对另一个分区使用InnoDB;但是,可以在同一个MySQL服务器中,甚至同一个数据库中,对于不同的分区表使用不同的存储引擎。

和非分区表设置存储引擎一样,分区表设置存储引擎,只能用[STORAGE]ENGINE子句。[STORAGE]ENGINE子句必须列在CREATE TABLE语句中的其他任何分区选项之前。例如,下面的例子给出了创建一个使用InnoDB引擎有6个HASH分区的表:

mysql> CREATE TABLE emp (empid INT, salary DECIMAL(7,2), birth_date DATE)

-> ENGINE=INNODB

-> PARTITION BY HASH( MONTH(birth_date) )

-> PARTITIONS 6;

Query OK, 0 rows affected (0.11 sec)

注意: MySQL的分区适用于一个表的所有数据和索引:不能只对表数据分区而不对索引分区;反过来也是一样的,不能只对索引分区而不对表分区,同时也不能只对表的一部分数据进行分区。MySQL的分区表上创建的索引一定是本地LOCAL索引。

6.2 分区类型

本节主要讨论在MySQL5.1中可用的分区类型,包括:

RANGE 分区:基于一个给定连续区间范围,把数据分配到不同的分区。

LIST 分区:类似RANGE分区,区别在LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区。

HASH 分区:基于给定的分区个数,把数据分配到不同的分区。

KEY 分区:类似于HASH分区

MySQL5.1版本中,RANGE分区、LIST分区、HASH分区都要求分区键必须是INT类型,或者通过表达式返回INT类型,也就说MySQL5.1仅仅支持整数分区,唯一的例外就是分区类型为KEY分区的时候,可以使用其他类型的列(BLOBor TEXT列类型除外)作为分区键。

注意:在MySQL5.5或以上版本中,已经支持非整数的RANGE和LIST分区了,在后面6.2.3 Columns分区章节有详细的例子说明。

无论是哪种MySQL分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其他字段分区,例如 emp表的主键为id字段,在尝试通过 store_id 字段分区的时候,MySQL会提示返回失败:

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL,

-> PRIMARY KEY (id)

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

去掉主键约束后,创建表就会成功:

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

分区的名字基本上遵循MySQL标识符的原则。说到命名,顺便介绍一下MySQL命名中的大小写敏感:在MySQL中,数据库和表对应于数据目录中的目录和文件。所以,操作系统的大小写敏感性决定数据库和表命名的大小写敏感性。这就意味着数据库和表名在Windows中是大小写不敏感的,而在大多数的Unix或Linux系统中是大小写敏感的。但是需要注意的是,分区的名字是不区分大小写的。例如,下面的CREATE TABLE 语句将会产生错误:

mysql> CREATE TABLE t2 (val INT)

-> PARTITION BY LIST(val)(

-> PARTITION mypart VALUES IN (1,3,5),

-> PARTITION MyPart VALUES IN (2,4,6)

-> );

ERROR 1517 (HY000): Duplicate partition name mypart

这是因为MySQL认为分区名字 mypart 和 MyPart 没有区别。

6.2.1Range 分区

按照RANGE分区的表是利用取值范围将数据分成分区,区间要连续并且不能互相重叠,使用VALUES LESS THAN 操作符进行分区定义。

例如雇员表emp 中按商店ID store_id 进行RANGE分区:

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

按照这种分区方案,在商店1到9工作的雇员相对应的所有行被保存在分区P0中,商店10到19的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求;类似JAVA或者C中的“switch case”语句。

这个时候,如果增加了商店ID大于等于30的行,会出现错误,因为没有规则包含了商店ID大于等于30的行,服务器不知道该把记录保存在那里。

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

ERROR 1526 (HY000): Table has no partition for value 50

可以通过设置分区的时候VALUES LESS THAN MAXVALUE子句,该子句提供给所有大于明确指定的最高值的值,MAXVALUE 表示最大的可能的整数值。例如,增加p3分区存储所有商店ID大于等于30的行之后再执行插入语句就没有问题:

mysql> alter table emp add partition (partition p3 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

Query OK, 1 row affected (0.04 sec)

MySQL支持在VALUES LESS THAN 子句中使用表达式,比如以日期作为RANGE分区的分区列:

mysql> CREATE TABLE emp_date (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

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

-> PARTITION p0 VALUES LESS THAN (1995),

-> PARTITION p1 VALUES LESS THAN (2000),

-> PARTITION p2 VALUES LESS THAN (2005)

-> );

Query OK, 0 rows affected (0.08 sec)

注意: 在RANGE分区中,分区键如果是NULL值会被当做一个最小值来处理,在后续的6.2.7 MySQL分区处理NULL值的方式 小节中有详细的说明。

MySQL 5.1 支持整数列分区,那么对于想在日期或者字符串列上进行分区,就得使用函数进行转换。但是要是查询如果不用函数转换,那么就无法利用RANGE分区特性来提高查询性能。

MySQL 5.5 改进了RANGE分区功能,提供了RANGE COLUMNS分区支持非整数分区,创建日期分区就不需要通过函数进行转换,例如:

mysql> CREATE TABLE emp_date(

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE COLUMNS (separated) (

-> PARTITION p0 VALUES LESS THAN ('1996-01-01'),

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

-> PARTITION p2 VALUES LESS THAN ('2006-01-01')

-> );

Query OK, 0 rows affected (0.07 sec)

注意: MySQL 5.1分区日期处理上支持的函数只有两个YEAR() 和 TO_DAYS()。MySQL 5.5 分区日期处理上增加支持函数TO_SECONDS(),把日期转换成秒钟,从能够比按天分区更细化的分区。

RANGE 特别适用于以下情况:

当需要删除过期的数据时,只需要简单的ALTER TABLE emp DROP PARTITION p0 来删除p0分区中数据。对于上百万的记录的表来说,删除分区要比运行一个DELETE语句有效的多。

经常运行包含分区键的查询,MySQL可以很快的确定只有某一个或者某些分区需要扫描,因为其他分区不可能包含有符合该WHERE子句的任何记录。例如,检索商店ID大于等于25的记录数,MySQL只需要扫描p2分区即可:

mysql> explain partitions select count(1) from emp where store_id >= 25\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: emp

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

6.2.2List 分区

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区,LIST分区在很多方面类似于RANGE分区,区别在LIST分区是从属于一个枚举列表的值的集合,RANGE分区是从属于一个连续区间值的集合。

LIST分区通过使用PARTITION BY LIST(expr) 子句来实现,expr是某列值或一个基于某列值返回一个整数值的表达式,然后通过VALUES IN(value_list) 的方式来定义分区,其中value_list是一个逗号分隔的整数列表。和RANGE分区不同,LIST分区不必声明任何特定的顺序,例如:

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category INT,

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST(category) (

-> PARTITION p0 VALUES IN (3, 5),

-> PARTITION p1 VALUES IN (1, 10),

-> PARTITION p2 VALUES IN (4, 9),

-> PARTITION p3 VALUES IN (2),

-> PARTITION p4 VALUES IN (6)

-> );

Query OK, 0 rows affected (0.09 sec)

注意: MySQL 5.1 中,LIST分区只能匹配整数列表。category 只能是INT类型,所以需要额外的转换表来记录类别编号和类别的名称。

如果试图插入的列值(或者分区表达式的返回值)不包含分区值列表中时,那么INSERT操作会失败并报错。要重点注意,LIST分区不存在类似VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。

MySQL 5.5 中支持非整数分区,创建LIST分区就不需要额外的转换表:

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category VARCHAR(30),

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST COLUMNS (category) (

-> PARTITION p0 VALUES IN ( 'lodging', 'food'),

-> PARTITION p1 VALUES IN ( 'flights', 'ground transportation'),

-> PARTITION p2 VALUES IN ( 'leisure', 'customer entertainment'),

-> PARTITION p3 VALUES IN ( 'communications'),

-> PARTITION p4 VALUES IN ( 'fees')

-> );

Query OK, 0 rows affected (0.07 sec)

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20171227G0MA1C00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券