MySQL分区表技术解析_MySQL

bitsCN.com

MySQL分区概述：
允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。[z1] 。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。
由于MySQL无全局索引的概念，只有本地分区索引，基于此种原因，一个表中如果有2个或2个以上的唯一索引，此表无法分区。分区函数应用的数据库的列必须是MySQL的主键，否则不能分区。[z2]
对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。

MySQL分区的建立
MySQL可以建立四种分区类型的分区：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。详情参见18.2.1节，“RANGE分区”。
· LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见18.2.2节，“LIST分区”。
· HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。详情参见18.2.3节，“HASH分区”。
· KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照：18.2.4. KEY分区。
子分区：子分区是分区表中每个分区的再次分割。书写格式参照：18.2.5. 子分区
（1）关于子分区应注意的地方： 每个分区必须有相同数量的子分区。
· （2）如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。
在建立分区的时候可以指定分区的数据存储位置和索引位置，这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度，因为采用多磁盘后，每个磁盘的I/O操作会降低。而且采用指定分区存储位置能够增大存储量。

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

常用的MySQL的分区管理：
RANGE 和LIST分区管理
分区对于程序来说是透明的，而且只有删除能在分区层面上操作，其他如查询、修改、增加都不能指定分区。

ALTER TABLE …DROPPARTITION ….(删除分区)
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分区
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
PARTITION p0 VALUES LESS THAN (…)
)；[z4] 合并拆分分区。

HASH和KEY分区管理
添加分区和RANGE、LIST分区方式相同，对于修改分区，不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTERTABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。

如果要查看分区的信息，可以通过sql语句来查询
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’

分区表效率比较


MySQL分区表实验
分区采用红色，不分区采用蓝色

测试环境：CentOS 虚拟机，1G内存，20G硬盘
实验数据库：test 不分区（内有1张表RPT_MALEVENTS）、test2（与test一样）
背景数据：
mysql> SELECT COUNT(*)FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (10.84 sec)

mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (14.63sec)

数据分布：2011/8/4~2011/8/17

分区表结构：
CREATETABLE `RPT_MALEVENTS` (
`RECORD_DATE` date NOT NULL,
`RECORD_HOUR` tinyint(2) NOT NULL,
`RECORD_MINUTE` tinyint(2) NOT NULL,
`RECORD_DATETIME` datetime NOT NULL,
`MC_IP` int(10) unsigned NOT NULL,
`PC_IP` int(10) unsigned NOT NULL,
`NETOBJECT_GROUP_ID` smallint(5) DEFAULTNULL,
`ALERT_TYPE` tinyint(3) NOT NULL,
`SUB_TYPE` smallint(5) NOT NULL,
`SHOW_TYPE` smallint(5) NOT NULL,
`ALERT_ID` tinyint(3) NOT NULL,
`EVENT_COUNT` int(10) unsigned DEFAULT NULL,
PRIMARY KEY(`RECORD_DATE`,`RECORD_HOUR`,`RECORD_MINUTE`,`MC_IP`,`PC_IP`,`ALERT_TYPE`,`SUB_TYPE`,`ALERT_ID`),
KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2] ENGINE = InnoDB)



分区表的物理存储如下，当前用的是innodB的存储引擎，采用分表结构
分析如下
(条件查询查询全部数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)

mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)

(查询部分数据，不使用分区函数使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (2.83sec)

mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (5.60sec)

(使用其他条件查询部分数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (8.49sec)

SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (12.88sec)


(小范围查询,在一个分区内查询)

mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (1.85sec)


mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (3.10sec)


分析SQL语句的执行过程
rows表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。


mysql>EXPLAIN PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 355911[z4]
Extra: Using where
1 row in set (0.00sec)

mysql> EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 1002288[z5]
Extra: Using where
1 row in set (0.00sec)

与分区函数使用列无关的查询条件

mysql>EXPLAIN PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17084274[z7]
Extra: Using where; Using index
1 row in set (0.00sec)

mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17082459
Extra: Using where; Using index
1 row in set (0.00sec)

采用分区函数使用的列
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3767081[z9]
Extra: Using where; Using index
1 row in set (0.08sec)

mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: NULL
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8541229[z10]
Extra: Using where; Using index
1 row in set (0.00sec)


删除数据，如果删除1整天的数据，由于我们采用按天分区，

mysql> ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0


删除后包含索引的和数据的RPT_MALEVENTS#P#p20110809.ibd被删除了

如果采用传统的不分区的方式删除。
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 3929328rows affected (1 min 29.68 sec)

由此可见，删除整个分区内的数据还是很快的，

如果分区表采用传统的方式删除：

mysql> DELETEFROM RPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (19.72 sec)

mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (18.75 sec)


采用传统的方式删除一天的数据，用的时间都差不多。


只删除数据后，数据分区配p20110810还在，而且大小不变。可以用ALTER TABLE t1 OPTIMIZE PARTITION来进行回收，但是MySQL5.1.22还没有实现。


跨分区删除。
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (55.20 sec)

DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (50.26 sec)

分区表删除比不分区的略慢
[z1]分区函数
[z2]分区信息，从2011-08-09开始
[z3]没有用分区函数使用的列会扫描所有分区
[z4]数据量为681311，分区后扫描行数为355911，虽然查询条件没有分区函数的列，但是mysql的查询优化器会将其对应于时间分区，这样可以减少扫描行数
[z5]数据量为681311，分区后扫描行数为1002288
[z6]查找所有分区
[z7]无关分区函数的字段，会遍历几乎所有行。
[z8]扫描部分分区
[z9]扫描行数随之减少
[z10]估计扫描的行数
[z11]这个分区的数据是所有2011-8-10之前的所有数据，共3929328。

总结：
分区表是在MySQL5.1中新增的的功能，截止到MySQL5.1.22-rc,分区技术并不很成熟，很多分区的维护和管理功能未实现。如，分区内数据存储空间的回收、分区的修复、分区的优化等，MySQL的分区可以用在可以按分区删除的表中，且对数据库的修改操作不大，且频繁按照分区字段进行查询的表中（如恶意代码中的统计表按天分区，经常按照时间进行查询、分组等，且可以按天删除分区）。此外，由于MySQL无全局索引只有分区索引，当一张有2个唯一索引[z5] 的时候，不能将此表分区，分区列中必须包含主键。否则MySQL会报错。
总之，MySQL对于分区的限制很多，且个人认为hash和key的分区实际意义不是太大。

分区引入了一种新的优化查询的方式（当然，也有相应的缺点）。优化器可以使用分区函数修整分区，或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下，修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键，即使它看上去像是多余的。通过分区键，优化器就可以去掉不用的分区，否则的话，执行引擎就会像合并表那样访问表的所有分区，这在大表上会非常慢。分区数据比非分区数据更好维护，并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置，这样服务器可以更有效地使用多个硬盘驱动器。
[z1]分区函数的返回值必须是整数，新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值。
[z2]对于有主键的表错误提示：#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION，没有主键的则无此约束
[z3]注意：对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。即不能添加比这个分区的范围小的分区。

[z4] 对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。不能使用REORGANIZEPARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。
[z5]注意主键和唯一索引的区别


作者“深巷明朝卖杏花”

bitsCN.com