前言

本文針對 MySQL 數(shù)據(jù)庫的 InnoDB 存儲引擎，介紹其中索引的實現(xiàn)以及索引在慢 SQL 優(yōu)化中的作用。

本文主要討論不同場景下索引生效與失效的原因。

慢SQL與索引的關系

慢SQL優(yōu)化原則

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

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

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

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

減少訪問量：

?創(chuàng)建合適的索引

?減少不必要訪問的列

?使用覆蓋索引

?語句改寫

?數(shù)據(jù)結轉

減少計算操作：

?排序列加入索引

?適當?shù)牧腥哂?/p>

?SQL 拆分

?計算功能拆分

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

?查詢優(yōu)化

?索引優(yōu)化

?庫表結構優(yōu)化

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

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

索引的作用

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

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

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

?定位到記錄所在的頁

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

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

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

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

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

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

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

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

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

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

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

索引

索引的數(shù)據(jù)結構

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

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

索引用于組織頁，頁用于組織行記錄。在介紹索引的結構之前首先介紹頁的結構，如下圖所示。

其中：

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

?數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實現(xiàn)，其中保存本頁的上一個和下一個頁的頁號。

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

其中：

?用戶數(shù)據(jù)保存在葉子節(jié)點，目錄項保存在非葉子節(jié)點，每個節(jié)點中可能保存多個頁；

?最上面的節(jié)點稱為根節(jié)點，根節(jié)點的地址保存在內存的數(shù)據(jù)字典中；

?B+ 樹的深度一般控制在 3 層以內，因此定位到單條記錄不超過 3 次 IO。

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

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

?從 B+ 樹的根節(jié)點出發(fā)，一層一層向下搜索目錄項，由于上層節(jié)點保存的都是下層節(jié)點的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁；

?如果數(shù)據(jù)頁在緩存池中，直接從內存中獲取，否則從磁盤加載到內存中；

?數(shù)據(jù)頁內部二分查找定位滿足條件的記錄行。

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

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

索引可以分為兩種類型：

?聚簇索引，主鍵索引。葉子節(jié)點中保存主鍵值+對應的完整行記錄，目錄項中保存主鍵最小值+頁號。InnoDB 屬于索引組織表，每張表都有聚簇索引，因此表必須有主鍵，表中行的物理順序與索引的邏輯順序相同；

?非聚簇索引，二級索引，在非主鍵的其他列上建的索引。葉子節(jié)點中保存索引列的值+對應的主鍵值，目錄項中保存索引列最小值+對應的主鍵值+頁號。

介紹三個與索引性能相關的概念：

?覆蓋索引，當二級索引中包含要查詢的所有字段時，這個索引稱為覆蓋索引；

?回表，當二級索引中不包含要查詢的所有字段時，就需要先通過二級索引查出主鍵索引，再通過主鍵索引查詢二級索引中沒有的其他列的數(shù)據(jù)，這個過程叫做回表；

?索引下推，用于優(yōu)化使用二級索引從表中檢索行的實現(xiàn)。條件過濾可以下推到存儲引擎層進行，先由索引元組（index tuple）根據(jù)查詢條件進行過濾，滿足條件的前提下才回表，否則跳過，相當于延遲加載數(shù)據(jù)行，因此 ICP 可以降低回表次數(shù)與 IO 次數(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)建二級索引時顯式指定主鍵，實際上是不需要的，二級索引末尾自動保存主鍵。

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

因此二級索引+主鍵與聯(liá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í)行用時
5 rows in set(7.311125 sec)

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

