目錄

Explain關鍵字 索引性能分析

Id ——select的查詢序列號

Select_type——select查詢的類型

Table——表名稱

Type——select的連接類型

Possible_key ——顯示可能應用在這張表的索引

Key——實際用到的索引

Key_len——實際索引使用到的字節(jié)數(shù)

Ref??? ——索引命中的列或常量

Rows——預計select語句要檢查的行數(shù)

Filtered——返回結果的行數(shù)占讀取行數(shù)的百分比

Extra——顯示額外的信息

索引的使用規(guī)則

SQL提示

最左前綴法則

索引失效情況

索引的設計原則

Explain關鍵字 索引性能分析

Explain可以應用于SELECT、DELETE、INSERT、REPLACE、UPDATE語句

通過Explain關鍵字可以看到SELECT語句的執(zhí)行計劃，即可以查看到MySQL如何處理SELECT語句，通過Explain顯示的結果來決定如何優(yōu)化

具體的作用有

1. 查看表的讀取順序
2. 查看此語句可以使用哪些索引
3. 此語句實際使用了哪些索引
4. 查看此語句查詢了多少行數(shù)據(jù)

explain語法

在任意的SELECT語句之前加上關鍵字 Explain或者Desc

EXPLAIN SELECT * FROM 表名;

使用Explain后返回的結果

查詢結果的各個字段

Id ——select的查詢序列號

表示查詢中SQL執(zhí)行的順序；id相同時的執(zhí)行順序為從上到下；id不同時值越大越先執(zhí)行

對于單表查詢，查詢一次一般會產(chǎn)生一個id的一行信息

explain select * from user;

對于多表查，查詢一次一般會產(chǎn)生相同id的多行信息

?explain select * from career,user where career.id = user.career_id;

對于子查詢，查詢一次一般會產(chǎn)生不同id的多行信息

explain select * from user where user.career_id = (select id from career where id=1);

Select_type——select查詢的類型

常見的取值有

SIMPLE：簡單的select查詢類型；查詢語句中不包含子查詢或UNION

PRIMARY：當查詢中包含子查詢或UNION時，即外層的查詢?yōu)榇瞬樵冾愋?/span>

SUBQUERY：在SELECT或WHERE中包含了子查詢時會被標記為此查詢類型

DERIVED：在FROM列表中包含的子查詢被標記為此查詢類型（MySQL會將此子查詢的查詢結果作為臨時表—派生表）

explain

select * from

?(select origo,count(*) as number from staff1 group by origo) as emp ???

?where emp.number > 2; #根據(jù)居住地分組，并查詢居住地人數(shù)大于2的（派生表的別名為emp）

UNION：在UNION中的第二個和隨后的SELECT語句被標記為UNION（如果UNION被FROM子句中的子查詢包含，則它的第一個SELECT會被標記為DERIVED）

explain

select origo,count(*) as number? from staff2 group by origo

union

select origo,count(*) as number? from staff1 group by origo;

explain

select origo,count(*) as number? from staff2 group by origo

union

select * from (select origo,count(*) as number from staff1 group by origo) as emp? where emp.number > 2;

?UNION RESULT：表示對應UNION的結果（UNION和UNRESULT一般會成對出現(xiàn)）

Table——表名稱

顯示這一行的數(shù)據(jù)是關于哪張表的，顯示結果可能為表的名稱、<derivedX>、<unionX1，X1>等

<derivedX>

當from子句中有子查詢時，table列為<derivedX>的格式（x為id值），對應子查詢返回的臨時表（派生表）

<unionX1，X1>

當存在union時，union result的table列為<unionX1，X1>的格式；X1和X2表示參與union的表的id序號

Type——select的連接類型

select的連接類型是查看索引執(zhí)行情況的一個重要指標，就是MySQL如何查找數(shù)據(jù)表中的記錄

連接類型的性能由好到差為NULL、system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all

重點關注的是：NULL、system、const、eq_ref、ref、range、index、all

在優(yōu)化時盡量優(yōu)化為性能好的（當查詢時不查詢?nèi)魏伪頃r才會出現(xiàn)NULL）

主鍵或唯一索引查詢會出現(xiàn)const，使用非唯一性索引查詢時會出現(xiàn)ref

一般我們最好保證查詢時type達到range、ref級別

