数据库分区
数据库分区
什么是分区?
分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，
而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置
了。app 读写的时候操作的还是表名字，db 自动去组织分区的数据。
分区主要有两种形式：
水平分区(Horizontal Partitioning)这种形式分区是对表的行进行分区，所有在表中定义的
列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
垂直分区(Vertical Partitioning)这种分区方式一般来说是通过对表的垂直划分来减少目标表
的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大 text 和 BLOB 列的表，这些 text 和 BLOB 列又不经常被访问，
这时候就要把这些不经常使用的 text 和 BLOB 了划分到另一个分区，在保证它们数据相关性
的同时还能提高访问速度。
分区技术支持
在 5.6 之前，使用这个参数查看当将配置是否支持分区
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+
如果是 yes 表示你当前的配置支持分区
在 5.6 及以采用后，则采用如下方式进行查看
mysql> show plugins;
在显示结果中，可以看到 partition 是 ACTIVE 的，表示支持分区
下面我们先演示一个按照范围(range)方式的表分区
创建 range 分区表
插入些数据
到存放数据库表文件的地方看一下
从 information_schema 系统库中的 partitions 表中查看分区信息
mysql> select * from information_schema.partitions where table_schema='test2' and
table_name='user'\\G;
从某个分区中查询数据
mysql> select * from test2.user partition(p0);
新增分区
mysql> alter table test2.user add partition (partition partionname values less than (n));
删除分区
当删除了一个分区，也同时删除了该分区中所有的数据。
ALTER TABLE test2.user DROP PARTITION p3;
分区的合并
下面的 SQL，将 p1 – p3 合并为 2 个分区 p01– p02
未分区表和分区表性能测试
创建一个未分区的表
mysql> create table test2.tab19(c1 int,c2 varchar(30),c3 date);
创建分区表,按日期的年份拆分
注意:最后一行，考虑到可能的最大值
通过存储过程插入 100 万条测试数据
创建存储过程：
注：RAND()函数在 0 和 1 之间产生一个随机数，如果一个整数参数 N 被指定，它被用作种
子值。每个种子产生的随机数序列是不同的。
执行存储过程 load_part_tab 向 test2.tab19 表插入数据。
向 test2.tab2 表中插入数据
测试 SQL 性能
结果表明分区表比未分区表的执行时间少很多。
通过 explain 语句来分析执行情况
explain 语句显示了 SQL 查询要处理的记录数目可以看出分区表比未分区表的明显扫描的记
录要少很多。
创建索引后情况测试
重启mysqld服务
创建索引后分区表比未分区表相差不大(数据量越大差别会明显些)
mysql 分区的类型
1.RANGE 分区
基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，
使用 VALUES LESS THAN 操作符来进行定义。以下是实例。
按照这种分区方案，在商店 1 到 5 工作的雇员相对应的所有行被保存在分区 P0 中，商店 6
到 10 的雇员保存在 P1 中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。
对于包含数据(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一个新行，可以很容易地确
定它将插入到 p2 分区中，但是如果增加了一个编号为第 21 的商店，将会发生什么呢?在这
种方案下，由于没有规则把 store_id 大于 20 的商店包含在内，服务器将不知道把该行保存
在何处，将会导致错误。要避免这种错误，可以创建 maxvalue 分区，所有不在指定范围内
的记录都会被存储到 maxvalue 所在的分区中。
mysql> alter table test2.user add partition (partition p4 values less than maxvalue);
2.LIST 分区
类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进
行选择。
LIST 分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列
值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，
其中“value_list”是一个通过逗号分隔的整数列表。
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：
这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录(行)可以使用查询“ALTER
TABLE employees DROP PARTITION pWest;”来进行删除，它与具有同样作用的 DELETE (删
除)查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);”比起来，
要有效得多。
要点：如果试图插入列值不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例
如，假定 LIST 分区的采用上面的方案，下面的插入将失败：
INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
这是因为“store_id”列值 21 不能在用于定义分区 pNorth, pEast, pWest,或 pCentral 的值列表中
找到。要重点注意的是，LIST 分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其
他值在内的定义。将要匹配的任何值都必须在值列表中找到。
3.HASH 分区
这种模式允许 DBA 通过对表的一个或多个列的 Hash Key 进行计算，最后通过这个 Hash 码不
同数值对应的数据区域进行分区。
hash 分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致
一致。在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个
分区中;而在 HASH 分区中，MYSQL 自动完成这些工作，用户所要定一个列值或者表达式，
以及指定被分区的表将要被分割成的分区数量。
hash 的分区函数页需要返回一个整数值。partitions 子句中的值是一个非负整数，不加的
partitions 子句的话，默认为分区数为 1。
该记录会被放入分区 p2 中。因为插入 2010-04-01 进入表 t_hash,那
MOD(YEAR('2010-04-01'),4)=2
mysql> select * from information_schema.partitions where table_schema='test2' and table_name='t_hash'\\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2017-02-26 21:11:08
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2017-02-26 21:11:08
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p2
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2017-02-26 21:11:08
UPDATE_TIME: 2017-02-26 21:11:58
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: t_hash
PARTITION_NAME: p3
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2017-02-26 21:11:08
UPDATE_TIME: 2017-02-26 21:11:58
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
4 rows in set (0.03 sec)
可以看到 P2 分区有一条记录。当前这个例子并不能把数据均匀的分布到各个分区，因为按
照 YEAR 函数进行的，该值本身是离散的。如果对连续的值进行 HASH 分区，如自增长的主
键，则可以较好地将数据平均分布。
4.key 分区
key 分区和 hash 分区相似，不同在于 hash 分区是用户自定义函数进行分区，key 分区使用
mysql 数据库提供的函数进行分区，NDB cluster 使用 MD5 函数来分区，对于其他存储引擎
mysql 使用内部的 hash 函数。
mysql> create table t_key( a int(11), b datetime) partition by key(b) partitions 4;
上面的 RANGE、LIST、HASH、KEY 四种分区中，分区的条件必须是整形，如果不是整形需要
通过函数将其转换为整形。
5.columns 分区
mysql-5.5 开始支持 COLUMNS 分区，可视为 RANGE 和 LIST 分区的进化，COLUMNS 分区可以
直接使用非整形数据进行分区。COLUMNS 分区支持以下数据类型：
所有整形，如 INT SMALLINT TINYINT BIGINT。FLOAT 和 DECIMAL 则不支持。
日期类型，如 DATE 和 DATETIME。其余日期类型不支持。
字符串类型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 类型不支持。
COLUMNS 可以使用多个列进行分区。
mysql 分表和分区有什么区别呢
1、实现方式上
a) mysql 的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都
对应三个文件，一个.MYD 数据文件，.MYI 索引文件，.frm 表结构文件。
b) 分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据
的区块变多了
2、数据处理上
a)分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表
里面。
b)分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的
表呢，还是一张表，数据处理还是由自己来完成。
3、提高性能上
a)分表后，单表的并发能力提高了，磁盘 I/O 性能也提高了。并发能力为什么提高了呢，
因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发
压力分到不同的小表里面。
b)mysql 提出了分区的概念，主要是想突破磁盘 I/O 瓶颈，想提高磁盘的读写能力，来增加
mysql 性能。
在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高 mysql 并发能力
上;而分区呢，如何突破磁盘的读写能力，从而达到提高 mysql 性能的目的。
4、实现的难易度上
a)分表的方法有很多，用 merge 来分表，是最简单的一种方式。这种方式跟分区难易度差
不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
b)分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说
是透明的。
mysql 分表和分区有什么联系?
1.都能提高 mysql 的性高，在高并发状态下都有一个良好的表现。
2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我
们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区
的方式等。
3.分表技术是比较麻烦的，需要手动去创建子表，app 服务端读写时候需要计算子表名。采
用 merge 好一些，但也要创建子表和配置子表间的 union 关系。
4.表分区相对于分表，操作方便，不需要创建子表。