mysql数据库为什么要分表和分区

为什么要分表和分区?

　　我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询书读变慢，而且

　　由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。

　　mysql 中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能

　　对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的 sql 必须等我对这条数

　　据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。

　　1、分表

　　什么是分表?

　　分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三

　　个文件，MYD 数据文件，.MYI 索引文件，.frm 表结构文件。这些表可以分布在同一块磁盘

　　上，也可以在不同的机器上。app 读写的时候根据事先定义好的规则得到对应的表名，然后

　　去操作它。

　　将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法(如

　　用 hash 的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多

　　个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，

　　减小数据库的负担，缩短查询时间。

　　Mysql 分表分为垂直切分和水平切分

　　垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表

　　通常我们按以下原则进行垂直拆分:

　　把不常用的字段单独放在一张表;

　　把 text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;

　　经常组合查询的列放在一张表中;

　　垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用 jion 关键起来

　　即可。

　　水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。

　　水平拆分原则

　　通常情况下，我们使用 hash、取模等方式来进行表的拆分

　　比如一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，

　　users3，users4

　　通过用 ID 取模的方法把数据分散到四张表内 Id%4= [0,1,2,3]

　　然后查询,更新,删除也是通过取模的方法来查询

　　部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;

　　进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时

　　候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

　　分表的几种方式：

　　1)mysql 集群

　　它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到

　　多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

　　2)预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

　　根据一定的算法(如用 hash 的方式，也可以用求余(取模)的方式)让用户访问不同的表。

　　例如论坛里面发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里面信息表，几十个人在一起一聊一个晚上，时间长了，这张表的数据肯定很大。像这样的

　　情况很多。所以这种能预估出来的大数据量表，我们就事先分出个 N 个表，这个 N 是多少，

　　根据实际情况而定。以聊天信息表为例：我们事先建 100 个这样的表，

　　message_00,message_01,message_02..........message_98,message_99.然后根据用户的 ID 来判

　　断这个用户的聊天信息放到哪张表里面，可以用 hash 的方式来获得，也可以用求余的方式

　　来获得，方法很多。

　　或者可以设计每张表容纳的数据量是 N 条，那么如何判断某张表的数据是否容量已满呢?

　　可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当

　　就直接插入，当已经到达阀值，可以在程序段新创建数据库表(或者已经事先创建好)，再

　　执行插入操作)。

　　3)利用 merge 存储引擎来实现分表

　　如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的 sql 语

　　句已经写好了，用 merge 存储引擎来实现分表, 这种方法比较适合。

　　merge 分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是

　　存储在子表中的。

　　我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

　　下面我们来实现一个简单的利用 merge 存储引擎来实现分表的演示：

　　创建一个完整表存储着所有的成员信息(表名为 member)

　　加入点数据：

　　第二条语句多执行几次就有了很多数据：

　　下面我们进行分表，这里我们把 member 分两个表 tb_member1,tb_member2

　　创建主表 tb_member

　　注：INSERT_METHOD,此参数 INSERT_METHOD = NO 表示该表不能做任何写入操作只作为查

　　询使用,INSERT_METHOD = LAST 表示插入到最后的一张表里面。INSERT_METHOD = first 表示

　　插入到第一张表里面。

　　查看一下 tb_member 表的结构:

　　注：查看子表与主表的字段定义要一致

　　接下来，我们把数据分到两个分表中去：

　　查看两个子表的数据：

　　查看一下主表的数据：

　　注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。

　　注意：每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件。

　　2、分区

　　什么是分区?

　　分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，

　　而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置

　　了。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.表分区相对于分表，操作方便，不需要创建子表。

 

本节主要讨论在MySQL5.1中可用的分区类型，包括：

RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区。

LIST 分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区。

HASH 分区：基于给定的分区个数，把数据分配到不同的分区。

KEY 分区：类似于HASH分区

MySQL5.1版本中，RANGE分区、LIST分区、HASH分区都要求分区键必须是INT类型，或者通过表达式返回INT类型，也就说MySQL5.1仅仅支持整数分区，唯一的例外就是分区类型为KEY分区的时候，可以使用其他类型的列（BLOBor TEXT列类型除外）作为分区键。

注意：在MySQL5.5或以上版本中，已经支持非整数的RANGE和LIST分区了，在后面6.2.3 Columns分区章节有详细的例子说明。

无论是哪种MySQL分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其他字段分区，例如 emp表的主键为id字段，在尝试通过 store_id 字段分区的时候，MySQL会提示返回失败：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

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

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

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL,

-> PRIMARY KEY (id)

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

去掉主键约束后，创建表就会成功：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

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

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

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

分区的名字基本上遵循MySQL标识符的原则。说到命名，顺便介绍一下MySQL命名中的大小写敏感：在MySQL中，数据库和表对应于数据目录中的目录和文件。所以，操作系统的大小写敏感性决定数据库和表命名的大小写敏感性。这就意味着数据库和表名在Windows中是大小写不敏感的，而在大多数的Unix或Linux系统中是大小写敏感的。但是需要注意的是，分区的名字是不区分大小写的。例如，下面的CREATE TABLE 语句将会产生错误：

mysql> CREATE TABLE t2 (val INT)

