EXPLAIN的rows值僅由索引連續(xù)前綴字段估算，索引下推（ICP）是減回表優(yōu)化，無法減少索引掃描量,ICP過濾字段不影響掃描行數(shù)預(yù)估；

一、基礎(chǔ)環(huán)境與表結(jié)構(gòu)信息

1.1 數(shù)據(jù)表結(jié)構(gòu)

本次分析基于業(yè)務(wù)表 contract_company_info（合同分公司明細(xì)表） ，核心表結(jié)構(gòu)及索引如下：

CREATE TABLE IF NOT EXISTS `contract_company_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '分公司明細(xì)表主鍵',
  `delete_flag` smallint(2) NOT NULL DEFAULT 0 COMMENT '數(shù)據(jù)狀態(tài),0正常,1刪除',
  `contract_code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '合同編號',
  `project_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '關(guān)聯(lián)項(xiàng)目號',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時(shí)間',
  PRIMARY KEY (`id`) USING BTREE,
 ?-- 核心聯(lián)合索引（本次分析重點(diǎn)）
  KEY `idx_contract_company` (`contract_code`,`company_code`,`delete_flag`) USING BTREE,
  KEY `idx_contract_oppo` (`contract_code`,`opportunity_code`,`delete_flag`),
  KEY `idx_company_code` (`company_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1686295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同分公司明細(xì)表';

1.2 核心索引說明

• idx_contract_company：聯(lián)合索引順序 contract_code > company_code > delete_flag
• 索引特性：僅最左前綴可用于縮小掃描區(qū)間，非連續(xù)字段僅可用于索引下推過濾，無法裁剪掃描范圍

二、目標(biāo)業(yè)務(wù)SQL

本次優(yōu)化分析的核心查詢SQL，業(yè)務(wù)需求：根據(jù)指定合同號、有效數(shù)據(jù)狀態(tài)，查詢合同關(guān)聯(lián)項(xiàng)目編碼

SELECT
  contract_code,
  project_code 
FROM
  contract_company_info 
WHERE
  delete_flag = 0
 ?AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

三、默認(rèn)執(zhí)行計(jì)劃分析（無強(qiáng)制索引）

3.1 原始執(zhí)行計(jì)劃結(jié)果

未添加任何強(qiáng)制索引時(shí)，MySQL優(yōu)化器默認(rèn)選擇全表掃描：

1 SIMPLE contract_company_info ALL idx_contract_company,idx_contract_oppo 799303 Using where

3.2 執(zhí)行計(jì)劃逐字段解析

• type=ALL：全表掃描，未使用任何二級索引
• possible_keys：優(yōu)化器識別到可用索引 idx_contract_company、idx_contract_oppo
• rows=799303：預(yù)估掃描全表近80萬行數(shù)據(jù)
• Extra=Using where：Server層過濾數(shù)據(jù)，無索引優(yōu)化

3.3 默認(rèn)走全表掃描的核心原因

MySQL基于成本優(yōu)化器（CBO） 決策，核心邏輯：

• 現(xiàn)有索引 idx_contract_company 不包含查詢字段 project_code，走索引必須回表查詢
• 優(yōu)化器基于全局統(tǒng)計(jì)信息，預(yù)判該條件匹配數(shù)據(jù)量大，回表產(chǎn)生的隨機(jī)IO成本遠(yuǎn)高于全表順序IO
• 全表掃描數(shù)據(jù)常駐內(nèi)存緩沖池，順序遍歷效率極高，優(yōu)化器判定更劃算

四、強(qiáng)制索引執(zhí)行計(jì)劃深度分析（觸發(fā)ICP索引下推）

4.1 強(qiáng)制索引SQL

EXPLAIN SELECT
  contract_code,
  project_code 
FROM
  contract_company_info FORCE INDEX(idx_contract_company)
WHERE
  delete_flag = 0
 ?AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

4.2 強(qiáng)制索引執(zhí)行計(jì)劃結(jié)果（結(jié)構(gòu)化表格解析）

強(qiáng)制索引后完整執(zhí)行計(jì)劃及逐字段解析如下：

4.3 核心字段逐行解析

4.3.1 type=range

IN 查詢被優(yōu)化為索引范圍掃描，成功命中二級索引，替代全表掃描。

4.3.2 key_len=259（核心關(guān)鍵）

僅使用索引最左前綴 contract_code一列，計(jì)算佐證：

• varchar(64) utf8mb4：64*4=256字節(jié)
• 變長字段標(biāo)記：2字節(jié)
• NULL標(biāo)識：1字節(jié)
• 合計(jì)：259字節(jié)

結(jié)論：delete_flag 未參與索引范圍裁剪，僅靠 contract_code 確定掃描區(qū)間。

4.3.3 rows=404811

優(yōu)化器僅根據(jù)索引前綴contract_code估算的掃描行數(shù)，和 delete_flag、索引下推無關(guān)，僅代表需要遍歷的索引總行數(shù)。

