京東二面：日常工作中，你是如何優(yōu)化SQL的？

作者：撿田螺的小男孩 2024-10-29 09:42:50

如果大家平時(shí)有優(yōu)化過生產(chǎn)的慢SQL，有自己的一套排查那一套最好哈。如果沒有的話，可以在自己搞個(gè)深分頁，或者因?yàn)閿?shù)據(jù)量、或者因?yàn)闆]加索引等原因，導(dǎo)致的慢SQL，然后按照這個(gè)思路去排查一遍。

大家好，我是田螺。

我們?nèi)ッ嬖嚨臅r(shí)候，經(jīng)常被問到，日常工作中，是如何優(yōu)化SQL的。今天跟大家再聊聊哈。

這里應(yīng)該如何去回答呢？可以從各種不同維度的，今天我再換個(gè)角度。

加索引
避免常見的索引不生效場(chǎng)景
避免返回不必要的數(shù)據(jù)
減少不必要的邏輯
分批量進(jìn)行思想
讀寫分離
優(yōu)化sql結(jié)構(gòu)
分庫分表
性能優(yōu)化分析神器—explain
慢SQL排查思路

1. 加索引

很多時(shí)候，我們的慢查詢，都是因?yàn)闅v史原因沒有加索引，或者忘記加索引導(dǎo)致的。如果沒有加索引的話，會(huì)導(dǎo)致全表掃描的。因此，應(yīng)考慮在where的條件列，建立索引，盡量避免全表掃描。

反例：

select * from user_info where name ='撿田螺的小男孩公眾號(hào)' ;

正例：

//添加索引
alter table user_info add index idx_name (name);

2. 避免常見的索引不生效場(chǎng)景

我之前整理了常見的十種索引不生效的場(chǎng)景，大家可以看看：

隱式的類型轉(zhuǎn)換，索引失效
查詢條件包含or，可能導(dǎo)致索引失效
like通配符可能導(dǎo)致索引失效
查詢條件不滿足聯(lián)合索引的最左匹配原則
在索引列上使用mysql的內(nèi)置函數(shù)
對(duì)索引進(jìn)行列運(yùn)算(如，+、-、*、/)
索引字段上使用(!=或者<>)，索引可能失效
索引字段上使用is null，is not null，索引可能失效
左右連接，關(guān)聯(lián)的字段編碼格式不一樣
優(yōu)化器選錯(cuò)了索引

3. 避免返回不必要的數(shù)據(jù)

這個(gè)點(diǎn)，我在昨天的文章，其實(shí)就提到一個(gè)點(diǎn)，包括盡量使用limit，避免不必要的返回。

其實(shí)這不僅僅是一個(gè)點(diǎn)，而是一種思想，就是要什么查什么，而不是返回一些不必要的數(shù)據(jù)。還有：查詢SQL盡量不要使用select *，而是select具體字段。也是這種思想。

反例子：

select * from employee;

正例子：

select id，name, age from employee;

select具體字段，節(jié)省資源、減少網(wǎng)絡(luò)開銷。
select * 進(jìn)行查詢時(shí)，很可能就不會(huì)使用到覆蓋索引了，就會(huì)造成回表查詢。

4. 減少不必要的邏輯

其實(shí)，盡量用 union all 替換 union，就是這種思想。

如果我們明知道，檢索結(jié)果中不會(huì)有重復(fù)的記錄，推薦union all 替換 union。

因?yàn)椋?/p>

如果使用union，不管檢索結(jié)果有沒有重復(fù)，都會(huì)嘗試進(jìn)行合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果已知檢索結(jié)果沒有重復(fù)記錄，使用union all 代替union，這樣會(huì)提高效率。

5. 分批量進(jìn)行思想

我們更推薦批量查詢、插入、刪除。

反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批進(jìn)行
insert into user(name,age) values
<foreach collectinotallow="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：

批量插入性能好，更加省時(shí)間
打個(gè)比喻: 假如你需要搬一萬塊磚到樓頂,你有一個(gè)電梯,電梯一次可以放適量的磚（最多放500）,你可以選擇一次運(yùn)送一塊磚,也可以一次運(yùn)送500,你覺得哪個(gè)時(shí)間消耗大?

6. 讀寫分離

一般情況我們的數(shù)據(jù)庫架構(gòu)，都要做主從的，然后進(jìn)行讀寫分離。主庫主要負(fù)責(zé)寫，和一些實(shí)時(shí)性比較高的讀。而從庫就負(fù)責(zé)讀實(shí)時(shí)性要求不高的請(qǐng)求。

