給你一段SQL，你會(huì)如何優(yōu)化？
大家好，我是飄渺！
我在面試的時(shí)候很喜歡問(wèn)候選人這樣一個(gè)問(wèn)題：“你在項(xiàng)目中遇到過(guò)慢查詢(xún)問(wèn)題嗎？你是怎么做SQL優(yōu)化的？”
很多時(shí)候，候選人會(huì)直接跟我說(shuō)他們?cè)诰帉?xiě)SQL時(shí)會(huì)遵循的一些常用技巧，比如：
合理使用索引
使用UNION ALL替代UNION
不要使用select * 寫(xiě)法
JOIN字段建議建立索引
避免復(fù)雜SQL語(yǔ)句
這里不能說(shuō)完全錯(cuò)誤，因?yàn)檫@些技巧確實(shí)可以提高SQL運(yùn)行效率；但是也不能說(shuō)完全正確，畢竟我是想問(wèn)他具體怎么是做SQL優(yōu)化的。
接下來(lái)我問(wèn)他，我這里有一段復(fù)雜的SQL，你可以動(dòng)手幫我優(yōu)化一下嗎？到這一步的時(shí)候就有很多候選人做不好打了退堂鼓。他們有很扎實(shí)的理論知識(shí)，但是動(dòng)手能力卻差點(diǎn)火候。
今天這篇文章就從實(shí)戰(zhàn)的角度出發(fā)，帶大家走一遍SQL優(yōu)化的真實(shí)流程。
找出有問(wèn)題的SQL？
在實(shí)際開(kāi)發(fā)中要判斷一段SQL有沒(méi)有問(wèn)題可以從兩方面來(lái)判斷：
1、系統(tǒng)層面
CPU消耗嚴(yán)重
IO等待嚴(yán)重
頁(yè)面響應(yīng)時(shí)間過(guò)長(zhǎng)
應(yīng)用的日志出現(xiàn)超時(shí)等錯(cuò)誤
2、SQL語(yǔ)句層面
冗長(zhǎng)
執(zhí)行時(shí)間過(guò)長(zhǎng)
從全表掃描獲取數(shù)據(jù)
執(zhí)行計(jì)劃中的rows、cost很大
冗長(zhǎng)的SQL都好理解，一段SQL太長(zhǎng)閱讀性肯定會(huì)差，出現(xiàn)問(wèn)題的頻率肯定會(huì)更高。更進(jìn)一步判斷SQL問(wèn)題就必須得從執(zhí)行計(jì)劃入手，如下所示：
執(zhí)行計(jì)劃告訴我們本次查詢(xún)走了全表掃描Type=ALL，rows很大(9950400)基本可以判斷這是一段"有味道"的SQL。
查看SQL執(zhí)行計(jì)劃？
找到了有問(wèn)題的SQL就要確定優(yōu)化方案，那究竟從何處下手呢？這里必須要通過(guò)執(zhí)行計(jì)劃來(lái)觀察。
執(zhí)行計(jì)劃會(huì)告訴你哪些地方效率低，哪里可以需要優(yōu)化。我們以MYSQL為例，看看執(zhí)行計(jì)劃是什么。（每個(gè)數(shù)據(jù)庫(kù)的執(zhí)行計(jì)劃都不一樣，需要自行了解）
csharp復(fù)制代碼explain select * from xxx
當(dāng)使用explain sql后會(huì)看到執(zhí)行計(jì)劃
執(zhí)行計(jì)劃中幾個(gè)重要字段的解釋說(shuō)明，大家需要記住
字段解釋id每個(gè)被獨(dú)立執(zhí)行的操作標(biāo)識(shí)，標(biāo)識(shí)對(duì)象被操作的順序，id值越大，先被執(zhí)行，如果相同，執(zhí)行順序從上到下select_type查詢(xún)中每個(gè)select 字句的類(lèi)型table被操作的對(duì)象名稱(chēng)，通常是表名，但有其他格式partitions匹配的分區(qū)信息(對(duì)于非分區(qū)表值為NULL)type連接操作的類(lèi)型possible_keys可能用到的索引key優(yōu)化器實(shí)際使用的索引(最重要的列) 從最好到最差的連接類(lèi)型為const、eq_reg、ref、range、index和ALL。當(dāng)出現(xiàn)ALL時(shí)表示當(dāng)前SQL出現(xiàn)了“壞味道”key_len被優(yōu)化器選定的索引鍵長(zhǎng)度，單位是字節(jié)ref表示本行被操作對(duì)象的參照對(duì)象，無(wú)參照對(duì)象為NULLrows查詢(xún)執(zhí)行所掃描的元組個(gè)數(shù)（對(duì)于innodb，此值為估計(jì)值）filtered條件表上數(shù)據(jù)被過(guò)濾的元組個(gè)數(shù)百分比extra執(zhí)行計(jì)劃的重要補(bǔ)充信息，當(dāng)此列出現(xiàn)Using filesort , Using temporary 字樣時(shí)就要小心了，很可能SQL語(yǔ)句需要優(yōu)化
通過(guò)執(zhí)行計(jì)劃我們就可以確定優(yōu)化方案，優(yōu)化一處后再回過(guò)頭來(lái)觀察執(zhí)行計(jì)劃，如此往復(fù)循環(huán)直到找到最優(yōu)目標(biāo)為止。
下面給出一段有問(wèn)題的SQL具體操作一下。
SQL優(yōu)化案例
慢查詢(xún)
1、表結(jié)構(gòu)如下：
scss復(fù)制代碼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、有問(wèn)題的查詢(xún)SQL
css復(fù)制代碼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 三張表關(guān)聯(lián)，查詢(xún)用戶(hù)17 在當(dāng)前時(shí)間前后10個(gè)小時(shí)的訂單情況，并根據(jù)訂單創(chuàng)建時(shí)間升序排列
優(yōu)化步驟
1、先查看各表數(shù)據(jù)量
2、查看原執(zhí)行時(shí)間，總耗時(shí)0.21s
3、查看原執(zhí)行計(jì)劃
4、通過(guò)觀察執(zhí)行計(jì)劃和SQL語(yǔ)句，確定初步優(yōu)化方案
SQL中 where條件字段類(lèi)型要跟表結(jié)構(gòu)一致，表中
user_id為varchar(50)類(lèi)型，實(shí)際SQL用的int類(lèi)型，存在隱式轉(zhuǎn)換，也未添加索引。將b和c表user_id字段改成int類(lèi)型。因存在b表和c表關(guān)聯(lián)，將b和c表
user_id創(chuàng)建索引因存在a表和b表關(guān)聯(lián)，將a和b表
seller_name字段創(chuàng)建索引利用復(fù)合索引消除臨時(shí)表和排序
初步優(yōu)化的SQL：
sql復(fù)制代碼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`);
查看優(yōu)化后的執(zhí)行時(shí)間
初步優(yōu)化后執(zhí)行速度提升了20倍，是否還能繼續(xù)優(yōu)化呢？
5、繼續(xù)查看優(yōu)化后的執(zhí)行計(jì)劃
這里只看到查詢(xún)需要掃描的元素比較大，不過(guò)還看到了有兩處告警信息，直接查看告警信息
sql復(fù)制代碼show warnings
Cannot use range access on index ‘idx_sellname_gmt_sellid’ due to type or collation conversion on field ‘get_create’，這句話是告訴你由于gmt_create列發(fā)生了類(lèi)型轉(zhuǎn)換所以無(wú)法走索引。
查看SQL建表語(yǔ)句發(fā)現(xiàn)gmt_create字段被設(shè)計(jì)成了varchar類(lèi)型，在SQL查詢(xún)時(shí)需要轉(zhuǎn)化成時(shí)間格式做查詢(xún)，確實(shí)不能走索引。
所以需要調(diào)整一下gmt_create字段格式
sql復(fù)制代碼alter table a modify "gmt_create" datetime DEFAULT NULL;
6、修改字段后再來(lái)查看執(zhí)行時(shí)間
執(zhí)行速度非常完美。
7、再觀察優(yōu)化后的執(zhí)行計(jì)劃
可以看到執(zhí)行計(jì)劃也很完美，至此SQL優(yōu)化結(jié)束。
SQL優(yōu)化小結(jié)
這里給大家總結(jié)一下優(yōu)化SQL的套路
查看執(zhí)行計(jì)劃 explain
如果有告警信息，查看告警信息 show warnings;
查看SQL涉及的表結(jié)構(gòu)和索引信息
根據(jù)執(zhí)行計(jì)劃，思考可能的優(yōu)化點(diǎn)
按照可能的優(yōu)化點(diǎn)執(zhí)行表結(jié)構(gòu)變更、增加索引、SQL改寫(xiě)等操作
查看優(yōu)化后的執(zhí)行時(shí)間和執(zhí)行計(jì)劃
如果優(yōu)化效果不明顯，重復(fù)第四步操作
