連結：http://blog.itpub.net/28602568/viewspace-2074090/

註釋：

30w+6w+2個100條表 left join where 多對多關係...求count(1) ，mysql資料庫執行4514s，posgresql執行206s，oracle執行45s.
mysql 只有nest loop；
pg、oracle 有hash join 哦....

詳情如下：

錶行數

SQL> SELECT table_name,num_rows FROM USER_TABLES WHERE upper(TABLE_NAME) IN ('BRAND','COLOR_INFO','SHOP_BRAND','ITEM');
TABLE_NAME NUM_ROWS
------------------------------ ----------
shop_brand 62524
color_info 183
brand 143
item 309674
SQL>

Oracle 部分

執行資訊

SQL> set timing on
SELECT count(1) AS s
FROM "item"
LEFT JOIN "color_info"
    ON "color_info"."color_no" = "item"."color_no"
LEFT JOIN "brand"
    ON "brand"."brand_no" = "item"."brand_no"
LEFT JOIN "shop_brand"
    ON "shop_brand"."brand_no" = "item"SQL>   2    3    4    5    6    7    8 ."brand_no"
WHERE 1 = 1; 9

S
----------
900302146

Elapsed: 00:00:45.94
SQL>

執行計劃

Plan hash value: 3456201856
------------------------------------------------------------------------------------------------------
| Id | Operation                | Name              | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 |   185 |       | 10888 (66)| 00:02:11 |
|   1 | SORT AGGREGATE          |                   |     1 |   185 |       |            |          |
|* 2 |   HASH JOIN RIGHT OUTER |                   |   915M|   157G| 2992K| 10888 (66)| 00:02:11 |
|   3 |    INDEX FAST FULL SCAN | A                 | 62524 | 2259K|       |   295   (1)| 00:00:04 |
|* 4 |    HASH JOIN RIGHT OUTER |                   |   309K|    43M|       | 1013   (1)| 00:00:13 |
|   5 |     INDEX FAST FULL SCAN | UK_COLOR_NO       |   183 | 6771 |       |     2   (0)| 00:00:01 |
|* 6 |     HASH JOIN RIGHT OUTER|                   |   309K|    32M|       | 1008   (1)| 00:00:13 |
|   7 |      INDEX FULL SCAN     | UK_BRAND_NO       |   143 | 5291 |       |     1   (0)| 00:00:01 |
|   8 |      INDEX FAST FULL SCAN| IX_BRAND_COLOR_NO |   309K|    21M|       | 1005   (1)| 00:00:13 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("shop_brand"."brand_no"(+)="item"."brand_no")
   4 - access("color_info"."color_no"(+)="item"."color_no")

6 - access("brand"."brand_no"(+)="item"."brand_no")

PostgreSQL 部分

執行資訊:

postgres=# SELECT count(1) AS s
postgres-# FROM item
postgres-# LEFT JOIN color_info ON color_info.color_no = item.color_no
postgres-# LEFT JOIN brand ON brand.brand_no = item.brand_no
postgres-# LEFT JOIN shop_brand ON shop_brand.brand_no = item.brand_no
postgres-# WHERE 1 = 1;
s
-----------

900302146

Time: 206415.142 ms
postgres=#

執行計劃：

postgres=# EXPLAIN
SELECT count(1) AS s
FROM item
LEFT JOIN color_info ON color_info.color_no = item.color_no
LEFT JOIN brand ON brand.brand_no = item.brand_no
LEFT JOIN shop_brand   ON shop_brand.brand_no = item.brand_no
WHERE 1 = 1;
+-------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                |
+-------------------------------------------------------------------------------------------+
| Aggregate (cost=13514243.57..13514243.58 rows=1 width=0)                                 |
|   -> Hash Left Join (cost=2602.12..11234175.66 rows=912027167 width=0)                  |
|         Hash Cond: (item.brand_no = shop_brand.brand_no)                                  |
|         -> Hash Left Join (cost=13.33..33128.01 rows=328178 width=19)                   |
|               Hash Cond: (item.brand_no = brand.brand_no)                                 |
|               -> Hash Left Join (cost=7.12..28609.34 rows=328178 width=19)              |
|                     Hash Cond: (item.color_no = color_info.color_no)                      |
|                     -> Seq Scan on item (cost=0.00..24089.78 rows=328178 width=38)      |
|                     -> Hash (cost=4.83..4.83 rows=183 width=19)                         |
|                           -> Seq Scan on color_info (cost=0.00..4.83 rows=183 width=19) |
|               -> Hash (cost=4.43..4.43 rows=143 width=19)                               |
|                     -> Seq Scan on brand (cost=0.00..4.43 rows=143 width=19)            |
|         -> Hash (cost=1807.24..1807.24 rows=62524 width=19)                             |
|               -> Seq Scan on shop_brand (cost=0.00..1807.24 rows=62524 width=19)        |
+-------------------------------------------------------------------------------------------+
14 rows in set

postgres=#

MySQL 部分

執行資訊

mysql> set profiling=1;

mysql> show profiles; #檢視sql執行時間
---------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                                                                                                              |
+----------+-------------+-----------------------------------------------------------       |
|        6 | 4514.459468 | SELECT count(1) AS s
FROM item
LEFT JOIN color_info ON color_info.color_no = item.color_no
LEFT JOIN brand ON brand.brand_no = item.brand_no
LEFT JOIN shop_brand   ON shop_brand.brand_no = item.brand_no
WHERE 1 = 1 |
+----------+-------------+----------------------------------------------------------------

mysql>

執行計劃：

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在資料庫分類目錄。將固定連線加入收藏夾。

Oracle/MySQL/PostgreSQL 簡單查詢的效能對比

相關文章