你的like語句為什么沒索引詳解
前言
本文旨在用最通俗的語言講述最枯燥的基本知識(shí)
這個(gè)話題比較有意思。昨天中午吃完飯間突然有個(gè)同事蹦出了一句:“l(fā)ike有索引嗎?”,我順口就說沒有,另一個(gè)同事反駁說有啊,還有些同事說看情況的有,這下有點(diǎn)懵逼了,都不知道那種說法是正確的,于是決定花了個(gè)半小時(shí)來研究驗(yàn)證這個(gè)問題,終于得到答案。
怎么驗(yàn)證的呢?
坊間有傳言:MySQL性能優(yōu)化有個(gè)神器,叫做explain,它可以對(duì)select語句進(jìn)行分析并且輸出詳細(xì)的select執(zhí)行過程的詳細(xì)信息,讓開發(fā)者從這些信息中獲得優(yōu)化的思路。
下面來講講這個(gè)MySQL提供的explain命令:
語法:explain SQL語句例如:
1explain select * from user where id=1
執(zhí)行完畢之后,它的輸出有以下字段:
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra
要想知道explain命名怎么使用,就必須把這些字段搞清楚
1. id
SELECT查詢的標(biāo)識(shí)符, 每個(gè)SELECT語句都會(huì)自動(dòng)分配一個(gè)唯一的標(biāo)識(shí)符
2. select_type
每個(gè)select查詢字句的類型,具體類型以及對(duì)應(yīng)作用如下表:
類型名 | 解釋 |
---|---|
SIMPLE | 簡(jiǎn)單SELECT,不使用UNION或子查詢等 |
PRIMARY | 查詢中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY |
UNION | UNION中的第二個(gè)或后面的SELECT語句 |
DEPENDENT UNION | UNION中的第二個(gè)或后面的SELECT語句,取決于外面的查詢 |
UNION RESULT | UNION的結(jié)果 |
SUBQUERY | 子查詢中的第一個(gè)SELECT |
DEPENDENT SUBQUERY | 子查詢中的第一個(gè)SELECT,取決于外面的查詢 |
DERIVED | 派生表的SELECT, FROM子句的子查詢 |
UNCACHEABLE SUBQUERY | 一個(gè)子查詢的結(jié)果不能被緩存,必須重新評(píng)估外鏈接的第一行 |
3. table
顯示這一行的數(shù)據(jù)是查哪張表的,不過有時(shí)短路顯示的不是真實(shí)的表名。
4. partitions
匹配的分區(qū)(這個(gè)目前用處不大)
5. type
訪問類型,表示MySQL在表中找到所需行的方式,對(duì)應(yīng)的值和解釋如下:
類型名 | 優(yōu)級(jí)別 | 解釋 |
---|---|---|
system | 1 | 表僅有一行 |
const | 2 | 表最多有一個(gè)匹配行,在查詢開始時(shí)即被讀取 |
eq_ref | 3 | 使用primary key或者unique key作為多表連接的條件,僅從該表中讀取一行 |
ref | 4 | 作為查詢條件的索引在每個(gè)表匹配索引值的行從表中讀取出來 |
fulltext | 5 | 全文索引檢索 |
ref_or_null | 6 | 和ref一致,但增加了NULL值查詢支持 |
index_merge | 7 | 表示使用了索引合并優(yōu)化方法 |
unique_subquery | 8 | 使用了替換了in子查詢 |
index_subquery | 9 | 使用了替換了in子查詢,但只適用于子查詢中的非唯一索引 |
range | 10 | 只檢索給定范圍的行,使用一個(gè)索引來選擇行 |
index | 11 | 全表掃描,但掃描表的方式是按索引的次序進(jìn)行 |
ALL | 12 | 全表掃描的方式找到匹配的行 |
type作為訪問類型,其值代表著當(dāng)前查詢所用的類型,是體現(xiàn)性能的一個(gè)重要指標(biāo),從表中可以看到,從上到下,掃描表的方式越來越寬,性能也就越來越差,因此,對(duì)于一個(gè)查詢,最好能保持在range級(jí)別以上。
6. possible_keys
主動(dòng)指出查詢能用哪個(gè)索引在表中找到記錄也就是會(huì)列出在查詢中的字段中有索引的字段,但不一定被查詢所用。
7. key
顯示再查詢中實(shí)際使用的索引/鍵,如果沒有索引,則顯示NULL。但如果想強(qiáng)制查詢中使用或忽視possible_keys列中的索引,則可以在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
8. key_len
表示索引中使用的字節(jié)數(shù)。
9. ref
表示哪些列或常量被用于查找索引列上的值。
10. rows
顯示當(dāng)前查詢估算到的查找到匹配記錄所需的記錄行數(shù)。
11. Extra
顯示當(dāng)前查詢所用的解決方式,它有以下幾種情況:
類型名 | 解釋 |
---|---|
Using where | 列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動(dòng)的表返回的, |
Using temporary | 表示MySQL需要使用臨時(shí)表來存儲(chǔ)結(jié)果集,常見于排序和分組查詢 |
Using filesort | MySQL中無法利用索引完成的排序操作稱為“文件排序” |
Using join buffer | 改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引,并且需要連接緩沖區(qū)來存儲(chǔ)中間結(jié)果。如果出現(xiàn)了這個(gè)值,那應(yīng)該注意,根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能。 |
Impossible where | 這個(gè)值強(qiáng)調(diào)了where語句會(huì)導(dǎo)致沒有符合條件的行。 |
Select tables optimized away | 這個(gè)值意味著僅通過使用索引,優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行 |
講完了語法,我們來實(shí)際操作一波,首先創(chuàng)建個(gè)表:
-- 創(chuàng)建表 CREATE TABLE test( id INT(11) NOT NULL AUTO_INCREMENT, uname VARCHAR(255), PRIMARY KEY(id) );
然后給uname字段加上索引:
-- 添加索引 ALTER TABLE test ADD INDEX uname_index (uname);
查看一下索引是否添加成功:
-- 查看是否有索引 SHOW INDEX FROM test;
輸出結(jié)果為:
可以看出索引已經(jīng)創(chuàng)建成功,接下來添加一些數(shù)據(jù):
-- 添加一些數(shù)據(jù) INSERT INTO test VALUES(1,'jay'); INSERT INTO test VALUES(2,'ja'); INSERT INTO test VALUES(3,'bril'); INSERT INTO test VALUES(4,'aybar');
一切準(zhǔn)備就緒,下面用explain這個(gè)命令來探究一些like語句是否有索引,like有四種情況,分別為沒有%、 %% 、左%、右%、
1. like 字段名
EXPLAIN SELECT * FROM test WHERE uname LIKE 'j';
輸出為:
可以看出:type的值為:range,key的值為uname_index,也就是說這種情況下,使用了索引。
2. like %字段名%
EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%';
輸出為:
可以看出:type的值為ALL也就是全表掃描,而且key的值為NULL,也就是說沒用到任何索引。
3. like %字段名
EXPLAIN SELECT * FROM test WHERE uname LIKE '%j';
輸出為:
可以看出:type的值為ALL,key的值為NULL,同樣沒用到索引。
4. like 字段名%
EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%';
輸出為:
可以看出:type的值為:range,key的值為uname_index,也就是說這種情況下,使用了索引。
總結(jié)
由上面的試驗(yàn)可以總結(jié)出like是否使用索引的規(guī)律:like語句要使索引生效,like后不能以%開始,也就是說 (like %字段名%) 、(like %字段名)這類語句會(huì)使索引失效,而(like 字段名)、(like 字段名%)這類語句索引是可以正常使用。
其它
為了查證like索引的問題,研究了MySQL神奇explain,但explain不僅僅只能檢查索引使用情況,還可以提供很多其它的性能優(yōu)化方面的幫助,至于具體的使用,其實(shí)跟上面講的一樣,把explain結(jié)果列出來,然后順藤摸瓜查閱相關(guān)的字段就可以得到相應(yīng)的內(nèi)容。
好了,以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
MySQL中的 inner join 和 left join的區(qū)別解析
這篇文章主要介紹了MySQL中的 inner join 和 left join的區(qū)別解析,本文通過場(chǎng)景描述給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-05-05淺談MySQL中授權(quán)(grant)和撤銷授權(quán)(revoke)用法詳解
下面小編就為大家?guī)硪黄獪\談MySQL中授權(quán)(grant)和撤銷授權(quán)(revoke)用法詳解。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-09-09MySQL循環(huán)語句之while循環(huán)測(cè)試
MySQL有循環(huán)語句操作,while 循環(huán)、loop循環(huán)和repeat循環(huán),目前我只測(cè)試了 while 循環(huán),下面與大家分享下2014-07-07mysql數(shù)據(jù)庫索引損壞及修復(fù)經(jīng)驗(yàn)分享
這篇文章主要介紹了mysql數(shù)據(jù)庫索引損壞及修復(fù)經(jīng)驗(yàn)分享,需要的朋友可以參考下2015-06-06解讀SQL中GROUP BY和HAVING子句中使用NULL條件問題
在使用SQL進(jìn)行數(shù)據(jù)查詢時(shí),可能會(huì)遇到查詢結(jié)果為空的情況,這通常與GROUP BY和HAVING子句的使用有關(guān),尤其是在處理包含NULL值的字段時(shí),當(dāng)使用GROUP BY進(jìn)行數(shù)據(jù)分組,并在HAVING子句中直接判斷字段是否為NULL時(shí)2024-10-10MySQL 數(shù)據(jù)庫設(shè)計(jì)復(fù)習(xí)筆記及項(xiàng)目實(shí)戰(zhàn)
參考的數(shù)據(jù)庫文檔主要有:目前國內(nèi)的常見的PHP系統(tǒng)的數(shù)據(jù)庫2010-03-03