mysql分区之RANGE类型

目录

首先查看MySQL是否支持分区

在实际操作分区前我们得了解下分区的几点限制：

子分区的建立需要注意以下几个问题：

RANGE分区实操

SQL如何查询分区数据

---

首先查看MySQL是否支持分区

show  plugins;

 

 当查询结果显示partition的状态为active则表示当前MySQL版本支持分区。分区方案一般有四种：

1.  RANGE分区：RANGE分区主要用于日期列的分区，是基于连续区间的列值来进行分区的。RANGE分区也是最常用的分区方式。
2. LIST分区：LIST分区列的值是散列的，也正是由于分区列的值是散列的所以需要手动定义分区列的值。
3. HASH分区：HASH分区就是通过自定义算法将数据均匀分布到不同分区，保证所有分区中数据大致相同。HASH分区只支持整数分区。
4. KEY分区：key分区使用数据库自带函数进行分区。KEY分区可支持BLOB和TEXT列之外的数据列分区。 

由于RANGE分区是最常用的分区方案，其他三种分区方案用的相对很少。这里主要介绍RANGE方案。

在实际操作分区前我们得了解下分区的几点限制：

1.  数据表分区上限最多只能有1024个分区（mysql5.6之后支持8192个分区）。
2. 同一个表的所有分区必须使用相同存储引擎。
3. 数据库使用分区则不能使用外键约束，会报错。 
4. 如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
5. mysql数据库支持的分区类型为水平分区，并不支持垂直分区，因此，mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中
6. 目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引
7. 在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。

子分区的建立需要注意以下几个问题：

1. 每个子分区的数量必须相同
2. 只要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须在所有分区上定义子分区，不能漏掉一些分区不进行子分区。
3. 每个subpartition子句必须包括子分区的一个名字
4. 子分区的名字必须是唯一的，不能在一张表中出现重名的子分区
5. mysql数据库的分区总是把null当作比任何非null更小的值，这和数据库中处理null值的order by操作是一样的，升序排序时null总是在最前面，因此对于不同的分区类型，mysql数据库对于null的处理也各不相同。对于range分区，如果向分区列插入了null，则mysql数据库会将该值放入最左边的分区，注意，如果删除分区，分区下的所有内容都从磁盘中删掉了，null所在分区被删除，null值也就跟着被删除了。在list分区下要使用null，则必须显式地定义在分区的散列值中，否则插入null时会报错。hash和key分区对于null的处理方式和range,list分区不一样，任何分区函数都会将null返回为0.

RANGE分区实操

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。 

CREATE TABLE order_info (
id int not NULL PRIMARY KEY auto_increment,
goods_title VARCHAR(50) NOT NULL,
created_at date
) 
PARTITION BY RANGE (id) (
PARTITION order1 VALUES less than (10),
PARTITION order2 VALUES less than (20),
PARTITION order3 VALUES less than (30),
PARTITION order4 VALUES less than (40),
PARTITION order5 VALUES less than (50)
);

 创建一张order_info表并创建了5个分区，接下来我们查看order_info表分区情况，看是否成功形成5个分区：

SELECT partition_name part,
partition_expression expr,
partition_description descr
,table_rows FROM information_schema.`PARTITIONS` 
WHERE table_schema=SCHEMA() AND table_name='order_info';

  

可以看到上面的创建分区我们使用values less than语句，我们将id<10的数据保存在order1分区，将id范围在10-20保存在order2分区，以此类推创建了五个分区。

现在我们可以写个存储过程插入50条数据测试下： 

CREATE PROCEDURE test010()
BEGIN
DECLARE i INT;
SET i=1;
START TRANSACTION;
WHILE i<=50 DO
	INSERT INTO order_info VALUES(NULL,'测试数据',NOW());
SET i=i+1;
END WHILE;
COMMIT;
END
//
delimiter;
CALL test010();

 可以明显看到报错了，报没有分区可以存放id为50的值的错误信息。因为我们设置的第五个分区范围为40-50，右边不包含，所以超过50直接报错无法存放。这很不合理，所以说我们得对分区做一下调整  

我们使用alter table命令新增分区order6保存大于50的值：

ALTER TABLE order_info ADD PARTITION(PARTITION order6 VALUES less than (MAXVALUE));

  

 我们增加了order6分区，值设置为maxvalue，表示如果值大于order5设置的上限则保存进入order6分区。

我们插入500条数据到数据库中测试下效果：

   

我们查看下分区下各自含有几条数据 

   

我们可以看到完全按照我们设置的分区进行数据存放到不同的分区。可能有人会说我怎么确认order1分区存放的9条数据一定是id为1-10的呢？
这个其实测试很简单，我们直接删除order1分区，然后查看数据库数据： 

ALTER TABLE order_info DROP PARTITION order1;

   

        可以看到我们删除掉order1分区之后，id10以下的数据也跟着被删除了，所以可以表示id在10以下的确实保存在分区order1中。而我们完全可以按照时间来进行分区，然后将半年以上的数据移到备用表然后删除分区，这样就可以提高我们单表的性能。在删除数据时直接删除分区会远远比delete数据效率高很多。 

SQL如何查询分区数据

在进行select/update/delete时如果where后面的限制条件包含分区字段id时会自动去对应分区中查找，否则还是全表扫描。

EXPLAIN SELECT * FROM order_info WHERE id = 20;

可以看到上图只查询了 order3 分区。

EXPLAIN SELECT * FROM order_info WHERE goods_title = '测试数据2';

 可以看到上图查询了 order2，order3，order4，order5，order6分区，共查询了491行 。

range分区字段只支持整型，如果需要对时间日期这样的字段进行range分区，可以通过相关函数将类型转为整型再分区。