【51CTO.com原創(chuàng)稿件】查詢優(yōu)化本就不是一蹴而就的，需要學(xué)會(huì)使用對(duì)應(yīng)的工具、借鑒別人的經(jīng)驗(yàn)來對(duì) SQL 進(jìn)行優(yōu)化，并且提升自己。

[[384738]]

圖片來自 Pexels

先來鞏固一下索引的優(yōu)點(diǎn)，檢索數(shù)據(jù)快、查詢穩(wěn)定、存儲(chǔ)具有順序性避免服務(wù)器建立臨時(shí)表、將隨機(jī)的 I/O 變?yōu)橛行虻?I/O。

但索引一旦創(chuàng)建的不規(guī)范就會(huì)造成以下問題，占用額外空間，浪費(fèi)內(nèi)存，降低數(shù)據(jù)的增、刪、改性能。

所以只有在理解索引數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上才能創(chuàng)建出高效的索引。本文所有操作均在 MySQL 8.0.12。

創(chuàng)建索引規(guī)范

在學(xué)習(xí)索引優(yōu)化之前，需要對(duì)創(chuàng)建索引的規(guī)范有一定的了解，此規(guī)范來自于阿里巴巴開發(fā)手冊(cè)。

主鍵索引：pk_column_column。

唯一索引：uk_column_column。

普通索引：idx_column_column。

索引失效原因

創(chuàng)建索引需知道在什么情況下索引會(huì)失效，只有了解索引失效的原因，在創(chuàng)建索引時(shí)才不會(huì)出現(xiàn)一些已知錯(cuò)誤。

帶頭大哥不能死

這局經(jīng)典的語句就是涵蓋創(chuàng)建索引時(shí)一定要符合最左側(cè)原則。

例如表結(jié)構(gòu)為 u_id，u_name，u_age，u_sex，u_phone，u_time，創(chuàng)建索引為 idx_user_name_age_sex。

查詢條件必須帶上 u_name 這一列。

不在索引列上做任何操作

不在索引列上做任何計(jì)算、函數(shù)、自動(dòng)或者手動(dòng)的類型轉(zhuǎn)換，否則會(huì)進(jìn)行全表掃描。簡(jiǎn)而言之不要在索引列上做任何操作。

倆邊類型不等

例如建立了索引 idx_user_name，name 字段類型為 varchar。在查詢時(shí)使用 where name = kaka，這樣的查詢方式會(huì)直接造成索引失效。

正確的用法為 where name = “kaka”。

不適當(dāng)?shù)?like 查詢會(huì)導(dǎo)致索引失效

創(chuàng)建索引為 idx_user_name，執(zhí)行語句為 select * from user where name like “kaka%”;可以命中索引。

執(zhí)行語句為 select name from user where name like “%kaka”;可以使用到索引(僅在 8.0 以上版本)。

執(zhí)行語句為 select * from user where name like ‘’%kaka";會(huì)直接導(dǎo)致索引失效。

范圍條件之后的索引會(huì)失效

創(chuàng)建索引為：

idx_user_name_age_sex 

執(zhí)行語句：

select * from user where name = ‘kaka’ and age > 11 and sex = 1; 

上面這條 SQL 語句只會(huì)命中 name 和 age 索引，sex 索引會(huì)失效。復(fù)合索引失效需要查看 key_len 的長度即可。

總結(jié)：% 在后邊會(huì)命令索引，當(dāng)使用了覆蓋索引時(shí)任何查詢方式都可命中索引。

以上就是我關(guān)于索引失效會(huì)出現(xiàn)的原因總結(jié)，在很多文章中沒有標(biāo)注 MySQL 版本，所以你有可能會(huì)看到 is null 、or 索引會(huì)失效的結(jié)論。

SQL 優(yōu)化殺手锏之 Explain

在寫完 SQL 語句之后必須要做的一件事情就是使用 Explain 進(jìn)行 SQL 語句檢測(cè)，看是否命中索引。

上圖就是使用 Explain 輸出格式，接下來將會(huì)對(duì)輸出格式進(jìn)行簡(jiǎn)單的解釋：

①id：這列就是查詢的編號(hào)，如果查詢語句中沒有子查詢或者聯(lián)合查詢這個(gè)標(biāo)識(shí)就一直是 1。如存在子查詢或者聯(lián)合查詢這個(gè)編號(hào)會(huì)自增。

②select_type：最常見的類型就是 SIMPLE 和 PRIMARY，此列知道就行了。

③table：理解為表名即可。

④**type：此列是在優(yōu)化 SQL 語句時(shí)最需要關(guān)注的列之一，此列顯示了查詢使用了何種類型。

以下排序從最優(yōu)到最差：

• system：表內(nèi)只有一行數(shù)據(jù)。
• const：最多只會(huì)有一條記錄匹配，常用于主鍵或者唯一索引為條件查詢。
• eq_ref：當(dāng)連接使用的索引為主鍵和唯一時(shí)會(huì)出現(xiàn)。
• ref：使用普通索引 = 或 <=> 運(yùn)算符進(jìn)行比較將會(huì)出現(xiàn)。
• fulltext：使用全文索引。
• ref_or_null：跟 ref 類型類似，只是增加了 null 值的判斷，實(shí)際用的不多。語句為 where name = ‘kaka’ and name is null，name 為普通索引。
• index_merge：查詢語句使用了倆個(gè)以上的索引，常見在使用 and、or 會(huì)出現(xiàn)，官方文檔將此類型放在 ref_or_null 之后，但是在很多的情況下由于讀取索引過多性能有可能還不如 range。
• unique_subquery：用于 where 中的 in 查詢，完全替換子查詢，效率更高。語句為 value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery：子查詢中的返回結(jié)果字段組合是一個(gè)索引(或索引組合)，但不是一個(gè)主鍵或唯一索引。
• range：索引范圍查詢，常見于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等運(yùn)算符的查詢中。
• index：索引全表掃描，把索引從頭到尾掃一遍。
• all：全表掃描，性能最差。

