Ⅰ 如何提高postgresql查詢性能
一、使用EXPLAIN：
PostgreSQL為每個查詢都生成一個查詢規劃，因為選擇正確的查詢路徑對性能的影響是極為關鍵的。PostgreSQL本身已經包含了一個規劃器用於尋找最優規劃，我們可以通過使用EXPLAIN命令來查看規劃器為每個查詢生成的查詢規劃。
PostgreSQL中生成的查詢規劃是由1到n個規劃節點構成的規劃樹，其中最底層的節點為表掃描節點，用於從數據表中返回檢索出的數據行。然而，不同
的掃描節點類型代表著不同的表訪問模式，如：順序掃描、索引掃描，以及點陣圖索引掃描等。如果查詢仍然需要連接、聚集、排序，或者是對原始行的其它操作，那
么就會在掃描節點"之上"有其它額外的節點。並且這些操作通常都有多種方法，因此在這些位置也有可能出現不同的節點類型。EXPLAIN將為規劃樹中的每
個節點都輸出一行信息，顯示基本的節點類型和規劃器為執行這個規劃節點計算出的預計開銷值。第一行(最上層的節點)是對該規劃的總執行開銷的預計，這個數
值就是規劃器試圖最小化的數值。
這里有一個簡單的例子，如下：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN引用的數據是：
1). 預計的啟動開銷(在輸出掃描開始之前消耗的時間，比如在一個排序節點里做排續的時間)。
2). 預計的總開銷。
3). 預計的該規劃節點輸出的行數。
4). 預計的該規劃節點的行平均寬度(單位：位元組)。
這里開銷(cost)的計算單位是磁碟頁面的存取數量，如1.0將表示一次順序的磁碟頁面讀取。其中上層節點的開銷將包括其所有子節點的開銷。這里的輸出
行數(rows)並不是規劃節點處理/掃描的行數，通常會更少一些。一般而言，頂層的行預計數量會更接近於查詢實際返回的行數。
現在我們執行下面基於系統表的查詢：
復制代碼 代碼如下:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
從查詢結果中可以看出tenk1表佔有358個磁碟頁面和10000條記錄，然而為了計算cost的值，我們仍然需要知道另外一個系統參數值。
復制代碼 代碼如下:
postgres=# show cpu_tuple_cost;
cpu_tuple_cost
----------------
0.01
(1 row)
cost = 358(磁碟頁面數) + 10000(行數) * 0.01(cpu_tuple_cost系統參數值)
下面我們再來看一個帶有WHERE條件的查詢規劃。
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
EXPLAIN的輸出顯示，WHERE子句被當作一個"filter"應用，這表示該規劃節點將掃描表中的每一行數據，之後再判定它們是否符合過濾的條
件，最後僅輸出通過過濾條件的行數。這里由於WHERE子句的存在，預計的輸出行數減少了。即便如此，掃描仍將訪問所有10000行數據，因此開銷並沒有
真正降低，實際上它還增加了一些因數據過濾而產生的額外CPU開銷。
上面的數據只是一個預計數字，即使是在每次執行ANALYZE命令之後也會隨之改變，因為ANALYZE生成的統計數據是通過從該表中隨機抽取的樣本計算的。
如果我們將上面查詢的條件設置的更為嚴格一些的話，將會得到不同的查詢規劃，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
這里，規劃器決定使用兩步規劃，最內層的規劃節點訪問一個索引，找出匹配索引條件的行的位置，然後上層規劃節點再從表裡讀取這些行。單獨地讀取數據行比順
序地讀取它們的開銷要高很多，但是因為並非訪問該表的所有磁碟頁面，因此該方法的開銷仍然比一次順序掃描的開銷要少。這里使用兩層規劃的原因是因為上層規
劃節點把通過索引檢索出來的行的物理位置先進行排序，這樣可以最小化單獨讀取磁碟頁面的開銷。節點名稱裡面提到的"點陣圖(bitmap)"是進行排序的機
制。
現在我們還可以將WHERE的條件設置的更加嚴格，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
在該SQL中，表的數據行是以索引的順序來讀取的，這樣就會令讀取它們的開銷變得更大，然而事實上這里將要獲取的行數卻少得可憐，因此沒有必要在基於行的物理位置進行排序了。
現在我們需要向WHERE子句增加另外一個條件，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
新增的過濾條件stringu1 = 'xxx'只是減少了預計輸出的行數，但是並沒有減少實際開銷，因為我們仍然需要訪問相同數量的數據行。而該條件並沒有作為一個索引條件，而是被當成對索引結果的過濾條件來看待。
如果WHERE條件里有多個欄位存在索引，那麼規劃器可能會使用索引的AND或OR的組合，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
這樣的結果將會導致訪問兩個索引，與只使用一個索引，而把另外一個條件只當作過濾器相比，這個方法未必是更優。
現在讓我們來看一下基於索引欄位進行表連接的查詢規劃，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
從查詢規劃中可以看出(Nested
Loop)該查詢語句使用了嵌套循環。外層的掃描是一個點陣圖索引，因此其開銷與行計數和之前查詢的開銷是相同的，這是因為條件unique1 <
100發揮了作用。 這個時候t1.unique2 =
t2.unique2條件子句還沒有產生什麼作用，因此它不會影響外層掃描的行計數。然而對於內層掃描而言，當前外層掃描的數據行將被插入到內層索引掃描
中，並生成類似的條件t2.unique2 = constant。所以，內層掃描將得到和EXPLAIN SELECT * FROM tenk2
WHERE unique2 = 42一樣的計劃和開銷。最後，以外層掃描的開銷為基礎設置循環節點的開銷，再加上每個外層行的一個迭代(這里是 106
* 3.01)，以及連接處理需要的一點點CPU時間。
如果不想使用嵌套循環的方式來規劃上面的查詢，那麼我們可以通過執行以下系統設置，以關閉嵌套循環，如：
復制代碼 代碼如下:
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
這個規劃仍然試圖用同樣的索引掃描從tenk1裡面取出符合要求的100行，並把它們存儲在內存中的散列(哈希)表裡，然後對tenk2做一次全表順序掃
描，並為每一條tenk2中的記錄查詢散列(哈希)表，尋找可能匹配t1.unique2 =
t2.unique2的行。讀取tenk1和建立散列表是此散列聯接的全部啟動開銷，因為我們在開始讀取tenk2之前不可能獲得任何輸出行。
此外，我們還可以用EXPLAIN ANALYZE命令檢查規劃器預估值的准確性。這個命令將先執行該查詢，然後顯示每個規劃節點內實際運行時間，以及單純EXPLAIN命令顯示的預計開銷，如：
復制代碼 代碼如下:
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37
rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1
loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
注意"actual time"數值是以真實時間的毫秒來計算的，而"cost"預估值是以磁碟頁面讀取數量來計算的，所以它們很可能是不一致的。然而我們需要關注的只是兩組數據的比值是否一致。
在一些查詢規劃里，一個子規劃節點很可能會運行多次，如之前的嵌套循環規劃，內層的索引掃描會為每個外層行執行一次。在這種情況下，"loops"將報告
該節點執行的總次數，而顯示的實際時間和行數目則是每次執行的平均值。這么做的原因是令這些真實數值與開銷預計顯示的數值更具可比性。如果想獲得該節點所
花費的時間總數，計算方式是用該值乘以"loops"值。
EXPLAIN ANALYZE顯示的"Total runtime"包括執行器啟動和關閉的時間，以及結果行處理的時間，但是它並不包括分析、重寫或者規劃的時間。
如果EXPLAIN命令僅能用於測試環境，而不能用於真實環境，那它就什麼用都沒有。比如，在一個數據較少的表上執行EXPLAIN，它不能適用於數量很
多的大表，因為規劃器的開銷計算不是線性的，因此它很可能對大些或者小些的表選擇不同的規劃。一個極端的例子是一個只佔據一個磁碟頁面的表，在這樣的表
上，不管它有沒有索引可以使用，你幾乎都總是得到順序掃描規劃。規劃器知道不管在任何情況下它都要進行一個磁碟頁面的讀取，所以再增加幾個磁碟頁面讀取用
以查找索引是毫無意義的。
二、批量數據插入：
有以下幾種方法用於優化數據的批量插入。
1. 關閉自動提交：
在批量插入數據時，如果每條數據都被自動提交，當中途出現系統故障時，不僅不能保障本次批量插入的數據一致性，而且由於有多次提交操作的發生，整個插入效
率也會受到很大的打擊。解決方法是，關閉系統的自動提交，並且在插入開始之前，顯示的執行begin
transaction命令，在全部插入操作完成之後再執行commit命令提交所有的插入操作。
2. 使用COPY：
使用COPY在一條命令里裝載所有記錄，而不是一系列的INSERT命令。COPY命令是為裝載數量巨大的數據行優化過的，它不像INSERT命令那樣靈
活，但是在裝載大量數據時，系統開銷也要少很多。因為COPY是單條命令，因此在填充表的時就沒有必要關閉自動提交了。
3. 刪除索引：
如果你正在裝載一個新創建的表，最快的方法是創建表，用COPY批量裝載，然後創建表需要的任何索引。因為在已存在數據的表上創建索引比維護逐行增加要快。當然在缺少索引期間，其它有關該表的查詢操作的性能將會受到一定的影響，唯一性約束也有可能遭到破壞。
4. 刪除外鍵約束：
和索引一樣，"批量地"檢查外鍵約束比一行行檢查更加高效。因此，我們可以先刪除外鍵約束，裝載數據，然後在重建約束。
5. 增大maintenance_work_mem：
在裝載大量數據時，臨時增大maintenance_work_mem系統變數的值可以改進性能。這個系統參數可以提高CREATE
INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的執行效率，但是它不會對COPY操作本身產生多大的影響。
6. 增大checkpoint_segments：
臨時增大checkpoint_segments系統變數的值也可以提高大量數據裝載的效率。這是因為在向PostgreSQL裝載大量數據時，將會導致
檢查點操作(由系統變數checkpoint_timeout聲明)比平時更加頻繁的發生。在每次檢查點發生時，所有的臟數據都必須flush到磁碟上。
通過提高checkpoint_segments變數的值，可以有效的減少檢查點的數目。
7. 事後運行ANALYZE：
在增加或者更新了大量數據之後，應該立即運行ANALYZE命令，這樣可以保證規劃器得到基於該表的最新數據統計。換句話說，如果沒有統計數據或者統計數據太過陳舊，那麼規劃器很可能會選擇一個較差的查詢規劃，從而導致查詢效率過於低下。
Ⅱ 為什麼postgrelsql的性能沒有mysql好
一、 PostgreSQL 的穩定性極強， Innodb 等引擎在崩潰、斷電之類的災難場景下抗打擊能力有了長足進步，然而很多 MySQL 用戶都遇到過Server級的資料庫丟失的場景——mysql系統庫是MyISAM的，相比之下，PG資料庫這方面要好一些。
二、任何系統都有它的性能極限，在高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySQL 明顯出現一個波峰後下滑（5.5版本之後，在企業級版本中有個插件可以改善很多，不過需要付費）。
三、PG 多年來在 GIS 領域處於優勢地位，因為它有豐富的幾何類型，實際上不止幾何類型，PG有大量字典、數組、bitmap 等數據類型，相比之下mysql就差很多，instagram就是因為PG的空間資料庫擴展POSTGIS遠遠強於MYSQL的my spatial而採用PGSQL的。
四、PG 的「無鎖定」特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和PGSQL的MVCC實現有關系。
五、PG 的可以使用函數和條件索引，這使得PG資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。
六、PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，比如分析函數（ORACLE的叫法，PG里叫window函數），還可以用多種語言來寫存儲過程，對於R的支持也很好。這一點上MYSQL就差的很遠，很多分析功能都不支持，騰訊內部數據存儲主要是MYSQL，但是數據分析主要是HADOOP+PGSQL。
七、PG 的有多種集群架構可以選擇，plproxy 可以支持語句級的鏡像或分片，slony 可以進行欄位級的同步設置，standby 可以構建WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便，操作非常簡單。
八、一般關系型資料庫的字元串有限定長度8k左右，無限長 TEXT 類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL語法內置正則表達式，可以索引，還可以全文檢索，或使用xml xpath。用PG的話，文檔資料庫都可以省了。
九，對於WEB應用來說，復制的特性很重要，mysql到現在也是非同步復制，pgsql可以做到同步，非同步，半同步復制。還有mysql的同步是基於binlog復制，類似oracle golden gate,是基於stream的復制，做到同步很困難，這種方式更加適合異地復制，pgsql的復制基於wal，可以做到同步復制。同時，pgsql還提供stream復制。
十，pgsql對於numa架構的支持比mysql強一些，比MYSQL對於讀的性能更好一些，pgsql提交可以完全非同步，而mysql的內存表不夠實用（因為表鎖的原因）
最後說一下我感覺 PG 不如 MySQL 的地方。
第一，MySQL有一些實用的運維支持，如 slow-query.log ，這個pg肯定可以定製出來，但是如果可以配置使用就更好了。
第二是mysql的innodb引擎，可以充分優化利用系統所有內存，超大內存下PG對內存使用的不那麼充分，
第三點，MySQL的復制可以用多級從庫，但是在9.2之前，PGSQL不能用從庫帶從庫。
第四點，從測試結果上看，mysql 5.5的性能提升很大，單機性能強於pgsql，5.6應該會強更多.
第五點，對於web應用來說,mysql 5.6 的內置MC API功能很好用，PGSQL差一些。
另外一些：
pgsql和mysql都是背後有商業公司，而且都不是一個公司。大部分開發者，都是拿工資的。
說mysql的執行速度比pgsql快很多是不對的，速度接近，而且很多時候取決於你的配置。
對於存儲過程，函數，視圖之類的功能，現在兩個資料庫都可以支持了。
另外多線程架構和多進程架構之間沒有絕對的好壞，oracle在unix上是多進程架構，在windows上是多線程架構。
很多pg應用也是24/7的應用，比如skype. 最近幾個版本VACUUM基本不影響PGSQL 運行，8.0之後的PGSQL不需要cygwin就可以在windows上運行。
至於說對於事務的支持，mysql和pgsql都沒有問題。
Ⅲ 《資料庫查詢優化器的藝術原理解析與SQL性能優化》epub下載在線閱讀，求百度網盤雲資源
《資料庫查詢優化器的藝術》（李海翔）電子書網盤下載免費在線閱讀
資源鏈接：
鏈接：
書名：資料庫查詢優化器的藝術
作者：李海翔
豆瓣評分：8.4
出版社：機械工業出版社
出版年份：2014-1-1
頁數：532
內容簡介：
《資料庫技術叢書·資料庫查詢優化器的藝術：原理解析與SQL性能優化》是資料庫查詢優化領域的里程碑之作，由Oracle公司MySQL全球開發團隊、資深專家撰寫，作者有10餘年資料庫內核和查詢優化器研究經驗。資料庫領域泰斗王珊教授親自作序推薦，PostgreSQL中國社區和中國用戶會發起人以及來自Oracle、新浪、網易、華為等企業的數位資深資料庫專家聯袂推薦。從原理角度深度解讀和展示資料庫查詢優化器的技術細節和全貌；從源碼實現角度全方位深入分析MySQL和PostgreSQL兩大主流開源資料庫查詢優化器的實現原理；從工程實踐的角度對比了兩大資料庫的查詢優化器的功能異同和實現異同。它是所有數據開發工程師、內核工程師、DBA以及其他資料庫相關工作人員值得反復研讀的一本書。
《資料庫技術叢書·資料庫查詢優化器的藝術：原理解析與SQL性能優化》共19章，分為四個部分：第一篇（第1～4章）對資料庫查詢優化技術的范圍、邏輯查詢優化、物理查詢優化，以及查詢優化器與其他模塊的關系做了非常細致、深入的講解；第二篇（第5～10章）首先從源碼角度對PostgreSQL查詢優化器的架構、層次、設計思想、相關數據結構和實現原理進行了深入、系統的分析，然後從功能角度對PostgreSQL的邏輯查詢優化、物理查詢優化、查詢優化器的關鍵演算法，以及PostgreSQL查詢優化器與其他模塊的關系做了深入的講解；第三篇（第11～16章）首先從源碼角度對MySQL查詢優化器的架構、層次、設計思想、相關數據結構和實現原理進行了深入、系統的分析，然後從功能角度對MySQL的邏輯查詢優化、物理查詢優化、查詢優化器的關鍵演算法，以及MySQL查詢優化器與其他模塊的關系做了深入的講解；第四篇（第17～19章）對PostgreSQL與MySQL的邏輯查詢優化技術、物理查詢優化技術、設計思想和編碼規范等各方面進行了深度的比較。
作者簡介：
李海翔，網名「那海藍藍」，資深資料庫專家，從事資料庫研發、資料庫測試與技術管理等工作10餘年，對資料庫的內核有深入的研究，長於PostgreSQL和MySQL等開源資料庫的內核與架構。現任職於Oracle公司MySQL全球開發團隊，從事查詢優化技術的研究和MySQL查詢優化器的開發工作。曾參與了863、核高基、工信部、科技部、發改委、北京市科委等多個重大科技項目。2005年獲得北京市科學技術進步獎一等獎，2006年獲高級工程師（系統分析師）。
Ⅳ SQL語句優化，使用postgresql資料庫，查詢下面sql,需要20多分鍾：
postgresql（8.2）的配置文件中有一個參數log_min_ration_statement，意思是只log執行時間大於設定值的語句，如果設為0，表示log所有語句；如果設為-1，表示不log任何語句。
看起來，這個配置選項對性能的調整是很有用的，比如可以設置：
log_min_ration_statement = 1000
則只log執行時間大於1s的語句，重點優化這些sql語句就好了。
然而，奇怪的，這個選項不太容易生效！經過反復試驗，原來需要如下配置：
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
log_connections = off
#log_disconnections = off
log_ration = off
log_line_prefix = '%t [%p]: [%l-1] ' # Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no milliseconds)
# %m = timestamp with milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start timestamp
# %x = transaction id
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_statement = 'none' # none, mod, ddl, all
#log_statement = 'all' # none, mod, ddl, all
#log_hostname = off
注意看上面的其中兩個選項的設置：
log_ration = off
log_statement = 'none'
這兩個選項的意思是不log任何sql語句和執行時間，但是恰恰是關閉了這兩個，log_min_ration_statement才會生效！可能postgresql內部 對這兩個選項做了「互斥」處理吧。
Ⅳ 如何提高一個函數在PostgreSQL的游標的性能
1. SQL優化的原則是：將一次操作需要讀取的BLOCK數減到最低,即在最短的時間達到最大的數據吞吐量。
調整不良SQL通常可以從以下幾點切入：
? 檢查不良的SQL，考慮其寫法是否還有可優化內容
? 檢查子查詢 考慮SQL子查詢是否可以用簡單連接的方式進行重新書寫
? 檢查優化索引的使用
? 考慮資料庫的優化器
Ⅵ SQL優化（一） Merge Join VS. Hash Join VS. Nested Loop
| 類別 | Nested Loop | Hash Join | Merge Join |
|---------------------------------------------|
| 使用條件 | 任何條件 | 等值連接（=） | 等值或非等值連接(>，<，=，>=，<=)，『<>』除外 |
| 相關資源 | CPU、磁碟I/O | 內存、臨時空間 | 內存、臨時空間 |
| 特點 | 當有高選擇性索引或進行限制性搜索時效率比較高，能夠快速返回第一次的搜索結果。 | 當缺乏索引或者索引條件模糊時，Hash Join比Nested Loop有效。通常比Merge Join快。在數據倉庫環境下，如果表的紀錄數多，效率高。| 當缺乏索引或者索引條件模糊時，Merge Join比Nested Loop有效。非等值連接時，Merge Join比Hash Join更有效
| 缺點 | 當索引丟失或者查詢條件限制不夠時，效率很低；當表的紀錄數多時，效率低。| 為建立哈希表，需要大量內存。第一次的結果返回較慢。 | 所有的表都需要排序。它為最優化的吞吐量而設計，並且在結果沒有全部找到前不返回數據。|
本文所做實驗均基於PostgreSQL 9.3.5平台
一張記錄數1萬以下的小表nbar.mse_test_test，一張大表165萬條記錄的大表nbar.nbar_test，大表上建有索引
如下圖所示，執行器將小表mse_test_test作為外表（驅動表），對於其中的每條記錄，通過大表（nbar_test）上的索引匹配相應記錄。
如下圖所示，執行器選擇一張表將其映射成散列表，再遍歷另外一張表並從散列表中匹配相應記錄。
如下圖所示，執行器先分別對mse_test_test和nbar_test按client_key排序。其中mse_test_test使用快速排序，而nbar_test使用external merge排序，之後對二者進行Merge Join。
通過對比 Query 1 Test 1 ， Query 1 Test 2 ， Query 1 Test 3 可以看出Nested Loop適用於結果集很小（一般要求小於一萬條），並且內表在Join欄位上建有索引（這點非常非常非常重要）。
如下圖所示，執行器通過聚簇索引對大表（nbar_test）排序，直接通過快排對無索引的小表（mse_test_test）排序，之後對二才進行Merge Join。
通過對比 Query 1 Test 3 和 Query 1 Test 4 可以看出，Merge Join的主要開銷是排序開銷，如果能通過建立聚簇索引（如果Query必須顯示排序），可以極大提高Merge Join的性能。從這兩個實驗可以看出，創建聚簇索引後，查詢時間從4956.768 ms縮減到了1815.238 ms。
如下圖所示，執行器通過聚簇索引對大表（nbar_test）和小表（mse_test_test）排序，之後對二才進行Merge Join。
對比 Query 1 Test 4 和 Query 1 Test 5 ，可以看出二者唯一的不同在於對小表（mse_test_test）的訪問方式不同，前者使用快排，後者因為聚簇索引的存在而使用Index Only Scan，在表數據量比較小的情況下前者比後者效率更高。由此可看出如果通過索引排序再查找相應的記錄比直接在原記錄上排序效率還低，則直接在原記錄上排序後Merge Join效率更高。
時間與 Query 1 Test 2 幾乎相等。
通過對比 Query 1 Test 2 ， Query 1 Test 6 可以看出Hash Join不要求表在Join欄位上建立索引。
mse_test約100萬條記錄，nbar_test約165萬條記錄
本次實驗通過設置 enable_hashjoin=true ， enable_nestloop=false ， enable_mergejoin=false 來試圖強制使用Hash Join，但是失敗了。
閱讀下一篇 SQL優化（二） 快速計算Distinct Count