863      釣魚_釣好魚

SQL優化__最佳實踐_分布式關係型數據庫 DRDS-阿裏雲

DRDS是一個高效穩定的分布式關係數據庫係統。但是，由於其處理的是分布式的關係查詢，因而它對於SQL的查詢優化與傳統的單一數據庫（如mysql，oracle）的查詢優化有所不同。後者在查詢優化時，主要考慮的就是磁盤IO的開銷，但前者在優化時還需要考慮另外一個更為重要的IO開銷—網絡。為了優化DRDS的SQL執行，其核心的優化思想就是減少網絡IO。為此，DRDS會盡量地將原本DRDS這一層的工作下發到其底層的各個分庫（如RDS 等）來做。這樣就可以將原本需要走網絡的IO開銷轉換為單機的磁盤IO開銷，從而提升查詢的執行效率。因此，我們在使用DRDS時若遇到了慢SQL，則需要針對DRDS的特點將SQL進行適當的改寫。

SQL條件優化

DRDS的數據是按拆分鍵進行水平切分的，查詢中若帶上拆分鍵對於減少SQL在DRDS的執行時間很有意義。查詢條件盡量帶分庫鍵，就可以讓DRDS根據分庫鍵的值將查詢直接路由到特定的分庫，這有助於避免DRDS做全庫掃描。含分庫鍵的條件的選擇度越高（或區分度越高），越有助於提高DRDS的查詢速度。例如，等值查詢會比範圍查詢執行得更快。

SQL的JOIN優化

在SQL中，Join操作常常會成為最為耗時的操作。DRDS在大多數情況下使用的Join算法都是Nested Loop及其派生算法（若Join有排序要求，則使用Sort Merge 算法）。DRDS 基於Nested Loop算法的Join過程是這樣的：對於Join的左右兩個表，DRDS首先從Join的左表（又叫驅動表）取出數據，然後將所取出數據中的Join列的值放到右表並進行IN查詢，從而完成Join過程。因此，如果Join的左表的數據量越少，那麼DRDS對右表做IN查詢就次數就越少，如果右表的數據量也很少或建有索引，則Join的速度會更快。因此，在DRDS中，Join的驅動表的選擇對於Join的優化非常重要。

小表作為Join驅動表

所謂的小表，並不是說這個表在數據庫中的記錄數目，而是這個表在查詢中在查詢中經過條件過濾後的所返回的記錄數。因此，要確定一張表的實際數據量，最簡單的方法，就是附帶上與這個表相關聯的where條件和join on條件，放到DRDS裏單獨做一次count(*)查詢，查看數據量。例如，假設有如下的SQL:

select t.title, t.price
from t_order o,
    ( select * from t_item i where i.id=242002396687 ) t
where  t.source_id=o.source_item_id and o.sellerId<1733635660;

它的查詢速度很慢，如下所示：

約需要24秒。咋看上述的SQL，這是一個inner JOIN，我們並不知道其中o表與t表在JOIN過程中的實際數據量，但是，我們可以分別去對o表與t表做count()查詢得到這組數據。對於o表，我們觀察where條件中的o.sellerId<1733635660隻與o表相關，可以將它提取出來，附加到o表的count()查詢中，即得到如下的查詢結果：

於是我們可以知道o表有50W條記錄；類似地，對於t表，這是一個子查詢，直接將其抽取出來單獨進行count(*)的查詢，則有:

這樣就可以知道t表的數據量隻有一條。於是，我們可以確定o表為大表，而t表為小表，根據盡量將小表作為Join驅動表的原則，我們將SQL調整為

select t.title, t.price
from 
( select * from t_item i where i.id=242002396687 ) t,
 t_order o
where  t.source_id=o.source_item_id and o.sellerId<1733635660

此時的查詢結果為:

查詢時間從24秒減少為0.15秒，提升巨大。

廣播表作為Join的驅動表

DRDS的廣播表在各個分庫都會存一份，所以當它作為Join的驅動表時，它和其它表的Join的都可以轉化為單機的Join，從麵提高查詢性能。例如，假設有以下的SQL（其中表t_area是廣播表）:

select t_area.name
from  t_item i join t_buyer b on i.sellerId=b.sellerId join t_area a on b.province=a.id
where  a.id < 110107 
limit 0, 10

這是三個表做JOIN，它的查詢結果如下:

執行時間比較長，約15秒。現在，我們調整一下join的順序，將廣播表放在在最左邊作為join的驅動表，即:

select t_area.name
    from t_area a join t_buyer b on b.province=a.id join t_item i on i.sellerId=b.sellerId
    where  a.id < 110107 
limit 0, 10

這樣整個join在DRDS中會被下推為單機join。我們再觀察一下調整後的SQL的執行結果:

SQL的Limit優化

DRDS在執行limit offset, count語句時，實際上是依次將offset之前的記錄讀取出來並直接丟棄，這樣當offset非常大的時候，即使count很小，也會導致查詢非常緩慢。例如以下的SQL：

SELECT * 
FROM t_order
ORDER BY t_order.id
LIMIT 10000,2

它雖然隻取10000與10001兩條記錄，可它的執行時間為12秒左右，這是因為DRDS實際讀取的記錄數為10002，如下圖：

針對上述的情況，SQL的優化方向是先查SQL的ID集合，再通過in查詢真正的記錄內容，改寫後的SQL，如下所示：

SELECT * 
FROM t_order o
WHERE o.id IN ( 
        SELECT id 
        FROM t_order 
        ORDER BY id  
LIMIT 10000,2  )

這樣改寫的目的是先用內存緩存ID（id數目不多），這樣磁盤IO就減少。如果t_order表的分庫鍵是id，那麼DRDS還可以將這樣的in查詢通過規則計算路由到不同的分庫來查詢，避免全庫掃描。我們再觀察改寫後的SQL的查詢效果

執行時間由原來的12秒變成1.08秒，縮減了一個數量級。

SQL的ORDER BY 優化

在DRDS中，默認情況下請盡可能保證Distinct，Group by 與Order By語句後所帶的列名相同，並使最終的SQL隻返回少量數據。因為在這種情況下，我們能夠讓分布式查詢中消耗的網絡帶寬最小，並且不需要取出大量數據在臨時表內進行排序，係統的性能能夠達到最優狀態。

例如，對於下邊的一條SQL：

select buyer_id, 
        count(*) as maxSize 
from t_trade 
group by buyer_id 
order by maxSize desc 
limit 1

DRDS執行過程中首先需要對數據按buyer_id排序以進行聚合操作，然後還需要對聚合結果按maxSize排序。因為存在兩個不同的排序列要求，DRDS不用臨時表保存中間結果的話，無法一次完成該SQL，因此這個SQL的實際執行會報以下的錯

報錯的內容提示了不允許使用臨時表。DRDS支持使用臨時表，但默認並不推薦，因為臨時表的使用一般就意味著係統存在性能瓶頸，因此如果沒有特殊必要，請盡可能通過Distimct Group by 與Order by語句所帶列名相同的方式來規避使用臨時表。如果讓上述的SQL順利執行，可以通過往SQL中添加HINT的方式來告訴DRDS可以允許使用臨時表，SQL修改如下（綠色部分）：

/*+TDDL({'extra':{'ALLOW_TEMPORARY_TABLE':'TRUE'}})*/
select buyer_id, 
       count(*) as maxSize 
from t_trade 
group by buyer_id 
order by maxSize desc 
limit 1

這樣DRDS就可以順利執行這個SQL了。不過，這個HINT在一些mysql 客戶端有可能會被當作注釋忽略掉，因而hint一般加在通過mysql connector直接給DRDS發送SQL的時候。請注意，請確定在臨時表內隻有很少量數據的前提下才使用臨時表，否則係統會有嚴重性能問題####

最後更新：2016-11-23 16:04:05

  上一篇： 數據導入和導出__最佳實踐_分布式關係型數據庫 DRDS-阿裏雲

  下一篇： 排查DRDS慢SQL__開發手冊_分布式關係型數據庫 DRDS-阿裏雲