圖片

這樣的話，我們不用所有請(qǐng)求都到主庫，大大降低了主庫的壓力。你試想一下，如果所有讀請(qǐng)求都到主庫，查詢壓力肯定很大，處理也會(huì)相對(duì)慢一點(diǎn)。

7. 優(yōu)化sql結(jié)構(gòu)、邏輯

有些時(shí)候，優(yōu)化SQL結(jié)構(gòu)，都能有一些預(yù)想不到的優(yōu)化效果。

假設(shè)我們有個(gè)客戶表和一個(gè)訂單表。其中訂單表有10萬記錄，客戶表只有1000行記錄。

現(xiàn)在要查詢下單過的客戶信息，可以這樣寫：

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

in 查詢會(huì)先執(zhí)行內(nèi)部查詢部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。

也可以這樣實(shí)現(xiàn)：

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 會(huì)逐行掃描 customers 表（即小表），對(duì)每一行 c.id，在 orders 表（大表）中檢查是否有 customer_id = c.id 的記錄。

因?yàn)閛rders表的數(shù)據(jù)量比較大，因此這里用exists效果會(huì)相對(duì)更好一點(diǎn)。其實(shí)這就是小表驅(qū)動(dòng)大表的思想。我們也只是調(diào)整SQL結(jié)構(gòu)，用exists去替換in，優(yōu)化效果也是比較明顯的。

8. 分庫分表

如果單表的數(shù)據(jù)量很大，達(dá)到百萬甚至千萬級(jí)別，我們這種時(shí)候，就是加了索引，可能效果也不是很明顯。這時(shí)候我們可以考慮分庫分表啦~~

分庫分表一般都是依賴客戶號(hào)、用戶Id、或者時(shí)間來拆分。但是需要注意一下，分庫分表存在的一些一些問題：

事務(wù)問題
跨庫關(guān)聯(lián)JOIN
排序問題
分頁問題
分布式ID選擇

9. 性能優(yōu)化分析神器—explain

之前我寫SQL習(xí)慣的時(shí)候，有提到explain，就是每次寫完查詢SQL，都用explain看一下它的執(zhí)行計(jì)劃。

有些面試官會(huì)單獨(dú)問這個(gè)，我們可以走面試官的路，讓面試官無路可走。在回答SQL優(yōu)化的時(shí)候，就把這個(gè)回答了。

一般在使用explain的時(shí)候，我們要關(guān)注:type、rows、filtered、extra、key。

9.1 type

type表示連接類型，查看索引執(zhí)行情況的一個(gè)重要指標(biāo)。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：這種類型要求數(shù)據(jù)庫表中只有一條數(shù)據(jù)，是const類型的一個(gè)特例，一般情況下是不會(huì)出現(xiàn)的。
const：通過一次索引就能找到數(shù)據(jù)，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關(guān)聯(lián)查詢
ref : 常用于非主鍵和唯一索引掃描。
ref_or_null：這種連接類型類似于ref，區(qū)別在于MySQL會(huì)額外搜索包含NULL值的行
index_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個(gè)以上的索引。
unique_subquery：類似于eq_ref，條件用了in子查詢
index_subquery：區(qū)別于unique_subquery，用于非唯一索引，可以返回重復(fù)值。
range：常用于范圍查詢，比如：between ... and 或 In 等操作
index：全索引掃描
ALL：全表掃描

9.2 rows

該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數(shù)。對(duì)于InnoDB表，此數(shù)字是估計(jì)值，并非一定是個(gè)準(zhǔn)確值。

9.3 filtered

該列是一個(gè)百分比的值，表里符合條件的記錄數(shù)的百分比。簡單點(diǎn)說，這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在經(jīng)過過濾后，剩下滿足條件的記錄數(shù)量的比例。

9.4 extra

該字段包含有關(guān)MySQL如何解析查詢的其他信息，它一般會(huì)出現(xiàn)這幾個(gè)值：

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會(huì)出現(xiàn)。一般見于order by語句
Using index ：表示是否用了覆蓋索引。
Using temporary: 表示是否使用了臨時(shí)表,性能特別差，需要重點(diǎn)優(yōu)化。一般多見于group by語句，或者union語句。
Using where : 表示使用了where條件過濾.
Using index condition：MySQL5.6之后新增的索引下推。在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過濾，而不是在服務(wù)層過濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。