淺談慢SQL優(yōu)化之索引的作用

更新時(shí)間：2023年05月25日 09:04:46 作者：hunter09

本文針對?MySQL?數(shù)據(jù)庫的?InnoDB?存儲引擎，介紹其中索引的實(shí)現(xiàn)以及索引在慢?SQL?優(yōu)化中的作用，本文主要討論不同場景下索引生效與失效的原因，感興趣的小伙伴可以跟著小編一起來探討

慢SQL與索引的關(guān)系

慢SQL優(yōu)化原則

數(shù)據(jù)庫也是應(yīng)用，MySQL 作為一種磁盤數(shù)據(jù)庫，屬于典型的 IO 密集型應(yīng)用，并且隨機(jī) IO 比順序 IO 更昂貴。

真實(shí)的慢 SQL 往往會伴隨著大量的行掃描、臨時(shí)文件排序，直接影響就是磁盤 IO 升高、CPU 使用率升高，正常 SQL 也變?yōu)榱寺?SQL，對于應(yīng)用來說就是大面積執(zhí)行超時(shí)。

線上很多事故都與慢 SQL 有關(guān)，因此慢 SQL 治理已成為 DBA 與業(yè)務(wù)研發(fā)的共識。

慢SQL的優(yōu)化原則為：減少數(shù)據(jù)訪問量與減少計(jì)算操作

減少訪問量：

創(chuàng)建合適的索引
減少不必要訪問的列
使用覆蓋索引
語句改寫
數(shù)據(jù)結(jié)轉(zhuǎn)

減少計(jì)算操作：

排序列加入索引
適當(dāng)?shù)牧腥哂?/li>
SQL 拆分
計(jì)算功能拆分

可以將慢 SQL 優(yōu)化的方法分為三類：

查詢優(yōu)化
索引優(yōu)化
庫表結(jié)構(gòu)優(yōu)化

其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具。

可是，為什么索引可以加快查詢，索引一定可以加快查詢嗎？

索引的作用

要回答這個(gè)問題，可以對比沒有索引與有索引時(shí)查詢操作的性能差異。

在此之前，首先介紹下查詢操作的處理流程。

查詢操作可以分為以下兩步：

定位到記錄所在的頁
從所在的頁中定位到具體的記錄

其中從頁中定位記錄的方法依賴每個(gè)頁面中創(chuàng)建的Page Directory（頁目錄），因此關(guān)鍵在于如何定位頁。

數(shù)據(jù)保存在磁盤上，數(shù)據(jù)處理發(fā)生在內(nèi)存中，數(shù)據(jù)頁是磁盤與內(nèi)存之間交互的基本單位，也是 MySQL 管理存儲空間的基本單位，大小默認(rèn)為 16KB。

因此通常一次最少從磁盤中讀取 16KB 的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的 16KB 內(nèi)容刷新到磁盤中。

要理解索引的作用，需要首先明確沒有索引時(shí)如何定位頁。

沒有索引時(shí)，由于每個(gè)頁中的數(shù)據(jù)沒有規(guī)律，因此無法快速定位記錄所在的頁，只能從第一個(gè)頁沿雙向鏈表向后遍歷，也就是說需要遍歷所有數(shù)據(jù)頁依次判斷是否滿足查詢條件。

簡單來說，沒有索引時(shí)每次查詢都是全表掃描。

因此索引需要解決的主要問題就是實(shí)現(xiàn)每個(gè)數(shù)據(jù)頁中數(shù)據(jù)有規(guī)律，具體是保證下一個(gè)數(shù)據(jù)頁中用戶記錄的索引列值必須大于上一個(gè)頁中用戶記錄的索引列值。

索引是存儲引擎用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu)。

有索引時(shí)，優(yōu)化器首先基于成本自動選擇最優(yōu)的執(zhí)行計(jì)劃，然后基于索引的有序性可以通過掃描更少的數(shù)據(jù)頁定位到滿足條件的數(shù)據(jù)。

具體原因與索引的數(shù)據(jù)結(jié)構(gòu)有關(guān)，下面基于索引的數(shù)據(jù)結(jié)構(gòu)介紹常見的索引生效與索引失效的場景。

索引

索引的數(shù)據(jù)結(jié)構(gòu)

索引是一種以空間換時(shí)間思想的具體實(shí)現(xiàn)，用于加速查詢。

MySQL 中由存儲引擎層實(shí)現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實(shí)現(xiàn)，因此每個(gè)索引都是一棵 B+ 樹。

索引用于組織頁，頁用于組織行記錄。在介紹索引的結(jié)構(gòu)之前首先介紹頁的結(jié)構(gòu)，如下圖所示。

其中：

