前言

數(shù)據(jù)庫(kù)系列更新到現(xiàn)在我想大家對(duì)所有的概念都已有個(gè)大概認(rèn)識(shí)了，這周我在看評(píng)論的時(shí)候我發(fā)現(xiàn)有個(gè)網(wǎng)友的提問(wèn)我覺(jué)得很有意思：帥丙如何設(shè)計(jì)一個(gè)索引?你們都是怎么設(shè)計(jì)索引的?怎么設(shè)計(jì)更高效?

我一想索引我寫(xiě)過(guò)很多了呀，沒(méi)道理讀者還不會(huì)啊，但是我一回頭看完，那確實(shí)，我就寫(xiě)了索引的概念，優(yōu)劣勢(shì)，沒(méi)提到怎么設(shè)計(jì)，那這篇文章又這樣應(yīng)運(yùn)而生了。

本文

還是會(huì)有很多之前寫(xiě)過(guò)的重復(fù)概念，但是也是為了大家能更好的理解MySQL中幾種索引設(shè)計(jì)的原理。

正文我們知道，索引是一個(gè)基于鏈表實(shí)現(xiàn)的樹(shù)狀Tree結(jié)構(gòu)，能夠快速的檢索數(shù)據(jù)，目前幾乎所RDBMS數(shù)據(jù)庫(kù)都實(shí)現(xiàn)了索引特性，比如MySQL的B+Tree索引，MongoDB的BTree索引等。

在業(yè)務(wù)開(kāi)發(fā)過(guò)程中，索引設(shè)計(jì)高效與否決定了接口對(duì)應(yīng)SQL的執(zhí)行效率，高效的索引可以降低接口的Response Time，同時(shí)還可以降低成本，我們要現(xiàn)實(shí)的目標(biāo)是：索引設(shè)計(jì)->降低接口響應(yīng)時(shí)間->降低服務(wù)器配置->降低成本，最終要落實(shí)到成本上來(lái)，因?yàn)槔习遄铌P(guān)心的是成本。

今天就跟大家聊聊MySQL中的索引以及如何設(shè)計(jì)索引，使用索引才能提降低接口的RT，提高用戶體檢。

MySQL中的索引

MySQL中的InnoDB引擎使用B+Tree結(jié)構(gòu)來(lái)存儲(chǔ)索引，可以盡量減少數(shù)據(jù)查詢時(shí)磁盤(pán)IO次數(shù)，同時(shí)樹(shù)的高度直接影響了查詢的性能，一般樹(shù)的高度維持在 3~4 層。

B+Tree由三部分組成：根root、枝branch以及Leaf葉子，其中root和branch不存儲(chǔ)數(shù)據(jù)，只存儲(chǔ)指針地址，數(shù)據(jù)全部存儲(chǔ)在Leaf Node，同時(shí)Leaf Node之間用雙向鏈表鏈接，結(jié)構(gòu)如下：

從上面可以看到，每個(gè)Leaf Node是三部分組成的，即前驅(qū)指針p_prev，數(shù)據(jù)data以及后繼指針p_next，同時(shí)數(shù)據(jù)data是有序的，默認(rèn)是升序ASC，分布在B+tree右邊的鍵值總是大于左邊的，同時(shí)從root到每個(gè)Leaf的距離是相等的，也就是訪問(wèn)任何一個(gè)Leaf Node需要的IO是一樣的，即索引樹(shù)的高度Level + 1次IO操作。

我們可以將MySQL中的索引可以看成一張小表，占用磁盤(pán)空間，創(chuàng)建索引的過(guò)程其實(shí)就是按照索引列排序的過(guò)程，先在sort_buffer_size進(jìn)行排序，如果排序的數(shù)據(jù)量大，sort_buffer_size容量不下，就需要通過(guò)臨時(shí)文件來(lái)排序，最重要的是通過(guò)索引可以避免排序操作(distinct，group by，order by)。

