腳本之家服務器常用軟件

快捷導航

MySQL?中這么多索引該怎么選擇

更新時間：2022年09月26日 09:44:47 作者：NPy

這篇文章主要介紹了MySQL?中這么多索引該怎么選擇，索引的本質(zhì)是存儲引擎用于快速查詢記錄的一種數(shù)據(jù)結構。特別是數(shù)據(jù)表中數(shù)據(jù)特別多的時候，索引對于數(shù)據(jù)庫的性能就愈發(fā)重要，下文詳細相關內(nèi)容介紹，需要的小伙伴可以參考一下

前言

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

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

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

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

當我們查詢一條數(shù)據(jù)的時候，我們首先在索引中查詢到對應的值，然后根據(jù)匹配到的索引去找到對應數(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ù)庫來說，索引是由存儲引擎實現(xiàn)的，所以不同的存儲引擎提供的索引也不一樣。下面我們就來了解一下 MySQL 數(shù)據(jù)庫中各種索引的優(yōu)缺點。

MySQL 單字段索引問題

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

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)

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

但是，在explain表達式中可以看出select * from city where fid + 1 = 2;是無法命中索引的。這是因為 MySQL 數(shù)據(jù)庫無法解析fid + 1 = 2這個表達式，所以我們在使用索引時，索引的列不能夠是一個表達式。

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

組合索引

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

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

這個時候，我們?nèi)绻胩岣卟樵兯俣?，一般就會選擇在fid字段和name字段上分別加上一個索引，但實際上這種做法是不恰當?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ū)'包含的所有結果；
其次，再根據(jù)fid字段全表掃描查詢出fid = 1包含的所有結果；
最后，通過UNION ALL將所有的結果組合到一起并返回。

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

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

具體如下：

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包含的所有結果；
最后，通過UNION ALL將所有的結果組合到一起并返回。

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

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

唯一索引和普通索引

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

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

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

假設現(xiàn)在我們有一個訂單系統(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 字段上設置唯一索引時，具體步驟如下：

MySQL 首先會在B-Tree的子樹上查詢 order_id = 52355096；
再根據(jù)查詢到的索引值，通過主鍵索引查詢出對應的記錄；
組裝結果并返回。

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

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

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

那么，在這個過程當中，普通索引與唯一索引之間的性能差多少呢？其實是微乎其微的。這是因為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ù)庫的性能。這是因為唯一索引在修改數(shù)據(jù)之前，還需要判斷該條數(shù)據(jù)是否唯一，這樣的話就需要將所有的數(shù)據(jù)全部掃描一遍，進而達到數(shù)據(jù)唯一。那么這樣就不需要使用Change Buffer了，因為在修改之前，唯一索引會將所有的數(shù)據(jù)全部讀取到Buffer Pool之中，直接在內(nèi)存修改即可。但是不可避免的是，唯一索引會將所有的數(shù)據(jù)全部獨到內(nèi)存之中，無異于一次全表掃描。

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

總結

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

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

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

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

您可能感興趣的文章: