Oracle/MySQL/PostgreSQL 简单查询的性能对比

最新推荐文章于 2024-05-18 08:00:00 发布

cixiaobi8104

最新推荐文章于 2024-05-18 08:00:00 发布

阅读量338

点赞数

链接： http://blog.itpub.net/28602568/viewspace-2074090/

标题： Oracle/MySQL/PostgreSQL 简单查询的性能对比

注释：

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>

执行计划：