概述
前面已经介绍了mysql一些分区内容,所以下面主要简单做个实验,然后看下分区表有什么优势。
1、环境准备
1.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.2、创建一个不分区的表
create table no_part_tab (c1 int(11) default NULL, c2 varchar(30) default NULL, c3 date default NULL ) engine=myisam;
2、准备数据
2.1、创建一个生成1000000行数据的存储过程:
delimiter // CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 1000000 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 //
2.2、调用存储过程,生成数据(本来想1千万的,这数据库性能比较差,所以改成100万)
mysql> delimiter ; mysql> call load_part_tab(); mysql> insert into no_part_tab select * from part_tab;
3、开始测试
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
因为数据量不是特别大,所以可以看到速度有一定下降,但差距不是很明显,下面看一下查询计划。
4、查看执行计划
mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G; mysql> explain partitions select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
到这里关于分区的实验就做完了,大家有空也可以自己测试一下,把数据量提上去看看,后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下!