共计 9088 个字符，预计需要花费 23 分钟才能阅读完成。
MySQL 中怎么实现分区表，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
MySQL 分区的建立
MySQL 可以建立四种分区类型的分区：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。详情参见 18.2.1 节，“RANGE 分区”。
middot; LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见 18.2.2 节，“LIST 分区”。
middot; HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。详情参见 18.2.3 节，“HASH 分区”。
middot; KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照：18.2.4. KEY 分区。
子分区：子分区是分区表中每个分区的再次分割。书写格式参照：18.2.5. 子分区
（1）关于子分区应注意的地方：每个分区必须有相同数量的子分区。
middot; （2）如果在一个分区表上的任何分区上使用 SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。
在建立分区的时候可以指定分区的数据存储位置和索引位置，这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度，因为采用多磁盘后，每个磁盘的 I / O 操作会降低。而且采用指定分区存储位置能够增大存储量。
无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从 0 开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用 4 个分区，那么这些分区就编号为 0, 1, 2, 和 3。对于 RANGE 和 LIST 分区类型，确认每个分区编号都定义了一个分区，很有必要。对 HASH 分区，使用的用户函数必须返回一个大于 0 的整数值。对于 KEY 分区，这个问题通过 MySQL 服务器内部使用的 哈希函数自动进行处理。注意：分区的名字是不区分大小写的，且对于 RANGE 分区和 LIST 分区，分区的名称是不能重复的。这几种可根据不同的需求来选择，比较常用的是 RANGE 分区。
常用的 MySQL 的分区管理：
RANGE 和 LIST 分区管理
分区对于程序来说是透明的，而且只有删除能在分区层面上操作，其他如查询、修改、增加都不能指定分区。
ALTER TABLE hellip;DROPPARTITION hellip;.(删除分区)
ALTER TABLE hellip; ADD PARTITION (PARTITION p3 VALUESLESS THAN ( hellip;));[z3] 增加分区
ALTER TABLE … REORGANIZE PARTITION hellip;, hellip; INTO (
PARTITION p0 VALUES LESS THAN (hellip;)
)；[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 rsquo;
分区表效率比较
MySQL 分区表实验
分区采用红色，不分区采用蓝色
测试环境：CentOS，1G 内存，20G 硬盘
实验：test 不分区（内有 1 张表 RPT_MALEVENTS）、test2（与 test 一样）
背景数据：
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 2011-08-19
+———-+
| COUNT(*) |
+———-+
| 17082107 |
+———-+
1 row in set (21.62sec)
mysql SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE 2011-08-01 AND RECORD_DATE 2011-08-19
+———-+
| COUNT(*) |
+———-+
| 17082107 |
+———-+
1 row in set (29.20sec)
(查询部分数据，不使用分区函数使用的列)
mysql SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME 2011-08-02 ANDRECORD_DATETIME 2011-08-11
+———-+
| COUNT(*) |
+———-+
| 5083194 |
+———-+
1 row in set (2.83sec)
mysql SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME 2011-08-02 AND RECORD_DATETIME 2011-08-11
+———-+
| 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 2011-08-15
+———-+
| COUNT(*) |
+———-+
| 2116249 |
+———-+
1 row in set (1.85sec)
mysql SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE 2011-08-13 AND RECORD_DATE 2011-08-15
+———-+
| 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 2011-08-13 LIMIT 1\G;
***************************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 2011-08-13 LIMIT1\G;
***************************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 2011-08-15
***************************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 2011-08-15
***************************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 2011-08-10
Query OK, 3929328rows affected (1 min 29.68 sec)
由此可见，删除整个分区内的数据还是很快的，
如果分区表采用传统的方式删除：
mysql DELETEFROM RPT_MALEVENTS WHERE RECORD_DATE 2011-08-11
Query OK, 1153866rows affected (19.72 sec)
mysql DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE 2011-08-11
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] 注意主键和唯一索引的区别
看完上述内容，你们掌握 MySQL 中怎么实现分区表的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
