mysql关于大型数据查找删除和添加索引的调查报告

为了研究性能，所以必须先引入大量的数据，基数据为1600多个，现在执行insert into xn_profile (select * from xn_profile)来不断增加数据。
其时间分别为0.11sec，0.27sec，0.35sec，0.39sec，1.6sec，3.45sec，7.38sec，27.41sec，46.72sec，99.66sec，大概是线性变化，但是不稳定，尤其7.38到27.41。

在437216的基数上查询

select count(*) from xn_profile where university="南京大学";

时间为2.27秒，但是再次查询相同的却用了0秒。


随后在university上建立了索引。

create index university_index on xn_profile(university);

花了44.93秒。
再次执行相同的查询

select count(*) from xn_profile where university="南京大学";

花了0.06秒


随后

select count(*) from xn_profile where university="清华大学"

，清华大学比南京大学少得多得多（大概1/100），也只用了0.05秒。

select count(*) from xn_profile where province="江苏";

在没有索引的情况下用了2.9秒，查了江西，也是2.79秒。


再去掉索引，花了27.94秒。


随后将基数上升到1730000，原数据的4倍

select count(*) from xn_profile where university="东南大学";

用了11.16秒，差不多是2.27秒的4倍，符合顺序查找的规律。

select count(*) from xn_profile;

也用了11.79秒。
此时再次建立索引用了223秒。



然后执行相同的

select count(*) from xn_profile where university="清华大学"

，用了0.13秒，查东南大学用了0.42秒，查南京大学2.03秒。


再增加一倍的数据，356秒（包括创建额外索引的时间），这个时间也不知道是应该怎么看
才好。基数达到3475000
再

select count(*) from xn_profile;

用了52.67秒，这个比是11.26秒的5倍，搞不懂了，怎么会增长这么快？

select count(1) from xn_profile where university="东南大学";

只用了0.83秒

 select count(1) from xn_profile where university="南京大学";

用了4.33秒


我插了一条数据，

 insert into xn_profile(university) values("test");

再

select count(1) from xn_profile where university="test";

用了0秒，快得一比。


mysql在不同的连接时也会缓存查询结果。
此时，备份该表，用了将近2分钟。数据量达820兆，当然都是sql语句，不敢打开。



结论1、在插入insert操作时时间随插入量大致是线性变化的。
结论2、mysql会把之前查询到的结果缓存，可以跨连接缓存。
结论3、影响索引查询的主要因素取决于数据量的多少，在数据量整体都不是很大时看不出来，但是数据量很大很大时，就明显了。貌似是根号关系，即数据量增加x的平方倍，时间增加x倍。
结论4、在顺序查找（无索引的情况下）时，查询时间主要受查询时间的影响而非io传输的影响。
结论5、我认为顺序查询主要受查询时间的影响。而索引查询主要受io数据传输的影响。

评论

3 楼 lubobill1990 2010-05-14  

maosuhan 写道

lubobill1990 写道

“其时间分别为0.11sec，0.27sec，0.35sec，0.39sec，1.6sec，3.45sec，7.38sec，27.41sec，46.72sec，99.66sec，大概是线性变化，但是不稳定，尤其7.38到27.41。 ”
这个不是线性变化吧，是2的n次方的增长。
前期一直到7.38s符合预期规律，之后都差不多是预期时间的两倍，应该是内存的问题。

“Sql代码
select count(*) from xn_profile where university="南京大学"; 
时间为2.27秒，但是再次查询相同的却用了0秒。 ”
第二次查询使用0秒应该是第一次的查询的缓存直接读出的结果

绝对不是2的n次方，因为理论上讲不通

1,2,4,8,16,32
这个应该不是线性变化吧。。。
1600->3200->6400->12800->.......
2^0    2^1   2^2    2^3……

应该是2的N次方吧

2 楼 maosuhan 2010-03-18  

lubobill1990 写道

“其时间分别为0.11sec，0.27sec，0.35sec，0.39sec，1.6sec，3.45sec，7.38sec，27.41sec，46.72sec，99.66sec，大概是线性变化，但是不稳定，尤其7.38到27.41。 ”
这个不是线性变化吧，是2的n次方的增长。
前期一直到7.38s符合预期规律，之后都差不多是预期时间的两倍，应该是内存的问题。

“Sql代码
select count(*) from xn_profile where university="南京大学"; 
时间为2.27秒，但是再次查询相同的却用了0秒。 ”
第二次查询使用0秒应该是第一次的查询的缓存直接读出的结果

绝对不是2的n次方，因为理论上讲不通

1 楼 lubobill1990 2010-03-15  

“其时间分别为0.11sec，0.27sec，0.35sec，0.39sec，1.6sec，3.45sec，7.38sec，27.41sec，46.72sec，99.66sec，大概是线性变化，但是不稳定，尤其7.38到27.41。 ”
这个不是线性变化吧，是2的n次方的增长。
前期一直到7.38s符合预期规律，之后都差不多是预期时间的两倍，应该是内存的问题。

“Sql代码
select count(*) from xn_profile where university="南京大学"; 
时间为2.27秒，但是再次查询相同的却用了0秒。 ”
第二次查询使用0秒应该是第一次的查询的缓存直接读出的结果