MySql分区介绍和Range案例

MySql分区介绍和Range案例

什么是表分区？

通俗地讲表分区是将一个大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区。
如：某用户表的记录超过了600万条仓储信息，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其它的条件分区。

为什么要对表进行分区？

• 为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。
• 分区的一些优点包括
  • 与单个磁盘或文件系统分区相比，可以存储更多的数据。
  • 通过删除与增加那些数据有关的分区，很容易地删除或增加那些数据。
  • 一些查询可以得到极大的优化。
  • 通过跨多个磁盘甚至服务器来分散数据查询，来获得更多的查询吞吐量。

基本分区类型

• RANGE分区：基于连续的区间范围，把数据分配到不同区。只支持整数分区。
• LIST分区：类似RANGE分区，区别是LIST分区基于给出的枚举的值进行分区，无需按照顺序。只支持整数分区。
• HASH分区：给定分区个数，按照一个散列函数，确定数据进入哪个分区。
• KEY分区，类似HASH分区。

首先看一下mysql中的year函数
如下图：

year函数会返回指定日期的年份。

RANGE分区
基于属于一个给定连续区间的列值，把多行分配给分区。
这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。看一个例子，如下：

CREATE TABLE part_tab (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
    )engine=INNODB PARTITION BY RANGE (year(c3) )( 
        PARTITION p3 VALUES LESS THAN (2000) ,
        PARTITION p4 VALUES LESS THAN (2010) ,
        PARTITION p11 VALUES LESS THAN MAXVALUE 
    ) ;
1
2
3
4
5
6
7
8
9

上述代码解释，我们会对日期年份进行一个范围分区，如果年份小于2000，那么我们把这些mysql表数据放到我们的p3区；如果2000 <= 年份 < 2010，那么我们把这些mysql表数据放到我们的p4分区；如果2010 <= 年份 < MAXVALUE，那么我们就把这些mysql表数据放到我们的p11分区。

LIST分区

• 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• LIST分区通过使用"PARTITION BY LIST(expr)"来实现，其中"expr"是某列值或一个基于某个列值，并返回一个整数值的表达式，然后通过"VALUES IN(value_list)"的方式来定义每个分区，其中"value_list"是一个通过逗号分隔的整数列表。

代码如下：

CREATE TABLE part_tab2 (
   c1 int default NULL,
   c2 varchar(30) default NULL,
   c3 date default NULL
   )engine=INNODB PARTITION BY LIST(year(c3) )( 
       PARTITION p1 VALUES IN (1999,2000) ,
       PARTITION p2 VALUES IN (1998,2005) ,
       PARTITION p2 VALUES IN (2010) 
   ) ;
1
2
3
4
5
6
7
8
9

List分区需要把所有的列值都写出来。

HASH分区

• 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的，产生非负整数值的任何表达式。
• 要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个"PARTITION BY HASH(expr)"子句，其中"expr"是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面添加一个"PARTITIONS num"子句，其中num是一个非负的整数，它表示表将要被分割成的分区的数量。如果没有这个子句，那么分区的数量默认为1个分区。
• 使用HASH分区的优点在于数据分布较为均匀，如何分区是通过函数进行计算的。

代码例子如下：

CREATE TABLE part_tab3 (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL,
    id INT NOT NULL
    )engine=INNODB PARTITION BY HASH(id) PARTITIONS 4;
1
2
3
4
5
6

上面的代码会分为4个分区，通过id列的值通过mysql内部的哈希函数，mysql会为我们自动的计算出改行的分区，所以使用HASH分区的有点就是数据分布的会比较均匀，如果我们指定了4个分区，一共有1000条数据，最后应该每个区都差不多有250条左右。具体的hash算法是很复杂的，但mysql内部已经帮助我们写好了，所以我们不用太关心。