聚集索引

MySQL中的表是IOT(Index Organization Table，索引組織表)，數(shù)據(jù)按照主鍵id順序存儲(chǔ)(邏輯上是連續(xù)，物理上不連續(xù))，而且主鍵id是聚集索引(clustered index)，存儲(chǔ)著整行數(shù)據(jù)，如果沒(méi)有顯示的指定主鍵，MySQL會(huì)將所有的列組合起來(lái)構(gòu)造一個(gè)row_id作為primary key，例如表users(id, user_id, user_name, phone, primary key(id))，id是聚集索引，存儲(chǔ)了id, user_id, user_name, phone整行的數(shù)據(jù)。

輔助索引

輔助索引也稱為二級(jí)索引，索引中除了存儲(chǔ)索引列外，還存儲(chǔ)了主鍵id，對(duì)于user_name的索引idx_user_name(user_name)而言，其實(shí)等價(jià)于idx_user_name(user_name, id)，MySQL會(huì)自動(dòng)在輔助索引的最后添加上主鍵id，熟悉Oracle數(shù)據(jù)庫(kù)的都知道，索引里除了索引列還存儲(chǔ)了row_id(代表數(shù)據(jù)的物理位置，由四部分組成：對(duì)象編號(hào)+數(shù)據(jù)文件號(hào)+數(shù)據(jù)塊號(hào)+數(shù)據(jù)行號(hào))，我們?cè)趧?chuàng)建輔助索引也可以顯示添加主鍵id。

-- 創(chuàng)建user_name列上的索引 
mysql> create index idx_user_name on users(user_name); 
-- 顯示添加主鍵id創(chuàng)建索引 
mysql> create index idx_user_name_id on users(user_name,id); 
-- 對(duì)比兩個(gè)索引的統(tǒng)計(jì)數(shù)據(jù) 
mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type,  b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type  from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b  on a.table_id =b.table_id where a.name = 'test/users'; 
+-------------+----------+------------+------------+------------+----------+------------------+----------+------ 
| tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name       | n_fields | page_no | index_type | 
+-------------+----------+------------+------------+------------+----------+------------------+----------+------ 
|         518 |     1586 | test/users | Dynamic    | Single     |     1254 | PRIMARY          |        9 |       4 |          3 | 
|         518 |     1586 | test/users | Dynamic    | Single     |     4003 | idx_user_name    |        2 |       5 |          0 | 
|         518 |     1586 | test/users | Dynamic    | Single     |     4004 | idx_user_name_id |        2 |      45 |          0 | 
mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in ('idx_user_name','idx_user_name_id'); 
+------------------+---------------------+--------------+------------+-----------------------------------+ 
| index_name       | last_update         | stat_name    | stat_value | stat_description                  | 
+------------------+---------------------+--------------+------------+-----------------------------------+    
| idx_user_name    | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index | 
| idx_user_name    | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      | 
| idx_user_name_id | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index | 
| idx_user_name_id | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      | 

對(duì)比一下兩個(gè)索引的結(jié)果，n_fields表示索引中的列數(shù)，n_leaf_pages表示索引中的葉子頁(yè)數(shù)，size表示索引中的總頁(yè)數(shù)，通過(guò)數(shù)據(jù)比對(duì)就可以看到，輔助索引中確實(shí)包含了主鍵id，也說(shuō)明了這兩個(gè)索引時(shí)完全一致。

索引回表

上面證明了輔助索引包含主鍵id，如果通過(guò)輔助索引列去過(guò)濾數(shù)據(jù)有可能需要回表，舉個(gè)例子：業(yè)務(wù)需要通過(guò)用戶名user_name去查詢用戶表users的信息，業(yè)務(wù)接口對(duì)應(yīng)的SQL：

select  user_id, user_name, phone from users where user_name = 'Laaa'; 

