前言本文針對 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 往往會伴隨著大量的行掃描、臨時文件排序，直接影響就是磁盤 IO 升高、CPU 使用率升高，正常 SQL 也變?yōu)榱寺?SQL，對于應(yīng)用來說就是大面積執(zhí)行超時 。
線上很多事故都與慢 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ù)牧腥哂?br /> ?SQL 拆分
?計(jì)算功能拆分
可以將慢 SQL 優(yōu)化的方法分為三類：
?查詢優(yōu)化
?索引優(yōu)化
?庫表結(jié)構(gòu)優(yōu)化
其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具 。
可是，為什么索引可以加快查詢 ， 索引一定可以加快查詢嗎？
索引的作用要回答這個問題，可以對比沒有索引與有索引時查詢操作的性能差異 。
在此之前，首先介紹下查詢操作的處理流程 。
查詢操作可以分為以下兩步：
?定位到記錄所在的頁
?從所在的頁中定位到具體的記錄
其中從頁中定位記錄的方法依賴每個頁面中創(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ù)據(jù)沒有規(guī)律，因此無法快速定位記錄所在的頁，只能從第一個頁沿雙向鏈表向后遍歷，也就是說需要遍歷所有數(shù)據(jù)頁依次判斷是否滿足查詢條件 。
簡單來說 ， 沒有索引時每次查詢都是全表掃描 。
因此索引需要解決的主要問題就是實(shí)現(xiàn)每個數(shù)據(jù)頁中數(shù)據(jù)有規(guī)律，具體是保證下一個數(shù)據(jù)頁中用戶記錄的索引列值必須大于上一個頁中用戶記錄的索引列值 。
索引是存儲引擎用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu) 。
有索引時，優(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í)現(xiàn)，用于加速查詢 。
MySQL 中由存儲引擎層實(shí)現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實(shí)現(xiàn) ， 因此每個索引都是一棵 B+ 樹 。
索引用于組織頁，頁用于組織行記錄 。在介紹索引的結(jié)構(gòu)之前首先介紹頁的結(jié)構(gòu)，如下圖所示 。

文章插圖
其中：
?每個數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個單向鏈表，依賴行記錄的Page Header中next_record屬性實(shí)現(xiàn) ， 其中保存下一條記錄相對于本條記錄的地址偏移量；
?數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實(shí)現(xiàn) ， 其中保存本頁的上一個和下一個頁的頁號 。
多個頁通過樹進(jìn)行組織 ， 其中保存用戶數(shù)據(jù)與目錄項(xiàng) 。目錄項(xiàng)中保存頁的用戶記錄中主鍵的最小值與頁號，從而保證下一個數(shù)據(jù)頁中用戶記錄的主鍵值大于上一個頁中用戶記錄的主鍵值 。