-> PARTITION BY LIST(val)(

-> PARTITION mypart VALUES IN (1,3,5),

-> PARTITION MyPart VALUES IN (2,4,6)

-> );

ERROR 1517 (HY000): Duplicate partition name mypart

这是因为MySQL认为分区名字 mypart 和 MyPart 没有区别。

6.2.1Range 分区

按照RANGE分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用VALUES LESS THAN 操作符进行分区定义。

例如雇员表emp 中按商店ID store_id 进行RANGE分区：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

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

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

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

按照这种分区方案，在商店1到9工作的雇员相对应的所有行被保存在分区P0中，商店10到19的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；类似JAVA或者C中的“switch case”语句。

这个时候，如果增加了商店ID大于等于30的行，会出现错误，因为没有规则包含了商店ID大于等于30的行，服务器不知道该把记录保存在那里。

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

ERROR 1526 (HY000): Table has no partition for value 50

可以通过设置分区的时候VALUES LESS THAN MAXVALUE子句，该子句提供给所有大于明确指定的最高值的值，MAXVALUE 表示最大的可能的整数值。例如，增加p3分区存储所有商店ID大于等于30的行之后再执行插入语句就没有问题：

mysql> alter table emp add partition (partition p3 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

Query OK, 1 row affected (0.04 sec)

MySQL支持在VALUES LESS THAN 子句中使用表达式，比如以日期作为RANGE分区的分区列：

mysql> CREATE TABLE emp_date (

-> id INT NOT NULL,

-> ename VARCHAR(30),

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

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

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (YEAR(separated)) (

-> PARTITION p0 VALUES LESS THAN (1995),

-> PARTITION p1 VALUES LESS THAN (2000),

-> PARTITION p2 VALUES LESS THAN (2005)

-> );

Query OK, 0 rows affected (0.08 sec)

注意： 在RANGE分区中，分区键如果是NULL值会被当做一个最小值来处理，在后续的6.2.7 MySQL分区处理NULL值的方式 小节中有详细的说明。

MySQL 5.1 支持整数列分区，那么对于想在日期或者字符串列上进行分区，就得使用函数进行转换。但是要是查询如果不用函数转换，那么就无法利用RANGE分区特性来提高查询性能。

MySQL 5.5 改进了RANGE分区功能，提供了RANGE COLUMNS分区支持非整数分区，创建日期分区就不需要通过函数进行转换，例如：

mysql> CREATE TABLE emp_date(

-> id INT NOT NULL,

-> ename VARCHAR(30),

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

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

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE COLUMNS (separated) (

-> PARTITION p0 VALUES LESS THAN ('1996-01-01'),

-> PARTITION p1 VALUES LESS THAN ('2001-01-01'),

-> PARTITION p2 VALUES LESS THAN ('2006-01-01')

-> );

Query OK, 0 rows affected (0.07 sec)

注意： MySQL 5.1分区日期处理上支持的函数只有两个YEAR() 和 TO_DAYS()。MySQL 5.5 分区日期处理上增加支持函数TO_SECONDS()，把日期转换成秒钟，从能够比按天分区更细化的分区。

RANGE 特别适用于以下情况：

当需要删除过期的数据时，只需要简单的ALTER TABLE emp DROP PARTITION p0 来删除p0分区中数据。对于上百万的记录的表来说，删除分区要比运行一个DELETE语句有效的多。

经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该WHERE子句的任何记录。例如，检索商店ID大于等于25的记录数，MySQL只需要扫描p2分区即可：

mysql> explain partitions select count(1) from emp where store_id >= 25\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: emp

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

6.2.2List 分区

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST分区在很多方面类似于RANGE分区，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。

LIST分区通过使用PARTITION BY LIST(expr) 子句来实现，expr是某列值或一个基于某列值返回一个整数值的表达式，然后通过VALUES IN(value_list) 的方式来定义分区，其中value_list是一个逗号分隔的整数列表。和RANGE分区不同，LIST分区不必声明任何特定的顺序，例如：

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category INT,

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST(category) (

-> PARTITION p0 VALUES IN (3, 5),

-> PARTITION p1 VALUES IN (1, 10),

-> PARTITION p2 VALUES IN (4, 9),

-> PARTITION p3 VALUES IN (2),

-> PARTITION p4 VALUES IN (6)

-> );

Query OK, 0 rows affected (0.09 sec)

注意： MySQL 5.1 中，LIST分区只能匹配整数列表。category 只能是INT类型，所以需要额外的转换表来记录类别编号和类别的名称。

如果试图插入的列值（或者分区表达式的返回值）不包含分区值列表中时，那么INSERT操作会失败并报错。要重点注意，LIST分区不存在类似VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。

MySQL 5.5 中支持非整数分区，创建LIST分区就不需要额外的转换表：

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category VARCHAR(30),

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST COLUMNS (category) (

-> PARTITION p0 VALUES IN ( 'lodging', 'food'),

-> PARTITION p1 VALUES IN ( 'flights', 'ground transportation'),

-> PARTITION p2 VALUES IN ( 'leisure', 'customer entertainment'),

-> PARTITION p3 VALUES IN ( 'communications'),

-> PARTITION p4 VALUES IN ( 'fees')

-> );

Query OK, 0 rows affected (0.07 sec)

转载于:https://my.oschina.net/shunshun/blog/3074463