我們知道，對(duì)于索引idx_user_name而言，其實(shí)就是一個(gè)小表idx_user_name(user_name, id)，如果只查詢索引中的列，只需要掃描索引就能獲取到所需數(shù)據(jù)，是不需要回表的，如下SQL語(yǔ)句：

SQL 1: select id, user_name from users where user_name = 'Laaa';

SQL 2: select id from users where user_name = 'Laaa';

mysql> explain select id, name from users where name = 'Laaa'; 
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+------- 
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       | 
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+------- 
|  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index | 
mysql> explain select id from users where name = 'Laaa'; 
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+------- 
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       | 
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+------- 
|  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index | 

SQL 1和SQL 2的執(zhí)行計(jì)劃中的Extra=Using index 表示使用覆蓋索引掃描，不需要回表，再來(lái)看上面的業(yè)務(wù)SQL：

select user_id, user_name, phone from users where user_name = 'Laaa';

可以看到select后面的user_id，phone列不在索引idx_user_name中，就需要通過(guò)主鍵id進(jìn)行回表查找，MySQL內(nèi)部分如下兩個(gè)階段處理：

Section 1：select **id** from users where user_name = 'Laaa' //id = 100101

Section 2: select user_id, user_name, phone from users where id = 100101;

將Section 2的操作稱為回表，即通過(guò)輔助索引中的主鍵id去原表中查找數(shù)據(jù)。

索引高度

MySQL的索引時(shí)B+tree結(jié)構(gòu)，即使表里有上億條數(shù)據(jù)，索引的高度都不會(huì)很高，通常維持在3-4層左右，我來(lái)計(jì)算下索引idx_name的高度，從上面知道索引信息：index_id = 4003， page_no = 5，它的偏移量offset就是page_no x innodo_page_size + 64 = 81984，通過(guò)hexdump進(jìn)行查看

$hexdump -s 81984 -n 10 /usr/local/var/mysql/test/users.ibd 
0014040 00 02 00 00 00 00 00 00 0f a3                   
001404a 

其中索引的PAGE_LEVEL為00，即idx_user_name索引高度為1，0f a3 代表索引編號(hào)，轉(zhuǎn)換為十進(jìn)制是4003，正是index_id。

數(shù)據(jù)掃描方式

全表掃描

從左到右依次掃描整個(gè)B+Tree獲取數(shù)據(jù)，掃描整個(gè)表數(shù)據(jù)，IO開(kāi)銷大，速度慢，鎖等嚴(yán)重，影響MySQL的并發(fā)。

對(duì)于OLAP的業(yè)務(wù)場(chǎng)景，需要掃描返回大量數(shù)據(jù)，這時(shí)候全表掃描的順序IO效率更高。

索引掃描

通常來(lái)講索引比表小，掃描的數(shù)據(jù)量小，消耗的IO少，執(zhí)行速度塊，幾乎沒(méi)有鎖等，能夠提高M(jìn)ySQL的并發(fā)。

對(duì)于OLTP系統(tǒng)，希望所有的SQL都能命中合適的索引總是美好的。

主要區(qū)別就是掃描數(shù)據(jù)量大小以及IO的操作，全表掃描是順序IO，索引掃描是隨機(jī)IO，MySQL對(duì)此做了優(yōu)化，增加了change buffer特性來(lái)提高IO性能。

索引優(yōu)化案例

分頁(yè)查詢優(yōu)化

業(yè)務(wù)要根據(jù)時(shí)間范圍查詢交易記錄，接口原始的SQL如下：

select  * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20; 

表trade_info上有索引idx_status_create_time(status,create_time)，通過(guò)上面分析知道，等價(jià)于索引**(status,create_time,id)**，對(duì)于典型的分頁(yè)limit m, n來(lái)說(shuō)，越往后翻頁(yè)越慢，也就是m越大會(huì)越慢，因?yàn)橐ㄎ籱位置需要掃描的數(shù)據(jù)越來(lái)越多，導(dǎo)致IO開(kāi)銷比較大，這里可以利用輔助索引的覆蓋掃描來(lái)進(jìn)行優(yōu)化，先獲取id，這一步就是索引覆蓋掃描，不需要回表，然后通過(guò)id跟原表trade_info進(jìn)行關(guān)聯(lián)，改寫(xiě)后的SQL如下：

