Oracle 相比 mysql 的优势在哪里 原帖子在这里： https://v2ex.com/t/773654?p=1
目前的项目用的 mysql，支撑几十亿数据没问题（分库分表），偶尔慢 sql 也能优化索引解决，至于复杂查询通过搜索引擎实现，听说 Oracle 超级贵，那 Oracle 具体有什么优势呢，哪些场景下是 oracle only 的？
看完了回复，oracle 能单表支撑几十亿数据还是很强的，因为目前因为分表键导致很多需求无法实现，只能同步一个表用另一个分表键。
我再问下，如果是同时涉及几十个字段的复杂搜索，oracle 可以支持吗，目前用的搜索引擎实现也非常贵。
有一说一，单表支撑几十亿数据，听着有点玄乎，真的假的啊
我的回复：
oracle 的优化器 CBO optimizer 目前应该是所有 RDBMS 里最复杂的（不说是最先进的）。MYSQL 至少在优化器上还处于比较初级的阶段，虽然 MySQL 的目标可能并不希望实现非常复杂的优化器算法。其他一些东西 例如 undo 的实现等等 可能优势并不明显， 这里就不提了。
举一个例子， 都不使用索引的情况下，NO INDEX ! NO INDEX | NO INDEX !
以下数据量是一样的，机器是同一台。 Oracle 11.2.0.4 MySQL 8.0.14
都没有索引的情况下：oracle 使用 0.04 秒， MySQL 等了 10 分钟也没运行完
对于简单的 SQL 而言，差别不会有那么大。对于复杂的 SQL 而言， 能明显体现出优化器的优势。
对于拔高某个技术，没有兴趣。 所以 MYSQL 死忠请勿拍。
Oracle :
SQL> set timing on;
SQL> SELECT c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
5 WHERE s.time_id = t.time_id
6 AND s.cust_id = c.cust_id
7 AND s.channel_id = ch.channel_id
8 AND c.cust_state_province = 'FL'
9 AND ch.channel_desc = 'Direct Sales'
10 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
11 GROUP BY c.cust_city, t.calendar_quarter_desc;
CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Cypress Gardens 2000-01 3545.82
Candler 2000-01 4166.32
Sanibel 2000-02 17908.67
Ocala 2000-02 7081.73
Molino 2000-01 18765.25
Ocala 2000-01 7146.73
Palmdale 2000-02 25829.24
Palmdale 2000-01 37793.44
Molino 2000-02 17144.7
Saint Marks 2000-01 55781.37
Noma 2000-01 33572.55
Evinston 2000-02 62657.21
Candler 2000-02 6493.94
Winter Springs 2000-02 20
Sugarloaf Key 2000-01 12027.66
Saint Marks 2000-02 48858.7
Blountstown 2000-02 38052.58
Sugarloaf Key 2000-02 9659.44
Cypress Gardens 2000-02 4928.93
Evinston 2000-01 53509.69
Blountstown 2000-01 27024.7
Sanibel 2000-01 15870.34
Winter Springs 2000-01 31.46
Noma 2000-02 23903.58
已选择 24 行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1865285285
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 607 | 46132 | 955 (2)| 00:00:12 | | |
| 1 | HASH GROUP BY | | 607 | 46132 | 955 (2)| 00:00:12 | | |
|* 2 | HASH JOIN | | 2337 | 173K| 954 (2)| 00:00:12 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 274 | 4384 | 18 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | TIMES | 274 | 4384 | 18 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 12456 | 729K| 936 (2)| 00:00:12 | | |
| 6 | MERGE JOIN CARTESIAN | | 383 | 14937 | 408 (1)| 00:00:05 | | |
|* 7 | TABLE ACCESS FULL | CHANNELS | 1 | 13 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 383 | 9958 | 405 (1)| 00:00:05 | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 405 (1)| 00:00:05 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 526 (2)| 00:00:07 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES | 918K| 18M| 526 (2)| 00:00:07 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."TIME_ID"="T"."TIME_ID")
4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
9 - filter("C"."CUST_STATE_PROVINCE"='FL')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1726 consistent gets
0 physical reads
0 redo size
1495 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
SQL> select * From v$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
MySQL :
mysql> SELECT c.cust_city,
-> t.calendar_quarter_desc,
-> SUM(s.amount_sold) sales_amount
-> FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
-> WHERE s.time_id = t.time_id
-> AND s.cust_id = c.cust_id
-> AND s.channel_id = ch.channel_id
-> AND c.cust_state_province = 'FL'
-> AND ch.channel_desc = 'Direct Sales'
-> AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
-> GROUP BY c.cust_city, t.calendar_quarter_desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | ch | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using temporary |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1804 | 30.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 55065 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 914584 | 0.10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.14 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from sh.sales;
+----------+
| count(*) |
+----------+
| 918843 |
+----------+
1 row in set (0.96 sec)
Leave a Reply