溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下MySQL中索引的案例分析，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去探討吧！
在 MySQL 中，從索引的邏輯或者說字段特性來區分，索引大致分為以下幾個種類：普通索引、唯一索引、主鍵索引、聯合索引和前綴索引。
還有另外一種從物理存儲上來區分的索引分類：聚簇索引和非聚簇索引。
簡單來說，所謂的聚簇索引就是索引 key 與數據行在一起，而非聚簇索引的索引 key 對應的值是聚簇索引的值。
常見的用于實現索引的數據結構有哈希表、有序數組和搜索樹。
哈希表是一個以 key-value 形式來存儲數據的容器，和 HashMap 一樣，哈希索引也會將 key 通過特定的哈希函數計算得到索引值，然后在數組的相應位置存放 key 對應的 value，如果有兩個 key 通過哈希函數計算得到的索引值相同（發生哈希沖突），那么數組的這個位置就會變成一個鏈表，存放所有哈希值相同的 value。
所以在一般情況下，哈希表進行等值查詢的時間復雜度可以達到 O(1)，但是在發生哈希沖突的情況下，還需要額外遍歷鏈表中的所有值，才能夠找到符合條件的數據。
另外，考慮到經過哈希函數計算得到的索引是不規律的——哈希表希望所有的 key 能夠得到充分散列，這樣才能讓 key 均勻分布，不浪費空間——即哈希表的 key 是非順序的，所以使用哈希表來進行區間查詢時很慢的，排序也是同樣的道理。
所以，哈希表僅適用于等值查詢。
有序數組顧名思義是一個按照 key 的順序進行排列的數組，它進行等值查詢的時間復雜度使用二分查詢可以達到O(logN)，這與哈希表相比遜色不少。
但是通過有序數組進行范圍查詢的效率較高：首先通過二分查詢找到最小值（或最大值），然后反向遍歷，直到另一個邊界。
至于排序，有序數組本來就是有序的，天然已經排好序了，當然排序字段不是索引字段就另說了。
但是有序數組有一個缺點，由于數組元素是連續且有序的，如果此時插入新的數據行，為了維持有序數組的有序性，需要將比此元素 key 大的元素都往后移動一個單位，給他騰出一個地方插入。而這種維護索引的方式的代價是很大的。
所以，有序數組適合存儲衣服初始化過后就不再更新的數據。
了解過數據結構的人應該會知道，搜索樹是一個查詢時間復雜度為O(logN)，更新的時間復雜度也是O(logN)的數據結構。所以搜索樹相較于哈希表和有序數組來說兼顧查詢與更新兩方面。也正是由于這個原因，在 MySQL 中最常用的數據模型就是搜索樹。
而考慮到索引是存放在磁盤中的，如果搜索樹是一棵二叉樹，那么它的子節點只能有左右兩個，在數據比價多的情況下，這棵二叉樹的樹高可能會非常高，當 MySQL 進行查詢的時候，可能由于樹高導致磁盤I/O次數過多，查詢效率變慢。
除此之外，還有一種全文索引，它通過建立倒排索引，解決了判斷字段是否包含的問題。
倒排索引是用來存儲在全文搜索下某個單詞在一個文檔或者一組文檔中的存儲位置的映射，通過倒排索引可以根據單詞快速獲取包含這個單詞的文檔列表。
當通過關鍵詞進行檢索的時候，全文索引就會派上用場。
這是一棵比較簡單的B+樹。
圖片來源: Data Structure Visualizations
從上面這張示例圖也可以看到，這棵B+樹最下面的葉子節點存儲了所有的元素，并且是按順序存儲的，而非葉子節點僅存儲索引列的值。
在 InnoDB 中，基于 BTree 的索引模型的最為常用的，下面以一個實際的例子來圖解 InnoDB 中 BTree 索引的結構。
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(36) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `nameIndex`(`name`) USING BTREE ) ENGINE = InnoDB;-- 插入數據insert into user1(id,name,age) values (1,'one',21),(2,'two',22),(3,'three',23),(4,'four',24),(5,'five',25);復制代碼
在這張表中只有兩個字段：主鍵 id 和 name 字段，同時建立了一個以 name 字段為索引列的 BTree 索引。
以主鍵 id 字段的為索引的索引，又叫主鍵索引，它的索引樹結構是：索引樹的非葉子階段存放的都是主鍵 id 的值，葉子節點存放的值是該主鍵 id 對應的整個數據行，如下圖所示：
也正因為主鍵索引的葉子節點存儲的是該主鍵 id 對應的整個數據行，主鍵索引又被稱為聚簇索引。
而以 name 字段為列的索引樹，非葉子節點存放的同樣是索引列的值，而其葉子階段存放的值是主鍵 id 的值，如下圖所示。
首先來看下面這句 SQL，查詢 user 表中 id=1 的數據行。
select * from user where id=1;復制代碼
這句 SQL 的執行流程很簡單，存儲引擎會走主鍵 id 的索引樹，當找到 id=1 時，就會把索引樹上 id=1 的數據行返回（由于主鍵值是唯一的，所以找到命中目標就會停止搜索，直接返回結果集）。
接下來再看使用普通索引進行查詢的情況，它的情況與主鍵索引略有不同。
select * from user where name='one';復制代碼
上面這句 SQL 查詢語句的流程是這樣的：首先存儲引擎會搜索普通索引 name 列的索引樹，當命中 name 等于 one 的記錄后，存儲引擎需要經過一個非常重要的步驟：回表。
由于普通索引的索引樹子節點存放的是主鍵值，當查詢語句需要查詢除主鍵 id 及索引列之外的其他字段時，需要根據主鍵 id 的值再回到主鍵索引樹中進行查詢，得到主鍵 id 對應的整個數據行，然后從中獲取客戶端需要的字段后，才將這一行加入結果集。
隨后存儲引擎會繼續搜索索引樹，直到遇到第一個不滿足 name='one' 的記錄才會停止搜索，最后將所有命中的記錄返回客戶端。
我們把根據從普通索引查詢到的主鍵 id 值，再在主鍵索引中查詢整個數據行的過程稱之為回表。
當數據量十分龐大時，回表是一個十分耗時的過程，所以我們應該盡量避免回表發生，這就引出了下一個問題：使用覆蓋索引避免回表。
不知道你有沒有注意到，在上一個回表的問題中有這樣一句描述：“當查詢語句需要查詢除主鍵 id 及索引列之外的其他字段時...”，在這種場景下需要通過回表來獲取其他的查詢字段。也就是說，如果查詢語句需要查詢的字段僅有主鍵 id 和索引列的字段時，是不是就不需要回表了？
下面來分析一波這個過程，首先建立一個聯合索引。
alter table user add index name_age ('name','age');復制代碼那么這棵索引樹的結構圖應該是下面這樣：
聯合索引索引樹的子節點順序是按照聲明索引時的字段來排序的，類似于 order by name, age ，而它索引對應的值與普通索引一樣是主鍵值。
select name,age from user where name='one';復制代碼
上面這條 SQL 是查詢所有 name='one' 記錄的 name 和 age 字段，理想的執行計劃應該是搜索剛剛建立的聯合索引。
與普通索引一樣，存儲引擎會搜索聯合索引，由于聯合索引的順序是先按照 name 再按照 age 進行排序的，所以當找到第一個 name 不是 one 的索引時，才會停止搜索。
而由于 SQL 語句查詢的只是 name 和 age 字段，恰好存儲引擎命中查詢條件時得到的數據正是 name, age 和 id 字段，已經包含了客戶端需要的字段了，所以就不需要再回表了。
我們把只需要在一棵索引樹上就可以得到查詢語句所需要的所有字段的索引成為覆蓋索引，覆蓋索引無須進行回表操作，速度會更快一些，所以我們在進行 SQL 優化時可以考慮使用覆蓋索引來優化。
上面所舉的例子都是使用索引的情況，事實上在項目中復雜的查詢語句中，也可能存在不使用索引的情況。首先我們要知道，MySQL 在執行 SQL 語句的時候一張表只會選擇一棵索引樹進行搜索，所以一般在建立索引時需要盡可能覆蓋所有的查詢條件，建立聯合索引。
而對于聯合索引，MySQL 會遵循最左前綴原則：查詢條件與聯合索引的最左列或最左連續多列一致，那么就可以使用該索引。
為了詳細說明最左前綴原則，同時說明最左前綴原則的一些特殊情況。
即便我們根據最左前綴的原則創建了聯合索引，還是會有一些特殊的場景會導致索引失效，下面舉例說明。
假設有一張 table 表，它有一個聯合索引，索引列為 a,b,c 這三個字段，這三個字段的長度均為10。
CREATE TABLE `demo` ( `a` varchar(1) DEFAULT NULL, `b` varchar(1) DEFAULT NULL, `c` varchar(1) DEFAULT NULL, INDEX `abc_index`(`a`, `b`, `c`) USING BTREE ) ENGINE = InnoDB;復制代碼
第一種情況是查詢條件與索引字段全部一致，并且用的是等值查詢，如：
select * from demo where a='1' and b='1' and c='1';select * from demo where c='1' and a='1' and b='1';復制代碼
輸出上述兩條 SQL 的執行計劃來看它們使用索引的情況。
mysql> explain select * from demo where a='1' and b='1' and c='1'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec) mysql> explain select * from demo where c='1' and a='1' and b='1'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)復制代碼
第一條 SQL 很顯然能夠用到聯合索引。
從執行計劃中可以看到，第二條 SQL 與第一條 SQL 使用的索引以及索引長度是一致的，都是使用 abc_index 索引，索引長度為 18 個字節。
按理說查詢條件與索引的順序不一致，應該不會用到索引，但是由于 MySQL 有優化器存在，它會把第二條 SQL 優化成第一條 SQL 的樣子，所以第二條 SQL 也使用到了聯合索引 abc_index。
綜上所述，全字段匹配且為等值查詢的情況下，查詢條件的順序不一致也能使用到聯合索引。
第二種情況是查詢條件與索引字段部分保持一致，這里就需要遵循最左前綴的原則，如：
select * from demo where a='1' and b='1';select * from demo where a='1' and c='1';復制代碼
上述的兩條查詢語句分別對應三個索引字段只用到兩個字段的情況，它們的執行計劃是：
mysql> explain select * from demo where a='1' and b='1'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 12 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec) mysql> explain select * from demo where a='1' and c='1'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 6 | const | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)復制代碼
從它們的執行計劃可以看到，這兩條查詢語句都使用到了 abc_index 索引，不同的是，它們使用到索引的長度分別是：12、6 字節。
在這里需要額外提一下索引長度的計算方式，對于本例中聲明為 varchar(1) 類型的 a 字段，它的索引長度= 1 * (3) + 1 + 2 = 6。
所以這兩條查詢語句使用索引的情況是：
由此可見：最左前綴原則要求，查詢條件必須是從索引最左列開始的連續幾列。
第三種情況是查詢條件用的是范圍查詢（<,>,!=,<=,>=,between,like）時，如：
select * from demo where a='1' and b!='1' and c='1';復制代碼
這兩條查詢語句的執行計劃是：
mysql> EXPLAIN select * from demo where a='1' and b!='1' and c='1'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 12 | NULL | 2 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)復制代碼
從執行計劃可以看到，第一條 SQL 使用了聯合索引，且索引長度為 12 字節，即用到了 a,b 兩個字段；第二條 SQL 也使用了聯合索引，索引長度為 6 字節，僅使用了聯合索引中的 a 字段。
綜上所述，在全字段匹配且為范圍查詢的情況下，也能使用聯合索引，但只能使用到聯合索引中第一個出現范圍查詢條件的字段。
需要注意的是：
第四種情況是查詢條件中帶有函數或特殊表達式的，比如：
select * from demo where id + 1 = 2;select * from demo where concat(a, '1') = '11';復制代碼
可能由于數據的原因（空表），我輸出的執行計劃是使用了聯合索引的，但是事實上，在查詢條件中，等式不等式左側的字段包含表達式或函數時，該字段是不會用到索引的。
至于原因，是因為使用函數或表達式的情況下，索引字段本身的值已不具備有序性。
上文中已經羅列了聯合索引的實際結構、最左前綴原則以及索引失效的場景，這里再說一下索引下推這個重要的優化規則。
select * from demo where a > '1' and b='1'; mysql> explain select * from demo where a > '1' and b='1'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 6 | NULL | 1 | 10.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)復制代碼
上面這條查詢語句，從它的執行計劃也可以看出，它使用的索引長度為 6 個字節，只用到了第一個字段。
所以 MySQL 在查詢過程中，只會對第一個字段 a 進行 a > '1' 的條件判斷，當滿足條件后，存儲引擎并不會進行 b=1 的判斷， 而是通過回表拿到整個數據行之后再進行判斷。
這好像很蠢，就算索引只用到了第一個字段，但明明索引樹中就有 b 字段的數據，為什么不直接進行判斷呢？
聽上去好像是個 bug，其實在未使用索引下推之前整個查詢邏輯是：由存儲引擎檢索索引樹，就算索引樹中存在 b 字段的值，但由于這條查詢語句的執行計劃使用了聯合索引但沒有用到 b 字段，所以也無法進行 b 字段的條件判斷，當存儲引擎拿到滿足條件（a>'1'）的數據后，再由 MySQL 服務器進行條件判斷。
在 MySQL5.6 版本中對這樣的情況進行優化，引入索引下推技術：在搜索索引樹的過程中，就算沒能用到聯合索引的其他字段，也能優先對查詢條件中包含且索引也包含的字段進行判斷，減少回表次數，提高查詢效率。
在使用索引下推優化之后，b 字段作為聯合索引列，又存在于查詢條件中，同時又沒有在搜索索引樹時被使用到，MySQL 服務器會把查詢條件中關于 b 字段的部分也傳給存儲引擎，存儲引擎會在搜索索引樹命中數據之后再進行 b 字段查詢條件的判斷，滿足的才會加入結果集。
Ps: 執行計劃中 Extra 字段的值包含 Using index condition 就代表使用到了索引下推。
看完了這篇文章，相信你對MySQL中索引的案例分析有了一定的了解，想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
