[TOC] ## **MySQL中的索引** MySQL中的InnoDB引擎使用B+Tree結構來存儲索引，可以盡量減少數據查詢時磁盤IO次數，同時樹的高度直接影響了查詢的性能，一般樹的高度維持在 3~4 層。 B+Tree由三部分組成：根root、枝branch以及Leaf葉子，其中root和branch不存儲數據，只存儲指針地址，數據全部存儲在Leaf Node，同時Leaf Node之間用雙向鏈表鏈接，結構如下：  從上面可以看到，每個Leaf Node是三部分組成的，即前驅指針p\_prev，數據data以及后繼指針p\_next，同時數據data是有序的，默認是升序ASC，分布在B+tree右邊的鍵值總是大于左邊的，同時從root到每個Leaf的距離是相等的，也就是訪問任何一個Leaf Node需要的IO是一樣的，即索引樹的高度Level + 1次IO操作。 我們可以將MySQL中的索引可以看成一張小表，占用磁盤空間，創建索引的過程其實就是按照索引列排序的過程，先在sort\_buffer\_size進行排序，如果排序的數據量大，sort\_buffer\_size容量不下，就需要通過臨時文件來排序，最重要的是通過索引可以避免排序操作（distinct，group by，order by）。 ### **聚集索引** MySQL中的表是IOT（Index Organization Table，索引組織表)，數據按照主鍵id順序存儲（邏輯上是連續，物理上不連續），而且主鍵id是聚集索引（clustered index），存儲著整行數據，如果沒有顯示的指定主鍵，MySQL會將所有的列組合起來構造一個row\_id作為primary key，例如表users(id, user\_id, user\_name, phone, primary key(id))，id是聚集索引，存儲了id, user\_id, user\_name, phone整行的數據。 ### **輔助索引**  輔助索引也稱為二級索引，索引中除了存儲索引列外，還存儲了主鍵id，對于user\_name的索引idx\_user\_name(user\_name)而言，其實等價于idx\_user\_name(user\_name, id)，MySQL會自動在輔助索引的最后添加上主鍵id，熟悉Oracle數據庫的都知道，索引里除了索引列還存儲了row\_id（代表數據的物理位置，由四部分組成：對象編號+數據文件號+數據塊號+數據行號），我們在創建輔助索引也可以顯示添加主鍵id。 ~~~text -- 創建user_name列上的索引 mysql> create index idx_user_name on users(user_name); -- 顯示添加主鍵id創建索引 mysql> create index idx_user_name_id on users(user_name,id); -- 對比兩個索引的統計數據 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 | ~~~ 對比一下兩個索引的結果，n\_fields表示索引中的列數，n\_leaf\_pages表示索引中的葉子頁數，size表示索引中的總頁數，通過數據比對就可以看到，輔助索引中確實包含了主鍵id，也說明了這兩個索引時完全一致。 | Index\_name | n\_fields | n\_leaf\_pages | size | | --- | --- | --- | --- | ### **索引回表** 上面證明了輔助索引包含主鍵id，如果通過輔助索引列去過濾數據有可能需要回表，舉個例子：業務需要通過用戶名user\_name去查詢用戶表users的信息，業務接口對應的SQL： ~~~text select user_id, user_name, phone from users where user_name = 'Laaa'; ~~~ 我們知道，對于索引idx\_user\_name而言，其實就是一個小表idx\_user\_name(user\_name, id)，如果只查詢索引中的列，只需要掃描索引就能獲取到所需數據，是不需要回表的，如下SQL語句： SQL 1:`select id, user_name from users where user_name = 'Laaa';` **SQL 2:**`select id from users where user_name = 'Laaa';` ~~~text 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的執行計劃中的Extra=**Using index**表示使用覆蓋索引掃描，不需要回表，再來看上面的業務SQL： `select user_id, user_name, phone from users where user_name = 'Laaa';` 可以看到select后面的user\_id，phone列不在索引idx\_user\_name中，就需要通過主鍵id進行回表查找，MySQL內部分如下兩個階段處理： **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**的操作稱為回表，即通過輔助索引中的主鍵id去原表中查找數據。 ### **索引高度** MySQL的索引時B+tree結構，即使表里有上億條數據，索引的高度都不會很高，通常維持在3-4層左右，我來計算下索引idx\_name的高度，從上面知道索引信息：index\_id = 4003， page\_no = 5，它的偏移量offset就是page\_no x innodo\_page\_size + 64 = 81984，通過hexdump進行查看 ~~~text $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 代表索引編號，轉換為十進制是4003，正是index\_id。 ### **數據掃描方式** **全表掃描** 從左到右依次掃描整個B+Tree獲取數據，掃描整個表數據，IO開銷大，速度慢，鎖等嚴重，影響MySQL的并發。 對于OLAP的業務場景，需要掃描返回大量數據，這時候全表掃描的順序IO效率更高。 **索引掃描** 通常來講索引比表小，掃描的數據量小，消耗的IO少，執行速度塊，幾乎沒有鎖等，能夠提高MySQL的并發。 對于OLTP系統，希望所有的SQL都能命中合適的索引總是美好的。 主要區別就是掃描數據量大小以及IO的操作，全表掃描是順序IO，索引掃描是隨機IO，MySQL對此做了優化，增加了change buffer特性來提高IO性能。 ### **索引優化案例** **分頁查詢優化** 業務要根據時間范圍查詢交易記錄，接口原始的SQL如下： ~~~text 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)，通過上面分析知道，等價于索引\*\*（status,create\_time,id)\*\*，對于典型的分頁limit m, n來說，越往后翻頁越慢，也就是m越大會越慢，因為要定位m位置需要掃描的數據越來越多，導致IO開銷比較大，這里可以利用輔助索引的覆蓋掃描來進行優化，先獲取id，這一步就是索引覆蓋掃描，不需要回表，然后通過id跟原表trade\_info進行關聯，改寫后的SQL如下： ~~~text 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; ~~~ 很多同學只知道這樣寫效率高，但是未必知道為什么要這樣改寫，理解索引特性對編寫高質量的SQL尤為重要。 **分而治之總是不錯的** 營銷系統有一批過期的優惠卷要失效，核心SQL如下： ~~~text -- 需要更新的數據量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數據是很快，因為可以利用多個cpu core去執行，但是MySQL就需要注意了，一個SQL只能使用一個cpu core去處理，如果SQL很復雜或執行很慢，就會阻塞后面的SQL請求，造成活動連接數暴增，MySQL CPU 100%，相應的接口Timeout，同時對于主從復制架構，而且做了業務讀寫分離，更新500w數據需要5分鐘，Master上執行了5分鐘，binlog傳到了slave也需要執行5分鐘，那就是Slave延遲5分鐘，在這期間會造成業務臟數據，比如重復下單等。 **優化思路：先獲取where條件中的最小id和最大id，然后分批次去更新，每個批次1000條，這樣既能快速完成更新，又能保證主從復制不會出現延遲。** **優化如下：** 1. 先獲取要更新的數據范圍內的最小id和最大id（表沒有物理delete，所以id是連續的） ~~~text 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，同時需要的數據都在索引中能找到，所以不需要回表查詢數據。 1. 以每次1000條commit一次進行循環update，主要代碼如下： ~~~text current_id = min_id; for current_id < max_id do update coupons set status = 1 where id >=current_id and id <= current_id + 1000; //通過主鍵id更新1000條很快 commit; current_id += 1000; done ~~~ 這兩個案例告訴我們，要充分利用輔助索引包含主鍵id的特性，先通過索引獲取主鍵id走覆蓋索引掃描，不需要回表，然后再通過id去關聯操作是高效的，同時根據MySQL的特性使用分而治之的思想既能高效完成操作，又能避免主從復制延遲產生的業務數據混亂。 ## **MySQL索引設計** 熟悉了索引的特性之后，就可以在業務開發過程中設計高質量的索引，降低接口的響應時間。 ### **前綴索引** 對于使用REDUNDANT或者COMPACT格式的InnoDB表，索引鍵前綴長度限制為767字節。如果TEXT或VARCHAR列的列前綴索引超過191個字符，則可能會達到此限制，假定為utf8mb4字符集，每個字符最多4個字節。 可以通過設置參數innodb\_large\_prefix來開啟或禁用索引前綴長度的限制，即是設置為OFF，索引雖然可以創建成功，也會有一個警告，主要是因為index size會很大，效率大量的IO的操作，即使MySQL優化器命中了該索引，效率也不會很高。 ~~~text -- 設置innodb_large_prefix=OFF禁用索引前綴限制，雖然可以創建成功，但是有警告。 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 | ~~~ 業務發展初期，為了快速實現功能，對一些數據表字段的長度定義都比較寬松，比如用戶表users的昵稱nickname定義為varchar(128)，而且有業務接口需要通過nickname查詢，系統運行了一段時間之后，查詢users表最大的nickname長度為30，這個時候就可以創建前綴索引來減小索引的長度提升性能。 ~~~text -- `nickname` varchar(128) DEFAULT NULL定義的執行計劃 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字符集，每個字符占4個字節，變長數據類型+2Bytes，允許NULL額外+1Bytes，即128 x 4 + 2 + 1 = 515Bytes。創建前綴索引，前綴長度也可以不是當前表的數據列最大值，應該是區分度最高的那部分長度，一般能達到90%以上即可，例如email字段存儲都是類似這樣的值xxxx@yyy.com，前綴索引的最大長度可以是xxxx這部分的最大長度即可。 ~~~text -- 創建前綴索引，前綴長度為30 mysql> create index idx_nickname_part on users(nickname(30)); -- 查看執行計劃 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 | ~~~ 可以看到優化器選擇了前綴索引，索引長度為123，即30 x 4 + 2 + 1 = 123 Bytes，大小不到原來的四分之。 **前綴索引雖然可以減小索引的大小，但是不能消除排序。** ~~~text 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表示使用了索引，但是需要回表查詢數據，沒有發生排序操作。 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表示在使用了索引的情況下，需要回表去查詢所需的數據，同時發生了排序操作。 ~~~ ### **復合索引** 在單列索引不能很好的過濾數據的時候，可以結合where條件中其他字段來創建復合索引，更好的去過濾數據，減少IO的掃描次數，舉個例子：業務需要按照時間段來查詢交易記錄，有如下的SQL： ~~~text 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'; ~~~ 開發同學根據以往復合索引的設計的經驗：**唯一值多選擇性好的列作為復合索引的前導列**，所以創建復合索idx\_create\_time\_status是高效的，因為create\_time是一秒一個值，唯一值很多，選擇性很好，而status只有離散的6個值，所以認為這樣創建是沒問題的，**但是這個經驗只適合于等值條件過濾，不適合有范圍條件過濾的情況**，例如idx\_user\_id\_status(user\_id，status)這個是沒問題的，但是對于包含有create\_time范圍的復合索引來說，就不適應了，我們來看下這兩種不同索引順序的差異，即idx\_status\_create\_time和idx\_create\_time\_status。 ~~~text -- 分別創建兩種不同的復合索引 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的執行計劃 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 | ~~~ 從執行計劃可以看到，兩種不同順序的復合索引都存在的情況，MySQL優化器選擇的是idx\_status\_create\_time索引，那為什么不選擇idx\_create\_time\_status，我們通過optimizer\_trace來跟蹤優化器的選擇。 ~~~text -- 開啟optimizer_trace跟蹤 mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 執行SQL語句 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'; -- 查看跟蹤結果 mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE\G; ~~~  對比下兩個索引的統計數據，如下所示： | 復合索引 | Type | Rows | 參與過濾索引列 | Chosen | Cause | | --- | --- | --- | --- | --- | --- | MySQL優化器是基于Cost的，COST主要包括IO\_COST和CPU\_COST，MySQL的CBO（Cost-Based Optimizer基于成本的優化器）總是選擇Cost最小的作為最終的執行計劃去執行，從上面的分析，CBO選擇的是復合索引idx\_status\_create\_time，因為該索引中的status和create\_time都能參與了數據過濾，成本較低；而idx\_create\_time\_status只有create\_time參數數據過濾，status被忽略了，其實CBO將其簡化為單列索引idx\_create\_time，選擇性沒有復合索引idx\_status\_create\_time好。 **復合索引設計原則** 1. 將范圍查詢的列放在復合索引的最后面，例如idx\_status\_create\_time。 2. 列過濾的頻繁越高，選擇性越好，應該作為復合索引的前導列，適用于等值查找，例如idx\_user\_id\_status。 這兩個原則不是矛盾的，而是相輔相成的。 ### **跳躍索引** 一般情況下，如果表users有復合索引idx\_status\_create\_time，我們都知道，單獨用create\_time去查詢，MySQL優化器是不走索引，所以還需要再創建一個單列索引idx\_create\_time。用過Oracle的同學都知道，是可以走索引跳躍掃描（Index Skip Scan），在MySQL 8.0也實現Oracle類似的索引跳躍掃描，在優化器選項也可以看到skip\_scan=on。 ~~~text | optimizer_switch |use_invisible_indexes=off,skip_scan=on,hash_join=on | ~~~ **適合復合索引前導列唯一值少，后導列唯一值多的情況，如果前導列唯一值變多了，則MySQL CBO不會選擇索引跳躍掃描，取決于索引列的數據分表情況。** ~~~text 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| ~~~ 也可以通過optimizer\_switch='skip\_scan=off'來關閉索引跳躍掃描特性。 ## **總結** 本位為大家介紹了MySQL中的索引，包括聚集索引和輔助索引，輔助索引包含了主鍵id用于回表操作，同時利用覆蓋索引掃描可以更好的優化SQL。 同時也介紹了如何更好做MySQL索引設計，包括前綴索引，復合索引的順序問題以及MySQL 8.0推出的索引跳躍掃描，我們都知道，索引可以加快數據的檢索，減少IO開銷，會占用磁盤空間，是一種用空間換時間的優化手段，同時更新操作會導致索引頻繁的合并分裂，影響索引性能，在實際的業務開發中，如何根據業務場景去設計合適的索引是非常重要的，今天就聊這么多，希望對大家有所幫助。 轉載至：[https://zhuanlan.zhihu.com/p/343312997](https://zhuanlan.zhihu.com/p/343312997)