http://www.cnitblog.com/cockerel/archive/2007/07/30/30911.html
SQL数据库与Lucene数据库性能测试报告
一、 测试目的
本测试试图对用相同数据制作的SQL数据库与Lucene数据库对语义相同的查询语句的性能作一个简单的比较与分析,以找出各自的优缺点,并讨论其各自的适用场合。
二、 测试环境
CPU:AMD Atholon64 3200+,2.0G
内存:896MB
OS:Windows 2003
Jre版本:1.6.0_01
Jdk版本:1.5.0_06
Lucene版本:2.2.0
JDBC版本:Microsoft SQL Server 2005JDBC Driver 1.2
三、 测试数据设计
本次测试采用的数据来自校讯通的真实运营数据,原始数据是SQL数据库的一张数据表,该表共有33个字段,各字段名称及类型描述如下:
其中,本测试选取了ID、senderName、msgBody、msgTo四列来测试。ID代表了已做索引的int型字段;senderName代表varchar型的短文本字段;msgBody代表nvarchar型长文本字段;msgTo代表保存为varchar型的数值字段。
原始数据共有5623689组,占用约2GB的硬盘空间。在本测试中,分别选取其中前100万组数据和前491万组数据来参与测试。相同数据量的Lucene索引使用自写的RDB2Lucene Java包生成。对所有的列都进行了索引化和词元化。
设计测试数据时,考虑到以下几个方面的对比:
ü 不同字段的查询的对比;
ü 不同结果集规模的对比;
ü 不同查询数据规模的对比;
ü 同一项测试连续进行多次的前后效率对比
具体测试数据及测试结果见以下两个统计表:
100万组数据测试统计表:
|
|
SQL |
Lucene |
| ||||
用例 |
搜索字段 |
搜索词 |
结果集 |
用时 |
搜索词 |
结果集 |
用时 |
结果集 |
1 |
ID |
%1% |
491542 |
10906 |
|
|
|
不同 |
2 |
ID |
1% |
153660 |
1250 |
1* |
153660 |
24502 |
相同 |
3 |
ID |
1234 |
1 |
1453 |
1234 |
1 |
2047 |
相同 |
4 |
ID |
2% |
110981 |
1109 |
2* |
110981 |
16845 |
相同 |
5 |
ID |
3% |
108023 |
1094 |
3* |
108023 |
3672 |
相同 |
6 |
ID |
111_ |
10 |
1437 |
111? |
10 |
63 |
相同 |
7 |
ID |
1%2% |
66133 |
1578 |
1*2* |
66133 |
1907 |
相同 |
8 |
ID |
7%8% |
44493 |
1531 |
7*8* |
44493 |
2750 |
相同 |
9 |
ID |
4_5_ |
100 |
1406 |
4?5? |
100 |
735 |
相同 |
10 |
senderName |
林% |
4753 |
1235 |
|
|
|
不同 |
11 |
senderName |
%林% |
9124 |
1141 |
林 |
9124 |
281 |
相同 |
12 |
senderName |
%林雨% |
6 |
1141 |
林雨 |
6 |
78 |
相同 |
13 |
senderName |
%家长%' |
103 |
1141 |
家长 |
103 |
32 |
相同 |
14 |
msgBody |
%林%' |
17579 |
14767 |
林 |
17579 |
219 |
相同 |
15 |
msgBody |
%林雨% |
46 |
14595 |
林雨 |
46 |
78 |
相同 |
16 |
msgBody |
%家长% |
532790 |
10798 |
家长 |
532790 |
3406 |
相同 |
17 |
msgBody |
%家长% |
532790 |
10501 |
家长 |
532790 |
375 |
相同 |
18 |
msgBody |
'%小学% |
61657 |
14861 |
小学 |
61679 |
890 |
不同 |
19 |
msgBody |
%小% |
132968 |
14439 |
小* |
132968 |
32 |
相同 |
20 |
msgBody |
小%孩% |
3666 |
1359 |
小*孩* |
0 |
16 |
不同 |
21 |
msgBody |
%你的孩子已于% |
680 |
14876 |
你的孩子已于 |
680 |
2828 |
相同 |
22 |
msgBody |
%小% |
50014 |
15032 |
|
|
|
不同 |
23 |
msgTo |
%1% |
999949 |
2906 |
1* |
999943 |
19609 |
不同 |
24 |
msgTo |
%1% |
999949 |
2454 |
1* |
999943 |
45750 |
不同 |
25 |
msgTo |
'%1% |
999949 |
2375 |
1* |
999943 |
5969 |
不同 |
26 |
msgTo |
%13% |
998059 |
2547 |
|
|
|
不同 |
27 |
msgTo |
13% |
997901 |
2110 |
13* |
997926 |
6203 |
不同 |
28 |
msgTo |
1392.22 |
3371 |
921 |
139222* |
3371 |
15 |
相同 |
29 |
msgTo |
139222%9 |
345 |
1750 |
139222*9 |
345 |
78 |
相同 |
30 |
msgTo |
13912345678 |
74 |
1672 |
13912345678 |
74 |
171 |
相同 |
31 |
msgTo |
1_9_2_0_0_9 |
95 |
1766 |
1?9?2?0?0?9 |
95 |
125 |
相同 |
32 |
msgTo |
'1%9%2%0%0%9 |
710 |
3891 |
1*9*2*0*0*9 |
710 |
219 |
相同 |
四、 对测试数据的分析
可以从两个方向来分析这两个数据表
Ø 纵向比较
通过对比不同测试数据的用时,我们发现以下规律:
1) SQL数据库的搜索用时与被搜索字段密切相关,返回结果集的大小对搜索用时的影响相对较小,对大数据量字段的查询会造成性能急剧下降。
在数据量为100万条记录的统计表中,对ID列的查询时间基本为1.2秒~1.4秒(例外是数据1);对senderName列的查询时间基本为1.1秒~1.2秒;对msgBody列的查询时间基本为14秒(例外是数据20),对msgTo的查询时间基本为1.7秒~2.4秒
在数据量为491万条记录的统计表中,对ID列的查询时间基本为4.5秒~6秒;对senderName列的查询时间基本为50秒;对msgBody列的查询时间基本为70秒~80秒,对msgTo的查询时间基本为47秒~50秒(例外是数据35)
2) Lucene索引的搜索用时与返回结果集的大小密切相关,而与被查询列关系不大。
将100万数据表按不同查询列进行染色,再按返回结果集的大小进行排序,或先按查询列排序,再按结果集排序,便得到以下两表(连续的同项测试只保留第一项):
数据 |
搜索字段 |
结果集 |
用时 | |
20 |
msgBody |
0 |
16 | |
3 |
ID |
1 |
2047 | |
12 |
senderName |
6 |
78 | |
6 |
ID |
10 |
63 | |
15 |
msgBody |
46 |
78 | |
30 |
msgTo |
74 |
171 | |
31 |
msgTo |
95 |
125 | |
9 |
ID |
100 |
735 | |
13 |
senderName |
103 |
32 | |
29 |
msgTo |
345 |
78 | |
21 |
msgBody |
680 |
2828 | |
32 |
msgTo |
710 |
219 | |
28 |
msgTo |
3371 |
15 | |
11 |
senderName |
9124 |
281 | |
14 |
msgBody |
17579 |
219 | |
8 |
ID |
44493 |
2750 | |
18 |
msgBody |
61679 |
890 | |
7 |
ID |
66133 |
1907 | |
5 |
ID |
108023 |
3672 | |
4 |
ID |
110981 |
16845 | |
19 |
msgBody |
132968 |
32 | |
2 |
ID |
153660 |
24502 | |
16 |
msgBody |
532790 |
3406 | |
27 |
msgTo |
997926 |
6203 | |
23 |
msgTo |
999943 |
19609 | |
数据 |
搜索字段 |
结果集 |
用时 | |
3 |
ID |
1 |
2047 | |
6 |
ID |
10 |
63 | |
9 |
ID |
100 |
735 | |
8 |
ID |
44493 |
2750 | |
7 |
ID |
66133 |
1907 | |
5 |
ID |
108023 |
3672 | |
4 |
ID |
110981 |
16845 | |
2 |
ID |
153660 |
24502 | |
20 |
msgBody |
0 |
16 | |
15 |
msgBody |
46 |
78 | |
21 |
msgBody |
680 |
2828 | |
14 |
msgBody |
17579 |
219 | |
18 |
msgBody |
61679 |
890 | |
19 |
msgBody |
132968 |
32 | |
16 |
msgBody |
532790 |
3406 | |
30 |
msgTo |
74 |
171 | |
31 |
msgTo |
95 |
125 | |
29 |
msgTo |
345 |
78 | |
32 |
msgTo |
710 |
219 | |
28 |
msgTo |
3371 |
15 | |
27 |
msgTo |
997926 |
6203 | |
23 |
msgTo |
999943 |
19609 | |
12 |
senderName |
6 |
78 | |
13 |
senderName |
103 |
32 | |
11 |
senderName |
9124 |
281 |
可见,搜索用时与结果集的大小密切相关,用时有明显地随结果集增大而增多(虽然不是绝对的)特别地,当结果集进一步加大时,搜索用时可能会急剧增加而使程序无法终止,这一点在491万数据组的测试中非常常见。也就是说,当数据库规模增长到490万条记录,2G数据时,获取全部结果集的做法已变得不太现实。
3) 当连续测试同一组数据时,SQL数据库和Lucene数据库的用时都会比第一次测试用时要少。相关的测试数据在100万数据组是:
|
|
SQL |
Lucene | ||||
用例 |
搜索字段 |
搜索词 |
结果集 |
用时 |
搜索词 |
结果集 |
用时 |
16 |
msgBody |
%家长% |
532790 |
10798 |
家长 |
532790 |
3406 |
17 |
msgBody |
%家长% |
532790 |
10501 |
家长 |
532790 |
375 |
23 |
msgTo |
%1% |
999949 |
2906 |
1* |
999943 |
19609 |
24 |
msgTo |
%1% |
999949 |
2454 |
1* |
999943 |
45750 |
25 |
msgTo |
'%1% |
999949 |
2375 |
1* |
999943 |
5969 |
在491万数据组是:
|
|
SQL |
Lucene | ||||
用例 |
搜索字段 |
搜索词 |
结果集 |
用时 |
搜索词 |
结果集 |
用时 |
4 |
ID |
3% |
1103743 |
4453 |
|
|
|
5 |
ID |
3% |
1103743 |
4829 |
|
|
|
17 |
|
|
|
|
家长 |
349 |
157 |
17 |
|
|
|
|
家长 |
349 |
16 |
17 |
|
|
|
|
家长 |
349 |
0 |
20 |
|
|
|
|
林 |
168364 |
500 |
20 |
|
|
|
|
林 |
168364 |
31 |
20 |
|
|
|
|
林 |
168364 |
16 |
30 |
msgTo |
'%1% |
4909927 |
64126 |
|
|
|
30 |
msgTo |
'%1% |
4909927 |
46563 |
|
|
|
30 |
msgTo |
'%1% |
4909927 |
44941 |
|
|
|
相比之下,重复测试带来的性能提升在Lucene表现得更为明显。
Ø 横向比较
我们现在逐条数据地比较SQL数据库与Lucene数据库的用时差异,可以发现,当返回结果集不是太大(少于100万组)的情况下,一般来说Lucene的表现是比SQL要好的。尤其是在SQL被查询列没有做索引,而该查询列刚好又是长文本列的时候。用时有时可以相关上百倍。但当结果集比较大时,Lucene用时会急剧上升,有时甚至无法终止。此时,程序占用物理内存大约在700M上下,占用虚拟内存大约在1G上下,CPU占用一开始很高,但约两秒后下降至几可忽略,硬盘灯狂闪,但任务管理器显示IO并无太大变化,所以怀疑是内存抖动造成频繁调页,使得系统性能急剧下降。
Ø 表间比较
作SQL对比表,选出两表中搜索词相同的项,并使之一一对应(相同项仅保留第一次测试的结果),得:
|
|
|
100万SQL |
491万SQL |
| ||
用例 |
搜索字段 |
SQL搜索词 |
结果集 |
用时 |
|
用时 |
用时增长 |
3 |
ID |
1234 |
1 |
1453 |
1 |
5578 |
3.8389539 |
2 |
ID |
1% |
153660 |
1250 |
1088349 |
4453 |
3.5624 |
4 |
ID |
2% |
110981 |
1109 |
1106148 |
4595 |
4.1433724 |
5 |
ID |
3% |
108023 |
1094 |
1103743 |
4453 |
4.0703839 |
6 |
ID |
111_ |
10 |
1437 |
10 |
5391 |
3.7515658 |
7 |
ID |
1%2% |
66133 |
1578 |
503923 |
6735 |
4.2680608 |
8 |
ID |
7%8% |
44493 |
1531 |
44493 |
5297 |
3.4598302 |
9 |
ID |
4_5_ |
100 |
1406 |
100 |
5579 |
3.9679943 |
10 |
senderName |
林% |
4753 |
1235 |
42840 |
46850 |
37.935223 |
11 |
senderName |
%林% |
9124 |
1141 |
63667 |
52552 |
46.057844 |
12 |
senderName |
%林雨% |
6 |
1141 |
5 |
45318 |
39.717791 |
13 |
senderName |
%家长%' |
103 |
1141 |
349 |
49880 |
43.716039 |
14 |
msgBody |
%林%' |
17579 |
14767 |
168364 |
72572 |
4.9144715 |
15 |
msgBody |
%林雨% |
46 |
14595 |
131 |
73759 |
5.053717 |
16 |
msgBody |
%家长% |
532790 |
10798 |
3196271 |
91398 |
8.4643452 |
18 |
msgBody |
'%小学% |
61657 |
14861 |
646573 |
73936 |
4.9751699 |
19 |
msgBody |
%小% |
132968 |
14439 |
1049424 |
68300 |
4.7302445 |
21 |
msgBody |
%你的孩子已于% |
680 |
14876 |
680 |
76211 |
5.1230842 |
23 |
msgTo |
%1% |
999949 |
2906 |
4909927 |
64126 |
22.066758 |
26 |
msgTo |
%13% |
998059 |
2547 |
4859042 |
45535 |
17.877896 |
28 |
msgTo |
139222% |
3371 |
921 |
20006 |
51709 |
56.144408 |
29 |
msgTo |
139222%9 |
345 |
1750 |
1804 |
219443 |
125.396 |
30 |
msgTo |
13912345678 |
74 |
1672 |
78 |
47817 |
28.598684 |
31 |
msgTo |
1_9_2_0_0_9 |
95 |
1766 |
186 |
48020 |
27.191393 |
32 |
msgTo |
'1%9%2%0%0%9 |
710 |
3891 |
3903 |
53240 |
13.682858 |
可见,随着数据库数据量的增大,所有的测试用例用时均有不同程度的增长。其中,用时增长幅度在3倍~8倍的有14组。此外,还有也有11组增长幅度更大。
用同样的方法制作Lucene对比表:
|
|
|
100万Lucene |
491万Lucene |
| ||
用例 |
搜索字段 |
Lucene搜索词 |
结果集 |
用时 |
结果集 |
用时 |
用时增长 |
3 |
ID |
1234 |
1 |
2047 |
1 |
390 |
0.190523 |
6 |
ID |
111? |
10 |
63 |
10 |
812 |
12.88889 |
8 |
ID |
7*8* |
44493 |
2750 |
44493 |
1641 |
0.596727 |
9 |
ID |
4?5? |
100 |
735 |
100 |
844 |
1.148299 |
11 |
senderName |
林 |
9124 |
281 |
63667 |
594 |
2.113879 |
12 |
senderName |
林雨 |
6 |
78 |
6 |
63 |
0.807692 |
13 |
senderName |
家长 |
103 |
32 |
349 |
157 |
4.90625 |
14 |
msgBody |
林 |
17579 |
219 |
168364 |
500 |
2.283105 |
15 |
msgBody |
林雨 |
46 |
78 |
131 |
172 |
2.205128 |
16 |
msgBody |
家长 |
532790 |
3406 |
3196271 |
31846 |
9.349971 |
18 |
msgBody |
小学 |
61679 |
890 |
646623 |
11298 |
12.69438 |
19 |
msgBody |
小* |
132968 |
32 |
1049424 |
469 |
14.65625 |
21 |
msgBody |
你的孩子已于 |
680 |
2828 |
680 |
38097 |
13.47136 |
28 |
msgTo |
139222* |
3371 |
15 |
20006 |
969 |
64.6 |
29 |
msgTo |
139222*9 |
345 |
78 |
1804 |
32 |
0.410256 |
30 |
msgTo |
13912345678 |
74 |
171 |
78 |
16 |
0.093567 |
31 |
msgTo |
1?9?2?0?0?9 |
95 |
125 |
186 |
438 |
3.504 |
32 |
msgTo |
1*9*2*0*0*9 |
710 |
219 |
3903 |
562 |
2.56621 |
可见,Lucene的搜索用时增幅远较SQL数据库为小,有5个用例甚至出现了负增长。这说明Lucene数据库的搜索用时较不稳定,受除被搜索数据的规模以外的因素影响较大。
五、原因分析
要分析产生上述各种现象的原因,首先要对SQL数据库与Lucene数据库的数据结构及算法实现有一个简单的了解。
(补数据库实现原理)
由于关系型数据库的查找采用顺序遍历表的方法,因此它的查找用时是与表规模和被查找列的数据量成正比的。纵向比较的规律1证实了这一点。
Lucene数据库使用了一个倒排文件索引来提供快速查找。该索引按关键词的字典序进行排序。从而使该索引支持快速的二分查找,相比于海量的文件内容而言,关键词的数量是微不足道的,所以这一步所需时间非常短,在本测试中,它花费的时间是毫秒级的。
Lucene在索引中记录了出现了该关键词的记录号、出现次数、位置等信息。因此,可以在海量的记录集中快速地定位该关键词的位置。从整体的方面来说,Lucene的倒排索引相当于实现了一个Hash表。我们知道,Hash表的查找复杂度是O(1)的,也就是说,Lucene的查找用时几乎与记录集的规模无关。这一点在《100万组数据测试统计表》与《491万组数据测试统计表》的对比中体现得非常明显。
Lucene数据库在索引中的检索速度很快,但每匹配一个记录后,就要进行一次磁盘的读写以获取相关的数据。同时,用Java实现的Luene无论是在程序执行效率上、内存使用效率上还是文件的IO效率上,都要比编译成二进制代码的SQL server要低,这种先天不足使得Lucene在生成结果集的效率较低,纵向比较中的规律2证实了这一点。
六、结论:关系型数据库与Lucene数据库的适用场合
关系型数据库的适用场合
涉及多种类型数据的处理(包括数值、二进制流等)
复杂的查询类型(如组合查询等)
复杂的权限和事务管理。
需要处理结果集中的所有数据(如数据转换)
统计分析。
Lucene数据库的适用场合
对大文本的匹配查找。
对文本的模糊匹配查找。需要匹配度控制的场合
基于词元的匹配
只需处理结果集中的小部分数据(通常是指匹配度最高的数据),并且需要很高效地取出这些数据,如搜索引擎。
可见,两种类型数据库的适用场合差别是相当明显的。在实际应用中,应该根据数据特点和应用需求来决定采用哪一种形式的数据库。
七、心得体会
第一次做实习。有很多新发现,不吐不快!
发现原来我还是更喜欢写代码。写代码会给人带来一种成就感,尤其是写一些有点难度的代码。看着自己的程序或者类库一天一天地强大起来,会很有一种把心爱的宠物养大的感觉;
发现原来开源阵营真的很强大。可以说是应有尽有!我仿佛走进了IT业的“共产主义”时代——按需分配,想用就用!
发现如何善用搜索引擎是一门学问!Leader Bruise老说,我让你好多秒了,但他还总是搜得比我快!膜拜ing~~orz
发现与他人的交流很重要!自己以为做得很好的东西往往在别人看来还有很多改进意见的~而有时自己感觉做不下去了,别人稍微提点一下就会茅塞顿开~能节省很多时间的~
发现在公司,MM很受欢迎!做IT的女生好有皇帝女的感觉啊~~羡慕ing~~(不是妒忌啊~不准说我没品!)
发现我还有很多发现~不过现在累了(午休时间到了!)所以不说啦~欲知后事如何,请听下回分解~~