select * from trade_info a , 
 
(select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   //這一步走的是索引覆蓋掃描，不需要回表 
 where a.id = b.id; 

很多同學(xué)只知道這樣寫(xiě)效率高，但是未必知道為什么要這樣改寫(xiě)，理解索引特性對(duì)編寫(xiě)高質(zhì)量的SQL尤為重要。

分而治之總是不錯(cuò)的

營(yíng)銷系統(tǒng)有一批過(guò)期的優(yōu)惠卷要失效，核心SQL如下：

-- 需要更新的數(shù)據(jù)量500w 
update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 

在Oracle里更新500w數(shù)據(jù)是很快，因?yàn)榭梢岳枚鄠€(gè)cpu core去執(zhí)行，但是MySQL就需要注意了，一個(gè)SQL只能使用一個(gè)cpu core去處理，如果SQL很復(fù)雜或執(zhí)行很慢，就會(huì)阻塞后面的SQL請(qǐng)求，造成活動(dòng)連接數(shù)暴增，MySQL CPU 100%，相應(yīng)的接口Timeout，同時(shí)對(duì)于主從復(fù)制架構(gòu)，而且做了業(yè)務(wù)讀寫(xiě)分離，更新500w數(shù)據(jù)需要5分鐘，Master上執(zhí)行了5分鐘，binlog傳到了slave也需要執(zhí)行5分鐘，那就是Slave延遲5分鐘，在這期間會(huì)造成業(yè)務(wù)臟數(shù)據(jù)，比如重復(fù)下單等。

優(yōu)化思路：先獲取where條件中的最小id和最大id，然后分批次去更新，每個(gè)批次1000條，這樣既能快速完成更新，又能保證主從復(fù)制不會(huì)出現(xiàn)延遲。

優(yōu)化如下：

1.先獲取要更新的數(shù)據(jù)范圍內(nèi)的最小id和最大id(表沒(méi)有物理delete，所以id是連續(xù)的)

mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';  
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+--- 
| id | select_type | table | partitions | type  | possible_keys          | key                    | key_len | ref  | rows   | filtered | Extra                    | 
+----+-------------+-------+------------+-------+------------------------+------------------------+---------+--- 
|  1 | SIMPLE      | users | NULL       | range | idx_status_create_time | idx_status_create_time | 6       | NULL | 180300 |   100.00 | Using where; Using index | 

Extra=Using where; Using index使用了索引idx_status_create_time，同時(shí)需要的數(shù)據(jù)都在索引中能找到，所以不需要回表查詢數(shù)據(jù)。

2.以每次1000條commit一次進(jìn)行循環(huán)update，主要代碼如下：

current_id = min_id; 
for  current_id < max_id do 
  update coupons set status = 1 where id >=current_id and id <= current_id + 1000;  //通過(guò)主鍵id更新1000條很快 
commit; 
current_id += 1000; 
done 

這兩個(gè)案例告訴我們，要充分利用輔助索引包含主鍵id的特性，先通過(guò)索引獲取主鍵id走覆蓋索引掃描，不需要回表，然后再通過(guò)id去關(guān)聯(lián)操作是高效的，同時(shí)根據(jù)MySQL的特性使用分而治之的思想既能高效完成操作，又能避免主從復(fù)制延遲產(chǎn)生的業(yè)務(wù)數(shù)據(jù)混亂。

MySQL索引設(shè)計(jì)

