MySQL中Innodb存儲(chǔ)引擎索引的示例分析

這篇文章主要為大家展示了“MySQL中Innodb存儲(chǔ)引擎索引的示例分析”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL中Innodb存儲(chǔ)引擎索引的示例分析”這篇文章吧。

概述

在數(shù)據(jù)庫(kù)當(dāng)中，索引就跟樹(shù)的目錄一樣用來(lái)加快數(shù)據(jù)的查找速度，對(duì)于一個(gè)SQL查詢操作，根據(jù)索引快速過(guò)濾掉不符合要求的數(shù)據(jù)并定位到符合要求的數(shù)據(jù)，從而不需要掃描整個(gè)表來(lái)獲取所需的數(shù)據(jù)。

在innodb存儲(chǔ)引擎中，主要是基于B+樹(shù)來(lái)實(shí)現(xiàn)索引，在非葉子節(jié)點(diǎn)存放索引關(guān)鍵字，在葉子節(jié)點(diǎn)存放數(shù)據(jù)記錄或者主鍵索引（或者說(shuō)是聚簇索引）中的主鍵值，所有的數(shù)據(jù)記錄都在同一層，葉子節(jié)點(diǎn)，即數(shù)據(jù)記錄直接之間通過(guò)指針相連，構(gòu)成一個(gè)雙向鏈表，從而可以方便地遍歷到所有的或者某一范圍的數(shù)據(jù)記錄。

B樹(shù)，B+樹(shù)

B樹(shù)和B+樹(shù)都是多路平衡搜索樹(shù)，通過(guò)在每個(gè)節(jié)點(diǎn)存放更多的關(guān)鍵字和通過(guò)旋轉(zhuǎn)、分裂操作來(lái)保持樹(shù)的平衡來(lái)降低樹(shù)的高度，從而減少數(shù)據(jù)檢索的磁盤(pán)訪問(wèn)量。

B+樹(shù)相對(duì)于B樹(shù)的一個(gè)主要的不同點(diǎn)是B+的葉子節(jié)點(diǎn)通過(guò)指針前后相連，具體為通過(guò)雙向鏈表來(lái)前后相連，所以非常適合執(zhí)行范圍查找。具體可以參考：

數(shù)據(jù)結(jié)構(gòu)-樹(shù)（三）：多路搜索樹(shù)B樹(shù)、B+樹(shù)

innodb存儲(chǔ)引擎的聚簇和非聚簇索引都是基于B+樹(shù)實(shí)現(xiàn)的。
主鍵索引

innodb存儲(chǔ)引擎使用主鍵索引作為表的聚簇索引，聚簇索引的特點(diǎn)是非葉子節(jié)點(diǎn)存放主鍵作為查找關(guān)鍵字，葉子節(jié)點(diǎn)存放實(shí)際的數(shù)據(jù)記錄本身（也稱為數(shù)據(jù)頁(yè)），從左到右以關(guān)鍵字的順序，存放數(shù)據(jù)記錄，故聚簇索引其實(shí)就是數(shù)據(jù)存放的方式，所以每個(gè)表只能存在一個(gè)聚簇索引，innodb存儲(chǔ)引擎的數(shù)據(jù)表也稱為索引組織表。結(jié)構(gòu)如下：（圖片引自《MySQL技術(shù)內(nèi)幕：Innodb存儲(chǔ)引擎》）

在查詢當(dāng)中，如果是通過(guò)主鍵來(lái)查找數(shù)據(jù)，即使用explain分析SQL的key顯示PRIMARY時(shí)，查找效率是最高的，因?yàn)槿~子節(jié)點(diǎn)存放的就是數(shù)據(jù)記錄本身，所有可以直接返回，而不需要像非聚簇索引一樣需要通過(guò)額外回表查詢（在主鍵索引中）獲取數(shù)據(jù)記錄。

其次是對(duì)于ORDER BY排序操作，不管是正序ASC還是逆序DESC，如果ORDER BY的列是主鍵，則由于主鍵索引對(duì)應(yīng)的B+樹(shù)本身是有序的， 故存儲(chǔ)引擎返回的數(shù)據(jù)就是已經(jīng)根據(jù)主鍵有序的，不需要在MySQL服務(wù)器層再進(jìn)行排序，提高了性能，如果通過(guò)explain分析SQL時(shí)，extra顯示Using filesort，則說(shuō)明需要在MySQL服務(wù)器層進(jìn)行排序，此時(shí)可能需要使用臨時(shí)表或者外部文件排序，這種情況一般需要想辦法優(yōu)化。

