「面試知識集錦」一文搞懂mysql索引！！【綻放吧！數據庫】

以我的資歷和文憑，將來這個城市的大街，都歸我掃。

【系列課程介紹】

『面試知識集錦』系列課程包括以下20個系列，超過100篇文章。每篇文章的前半部分為「知識體系」幫助你夯實基礎，后半部分為「面試真題」幫助你拿下面試。

如果覺得還不錯，求、求、關注專欄。

目錄

1、創建索引的幾種方式

1.1 直接創建

1.2 修改表結構(添加索引)

1.3 創建表的時候直接指定

2、mysql索引知識

2.1 B+Tree索引

2.2 主鍵索引和普通索引的區別

2.3 唯一索引vs普通索引

3、mysql索引優化

3.1 查看索引使用情況

3.2 mysql索引使用策略

3.3 mysql索引使用原則

4、索引選擇異常處理辦法

1、創建索引的幾種方式

1.1 直接創建

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。

1.2 修改表結構(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

1.3 創建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

2、mysql索引知識

2.1 B+Tree索引

在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表（IOT），InnoDB使用B+樹索引模型，數據都是存儲在B+樹中的。

假設，有一個表的主鍵列為ID，字段為k，并且在k上有索引。表中R1~R5的（ID,k）值分別為（100,1）、（200,2）、（300,3）、（500,5）、（600,6），每一個索引在InnoDB里面對應一棵B+樹，兩棵樹的簡意示意圖如下：

2.2 主鍵索引和普通索引的區別

主鍵索引的葉子節點存的是整行數據。主鍵索引也被稱為聚簇索引(clustered index)

非主鍵索引的葉子節點內容是主鍵的值。非主鍵索引也被稱為二級索引(secondary index)

如果語句是select * from T where ID=500，即主鍵查詢方式，則只需要搜索ID這棵B+樹；

如果語句是select * from T where k=5，即普通索引查詢方式，則需要先搜索k索引樹，得到ID的值為500，再到ID索引樹搜索一次，這個過程稱為回表！

也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹，因此，我們在應用中應該盡量使用主鍵查詢。

2.3 唯一索引vs普通索引

從查詢上來說

對于普通索引來說，查找到滿足條件的第一個記錄后，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。

對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續檢索。

從更新上來說

A??如果目標頁在內存中：

對于唯一索引來說，找到3和5之間的位置，判斷有沒有沖突，插入這個值，語句執行結束；

對于普通索引來說，找到3和5之間的位置，插入這個值，語句執行結束。

B??如果目標頁在不在內存中：

對于唯一索引來說，需要將數據頁讀入內存，判斷到沒有沖突，插入這個值，語句執行結束；

對于普通索引來說，則是將更新記錄在change buffer，語句執行就結束了。

從這里可以看到，查詢上普通索引只是比唯一索引多了一個一次指針尋找和一次計算，由于數據是按頁讀取的，數據幾乎都在內存中，所以性能相差不大。

但從更新上來看，如果數據不在內存中，唯 一索引需要將數據從磁盤上讀取到內存中，這樣會引發隨機讀，導致IO消耗增多，而普通索引可以利用change buffer，IO上邊要節省很多。性能相差會很多，所以如果可以在業務端保證數據的唯一性，那就可以使用普通索引。

3、mysql索引優化

3.1 查看索引使用情況

使用方法：在select語句前加上explain

示例：EXPLAIN SELECT surname,first_name form a,b WHERE a.id=b.id

EXPLAIN列的解釋：

table：顯示這一行的數據是關于哪張表的。

type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL。

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句。

key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MySQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MySQL忽略索引。

key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好。

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。

rows：MySQL認為必須檢查的用來返回請求數據的行數。

Extra：關于MySQL如何解析查詢的額外信息。

Extra列返回的描述的意義：

Distinct: 一旦MySQL找到了與行相聯合匹配的行，就不再搜索了。 Not exists: MySQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了。 Range checked for each Record（index map:#）: 沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MySQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。 Using filesort: 看到這個的時候，查詢就需要優化了。MySQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行。 Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候。 Using temporary: 看到這個的時候，查詢需要優化了。這里，MySQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上。 Where used: 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序）。 system: 表只有一行：system表。這是const連接類型的特殊情況。 const: 表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MySQL先讀這個值然后把它當做常數來對待。 eq_ref: 在連接中，MySQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用。 ref: 這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對于之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少—越少越好。 range: 這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西時發生的情況。 index: 這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小于表數據）。 ALL: 這個連接類型對于前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免。

3.2 mysql索引使用策略

最好全值匹配--索引怎么建我怎么用。

最佳左前綴法則--如果是多列復合索引，要遵守最左前綴法則。指的是查詢要從索引的最左前列開始并且不跳過索引中的列。

不在索引列上做任何操作（計算，函數，（自動或者手動）類型裝換），會導致索引失效而導致全表掃描。

存儲引擎不能使用索引中范圍條件右邊的列。--范圍之后索引失效（< ,>,between and）。

盡量使用覆蓋索引--索引和查詢列一致，減少select *。--按需取數據用多少取多少。

在MYSQL使用不等于（<,>,!=）的時候無法使用索引，會導致索引失效。

is null或者is not null 也會導致無法使用索引。

like以通配符開頭（'%abc...'）MYSQL索引失效會變成全表掃描的操作。--覆蓋索引。

隱式轉換索引失效：字符串不加單引號。

where條件少用or，用它來連接時索引會失效。

3.3 mysql索引使用原則

（1）復合索引：選擇索引列的順序

1)盡量把字段長度小的列放在聯合索引的最左側（因為字段長度越小，一頁能存儲的數據量越大，IO性能也就越好）

2)區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）

3)使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）

（2）表關聯查詢

1)類型和大小要相同，可以使用索引。

VARCHAR(10)和?CHAR(10)大小相同，但?VARCHAR(10)與?CHAR(15)不相同。

2)字符串列之間比較，兩列應使用相同的字符集。例如，將utf8列與?latin1列進行比較會不使用索引。

3)將字符串列與時間或數字列進行比較時，在沒有轉換情況下,不使用索引。

（3）常見的索引列建議

1)? ?WHERE 字段

2)? ?ORDER BY、GROUP BY、DISTINCT 中的字段不要將符合1和2中字段的列都建立一個索引，通常將1、2中的字段建立聯合索引效果更好

3)? 多表join的關聯列

4）通過索引掃描的行記錄數超過全表的10%~30%左右,優化器不會走索引，而變成全表掃描

5）避免使用雙%號的查詢條件。

（如果無前置%,只有后置%，是可以用到列上的索引的）

覆蓋索引、前綴索引、索引下推，在滿足語句需求的情況下，盡量少地訪問資源是數據庫設計的重要原則之一。我們在使用數據庫的時候，尤其是在設計表結構時，也要以減少資源消耗為目標。

4、索引選擇異常處理辦法

采用force index 強行選擇一個索引。

修改sql語句、引導MySQL使用我們期望的索引。

在有些場景下，我們可以新建一個更適合的索引，來提供給優化器做選擇，或刪除掉誤用的索引。

由于索引統計信息的不準確，可以用analyze table來解決。

而對于其它優化器誤判斷的情況，你可以在應用端用force index 來強行指定索引，也可以通過修改語句來引導優化器，還可以通過增加或者刪除索引來繞過這個問題。

【綻放吧！數據庫】有獎征文火熱進行中：https://bbs.huaweicloud.com/blogs/285617

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。