MySQL分区实验
环境 CentOS5.5,内存256MB,MySQL5.5.16
一、创建测试表
CREATE TABLE part_tab( c1 INT DEFAULT NULL, c2 VARCHAR(30) DEFAULT NULL, c3 DATE DEFAULT NULL ) ENGINE=myisam CHARSET=utf8 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)
CREATE TABLE `no_part_tab` ( `c1` int(11) DEFAULT NULL, `c2` varchar(30) DEFAULT NULL, `c3` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
二、创建测试数据
CREATE PROCEDURE `p1`() begin declare v int default 0; while v<8000000 do insert into part_tab values (v,‘testing partition‘,adddate(‘1995-01-01‘,(rand(v)*36520) mod 3652)); set v=v+1; end while; end
call p1;
insert into no_part_tab select * from part_tab
三、测试
select count(*) from part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘; #1.875s
select count(*) from no_part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘; #9.269s
四、分析
desc select count(*) from part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘ \G;
desc select count(*) from no_part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘ \G;
可以看出慢的原因了
五、创建索引再测试
create index idx_of_c3 on part_tab(c3);
create index idx_of_c3 on no_part_tab(c3);
select count(*) from no_part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘; #1.49s
select count(*) from part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1995-12-31‘; #1.48s
查询时间差不多,分析图就想想就知道样子了
六、使用未索引字段查询
select count(*) from no_part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1996-12-31‘ and c2=‘hello‘; #12.274s
select count(*) from part_tab where c3>date ‘1995-01-01‘ and c3< date ‘1996-12-31‘ and c2=‘hello‘; #2.74s
七、分析
八、总结
在数据量较大的情况 使用被作为查询条件较频繁的字段 作为分区的依据,今天才知道\d 可以代替delimiter 。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。