每個(gè)數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個(gè)單向鏈表，依賴行記錄的Page Header中next_record屬性實(shí)現(xiàn)，其中保存下一條記錄相對于本條記錄的地址偏移量；
數(shù)據(jù)頁之間組成一個(gè)雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實(shí)現(xiàn)，其中保存本頁的上一個(gè)和下一個(gè)頁的頁號。

多個(gè)頁通過樹進(jìn)行組織，其中保存用戶數(shù)據(jù)與目錄項(xiàng)。目錄項(xiàng)中保存頁的用戶記錄中主鍵的最小值與頁號，從而保證下一個(gè)數(shù)據(jù)頁中用戶記錄的主鍵值大于上一個(gè)頁中用戶記錄的主鍵值。

其中：

用戶數(shù)據(jù)保存在葉子節(jié)點(diǎn)，目錄項(xiàng)保存在非葉子節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)中可能保存多個(gè)頁；
最上面的節(jié)點(diǎn)稱為根節(jié)點(diǎn)，根節(jié)點(diǎn)的地址保存在內(nèi)存的數(shù)據(jù)字典中；
B+ 樹的深度一般控制在 3 層以內(nèi)，因此定位到單條記錄不超過 3 次 IO。

因此，頁面和記錄是排好序的，就可以通過二分法來快速定位查找。

有索引時(shí)，查詢操作變成了什么樣呢？

從 B+ 樹的根節(jié)點(diǎn)出發(fā)，一層一層向下搜索目錄項(xiàng)，由于上層節(jié)點(diǎn)保存的都是下層節(jié)點(diǎn)的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁；
如果數(shù)據(jù)頁在緩存池中，直接從內(nèi)存中獲取，否則從磁盤加載到內(nèi)存中；
數(shù)據(jù)頁內(nèi)部二分查找定位滿足條件的記錄行。

索引保存的數(shù)據(jù)

索引中保存的數(shù)據(jù)與索引的類型有關(guān)。

索引可以分為兩種類型：

聚簇索引，主鍵索引。葉子節(jié)點(diǎn)中保存主鍵值+對應(yīng)的完整行記錄，目錄項(xiàng)中保存主鍵最小值+頁號。InnoDB 屬于索引組織表，每張表都有聚簇索引，因此表必須有主鍵，表中行的物理順序與索引的邏輯順序相同；
非聚簇索引，二級索引，在非主鍵的其他列上建的索引。葉子節(jié)點(diǎn)中保存索引列的值+對應(yīng)的主鍵值，目錄項(xiàng)中保存索引列最小值+對應(yīng)的主鍵值+頁號。

引性能相關(guān)的概念：

概念	explain.extra
覆蓋索引	Using index
回表	Using where
索引下推	Using index condition

覆蓋索引，當(dāng)二級索引中包含要查詢的所有字段時(shí)，這個(gè)索引稱為覆蓋索引；
回表，當(dāng)二級索引中不包含要查詢的所有字段時(shí)，就需要先通過二級索引查出主鍵索引，再通過主鍵索引查詢二級索引中沒有的其他列的數(shù)據(jù)，這個(gè)過程叫做回表；
索引下推，用于優(yōu)化使用二級索引從表中檢索行的實(shí)現(xiàn)。條件過濾可以下推到存儲引擎層進(jìn)行，先由索引元組（index tuple）根據(jù)查詢條件進(jìn)行過濾，滿足條件的前提下才回表，否則跳過，相當(dāng)于延遲加載數(shù)據(jù)行，因此 ICP 可以降低回表次數(shù)與 IO 次數(shù)。

正常情況下看不到二級索引中隱藏的主鍵，但實(shí)際上，如下所示查看鎖信息，顯示LOCK_DATA: '17118168721', 2，其中 2 就是二級索引中保存的主鍵值。

 ? ? ? ? ? ? ? ENGINE:INNODB
 ? ? ? ENGINE_LOCK_ID:140123070938328:14:7:4:140122972537552
ENGINE_TRANSACTION_ID:2032566
 ? ? ? ? ?  THREAD_ID:157
 ? ? ? ? ? ? EVENT_ID:44
 ? ? ?  OBJECT_SCHEMA: test_zk
 ? ? ? ?  OBJECT_NAME: t_lock_test
 ? ? ? PARTITION_NAME:NULL
 ?  SUBPARTITION_NAME:NULL
 ? ? ? ? ? INDEX_NAME: idx_uk_mobile
OBJECT_INSTANCE_BEGIN:140122972537552
 ? ? ? ? ?  LOCK_TYPE: RECORD
 ? ? ? ? ?  LOCK_MODE: X,REC_NOT_GAP
 ? ? ? ?  LOCK_STATUS: GRANTED
 ? ? ? ? ?  LOCK_DATA:'17118168721',2

如下所示，工作中多次遇到研發(fā)創(chuàng)建二級索引時(shí)顯式指定主鍵，實(shí)際上是不需要的，二級索引末尾自動保存主鍵。

alter table payable_unsettled 
add index idx_seller_no_recno_id(seller_no, receipt_no,id) using BTREE;

因此二級索引+主鍵與聯(lián)合索引的相同點(diǎn)是依次排序，不同點(diǎn)是索引中保存的數(shù)據(jù)不同。

索引生效的場景

等值查詢

線上環(huán)境多次遇到表沒有創(chuàng)建二級索引，只有主鍵索引。

SQL

select sys_no,area_no,area_name,dc_no,dc_name,wh_no,wh_name, business_type,business_no,item_code,item_grade, item_result,item_remark,status, yn,ts,create_time,create_pin,update_time,update_pin
from
  evaluate_result
where
  yn =1
 ?and wh_no ='611-887-2'
 ?and business_no ='QNSYKF23020900000018'
 ?and create_pin ='13940137489'
orderby
  update_time desc;
?
# 執(zhí)行用時(shí)
5 rows in set(7.311125 sec)

執(zhí)行計(jì)劃，顯示全表掃描

表結(jié)構(gòu)，顯示查詢字段無索引

mysql>show create table evaluate_result \G
***************************1.row***************************
 ? ? ? Table: evaluate_result
CreateTable:CREATE TABLE `evaluate_result`(
 ?`sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '內(nèi)部主鍵',
 ?`wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT'倉庫編碼',
 ?`business_no` varchar(20) NOT NULL DEFAULT '' COMMENT'調(diào)研業(yè)務(wù)主鍵',
 ? ...
 ? PRIMARY KEY(`sys_no`)
)ENGINE=InnoDB AUTO_INCREMENT=2007412 DEFAULT CHARSET=utf8 COMMENT='評價(jià)結(jié)果表'
1rowinset(0.00 sec)

優(yōu)化方法：創(chuàng)建索引

alter table evaluate_result add index idx_wh_bus_no(wh_no,business_no);

執(zhí)行計(jì)劃

***************************1.row***************************
 ? ? ? ? ? id:1
  select_type: SIMPLE
 ? ? ? ?table: evaluate_result
 ? partitions:NULL
 ? ? ? ? type: ref
possible_keys: idx_wh_bus_no
 ? ? ? ? ?key: idx_wh_bus_no
 ? ?  key_len:160
 ? ? ? ?  ref: const,const
 ? ? ? ? rows:5
 ? ? filtered:1.00
 ? ? ?  Extra:Using index condition;Using where;Using filesort
1rowinset,1 warning (0.00 sec)
?
# 執(zhí)行用時(shí)
5 rows in set(0.01 sec)

其中：

key_len: 160，表明聯(lián)合索引的兩個(gè)字段都用到了，(32+20) * 3+2 * 2 = 160。

等值查詢索引生效的原因是相同值的數(shù)據(jù)組成單向鏈表，因此定位到滿足條件的 5 行數(shù)據(jù)需要掃描的行數(shù)從 1377442 行降低到 5 行。

范圍查詢

SQL

select
  id
from
  board_chute
where
 ?status=1
 ?and create_time <= date_sub(now(),interval 24 hour);

執(zhí)行計(jì)劃，顯示全表掃描

***************************1.row***************************
 ? ? ? ? ? id:1
  select_type: SIMPLE
 ? ? ? ?table: board_chute
 ? partitions:NULL
 ? ? ? ? type:ALL
possible_keys: idx_create_time
 ? ? ? ? ?key:NULL
 ? ?  key_len:NULL
 ? ? ? ?  ref:NULL
 ? ? ? ? rows:407632
 ? ? filtered:5.00
 ? ? ?  Extra:Using where
1rowinset,1 warning (0.00 sec)

查詢字段有索引，但是索引失效

 ?KEY`idx_create_time`(`create_time`),

status 字段的區(qū)分度

mysql> select status,count(*) from board_chute group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|      0 |   407317 |
|      1 |     4309 |
+--------+----------+
2 rows in set (0.17 sec)

因此范圍查詢索引失效的原因是查看數(shù)據(jù)量大并且需要回表。

優(yōu)化方法：創(chuàng)建聯(lián)合索引實(shí)現(xiàn)覆蓋索引

alter table board_chute add index idx_status_create_time(status, create_time);

執(zhí)行計(jì)劃，顯示 Using index 表明用到了覆蓋索引，不需要回表。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: board_chute
   partitions: NULL
         type: range
possible_keys: idx_create_time,idx_status_create_time
          key: idx_status_create_time
      key_len: 8
          ref: NULL
         rows: 203816
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

范圍查詢索引生效的原因是葉子節(jié)點(diǎn)中除了保存索引，還保存指向下個(gè)節(jié)點(diǎn)的指針，因此遍歷葉子節(jié)點(diǎn)就可以獲得范圍值。

