如何高效高性能的選擇使用 MySQL 索引？

想要實(shí)現(xiàn)高性能的查詢，正確的使用索引是基礎(chǔ)。本小節(jié)通過(guò)多個(gè)實(shí)際應(yīng)用場(chǎng)景，幫助大家理解如何高效地選擇和使用索引。

1. 獨(dú)立的列

獨(dú)立的列，是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。如果 SQL 查詢中的列不是獨(dú)立的，MySQL 不能使用該索引。

下面兩個(gè)查詢，MySQL 無(wú)法使用 id 列和 birth_date 列的索引。開(kāi)發(fā)人員應(yīng)該養(yǎng)成編寫(xiě) SQL 的好習(xí)慣，始終要將索引列單獨(dú)放在比較符號(hào)的左側(cè)。

mysql> select * from customer where id + 1 = 2;
mysql> select * from customer where to_days(birth_date) - to_days('2020-06-07') <= 10;

2. 前綴索引

有時(shí)候需要對(duì)很長(zhǎng)的字符列創(chuàng)建索引，這會(huì)使得索引變得很占空間，效率也很低下。碰到這種情況，一般可以索引開(kāi)始的部分字符，這樣可以節(jié)省索引產(chǎn)生的空間，但同時(shí)也會(huì)降低索引的選擇性。

那我們就要選擇足夠長(zhǎng)的前綴來(lái)保證較高的選擇性，但是為了節(jié)省空間，前綴又不能太長(zhǎng)，只要前綴的基數(shù)，接近于完整列的基數(shù)即可。

Tips：索引的選擇性指，不重復(fù)的索引值（也叫基數(shù)，cardinality）和數(shù)據(jù)表的記錄總數(shù)的比值，索引的選擇性越高表示查詢效率越高。

完整列的選擇性：

mysql> select count(distinct last_name)/count(*) from customer;
+------------------------------------+
| count(distinct last_name)/count(*) |
+------------------------------------+
|                              0.053 |
+------------------------------------+

不同前綴長(zhǎng)度的選擇性：

mysql> select count(distinct left(last_name,3))/count(*) left_3, count(distinct left(last_name,4))/count(*) left_4, count(distinct left(last_name,5))/count(*) left_5, count(distinct left(last_name,6))/count(*) left_6 from customer;
+--------+--------+--------+--------+
| left_3 | left_4 | left_5 | left_6 |
+--------+--------+--------+--------+
|   0.043|   0.046|   0.050|   0.051|
+--------+--------+--------+--------+

從上面的查詢可以看出，當(dāng)前綴長(zhǎng)度為 6 時(shí)，前綴的選擇性接近于完整列的選擇性 0.053，再增加前綴長(zhǎng)度，能夠提升選擇性的幅度也很小了。

創(chuàng)建前綴長(zhǎng)度為6的索引：

mysql> alter table customer add index idx_last_name(last_name(6));

前綴索引可以使索引更小更快，但同時(shí)也有缺點(diǎn)：無(wú)法使用前綴索引做 order by 和 group by，也無(wú)法使用前綴索引做覆蓋掃描。

3. 合適的索引列順序

在一個(gè)多列 B-Tree 索引中，索引列的順序表示索引首先要按照最左列進(jìn)行排序，然后是第二列、第三列等。索引可以按照升序或降序進(jìn)行掃描，以滿足精確符合列順序的 order by、group by 和 distinct 等的查詢需求。

索引的列順序非常重要，在不考慮排序和分組的情況下，通常我們會(huì)將選擇性最高的列放到索引最前面。

以下查詢，是應(yīng)該創(chuàng)建一個(gè) (last_name,first_name) 的索引，還是應(yīng)該創(chuàng)建一個(gè)(first_name,last_name) 的索引？

mysql> select * from customer where last_name = 'Allen' and first_name = 'Cuba'

我們首先來(lái)計(jì)算下這兩個(gè)列的選擇性，看哪個(gè)列更高。

mysql> select count(distinct last_name)/count(*) last_name_selectivity, count(distinct first_name)/count(*) first_name_selectivity from customer;
+-----------------------+------------------------+
| last_name_selectivity | first_name_selectivity |
+-----------------------+------------------------+
|                 0.053 |                  0.372 |
+-----------------------+------------------------+