⑤possible_keys：此列顯示的可能會(huì)使用到的索引。

⑥**key：優(yōu)化器從 possible_keys 中命中的索引。

⑦key_len：查詢用到的索引長度(字節(jié)數(shù))，key_len 只計(jì)算 where 條件用到的索引長度，而排序和分組就算用到了索引，也不會(huì)計(jì)算到 key_len 中。

⑧ref：如果是使用的常數(shù)等值查詢，這里會(huì)顯示 const。

如果是連接查詢，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段。如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為 func。

⑨**rows：這是 MySQL 估算的需要掃描的行數(shù)(不是精確值)。這個(gè)值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。

⑩filtered：此列表示存儲(chǔ)引擎返回的數(shù)據(jù)在 server 層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)。

⑪**extra：在大多數(shù)情況下會(huì)出現(xiàn)以下幾種情況。

• Using index：使用了覆蓋索引，查詢列都為索引字段。
• Using where：使用了 where 語句。
• Using temporary：查詢結(jié)果進(jìn)行排序的時(shí)候使用了一張臨時(shí)表。
• Using filesort：對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序。
• Using index condition：使用了索引下推。

關(guān)于索引下推可以查看我之前的一篇文章《MySQL 索引》。

總結(jié)：以上就是關(guān)于 Explain 所有列的說明，在平時(shí)開發(fā)的過程中，一般只會(huì)關(guān)注 type、key、rows、extra 這四列。

type 優(yōu)化目標(biāo)至少達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，如果可以 consts 最好。key 是查詢使用到的索引，如果此列為空，要么未建立索引，要么索引失效。

rows 是這條 SQL 語句掃描的行數(shù)，越少越好。extra：此列為擴(kuò)展列，如果出現(xiàn)臨時(shí)表、文件排序則需要優(yōu)化。

SQL 優(yōu)化殺手锏之慢查詢

上文說到了可以直接使用 Explain 來分析自己的 SQL 語句是否合理，接下來再聊一個(gè)點(diǎn)那就是慢查詢。

查看慢查詢是否打開：

查看是否記錄沒有使用索引的 SQL 語句：

開啟慢查詢、開啟記錄沒有使用到索引的 SQL 語句：

set global log_queries_not_using_idnexes=‘on’; 
set global log_queries_not_using_indexes=‘on’; 

查詢以上倆個(gè)配置是否打開：

設(shè)置慢查詢時(shí)間，這個(gè)時(shí)間由自己把控，一般 1s 即可：

set globle long_query_time=1; 

如果查看這個(gè)時(shí)間沒有變，則關(guān)于客戶端在重新連接一次即可。

 查看慢查詢存儲(chǔ)位置：

然后隨便執(zhí)行一條不執(zhí)行索引的語句即可在這個(gè)日志中查看到此語句：

 上圖中一般需要主要觀察的是 Query_time、SQL 語句內(nèi)容。以上就是關(guān)于如何使用慢查詢來查看項(xiàng)目中出現(xiàn)問題的 SQL 語句。

優(yōu)化大法

此處跟大家聊一些常用的 SQL 語句優(yōu)化方案，以上的倆個(gè)工具要好好的利用，輔助我們進(jìn)行打怪：

①禁止使用 select *，需要什么字段查詢什么字段。

②where 字段設(shè)置索引。

③group by、order by 字段設(shè)置索引。

④舍棄 offset，limit 分頁，使用延遲關(guān)聯(lián)來實(shí)現(xiàn)分頁(數(shù)據(jù)量不大時(shí)可不用)。

⑤寫分頁時(shí)當(dāng) count 為 0 時(shí)，直接返回避免執(zhí)行分頁語句。

⑥利用覆蓋索引進(jìn)行查詢避免回表。

⑦建立復(fù)合索引時(shí)區(qū)分度最高的放在最左側(cè)。

⑧統(tǒng)計(jì)數(shù)據(jù)行數(shù)只用 count(*)，別整的花里胡哨的。

⑨關(guān)于 in 和 exist，如果查詢的倆個(gè)表大小一致則性能差別可忽略，如果子查詢表大用 exist，否則使用 in。

⑩查詢一行數(shù)據(jù)時(shí)加上 limit 1。

⑪選擇合理的數(shù)據(jù)類型，在滿足條件下數(shù)據(jù)類型越小越好。

⑫聯(lián)合查詢 join 最多三個(gè)表，并且需要 join 的字段數(shù)據(jù)類型保持一致。

⑬in 操作能避免盡量避免，無法避免的情況下 in 元素控制在 1000 以內(nèi)。

⑭數(shù)據(jù)更新頻繁，區(qū)分度不高的列不適合建立索引。

⑮explain 中的 type 至少要達(dá)到 range，要求為 ref。

⑯聯(lián)合索引滿足最左側(cè)原則。

作者：咔咔

編輯：陶家龍

征稿：有投稿、尋求報(bào)道意向技術(shù)人請(qǐng)?zhí)砑有【幬⑿?gordonlonglong

【51CTO原創(chuàng)稿件，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文作者和出處為51CTO.com】