4.3.4 Extra 核心優(yōu)化標(biāo)識

• Using index condition（ICP索引下推） ：過濾邏輯從Server層下沉到InnoDB引擎層，在索引層直接過濾 delete_flag=0，減少回表次數(shù)
• Rowid-ordered scan（MRR主鍵有序回表） ：將二級索引亂序主鍵ID排序，把隨機(jī)IO轉(zhuǎn)為順序IO，降低回表開銷

五、真實(shí)數(shù)據(jù)實(shí)測驗(yàn)證（推翻優(yōu)化器估算偏差）

通過真實(shí)計(jì)數(shù)SQL，驗(yàn)證索引掃描行數(shù)與有效數(shù)據(jù)行數(shù)的巨大差異，解釋優(yōu)化器誤判根源。

5.1 僅contract_code條件（索引全掃描行數(shù)）

SELECT COUNT(*) FROM contract_company_info 
WHERE contract_code IN ('ACCS20022962N', 'ACCS20024734W');

實(shí)測結(jié)果：694501 條（真實(shí)索引掃描總行數(shù)，優(yōu)化器估算40萬存在采樣偏差）

5.2 帶delete_flag有效條件（最終業(yè)務(wù)數(shù)據(jù)）

SELECT COUNT(*) FROM contract_company_info ?
WHERE delete_flag = 0
 ?AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

實(shí)測結(jié)果：31 條（最終有效業(yè)務(wù)數(shù)據(jù)）

六、優(yōu)化器執(zhí)行計(jì)劃決策與rows估算機(jī)制

6.1 優(yōu)化器為何默認(rèn)選擇全表掃描（type=ALL）

MySQL采用基于成本的優(yōu)化器（CBO, Cost-Based Optimizer），執(zhí)行計(jì)劃的選擇完全由成本估算結(jié)果決定，而非“索引一定比全表快”的固定規(guī)則。優(yōu)化器會分別計(jì)算不同執(zhí)行路徑的總成本，最終選擇成本最低的方案。

6.1.1 成本計(jì)算核心維度

• IO成本：將數(shù)據(jù)頁從磁盤讀取到內(nèi)存的開銷，是成本模型的核心權(quán)重項(xiàng)。InnoDB默認(rèn)配置下，隨機(jī)IO成本約為順序IO的4倍，回表產(chǎn)生的隨機(jī)讀成本遠(yuǎn)高于全表順序讀。
• CPU成本：內(nèi)存中數(shù)據(jù)過濾、排序、字段拼接的計(jì)算開銷，占比遠(yuǎn)低于IO成本。

6.1.2 兩種執(zhí)行路徑的成本對比

針對當(dāng)前查詢，優(yōu)化器會分別計(jì)算「走idx_contract_company索引」和「全表掃描」兩條路徑的總成本：

0. 走二級索引的預(yù)估成本：索引掃描成本：讀取contract_code對應(yīng)區(qū)間的索引頁，預(yù)估掃描約40萬條索引記錄；
1. 回表成本：優(yōu)化器基于全局統(tǒng)計(jì)信息，默認(rèn)delete_flag=0占絕大多數(shù)，預(yù)估絕大多數(shù)索引行都需要回表讀取聚簇索引完整數(shù)據(jù)，產(chǎn)生大量隨機(jī)IO；
2. 綜合判定：大范圍索引掃描+高頻隨機(jī)回表的總成本，高于全表順序掃描。
3. 全表掃描的預(yù)估成本：直接順序掃描聚簇索引全部數(shù)據(jù)頁，預(yù)估掃描約80萬行數(shù)據(jù)；
4. 純順序IO，且表數(shù)據(jù)大概率已常駐Buffer Pool內(nèi)存，內(nèi)存遍歷開銷極低；
5. 綜合判定：順序IO總成本低于索引+隨機(jī)回表方案。

6.1.3 決策偏差的核心原因：局部數(shù)據(jù)傾斜

優(yōu)化器的成本估算依賴全局統(tǒng)計(jì)信息，無法感知字段間的局部關(guān)聯(lián)分布，導(dǎo)致本次場景出現(xiàn)決策偏差：

• 全局視角：delete_flag默認(rèn)值為0，全表絕大多數(shù)數(shù)據(jù)為有效狀態(tài)，過濾比例極低，回表次數(shù)接近索引掃描行數(shù)；
• 局部視角：本次查詢的2個(gè)合同號下，99.9%的數(shù)據(jù)為delete_flag=1的已刪除數(shù)據(jù)，索引下推后僅31條需要回表，實(shí)際回表成本極低；
• 優(yōu)化器無法識別這種局部數(shù)據(jù)傾斜，最終錯(cuò)誤判定全表掃描成本更低。

6.2 EXPLAIN中rows值的估算原理

