mysql暫存資料表產生的執行效率問題改進(轉)

標籤：print   nts   避免   閱讀   from   使用   sub   建立   fine   

• 問題：

 

近日，線上MySQL查出一個慢sql，每次都要查詢1000ms以上，嚴重影響使用者體驗

今得空去診斷一番，記錄如下：

sql原句：

 

[html] view plain copy  print?

1. SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r    
2.     LEFT JOIN   
3.     ( SELECT card_id FROM card_member WHERE user_id = #uid# and card_member.deleted=0  
4.      UNION  
5.     SELECT card_id FROM card_subscribed where user_id = #uid# and card_subscribed.deleted=0  
6.     ) m ON r.object_id = m.card_id  
7.     WHERE r.object_type = #objectType# AND r.deleted = 0  
8.     GROUP BY r.object_id;  

 

 

• 解決問題：

 

由於對資料庫最佳化一知半解，完全無從下手，只能求助度娘和穀哥了，實驗了各種方法，都不見效果

幾番周折之後，最終把注意力集中到了暫存資料表上，因為explain查看執行計畫，可以看到Using temporary

MySQL在執行SQL查詢時可能會用到暫存資料表，一般情況下，用到暫存資料表就意味著效能較低。

於是想辦法修改sql語句，摒棄暫存資料表，修改如下：

 

[html] view plain copy  print?

1. SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r    
2.     WHERE r.object_type = #objectType#  AND r.deleted = 0 and r.object_id in (  
3.     SELECT card_id FROM card_member WHERE user_id = #uid# and card_member.deleted=0  
4.         UNION  
5.       SELECT card_id FROM card_subscribed where user_id = #uid# and card_subscribed.deleted=0  
6.     )  
7.     GROUP BY r.object_id;  

 

即把語句給拆分成兩個sql語，用in操作拼接

 

• 本機測試：

 

最佳化前執行時間1040ms，最佳化後執行時間：85ms，執行速度是原來的12倍多！贊

 

• PS：

 

常理我們都會排斥用in操作，用union替換，那為什麼這裡用in會更快呢？

帶著問題，接著去網上找，原來：

sql執行會產生一個巨大的暫存資料表，當記憶體放不下時，要全部copy 到磁碟，導致IO飆升，時間開銷增大。

 

額外收穫知識收藏如下：

 

• 暫存資料表儲存

 

MySQL暫存資料表分為“記憶體暫存資料表”和“磁碟暫存資料表”，其中記憶體暫存資料表使用MySQL的MEMORY儲存引擎，磁碟暫存資料表使用MySQL的MyISAM儲存引擎；
一般情況下，MySQL會先建立記憶體暫存資料表，但記憶體暫存資料表超過配置指定的值後，MySQL會將記憶體暫存資料表匯出到磁碟暫存資料表；

 

• 使用暫存資料表的情境

1）ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；
2）在JOIN查詢中，ORDER BY或者GROUP BY使用了不是第一個表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3）ORDER BY中使用了DISTINCT關鍵字 ORDERY BY DISTINCT(price)
4）SELECT語句中指定了SQL_SMALL_RESULT關鍵字 SQL_SMALL_RESULT的意思就是告訴MySQL，結果會很小，請直接使用記憶體暫存資料表，不需要使用索引排序 SQL_SMALL_RESULT必須和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情況下，我們沒有必要使用這個選項，讓MySQL伺服器選擇即可。

 

 

• 直接使用磁碟暫存資料表的情境

1）表包含TEXT或者BLOB列；
2）GROUP BY 或者 DISTINCT 子句中包含長度大於512位元組的列；
3）使用UNION或者UNION ALL時，SELECT子句中包含大於512位元組的列；

 

 

• 表的設計原則

使用暫存資料表一般都意味著效能比較低，特別是使用磁碟暫存資料表，效能更慢，因此我們在實際應用中應該盡量避免暫存資料表的使用。 常見的避免暫存資料表的方法有：
1）建立索引：在ORDER BY或者GROUP BY的列上建立索引；
2）分拆很長的列：一般情況下，TEXT、BLOB，大於512位元組的字串，基本上都是為了顯示資訊，而不會用於查詢條件， 因此表設計的時候，應該將這些列獨立到另外一張表。

• SQL最佳化

如果表的設計已經確定，修改比較困難，那麼也可以通過最佳化SQL語句來減少暫存資料表的大小，以提升SQL執行效率。
常見的最佳化SQL語句方法如下：
1）拆分SQL語句
暫存資料表主要是用於排序和分組，很多業務都是要求排序後再取出詳細的分頁資料，這種情況下可以將排序和取出詳細資料拆分成不同的SQL，以降低排序或分組時暫存資料表的大小，提升排序和分組的效率，我們的案例就是採用這種方法。
2）最佳化業務，去掉排序分組等操作
有時候業務其實並不需要排序或分組，僅僅是為了好看或者閱讀方便而進行了排序，例如資料匯出、資料查詢等操作，這種情況下去掉排序和分組對業務也沒有多大影響。

• 如何判斷使用了暫存資料表？

使用explain查看執行計畫，Extra列看到Using temporary就意味著使用了暫存資料表。

mysql暫存資料表產生的執行效率問題改進(轉)