mysql 分区 range_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文件。数据物理上是真实分开的。

往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分区两行数据。说明数据被分区了。

对SQL语句的分析

explain partitions select * from t where id < 9;

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

输出结果：

explain解析结果.png

我们发现这条语句仅仅使用了p0分区，因此相比于没有分区之前查询的效率大大提高了。

接下来我们看下分区失败的一些情况。

创建分区表

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。

插入几条数据

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';

SQL语句分析

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

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

分区执行计划.png

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

创建分区表

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分区时要注意使用合适的分区函数，否则会没有效果，甚至是不能得到正确的查询结果。