All和Index都是讀全表，只是Index讀的是索引樹，All讀的是數(shù)據(jù)表

不同連接類型代表的含義（通過Staff1表來模擬現(xiàn)象）

Fulltext：?????? 當查詢使用到全文索引時的連接類型

Ref_or_null： 類似于ref，也是非唯一性索引掃描；不過MySQL還會掃描哪些行包含了NULL

Index_merge： 表示使用了索引合并優(yōu)化（即一個中使用到了多個索引）

Unique_subquery：類似于eq_ref，唯一性索引掃描；但是使用了IN查詢，并且子查詢查詢字段為主鍵或唯一索引

Index_subquery： ?類似于unique_subquery；不過子查詢查詢字段為非唯一索引

Null： ??查詢時不查詢?nèi)魏伪恚∕ySQL在優(yōu)化階段會分析查詢語句，以此來判斷是否需要訪問表）或者在查詢的值在此字段找不到，并且此字段建立了唯一索引

explain select min(id) from staff1; #查看主鍵的最小id

System：表只有一行記錄；是Cost的特殊情況，平時不會出現(xiàn)可忽略

Const： 表示通過索引一次就找到了要查詢的記錄（一般存在于單表查詢時，主鍵或唯一索引作為查詢條件）

explain select * from staff1 where number=2021004;

?Eq_ref： 唯一性索引掃描；對于每個索引鍵，表中只有一條記錄與之匹配；（一般存在于多表查詢時，使用主鍵或唯一索引掃描作為查詢條件）

explain select s1.*,a1.* from staff1 s1, account a1 where s1.id = a1.id;

?Ref：????? 非唯一性索引掃描；返回匹配某個單獨值的所有行，可能會找到多個符合條件的行，屬于查找和掃描的混合體（用于常規(guī)索引、聯(lián)合索引情況）

explain select * from staff1 where origo='重慶';

Range：范圍查詢；當給一個字段添加索引之后，使用范圍作為此字段的條件進行數(shù)據(jù)查詢時的連接類型（般就是在where語句中出現(xiàn)了between、<、>、in等的查詢）

explain select * from staff1 where number>2021001;

Index：index類型值遍歷索引樹（通過遍歷索引樹來查找數(shù)據(jù)，需要查找的字段都已經(jīng)建立了索引-主鍵索引、唯一索引、常規(guī)索引等）

explain select id,number from staff1;

ALL：將遍歷全表已找到匹配的行，沒有使用索引

Possible_key ——顯示可能應用在這張表的索引

該值為一個或多個

此字段顯示的索引不一定會被查詢使用到，可能會出現(xiàn)索引失效的問題

當Select語句發(fā)現(xiàn)可以使用多個索引的時候，可以通過SQL提示來建議Mysql語句使用指定的索引，可以避免SQL使用了性能比較低的索引（例如如果同時存在唯一索引和常規(guī)索引，可以建立SQL使用唯一索引）

Key——實際用到的索引

如果沒有使用索引，則為NULL

哪些情況會導致有可用索引但是實際上沒有使用到索引呢？

1、對于聯(lián)合索引來說，沒有遵守最左前綴法則

2、范圍查詢時使用到>或<，會導致范圍查詢右側的列索引失效

3、在Where之后的索引列上進行運算操作（包含函數(shù)、比較運算符、謂詞等）

4、字符串類型字段的值使用時，如果不加引號，存在隱式類型轉換，索引將失效

5、當對頭部進行模糊匹配時，索引會失效（即Like(%字符)或者Like(_字符)）

6、用or分隔開的條件，如果or前條件中的列有索引，而后面的列中沒有索引，那么or前面的索引不會被用到

7、數(shù)據(jù)分布影響；如果MySQL評估使用索引查詢比全表查詢更慢，則不使用索引，使用全表查詢

如何規(guī)避索引失效呢？————具體在索引的優(yōu)化介紹

1、聯(lián)合索引遵守最左前綴法則，在創(chuàng)建聯(lián)合索引時盡量將使用頻率高的字段放在最左端

2、在范圍查詢時盡量使用過>=或者<=來規(guī)避范圍查詢

3、盡量不對索引列進行運算操作

4、在使用屬于字符串類型的字段時，需要對其值加上引號

5、盡量使用尾部模糊匹配來代替頭部模糊匹配；當對尾部進行模糊匹配時，則索引不會失效（即Like(字符%)或者Like(字符_)）

