高性能MySql进化论(十二):Mysql中分区表的使用总结

当数据量非常大时（表的容量到达GB或者是TB），如果仍然采用索引的方式来优化查询，由于索引本生的消耗以及大量的索引碎片的产生，查询的过程会导致大量的随机I/O的产生，在这种场景下除非可以很好的利用覆盖索引，否则由于在查询的过程中需要根据索引回数据表查询，会导致性能受到很大的影响，这时可以考虑通过分区表的策略来提高查询的性能。

 不同的数据库管理系统对分区的实现可能有所区别，本文主要以MYSQL为基础

1        分区的类型

1.1RANGE分区

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义

假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示。

例如，你可能决定通过添加一个PARTITION BY RANGE子句把这个表分割成4个区间

[sql]  view plain  copy

 print ?

1. CREATE TABLE employees (  
2.   
3.     id INT NOT NULL,  
4.   
5.     fname VARCHAR(30),  
6.   
7.     lname VARCHAR(30),  
8.   
9.     hired DATE NOT NULL DEFAULT '1970-01-01',  
10.   
11.     separated DATE NOT NULL DEFAULT '9999-12-31',  
12.   
13.     job_code INT,  
14.   
15.     store_id INT  
16.   
17. )  
18.   
19. PARTITION BY RANGE (YEAR(separated)) (  
20.   
21.     PARTITION p0 VALUES LESS THAN (1991),  
22.   
23.     PARTITION p1 VALUES LESS THAN (1996),  
24.   
25.     PARTITION p2 VALUES LESS THAN (2001),  
26.   
27.     PARTITION p3 VALUES LESS THAN MAXVALUE  
28.   
29. )；  

插入一些测试数据后发现P1的数据文件明显增大

[sql]  view plain  copy

 print ?

1. mysql> DELIMITER $$  
2.   
3. mysql> DROP PROCEDURE IF EXISTS SampleProc$$  
4.   
5. Query OK, 0 rows affected (0.00 sec)  
6.   
7. mysql> CREATE PROCEDURE SampleProc()  
8.   
9.     -> BEGIN  
10.   
11.     ->  DECLARE x INT;  
12.   
13.     ->  SET x = 1000;  
14.   
15.     ->  WHILE x<= 2000 DO  
16.   
17.     ->          insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1995-01-01',10,20);  
18.   
19.     ->          SET  x = x + 1;  
20.   
21.     ->  END WHILE;  
22.   
23.     -> END$$  
24.   
25. Query OK, 0 rows affected (0.00 sec)  
26.   
27. mysql> call SampleProc() $$  
28.   
29. Query OK, 1 row affected (22.55 sec)  
30.   
31. mysql> delimiter ;  

RANGE分区在如下场合特别有用：

·             当需要删除“旧的”数据时。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用 “ALTER TABLEemployees DROP PARTITION p0；”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。(更多信息请参见13.1.2节，“ALTER TABLE语法” 和18.3节，“分区管理”）。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”这样的一个DELETE查询要有效得多。

·             想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。

·             经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BYstore_id；”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

·             分区表达式可以是MySQL 中有效的任何函数或其他表达式，只要它们返回一个既非常数、也非随机数的整数

1.2LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

地区	商店ID 号
北区	3, 5, 6, 9, 17
东区	1, 2, 10, 11, 19, 20
西区	4, 12, 13, 14, 18
中心区	7, 8, 15, 16

要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATETABLE”语句

[sql]  view plain  copy

 print ?

1. CREATE TABLE employees (  
2.   
3.     id INT NOT NULL,  
4.   
5.     fname VARCHAR(30),  
6.   
7.     lname VARCHAR(30),  
8.   
9.     hired DATE NOT NULL DEFAULT '1970-01-01',  
10.   
11.     separated DATE NOT NULL DEFAULT '9999-12-31',  
12.   
13.     job_code INT,  
14.   
15.     store_id INT  
16.   
17. )  
18.   
19. PARTITION BY LIST(store_id)  
20.   
21.     PARTITION pNorth VALUES IN (3,5,6,9,17),  
22.   
23.     PARTITION pEast VALUES IN (1,2,10,11,19,20),  
24.   
25.     PARTITION pWest VALUES IN (4,12,13,14,18),  
26.   
27.     PARTITION pCentral VALUES IN (7,8,15,16)  
28.   
29. )；  

相关的操作和range 分区类似，但有以下问题需要注意

 

·        在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。

·        如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

·        LIST分区没有类似如“VALUESLESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到

