前言

索引的本質(zhì)是存儲(chǔ)引擎用于快速查詢(xún)記錄的一種數(shù)據(jù)結(jié)構(gòu)。特別是數(shù)據(jù)表中數(shù)據(jù)特別多的時(shí)候，索引對(duì)于數(shù)據(jù)庫(kù)的性能就愈發(fā)重要。

在數(shù)據(jù)量比較大的時(shí)候，不恰當(dāng)?shù)乃饕龑?duì)于數(shù)據(jù)庫(kù)的性能的影響是非常大的。在實(shí)際的應(yīng)用中常常會(huì)遇見(jiàn)使用錯(cuò)誤的索引而導(dǎo)致一系列問(wèn)題，所以，選擇正確的索引對(duì)于 MySQL 數(shù)據(jù)庫(kù)來(lái)說(shuō)相當(dāng)重要。

下面我們就來(lái)一起聊聊在 MySQL 數(shù)據(jù)庫(kù)中該怎么選擇正確的索引。

在了解怎么選擇索引之前，我先給你舉一個(gè)例子。如果我們?cè)谧值渲杏闷匆舨樵?xún)某一個(gè)字，首先我們得根據(jù)拼音字母進(jìn)而找到對(duì)應(yīng)的頁(yè)碼。索引也是這個(gè)原理。

當(dāng)我們查詢(xún)一條數(shù)據(jù)的時(shí)候，我們首先在索引中查詢(xún)到對(duì)應(yīng)的值，然后根據(jù)匹配到的索引去找到對(duì)應(yīng)數(shù)據(jù)。

例如：

mysql> select name from city where fid = 1;
+--------------+
| name         |
+--------------+
| 浦東新區(qū)      |
+--------------+
1 row in set (0.00 sec)

如果我們?cè)?code>fid字段上建立索引，那么 MySQL 數(shù)據(jù)庫(kù)就會(huì)使用索引找到fid = 1的行，然后返回包含fid = 1的行中的所有數(shù)據(jù)。

對(duì)于 MySQL 數(shù)據(jù)庫(kù)來(lái)說(shuō)，索引是由存儲(chǔ)引擎實(shí)現(xiàn)的，所以不同的存儲(chǔ)引擎提供的索引也不一樣。下面我們就來(lái)了解一下 MySQL 數(shù)據(jù)庫(kù)中各種索引的優(yōu)缺點(diǎn)。

MySQL 單字段索引問(wèn)題

在 MySQL 數(shù)據(jù)庫(kù)中，索引不能夠使用表達(dá)式，具體如下：

mysql> explain select * from city where fid + 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from city where fid = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1       | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

從結(jié)果上講，select * from city where fid + 1 = 2; 和 select * from city where fid = 1;是完全一致的。

但是，在explain表達(dá)式中可以看出select * from city where fid + 1 = 2;是無(wú)法命中索引的。這是因?yàn)?MySQL 數(shù)據(jù)庫(kù)無(wú)法解析fid + 1 = 2這個(gè)表達(dá)式，所以我們?cè)谑褂盟饕龝r(shí)，索引的列不能夠是一個(gè)表達(dá)式。

總之，通常情況下，對(duì)于單個(gè)字段的索引來(lái)說(shuō)，必須直接使用，不能夠使用一個(gè)表達(dá)式。

組合索引

我們經(jīng)常會(huì)遇見(jiàn)這樣一個(gè)場(chǎng)景，假設(shè)要求查詢(xún)fid=1或者name='青浦區(qū)'，這個(gè)時(shí)候我們查詢(xún)的SQL語(yǔ)句如下：

select * from city where fid = 1 or name = '青浦區(qū)';

這個(gè)時(shí)候，我們?nèi)绻胩岣卟樵?xún)速度，一般就會(huì)選擇在fid字段和name字段上分別加上一個(gè)索引，但實(shí)際上這種做法是不恰當(dāng)?shù)摹?/p>

具體如下：

mysql> explain select * from city where name = '青浦區(qū)' or fid = 1;
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1,index_2 | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

我們可以看出，本次查詢(xún)并沒(méi)有使用到任何索引。

