mysql根据区域分区_细聊MySQL的分区功能

此篇主要介绍下MySQL的分区功能。我们分别从分区的概念、分区对于MySQL应用的优点、分区的类别及设置来和大家一起探讨下MySQL的分区。

什么是分区？

MySQL在未启用分区功能时，数据库的单个表内容是以单个文件的形式存放在文件系统上的。当启用分区功能后，MySQL将按用户指定的规则将单个表内容分割成几个文件存放在文件系统上。分区分为水平分区和垂直分区，水平分区是将表的数据按行分割成不同的数据文件，而垂直分区则是将表的数据按列分割成不同的数据文件。分片要遵循完备性原则、可重构性原则与不相交原则。完备性代表所有数据必须映射到某个片段上。可重构性表示所有分片数据必须可以重新构成全局数据。不相交性表示不同分片上的数据没有重复(除非你是特意做的冗余)。

由于自MySQL 5.7起已不支持垂直分区，所以此篇文章主要介绍数据库的水平分区。

分区的优势

1、由于分区可以将表数据分割成不同的文件，并分配到不同的磁盘，因此分区可以在一个表中存放更多的数据。

2、分区后可以快速的移除不需要的数据或添加新的数据。举个例子，如果按时间范围分区，分区规则为从开始至2010年设置为分区1，2011年至2013年设置为分区2，2014年至现在设置为分区3。如果我想删除2010年之前的数据，那么只用删除分区1即可，而无需扫描整个表。

3、分区可以优化查询，你可以将经常需要被查询的内容设置为一个分区，这样查询数据时就可以直接显示分区的内容。例如，你可以利用SELECT * FROM t PARTITION(p0,p1) WHERE c < 5来查询p0与p1两个分区下的内容。这样系统扫描的行会更少，加快了查询效率。由于查询条件可能在一段时间后有所变动，此时我们需要重新调整分区规则。所以，数据库是需要经常维护的。

分区类别

我们可以根据范围、列表、哈希与关键字这四种类别进行分区。范围分区是根据用户指定某列的范围值进行分区。列表分区类似于范围分区，但列表分区必须根据用户具体指定的集合进行分区。哈希分区根据用户表达式返回的正整数值进行分区。关键字分区类似与哈希分区，一般根据用户指定的关键列名进行分区。下面，我们就来详细了解下这四种分区的相关用法。

范围分区

我们以例子来说明如何设置范围分区。首先创建一个表CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT NOT NULL,

store_id INT NOT NULL

);

这是一张雇员表，fname为first name，lname为last name，hired为聘用时间，separated为离职时间，job_code为工号，store_id为此雇员属于哪个门店。

我们可以用多种方式对它进行范围分区，可以根据门店ID的范围，聘用时间的范围，工号的范围均可。下面我们根据门店ID的范围对此表进行分区，SQL声明如下：CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6),

PARTITION p1 VALUES LESS THAN (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN (21)

);

在这种分区设置下，门店ID1～5的雇员信息被分配到分区p0中，6～10的雇员信息被分配到分区p1中，以此类推。如果你的store_id确定在1～20之间，此种分区方法是没有问题的。那么，如果你的store_id不确定，当出现store_id的值为大于21的值时，此分区设置就会导致错误，因为我们没有分配store_id大于21时的行到任何一个分区。要解决这个问题，我们需要引进关键字“MAXVALUE”，按如下的SQL声明，即可解决store_id不固定的问题。CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6),

PARTITION p1 VALUES LESS THAN (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

当然，我们也可以按照时间范围来进行分区，具体就看个人的应用场景了。值得注意的是，由于MySQL的bug，在按照时间范围来进行分区时，如果你需要转化成时间戳来分区，则最好使用UNIX_TIMESTAMP函数而不是TIMESTAMP函数。

列表分区

列表分区在我看来是一种比较“笨”的分区方法，因为它得把列中的所有可能值显式写在SQL声明中。还是以employees表为例。我们按store_id，以列表的方式进行分区。假设我们将门店分为东南西北四个区域，北部的门店ID分别为3,5,6,9,17。东部的门店ID分别为1,2,10,11,19,20。西部的门店ID分别为4,12,13,14,18。南部的门店ID分别为7,8,15,16。每一个区域对应一个分区，则列表分区的SQL声明如下：CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT,

store_id INT

)

