前言

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

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

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

在了解怎么選擇索引之前，我先給你舉一個(gè)例子。如果我們在字典中用拼音查詢某一個(gè)字，首先我們得根據(jù)拼音字母進(jìn)而找到對應(yīng)的頁碼。索引也是這個(gè)原理。

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

例如：

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

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

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

MySQL 單字段索引問題

在 MySQL 數(shù)據(jù)庫中，索引不能夠使用表達(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;是無法命中索引的。這是因?yàn)?MySQL 數(shù)據(jù)庫無法解析fid + 1 = 2這個(gè)表達(dá)式，所以我們在使用索引時(shí)，索引的列不能夠是一個(gè)表達(dá)式。

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

組合索引

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

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

這個(gè)時(shí)候，我們?nèi)绻胩岣卟樵兯俣?，一般就會選擇在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)

我們可以看出，本次查詢并沒有使用到任何索引。

具體步驟如下：

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

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

為了解決多個(gè)字段同時(shí)需要索引的這一問題，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)

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

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

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

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

唯一索引和普通索引

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

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

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

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

具體如下：

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 首先會在B-Tree的子樹上查詢 order_id = 52355096；
• 再根據(jù)查詢到的索引值，通過主鍵索引查詢出對應(yīng)的記錄；
• 組裝結(jié)果并返回。

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

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

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

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

聊完普通索引與唯一索引讀的性能之后，我們再來聊一下寫的性能。

具體如下：

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

對于MySQL來說，寫的過程如下。

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

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

• 之后，再統(tǒng)一寫入磁盤。

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

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

總結(jié)

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

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

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

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

最新評論