快捷導(dǎo)航

你的like語句為什么沒索引詳解

更新時間：2018年10月12日 08:27:01 作者：顧家進

這篇文章主要給大家介紹了關(guān)于你的like語句為什么沒索引的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

前言

本文旨在用最通俗的語言講述最枯燥的基本知識

這個話題比較有意思。昨天中午吃完飯間突然有個同事蹦出了一句：“l(fā)ike有索引嗎？”，我順口就說沒有，另一個同事反駁說有啊，還有些同事說看情況的有，這下有點懵逼了，都不知道那種說法是正確的，于是決定花了個半小時來研究驗證這個問題，終于得到答案。

怎么驗證的呢？

坊間有傳言：MySQL性能優(yōu)化有個神器，叫做explain，它可以對select語句進行分析并且輸出詳細的select執(zhí)行過程的詳細信息，讓開發(fā)者從這些信息中獲得優(yōu)化的思路。

下面來講講這個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查詢的標識符，每個SELECT語句都會自動分配一個唯一的標識符

2. select_type

每個select查詢字句的類型，具體類型以及對應(yīng)作用如下表：

類型名	解釋
SIMPLE	簡單SELECT,不使用UNION或子查詢等
PRIMARY	查詢中若包含任何復(fù)雜的子部分,最外層的select被標記為PRIMARY
UNION	UNION中的第二個或后面的SELECT語句
DEPENDENT UNION	UNION中的第二個或后面的SELECT語句，取決于外面的查詢
UNION RESULT	UNION的結(jié)果
SUBQUERY	子查詢中的第一個SELECT
DEPENDENT SUBQUERY	子查詢中的第一個SELECT，取決于外面的查詢
DERIVED	派生表的SELECT, FROM子句的子查詢
UNCACHEABLE SUBQUERY	一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行

3. table

顯示這一行的數(shù)據(jù)是查哪張表的，不過有時短路顯示的不是真實的表名。

4. partitions

匹配的分區(qū)(這個目前用處不大)

5. type

訪問類型，表示MySQL在表中找到所需行的方式，對應(yīng)的值和解釋如下：

類型名	優(yōu)級別	解釋
system	1	表僅有一行
const	2	表最多有一個匹配行,在查詢開始時即被讀取
eq_ref	3	使用primary key或者unique key作為多表連接的條件,僅從該表中讀取一行
ref	4	作為查詢條件的索引在每個表匹配索引值的行從表中讀取出來
fulltext	5	全文索引檢索
ref_or_null	6	和ref一致，但增加了NULL值查詢支持
index_merge	7	表示使用了索引合并優(yōu)化方法
unique_subquery	8	使用了替換了in子查詢
index_subquery	9	使用了替換了in子查詢，但只適用于子查詢中的非唯一索引
range	10	只檢索給定范圍的行,使用一個索引來選擇行
index	11	全表掃描，但掃描表的方式是按索引的次序進行
ALL	12	全表掃描的方式找到匹配的行

type作為訪問類型，其值代表著當前查詢所用的類型，是體現(xiàn)性能的一個重要指標，從表中可以看到，從上到下，掃描表的方式越來越寬，性能也就越來越差，因此，對于一個查詢，最好能保持在range級別以上。

6. possible_keys

主動指出查詢能用哪個索引在表中找到記錄也就是會列出在查詢中的字段中有索引的字段，但不一定被查詢所用。

7. key

顯示再查詢中實際使用的索引/鍵，如果沒有索引，則顯示NULL。但如果想強制查詢中使用或忽視possible_keys列中的索引，則可以在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

表示索引中使用的字節(jié)數(shù)。

9. ref

表示哪些列或常量被用于查找索引列上的值。

10. rows

顯示當前查詢估算到的查找到匹配記錄所需的記錄行數(shù)。

11. Extra

顯示當前查詢所用的解決方式，它有以下幾種情況：

類型名	解釋
Using where	列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，
Using temporary	表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢
Using filesort	MySQL中無法利用索引完成的排序操作稱為“文件排序”
Using join buffer	改值強調(diào)了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進能。
Impossible where	這個值強調(diào)了where語句會導(dǎo)致沒有符合條件的行。
Select tables optimized away	這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

講完了語法，我們來實際操作一波，首先創(chuàng)建個表：

-- 創(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');

一切準備就緒，下面用explain這個命令來探究一些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é)

由上面的試驗可以總結(jié)出like是否使用索引的規(guī)律：like語句要使索引生效，like后不能以%開始，也就是說（like %字段名%）、（like %字段名）這類語句會使索引失效，而（like 字段名）、（like 字段名%）這類語句索引是可以正常使用。

其它

為了查證like索引的問題，研究了MySQL神奇explain，但explain不僅僅只能檢查索引使用情況，還可以提供很多其它的性能優(yōu)化方面的幫助，至于具體的使用，其實跟上面講的一樣，把explain結(jié)果列出來，然后順藤摸瓜查閱相關(guān)的字段就可以得到相應(yīng)的內(nèi)容。

好了，以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章: