SQL数据库与Lucene数据库性能测试报告

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~~（不是妒忌啊~不准说我没品！）

发现我还有很多发现~不过现在累了（午休时间到了！）所以不说啦~欲知后事如何，请听下回分解~~