表結構，顯示查詢字段無索引

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 '內部主鍵',
  `wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT'倉庫編碼',
  `business_no` varchar(20) NOT NULL DEFAULT '' COMMENT'調研業(yè)務主鍵',
   ...
   PRIMARY KEY(`sys_no`)
)ENGINE=InnoDB AUTO_INCREMENT=2007412 DEFAULT CHARSET=utf8 COMMENT='評價結果表'
1rowinset(0.00 sec)

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

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

執(zhí)行計劃

***************************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í)行用時
5 rows in set(0.01 sec)

其中：

?key_len: 160，表明聯(lián)合索引的兩個字段都用到了，(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í)行計劃，顯示全表掃描

***************************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)合索引實現(xiàn)覆蓋索引

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

執(zhí)行計劃，顯示 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é)點中除了保存索引，還保存指向下個節(jié)點的指針，因此遍歷葉子節(jié)點就可以獲得范圍值。

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

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

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

回表查詢成本高有兩點原因：

?需要使用到兩個 B+ 樹索引，一個二級索引，一個聚簇索引；

?訪問二級索引使用順序 I/O，訪問聚簇索引使用隨機 I/O。

因此有兩條建議：

?建議為區(qū)分度高的字段創(chuàng)建索引，并且將區(qū)分度高的字段優(yōu)先放在聯(lián)合索引前面；

?建議優(yōu)先使用覆蓋索引，必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風險。

索引失效的場景

違反最左匹配原則

SQL

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

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

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

當前查詢條件的多個字段區(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í)行計劃

*************************** 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í)行計劃中的 key_len 字段評估具體 SQL 使用到了聯(lián)合索引中的幾個字段；

?聯(lián)合索引中頁面和記錄首先按照聯(lián)合索引前面的列排序，如果該列值相同，再按照聯(lián)合索引后邊的列排序。

違反最左匹配原則導致索引失效的原因是只有當索引前面的列相同時，后面的列才有序。

下面結合 innodb_ruby 工具解析 InnoDB 數(shù)據(jù)文件查看記錄保存的順序驗證聯(liá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 時，三條記錄按照 name 字段逆序；

?select 時，三條記錄按照聯(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é)點頁號分別是 3 與 4；

?查看聚簇索引中保存的記錄，按照主鍵排序；

?查看二級索引中保存的記錄，聯(lián)合索引中當 age 相同時，name 有序，age 不同時，name 無序。

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

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 表有兩個索引，對應的根節(jié)點頁號分別等于 3 與 4，與上面數(shù)據(jù)文件解析的結果一致。

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í)行用時
2 rows in set (1 min 9.71 sec)

執(zhí)行計劃，主鍵全索引掃描，聯(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)

表結構與查詢條件

# 查詢條件
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í)行用時
2 rows in set (0.06 sec)

執(zhí)行計劃顯示當改為 limit 30 時，聯(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 值也有關系。

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

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

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

?強制索引，通過 hint 固化執(zhí)行計劃，比如可以通過 force index 指定使用的索引，但是當條件發(fā)生變化時有可能失效，因此生產環(huán)境中不建議使用；

?prefer_ordering_index，5.7.33 中已修復該 bug，因此建議新申請時使用 5.7.33 及以上版本，存量低版本建議升級，建議優(yōu)先使用該方法；

?聯(lián)合索引，建議在合適的字段加聯(lián)合索引， 增強可選索引的區(qū)分度，讓優(yōu)化器認為這種方式優(yōu)于有序索引；

?order by (id+0)，通過 trick 的方式欺騙優(yōu)化器，由于 id 上進行了加法這種耗時操作，使優(yōu)化器認為此時基于全表掃描的會更耗性能，因此選擇基于成本選擇的索引。

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

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

執(zhí)行計劃

*************************** 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)

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

隱式轉換

字段類型不一致或字符集不一致時自動隱式轉換將導致索引失效。

SQL

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

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

*************************** 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)

查看警告信息，顯示隱式轉換導致索引失效

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'
*************************** 2. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use range access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `dms_uat`.`base_operating_report`.`id` AS `id` from `dms_uat`.`base_operating_report` where ((`dms_uat`.`base_operating_report`.`yn` = 1) and (`dms_uat`.`base_operating_report`.`ec_code` = 42)) order by `dms_uat`.`base_operating_report`.`inbound_time` desc
3 rows in set (0.00 sec)

表結構

# 索引信息
KEY `idx_ecCode_transferCode` (`ec_code`, `transfer_code`)

# 字段類型
`ec_code` varchar(64) DEFAULT NULL COMMENT '倉庫編碼'

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

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

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

*************************** 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)

隱式轉換導致索引失效的原因是字段上有函數(shù)，而函數(shù)并不一定是單調函數(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í)行用時
1 rows in set (1.309 sec)

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

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

				"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)建一個更多查詢字段的聯(lián)合索引，減少回表次數(shù)；

?縮小查詢的時間范圍，因為查詢的數(shù)據(jù)量比較大，而且用到的字段比較多，導致回表成本高。

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

分組字段無索引

提數(shù)，創(chuàng)建唯一鍵之前分組查詢是否有重復數(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í)行計劃，顯示全表掃描

*************************** 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)合索引，有兩個索引覆蓋三個分組字段，因此索引無法使用。

  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í)行計劃

*************************** 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)合索引導致全表掃描的原因是分組時需要先排序，因此只有當分組字段在同一個索引中時才可以保證有序。

索引的優(yōu)缺點

索引的代價

通過以上分析可以發(fā)現(xiàn)索引不是萬能的，實際上有時候索引甚至會有副作用。

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

?空間代價，索引需要占用磁盤空間，并且刪除索引并不會立即釋放空間，因此無法通過刪除索引的方式降低磁盤使用率；

?時間代價，有的時候會發(fā)現(xiàn)創(chuàng)建索引后導致寫入變慢，原因是每次數(shù)據(jù)寫入后還需要對該記錄按照索引排序。因此經常更新的列不建議創(chuàng)建索引。

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

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

索引使用的建議

關于索引的使用有以下幾點建議：

?建議給區(qū)分度高的字段創(chuàng)建索引；

?建議刪除冗余索引，否則優(yōu)化器可能使用 index_merge 導致選擇到錯誤的索引；

?不建議使用強制索引，比如當數(shù)據(jù)量或統(tǒng)計信息發(fā)生變化時，強制索引不一定最優(yōu)。

下面測試下與索引相關的兩個操作：

?如果 SQL 中強制指定已刪除的索引，SQL 執(zhí)行會報錯嗎？

?如果刪除字段，索引也會自動刪除嗎？

準備數(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 中強制指定已刪除的索引，SQL 將直接報錯，生產環(huá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)化器選擇索引有可能選錯索引導致性能下降，而使用強制索引可能導致 SQL 執(zhí)行直接報錯。

結論

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

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

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

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

其中：

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

?數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實現(xiàn)，其中保存本頁的上一個和下一個頁的頁號。

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

?定位到記錄所在的頁

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

對比沒有索引與有索引時查詢操作的性能差異：

?沒有索引時每次查詢都是全表掃描；

?有索引時從 B+ 樹的根節(jié)點出發(fā)，一層一層向下搜索目錄項，由于上層節(jié)點保存的都是下層節(jié)點的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁。

關于索引失效的場景總結以下兩點：

?索引的本質是有序的數(shù)據(jù)結構，因此破壞索引有序性的操作都有可能導致索引失效或部分生效；

?回表成本較高，因此優(yōu)先使用覆蓋索引，必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風險。

參考教程

?《MySQL 是怎樣運行的：從根兒上理解 MySQL》

?MySQL工具之innodb_ruby：探究InnoDB存儲結構的利器

?你管這破玩意叫B+樹?

作者：京東物流 張凱

來源：京東云開發(fā)者社區(qū)文章來源地址http://www.zghlxwxcb.cn/news/detail-474177.html

到了這里，關于慢 SQL 優(yōu)化之索引的作用是什么？的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！