對含distinct操作的SQL的優化

space6212發表於2019-02-15

今天幫同事優化了一條含distinct操作的SQL，因為這個SQL在資料倉儲環境中非常經典，因此有比較拿出來說一說。

[@more@]原SQL：

sys@dwrac1> select count(distinct a.ip)
2 from (select t.ip
3 from creater_user.popt_total_login_month_his t
4 where t.data_desc = '2010-02-01') a, (select t.ip
5 from creater_user.popt_total_login_month_his t
6 where t.data_desc between
7 '2010-03-01' and
8 '2011-12-31') b
9 where a.ip = b.ip;

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | | 1032K (4)| 04:00:50 | | |
| 1 | SORT GROUP BY | | 1 | 40 | | | | | |
|* 2 | HASH JOIN | | 3533K| 134M| 107M| 1032K (4)| 04:00:50 | | |
| 3 | PARTITION RANGE ITERATOR| | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
|* 4 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
| 5 | PARTITION RANGE SINGLE | | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 6 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------------------

從執行計劃可以看到，Oracle先掃描兩個表的部分分割槽，在用hash連線，最後做distinct操作。
在這個案例中，這樣是有很大問題的，因為兩個結果集的IP重複度都比較高，根據a.ip=b.ip關聯後結果集劇烈膨脹，導致需要distinct的記錄集太大。
知道原因，那麼就可以想辦法避免了。我們的目標很簡單，就是讓關聯後的記錄集減少。
這個例子很明顯就可以先分別對兩個結果集做排重後再關聯，於是得到第一個優化後SQL：

sys@dwrac1> select count(distinct a.ip)
2 from (select distinct t.ip
3 from creater_user.popt_total_login_month_his t
4 where t.data_desc = '2010-02-01') a, (select distinct t.ip
5 from creater_user.popt_total_login_month_his t
6 where t.data_desc between
7 '2010-03-01' and
8 '2011-12-31') b
9 where a.ip = b.ip;

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | | 1032K (4)| 04:00:50 | | |
| 1 | SORT GROUP BY | | 1 | 40 | | | | | |
|* 2 | HASH JOIN | | 3533K| 134M| 107M| 1032K (4)| 04:00:50 | | |
| 3 | PARTITION RANGE ITERATOR| | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
|* 4 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
| 5 | PARTITION RANGE SINGLE | | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 6 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------------------

從執行計劃看到，雖然我們改寫了SQL，但是執行計劃沒有改變，這是因為CBO“聰明”地把兩個子查詢merge進去了。
我們可以用no_merge禁止子查詢的merge操作，於是得到第二次優化的SQL：

sys@dwrac1> select count(distinct a.ip)
2 from (select /*+ no_merge */distinct t.ip
3 from creater_user.popt_total_login_month_his t
4 where t.data_desc = '2010-02-01') a, (select /*+ no_merge */distinct t.ip
5 from creater_user.popt_total_login_month_his t
6 where t.data_desc between
7 '2010-03-01' and
8 '2011-12-31') b
9 where a.ip = b.ip;

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1087K (4)| 04:13:49 | | |
| 1 | SORT GROUP BY | | 1 | 26 | | | | | |
| 2 | MERGE JOIN | | 3533K| 87M| | 1087K (4)| 04:13:49 | | |
| 3 | SORT JOIN | | 14M| 175M| | 98667 (4)| 00:23:02 | | |
| 4 | VIEW | | 14M| 175M| | 98667 (4)| 00:23:02 | | |
| 5 | HASH UNIQUE | | 14M| 269M| 378M| 98667 (4)| 00:23:02 | | |
| 6 | PARTITION RANGE SINGLE | | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 7 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 8 | SORT JOIN | | 3533K| 43M| 135M| 989K (4)| 03:50:48 | | |
| 9 | VIEW | | 3533K| 43M| | 978K (4)| 03:48:18 | | |
| 10 | HASH UNIQUE | | 3533K| 67M| 94M| 978K (4)| 03:48:18 | | |
| 11 | PARTITION RANGE ITERATOR| | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
|* 12 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
------------------------------------------------------------------------------------------------------------------------------------

從執行計劃看出，no_merge已經生效了，但是目前還不是最優的，因為對一個子查詢都做了太多的工作，既要hash unique又要sort，最後兩個記錄集關聯還是用merge join。
實際上，這種關聯用hash join更合適，可以強制它們走hash join，於是得到第三個優化SQL：

sys@dwrac1> select /*+ use_hash(a,b) */count(distinct a.ip)
2 from (select /*+ no_merge */distinct t.ip
3 from creater_user.popt_total_login_month_his t
4 where t.data_desc = '2010-02-01') a, (select /*+ no_merge */distinct t.ip
5 from creater_user.popt_total_login_month_his t
6 where t.data_desc between
7 '2010-03-01' and
8 '2011-12-31') b
9 where a.ip = b.ip;

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1095K (4)| 04:15:35 | | |
| 1 | SORT GROUP BY | | 1 | 26 | | | | | |
|* 2 | HASH JOIN | | 3533K| 87M| 84M| 1095K (4)| 04:15:35 | | |
| 3 | VIEW | | 3533K| 43M| | 978K (4)| 03:48:18 | | |
| 4 | HASH UNIQUE | | 3533K| 67M| 94M| 978K (4)| 03:48:18 | | |
| 5 | PARTITION RANGE ITERATOR| | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
|* 6 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
| 7 | VIEW | | 14M| 175M| | 98667 (4)| 00:23:02 | | |
| 8 | HASH UNIQUE | | 14M| 269M| 378M| 98667 (4)| 00:23:02 | | |
| 9 | PARTITION RANGE SINGLE | | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 10 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------

