MySQL索引學習

最近，在項目中要對一個mysql表建立唯一索引，但是發生了一系列自己意料之外的現象，是以趁機鞏固下mysql索引相關的知識。

1. 唯一性限制不起作用

首先，本文中所用的mysql版本是5.7.14：

得到的結果：

5.7.14.6.3-20171229-log
           

我對我的表建立了一個唯一索引，包含幾列：

這裡A、B、C都有可能為NULL，但是不會全部為NULL（由業務語義保證）。然後我往裡面插入資料

Insert into testTable (A,B) VALUES ("test", "test"); --- OK
Insert into testTable (A,B) VALUES ("test", "test"); --- OK
           

上面的兩條SQL都是執行成功了！ 資料庫中會有多條一樣的記錄。可按照我之的構想，在執行後面一條SQL時應該抛出 ‘Duplicate key’ 異常的。

後來查了一下，才發現MySQL唯一性索引是允許多個 NULL 值的存在的：

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.
           

MySQL官網上也有一個report讨論這個問題：unique index allows duplicates with null values ，一部分人認為這是MySQL的bug， 另一部分則認為是一個feature（NULL != NULL，NULL隻是表示data的空缺）。這是一個仁者見仁，智者見智的問題。

既然MySQL認為存在多個NULL值是合理的，那麼怎麼解決這個問題呢？我的方案是把A、B、C的預設值設定為空串，然後還是執行上面的SQL：

Insert into testTable (A,B) VALUES ("test", "test"); --- OK
Insert into testTable (A,B) VALUES ("test", "test"); --- Duplicate key
           

插入第二條語句時報錯，就解決這個問題了。

2. MySQL explain詳解

解決完上面的唯一性限制後，我想看看我的查詢有沒有使用到索引，因為我是對A B C 三列建了一個唯一索引，是以，我猜想下面這樣的一條語句會使用到我的唯一索引：

但是卻得到一條類似下面這樣的結果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	testTable	NULL	ALL	UK_IDX_TEST_A_B_C	NULL	NULL	NULL	1003	100.0	Using where

下面先對explain出來的字段進行解釋。注意explain的執行計劃，隻是作為語句執行過程的一個參考，實際執行的過程不一定和計劃完全一緻，但是執行計劃中透露出的訊息卻可以幫助選擇更好的索引和寫出更優化的查詢語句。

2.1 id

執行編号，辨別select所屬的行。如果在語句中沒子查詢或關聯查詢，隻有唯一的select，每行都将顯示1。否則，内層的select語句一般會順序編号，對應于其在原始語句中的位置；SQL會從id大到小的執行, 如果id相同，則執行順序由上至下。例如執行下面這樣一條語句：

explain select * from testTable
where id in 
(
    select id from testTable where A="testA"
    UNION 
    select id from testTable where A="testB"
);
           

得到如下的結果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	testTable	NULL	ALL	NULL	NULL	NULL	NULL	1003	100.0	Using where
2	DEPENDENT SUBQUERY	testTable	NULL	eq_ref	PRIMARY	PRIMARY	8	func	1	10.0	Using where
3	DEPENDENT UNION	testTable	NULL	eq_ref	PRIMARY	PRIMARY	8	func	1	10.0	Using where
NULL	UNION RESULT	<union2,3>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

2.2 select_type

顯示示查詢中每個select子句的類型。

(1) SIMPLE(簡單SELECT,不使用UNION或子查詢等)

(2) PRIMARY(查詢中若包含任何複雜的子部分,最外層的select被标記為PRIMARY)

(3) UNION(UNION中的第二個或後面的SELECT語句)

(4) DEPENDENT UNION(UNION中的第二個或後面的SELECT語句，取決于外面的查詢)

(5) UNION RESULT(UNION的結果)

(6) SUBQUERY(子查詢中的第一個SELECT)

(7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢)

(8) DERIVED(派生表的SELECT, FROM子句的子查詢)

(9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外連結的第一行)

2.3 table

顯示這一行的資料是關于哪張表的；當from中有子查詢的時候，表名是derivedN的形式，N指向子查詢，也就是explain結果中的下一列；當有union result的時候，表名是union 1,2等的形式，1,2表示參與union的query id。

2.4 paritions

該列顯示的為分區表命中的分區情況。非分區表該字段為空（null）。

2.5 type

表示MySQL在表中找到所需行的方式，又稱“通路類型”。

常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL将周遊全表以找到比對的行

index: Full Index Scan，和全表掃描一樣。隻是掃描表的時候按照索引次序進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。

range:範圍掃描，一個有限制的索引掃描。key 列顯示使用了哪個索引。當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關鍵字列時,可以使用 range

ref: 一種索引通路，它傳回所有比對某個單個值的行。此類索引通路隻有當使用非唯一性索引或唯一性索引非唯一性字首時才會發生。這個類型跟eq_ref不同的是，它用在關聯操作隻使用了索引的最左字首，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的帶索引的列

eq_ref: 類似ref，差別就在使用的索引是唯一索引，對于每個索引鍵值，表中隻有一條記錄比對，簡單來說，就是多表連接配接中使用primary key或者 unique key作為關聯條件