對(duì)于基于主鍵的范圍查找，由于聚簇索引的葉子節(jié)點(diǎn)已經(jīng)根據(jù)主鍵的順序，使用雙向鏈表進(jìn)行了相連，故可以快速找到某一范圍的數(shù)據(jù)記錄。

輔助索引

輔助索引也稱為二級(jí)索引，是一種非聚簇索引，一般是為了提高某些查詢的效率而設(shè)計(jì)的，即使用該索引列查詢時(shí)，通過(guò)輔助索引來(lái)避免全表掃描。由于輔助索引不是聚簇索引，每個(gè)表可以存在多個(gè)輔助索引，結(jié)構(gòu)如下：

輔助索引的非葉子節(jié)存放索引列的關(guān)鍵字，葉子節(jié)點(diǎn)存放對(duì)應(yīng)聚簇索引（或者說(shuō)是主鍵索引）的主鍵值。即通過(guò)輔助索引定位到需要的數(shù)據(jù)后，如果不能通過(guò)索引覆蓋所需列，即通過(guò)該輔助索引列來(lái)獲取該次查詢所需的所有數(shù)據(jù)列，則需要通過(guò)該對(duì)應(yīng)聚簇索引的主鍵值定位到在聚簇索引中的主鍵，然后再通過(guò)該主鍵值在聚簇索引中找到對(duì)應(yīng)的葉子頁(yè)，從而獲取到對(duì)應(yīng)的數(shù)據(jù)記錄，所以整個(gè)過(guò)程涉及到先在輔助索引中查找，再在聚簇索引（即主鍵索引）中查找（回表查詢）兩個(gè)過(guò)程。

舉個(gè)例子：

1. 輔助索引對(duì)應(yīng)的B+樹(shù)的高度為3，則需要3次磁盤(pán)IO來(lái)定位到葉子節(jié)點(diǎn)，其中葉子節(jié)點(diǎn)包含對(duì)應(yīng)聚簇索引的某個(gè)主鍵值；

2. 然后通過(guò)葉子節(jié)點(diǎn)的對(duì)應(yīng)聚簇索引的主鍵值，在聚簇索引中找到對(duì)應(yīng)的數(shù)據(jù)記錄，即如果聚簇索引對(duì)應(yīng)的B+樹(shù)高度也是3，則也需要3次磁盤(pán)IO來(lái)定位到聚簇索引的葉子頁(yè)，從而在該葉子頁(yè)中獲取實(shí)際的數(shù)據(jù)記錄。

以上過(guò)程總共需要進(jìn)行6次磁盤(pán)IO。故如果需要回表查詢的數(shù)據(jù)行較多，則所需的磁盤(pán)IO將會(huì)成倍增加，查詢性能會(huì)下降。所以需要在過(guò)濾程度高，即重復(fù)數(shù)據(jù)少的列來(lái)建立輔助索引。

Cardinality：索引列的數(shù)據(jù)重復(fù)度

由以上分析可知，通過(guò)輔助索引進(jìn)行查詢時(shí)，如果需要回表查詢并且查詢的數(shù)據(jù)行較多時(shí)，需要大量的磁盤(pán)IO來(lái)獲取數(shù)據(jù)，故這種索引不但沒(méi)有提供查詢性能，反而會(huì)降低查詢性能，并且MySQL優(yōu)化器在需要返回較多數(shù)據(jù)行時(shí)，也會(huì)放棄使用該索引，直接進(jìn)行全表掃描。所以輔助索引所選擇的列需要是重復(fù)度低的列，即一般查詢后只需要返回一兩行數(shù)據(jù)。如果該列存在太多的重復(fù)值，則需要考慮放棄在該列建立輔助索引。

具體可以通過(guò)：SHOW INDEX FROM 數(shù)據(jù)表，的Cardinality的值來(lái)判斷：

mysql> SHOW INDEX FROM store_order;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| store_order |   0 | PRIMARY |   1 | store_id | A   |   201 |  NULL | NULL |  | BTREE  |   |    |
| store_order |   1 | idx_expire |   1 | expire_date | A   |   68 |  NULL | NULL | YES | BTREE  |   |    |
| store_order |   1 | idx_ul  |   1 | ul   | A   |   22 |  NULL | NULL | YES | BTREE  |   |    |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

