給你一段SQL,你會如何優化?

語言: CN / TW / HK

大家好,我是飄渺!

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

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

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

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

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

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

找出有問題的SQL?

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

1、系統層面

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

2、SQL語句層面

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

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

image-20230227215628407

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

檢視SQL執行計劃?

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

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

explain select * from xxx

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

image-20230227220142368

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

| 欄位 | 解釋 | | ------------- | ------------------------------------------------------------------------------------------------------- | | id | 每個被獨立執行的操作標識,標識物件被操作的順序,id值越大,先被執行,如果相同,執行順序從上到下 | | select_type | 查詢中每個select 字句的型別 | | table | 被操作的物件名稱,通常是表名,但有其他格式 | | partitions | 匹配的分割槽資訊(對於非分割槽表值為NULL) | | type | 連線操作的型別 | | possible_keys | 可能用到的索引 | | key | 優化器實際使用的索引(最重要的列) 從最好到最差的連線型別為consteq_regrefrangeindexALL。當出現ALL時表示當前SQL出現了“壞味道” | | key_len | 被優化器選定的索引鍵長度,單位是位元組 | | ref | 表示本行被操作物件的參照物件,無參照物件為NULL | | rows | 查詢執行所掃描的元組個數(對於innodb,此值為估計值) | | filtered | 條件表上資料被過濾的元組個數百分比 | | extra | 執行計劃的重要補充資訊,當此列出現Using filesort , Using temporary 字樣時就要小心了,很可能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、先檢視各表資料量

image-20230227220949339

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

image-20230227221022931

3、檢視原執行計劃

image-20230227221056622

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`);

檢視優化後的執行時間

image-20230227221442173

初步優化後執行速度提升了20倍,是否還能繼續優化呢?

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

image-20230227221746680

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

show warnings

image-20230227221946032

Cannot use range access on index ‘idx_sellname_gmt_sellid’ due to type or collation conversion on field ‘get_create’,這句話是告訴你由於gmt_create列發生了型別轉換所以無法走索引。

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

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

alter table a modify "gmt_create" datetime DEFAULT NULL;

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

image-20230227222613569

執行速度非常完美。

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

image-20230227222703092

可以看到執行計劃也很完美,至此SQL優化結束。

SQL優化小結

這裡給大家總結一下優化SQL的套路

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

更多精彩內容請搜尋並關注公號 java日知錄