const、system: 當确定最多隻會有一行比對的時候，MySQL優化器會在查詢前讀取它而且隻讀取一次，是以非常快。當主鍵放入where子句時，mysql把這個查詢轉為一個常量（高效）；system是const類型的特例，當查詢的表隻有一行的情況下，使用system

NULL: 意味說mysql能在優化階段分解查詢語句，在執行階段甚至用不到通路表或索引（高效）

2.6 possible_keys

指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引将被列出，但不一定被查詢使用.

該列完全獨立于EXPLAIN輸出所示的表的次序。這意味着在possible_keys中的某些鍵實際上不能按生成的表次序使用。

如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或适合索引的列來提高你的查詢性能。如果是這樣，創造一個适當的索引并且再次用EXPLAIN檢查查詢。

2.7 Key

key列顯示MySQL實際決定使用的鍵（索引）

如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

2.8 key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的）

不損失精确性的情況下，長度越短越好。

2.9 ref

如果是使用的常數等值查詢，這裡會顯示const，如果是連接配接查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯字段，如果是條件使用了表達式或者函數，或者條件列發生了内部隐式轉換，這裡可能顯示為func。

2.10 rows

表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數

2.11 filtered

這個字段表示存儲引擎傳回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

2.12 Extra

Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細資訊，MySQL查詢優化器執行查詢的過程中對查詢計劃的重要補充資訊，如果你想要優化你的查詢，那就要注意extra輔助資訊中的using filesort和using temporary，這兩項非常消耗性能，需要注意。

Using where: 使用了WHERE從句來限制哪些行将與下一張表比對或者是傳回給使用者。注意：Extra列出現Using where表示MySQL伺服器将存儲引擎傳回服務層以後再應用WHERE條件過濾。

Using temporary：用臨時表儲存中間結果，常用于GROUP BY 和 ORDER BY操作中，一般看到它說明查詢需要優化了，就算避免不了臨時表的使用也要盡量避免硬碟臨時表的使用。

Using filesort：MySQL有兩種方式可以生成有序的結果，通過排序操作或者使用索引，當Extra中出現了Using filesort 說明MySQL使用了排序操作，但注意雖然叫filesort但并不是說明就是用了檔案來進行排序，隻要可能排序都是在記憶體裡完成的。大部分情況下利用索引排序更快，是以一般這時也要考慮優化查詢了。使用檔案完成排序操作，這是可能是ordery by，group by語句的結果，這可能是一個CPU密集型的過程，可以通過選擇合适的索引來改進性能，用索引來為查詢結果排序。

Using join buffer：該值強調了在擷取連接配接條件時沒有使用索引，并且需要連接配接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

Impossible where：這個值強調了where語句會導緻沒有符合條件的行。

Select tables optimized away：這個值意味着僅通過使用索引，優化器可能僅從聚合函數結果中傳回一行

3. 為什麼沒有使用索引

回到第二節一開始提到的問題，以下的sql：

使用的執行計劃是這樣的：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	testTable	NULL	ALL	UK_IDX_TEST_A_B_C	NULL	NULL	NULL	1003	100.0	Using where

key那列是NULL，也就是沒有使用到索引，為什麼呢？

這是因為選擇索引是優化器的工作。而優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行語句。在資料庫裡面，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味着通路磁盤資料的次數越少，消耗的CPU資源越少。

優化器是怎麼判斷掃描的行數呢? 答案是使用一個統計資訊就是索引的“區分度”。顯然，一個索引上不同的值越多，這個索引的區分度就越好。而一個索引上不同的值的個數，我們稱之為“基數”（cardinality）。也就是說，這個基數越大，索引的區分度越好。 我們可以使用

show index from table

方法，看到一個索引的基數。

MySQL是使用采樣統計得到索引的基數的。采樣統計的時候，InnoDB預設會選擇N個資料頁，統計這些頁面上的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了這個索引的基數。而資料表是會持續更新的，索引統計資訊也不會固定不變。是以，當變更的資料行數超過1/M的時候，會自動觸發重新做一次索引統計。

如果統計資訊不對，那就修正。使用

analyze table t

指令，可以用來重新統計索引資訊。

當然，掃描行數并不是唯一的判斷标準，優化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。使用普通索引的時候還需要把回表的代價算進去

在上面的例子中，因為使用索引也要掃描所有的行，而且使用了索引還需要回表，是以mysql就沒有使用索引了。

tips： 什麼是回表？

MySQL B+樹索引結構中，主鍵索引的葉子節點存的是整行資料。非主鍵索引的葉子節點内容是主鍵的值。
基于主鍵索引和普通索引的查詢有什麼差別？如果語句是 select * from T where id = 6，即主鍵查詢方式，則隻需要搜尋 ID 這棵 B+ 樹；如果語句是 select * from T where name = '張四'，即普通索引查詢方式，則需要先搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。這個過程稱為回表。
           

參考文獻

1. MySQL 唯一性限制與 NULL
2. MySQL EXPLAIN詳解
3. MySQL Explain詳解
4. mysql為什麼有些時候會選錯索引
5. Mysql優化之explain詳解