歡迎光臨
每天分享高質量文章

你的like陳述句為啥沒索引?

來自:編程無界(微信號:qianshic),作者:假不理

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

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

怎麼驗證的呢?

坊間有傳言:MySQL性能優化有個神器,叫做explain,它可以對select陳述句進行分析並且輸出詳細的select執行過程的詳細信息,讓開發者從這些信息中獲得優化的思路。

下麵來講講這個MySQL提供的explain命令:

語法:explain SQL陳述句
例如:

1explain select * from user where id=1

執行完畢之後,它的輸出有以下欄位:

  • id

  • select_type

  • table

  • partitions

  • type

  • possible_keys

  • key

  • key_len

  • ref

  • rows

  • Extra

要想知道explain命名怎麼使用,就必須把這些欄位搞清楚

1. id

SELECT查詢的識別符號, 每個SELECT陳述句都會自動分配一個唯一的識別符號

2. select_type

每個select查詢字句的型別,具體型別以及對應作用如下表:

型別名 解釋
SIMPLE 簡單SELECT,不使用UNION或子查詢等
PRIMARY 查詢中若包含任何複雜的子部分,最外層的select被標記為PRIMARY
UNION UNION中的第二個或後面的SELECT陳述句
DEPENDENT UNION UNION中的第二個或後面的SELECT陳述句,取決於外面的查詢
UNION RESULT UNION的結果
SUBQUERY 子查詢中的第一個SELECT
DEPENDENT SUBQUERY 子查詢中的第一個SELECT,取決於外面的查詢
DERIVED 派生表的SELECT, FROM子句的子查詢
UNCACHEABLE SUBQUERY 一個子查詢的結果不能被快取,必須重新評估外鏈接的第一行

3. table

顯示這一行的資料是查哪張表的,不過有時短路顯示的不是真實的表名。

4. partitions

匹配的分割槽(這個目前用處不大)

5. type

訪問型別,表示MySQL在表中找到所需行的方式,對應的值和解釋如下:

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

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

6. possible_keys

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

7. key

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

8. key_len

表示索引中使用的位元組數。

9. ref

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

10. rows

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

11. Extra

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

型別名 解釋
Using where 列資料是從僅僅使用了索引中的信息而沒有讀取實際的行動的表傳回的,
Using temporary 表示MySQL需要使用臨時表來儲存結果集,常見於排序和分組查詢
Using filesort MySQL中無法利用索引完成的排序操作稱為“檔案排序”
Using join buffer 改值強調了在獲取連接條件時沒有使用索引,並且需要連接緩衝區來儲存中間結果。如果出現了這個值,那應該註意,根據查詢的具體情況可能需要添加索引來改進能。
Impossible where 這個值強調了where陳述句會導致沒有符合條件的行。
Select tables optimized away 這個值意味著僅通過使用索引,優化器可能僅從聚合函式結果中傳回一行

講完了語法,我們來實際操作一波,首先創建個表:

1-- 創建表
2CREATE TABLE test(
3id INT(11NOT NULL AUTO_INCREMENT,
4uname VARCHAR(255),
5PRIMARY KEY(id
6);

然後給uname欄位加上索引:

1-- 添加索引
2ALTER TABLE test ADD INDEX uname_index (uname);

查看一下索引是否添加成功:

1-- 查看是否有索引
2SHOW INDEX FROM test;

輸出結果為:

可以看出索引已經創建成功,接下來添加一些資料:

1-- 添加一些資料
2INSERT INTO test VALUES(1,'jay');
3INSERT INTO test VALUES(2,'ja');
4INSERT INTO test VALUES(3,'bril');
5INSERT INTO test VALUES(4,'aybar');

一切準備就緒,下麵用explain這個命令來探究一些like陳述句是否有索引,
like有四種情況,分別為沒有%、 %% 、左%、右%、

1. like 欄位名

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'

輸出為:

可以看出:
type的值為:range,key的值為uname_index,也就是說這種情況下,使用了索引。

2. like %欄位名%

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%'

輸出為:

可以看出:
type的值為ALL也就是全表掃描,而且key的值為NULL,也就是說沒用到任何索引。

3. like %欄位名

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j'

輸出為:

可以看出:
type的值為ALL,key的值為NULL,同樣沒用到索引。

4. like 欄位名%

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%'

輸出為:

可以看出:
type的值為:range,key的值為uname_index,也就是說這種情況下,使用了索引。

總結

由上面的試驗可以總結出like是否使用索引的規律:
like陳述句要使索引生效,like後不能以%開始,也就是說 :

(like %欄位名%)  、(like %欄位名)這類陳述句會使索引失效,(like 欄位名)、(like 欄位名%)這類陳述句索引是可以正常使用。

其它

為了查證like索引的問題,研究了MySQL神奇explain,但explain不僅僅只能檢查索引使用情況,還可以提供很多其它的性能優化方面的幫助,至於具體的使用,其實跟上面講的一樣,把explain結果列出來,然後順藤摸瓜查閱相關的欄位就可以得到相應的內容。


●編號428,輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

 

Web開發

更多推薦18個技術類微信公眾號

涵蓋:程式人生、演算法與資料結構、黑客技術與網絡安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

赞(0)

分享創造快樂