Mysql大数据中表分区的应用

一、支持Mysql表分区需要MYSQL版本为5.1以上，含5.1~

二、表分区的作用：增加MYSQL的执行效率，可以在以下几点分析表分区是如何增加MYSQL的执行效率的。

1、与单个磁盘或文件系统分区相比，可以存储更多的数据。

2、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。

3、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。

4、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

5、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、通俗的讲表分区是将一个大表，根据条件分割成若干个小表

四、表分区有哪些类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

五、细分类型

RANGE分区

<1>创建含分区表

CREATE TABLE part_tab( c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL) engine=myisam
PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE);

<1>创建不含分区表

CREATE TABLE no_part_tab( c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL) engine=myisam

<3>创建存储过程，在表中插入800,0000条数据以做测试

CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end

<4>再写入未分区表中

insert into no_part_tab select * from part_tab;

<5>好了，所有表数据准备完毕，下面我们来做一下测试

执行命令： select count(*) from no_part_tab where c3 >date('1995-01-01') and c3 < date('1995-12-31');

执行结果：[SQL]  select count(*) from no_part_tab where c3 >date('1995-01-01') and c3 <date('1995-12-31'); 受影响的行: 0 时间: 2.594ms 执行命令： select count(*) from part_tab where c3 >date('1995-01-01') and c3  < date('1995-12-31'); 执行结果：[SQL] select count(*) from part_tab where c3 > date('1995-01-01') and c3 <date('1995-12-31');
受影响的行: 0
时间: 0.297ms

很清楚的看到了，读取同样的数据，分区与未分区的两个表相差的不是同一个级别的数据~，接下来就可以修改自己大数据的表了

ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (
PARTITION s2009 VALUES LESS THAN (201001),
PARTITION s2010 VALUES LESS THAN (201004)
);