EXPLAIN輸出的rows字段，是優(yōu)化器基于統(tǒng)計(jì)信息估算的需要掃描的記錄條數(shù)，而非最終返回給客戶端的結(jié)果行數(shù)。其估算嚴(yán)格遵循最左前綴原則，僅由可用于索引區(qū)間裁剪的字段決定。

6.2.1 全表掃描場景的rows估算

當(dāng)執(zhí)行計(jì)劃為type=ALL時(shí)，rows值為表的預(yù)估總行數(shù)，來源于InnoDB的元數(shù)據(jù)統(tǒng)計(jì)信息：

• InnoDB采用采樣統(tǒng)計(jì)機(jī)制，通過抽取部分?jǐn)?shù)據(jù)頁估算全表行數(shù)，并非精確值；
• 本次場景全表rows=799303，與表的真實(shí)數(shù)據(jù)量基本一致，代表優(yōu)化器預(yù)估需要掃描全表所有行。

6.2.2 索引掃描場景的rows估算（關(guān)鍵）

當(dāng)執(zhí)行計(jì)劃走二級索引時(shí)，rows值僅由索引最左連續(xù)前綴字段的過濾性估算得出，非連續(xù)前綴的過濾條件不參與行數(shù)估算。

結(jié)合本次強(qiáng)制索引場景（idx_contract_company，key_len=259）：

0. 僅contract_code作為連續(xù)前綴參與索引區(qū)間定位，優(yōu)化器根據(jù)索引基數(shù)、等值條件的分布，估算出2個(gè)合同號對應(yīng)約404811條索引記錄；
1. delete_flag為索引第三列，中間跳過company_code，不屬于連續(xù)前綴，無法用于縮小索引掃描區(qū)間，因此不會影響rows的估算值；
2. 索引下推（ICP）僅在索引遍歷階段過濾數(shù)據(jù)，不會改變需要掃描的索引總行數(shù)，因此也不會反映在rows字段中。

6.2.3 估算值與真實(shí)值的偏差說明

本次強(qiáng)制索引場景下，優(yōu)化器估算rows=404811，而實(shí)測contract_code條件匹配的真實(shí)行數(shù)為694501，存在明顯偏差，原因在于：

• InnoDB的統(tǒng)計(jì)信息是采樣生成的，非全量精確統(tǒng)計(jì)，對于數(shù)據(jù)分布不均勻的字段，估算偏差會進(jìn)一步放大；
• 該偏差僅影響優(yōu)化器的成本決策，不影響實(shí)際執(zhí)行時(shí)的數(shù)據(jù)準(zhǔn)確性。

6.3 索引下推（ICP）的局限性

• 僅優(yōu)化回表次數(shù)，不減少索引掃描行數(shù)（仍需遍歷69萬條索引）
• 屬于「補(bǔ)救型優(yōu)化」，無法從根源減少掃描開銷

6.4 為什么EXPLAIN的rows只看索引前綴？

核心規(guī)則：EXPLAIN的rows是「索引掃描預(yù)估行數(shù)」，僅由可裁剪索引區(qū)間的連續(xù)前綴字段決定。

當(dāng)前索引 (contract_code,company_code,delete_flag)，查詢跳過中間 company_code，delete_flag 屬于非連續(xù)索引字段：

• 無法用于縮小索引掃描區(qū)間，不能減少rows預(yù)估值
• 僅能通過ICP在遍歷過程中過濾數(shù)據(jù)，不改變掃描總行數(shù)

6.5 優(yōu)化器默認(rèn)選錯(cuò)執(zhí)行計(jì)劃的根本原因

MySQL優(yōu)化器僅依賴全局統(tǒng)計(jì)信息，無法識別局部數(shù)據(jù)傾斜：

• 全局：delete_flag=0為默認(rèn)值，大部分?jǐn)?shù)據(jù)有效，過濾效果差
• 局部：本次2個(gè)合同號下，99.9%數(shù)據(jù)為已刪除狀態(tài)（delete_flag=1），過濾效果極強(qiáng)
• 優(yōu)化器感知不到局部傾斜，誤判回表成本過高，選擇全表掃描

七、全方案性能對比總結(jié)

八、最終核心結(jié)論

0. EXPLAIN的rows值僅由索引連續(xù)前綴字段估算，ICP過濾字段不影響掃描行數(shù)預(yù)估；
1. 索引下推（ICP）是減回表優(yōu)化，無法減少索引掃描量，性能上限低；
2. MySQL優(yōu)化器存在局部數(shù)據(jù)傾斜感知缺陷，會出現(xiàn)“索引效率更高但默認(rèn)選全表”的誤判；
3. 業(yè)務(wù)高頻查詢最優(yōu)解為定制覆蓋索引，徹底規(guī)避掃描和回表開銷，碾壓ICP優(yōu)化效果。

到此這篇關(guān)于MySQL索引執(zhí)行計(jì)劃不走索引下推的文章就介紹到這了,更多相關(guān)MySQL 不走索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論