目錄
Explain關(guān)鍵字 索引性能分析
Id ——select的查詢序列號
Select_type——select查詢的類型
Table——表名稱
Type——select的連接類型
Possible_key ——顯示可能應(yīng)用在這張表的索引
Key——實際用到的索引
Key_len——實際索引使用到的字節(jié)數(shù)
Ref??? ——索引命中的列或常量
Rows——預(yù)計select語句要檢查的行數(shù)
Filtered——返回結(jié)果的行數(shù)占讀取行數(shù)的百分比
Extra——顯示額外的信息
索引的使用規(guī)則
SQL提示
最左前綴法則
索引失效情況
索引的設(shè)計原則
Explain關(guān)鍵字 索引性能分析
Explain可以應(yīng)用于SELECT、DELETE、INSERT、REPLACE、UPDATE語句
通過Explain關(guān)鍵字可以看到SELECT語句的執(zhí)行計劃，即可以查看到MySQL如何處理SELECT語句，通過Explain顯示的結(jié)果來決定如何優(yōu)化
具體的作用有
- 查看表的讀取順序
- 查看此語句可以使用哪些索引
- 此語句實際使用了哪些索引
- 查看此語句查詢了多少行數(shù)據(jù)
explain語法
在任意的SELECT語句之前加上關(guān)鍵字 Explain或者Desc
EXPLAIN SELECT * FROM 表名;
使用Explain后返回的結(jié)果
查詢結(jié)果的各個字段
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：當(dāng)查詢中包含子查詢或UNION時，即外層的查詢?yōu)榇瞬樵冾愋?/span>
SUBQUERY：在SELECT或WHERE中包含了子查詢時會被標(biāo)記為此查詢類型
DERIVED：在FROM列表中包含的子查詢被標(biāo)記為此查詢類型（MySQL會將此子查詢的查詢結(jié)果作為臨時表—派生表）
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語句被標(biāo)記為UNION（如果UNION被FROM子句中的子查詢包含，則它的第一個SELECT會被標(biāo)記為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：表示對應(yīng)UNION的結(jié)果（UNION和UNRESULT一般會成對出現(xiàn)）
Table——表名稱
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的，顯示結(jié)果可能為表的名稱、<derivedX>、<unionX1，X1>等
<derivedX>
當(dāng)from子句中有子查詢時，table列為<derivedX>的格式（x為id值），對應(yīng)子查詢返回的臨時表（派生表）
<unionX1，X1>
當(dāng)存在union時，union result的table列為<unionX1，X1>的格式；X1和X2表示參與union的表的id序號
Type——select的連接類型
select的連接類型是查看索引執(zhí)行情況的一個重要指標(biāo)，就是MySQL如何查找數(shù)據(jù)表中的記錄
連接類型的性能由好到差為NULL、system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all
重點關(guān)注的是：NULL、system、const、eq_ref、ref、range、index、all
在優(yōu)化時盡量優(yōu)化為性能好的（當(dāng)查詢時不查詢?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：?????? 當(dāng)查詢使用到全文索引時的連接類型
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：范圍查詢；當(dāng)給一個字段添加索引之后，使用范圍作為此字段的條件進行數(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 ——顯示可能應(yīng)用在這張表的索引
該值為一個或多個
此字段顯示的索引不一定會被查詢使用到，可能會出現(xiàn)索引失效的問題
當(dāng)Select語句發(fā)現(xiàn)可以使用多個索引的時候，可以通過SQL提示來建議Mysql語句使用指定的索引，可以避免SQL使用了性能比較低的索引（例如如果同時存在唯一索引和常規(guī)索引，可以建立SQL使用唯一索引）
Key——實際用到的索引
如果沒有使用索引，則為NULL
哪些情況會導(dǎo)致有可用索引但是實際上沒有使用到索引呢？
1、對于聯(lián)合索引來說，沒有遵守最左前綴法則
2、范圍查詢時使用到>或<，會導(dǎo)致范圍查詢右側(cè)的列索引失效
3、在Where之后的索引列上進行運算操作（包含函數(shù)、比較運算符、謂詞等）
4、字符串類型字段的值使用時，如果不加引號，存在隱式類型轉(zhuǎn)換，索引將失效
5、當(dāng)對頭部進行模糊匹配時，索引會失效（即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、盡量使用尾部模糊匹配來代替頭部模糊匹配；當(dāng)對尾部進行模糊匹配時，則索引不會失效（即Like(字符%)或者Like(字符_)）
6、只有當(dāng)or前后都是用到索引時，索引才會失效
Key_len——實際索引使用到的字節(jié)數(shù)
表明了在索引中使用的字節(jié)數(shù)，通過此值可以大致估算出使用了索引中的哪些列
Key_len的計算規(guī)則
當(dāng)字段允許為Null時，比不允許為Null大1個字節(jié)
不同的數(shù)據(jù)類型，占用的字節(jié)數(shù)時不同的，詳情可以參考以下官方文檔（介紹的是不允許為空的情況）
MySQL :: MySQL 8.0 Reference Manual :: 11.7 Data Type Storage Requirements
對于字符串?dāng)?shù)據(jù)類型來說，其占用的字節(jié)數(shù)還跟使用的字符編碼有關(guān)
GBK?????????????? 2字節(jié)
UTF8???????????? 3字節(jié)
ISO8859-1???? 1字節(jié)
GB2312???????? 2字節(jié)
UTF-16????????? 2字節(jié)
Ref??? ——索引命中的列或常量
表進行數(shù)據(jù)查找時使用字段、常量、函數(shù)的結(jié)果等
常量：??? ?????? const
空：?????? ?????? NULL
字段：??? ?????? 數(shù)據(jù)庫名.表名.字段名
函數(shù)的結(jié)果：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——預(yù)計select語句要檢查的行數(shù)
mysql估計要讀取并檢查的行數(shù)；并不是結(jié)果的行數(shù)
在innoDB引擎中的表中，是一個估計值，不是很準(zhǔn)確
Filtered——返回結(jié)果的行數(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中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫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ù)庫使用哪個索引（當(dāng)一列屬于多個索引類型式，建議此列使用哪種類型的索引，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)合查詢時必須包含最左列） ?在查詢時不用關(guā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)范圍查詢（>或<）時，范圍查詢右側(cè)的列索引失效
盡量使用過>=或者<=來規(guī)避范圍查詢
運算操作
在索引列上進行運算操作時，索引列將失效；運算包括使用函數(shù)、比較運算符、謂詞等
字符串?dāng)?shù)據(jù)類型
字符串類型字段使用時，如果不對其值加引號，則存在隱式類型轉(zhuǎn)換，索引將失效
Like字段模糊查詢
當(dāng)對尾部進行模糊匹配時，則索引不會失效（即Like(字符%)或者Like(字符_)）
當(dāng)對頭部進行模糊匹配時，索引會失效（即Like(%字符)或者Like(_字符)）
or連接條件
用or分隔開的條件，如果or前/后的條件中的列有索引，而后/前面的列中沒有索引，那么or前/后面的索引不會被用到；
只有當(dāng)or前后都是用到索引時，索引才會失效
數(shù)據(jù)分布影響
如果MySQL評估使用索引查詢比全表查詢更慢，則不使用索引，使用全表查詢（一般用于大小判斷的時候會出現(xiàn)）
由于B+樹是順序鏈表，當(dāng)?shù)谝粋€葉子就符合或者前幾個葉子就符合時，后面的葉子就必然也符合；此時MySql就判斷使用全表查詢更快，就會不適用索引，使用全表查詢了
即：當(dāng)要查詢的結(jié)果占全表很大的比例時，可能就進行全表查詢了
索引的設(shè)計原則
- 針對數(shù)據(jù)量較大（100多萬數(shù)據(jù)及以上）、且查詢比較頻繁的表建立索引（很少查詢，沒有必要建立索引
- 針對于常作為查詢條件（Where）、排序（Order By）、分組（Group By）操作的字段建立索引
- 盡量區(qū)分度高的列作為索引，盡量建立唯一索引；區(qū)分度越高，使用索引的效率越高
- 如果是字符串類型的字段，字段的長度較長，可以針對字段的特點，建立前綴索引
- 盡量使用聯(lián)合索引，減少單列索引；查詢時聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率
- 控制索引的數(shù)量，索引越多，維護索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率
- 如果索引列不能存儲Null，就在創(chuàng)建表時使用NOT NULL約束此字段；當(dāng)MySQL優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢
總結(jié)
1、查詢效率不高，首先使用explain分析：
如果發(fā)現(xiàn)沒有索引，可以創(chuàng)建索引
如果發(fā)現(xiàn)是單列索引，要注意是否存在索引失效
如果發(fā)現(xiàn)是聯(lián)合索引，要注意是否遵守最左匹配原則
2、盡可能地使得查詢語句掃描更少地行數(shù)、表、列
3、如果對字符串創(chuàng)建了索引，盡可能減少字符串的長度（即為較短的字符串建立前綴索引）文章來源：http://www.zghlxwxcb.cn/news/detail-628291.html
4、盡量使得索引查詢滿足覆蓋索引，避免回表查詢文章來源地址http://www.zghlxwxcb.cn/news/detail-628291.html
到了這里，關(guān)于MySQL索引3——Explain關(guān)鍵字和索引優(yōu)化（SQL提示、索引失效、索引使用規(guī)則）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