具體步驟如下：

• 首先，根據(jù)name字段全表掃描查詢(xún)出name = '青浦區(qū)'包含的所有結(jié)果；
• 其次，再根據(jù)fid字段全表掃描查詢(xún)出fid = 1包含的所有結(jié)果；
• 最后，通過(guò)UNION ALL將所有的結(jié)果組合到一起并返回。

在這一過(guò)程中，MySQL 數(shù)據(jù)庫(kù)需要通過(guò)全表掃描兩次才能查詢(xún)出結(jié)果。如果有更多的條件，查詢(xún)的次數(shù)會(huì)更多。所以，在大多數(shù)情況下，多個(gè)條件查詢(xún)?cè)诙鄠€(gè)字段上建立索引并不能夠提高MySQL的查詢(xún)性能。

為了解決多個(gè)字段同時(shí)需要索引的這一問(wèn)題，MySQL 5.0之后的版本中提供了一個(gè)組合索引。它主要是將所有的字段組合建立一個(gè)索引，這樣就可以直接利用索引匹配，而不需要全表掃描了。

具體如下：

mysql> explain select * from city where name = '青浦區(qū)' or fid = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | index | index_3       | index_3 | 772     | NULL |    5 |    36.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

我們可以看出，利用了組合索引之后的查詢(xún)是使用到了索引，具體如下：

• 首先，根據(jù)索引匹配出name = '青浦區(qū)'的所有的內(nèi)容；
• 第二次查詢(xún)?nèi)匀皇歉鶕?jù)fid字段全表掃描查詢(xún)出fid = 1包含的所有結(jié)果；
• 最后，通過(guò)UNION ALL將所有的結(jié)果組合到一起并返回。

在這一過(guò)程中，MySQL 數(shù)據(jù)庫(kù)需要通過(guò)索引匹配兩次就能查詢(xún)出結(jié)果。所以，在大多數(shù)情況下，當(dāng)有多個(gè)條件查詢(xún)時(shí)，組合索引可以有效地提高MySQL的查詢(xún)性能。

講完單字段索引和組合索引之后，下面我們可以聊一下唯一索引和普通索引的區(qū)別以及使用場(chǎng)景。

唯一索引和普通索引

說(shuō)起唯一索引和普通索引，有朋友可能就非常熟悉。普通索引的主要特征就是提高了查詢(xún)的速度，唯一索引的主要特征除了提高查詢(xún)的速度外就是所有字段的值唯一。

那么，我現(xiàn)在提一個(gè)問(wèn)題，唯一索引和普通索引都應(yīng)該在什么場(chǎng)景下使用呢？一定是需要唯一值的場(chǎng)景下才使用唯一索引嗎？下面我們就來(lái)對(duì)比著聊一下普通索引和唯一索引。

為了加強(qiáng)了解，我們從讀寫(xiě)性能方面來(lái)聊一下普通索引和唯一索引。

假設(shè)現(xiàn)在我們有一個(gè)訂單系統(tǒng)，訂單號(hào)唯一，那么我們看一下訂單號(hào)在使用唯一索引和普通索引的情況下讀的性能。

具體如下：

mysql> select * from sp_order where order_id = 52355096;
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
| id | order_id | user_id | order_number | order_price | order_pay | pay_status | create_time | update_time |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
|  1 | 52355096 |     410 | DD52355096   |      332.44 | 2         | 1          |  1509051984 |  1507411372 |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
1 row in set (0.00 sec)

在 order_id 字段上設(shè)置唯一索引時(shí)，具體步驟如下：

• MySQL 首先會(huì)在B-Tree的子樹(shù)上查詢(xún) order_id = 52355096；
• 再根據(jù)查詢(xún)到的索引值，通過(guò)主鍵索引查詢(xún)出對(duì)應(yīng)的記錄；
• 組裝結(jié)果并返回。

在 order_id 字段上設(shè)置普通索引時(shí)，具體步驟如下：