從執行計劃看，已經基本達到我們的要求了，但是還不夠完美，因為我們對兩個記錄集做完排重後，關聯出來的記錄肯定是沒有重複的了，因此可以把最外層的distinct去掉，於是得到第四個優化的SQL：

sys@dwrac1> select /*+ use_hash(a,b) */count(a.ip)
2 from (select /*+ no_merge */distinct t.ip
3 from creater_user.popt_total_login_month_his t
4 where t.data_desc = '2010-02-01') a, (select /*+ no_merge */distinct t.ip
5 from creater_user.popt_total_login_month_his t
6 where t.data_desc between
7 '2010-03-01' and
8 '2011-12-31') b
9 where a.ip = b.ip;

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1095K (4)| 04:15:35 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
|* 2 | HASH JOIN | | 3533K| 87M| 84M| 1095K (4)| 04:15:35 | | |
| 3 | VIEW | | 3533K| 43M| | 978K (4)| 03:48:18 | | |
| 4 | HASH UNIQUE | | 3533K| 67M| 94M| 978K (4)| 03:48:18 | | |
| 5 | PARTITION RANGE ITERATOR| | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
|* 6 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 3533K| 67M| | 964K (4)| 03:45:07 | 2 | 17 |
| 7 | VIEW | | 14M| 175M| | 98667 (4)| 00:23:02 | | |
| 8 | HASH UNIQUE | | 14M| 269M| 378M| 98667 (4)| 00:23:02 | | |
| 9 | PARTITION RANGE SINGLE | | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
|* 10 | TABLE ACCESS FULL | POPT_TOTAL_LOGIN_MONTH_HIS | 14M| 269M| | 44061 (4)| 00:10:17 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------

原SQL執行超過幾小時都出不來，最終優化過的SQL 只需要不到1分鐘就得到結果，優化效果非常顯著。

以上步驟是我做優化的思路，從優化的步驟看，要優化，不但需要對技術瞭解，更需要對資料分佈瞭解！
在資料倉儲中，我們可能經常會碰到類似的SQL，對此，我們的處理原則是：先排重，再關聯，儘可能避免中間結果集膨脹。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1047939/，如需轉載，請註明出處，否則將追究法律責任。

【PostgreSQL 】PostgreSQL 15對distinct的優化
2022-07-17
SQL優化
Sql優化（二）快速計算Distinct Count
2015-03-28
SQL優化
藉助索引+非空優化distinct操作一例
2011-04-19
索引優化
[20170601]distinct的優化.txt
2017-06-01
優化
對sql語句的優化問題
2013-12-04
SQL優化
SQL Server中distinct的用法
2010-09-06
SQLServer
sql - distinct 去重複的用法
2019-03-04
SQL
oracle之優化一用group by或exists優化distinct
2018-12-30
Oracle優化
SQL語句操作符優化
2014-04-13
SQL優化
Oracle SQL對錶的操作
2008-05-05
OracleSQL
【SQL優化】SQL優化的10點注意事項
2009-09-03
SQL優化
SQL -去重Group by 和Distinct的效率
2021-01-22
SQL
mysql的sql優化
2017-11-22
MySql優化
優化SQL中的or
2012-01-12
優化SQL
SQL優化之操作符篇(zt)
2008-08-13
SQL優化
標量子查詢優化（用group by 代替distinct）
2013-12-06
優化
使用exists(Semi-Join)優化distinct語句
2013-09-09
優化
mysql常用的優化操作
2019-01-19
MySql優化
對一條基於分割槽的簡單SQL的優化
2019-03-01
SQL優化
[20151212優化sql語句要注意關鍵字DISTINCT
2015-12-14
優化SQL
增加Distinct後查詢效率反而提高——SQL優化之Everything is possible
2009-11-20
SQL優化
Spark效能優化：對RDD持久化或CheckPoint操作
2018-09-14
Spark優化持久化
SQL優化的方法論
2020-04-04
SQL優化
SQL語句的優化
2017-11-14
SQL優化
優化sql的利器SQLT
2013-10-15
優化SQL
一個sql的優化
2011-08-01
SQL優化
SQL優化這麼做就對了
2021-02-17
SQL優化
對IN & EXISTS NOT IN & NOT EXISTS的優化
2008-06-10
優化
介紹幾種提高mysql的效能和對於sql的優化的方法
2018-03-23
MySql優化
msyql 簡單的sql優化
2016-11-16
SQL優化
不懂業務的SQL優化方法
2015-06-18
SQL優化
SQL查詢優化的方法
2010-09-01
SQL優化
SQL 語句的優化方法
2011-09-19
SQL優化
【SQL優化】SQL優化工具
2017-03-13
SQL優化
SQL Server優化之SQL語句優化
2016-01-12
SQLServer優化
sql server中對時間日期的操作
2009-04-23
SQLServer
SQL優化
2014-04-13
SQL優化
with as優化sql
2011-11-29
優化SQL

對含distinct操作的SQL的優化

相關文章