6、只有當or前后都是用到索引時，索引才會失效

Key_len——實際索引使用到的字節(jié)數(shù)

表明了在索引中使用的字節(jié)數(shù)，通過此值可以大致估算出使用了索引中的哪些列

Key_len的計算規(guī)則

當字段允許為Null時，比不允許為Null大1個字節(jié)

不同的數(shù)據(jù)類型，占用的字節(jié)數(shù)時不同的，詳情可以參考以下官方文檔（介紹的是不允許為空的情況）

MySQL :: MySQL 8.0 Reference Manual :: 11.7 Data Type Storage Requirements

對于字符串數(shù)據(jù)類型來說，其占用的字節(jié)數(shù)還跟使用的字符編碼有關

GBK?????????????? 2字節(jié)

UTF8???????????? 3字節(jié)

ISO8859-1???? 1字節(jié)

GB2312???????? 2字節(jié)

UTF-16????????? 2字節(jié)

Ref??? ——索引命中的列或常量

表進行數(shù)據(jù)查找時使用字段、常量、函數(shù)的結果等

常量：??? ?????? const

空：?????? ?????? NULL

字段：??? ?????? 數(shù)據(jù)庫名.表名.字段名

函數(shù)的結果：func（如果要想查看是哪個函數(shù)，可以在Explain語句后跟上SHOW WARNING語句）

explain select origo from staff1 where origo='重慶';

explain select * from staff1 where id in (select id from staff1 where id > 2);

Rows——預計select語句要檢查的行數(shù)

mysql估計要讀取并檢查的行數(shù)；并不是結果的行數(shù)

在innoDB引擎中的表中，是一個估計值，不是很準確

Filtered——返回結果的行數(shù)占讀取行數(shù)的百分比

該值越大越好

Extra——顯示額外的信息

通過此字段顯示的額外信息，也可以進行查詢的優(yōu)化（不同MySQL版本顯示的內(nèi)容可能會有些許差別）

Using Index：查找使用了索引，并且返回所需要的數(shù)據(jù)在該索引列中就可以找到（無需回表查詢）

Using Where：先讀取整行數(shù)據(jù)，再按照Where條件進行查詢（符合就留下，不符合則丟棄）

Using Join Buffer：表示查詢使用了連接緩沖（多用于多表連接查詢）

Using Index Condition：查找使用了索引，但是需要回表查詢數(shù)據(jù)—此種情況一般需要優(yōu)化（可以使其滿足覆蓋索引條件來避免回表查詢）

Using Temporary：查找使用了臨時表（多見于group by語句）--此種情況一般需要優(yōu)化（優(yōu)先通過建立索引解決）

Using Filesort：通過表的索引或者全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū)sort buffer中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫FileSort排序（多見于order by語句）----此種情況一般需要優(yōu)化（優(yōu)先通過建立索引解決）

一般需要將Using Filesort、Using Temporary、Using Index Condition 等優(yōu)化為Using Index

索引的使用規(guī)則

通過遵守索引的使用規(guī)則，避免索引失效；并且可以手動選擇索引進行索引查詢；使得索引的到最大利用

SQL提示

是優(yōu)化數(shù)據(jù)庫的一個重要手段，就是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的

SQL提示的字段

USE INDEX??????????? 建議數(shù)據(jù)庫使用哪個索引（當一列屬于多個索引類型式，建議此列使用哪種類型的索引，MySQL可能不會采用此建議）

IGNORE INEDX???? 告訴數(shù)據(jù)庫不要用哪個索引??????

FORCR INDEX?????? 告訴數(shù)據(jù)庫必須使用哪個索引

SQL提示的格式

SELECT 字段列表 FROM 表名 USE INDEX (索引名稱) WHERE 判斷條件;?

情景模擬

針對上述表，我們?yōu)閛rigo、age創(chuàng)建了聯(lián)合索引，現(xiàn)在我們再針對origo創(chuàng)建一個常規(guī)索引

?explain select origo from staff1 where origo='重慶' and age > 18; #此時我們查詢此語句走的是聯(lián)合索引? 我們可以通過語句修改使其走單列索引

explain select origo from staff1 use index (as_origo) where origo='重慶' and age > 18;

最左前綴法則

