前言

查詢(xún)優(yōu)化本就不是一蹴而就的，需要學(xué)會(huì)使用對(duì)應(yīng)的工具、借鑒別人的經(jīng)驗(yàn)來(lái)對(duì)SQL進(jìn)行優(yōu)化，并且提升自己。

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

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

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

**本文所有操作均在MySQL8.0.12**

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

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

主鍵索引：pk_column_column

唯一索引：uk_column_column

普通索引：idx_column_column

二、索引失效原因

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

1.帶頭大哥不能死

這局經(jīng)典的語(yǔ)句就是涵蓋創(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 。

查詢(xún)條件必須帶上u_name這一列。

2.不在索引列上做任何操作

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

3.倆邊類(lèi)型不等

例如建立了索引idx_user_name，name字段類(lèi)型為varchar

在查詢(xún)時(shí)使用where name = kaka，這樣的查詢(xún)方式會(huì)直接造成索引失效。

正確的用法為where name = "kaka" 。

4.不適當(dāng)?shù)膌ike查詢(xún)會(huì)導(dǎo)致索引失效

創(chuàng)建索引為idx_user_name

執(zhí)行語(yǔ)句為select * from user where name like "kaka%";可以命中索引。

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

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

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

創(chuàng)建索引為idx_user_name_age_sex

執(zhí)行語(yǔ)句select * from user where name = 'kaka' and age > 11 and sex = 1;

上面這條sql語(yǔ)句只會(huì)命中name和age索引，sex索引會(huì)失效。

復(fù)合索引失效需要查看key_len的長(zhǎng)度即可。

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

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

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

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

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

1.id 這列就是查詢(xún)的編號(hào)，如果查詢(xún)語(yǔ)句中沒(méi)有子查詢(xún)或者聯(lián)合查詢(xún)這個(gè)標(biāo)識(shí)就一直是1。

如存在子查詢(xún)或者聯(lián)合查詢(xún)這個(gè)編號(hào)會(huì)自增。

2.select_type

最常見(jiàn)的類(lèi)型就是SIMPLE和PRIMARY，此列知道就行了。

3.table

理解為表名即可

4. **type

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

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

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

5.possible_keys

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

6. **key

優(yōu)化器從possible_keys中命中的索引

7.key_len

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

8.ref

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

如果是連接查詢(xún)，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段。

如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func。

9. **rows

這是mysql估算的需要掃描的行數(shù)（不是精確值）。

這個(gè)值非常直觀(guān)顯示 SQL 的效率好壞, 原則上 rows 越少越好。

10.filtered

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

11. **extra

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

• Using index：使用了覆蓋索引，查詢(xún)列都為索引字段
• Using where：使用了where語(yǔ)句
• Using temporary ：查詢(xún)結(jié)果進(jìn)行排序的時(shí)候使用了一張臨時(shí)表
• Using filesort ：對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序
• Using index condition：使用了索引下推，關(guān)于索引下推可以查看咔咔之前文章MySQL索引一文

12.總結(jié)

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

• type優(yōu)化目標(biāo)至少達(dá)到range級(jí)別，要求是ref級(jí)別，如果可以consts最好。
• key是查詢(xún)使用到的索引，如果此列為空，要么未建立索引，要么索引失效。
• rows是這條SQL語(yǔ)句掃描的行數(shù)，越少越好。
• extra：此列為擴(kuò)展列，如果出現(xiàn)臨時(shí)表、文件排序則需要優(yōu)化。

四、SQL優(yōu)化殺手锏之 慢查詢(xún)

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

查看慢查詢(xún)是否打開(kāi)

查看是否記錄沒(méi)有使用索引的SQL語(yǔ)句

開(kāi)啟慢查詢(xún)、開(kāi)啟記錄沒(méi)有使用到索引的SQL語(yǔ)句

set global log_queries_not_using_idnexes='on';

set global log_queries_not_using_indexes='on';

查詢(xún)以上倆個(gè)配置是否打開(kāi)

設(shè)置慢查詢(xún)時(shí)間，這個(gè)時(shí)間由自己把控，一般1s即可 set globle long_query_time=1;

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

查看慢查詢(xún)存儲(chǔ)位置

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

上圖中一般需要主要觀(guān)察的是Query_time、SQL語(yǔ)句內(nèi)容。

以上就是關(guān)于如何使用慢查詢(xún)來(lái)查看項(xiàng)目中出現(xiàn)問(wèn)題的SQL語(yǔ)句。

五、優(yōu)化大法

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

• 禁止使用select *，需要什么字段查詢(xún)什么字段
• where字段設(shè)置索引
• group by、order by字段設(shè)置索引
• 舍棄offset，limit分頁(yè)，使用延遲關(guān)聯(lián)來(lái)實(shí)現(xiàn)分頁(yè)(數(shù)據(jù)量不大時(shí)可不用)
• 寫(xiě)分頁(yè)時(shí)當(dāng)count為0時(shí)，直接返回避免執(zhí)行分頁(yè)語(yǔ)句
• 利用覆蓋索引進(jìn)行查詢(xún)避免回表
• 建立復(fù)合索引時(shí)區(qū)分度最高的放在最左側(cè)
• 統(tǒng)計(jì)數(shù)據(jù)行數(shù)只用count(*)，別整的花里胡哨的
• 關(guān)于in和exist，如果查詢(xún)的倆個(gè)表大小一致則性能差別可忽略，如果子查詢(xún)表大用exist，否則使用in
• 查詢(xún)一行數(shù)據(jù)時(shí)加上limit 1
• 選擇合理的數(shù)據(jù)類(lèi)型，在滿(mǎn)足條件下數(shù)據(jù)類(lèi)型越小越好
• 聯(lián)合查詢(xún)join最多三個(gè)表，并且需要join的字段數(shù)據(jù)類(lèi)型保持一致
• in操作能避免盡量避免，無(wú)法避免的情況下in元素控制在1000以?xún)?nèi)
• 數(shù)據(jù)更新頻繁，區(qū)分度不高的列不適合建立索引
• explain中的type至少要達(dá)到range，要求為ref
• 聯(lián)合索引滿(mǎn)足最左側(cè)原則

六、總結(jié)

到此這篇關(guān)于MySQL查詢(xún)優(yōu)化必備知識(shí)點(diǎn)的文章就介紹到這了,更多相關(guān)MySQL查詢(xún)優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！