因此建議使用 between and 代替 in，如select * from T where k in (1,2,3,4,5);對應(yīng) 5 次樹的搜索，而select * from T where k between 1 and 5;對應(yīng) 1 次樹的搜索。

假設(shè)索引基于哈希表實(shí)現(xiàn)，可以通過散列函數(shù)將 key 值轉(zhuǎn)換成一個(gè)固定的地址，如果發(fā)生哈希碰撞就在這個(gè)位置拉出一個(gè)鏈表。因此哈希表的優(yōu)點(diǎn)是插入操作的速度快，根據(jù) key 直接往后追加即可。但由于散列函數(shù)的離散特性，經(jīng)過散列函數(shù)處理后的 key 將失去原有的順序，所以哈希表無法滿足范圍查詢，只適合等值查詢。

注意上述索引生效的場景并非絕對成立，需要回表的記錄越多，優(yōu)化器越傾向于使用全表掃描，反之傾向于使用二級索引 + 回表的方式。

回表查詢成本高有兩點(diǎn)原因：

需要使用到兩個(gè) B+ 樹索引，一個(gè)二級索引，一個(gè)聚簇索引；
訪問二級索引使用順序 I/O，訪問聚簇索引使用隨機(jī) I/O。

因此有兩條建議：

建議為區(qū)分度高的字段創(chuàng)建索引，并且將區(qū)分度高的字段優(yōu)先放在聯(lián)合索引前面；
建議優(yōu)先使用覆蓋索引，必須要回表時(shí)也需要控制回表的記錄數(shù)，從而降低索引失效的風(fēng)險(xiǎn)。

索引失效的場景

違反最左匹配原則

SQL

select
 ?count(*)
from
  sort_cross_detail
where
  yn =1
 ?and org_id =3
 ?and site_type =16
 ?and site_code ='121671';

執(zhí)行計(jì)劃，顯示全表掃描

盡管當(dāng)前有聯(lián)合索引 idx_site_type(SUB_TYPE, THIRD_TYPE, SITE_TYPE )，但由于查詢條件中不包括 SUB_TYPE 字段，因此違反最左匹配原則，導(dǎo)致索引失效。

當(dāng)前查詢條件的多個(gè)字段區(qū)分度由高到低為 site_code、org_id、site_type。

優(yōu)化方法：site_code 字段區(qū)分度很高，創(chuàng)建單列索引。

alter table sort_cross_detail add index `idx_site_code` (`SITE_CODE`);

執(zhí)行計(jì)劃

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sort_cross_detail
   partitions: NULL
         type: ref
possible_keys: idx_site_code
          key: idx_site_code
      key_len: 99
          ref: const
         rows: 1336
     filtered: 0.10
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

其中：

使用聯(lián)合索引過程中可以通過執(zhí)行計(jì)劃中的 key_len 字段評估具體 SQL 使用到了聯(lián)合索引中的幾個(gè)字段；
聯(lián)合索引中頁面和記錄首先按照聯(lián)合索引前面的列排序，如果該列值相同，再按照聯(lián)合索引后邊的列排序。

違反最左匹配原則導(dǎo)致索引失效的原因是只有當(dāng)索引前面的列相同時(shí)，后面的列才有序。

下面結(jié)合 innodb_ruby 工具解析 InnoDB 數(shù)據(jù)文件查看記錄保存的順序驗(yàn)證聯(lián)合索引中索引前面的列不同時(shí)，后面的列可能無序。

準(zhǔn)備測試數(shù)據(jù)。

mysql> show create table t_index \G
*************************** 1. row ***************************
       Table: t_index
