面試官：給你一段有問題的SQL，如何最佳化？

大家好，我是飄渺！

我在面試的時候很喜歡問候選人這樣一個問題：“你在專案中遇到過慢查詢問題嗎？你是怎麼做SQL最佳化的？”

很多時候，候選人會直接跟我說他們在編寫SQL時會遵循的一些常用技巧，比如：

• 合理使用索引
• 使用UNION ALL替代UNION
• 不要使用select * 寫法
• JOIN欄位建議建立索引
• 避免複雜SQL語句

這裡不能說完全錯誤，因為這些技巧確實可以提高SQL執行效率；但是也不能說完全正確，畢竟我是想問他具體怎麼是做SQL最佳化的。

接下來我問他，我這裡有一段複雜的SQL，你可以動手幫我最佳化一下嗎？到這一步的時候就有很多候選人做不好打了退堂鼓。他們有很紮實的理論知識，但是動手能力卻差點火候。

今天這篇文章就從實戰的角度出發，帶大家走一遍SQL最佳化的真實流程。

找出有問題的SQL？

在實際開發中要判斷一段SQL有沒有問題可以從兩方面來判斷：

1、系統層面

• CPU消耗嚴重
• IO等待嚴重
• 頁面響應時間過長
• 應用的日誌出現超時等錯誤

2、SQL語句層面

• 冗長
• 執行時間過長
• 從全表掃描獲取資料
• 執行計劃中的rows、cost很大

冗長的SQL都好理解，一段SQL太長閱讀性肯定會差，出現問題的頻率肯定會更高。更進一步判斷SQL問題就必須得從執行計劃入手，如下所示：

執行計劃告訴我們本次查詢走了全表掃描Type=ALL，rows很大(9950400)基本可以判斷這是一段"有味道"的SQL。

檢視SQL執行計劃？

找到了有問題的SQL就要確定最佳化方案，那究竟從何處下手呢？這裡必須要透過執行計劃來觀察。

執行計劃會告訴你哪些地方效率低，哪裡可以需要最佳化。我們以MYSQL為例，看看執行計劃是什麼。（每個資料庫的執行計劃都不一樣，需要自行了解）

explain select * from xxx

當使用explain sql後會看到執行計劃

執行計劃中幾個重要欄位的解釋說明，大家需要記住

透過執行計劃我們就可以確定最佳化方案，最佳化一處後再回過頭來觀察執行計劃，如此往復迴圈直到找到最優目標為止。

下面給出一段有問題的SQL具體操作一下。

SQL最佳化案例

慢查詢

1、表結構如下：

CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

2、有問題的查詢SQL

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

a，b，c 三張表關聯，查詢使用者17 在當前時間前後10個小時的訂單情況，並根據訂單建立時間升序排列

最佳化步驟

1、先檢視各表資料量

2、檢視原執行時間，總耗時0.21s

3、檢視原執行計劃

4、透過觀察執行計劃和SQL語句，確定初步最佳化方案

• SQL中 where條件欄位型別要跟表結構一致，表中 user_id 為varchar(50)型別，實際SQL用的int型別，存在隱式轉換，也未新增索引。將b和c表 user_id 欄位改成int型別。

• 因存在b表和c表關聯，將b和c表 user_id建立索引

• 因存在a表和b表關聯，將a和b表 seller_name欄位建立索引

• 利用複合索引消除臨時表和排序

初步最佳化的SQL：

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

檢視最佳化後的執行時間

初步最佳化後執行速度提升了20倍，是否還能繼續最佳化呢？

5、繼續檢視最佳化後的執行計劃

這裡只看到查詢需要掃描的元素比較大，不過還看到了有兩處告警資訊，直接檢視告警資訊

show warnings

檢視SQL建表語句發現gmt_create欄位被設計成了varchar型別，在SQL查詢時需要轉化成時間格式做查詢，確實不能走索引。

所以需要調整一下gmt_create欄位格式

alter table a modify "gmt_create" datetime DEFAULT NULL;

6、修改欄位後再來檢視執行時間

執行速度非常完美。

7、再觀察最佳化後的執行計劃

可以看到執行計劃也很完美，至此SQL最佳化結束。

SQL最佳化小結

這裡給大家總結一下最佳化SQL的套路，再也不怕面試官問你怎麼做SQL最佳化的啦。

1. 檢視執行計劃 explain
2. 如果有告警資訊，檢視告警資訊 show warnings;
3. 檢視SQL涉及的表結構和索引資訊
4. 根據執行計劃，思考可能的最佳化點
5. 按照可能的最佳化點執行表結構變更、增加索引、SQL改寫等操作
6. 檢視最佳化後的執行時間和執行計劃
7. 如果最佳化效果不明顯，重複第四步操作