MySQL Range 分区详解

为了更好的理解分区表，故写下了此篇文章。希望把自己的每步操作都记录下来。

一、简介

Range 分区是最常用的一种分区类型。它是根据某个列的值划分为几个连续的区，行数据根据该列的值分别放入到不同的分区。比如：订单表，由于数据量比较大，我们根据年份来把数据存放到不同的区，比如：2010 年的数据放到p0,2011年的数据放p1，等等。当我们要查询2010年某个月的数据时，我们只需要查询p0这个分区，大大提高了查询的速度。不过需要注意的是在建立分区的时候我们要选择合适的分区方法，否则导致查询语句不能利用分区带来的遍历。因为优化器只能对year()，to_days()，to_seconds()，unix_timestamp()这类函数进行优化。

year(); 给定一个日期，返回当前日期的年份，例如year('2010-01-01')，返回2010
to_days(); 返回当前的日期距离公元0年的天数。
to_seconds(); 把当前日期转换为秒。

二、 实践

1.创建一个分区表

create table t(
  id int
) engine=innodb
partition by range(id) (
  partition p0 values less than(10),
  partition p1 values less than(20)
);

执行结果：Query OK, 0 rows affected (0.05 sec)
查看物理文件如下图所示：

分区数据文件.png

分析：发现每个分区都有一个对应的ibd文件。数据物理上是真实分开的。

2. 往t表中插入几条数据

insert into t select 9;
insert into t select 10;
insert into t select 15;

当然不是所有大小的数据都能插入成功，比如插入30后会报错，因为30不属于现有的任何分区之内，为此，我们可以加一个maxvalue值的分区。maxvalue可以理解为正无穷，因此所有大于等于20且小于maxvalue的值都放到p2分区。alter table t add partition(partition p2 values less than maxvalue);，这里就不实验了，小伙伴们感兴趣的可以试试。
为了检测数据是否真实按照要求分区，可以通过information_schema库下的partitions表来查看。

select * from partitions where table_name = 't' \G;

看下输出结果：

分区展示.png

其中标红的部分是需要重点看的，我们发现有两个分区，p0分区一行数据，p1分区两行数据。说明数据被分区了。

3. 对SQL语句的分析

explain  partitions  select * from t where  id < 9;

为了结果能展示分区信息这里语句里面加上partitions
输出结果：

explain解析结果.png

我们发现这条语句仅仅使用了p0分区，因此相比于没有分区之前查询的效率大大提高了。
接下来我们看下分区失败的一些情况。

4. 创建分区表

create table sales1 (
    money int unsigned not null,
  date datetime
)engine = innodb charset=utf8
partition by range(year(date)*100 + month(date))(
     partition p201001 values less than(201002),
     partition p201002 values less than(201003),
     partition p201003 values less than(201004)
);

执行结果：Query OK, 0 rows affected (0.06 sec)
查看物理文件如下图所示：

分区物理文件.png

在执行上面创建表的语句后mysql 为我们创建了三个partition。

5. 插入几条数据

insert into sales1 select 100,'2008-01-01';
insert into sales1 select 100,'2008-02-01';
insert into sales1 select 100,'2009-03-01';
insert into sales1 select 100,'2010-03-01';

6. SQL语句分析

explain partitions select * from sales where date >='2010-01-01' and date <= '2010-01-31';

这条语句我们预想是只会在p201001这个分区去查询，那真实的执行计划如下图所示：

分区执行计划.png

我们发现并不是我们预想的那样，mysql在查询的过程中扫描了所有的分区表，尽管数据被分配到多个分区。这是因为mysql只对特定的一些函数分区后的表进行查询优化。于是，为了能使用到分区这一特性带来的便利，我们使用其他的方法来进行分区。

7. 创建分区表

create table sales1 (
  money int unsigned not null,
  date datetime
)engine = innodb charset=utf8
partition by range(to_days(date))(
     partition p201001 values less than(734169),
     partition p201002 values less than(734197),
     partition p201003 values less than(734228)
);
insert into sales1 select 100,'2008-01-01';
insert into sales1 select 100,'2008-02-01';
insert into sales1 select 100,'2009-03-01';
insert into sales1 select 100,'2010-03-01';
explain partitions select * from sales where date >='2010-01-01' and date <= '2010-01-31';

我们再来看下explain 执行计划

执行计划.png

这个时候就使用了分区。所以大家在使用range分区时要注意使用合适的分区函数，否则会没有效果，甚至是不能得到正确的查询结果。