給你一段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
後會看到執行計劃
執行計劃中幾個重要字段的解釋説明,大家需要記住
| 字段 | 解釋 |
| ------------- | ------------------------------------------------------------------------------------------------------- |
| id | 每個被獨立執行的操作標識,標識對象被操作的順序,id值越大,先被執行,如果相同,執行順序從上到下 |
| select_type | 查詢中每個select 字句的類型 |
| table | 被操作的對象名稱,通常是表名,但有其他格式 |
| partitions | 匹配的分區信息(對於非分區表值為NULL) |
| type | 連接操作的類型 |
| possible_keys | 可能用到的索引 |
| key | 優化器實際使用的索引(最重要的列) 從最好到最差的連接類型為const
、eq_reg
、ref
、range
、index
和ALL
。當出現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、先查看各表數據量
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
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、修改字段後再來查看執行時間
執行速度非常完美。
7、再觀察優化後的執行計劃
可以看到執行計劃也很完美,至此SQL優化結束。
SQL優化小結
這裏給大家總結一下優化SQL的套路
- 查看執行計劃 explain
- 如果有告警信息,查看告警信息 show warnings;
- 查看SQL涉及的表結構和索引信息
- 根據執行計劃,思考可能的優化點
- 按照可能的優化點執行表結構變更、增加索引、SQL改寫等操作
- 查看優化後的執行時間和執行計劃
- 如果優化效果不明顯,重複第四步操作
更多精彩內容請搜索並關注公號 java日知錄
- 數據權限就該這麼實現(設計篇)
- 數據權限就該這麼實現(實現篇)
- 給你一段SQL,你會如何優化?
- 當我把ChatGPT機器人拉到微信羣裏,羣友都玩瘋了!!
- SpringBoot 如何保證接口安全?老鳥們都是這麼玩的!
- 掌握系統思維,你就可以既勤奮努力又輕鬆愉快。
- SpringBoot自定義註解 AOP 防止重複提交(建議收藏)
- 面試官:應用上線後Cpu使用率飆升如何排查?
- SpringBoot中實現業務校驗,這種方式才叫優雅!
- SpringCloud Gateway 收集輸入輸出日誌
- 震驚,Spring官方推薦的@Transational還能導致生產事故?
- 為什麼要在MVC三層架構上再加一層Manager層?
- SpringBoot 如何生成接口文檔,老鳥們都這麼玩的!
- SpringBoot 如何進行限流?老鳥們都這麼玩的!
- SpringBoot 生成接口文檔,我用smart-doc,一款比Swagger更nice的工具!
- SpringBoot 如何進行對象複製,老鳥們都這麼玩的!
- 3天,我把MySQL索引、鎖、事務、分庫分表擼乾淨了!
- 字節全面對外開放中台能力!中台,又靈了?
- 基於 Kubernetes 的微服務項目設計與實現
- 老闆要我開發一個簡單的工作流引擎