很明顯，列 first_name 的選擇性更高，所以選擇 first_name 作為索引列的第一列：

mysql> alter table customer add index idx1_customer(first_name,last_name);

4. 覆蓋索引

如果一個(gè)索引包含所有需要查詢的字段，稱之為覆蓋索引。由于覆蓋索引無(wú)須回表，通過(guò)掃描索引即可拿到所有的值，它能極大地提高查詢效率：索引條目一般比數(shù)據(jù)行小的多，只通過(guò)掃描索引即可滿足查詢需求，MySQL 可以極大地減少數(shù)據(jù)的訪問(wèn)量。

表 customer 有一個(gè)多列索引 (first_name,last_name)，以下查詢只需要訪問(wèn) first_name 和last_name，這時(shí)就可以通過(guò)這個(gè)索引來(lái)實(shí)現(xiàn)覆蓋索引。

mysql> explain select last_name, first_name from customer\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx1_customer
      key_len: 186
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

當(dāng)查詢?yōu)楦采w索引查詢時(shí)，在 explain 的 extra 列可以看到 Using index。

5. 使用索引實(shí)現(xiàn)排序

MySQL 可以通過(guò)排序操作，或者按照索引順序掃描來(lái)生成有序的結(jié)果。如果 explain 的 type 列的值為index，說(shuō)明該查詢使用了索引掃描來(lái)做排序。

order by 和查詢的限制是一樣的，需要滿足索引的最左前綴要求，否則無(wú)法使用索引進(jìn)行排序。只有當(dāng)索引的列順序和 order by 子句的順序完全一致，并且所有列的排序方向（正序或倒序)都一致，MySQL才能使用索引來(lái)做排序。如果查詢是多表關(guān)聯(lián)，只有當(dāng) order by 子句引用的字段全部為第一個(gè)表時(shí)，才能使用索引來(lái)做排序。

以表 customer 為例，我們來(lái)看看哪些查詢可以通過(guò)索引進(jìn)行排序。

mysql> create table customer(
		 id int,
         last_name varchar(30),
		 first_name varchar(30),
		 birth_date date,
		 gender char(1),
		 key idx_customer(last_name,first_name,birth_date)
     );

5.1 可以通過(guò)索引進(jìn)行排序的查詢

索引的列順序和 order by 子句的順序完全一致：

mysql> explain select last_name,first_name from customer order by last_name, first_name, birth_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_customer
      key_len: 190
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

索引的第一列指定為常量：

從 explain 可以看到?jīng)]有出現(xiàn)排序操作（filesort）：

mysql> explain select * from customer where last_name = 'Allen' order by first_name, birth_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

索引的第一列指定為常量，使用第二列排序：

mysql> explain select * from customer where last_name = 'Allen' order by first_name desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

索引的第一列為范圍查詢，order by 使用的兩列為索引的最左前綴：

mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by last_name,first_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: range
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

5.2 不能通過(guò)索引進(jìn)行排序的查詢

使用兩種不同的排序方向：

mysql> explain select * from customer where last_name = 'Allen' order by first_name desc, birth_date asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

order by 子句引用了一個(gè)不在索引的列：

mysql> explain select * from customer where last_name = 'Allen' order by first_name, gender\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

where 條件和 order by 的列無(wú)法組成索引的最左前綴：

mysql> explain select * from customer where last_name = 'Allen' order by birth_date\G

第一列是范圍查詢，where 條件和 order by 的列無(wú)法組成索引的最左前綴：

mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by first_name\G

第一列是常量，第二列是范圍查詢（多個(gè)等于也是范圍查詢）：

mysql> explain select * from customer where last_name = 'Allen' and first_name in ('Cuba','Kim') order by birth_date\G

6. 小結(jié)

本小節(jié)介紹了高效使用索引的多種方法：獨(dú)立的列、前綴索引、合適的索引列順序、覆蓋索引、使用索引實(shí)現(xiàn)排序。應(yīng)該使用哪個(gè)索引，以及評(píng)估選擇不同索引的性能影響，需要不斷地學(xué)習(xí)。