mysql的分区技术

Mysql5种分区技术

1，        RANDE分区技术

Create table if not exist 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-01-01);
partition p2 values less than (1996-01-01);
…………
Partition p17 values less than MAXVALUE
) ;

2，  LIST分区技术

Create table if not exist part_tab(c1 int default null , c2 varchar(30) default null , c3 date default null) engine= myisam
Partition by list (c1)(
Partition p1 values in (1,2,3,4,5,6),
Partition p1 values in (7,8,9,10,11,12),
Partition p1 values in (23,24,25,26,27,29),
);

 3 ， HASH分区技术

Create table if not exist part_tab(c1 int default null , c2 varchar(30) default null , c3 date default null) engine= myisam
 partition by  hash(year(c3)) 
partitions 4;

4，        KEY分区技术

与hash分区技术类似

测试RANDG分区技术：

      创建普通表：

      Createtable no_part_table(c1 int default null, c2 varchar(30) default null,c3 datedefault null) engine=myisam;

创建RANDG分区表:

Create table part_tab(c1 int default NULL,c2 varchar(30) default NULL,c3 date default NULL ) engine=myisam

Partition by range(year(c3))(

      Partitionp0 values less than (1995),

      Partitionp1 values less than (1996),

      Partitionp2 values less than (1997),

      Partitionp3 values less than (1998),

      Partitionp4 values less than (1999),

      Partitionp5 values less than (2000),

      Partitionp6 values less than (2001),

      Partitionp7 values less than (2002),

      Partitionp8 values less than (2003),

      Partitionp9 values less than (2004),

      Partitionp10 values less than (2005),

      Partitionp11 values less than (2006),

      Partitionp12 values less than (2007),

      Partitionp13 values less than (2008),

      Partitionp14 values less than (2009),

      Partitionp15 values less than (2010),

      Partitionp16 values less than (2011),

      Partitionp17 values less than MAXVALUE

);

通过mysql的存储来插入大量的数据：

Mysql> \d //

create procedure load_part_tab()

      begin

      declarev int default 0;

      whilev < 8000000

      do

           insertinto part_tab values (v,'testingpartition',adddate('1995-01-01',(rand(v)*36520) mod 3652));

           setv = v+1;

      endwhile;

      end

      //

Mysql> /d ;

执行mysql的存储插入数据：

Mysql>call load_part_tab;

在没有分区的表里插入数据

Mysql> insert into no_part_tab select *from part_tab;

分别测试有分区的表part_tab和没有分区的表no_part_tab 的执行效率：

同样的数据和同样的SQL语句：