問題：

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

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

sql原句：

SELECT?r.object_id?AS?cardId,?count(1)?AS?attachs?FROM?hzresource_object?r

LEFT?JOIN

(?SELECT?card_id?FROM?card_member?WHERE?user_id?=?#uid#?and?card_member.deleted=0

UNION

SELECT?card_id?FROM?card_subscribed?where?user_id?=?#uid#?and?card_subscribed.deleted=0

)?m?ON?r.object_id?=?m.card_id

WHERE?r.object_type?=?#objectType#?AND?r.deleted?=?0

GROUP?BY?r.object_id;

解決問題：

由于對數據庫優化一知半解，完全無從下手，只能求助度娘和谷哥了，試驗了各種方法，都不見效果

幾番周折之后，最終把注意力集中到了臨時表上，因為explain查看執行計劃，可以看到Using temporary

MySQL在執行SQL查詢時可能會用到臨時表，一般情況下，用到臨時表就意味著性能較低。

于是想辦法修改sql語句，摒棄臨時表，修改如下：

SELECT?r.object_id?AS?cardId,?count(1)?AS?attachs?FROM?hzresource_object?r

WHERE?r.object_type?=?#objectType#??AND?r.deleted?=?0?and?r.object_id?in?(

SELECT?card_id?FROM?card_member?WHERE?user_id?=?#uid#?and?card_member.deleted=0

UNION

SELECT?card_id?FROM?card_subscribed?where?user_id?=?#uid#?and?card_subscribed.deleted=0

)

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就意味著使用了臨時表。

小結：

可見， 完全顛覆了對in操作符的認識，凡事兒都是要分情況討論的

總結

以上是生活随笔為你收集整理的mysql临时表好处和坏处_mysql临时表产生的执行效率问题改进(转)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。