Mysql分区

MySQL表分区

不同分区技术对比

类型

优点

缺点

Range

适合与日期类型，支持复合分区

有限的分区

List

适合与有固定取值的列，支持符合分区

有限的分区，插入值不在List中，则数据丢失

Hash

线性Hash使得增加、删除和合并分区更快捷

线性Hash的数据分布不均匀，而一般Hash的数据分布较均匀

Key

列可以为字符型等其他非Int类型

效率较之前的低，因为函数为复杂的函数（如MD5或SHA函数）

 

分区实例

创建表分区，按日期的年份拆分

mysql > CREATE TABLE part_tab(c1 intdefault NULL, c2 varchar(30) default NULL, c3 date defatul 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(2010),

      PARTITIONP11 VALUES LESS THAN MAXVALUE

);

创建未分区表

CREAT TABLE `no_part_tab`(

      `c1`int(11) DEFAULT NULL,

      `c2`varchar(30) DEFAULT NULL,

      `c3`date DEFAULT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

创建load_part_tab存储

CREATE PROCEDURE load_part_tab()

begin

declare v int default 0;

while v < 8000000

do

      insertinto part_tab

      values(v,‘testing partitions’, adddate(‘1995-01-01’,(rand(v)*36520)mod 3652));

      setv = v + 1;

end while;

end

 

执行load_part_tab存储

mysql> call load_par_tab();

mysql> insert into no_part_tab select *from part_tab;

 

测试SQL性能

mysql> select count(*) from part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’;

mysql> select count(*) from no_part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’;

结果表明分区表比未分区表的执行时间少90%

 

解析sql语句，查看rows影响行数，未分区表查询了8000000条数据

mysql> desc select count(*) frompart_tab where c3 > date ‘1995-01-01’ and c3 <date ‘1995-12-31’\G

mysql> desc select count(*) fromno_part_tab where c3 > date ‘1995-01-01’and c3 < date ‘1995-12-31’\G

 

创建索引

mysql> create index idx_of_c3 onno_part_tab(c3);

mysql> create index idx_of_c3 onpart_tab(c3);

 

测试创建索引后效果

mysql> select count(*) from part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’;

mysql> select count(*) from no_part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’;

两个表查询时间相差不多，主要原因是分区表拆成多个表后，索引文件有多个，消耗系统资源和cpu性能比未分区表要高

 

增加未索引字段查询

mysql> select count(*) from part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’and c2=’hello’;

mysql> select count(*) from no_part_tabwhere c3 > date ‘1995-01-01’ and c3 < date ‘1995-12-31’and c2=’hello’;

 

 

 

为innodb做分区

      innodb默认索引文件为共享空间，默认10M在ibdata1文件中，而且做分区的话，没有在物理上进行真正的分区，没有效果。

只有把innodb设置成独立表空间后，才能创建成功innodb表引擎的表分区

 

vim /etc/my.cnf

打开innodb配置

innodb_file_per_table = 1

重启mysql