Cardinality表示索引列的唯一值的估計(jì)數(shù)量，如果跟數(shù)據(jù)行的數(shù)量接近，則說(shuō)明該列存在的重復(fù)值少，列的過(guò)濾性較好；如果相差太大，即Cardinality / 數(shù)據(jù)行總數(shù)，的值太小，如性別列只包含“男”，“女”兩個(gè)值，則說(shuō)明該列存在大量重復(fù)值，需要考慮是否刪除該索引。

覆蓋索引

1. 由于回表查詢開(kāi)銷較大，故為了減少回表查詢的次數(shù)，可以在輔助索引中增加查詢所需要的所有列，如使用聯(lián)合索引，這樣可以從輔助索引中獲取查詢所需的所有數(shù)據(jù)（由于輔助索引的葉子頁(yè)包含主鍵值，即使索引沒(méi)有該主鍵值，如果只需返回主鍵值和索引列，則也會(huì)使用覆蓋索引），不需要回表查詢完整的數(shù)據(jù)行，從而提高性能，這種機(jī)制稱為覆蓋索引。

2. 當(dāng)使用explain分析查詢SQL時(shí)，如果extra顯示 using index 則說(shuō)明使用了覆蓋索引返回?cái)?shù)據(jù)，該查詢性能較高。

3. 由于索引的存在會(huì)增加更新數(shù)據(jù)的開(kāi)銷，即更新數(shù)據(jù)時(shí)，如增加和刪除數(shù)據(jù)行，需要通過(guò)更新對(duì)應(yīng)的輔助索引，故在具體設(shè)計(jì)時(shí)，需要在兩者之間取個(gè)折中。

聯(lián)合索引與最左前戳匹配

1. 聯(lián)合索引是使用多個(gè)列作為索引，如（a,b,c)，表示使用a，b，c三個(gè)列來(lái)作為索引，由B+樹(shù)的特征可知，索引都是需要符合最左前戳匹配的，故其實(shí)相當(dāng)于建立a，（a,b），(a,b,c)三個(gè)索引。

2. 所以在設(shè)計(jì)聯(lián)合索引時(shí)，除了需要考慮是否可以優(yōu)化為覆蓋索引外，還需要考慮多個(gè)列的順序，一般的經(jīng)驗(yàn)是：查詢頻率最高，過(guò)濾性最好（重復(fù)值較少)的列在前，即左邊。

聯(lián)合索引優(yōu)化排序order by

除此之外，可以考慮通過(guò)聯(lián)合索引來(lái)減少M(fèi)ySQL服務(wù)端層的排序，如用戶訂單表包含聯(lián)合索引(user_id, buy_date)，單列索引(user_id)：（注意這里只是為了演示聯(lián)合索引，實(shí)際項(xiàng)目，只需聯(lián)合索引即可，如上所述，(a,b)，相當(dāng)于a, (a,b)兩個(gè)索引）：

KEY `idx_user_id` (`user_id`),
KEY `idx_user_id_buy_date` (`user_id`,`buy_date`)

如果只是普通的查詢某個(gè)用戶的訂單，則innodb會(huì)使用user_id索引，如下：

mysql> explain select user_id, order_id from t_order where user_id = 1;
+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key   | key_len | ref | rows | filtered | Extra  |
+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE  | t_order | NULL  | ref | idx_user_id,idx_user_id_buy_date | idx_user_id | 4  | const | 4 | 100.00 | Using index |
+----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

但是當(dāng)需要基于購(gòu)買(mǎi)日期buy_date來(lái)排序并取出該用戶最近3天的購(gòu)買(mǎi)記錄時(shí)，則單列索引user_id和聯(lián)合索引（user_id, buy_date）都可以使用，innodb會(huì)選擇使用聯(lián)合索引，因?yàn)樵谠撀?lián)合索引中buy_date已經(jīng)有序了，故不需要再在MySQL服務(wù)器層進(jìn)行一次排序，從而提高了性能，如下：

mysql> explain select user_id, order_id from t_order where user_id = 1 order by buy_date limit 3;
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys     | key     | key_len | ref | rows | filtered | Extra     |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE  | t_order | NULL  | ref | idx_user_id,idx_user_id_buy_date | idx_user_id_buy_date | 4  | const | 4 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

如果刪除idx_user_id_buy_date這個(gè)聯(lián)合索引，則顯示Using filesort：

mysql> alter table t_order drop index idx_user_id_buy_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select user_id, order_id from t_order where user_id = 1 order by buy_date limit 3;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra      |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE  | t_order | NULL  | ALL | idx_user_id | NULL | NULL | NULL | 4 | 100.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

以上是“MySQL中Innodb存儲(chǔ)引擎索引的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！