由CSDN密码泄露窥视各大数据库性能-技术开发专区

由CSDN密码泄露窥视各大数据库性能

作者：心中无码编辑：王玉圆 2011-12-28 00:05 来源：博客

　　聚集函数性能测试

　　这里的测试我们使用网上用来统计密码使用次数TOP 10的语句，大家都知道Oracle没有TOP N，取而代之的是ROWNUM，MySQL也没有TOP N，取而代之的是LIMIT，DM7支持TOP N、ROWNUM和LIMIT三种语法。所有的测试都关闭了结果集重用。

　　ORACLE

　　Oracle没有进行相关的设置，使用了默认配置。

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并没有利用到索引。

　　MySQL

　　MySQL设置了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的测试结果有些失望，可能是我设置的参数不够，希望有经验的同学尝试下，指点下～～。

　　DM7

　　DM7设置了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的复杂查询一直是软肋，可能和插件式的存储引擎设计模式有关系，支持多种存储引擎导致其优化器的设计是比较通用，通用的结果就无法进行精确的优化。

第1页：窥视各大数据库性能——装载数据第2页：窥视各大数据库性能——聚集函数性能

关注我们

由CSDN密码泄露 窥视各大数据库性能

由CSDN密码泄露窥视各大数据库性能