熟悉了索引的特性之后，就可以在業(yè)務(wù)開(kāi)發(fā)過(guò)程中設(shè)計(jì)高質(zhì)量的索引，降低接口的響應(yīng)時(shí)間。

前綴索引

對(duì)于使用REDUNDANT或者COMPACT格式的InnoDB表，索引鍵前綴長(zhǎng)度限制為767字節(jié)。如果TEXT或VARCHAR列的列前綴索引超過(guò)191個(gè)字符，則可能會(huì)達(dá)到此限制，假定為utf8mb4字符集，每個(gè)字符最多4個(gè)字節(jié)。

可以通過(guò)設(shè)置參數(shù)innodb_large_prefix來(lái)開(kāi)啟或禁用索引前綴長(zhǎng)度的限制，即是設(shè)置為OFF，索引雖然可以創(chuàng)建成功，也會(huì)有一個(gè)警告，主要是因?yàn)閕ndex size會(huì)很大，效率大量的IO的操作，即使MySQL優(yōu)化器命中了該索引，效率也不會(huì)很高。

-- 設(shè)置innodb_large_prefix=OFF禁用索引前綴限制，雖然可以創(chuàng)建成功，但是有警告。 
mysql> create index idx_nickname on users(nickname);    // `nickname` varchar(255) 
Records: 0  Duplicates: 0  Warnings: 1 
mysql> show warnings; 
+---------+------+---------------------------------------------------------+ 
| Level   | Code | Message                                                 | 
+---------+------+---------------------------------------------------------+ 
| Warning | 1071 | Specified key was too long; max key length is 767 bytes | 

業(yè)務(wù)發(fā)展初期，為了快速實(shí)現(xiàn)功能，對(duì)一些數(shù)據(jù)表字段的長(zhǎng)度定義都比較寬松，比如用戶表users的昵稱nickname定義為varchar(128)，而且有業(yè)務(wù)接口需要通過(guò)nickname查詢，系統(tǒng)運(yùn)行了一段時(shí)間之后，查詢users表最大的nickname長(zhǎng)度為30，這個(gè)時(shí)候就可以創(chuàng)建前綴索引來(lái)減小索引的長(zhǎng)度提升性能。

-- `nickname` varchar(128) DEFAULT NULL定義的執(zhí)行計(jì)劃 
mysql> explain select * from users where nickname = 'Laaa'; 
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+-------- 
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra | 
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+-------- 
|  1 | SIMPLE      | users | NULL       | ref  | idx_nickname  | idx_nickname | 515     | const |    1 |   100.00 | NULL  | 

key_len=515，由于表和列都是utf8mb4字符集，每個(gè)字符占4個(gè)字節(jié)，變長(zhǎng)數(shù)據(jù)類型+2Bytes，允許NULL額外+1Bytes，即128 x 4 + 2 + 1 = 515Bytes。創(chuàng)建前綴索引，前綴長(zhǎng)度也可以不是當(dāng)前表的數(shù)據(jù)列最大值，應(yīng)該是區(qū)分度最高的那部分長(zhǎng)度，一般能達(dá)到90%以上即可，例如email字段存儲(chǔ)都是類似這樣的值xxxx@yyy.com，前綴索引的最大長(zhǎng)度可以是xxxx這部分的最大長(zhǎng)度即可。

-- 創(chuàng)建前綴索引，前綴長(zhǎng)度為30 
mysql> create index idx_nickname_part on users(nickname(30)); 
-- 查看執(zhí)行計(jì)劃 
mysql> explain select * from users where nickname = 'Laaa'; 
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+- 
| id | select_type | table | partitions | type | possible_keys                  | key               | key_len | ref   | rows | filtered | Extra       | 
+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+- 
|  1 | SIMPLE      | users | NULL       | ref  | idx_nickname_part,idx_nickname | idx_nickname_part | 123     | const |    1 |   100.00 | Using where | 