文章插圖
其中：
?用戶數(shù)據(jù)保存在葉子節(jié)點(diǎn)，目錄項(xiàng)保存在非葉子節(jié)點(diǎn)，每個節(jié)點(diǎn)中可能保存多個頁；
?最上面的節(jié)點(diǎn)稱為根節(jié)點(diǎn)，根節(jié)點(diǎn)的地址保存在內(nèi)存的數(shù)據(jù)字典中；
?B+ 樹的深度一般控制在 3 層以內(nèi)，因此定位到單條記錄不超過 3 次 IO 。
因此，頁面和記錄是排好序的 ， 就可以通過二分法來快速定位查找 。
有索引時，查詢操作變成了什么樣呢？
?從 B+ 樹的根節(jié)點(diǎn)出發(fā)，一層一層向下搜索目錄項(xiàng) ， 由于上層節(jié)點(diǎn)保存的都是下層節(jié)點(diǎn)的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁；
?如果數(shù)據(jù)頁在緩存池中，直接從內(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)二級索引中包含要查詢的所有字段時 ， 這個索引稱為覆蓋索引；
?回表，當(dāng)二級索引中不包含要查詢的所有字段時，就需要先通過二級索引查出主鍵索引 ， 再通過主鍵索引查詢二級索引中沒有的其他列的數(shù)據(jù)，這個過程叫做回表；
?索引下推，用于優(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:INNODBENGINE_LOCK_ID:140123070938328:14:7:4:140122972537552ENGINE_TRANSACTION_ID:2032566THREAD_ID:157EVENT_ID:44OBJECT_SCHEMA: test_zkOBJECT_NAME: t_lock_testPARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME: idx_uk_mobileOBJECT_INSTANCE_BEGIN:140122972537552LOCK_TYPE: RECORDLOCK_MODE: X,REC_NOT_GAPLOCK_STATUS: GRANTEDLOCK_DATA:'17118168721',2如下所示，工作中多次遇到研發(fā)創(chuàng)建二級索引時顯式指定主鍵，實(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_pinfromevaluate_resultwhereyn =1and wh_no ='611-887-2'and business_no ='QNSYKF23020900000018'and create_pin ='13940137489'orderbyupdate_time desc;# 執(zhí)行用時5 rows in set(7.311125 sec)執(zhí)行計(jì)劃，顯示全表掃描

文章插圖
表結(jié)構(gòu)，顯示查詢字段無索引
mysql>show create table evaluate_result \G***************************1.row***************************Table: evaluate_resultCreateTable: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é)果表'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:1select_type: SIMPLEtable: evaluate_resultpartitions:NULLtype: refpossible_keys: idx_wh_bus_nokey: idx_wh_bus_nokey_len:160ref: const,constrows:5filtered:1.00Extra:Using index condition;Using where;Using filesort1rowinset,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
selectidfromboard_chutewherestatus=1and create_time <= date_sub(now(),interval 24 hour);執(zhí)行計(jì)劃 ， 顯示全表掃描
***************************1.row***************************id:1select_type: SIMPLEtable: board_chutepartitions:NULLtype:ALLpossible_keys: idx_create_timekey:NULLkey_len:NULLref:NULLrows:407632filtered:5.00Extra:Using where1rowinset,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: 1select_type: SIMPLEtable: board_chutepartitions: NULLtype: rangepossible_keys: idx_create_time,idx_status_create_timekey: idx_status_create_timekey_len: 8ref: NULLrows: 203816filtered: 10.00Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)范圍查詢索引生效的原因是葉子節(jié)點(diǎn)中除了保存索引，還保存指向下個節(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)換成一個固定的地址，如果發(fā)生哈希碰撞就在這個位置拉出一個鏈表 。因此哈希表的優(yōu)點(diǎn)是插入操作的速度快，根據(jù) key 直接往后追加即可 。但由于散列函數(shù)的離散特性，經(jīng)過散列函數(shù)處理后的 key 將失去原有的順序，所以哈希表無法滿足范圍查詢，只適合等值查詢 。
注意上述索引生效的場景并非絕對成立，需要回表的記錄越多，優(yōu)化器越傾向于使用全表掃描，反之傾向于使用二級索引 + 回表的方式 。
回表查詢成本高有兩點(diǎn)原因：
?需要使用到兩個 B+ 樹索引，一個二級索引，一個聚簇索引；
?訪問二級索引使用順序 I/O ， 訪問聚簇索引使用隨機(jī) I/O 。
因此有兩條建議：
【關(guān)于sql索引的使用例子 sql索引有什么用途】?建議為區(qū)分度高的字段創(chuàng)建索引，并且將區(qū)分度高的字段優(yōu)先放在聯(lián)合索引前面；
?建議優(yōu)先使用覆蓋索引，必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風(fēng)險 。
索引失效的場景違反最左匹配原則SQL
selectcount(*)fromsort_cross_detailwhereyn =1and org_id =3and site_type =16and site_code ='121671';執(zhí)行計(jì)劃，顯示全表掃描

