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