可以看到優(yōu)化器選擇了前綴索引，索引長(zhǎng)度為123，即30 x 4 + 2 + 1 = 123 Bytes，大小不到原來(lái)的四分之。

前綴索引雖然可以減小索引的大小，但是不能消除排序。

mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10; 
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+----- 
| id | select_type | table | partitions | type  | possible_keys                  | key          | key_len | ref  | rows | filtered | Extra                 | 
+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+----- 
|  1 | SIMPLE      | users | NULL       | range | idx_nickname_part,idx_nickname | idx_nickname | 515     | NULL |  899 |   100.00 | Using index condition | 
--可以看到Extra= Using index condition表示使用了索引，但是需要回表查詢數(shù)據(jù)，沒(méi)有發(fā)生排序操作。 
mysql> explain select gender,count(*) from users where nickname like  'User100%' group by nickname limit 10; 
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------ 
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                        | 
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------ 
|  1 | SIMPLE      | users | NULL       | range | idx_nickname_part | idx_nickname_part | 123     | NULL |  899 |   100.00 | Using where; Using temporary | 
--可以看到Extra= Using where; Using temporaryn表示在使用了索引的情況下，需要回表去查詢所需的數(shù)據(jù)，同時(shí)發(fā)生了排序操作。 

復(fù)合索引

在單列索引不能很好的過(guò)濾數(shù)據(jù)的時(shí)候，可以結(jié)合where條件中其他字段來(lái)創(chuàng)建復(fù)合索引，更好的去過(guò)濾數(shù)據(jù)，減少IO的掃描次數(shù)，舉個(gè)例子：業(yè)務(wù)需要按照時(shí)間段來(lái)查詢交易記錄，有如下的SQL：

select  * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 

開(kāi)發(fā)同學(xué)根據(jù)以往復(fù)合索引的設(shè)計(jì)的經(jīng)驗(yàn)：唯一值多選擇性好的列作為復(fù)合索引的前導(dǎo)列，所以創(chuàng)建復(fù)合索idx_create_time_status是高效的，因?yàn)閏reate_time是一秒一個(gè)值，唯一值很多，選擇性很好，而status只有離散的6個(gè)值，所以認(rèn)為這樣創(chuàng)建是沒(méi)問(wèn)題的，但是這個(gè)經(jīng)驗(yàn)只適合于等值條件過(guò)濾，不適合有范圍條件過(guò)濾的情況，例如idx_user_id_status(user_id，status)這個(gè)是沒(méi)問(wèn)題的，但是對(duì)于包含有create_time范圍的復(fù)合索引來(lái)說(shuō)，就不適應(yīng)了，我們來(lái)看下這兩種不同索引順序的差異，即idx_status_create_time和idx_create_time_status。

-- 分別創(chuàng)建兩種不同的復(fù)合索引 
mysql> create index idx_status_create_time on trade_info(status, create_time); 
mysql> create index idx_create_time_status on trade_info(create_time,status); 
-- 查看SQL的執(zhí)行計(jì)劃 
mysql> explain select * from users where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59'; 
+----+-------------+-------+------------+-------+-----------------------------------------------+--------------- 
| id | select_type | table | partitions | type  | possible_keys                                 | key                    | key_len | ref  | rows  | filtered | Extra                 | 
+----+-------------+-------+------------+-------+-----------------------------------------------+--------------- 
|  1 | SIMPLE      | trade_info | NULL       | range | idx_status_create_time,idx_create_time_status | idx_status_create_time | 6       | NULL | 98518 |   100.00 | Using index condition | 

從執(zhí)行計(jì)劃可以看到，兩種不同順序的復(fù)合索引都存在的情況，MySQL優(yōu)化器選擇的是idx_status_create_time索引，那為什么不選擇idx_create_time_status，我們通過(guò)optimizer_trace來(lái)跟蹤優(yōu)化器的選擇。