文章插圖
盡管當(dāng)前有聯(lián)合索引 idx_site_type(SUB_TYPE, THIRD_TYPE, SITE_TYPE )，但由于查詢條件中不包括 SUB_TYPE 字段，因此違反最左匹配原則，導(dǎo)致索引失效 。
當(dāng)前查詢條件的多個字段區(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: 1select_type: SIMPLEtable: sort_cross_detailpartitions: NULLtype: refpossible_keys: idx_site_codekey: idx_site_codekey_len: 99ref: constrows: 1336filtered: 0.10Extra: Using where1 row in set, 1 warning (0.00 sec)其中：
?使用聯(lián)合索引過程中可以通過執(zhí)行計(jì)劃中的 key_len 字段評估具體 SQL 使用到了聯(lián)合索引中的幾個字段；
?聯(lián)合索引中頁面和記錄首先按照聯(lián)合索引前面的列排序，如果該列值相同，再按照聯(lián)合索引后邊的列排序 。
違反最左匹配原則導(dǎo)致索引失效的原因是只有當(dāng)索引前面的列相同時，后面的列才有序 。
下面結(jié)合 innodb_ruby 工具解析 InnoDB 數(shù)據(jù)文件查看記錄保存的順序驗(yàn)證聯(lián)合索引中索引前面的列不同時，后面的列可能無序 。
準(zhǔn)備測試數(shù)據(jù) 。
mysql> show create table t_index \G*************************** 1. row ***************************Table: t_indexCreate 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=utf81 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: 3Duplicates: 0Warnings: 0mysql> 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: 1select_type: SIMPLEtable: t_indexpartitions: NULLtype: indexpossible_keys: NULLkey: idx_age_namekey_len: 38ref: NULLrows: 3filtered: 100.00Extra: Using index1 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-indexesidnamerootfsegfseg_idusedallocatedfill_factor 218PRIMARY3internal111100.00%218PRIMARY3leaf2000.00%219idx_age_name4internal311100.00%219idx_age_name4leaf4000.00%[root@exps-test3 data]# [root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 3 page-recordsRecord 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-recordsRecord 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 相同時，name 有序 ， age 不同時，name 無序 。
上面提到 ， 數(shù)據(jù)字典中保存表的根節(jié)點(diǎn)的地址，具體是
INFORMATION_SCHEMA.INNODB_SYS_INDEXES 系統(tǒng)表的 space 與 page_no 字段，分別保存表空間 ID 與根節(jié)點(diǎn)頁號 。
mysql> SELECTtables.name, indexs.space, indexs.name, indexs.page_noFROMINFORMATION_SCHEMA.INNODB_SYS_TABLES as tablesinner join INFORMATION_SCHEMA.INNODB_SYS_INDEXES as indexs on tables.table_id = indexs.table_idWHEREtables.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 表有兩個索引，對應(yīng)的根節(jié)點(diǎn)頁號分別等于 3 與 4，與上面數(shù)據(jù)文件解析的結(jié)果一致 。
order by limitSQL，不建議使用 select *
select* fromwaybill_order_added_value_report_detail goodsInfo WHEREgoodsInfo.is_delete = 0AND goodsInfo.org_no = '418'AND goodsInfo.distribute_no = '636'AND (goodsInfo.company_code = 'EBU4418046542406'OR goodsInfo.company_name = 'EBU4418046542406')AND goodsInfo.network_type = 1AND 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 BYgoodsInfo.id DESC LIMIT0, 20 \G# 執(zhí)行用時2 rows in set (1 min 9.71 sec)執(zhí)行計(jì)劃 ， 主鍵全索引掃描，聯(lián)合索引失效
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: goodsInfotype: indexpossible_keys: idx_org_dc_operator_time,idx_operator_time_networkkey: PRIMARYkey_len: 8ref: NULLrows: 16156Extra: Using where1 row in set (0.00 sec)表結(jié)構(gòu)與查詢條件
# 查詢條件WHEREgoodsInfo.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* fromwaybill_order_added_value_report_detail goodsInfo WHERE...ORDER BYgoodsInfo.id DESC LIMIT0, 30 \G# 執(zhí)行用時2 rows in set (0.06 sec)執(zhí)行計(jì)劃顯示當(dāng)改為 limit 30 時，聯(lián)合索引生效 。
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: goodsInfotype: rangepossible_keys: idx_org_dc_operator_time,idx_operator_time_networkkey: idx_org_dc_operator_timekey_len: 132ref: NULLrows: 19024Extra: Using index condition; Using where; Using filesort1 row in set (0.00 sec)可見 ， 索引的選擇與 limit n 的 n 值也有關(guān)系 。
從現(xiàn)象上看，當(dāng) limit n 的 n 值變大時，SQL的執(zhí)行反倒有可能變快了 。
實(shí)際上，這是 MySQL 低版本中的 bug #97001 ， 優(yōu)化器認(rèn)為排序是個昂貴的操作，因此在執(zhí)行 order by id limit 這條 SQL 時，為了避免排序，并且認(rèn)為當(dāng) limit n 的 n 很小時，全表掃描可以很快執(zhí)行完 ， 因此選擇使用全表掃描，以避免額外的排序 。
針對 MySQL 中 order by limit 或 group by limit 優(yōu)化器選擇錯誤索引的場景，常見的優(yōu)化方法有四種：
?強(qiáng)制索引，通過 hint 固化執(zhí)行計(jì)劃，比如可以通過 force index 指定使用的索引，但是當(dāng)條件發(fā)生變化時有可能失效 ， 因此生產(chǎn)環(huán)境中不建議使用；
?prefer_ordering_index ， 5.7.33 中已修復(fù)該 bug，因此建議新申請時使用 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)行了加法這種耗時操作，使優(yōu)化器認(rèn)為此時基于全表掃描的會更耗性能，因此選擇基于成本選擇的索引 。
優(yōu)化方法：order by (id+0)
select ...ORDER BY goodsInfo.id+0 DESCLIMIT 0, 20\G執(zhí)行計(jì)劃
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: goodsInfotype: rangepossible_keys: idx_org_dc_operator_time,idx_operator_time_networkkey: idx_org_dc_operator_timekey_len: 132ref: NULLrows: 19024Extra: Using index condition; Using where; Using filesort1 row in set (0.00 sec)order by limit 導(dǎo)致索引失效的原因是當(dāng)查詢字段與排序字段不同時，如果使用查詢字段的索引，排序字段將無序 。優(yōu)化器認(rèn)為排序操作昂貴，因此優(yōu)先使用排序字段的索引 。
隱式轉(zhuǎn)換字段類型不一致或字符集不一致時自動隱式轉(zhuǎn)換將導(dǎo)致索引失效 。
SQL
SELECTidFROMbase_operating_reportWHEREyn = 1and ec_code = 42order byinbound_time desc;執(zhí)行計(jì)劃，顯示索引失效全表掃描
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: base_operating_reportpartitions: NULLtype: ALLpossible_keys: idx_ecCode_transferCodekey: NULLkey_len: NULLref: NULLrows: 36524filtered: 1.00Extra: Using where; Using filesort1 row in set, 3 warnings (0.00 sec)查看警告信息，顯示隱式轉(zhuǎn)換導(dǎo)致索引失效
mysql> show warnings \G*************************** 1. row ***************************Level: WarningCode: 1739Message: Cannot use ref access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'*************************** 2. row ***************************Level: WarningCode: 1739Message: Cannot use range access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'*************************** 3. row ***************************Level: NoteCode: 1003Message: /* 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` desc3 rows in set (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)換成字符串
SELECTidFROMbase_operating_reportWHEREyn = 1and ec_code = '42'order byinbound_time desc;執(zhí)行計(jì)劃，顯示索引生效 。
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: base_operating_reportpartitions: NULLtype: refpossible_keys: idx_ecCode_transferCodekey: idx_ecCode_transferCodekey_len: 195ref: constrows: 443filtered: 10.00Extra: Using index condition; Using where; Using filesort1 row in set, 1 warning (0.00 sec)隱式轉(zhuǎn)換導(dǎo)致索引失效的原因是字段上有函數(shù)，而函數(shù)并不一定是單調(diào)函數(shù)，因此會破壞索引本身的有序性 。
INSQL
selectIFNULL(count(DISTINCT (awi.id)), 0)fromtc_attorney_waybill_info awiwhereawi.is_delete = 0and 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 > 0and awi.first_split_type = 1;# 執(zhí)行用時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)建一個更多查詢字段的聯(lián)合索引 ， 減少回表次數(shù)；
?縮小查詢的時間范圍，因?yàn)椴樵兊臄?shù)據(jù)量比較大，而且用到的字段比較多，導(dǎo)致回表成本高 。
IN 導(dǎo)致索引失效的原因是符合條件的數(shù)據(jù)量過大導(dǎo)致回表成本高于全表掃描 。
分組字段無索引提數(shù)，創(chuàng)建唯一鍵之前分組查詢是否有重復(fù)數(shù)據(jù) 。
SQL
selectoperate_id,waybill_code,private_call_idfromtos_resource.courier_call_out_record_0group byoperate_id,waybill_code,private_call_idhavingcount(*) > 1;執(zhí)行計(jì)劃，顯示全表掃描
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: courier_call_out_record_0partitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1470107filtered: 100.00Extra: Using temporary; Using filesort1 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í)行計(jì)劃
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: courier_call_out_record_0partitions: NULLtype: indexpossible_keys: courier_call_out_record_0_composite_keykey: courier_call_out_record_0_composite_keykey_len: 907ref: NULLrows: 2230391filtered: 100.00Extra: Using index1 row in set, 1 warning (0.00 sec)分組字段無聯(lián)合索引導(dǎo)致全表掃描的原因是分組時需要先排序，因此只有當(dāng)分組字段在同一個索引中時才可以保證有序 。
索引的優(yōu)缺點(diǎn)索引的代價通過以上分析可以發(fā)現(xiàn)索引不是萬能的 ， 實(shí)際上有時候索引甚至?xí)懈弊饔?。
創(chuàng)建索引的代價可以分為兩類：
?空間代價 ， 索引需要占用磁盤空間，并且刪除索引并不會立即釋放空間，因此無法通過刪除索引的方式降低磁盤使用率；
?時間代價，有的時候會發(fā)現(xiàn)創(chuàng)建索引后導(dǎo)致寫入變慢，原因是每次數(shù)據(jù)寫入后還需要對該記錄按照索引排序 。因此經(jīng)常更新的列不建議創(chuàng)建索引 。
可見，索引的優(yōu)點(diǎn)是可以加快查詢速度 ， 缺點(diǎn)是占用內(nèi)存與磁盤空間，同時減慢了插入與更新操作的速度 。
因此，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)致選擇到錯誤的索引；
?不建議使用強(qiáng)制索引，比如當(dāng)數(shù)據(jù)量或統(tǒng)計(jì)信息發(fā)生變化時，強(qiáng)制索引不一定最優(yōu) 。
下面測試下與索引相關(guān)的兩個操作：
?如果 SQL 中強(qiáng)制指定已刪除的索引 ， SQL 執(zhí)行會報錯嗎？
?如果刪除字段，索引也會自動刪除嗎？
準(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_dropCreate 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=utf81 row in set (0.00 sec)如果 SQL 中強(qiáng)制指定已刪除的索引，SQL 將直接報錯，生產(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: 0Duplicates: 0Warnings: 0mysql> 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: 0Duplicates: 0Warnings: 0mysql> show create table t_index_drop \G*************************** 1. row ***************************Table: t_index_dropCreate 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=utf81 row in set (0.00 sec)mysql> alter table t_index_drop drop column age;Query OK, 0 rows affected (0.02 sec)Records: 0Duplicates: 0Warnings: 0mysql> show create table t_index_drop \G*************************** 1. row ***************************Table: t_index_dropCreate 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=utf81 row in set (0.00 sec)基于優(yōu)化器選擇索引有可能選錯索引導(dǎo)致性能下降，而使用強(qiáng)制索引可能導(dǎo)致 SQL 執(zhí)行直接報錯 。
結(jié)論慢SQL的優(yōu)化原則是減少數(shù)據(jù)訪問量與減少計(jì)算操作，其中索引是數(shù)據(jù)庫中用來提升性能的最常用工具 。
索引是一種用于快速查找的一種排序的數(shù)據(jù)結(jié)構(gòu)，基于以空間換時間的思想實(shí)現(xiàn) 。
MySQL 中由存儲引擎層實(shí)現(xiàn)索引，InnoDB 存儲引擎中基于 B+ 樹實(shí)現(xiàn)，因此每個索引都是一棵 B+ 樹 。
索引用于組織頁，頁用于組織行記錄 。
其中：
?每個數(shù)據(jù)頁中的記錄會按照主鍵值從小到大的順序組成一個單向鏈表，依賴行記錄的Page Header中next_record屬性實(shí)現(xiàn)，其中保存下一條記錄相對于本條記錄的地址偏移量；
?數(shù)據(jù)頁之間組成一個雙向鏈表，依賴數(shù)據(jù)頁的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT屬性實(shí)現(xiàn)，其中保存本頁的上一個和下一個頁的頁號 。
查詢操作可以分為以下兩步：
?定位到記錄所在的頁
?從所在的頁中定位到具體的記錄
對比沒有索引與有索引時查詢操作的性能差異：
?沒有索引時每次查詢都是全表掃描；
?有索引時從 B+ 樹的根節(jié)點(diǎn)出發(fā)，一層一層向下搜索目錄項(xiàng)，由于上層節(jié)點(diǎn)保存的都是下層節(jié)點(diǎn)的最小值，因此可以快速定位到數(shù)據(jù)可能所在的頁 。
關(guān)于索引失效的場景總結(jié)以下兩點(diǎn)：
?索引的本質(zhì)是有序的數(shù)據(jù)結(jié)構(gòu)，因此破壞索引有序性的操作都有可能導(dǎo)致索引失效或部分生效；
?回表成本較高，因此優(yōu)先使用覆蓋索引 ， 必須要回表時也需要控制回表的記錄數(shù)，從而降低索引失效的風(fēng)險 。

以上關(guān)于本文的內(nèi)容，僅作參考！溫馨提示：如遇健康、疾病相關(guān)的問題，請您及時就醫(yī)或請專業(yè)人士給予相關(guān)指導(dǎo)!

「愛刨根生活網(wǎng)」www.malaban59.cn小編還為您精選了以下內(nèi)容，希望對您有所幫助：

• Access數(shù)據(jù)庫中SQL語句使用技巧
• 關(guān)于離婚協(xié)議書怎么寫 離婚協(xié)議書怎么寫
• 二十首關(guān)于冬至的詩詞 冬至的古詩
• 翡翠手鐲保養(yǎng)小知識 關(guān)于翡翠手鐲知識
• 關(guān)于勞動節(jié)的由來 勞動節(jié)的由來
• 優(yōu)化你的瀏覽器搜索引擎設(shè)置
• 如何在Power BI中連接MySQL數(shù)據(jù)源
• Navicat：讓MySQL管理更簡單
• 關(guān)于ecco這個品牌你了解多少 ECCO什么牌子
• 關(guān)于助聽器價格的說明 一個助聽器大約要多少錢