SQL数据库与Lucene数据库性能测试报告
<spanlang=en-us style="font-size: 14pt;">一、 测试目的
<spanstyle='font-family:宋体'>本测试试图对用相同数据制作的SQL<spanstyle='font-family:宋体'>数据库与Lucene<spanstyle='font-family:宋体'>数据库对语义相同的查询语句的性能作一个简单的比较与分析,以找出各自的优缺点,并讨论其各自的适用场合。
<spanlang=en-us>
<spanlang=en-us style="font-size: 14pt;">二、 测试环境
CPU<spanstyle='font-family:宋体'>:AMD Atholon64 3200+<spanstyle='font-family:宋体'>,<span lang=en-us>2.0G
<spanstyle='font-family:宋体'>内存:896MB
OS<spanstyle='font-family:宋体'>:Windows 2003
Jre<spanstyle='font-family:宋体'>版本:<span lang=en-us>1.6.0_01
Jdk<spanstyle='font-family:宋体'>版本:<span lang=en-us>1.5.0_06
Lucene<spanstyle='font-family:宋体'>版本:<span lang=en-us>2.2.0
JDBC<spanstyle='font-family:宋体'>版本:Microsoft SQL Server 2005JDBC Driver 1.2
<spanlang=en-us style="font-size: 14pt;">三、 测试数据设计
本次测试采用的数据来自校讯通的真实运营数据,原始数据是<spanlang=en-us>SQL数据库的一张数据表,该表共有<spanlang=en-us>33个字段,各字段名称及类型描述如下:
其中,本测试选取了<spanlang=en-us>ID、senderName<spanstyle='font-family:宋体'>、msgBody<spanstyle='font-family:宋体'>、msgTo<spanstyle='font-family:宋体'>四列来测试。ID<spanstyle='font-family:宋体'>代表了已做索引的int<spanstyle='font-family:宋体'>型字段;senderName<spanstyle='font-family:宋体'>代表varchar<spanstyle='font-family:宋体'>型的短文本字段;msgBody<spanstyle='font-family:宋体'>代表nvarchar<spanstyle='font-family:宋体'>型长文本字段;msgTo<spanstyle='font-family:宋体'>代表保存为varchar<spanstyle='font-family:宋体'>型的数值字段。
原始数据共有<spanlang=en-us>5623689组,占用约<spanlang=en-us>2GB的硬盘空间。在本测试中,分别选取其中前<spanlang=en-us>100万组数据和前<spanlang=en-us>491万组数据来参与测试。相同数据量的<spanlang=en-us>Lucene索引使用自写的<spanlang=en-us>RDB2Lucene Java包生成。对所有的列都进行了索引化和词元化。
设计测试数据时,考虑到以下几个方面的对比:
<spanlang=en-us style="font-family: Wingdings;">ü 不同字段的查询的对比;
<spanlang=en-us style="font-family: Wingdings;">ü 不同结果集规模的对比;
<spanlang=en-us style="font-family: Wingdings;">ü 不同查询数据规模的对比;
<spanlang=en-us style="font-family: Wingdings;">ü 同一项测试连续进行多次的前后效率对比
具体测试数据及测试结果见以下两个统计表:
100<spanstyle='font-family:宋体'>万组数据测试统计表:
|
| SQL | Lucene |
| ||||
<span style="font-family: 宋体;">用例 | 搜索字段 | 搜索词 | 结果集 | 用时 | 搜索词 | 结果集 | 用时 | 结果集 |
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 | %<span style="font-family: 宋体;">林% | 9124 | 1141 | 林 | 9124 | 281 | 相同 |
12 | senderName | %<span style="font-family: 宋体;">林雨% | 6 | 1141 | 林雨 | 6 | 78 | 相同 |
13 | senderName | %<span style="font-family: 宋体;">家长%' | 103 | 1141 | 家长 | 103 | 32 | 相同 |
14 | msgBody | %<span style="font-family: 宋体;">林%' | 17579 | 14767 | 林 | 17579 | 219 | 相同 |
15 | msgBody | %<span style="font-family: 宋体;">林雨% | 46 | 14595 | 林雨 | 46 | 78 | 相同 |
16 | msgBody | %<span style="font-family: 宋体;">家长% | 532790 | 10798 | 家长 | 532790 | 3406 | 相同 |
17 | msgBody | %<span style="font-family: 宋体;">家长% | 532790 | 10501 | 家长 | 532790 | 375 | 相同 |
18 | msgBody | '%<span style="font-family: 宋体;">小学% | 61657 | 14861 | 小学 | 61679 | 890 | 不同 |
19 | msgBody | %<span style="font-family: 宋体;">小% | 132968 | 14439 | 小* | 132968 | 32 | 相同 |
20 | msgBody | 小%孩<span lang="EN-US">% | 3666 | 1359 | 小*孩<span lang="EN-US">* | 0 | 16 | 不同 |
21 | msgBody | %<span style="font-family: 宋体;">你的孩子已于% | 680 | 14876 | 你的孩子已于 | 680 | 2828 | 相同 |
22 | msgBody | %<span style="font-family: 宋体;">小% | 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 | 相同 |
<spanlang=en-us style="font-size: 14pt;">四、 对测试数据的分析
可以从两个方向来分析这两个数据表
<spanlang=en-us style="font-family: Wingdings;">Ø 纵向比较
通过对比不同测试数据的用时,我们发现以下规律:
<spanlang=en-us>1) <spanlang=en-us>SQL数据库的搜索用时与被搜索字段密切相关,返回结果集的大小对搜索用时的影响相对较小,对大数据量字段的查询会造成性能急剧下降。
<spanstyle='font-family:宋体'>在数据量为100<spanstyle='font-family:宋体'>万条记录的统计表中,对ID<spanstyle='font-family:宋体'>列的查询时间基本为1.2<spanstyle='font-family:宋体'>秒~1.4<spanstyle='font-family:宋体'>秒(例外是数据1<spanstyle='font-family:宋体'>);对senderName<spanstyle='font-family:宋体'>列的查询时间基本为1.1<spanstyle='font-family:宋体'>秒~1.2<spanstyle='font-family:宋体'>秒;对msgBody<spanstyle='font-family:宋体'>列的查询时间基本为14<spanstyle='font-family:宋体'>秒(例外是数据20<spanstyle='font-family:宋体'>),对msgTo<spanstyle='font-family:宋体'>的查询时间基本为1.7<spanstyle='font-family:宋体'>秒~2.4<spanstyle='font-family:宋体'>秒
<spanstyle='font-family:宋体'>在数据量为491<spanstyle='font-family:宋体'>万条记录的统计表中,对ID<spanstyle='font-family:宋体'>列的查询时间基本为4.5<spanstyle='font-family:宋体'>秒~6<spanstyle='font-family:宋体'>秒;对senderName<spanstyle='font-family:宋体'>列的查询时间基本为50<spanstyle='font-family:宋体'>秒;对msgBody<spanstyle='font-family:宋体'>列的查询时间基本为70<spanstyle='font-family:宋体'>秒~80<spanstyle='font-family:宋体'>秒,对msgTo<spanstyle='font-family:宋体'>的查询时间基本为47<spanstyle='font-family:宋体'>秒~50<spanstyle='font-family:宋体'>秒(例外是数据35<spanstyle='font-family:宋体'>)
<spanlang=en-us>2) <spanlang=en-us>Lucene索引的搜索用时与返回结果集的大小密切相关,而与被查询列关系不大。
将<spanlang=en-us>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 | |
<brclear=all style="page-break-before: auto;"><brclear=all style="page-break-before: auto;">
可见,搜索用时与结果集的大小密切相关,用时有明显地随结果集增大而增多(虽然不是绝对的)特别地,当结果集进一步加大时,搜索用时可能会急剧增加而使程序无法终止,这一点在<spanlang=en-us>491万数据组的测试中非常常见。也就是说,当数据库规模增长到<spanlang=en-us>490万条记录,<span lang=en-us>2G数据时,获取全部结果集的做法已变得不太现实。
<spanlang=en-us>3) <spanstyle='font-family:宋体'>当连续测试同一组数据时,SQL<spanstyle='font-family:宋体'>数据库和Lucene<spanstyle='font-family:宋体'>数据库的用时都会比第一次测试用时要少。相关的测试数据在100<spanstyle='font-family:宋体'>万数据组是:
|
| SQL | Lucene | ||||
<span style="font-size: 12pt; font-family: 宋体;">用例 | 搜索字段 | 搜索词 | 结果集 | 用时 | 搜索词 | 结果集 | 用时 |
16 | msgBody | %<span style="font-family: 宋体;">家长% | 532790 | 10798 | 家长 | 532790 | 3406 |
17 | msgBody | %<span style="font-family: 宋体;">家长% | 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<spanstyle='font-family:宋体'>万数据组是:
|
| SQL | Lucene | ||||
<span style="font-size: 12pt; font-family: 宋体;">用例 | 搜索字段 | 搜索词 | 结果集 | 用时 | 搜索词 | 结果集 | 用时 |
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 |
|
|
|
相比之下,重复测试带来的性能提升在<spanlang=en-us>Lucene表现得更为明显。
<spanlang=en-us style="font-family: Wingdings;">Ø 横向比较
我们现在逐条数据地比较<spanlang=en-us>SQL数据库与Lucene数据库的用时差异,可以发现,当返回结果集不是太大(少于<spanlang=en-us>100万组)的情况下,一般来说Lucene的表现是比<spanlang=en-us>SQL要好的。尤其是在SQL被查询列没有做索引,而该查询列刚好又是长文本列的时候。用时有时可以相关上百倍。但当结果集比较大时,<spanlang=en-us>Lucene用时会急剧上升,有时甚至无法终止。此时,程序占用物理内存大约在<span lang=en-us>700M上下,占用虚拟内存大约在<span lang=en-us>1G上下,CPU占用一开始很高,但约两秒后下降至几可忽略,硬盘灯狂闪,但任务管理器显示<spanlang=en-us>IO并无太大变化,所以怀疑是内存抖动造成频繁调页,使得系统性能急剧下降。
<spanlang=en-us style="font-family: Wingdings;">Ø 表间比较
作<spanlang=en-us>SQL对比表,选出两表中搜索词相同的项,并使之一一对应(相同项仅保留第一次测试的结果),得:
|
|
| 100万SQL | 491万SQL |
| ||
<span style="font-family: 宋体;">用例 | 搜索字段 | 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 | %<span style="font-family: 宋体;">林% | 9124 | 1141 | 63667 | 52552 | 46.057844 |
12 | senderName | %<span style="font-family: 宋体;">林雨% | 6 | 1141 | 5 | 45318 | 39.717791 |
13 | senderName | %<span style="font-family: 宋体;">家长%' | 103 | 1141 | 349 | 49880 | 43.716039 |
14 | msgBody | %<span style="font-family: 宋体;">林%' | 17579 | 14767 | 168364 | 72572 | 4.9144715 |
15 | msgBody | %<span style="font-family: 宋体;">林雨% | 46 | 14595 | 131 | 73759 | 5.053717 |
16 | msgBody | %<span style="font-family: 宋体;">家长% | 532790 | 10798 | 3196271 | 91398 | 8.4643452 |
18 | msgBody | '%<span style="font-family: 宋体;">小学% | 61657 | 14861 | 646573 | 73936 | 4.9751699 |
19 | msgBody | %<span style="font-family: 宋体;">小% | 132968 | 14439 | 1049424 | 68300 | 4.7302445 |
21 | msgBody | %<span style="font-family: 宋体;">你的孩子已于% | 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 |
可见,随着数据库数据量的增大,所有的测试用例用时均有不同程度的增长。其中,用时增长幅度在<spanlang=en-us>3倍~8倍的有14组。此外,还有也有<spanlang=en-us>11组增长幅度更大。
用同样的方法制作<spanlang=en-us>Lucene对比表:
|
|
| 100万Lucene | 491万Lucene |
| ||
<span style="font-size: 12pt; font-family: 宋体;">用例 | 搜索字段 | 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 |
可见,<spanlang=en-us>Lucene的搜索用时增幅远较SQL数据库为小,有<spanlang=en-us>5个用例甚至出现了负增长。这说明Lucene数据库的搜索用时较不稳定,受除被搜索数据的规模以外的因素影响较大。
<spanlang=en-us style="font-size: 14pt; font-family: 宋体-方正超大字符集;">五、<spanstyle='font:7.0pt times="" new="" roman=""> 原因分析
要分析产生上述各种现象的原因,首先要对<spanlang=en-us>SQL数据库与Lucene数据库的数据结构及算法实现有一个简单的了解。
(补数据库实现原理<spanlang=en-us>)
由于关系型数据库的查找采用顺序遍历表的方法,因此它的查找用时是与表规模和被查找列的数据量成正比的。纵向比较的规律<spanlang=en-us>1证实了这一点。
Lucene数据库使用了一个倒排文件索引来提供快速查找。该索引按关键词的字典序进行排序。从而使该索引支持快速的二分查找,相比于海量的文件内容而言,关键词的数量是微不足道的,所以这一步所需时间非常短,在本测试中,它花费的时间是毫秒级的。
Lucene在索引中记录了出现了该关键词的记录号、出现次数、位置等信息。因此,可以在海量的记录集中快速地定位该关键词的位置。从整体的方面来说,<spanlang=en-us>Lucene的倒排索引相当于实现了一个Hash表。我们知道,<spanlang=en-us>Hash表的查找复杂度是O(1)的,也就是说,<spanlang=en-us>Lucene的查找用时几乎与记录集的规模无关。这一点在《100万组数据测试统计表》与《<spanlang=en-us>491万组数据测试统计表》的对比中体现得非常明显。
Lucene数据库在索引中的检索速度很快,但每匹配一个记录后,就要进行一次磁盘的读写以获取相关的数据。同时,用<spanlang=en-us>Java实现的Luene无论是在程序执行效率上、内存使用效率上还是文件的<spanlang=en-us>IO效率上,都要比编译成二进制代码的SQL server要低,这种先天不足使得<spanlang=en-us>Lucene在生成结果集的效率较低,纵向比较中的规律2证实了这一点。
<spanlang=en-us style="font-size: 14pt; font-family: 宋体-方正超大字符集;">六、<spanstyle='font:7.0pt times="" new="" roman=""> 结论:关系型数据库与Lucene数据库的适用场合
关系型数据库的适用场合
<spanlang=en-us style="font-family: Wingdings;"> 涉及多种类型数据的处理(包括数值、二进制流等)
<spanlang=en-us style="font-family: Wingdings;"> 复杂的查询类型(如组合查询等)
<spanlang=en-us style="font-family: Wingdings;"> 复杂的权限和事务管理。
<spanlang=en-us style="font-family: Wingdings;"> 需要处理结果集中的所有数据(如数据转换)
<spanlang=en-us style="font-family: Wingdings;"> 统计分析。
Lucene数据库的适用场合
<spanlang=en-us style="font-family: Wingdings;"> 对大文本的匹配查找。
<spanlang=en-us style="font-family: Wingdings;"> 对文本的模糊匹配查找。需要匹配度控制的场合
<spanlang=en-us style="font-family: Wingdings;"> 基于词元的匹配
<spanlang=en-us style="font-family: Wingdings;"> 只需处理结果集中的小部分数据(通常是指匹配度最高的数据),并且需要很高效地取出这些数据,如搜索引擎。
可见,两种类型数据库的适用场合差别是相当明显的。在实际应用中,应该根据数据特点和应用需求来决定采用哪一种形式的数据库。