主要針對聯(lián)合索引，如果索引為聯(lián)合索引，則要遵守最左前綴法則

最左前綴法則的要求

在使用聯(lián)合索引進行查詢時，查詢從聯(lián)合索引的最左列開始，并且不跳過索引中的列，可以跳過最右邊的一列或多列；

在查詢時，如果中途跳過了聯(lián)合索引中的某一列，索引部分失效（此列之后的列索引失效），即無法進行索引查詢，只可以進行全文查詢

在查詢時，如果最左邊的列不存在，則不走索引，走全文掃描（即進行聯(lián)合查詢時必須包含最左列） ?在查詢時不用關心順序，只要存在就可以了

create index as_origo on staff1(origo,age,name); #創(chuàng)建時由左到右創(chuàng)建，左邊一般為查詢頻率高的

explain select * from staff1 where origo='重慶';

explain select * from staff1 where origo='重慶' and age = 22 ;??? #查詢時，也是從左到右查詢；此時使用了索引

explain select * from staff1 where origo='重慶' and name='老六';?? #此時origo使用了索引，name沒有使用索引（通過key_len使用索引的字節(jié)數(shù)判斷）

explain select * from staff1 where age = 22 ;?? #此時沒有使用索引（沒有包含origo字段）

索引失效情況

范圍查詢

在聯(lián)合索引中，出現(xiàn)范圍查詢（>或<）時，范圍查詢右側的列索引失效

盡量使用過>=或者<=來規(guī)避范圍查詢

運算操作

在索引列上進行運算操作時，索引列將失效；運算包括使用函數(shù)、比較運算符、謂詞等

字符串數(shù)據(jù)類型

字符串類型字段使用時，如果不對其值加引號，則存在隱式類型轉換，索引將失效

Like字段模糊查詢

當對尾部進行模糊匹配時，則索引不會失效（即Like(字符%)或者Like(字符_)）

當對頭部進行模糊匹配時，索引會失效（即Like(%字符)或者Like(_字符)）

or連接條件

用or分隔開的條件，如果or前/后的條件中的列有索引，而后/前面的列中沒有索引，那么or前/后面的索引不會被用到；

只有當or前后都是用到索引時，索引才會失效

數(shù)據(jù)分布影響

如果MySQL評估使用索引查詢比全表查詢更慢，則不使用索引，使用全表查詢（一般用于大小判斷的時候會出現(xiàn)）

由于B+樹是順序鏈表，當?shù)谝粋€葉子就符合或者前幾個葉子就符合時，后面的葉子就必然也符合；此時MySql就判斷使用全表查詢更快，就會不適用索引，使用全表查詢了

即：當要查詢的結果占全表很大的比例時，可能就進行全表查詢了

索引的設計原則

1. 針對數(shù)據(jù)量較大（100多萬數(shù)據(jù)及以上）、且查詢比較頻繁的表建立索引（很少查詢，沒有必要建立索引
2. 針對于常作為查詢條件（Where）、排序（Order By）、分組（Group By）操作的字段建立索引
3. 盡量區(qū)分度高的列作為索引，盡量建立唯一索引；區(qū)分度越高，使用索引的效率越高
4. 如果是字符串類型的字段，字段的長度較長，可以針對字段的特點，建立前綴索引
5. 盡量使用聯(lián)合索引，減少單列索引；查詢時聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率
6. 控制索引的數(shù)量，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率
7. 如果索引列不能存儲Null，就在創(chuàng)建表時使用NOT NULL約束此字段；當MySQL優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢

總結

1、查詢效率不高，首先使用explain分析：

如果發(fā)現(xiàn)沒有索引，可以創(chuàng)建索引

如果發(fā)現(xiàn)是單列索引，要注意是否存在索引失效

如果發(fā)現(xiàn)是聯(lián)合索引，要注意是否遵守最左匹配原則

2、盡可能地使得查詢語句掃描更少地行數(shù)、表、列

3、如果對字符串創(chuàng)建了索引，盡可能減少字符串的長度（即為較短的字符串建立前綴索引）

4、盡量使得索引查詢滿足覆蓋索引，避免回表查詢文章來源地址http://www.zghlxwxcb.cn/news/detail-629994.html

到了這里，關于MySQL索引3——Explain關鍵字和索引使用規(guī)則（SQL提示、索引失效、最左前綴法則）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！