PARTITION BY LIST(store_id) (

PARTITION pNorth VALUES IN (3,5,6,9,17),

PARTITION pEast VALUES IN (1,2,10,11,19,20),

PARTITION pWest VALUES IN (4,12,13,14,18),

PARTITION pSouth VALUES IN (7,8,15,16)

);

与范围分区不同，列表分区中没用类似“MAXVALUE”这种用法，我们必须覆盖所有可能的值。如果插入一个store_id为21的记录，则系统将会报错。

哈希分区

哈希分区的设置很简单，只需保证用户指定的表达式为正整数，并且显式设置分区个数即可。分区个数也需为正整数。如下所示：CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT,

store_id INT

)

PARTITION BY HASH( YEAR(hired) )

PARTITIONS 4;

YEAR(hired)为用户设置的表达式，返回正整数。需要注意的是表达式的结果值与指定的列值最好成线性关系，否则，分区的数据将不能均匀的分布。如果要确认某个记录分布在哪个分区，可以使用N = MOD(expr, num)的公式进行确认。MOD为取模，N为记录所在的分区，expr为YEAR(hired)的值，num为4 (根据上例的值所得)。那么如果expr的值为2005，则记录所在的分区为第一分区。

线性哈希分区

线性哈希分区与普通哈希分区在确定记录所在分区的算法上有一定区别，其它并无不同之处，线性哈希分区的SQL声明如下所示：CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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

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

job_code INT,

store_id INT

)

PARTITION BY LINEAR HASH( YEAR(hired) )

PARTITIONS 4;

关键字分区

关键字分区需以表中的主键或唯一列来作为分区的关键字指定，否则将不能成功设置。关键字分区的SQL声明如下：CREATE TABLE tm1 (

s1 CHAR(32) PRIMARY KEY

)

PARTITION BY KEY(s1)

PARTITIONS 10;

可以看到，关键字的类型并不一定是需要正整数，字符型的列也可以作为关键字。

除以上介绍的分区外，MySQL还能设置列分区与子分区。那么，什么是列分区，什么是子分区呢？下面我们简单的了解下。

子分区，子分区就是分区下的分区，将每个分区继续细分，变成更小的分区。有这种需求的，可能是单表数据量非常大的场景吧。具体的设置方式如下所示：CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) )

SUBPARTITIONS 2 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

此声明将ts表按范围分成p0，p1，p2三个分区，又将p0，p1，p2每个分区用哈希分区分成了p0-1和p0-2(p1，p2以此类推)两个子分区。实际上，此声明是将表分成了6个分区。

还有更加详细的子分区法，如下所示：CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990) (

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx',

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

),

PARTITION p1 VALUES LESS THAN (2000) (

SUBPARTITION s2

DATA DIRECTORY = '/disk2/data'

INDEX DIRECTORY = '/disk2/idx',

SUBPARTITION s3

DATA DIRECTORY = '/disk3/data'

INDEX DIRECTORY = '/disk3/idx'

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4

DATA DIRECTORY = '/disk4/data'

INDEX DIRECTORY = '/disk4/idx',

SUBPARTITION s5

DATA DIRECTORY = '/disk5/data'

INDEX DIRECTORY = '/disk5/idx'

)

);

此声明首先利用范围分区将表分成了p0，p1，p2三个分区，然后利用哈希分区将p0细分为s0，s1两个分区，s0的数据文件路径为/disk0/data，索引文件路径为/disk0/idx，以此类推。最后的分区为s0，s1，s2，s3，s4，s5。

列分区

列分区就是利用多个列值进行分区，范围分区与列表分区都支持列分区。列分区不支持表达式，只支持列名，但列名可以是多个。列分区是通过比较多个列值形成的元组进行分区。下面我们范围列分区来介绍下列分区，SQL声明如下：mysql> CREATE TABLE rcx (

->     a INT,

->     b INT,

->     c CHAR(3),

->     d INT

-> )

-> PARTITION BY RANGE COLUMNS(a,d,c) (

->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),

->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),

->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),

->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

-> );

Query OK, 0 rows affected (0.15 sec)

此声明依据a，d，c三列进行范围分区。通过比较(5,10,’ggg’)，(10,20,’mmmm’)，(15,30,’sss’)来对表中的数据进行分割。由于对于元组的范围判断本人也不是太清楚，保证不了分区的完备性原则，所以这里就不详细介绍列分区了。对列分区有兴趣的同学可以自行查看手册。

关于MySQL的分区今天就先聊到这，具体使用哪种类型的分区，分多少区还要看各位的具体应用场景和应用逻辑来决定，不能一概而论。