• MySQL 首先會(huì)在B-Tree的子樹(shù)上查詢(xún) order_id = 52355096；
• 繼續(xù)向下匹配，直至匹配到 order_id 不等于 52355096時(shí)；
• 再根據(jù)查詢(xún)到的索引值，通過(guò)主鍵索引查詢(xún)出對(duì)應(yīng)的記錄；
• 組裝結(jié)果并返回。

唯一索引與普通索引之間對(duì)比之后，可以發(fā)現(xiàn)：普通索引比唯一索引多了一個(gè)步驟，就是唯一索引匹配成功之后直接返回，而普通索引還需要往下繼續(xù)匹配直至條件不符合為止。

那么，在這個(gè)過(guò)程當(dāng)中，普通索引與唯一索引之間的性能差多少呢？其實(shí)是微乎其微的。這是因?yàn)?code>B-Tree算法將相鄰或相近的數(shù)據(jù)都放在相鄰的子樹(shù)之中，索引查詢(xún)性能相差無(wú)幾。

聊完普通索引與唯一索引讀的性能之后，我們?cè)賮?lái)聊一下寫(xiě)的性能。

具體如下：

mysql> update sp_order set order_price = '888' where order_id = 52355096;

對(duì)于MySQL來(lái)說(shuō)，寫(xiě)的過(guò)程如下。

• 首先判斷需要修改的數(shù)據(jù)是否在Buffer Pool之中。

  • 如果該數(shù)據(jù)在Buffer Pool之中，則直接修改邏輯記錄到Buffer Pool中的數(shù)據(jù)。
  • 如果該數(shù)據(jù)不在Buffer Pool之中，MySQL 會(huì)將這一修改的過(guò)程記錄在Change Buffer之中。之后如果該條數(shù)據(jù)被查詢(xún)到，則會(huì)將該修改過(guò)程merge到Buffer Pool之中，確保數(shù)據(jù)一致性。

• 之后，再統(tǒng)一寫(xiě)入磁盤(pán)。

那么對(duì)于普通索引來(lái)說(shuō)，完全適用于這一過(guò)程；但是對(duì)于唯一索引來(lái)說(shuō)，按著這種方式修改數(shù)據(jù)則會(huì)影響 MySQL 數(shù)據(jù)庫(kù)的性能。這是因?yàn)槲ㄒ凰饕谛薷臄?shù)據(jù)之前，還需要判斷該條數(shù)據(jù)是否唯一，這樣的話就需要將所有的數(shù)據(jù)全部掃描一遍，進(jìn)而達(dá)到數(shù)據(jù)唯一。那么這樣就不需要使用Change Buffer了，因?yàn)樵谛薷闹?，唯一索引?huì)將所有的數(shù)據(jù)全部讀取到Buffer Pool之中，直接在內(nèi)存修改即可。但是不可避免的是，唯一索引會(huì)將所有的數(shù)據(jù)全部獨(dú)到內(nèi)存之中，無(wú)異于一次全表掃描。

于是，我們可以得出：唯一索引和普通索引都適用于讀的場(chǎng)景，而唯一索引不適用于寫(xiě)的場(chǎng)景。

總結(jié)

本次我從根本上給你介紹了各種索引的情況。

• 對(duì)于單個(gè)字段的索引來(lái)說(shuō)，要直接使用，而不能寫(xiě)成一個(gè)表達(dá)式，寫(xiě)成表達(dá)式將會(huì)無(wú)法命中索引。
• 對(duì)于多個(gè)字段需要索引來(lái)說(shuō)，一般需要?jiǎng)?chuàng)建組合索引，這樣有利于命中索引，但是一定要注意組合索引的前綴性。
• 對(duì)于索引的類(lèi)型，我還給你介紹了唯一索引和普通索引，在讀的場(chǎng)景比較多的情況下普通索引和唯一索引都能勝任，不過(guò)在寫(xiě)場(chǎng)景比較多的情況下，普通索引的性能要優(yōu)于唯一索引。

在實(shí)際應(yīng)用中，我們通常建議使用普通索引，對(duì)于需要唯一的字段，我們一般在代碼的層面去控制其唯一性。

到此這篇關(guān)于MySQL 中這么多索引該怎么選擇的文章就介紹到這了,更多相關(guān)MySQL 索引選擇內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論