-- 開(kāi)啟optimizer_trace跟蹤 
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; 
-- 執(zhí)行SQL語(yǔ)句 
mysql> select * from trade_info where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59'; 
-- 查看跟蹤結(jié)果 
mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE\G; 

對(duì)比下兩個(gè)索引的統(tǒng)計(jì)數(shù)據(jù)，如下所示：

MySQL優(yōu)化器是基于Cost的，COST主要包括IO_COST和CPU_COST，MySQL的CBO(Cost-Based Optimizer基于成本的優(yōu)化器)總是選擇Cost最小的作為最終的執(zhí)行計(jì)劃去執(zhí)行，從上面的分析，CBO選擇的是復(fù)合索引idx_status_create_time，因?yàn)樵撍饕械膕tatus和create_time都能參與了數(shù)據(jù)過(guò)濾，成本較低;而idx_create_time_status只有create_time參數(shù)數(shù)據(jù)過(guò)濾，status被忽略了，其實(shí)CBO將其簡(jiǎn)化為單列索引idx_create_time，選擇性沒(méi)有復(fù)合索引idx_status_create_time好。

復(fù)合索引設(shè)計(jì)原則

1. 將范圍查詢的列放在復(fù)合索引的最后面，例如idx_status_create_time。
2. 列過(guò)濾的頻繁越高，選擇性越好，應(yīng)該作為復(fù)合索引的前導(dǎo)列，適用于等值查找，例如idx_user_id_status。

這兩個(gè)原則不是矛盾的，而是相輔相成的。

跳躍索引

一般情況下，如果表users有復(fù)合索引idx_status_create_time，我們都知道，單獨(dú)用create_time去查詢，MySQL優(yōu)化器是不走索引，所以還需要再創(chuàng)建一個(gè)單列索引idx_create_time。用過(guò)Oracle的同學(xué)都知道，是可以走索引跳躍掃描(Index Skip Scan)，在MySQL 8.0也實(shí)現(xiàn)Oracle類似的索引跳躍掃描，在優(yōu)化器選項(xiàng)也可以看到skip_scan=on。

| optimizer_switch             |use_invisible_indexes=off,skip_scan=on,hash_join=on | 

適合復(fù)合索引前導(dǎo)列唯一值少，后導(dǎo)列唯一值多的情況，如果前導(dǎo)列唯一值變多了，則MySQL CBO不會(huì)選擇索引跳躍掃描，取決于索引列的數(shù)據(jù)分表情況。

mysql> explain select id, user_id，status, phone from users where create_time >='2021-01-02 23:01:00' and create_time <= '2021-01-03 23:01:00'; 
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---- 
|  1 | SIMPLE      | users | NULL       | range  | idx_status_create_time          | idx_status_create_time | NULL    | NULL | 15636 |    11.11 | Using where; Using index for skip scan| 

也可以通過(guò)optimizer_switch='skip_scan=off'來(lái)關(guān)閉索引跳躍掃描特性。

總結(jié)

本位為大家介紹了MySQL中的索引，包括聚集索引和輔助索引，輔助索引包含了主鍵id用于回表操作，同時(shí)利用覆蓋索引掃描可以更好的優(yōu)化SQL。

同時(shí)也介紹了如何更好做MySQL索引設(shè)計(jì)，包括前綴索引，復(fù)合索引的順序問(wèn)題以及MySQL 8.0推出的索引跳躍掃描，我們都知道，索引可以加快數(shù)據(jù)的檢索，減少IO開(kāi)銷，會(huì)占用磁盤(pán)空間，是一種用空間換時(shí)間的優(yōu)化手段，同時(shí)更新操作會(huì)導(dǎo)致索引頻繁的合并分裂，影響索引性能，在實(shí)際的業(yè)務(wù)開(kāi)發(fā)中，如何根據(jù)業(yè)務(wù)場(chǎng)景去設(shè)計(jì)合適的索引是非常重要的，今天就聊這么多，希望對(duì)大家有所幫助。