CSDN密码库窥视各大数据库性能
CSDN密码库窥视各大数据库性能
2011-12-26 17:30 by 心中无码, 1076 visits, 收藏 , 编辑
很多同学一直抱怨手头木有一个真实的数据库，无法进行数据库性能试验，CSDN得知了同学们的苦恼，立刻开放了部分数据库，供同学们学习、参考，对CSDN这种大公无私、舍己为人的行为，有关部门对此进行了高度的赞扬，下面就带领同学们来进行一个小小的性能对比测试。
实验选择了三个数据库： Oracle11g、MySQL5.1以及DM7 。Oracle作为商业数据库大哥大的代表，MySQL作为开源数据库的代表，DM7很多同学估计就不清楚了（达梦数据库），暂时作为国产数据库的代表吧（同学们轻拍^_^）。
测试环境
OS WINDOWS XP SP3 CPU Intel i3 530 @2.93GHz MEMORY 4G ORACLE 11.1.0.6.0 MySQL 5.1.30 DM V7
导入数据库性能测试
这次CSDN泄漏的密码库规模说大不大，说小不小，600多万行（CSDN的用户数霸气外漏，博客园有多少呢？），同学们测试的福音啊。还好我第一时间download了，CSDN的处理还是比较迅速的，很快网上的下载链接就失效了，赞一个。这个密码库是一个文本文件，200多M，用UE打开还要等一会...每行的格式如下：
用户名 # 密码 # 邮箱
三个字段间的间隔是’空格#空格’,为了方便导入数据库，我们使用UE将其分割符全部替换为#。
肿么导入数据库呢？上面选的三个数据库都具有快速导入的工具， Oralce提供了sqlldr工具，MySQL提供了load data命令，DM7提供了dmfldr工具 ，dmfldr和sqlldr工具感觉使用起来比较相似，MySQL提供的load data作为一种SQL命令。
建表
导入之前我们需要创建一个空表，Oracle和DM7的建表语句如下：
CREATE TABLE CSDN(ID VARCHAR (256), PWD VARCHAR (256), EMAIL VARCHAR (256));
MySQL的建表语句我们 指定存储引擎为INNODB ：
CREATE TABLE CSDN(ID VARCHAR (256), PWD VARCHAR (256), EMAIL VARCHAR (256)) ENGINE=INNODB;
初始建表语句这里不指定任何索引。
装载数据 ORACLE
首先创建控制文件ora_csdn.ctrl，内容如下：
UNRECOVERABLE
LOAD DATA
INFILE 'D:\CSDN\HdhCmsTestcsdn.net.sql'
INSERT into table CSDN
fields terminated by '#'
(
ID,
PWD,
)
然后使用sqlldr工具：
sqlldr userid=SYSMAN /SYSDBA control=D: /CSDN/ora_csdn .ctrl DIRECT=TRUE log=resulthis.out
输出文件为resulthis.out:
SQL*Loader: Release 11.1.0.6.0 - Production on 星期六 12月 24 16:01:06 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
控制文件: D: /CSDN/ora_csdn .ctrl
数据文件: D:\CSDN\HdhCmsTestcsdn.net.sql
错误文件: D: /CSDN/www .csdn.net.bad
废弃文件: 未作指定
(可废弃所有记录)
要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续: 未作指定
所用路径: 直接
加载是 UNRECOVERABLE;产生无效的恢复操作。
表 CSDN,已加载从每个逻辑记录
插入选项对此表 INSERT 生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * # CHARACTER
PWD NEXT * # CHARACTER
EMAIL NEXT * # CHARACTER
表 CSDN:
6428632 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
在直接路径中没有使用绑定数组大小。
列数组 行数: 5000
流缓冲区字节数: 256000
读取 缓冲区字节数: 1048576
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 6428632
拒绝的逻辑记录总数: 0
废弃的逻辑记录总数: 0
由 SQL*Loader 主线程加载的流缓冲区总数: 1497
由 SQL*Loader 加载线程加载的流缓冲区总数: 0
从 星期六 12月 24 16:01:06 2011 开始运行
在 星期六 12月 24 16:01:13 2011 处运行结束
经过时间为: 00: 00: 06.93
CPU 时间为: 00: 00: 04.65
从输出文件可以看出时间为 00：00：06.93 ，将近 每秒钟100W 行的导入效率，很不错。
MySQL
MySQL导入比较简单，不需要CTRL文件，直接在mysql命令行执行即可。
mysql> LOAD DATA local infile "D:/CSDN/HdhCmsTestcsdn.net.sql" into table CSDN fields
terminated by '#' ;
Query OK, 6428632 rows affected, 14275 warnings (1 min 51.95 sec)
Records: 6428632 Deleted: 0 Skipped: 0 Warnings: 14275
从打印信息，可以看出， 耗时接近2分钟 ，性能较差。
DM7DM7提供了dmfldr.exe工具，需要ctrl文件， 用法和oracle基本类似 ，有些细微的差别，比如指定输出导入到输出文件，还是会在CMD中打印信息。
D:\SRC\DM7\Release>dmfldr.exe USERID=SYSDBA /SYSDBA CONTROL='D: /CSDN/dm_csdn .ctrl
' DIRECT=TRUE LOG=' OUT.LOG'
输出文件为OUT.LOG:
dmfldr:
Copyright (c) 2011, 2015, Dameng. All rights reserved.
控制文件：D: /CSDN/dm_csdn .ctrl
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:是
是否插入自增列:否
数据是否已按照聚集索引排序:否
字符集:GBK
数据文件共1个:
D:\CSDN\HdhCmsTestcsdn.net.sql
错误文件:fldr.bad
目标表:CSDN
列名 终止 包装数据类型
ID WHT CHARACTER
PWD WHT CHARACTER
EMAIL WHT CHARACTER
目标表 CSDN :
6428632行 加载成功。
由于数据格式错误,0行 丢弃。
由于数据错误,0行 没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:6428632
拒绝的逻辑记录总数:0
用时:15238.166(ms)
查看输出文件，用时15s，比MySQL好很多，比Oralce差一点。
聚集函数性能测试
这里的测试我们使用网上用来统计密码使用次数TOP 10的语句，大家都知道Oracle没有TOP N，取而代之的是ROWNUM，MySQL也没有TOP N，取而代之的是LIMIT，DM7支持TOP N、ROWNUM和LIMIT三种语法。所有的测试都 关闭了结果集重用 。
ORACLEOracle没有进行相关的设置，使用了默认配置。
SQL> SELECT CNT, PWD FROM ( SELECT COUNT (PWD) as cnt, PWD FROM CSDN GROUP BY PWD
ORDER BY COUNT (PWD) DESC ) WHERE ROWNUM < 11;
CNT PWD
---------- --------------------
235017 123456789
212759 12345678
76348 11111111
46053 dearbook
34953 00000000
20010 123123123
17793 1234567890
15033 88888888
6995 111111111
5965 147258369
已选择10行。
已用时间: 00: 00: 11.09
ORACLE用时11S，我们来看下Oracle的执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 3269342783
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | 11354 (5)| 00:02:17 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6856K| 935M| 11354 (5)| 00:02:17 |
|* 3 | SORT ORDER BY STOPKEY| | 6856K| 850M| 11354 (5)| 00:02:17 |
| 4 | HASH GROUP BY | | 6856K| 850M| 11354 (5)| 00:02:17 |
| 5 | TABLE ACCESS FULL | CSDN | 6856K| 850M| 10890 (1)| 00:02:11 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
133 recursive calls
0 db block gets
39767 consistent gets
56161 physical reads
0 redo size
684 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to /from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
执行计划可以看出来，由于没有索引，首先进行了全表扫描，然后根据PWD进行HASH，按照COUNT进行SORT，构建一个临时的VIEW，然后是过滤rownum<11。
下面对PWD列创建索引，看下Oracle是否能利用这个索引。
SQL> CREATE INDEX I1 ON CSDN(PWD);
索引已创建。
已用时间: 00: 00: 27.17
查看下执行计划：
SQL> SELECT CNT, PWD FROM ( SELECT COUNT (PWD) as cnt, PWD FROM CSDN GROUP BY PWD
ORDER BY COUNT (PWD) DESC ) WHERE ROWNUM < 11;
已选择10行。
已用时间: 00: 00: 10.31
执行计划
----------------------------------------------------------
Plan hash value: 3269342783
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1430 | 11354 (5)| 00:02:17 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6856K| 935M| 11354 (5)| 00:02:17 |
|* 3 | SORT ORDER BY STOPKEY| | 6856K| 850M| 11354 (5)| 00:02:17 |
| 4 | HASH GROUP BY | | 6856K| 850M| 11354 (5)| 00:02:17 |
| 5 | TABLE ACCESS FULL | CSDN | 6856K| 850M| 10890 (1)| 00:02:11 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
133 recursive calls
0 db block gets
39776 consistent gets
32748 physical reads
0 redo size
684 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to / from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
可以看到时间并没有提升， ORACLE并没有利用到索引 。 MySQLMySQL设置了INNODB的BUFFER大小为700M，执行如下语句：
SELECT PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD) DESC LIMIT 10;
长时间木有相应啊，打了俩小时羽毛球回来，还是没执行完....
设置了SROT BUFFER后仍无效果，尝试创建索引，发现很慢，慢到简直就是挑战我的极限，so close it。INNODB的测试结果有些失望，可能是我设置的参数不够，希望有经验的同学尝试下，指点下～～。
DM7DM7设置了BUFFER大小也为700M。
SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD) DESC ;
PWD COUNT (PWD)
1 123456789 235029
2 12345678 212761
3 11111111 76348
4 dearbook 46053
5 00000000 34953
6 123123123 20010
7 1234567890 17794
8 88888888 15033
9 111111111 6995
10 147258369 5966
10 rows got
time used: 12186.676(ms) clock tick:1383768307. Execute id is 1.
执行时间为12S，第二次和第三次执行时，时间会缩短为7S左右，因为数据全部缓存在buffer中，没有I/O。有同学可能发现和oracle比较有些count计算不一致，这是因为存在一些密码结尾为空格的数据，DM的处理在这里类似SQL SERVER了...
查看下执行计划：
SQL>EXPLAIN SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (
PWD) DESC ;
EXPLAIN SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD)
DESC ;
#NSET2: [0, 0, 0]
#PRJT2: [0, 0, 0]; exp_num(2), is_atom( FALSE )
#SORT3: [0, 0, 0]; key_num(1), is_distinct( FALSE )
#HAGR2: [0, 0, 0]; grp_num(1), sfun_num(2)
#CSCN2: [2721, 6428632, 0]; INDEX33555437(CSDN)
time used: 0.732(ms) clock tick:2138943. Execute id is 0.
可以看出，DM7也首先进行了全表扫描SCAN，然后针对GROUP BY执行了HASH AGR，针对ORDER BY执行了SORT节点。
DM7同时支持查看每个执行节点的执行时间：
SQL> select n. name , time_used, n_enter from v$sql_node_name n, v$sql_node_history
h where n.type$ = h.type$ and exec_id = 1 order by seq_no;
select n. name , time_used, n_enter from v$sql_node_name n, v$sql_node_history h w
here n.type$ = h.type$ and exec_id = 1 order by seq_no;
NAME TIME_USED N_ENTER
1 DLCK 2 2
2 NSET2 71 3
3 PRJT2 2 4
4 SORT3 470170 4038
5 HAGR2 6467980 10466
6 CSCN2 5222702 6430
6 rows got
time used: 15.969(ms) clock tick:46813291. Execute id is 4.
这个比较方便了，可以知道性能瓶颈是在哪个执行节点，如果实在CSCN，那就是I/O的问题了，如果是HAGR或者是SORT，那可能就需要调整下相应的BUFFER了。
下面创建索引试下：
CREATE INDEX I1 ON CSDN(PWD);
time used: 25562.295(ms) clock tick:1959731992. Execute id is 1.
首先看下执行计划是否改变，如果没变的话，我们就不需要再去执行看时间了：
SQL>EXPLAIN SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (
PWD) DESC ;
EXPLAIN SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD)
DESC ;
#NSET2: [0, 0, 0]
#PRJT2: [0, 0, 0]; exp_num(2), is_atom( FALSE )
#SORT3: [0, 0, 0]; key_num(1), is_distinct( FALSE )
#SAGR2: [0, 0, 0]; grp_num(1), sfun_num(2)
#SSCN: [928, 6428632, 0]; I1(CSDN)
time used: 0.459(ms) clock tick:1340375. Execute id is 0.
执行计划发生了变化，可以看到SSCN里面是使用了I1索引，同时HAGR也改为了SAGR，看下这个新的执行计划是否是最好的。
SQL> SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD) DES
C;
SELECT TOP 10 PWD, COUNT (PWD) FROM CSDN GROUP BY PWD ORDER BY COUNT (PWD) DESC ;
PWD COUNT (PWD)
1 123456789 235029
2 12345678 212761
3 11111111 76348
4 dearbook 46053
5 00000000 34953
6 123123123 20010
7 1234567890 17794
8 88888888 15033
9 111111111 6995
10 147258369 5966
10 rows got
time used: 1897.215(ms) clock tick:1269554324. Execute id is 4.
时间缩短为1897.215(ms)， 可以看到DM7在执行计划的选择上更加精确一点。
小结
通过上面简单的测试，可以看出在文本数据导入方面Oracle比较快，DM7紧随其后，MySQL就略显不足了。在聚集函数和排序处理方面，DM7的计划选择更加精确一点，Oracle表现中规中矩，MySQL的复杂查询一直是软肋，可能和插件式的存储引擎设计模式有关系，支持多种存储引擎导致其优化器的设计是比较通用，通用的结果就无法进行精确的优化。
经过@CFR同学的提醒，进行了这个比较粗略的测试，从库的下载到导入到测试，还是花费了一点时间，发这个博文很纠结，怕同学说我是水文，最后声明下吧，以上数据均是实际测试结果。最后再次感谢下CSDN^_^
踏着落叶，追寻着我的梦想。转载请注明出处
作者： Leo_wl
出处： http://HdhCmsTestcnblogs测试数据/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于CSDN密码库窥视各大数据库性能的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did50092