Create Table: CREATE TABLE `t_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT '0',
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into t_index(age, name) values(8, "Tom"),(8, "David"), (10, "Andy");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t_index;
+----+------+-------+
| id | age  | name  |
+----+------+-------+
|  2 |    8 | David |
|  1 |    8 | Tom   |
|  3 |   10 | Andy  |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> explain select * from t_index \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_index
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_name
      key_len: 38
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

其中：

insert 時(shí)，三條記錄按照 name 字段逆序；
select 時(shí)，三條記錄按照聯(lián)合索引排序，并不是按照主鍵排序。

分別查看索引以及索引中保存的數(shù)據(jù)

[root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index space-indexes
id ? ? ? ?  name ? ? ? ? ? ? ? ? ? ? ? ? ?  root ? ? ?  fseg ? ? ?  fseg_id ? ? used ? ? ?  allocated ? fill_factor 
218 ? ? ? ? PRIMARY ? ? ? ? ? ? ? ? ? ? ? ? 3 ? ? ? ? ? internal ? ?1 ? ? ? ? ? 1 ? ? ? ? ? 1 ? ? ? ? ? 100.00% ? ? 
218 ? ? ? ? PRIMARY ? ? ? ? ? ? ? ? ? ? ? ? 3 ? ? ? ? ? leaf ? ? ? ?2 ? ? ? ? ? 0 ? ? ? ? ? 0 ? ? ? ? ? 0.00% ? ? ? 
219 ? ? ? ? idx_age_name ? ? ? ? ? ? ? ? ? ?4 ? ? ? ? ? internal ? ?3 ? ? ? ? ? 1 ? ? ? ? ? 1 ? ? ? ? ? 100.00% ? ? 
219 ? ? ? ? idx_age_name ? ? ? ? ? ? ? ? ? ?4 ? ? ? ? ? leaf ? ? ? ?4 ? ? ? ? ? 0 ? ? ? ? ? 0 ? ? ? ? ? 0.00% ? ? ? 
[root@exps-test3 data]# 
[root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 3 page-records
Record 127: (id=1) → (age=8, name="Tom")
Record 158: (id=2) → (age=8, name="David")
Record 191: (id=3) → (age=10, name="Andy")
[root@exps-test3 data]# 
[root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 4 page-records
Record 145: (age=8, name="David") → (id=2)
Record 127: (age=8, name="Tom") → (id=1)
Record 165: (age=10, name="Andy") → (id=3)

其中：

主鍵與二級索引的根節(jié)點(diǎn)頁號分別是 3 與 4；
查看聚簇索引中保存的記錄，按照主鍵排序；
查看二級索引中保存的記錄，聯(lián)合索引中當(dāng) age 相同時(shí)，name 有序，age 不同時(shí)，name 無序。

上面提到，數(shù)據(jù)字典中保存表的根節(jié)點(diǎn)的地址，具體是 INFORMATION_SCHEMA.INNODB_SYS_INDEXES 系統(tǒng)表的 space 與 page_no 字段，分別保存表空間 ID 與根節(jié)點(diǎn)頁號。

mysql> SELECT
  tables.name, indexs.space, indexs.name, indexs.page_no
FROM
  INFORMATION_SCHEMA.INNODB_SYS_TABLES as tables
  inner join INFORMATION_SCHEMA.INNODB_SYS_INDEXES as indexs on tables.table_id = indexs.table_id
WHERE
  tables.NAME = 'test_zk/t_index';
+-----------------+-------+--------------+---------+
| name            | space | name         | page_no |
+-----------------+-------+--------------+---------+
| test_zk/t_index |   106 | PRIMARY      |       3 |
| test_zk/t_index |   106 | idx_age_name |       4 |
+-----------------+-------+--------------+---------+
2 rows in set (0.00 sec)

其中 test_zk/t_index 表有兩個(gè)索引，對應(yīng)的根節(jié)點(diǎn)頁號分別等于 3 與 4，與上面數(shù)據(jù)文件解析的結(jié)果一致。

order by limit

SQL，不建議使用 select *

select 
  * 
from 
  waybill_order_added_value_report_detail goodsInfo 
WHERE 
  goodsInfo.is_delete = 0 
  AND goodsInfo.org_no = '418' 
  AND goodsInfo.distribute_no = '636' 
  AND (
    goodsInfo.company_code = 'EBU4418046542406' 
    OR goodsInfo.company_name = 'EBU4418046542406'
  ) 
  AND goodsInfo.network_type = 1 
  AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00') 
  AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59') 
  AND goodsInfo.uniform_status = 0 
ORDER BY 
  goodsInfo.id DESC 
LIMIT 
  0, 20 \G
# 執(zhí)行用時(shí)
2 rows in set (1 min 9.71 sec)

執(zhí)行計(jì)劃，主鍵全索引掃描，聯(lián)合索引失效

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goodsInfo
         type: index
possible_keys: idx_org_dc_operator_time,idx_operator_time_network
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 16156
        Extra: Using where
1 row in set (0.00 sec)

表結(jié)構(gòu)與查詢條件

# 查詢條件
WHERE 
  goodsInfo.org_no = '418' 
  AND goodsInfo.distribute_no = '636' 
  AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00') 
  AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59') 
# 索引
  KEY `idx_org_dc_operator_time` (`org_no`,`distribute_no`,`should_operator_time`),
  KEY `idx_operator_time_network` (`should_operator_time`,`network_type`)

將 limit 20 修改為 limit 30，SQL 如下所示。

select 
  * 
from 
  waybill_order_added_value_report_detail goodsInfo 
WHERE 
    ...
ORDER BY 
  goodsInfo.id DESC 
LIMIT 
  0, 30 \G
# 執(zhí)行用時(shí)
2 rows in set (0.06 sec)

執(zhí)行計(jì)劃顯示當(dāng)改為 limit 30 時(shí)，聯(lián)合索引生效。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goodsInfo
         type: range
possible_keys: idx_org_dc_operator_time,idx_operator_time_network
          key: idx_org_dc_operator_time
      key_len: 132
          ref: NULL
         rows: 19024
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

可見，索引的選擇與 limit n 的 n 值也有關(guān)系。

從現(xiàn)象上看，當(dāng) limit n 的 n 值變大時(shí)，SQL的執(zhí)行反倒有可能變快了。

實(shí)際上，這是 MySQL 低版本中的 bug #97001，優(yōu)化器認(rèn)為排序是個(gè)昂貴的操作，因此在執(zhí)行 order by id limit 這條 SQL 時(shí)，為了避免排序，并且認(rèn)為當(dāng) limit n 的 n 很小時(shí)，全表掃描可以很快執(zhí)行完，因此選擇使用全表掃描，以避免額外的排序。

針對 MySQL 中 order by limit 或 group by limit 優(yōu)化器選擇錯(cuò)誤索引的場景，常見的優(yōu)化方法有四種：

強(qiáng)制索引，通過 hint 固化執(zhí)行計(jì)劃，比如可以通過 force index 指定使用的索引，但是當(dāng)條件發(fā)生變化時(shí)有可能失效，因此生產(chǎn)環(huán)境中不建議使用；
prefer_ordering_index，5.7.33 中已修復(fù)該 bug，因此建議新申請時(shí)使用 5.7.33 及以上版本，存量低版本建議升級，建議優(yōu)先使用該方法；
聯(lián)合索引，建議在合適的字段加聯(lián)合索引，增強(qiáng)可選索引的區(qū)分度，讓優(yōu)化器認(rèn)為這種方式優(yōu)于有序索引；
order by (id+0)，通過 trick 的方式欺騙優(yōu)化器，由于 id 上進(jìn)行了加法這種耗時(shí)操作，使優(yōu)化器認(rèn)為此時(shí)基于全表掃描的會更耗性能，因此選擇基于成本選擇的索引。

優(yōu)化方法：order by (id+0)

select ...
ORDER BY goodsInfo.id+0 DESC
LIMIT 0, 20\G

執(zhí)行計(jì)劃

select ...
ORDER BY goodsInfo.id+0 DESC
LIMIT 0, 20\G

order by limit 導(dǎo)致索引失效的原因是當(dāng)查詢字段與排序字段不同時(shí)，如果使用查詢字段的索引，排序字段將無序。優(yōu)化器認(rèn)為排序操作昂貴，因此優(yōu)先使用排序字段的索引。

隱式轉(zhuǎn)換

字段類型不一致或字符集不一致時(shí)自動隱式轉(zhuǎn)換將導(dǎo)致索引失效。

SQL

SELECT
  id
FROM
  base_operating_report
WHERE
  yn = 1
  and ec_code = 42
order by
  inbound_time desc;

執(zhí)行計(jì)劃，顯示索引失效全表掃描

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: base_operating_report
   partitions: NULL
         type: ALL
possible_keys: idx_ecCode_transferCode
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 36524
     filtered: 1.00
        Extra: Using where; Using filesort
1 row in set, 3 warnings (0.00 sec)

查看警告信息，顯示隱式轉(zhuǎn)換導(dǎo)致索引失效

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: base_operating_report
   partitions: NULL
         type: ALL
possible_keys: idx_ecCode_transferCode
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 36524
     filtered: 1.00
        Extra: Using where; Using filesort
1 row in set, 3 warnings (0.00 sec)

表結(jié)構(gòu)

# 索引信息
KEY `idx_ecCode_transferCode` (`ec_code`, `transfer_code`)
# 字段類型
`ec_code` varchar(64) DEFAULT NULL COMMENT '倉庫編碼'

優(yōu)化方法：將參數(shù)中的數(shù)值類型轉(zhuǎn)換成字符串

SELECT
  id
FROM
  base_operating_report
WHERE
  yn = 1
  and ec_code = '42'
order by
  inbound_time desc;

執(zhí)行計(jì)劃，顯示索引生效。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: base_operating_report
   partitions: NULL
         type: ref
possible_keys: idx_ecCode_transferCode
          key: idx_ecCode_transferCode
      key_len: 195
          ref: const
         rows: 443
     filtered: 10.00
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

隱式轉(zhuǎn)換導(dǎo)致索引失效的原因是字段上有函數(shù)，而函數(shù)并不一定是單調(diào)函數(shù)，因此會破壞索引本身的有序性。

IN

SQL

select
  IFNULL(count(DISTINCT (awi.id)), 0)
from
  tc_attorney_waybill_info awi
where
  awi.is_delete = 0
  and awi.cur_transit_center_code in (
'2008' , '2052' , '2053' , '2054' , '2055' , '2056' , '2057' , '2058' , '2059' , '2061' , '2064' , '2069' , '2079' , '2084' , '2085' , '2094' , '2171' , '2201' , '2202' , '2207' , '2216' , '2258' , '2292' , '2301' , '2311' , '2324' , '2332' , '2334' , '2336' , '2349' , '2354' , '2355' , '2359' , '2367' , '2369' , '2373' , '2381' , '2385'
  )
  and awi.send_time >= '2022-10-20 00:00:00'
  and awi.send_time <= '2022-11-18 23:59:59'
  and awi.split_send_package_times > 0
  and awi.first_split_type = 1;
# 執(zhí)行用時(shí)
1 rows in set (1.309 sec)

執(zhí)行計(jì)劃，顯示索引失效全表掃描

trace，顯示當(dāng)前的索引中全表掃描的成本最低，因此索引失效。

                        "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_transit_code_waybill_code",
                        "ranges": [
                          "2008 <= cur_transit_center_code <= 2008",
                          "2052 <= cur_transit_center_code <= 2052",
                          "2053 <= cur_transit_center_code <= 2053",
                          "2054 <= cur_transit_center_code <= 2054",
                          "2055 <= cur_transit_center_code <= 2055",
                          "2056 <= cur_transit_center_code <= 2056",
                          "2057 <= cur_transit_center_code <= 2057",
                          "2058 <= cur_transit_center_code <= 2058",
                          "2059 <= cur_transit_center_code <= 2059",
                          "2061 <= cur_transit_center_code <= 2061",
                          "2064 <= cur_transit_center_code <= 2064",
                          "2069 <= cur_transit_center_code <= 2069",
                          "2079 <= cur_transit_center_code <= 2079",
                          "2084 <= cur_transit_center_code <= 2084",
                          "2085 <= cur_transit_center_code <= 2085",
                          "2094 <= cur_transit_center_code <= 2094",
                          "2171 <= cur_transit_center_code <= 2171",
                          "2201 <= cur_transit_center_code <= 2201",
                          "2202 <= cur_transit_center_code <= 2202",
                          "2207 <= cur_transit_center_code <= 2207",
                          "2216 <= cur_transit_center_code <= 2216",
                          "2258 <= cur_transit_center_code <= 2258",
                          "2292 <= cur_transit_center_code <= 2292",
                          "2301 <= cur_transit_center_code <= 2301",
                          "2311 <= cur_transit_center_code <= 2311",
                          "2324 <= cur_transit_center_code <= 2324",
                          "2332 <= cur_transit_center_code <= 2332",
                          "2334 <= cur_transit_center_code <= 2334",
                          "2336 <= cur_transit_center_code <= 2336",
                          "2349 <= cur_transit_center_code <= 2349",
                          "2354 <= cur_transit_center_code <= 2354",
                          "2355 <= cur_transit_center_code <= 2355",
                          "2359 <= cur_transit_center_code <= 2359",
                          "2367 <= cur_transit_center_code <= 2367",
                          "2369 <= cur_transit_center_code <= 2369",
                          "2373 <= cur_transit_center_code <= 2373",
                          "2381 <= cur_transit_center_code <= 2381",
                          "2385 <= cur_transit_center_code <= 2385"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1328061,
                        "cost": 1.59e6,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx_outstoretime",
                        "ranges": [
                          "0x99ae280000 <= send_time <= 0x99ae657efb"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1597295,
                        "cost": 1.92e6,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`tc_attorney_waybill_info` `awi`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3194590,
                      "access_type": "scan",
                      "resulting_rows": 3.19e6,
                      "cost": 677027,
                      "chosen": true
                    }
                  ]
                },

優(yōu)化方法：

創(chuàng)建一個(gè)更多查詢字段的聯(lián)合索引，減少回表次數(shù)；
縮小查詢的時(shí)間范圍，因?yàn)椴樵兊臄?shù)據(jù)量比較大，而且用到的字段比較多，導(dǎo)致回表成本高。

IN 導(dǎo)致索引失效的原因是符合條件的數(shù)據(jù)量過大導(dǎo)致回表成本高于全表掃描。

分組字段無索引

提數(shù)，創(chuàng)建唯一鍵之前分組查詢是否有重復(fù)數(shù)據(jù)。

SQL

select
  operate_id,
  waybill_code,
  private_call_id
from
  tos_resource.courier_call_out_record_0
group by
  operate_id,
  waybill_code,
  private_call_id
having
  count(*) > 1;

執(zhí)行計(jì)劃，顯示全表掃描

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: courier_call_out_record_0
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1470107
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)

分組字段無聯(lián)合索引，有兩個(gè)索引覆蓋三個(gè)分組字段，因此索引無法使用。

  KEY `courier_call_out_record_0_operate_id_IDX` (`operate_id`,`waybill_code`),
  KEY `courier_call_out_recourd_0_waybill_code` (`waybill_code`)

優(yōu)化方法：給分組字段創(chuàng)建聯(lián)合索引

alter table courier_call_out_record_0 
add index `courier_call_out_record_0_composite_key` (`operate_id`,`waybill_code`,`private_call_id`);

執(zhí)行計(jì)劃

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: courier_call_out_record_0
   partitions: NULL
         type: index
possible_keys: courier_call_out_record_0_composite_key
          key: courier_call_out_record_0_composite_key
      key_len: 907
          ref: NULL
         rows: 2230391
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

分組字段無聯(lián)合索引導(dǎo)致全表掃描的原因是分組時(shí)需要先排序，因此只有當(dāng)分組字段在同一個(gè)索引中時(shí)才可以保證有序。

索引的優(yōu)缺點(diǎn)

索引的代價(jià)

通過以上分析可以發(fā)現(xiàn)索引不是萬能的，實(shí)際上有時(shí)候索引甚至?xí)懈弊饔谩?/p>

創(chuàng)建索引的代價(jià)可以分為兩類：

空間代價(jià)，索引需要占用磁盤空間，并且刪除索引并不會立即釋放空間，因此無法通過刪除索引的方式降低磁盤使用率；
時(shí)間代價(jià)，有的時(shí)候會發(fā)現(xiàn)創(chuàng)建索引后導(dǎo)致寫入變慢，原因是每次數(shù)據(jù)寫入后還需要對該記錄按照索引排序。因此經(jīng)常更新的列不建議創(chuàng)建索引。

可見，索引的優(yōu)點(diǎn)是可以加快查詢速度，缺點(diǎn)是占用內(nèi)存與磁盤空間，同時(shí)減慢了插入與更新操作的速度。

因此，B+ Tree 適用于讀多寫少的業(yè)務(wù)場景，相對應(yīng)的 LSM-Tree 適用于寫多讀少的業(yè)務(wù)場景，原因是每次數(shù)據(jù)寫入對應(yīng)一條日志追加寫入磁盤文件，用順序 IO 代替了隨機(jī) IO。

索引使用的建議

關(guān)于索引的使用有以下幾點(diǎn)建議：

建議給區(qū)分度高的字段創(chuàng)建索引；
建議刪除冗余索引，否則優(yōu)化器可能使用 index_merge 導(dǎo)致選擇到錯(cuò)誤的索引；
不建議使用強(qiáng)制索引，比如當(dāng)數(shù)據(jù)量或統(tǒng)計(jì)信息發(fā)生變化時(shí)，強(qiáng)制索引不一定最優(yōu)。

下面測試下與索引相關(guān)的兩個(gè)操作：

如果 SQL 中強(qiáng)制指定已刪除的索引，SQL 執(zhí)行會報(bào)錯(cuò)嗎？
如果刪除字段，索引也會自動刪除嗎？

準(zhǔn)備數(shù)據(jù)

mysql> create table t_index_drop like t_index;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
       Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT '0',
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如果 SQL 中強(qiáng)制指定已刪除的索引，SQL 將直接報(bào)錯(cuò)，生產(chǎn)環(huán)境中遇到過相關(guān)案例。

mysql> alter table t_index_drop drop index idx_age_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from t_index_drop force index(idx_age_name);
ERROR 1176 (42000): Key 'idx_age_name' doesn't exist in table 't_index_drop'

如果刪除字段，索引也會自動刪除。

mysql> alter table t_index_drop add index idx_age(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
       Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT '0',
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table t_index_drop drop column age;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t_index_drop \G
*************************** 1. row ***************************
       Table: t_index_drop
Create Table: CREATE TABLE `t_index_drop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

基于優(yōu)化器選擇索引有可能選錯(cuò)索引導(dǎo)致性能下降，而使用強(qiáng)制索引可能導(dǎo)致 SQL 執(zhí)行直接報(bào)錯(cuò)。

結(jié)論

慢SQL的優(yōu)化原則是減少數(shù)據(jù)訪問量與減少計(jì)算操作，其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具。

索引是一種用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu)，基于以空間換時(shí)間的思想實(shí)現(xiàn)。

MySQL 中由存儲引擎層實(shí)現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實(shí)現(xiàn)，因此每個(gè)索引都是一棵 B+ 樹。

索引用于組織頁，頁用于組織行記錄。

其中：

每個(gè)數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個(gè)單向鏈表，依賴行記錄的Page Header中next_record屬性實(shí)現(xiàn)，其中保存下一條記錄相對于本條記錄的地址偏移量；
數(shù)據(jù)頁之間組成一個(gè)雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實(shí)現(xiàn)，其中保存本頁的上一個(gè)和下一個(gè)頁的頁號。

查詢操作可以分為以下兩步：