1.3     HASH分区

 

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

 

例如，下面的语句创建了一个使用基于“store_id”列进行哈希处理的表，该表被分成了4个分区：

[sql]  view plain  copy

 print ?

1. CREATE TABLE employees (  
2.   
3.     id INT NOT NULL,  
4.   
5.     fname VARCHAR(30),  
6.   
7.     lname VARCHAR(30),  
8.   
9.     hired DATE NOT NULL DEFAULT '1970-01-01',  
10.   
11.     separated DATE NOT NULL DEFAULT '9999-12-31',  
12.   
13.     job_code INT,  
14.   
15.     store_id INT  
16.   
17. )  
18.   
19. PARTITION BY HASH(store_id)  
20.   
21. PARTITIONS 4；  

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1

 

1.4     KEY分区

KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATETABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

通过线性KEY分割一个表也是可能的。下面是一个简单的例子：

[sql]  view plain  copy

 print ?

1. CREATE TABLE tk (  
2.   
3.     col1 INT NOT NULL,  
4.   
5.     col2 CHAR(5),  
6.   
7.     col3 DATE  
8.   
9. )   
10.   
11. PARTITION BY LINEAR KEY (col1)  
12.   
13. PARTITIONS 3;  

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法

1.5     更多的知识

上面列出的仅仅是常用的分区策略的用法，当创建了分区后往往还要对分区进行维护，具体请参见：

http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-hash

 

无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。

2        使用分区表的限制

·             一个数据表最多只能有1024个分区

·             在MYSQL5.1 版本中分区表达式的结果必须是整数，在MYSQL5.5分区表达式可以使用列

·             如果分区字段中有主键或者是唯一索引列，则所有的主键或者是唯一索引列必须全部包含进来

·             分区表无法使用外键

·             对于同一个表的各个分区表必须使用相同的存储引擎

·             分区函数有限制，只可以是MySQL 中有效的任何函数或其他表达式，且它们返回一个既非常数、也非随机数的整数

·             某些存储引擎不支持分区

3        使用分区表的“陷阱”

在数据量非常大的时候使用分区表可以使性能有较好的改善，但是前提是必须能有效的规避下面列出的一些陷阱

·        NULL值

MySQL 中的分区在禁止空值（NULL）上没有进行处理，无论它是一个列值还是一个用户定义表达式的值。一般而言，对于NULL,或者是当表达式接收非法值时（e.g. YEAR(‘asdf-12-12’)）返回的结果都是NULL,在这种情况下MySQL 把NULL视为0,如果大量的记录存在这种情况，最终会导致大量的记录都集中在一个分区中，也也就违背了分区的初衷。

如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。

·        分区列和索引列不匹配

如果定义的索引列和分区列不匹配，则会导致查询无法进行分区过滤。例如在列a上定义分区，在列b上定义索引，因为每个分区都有独立的索引，所以扫描索引时需要扫描每个分区。

应该避免建立和分区列不匹配的索引，除非查询中包含了可以过滤分区的条件。

 

·        选择分区的成本很高

对于Range 和list类型的分区，每次进行操作时都需要遍历所有的分区条件，以判断相关的记录是属于哪个分区，如果分区的数量很多，会在选择分区上浪费较多的资源

 

为了避免这种情况 可以限制分区的数目（<100）,或是选择hash分区

 

·        锁住底层表的成本很高

在查询访问分区表的时候，MYSQL会打开并锁住所有的底层表，该操作时再分区过滤之前发生而且和分区类型无关，会影响所有的分区查询。

 

可以通过批量更新的方式来降低该操作的次数，同时也需要限制分区的数目

 

·        维护成本高

增加/删除分区很快捷，但是重组或者是alter分区的过程类似于alter table,会进行大量的数据复制操作，效率很低。

 

4        优化查询语句

对于分区表的访问，最重要的一点是要在where条件中包含分区列，即使看起来是多余的，只有这样才能过滤不需要的分区，否则会访问所以的分区表。

看一个简单的例子。

Employees表使用store_id作为范围分区的条件，如果不使用store_id作为where条件，会查询所有的分区

Store_id作为where条件时，只查询对应的分区

 

关于where条件中的表达式有几点需要注意

·        单纯的使用分区列

Where条件中分区列必须是未经函数处理的，如果where条件写成where YEAR(store_id), 则分区过滤会失效，且查询时会检查所有的分区。这一点和索引类似

 

·        关联查询

如果分区表是关联操作的第二张表，且关联条件是分区建，则MYSQL只会在对应的分区里进行匹配