MySQL 執(zhí)行計(jì)劃中 rows 數(shù)量的準(zhǔn)確性解析：原理、影響因素與優(yōu)化

在 MySQL SQL 調(diào)優(yōu)中，EXPLAIN執(zhí)行計(jì)劃是核心工具，而其中的rows列（估算的掃描行數(shù)）更是優(yōu)化器選擇執(zhí)行計(jì)劃的關(guān)鍵參考 —— 它直接影響優(yōu)化器對(duì) “全表掃描 vs 索引掃描”“join 表順序”“索引選擇” 的判斷。但實(shí)際使用中，開發(fā)者常困惑：rows值是精確值還是估算值？為什么有時(shí)和實(shí)際掃描行數(shù)相差懸殊？本文將從rows的計(jì)算原理出發(fā)，拆解準(zhǔn)確性的影響因素，提供判斷與優(yōu)化方法，幫助讀者正確利用rows進(jìn)行 SQL 調(diào)優(yōu)。

一、基礎(chǔ)認(rèn)知：執(zhí)行計(jì)劃中 rows 的本質(zhì) ——“估算值” 而非 “精確值”

首先需明確一個(gè)核心前提：MySQL 執(zhí)行計(jì)劃中的rows列，不是實(shí)際執(zhí)行 SQL 時(shí)掃描的行數(shù)，而是優(yōu)化器基于 “統(tǒng)計(jì)信息” 估算的 “需要掃描的行數(shù)”。其核心作用是為優(yōu)化器提供 “成本評(píng)估依據(jù)”—— 優(yōu)化器通過rows估算 “IO 成本”（讀取數(shù)據(jù)頁的數(shù)量）和 “CPU 成本”（處理數(shù)據(jù)的耗時(shí)），最終選擇成本最低的執(zhí)行計(jì)劃。

1. rows 的計(jì)算邏輯：依賴 “統(tǒng)計(jì)信息”

MySQL 優(yōu)化器無法實(shí)時(shí)遍歷表或索引獲取精確行數(shù)（否則會(huì)消耗大量資源，違背 “執(zhí)行計(jì)劃快速生成” 的初衷），而是依賴存儲(chǔ)引擎（如 InnoDB、MyISAM）維護(hù)的 “統(tǒng)計(jì)信息” 進(jìn)行估算，核心統(tǒng)計(jì)信息包括：

• 表級(jí)統(tǒng)計(jì)：表的總行數(shù)（TABLE_ROWS，可通過INFORMATION_SCHEMA.TABLES查詢）、數(shù)據(jù)頁數(shù)量、平均行長度；

• 索引級(jí)統(tǒng)計(jì)：索引的基數(shù)（CARDINALITY，索引列不重復(fù)值的數(shù)量，可通過SHOW INDEX FROM 表名查詢）、索引樹的深度、索引頁數(shù)量；

• 列值分布統(tǒng)計(jì)：列值的直方圖（MySQL 8.0 引入，記錄列值的分布區(qū)間及每個(gè)區(qū)間的行數(shù)，用于優(yōu)化范圍查詢的估算）。

例如，對(duì)于單表等值查詢SELECT * FROM user WHERE age = 30，優(yōu)化器的估算邏輯為：

rows ≈ 表總行數(shù)（TABLE_ROWS） / 索引列age的基數(shù)（CARDINALITY）

若表總行數(shù) 10000，age 的基數(shù) 100（即 age 有 100 個(gè)不同值，平均每個(gè)值對(duì)應(yīng) 100 行），則rows估算為 100。

2. rows 準(zhǔn)確性的 “價(jià)值邊界”

不必追求rows與實(shí)際掃描行數(shù)完全一致 —— 優(yōu)化器只需rows在 “合理誤差范圍”（通常認(rèn)為 10 倍以內(nèi)），就能正確選擇執(zhí)行計(jì)劃。例如：

• 若實(shí)際掃描行數(shù) 100，rows估算為 80 或 120，優(yōu)化器仍會(huì)選擇正確的索引；

• 若rows估算為 1000（實(shí)際 100），可能導(dǎo)致優(yōu)化器誤判 “索引掃描成本高”，轉(zhuǎn)而選擇全表掃描，此時(shí)才需關(guān)注準(zhǔn)確性問題。

二、影響 rows 準(zhǔn)確性的核心因素：為什么有時(shí)準(zhǔn)，有時(shí)不準(zhǔn)？

rows的準(zhǔn)確性由 “統(tǒng)計(jì)信息質(zhì)量”“查詢復(fù)雜度”“存儲(chǔ)引擎特性” 三大維度決定，不同場景下準(zhǔn)確性差異顯著。

1. 哪些情況 rows 估算較準(zhǔn)確？

當(dāng)統(tǒng)計(jì)信息新鮮、查詢邏輯簡單、數(shù)據(jù)分布均勻時(shí)，rows估算值與實(shí)際值偏差通常小于 20%，典型場景包括：

（1）統(tǒng)計(jì)信息 “新鮮且完整”

• 剛執(zhí)行過ANALYZE TABLE 表名（手動(dòng)更新統(tǒng)計(jì)信息），或 MySQL 自動(dòng)觸發(fā)統(tǒng)計(jì)信息更新（如 InnoDB 在數(shù)據(jù)修改量超過 10% 時(shí)自動(dòng)更新）；

• 表數(shù)據(jù)量?。ㄈ?< 1 萬行），統(tǒng)計(jì)信息采樣率足夠高（小表默認(rèn)全量采樣，無抽樣誤差）。

示例：

對(duì) 1000 行的user表執(zhí)行ANALYZE TABLE user后，執(zhí)行EXPLAIN SELECT * FROM user WHERE id = 10（id為主鍵，基數(shù) 1000）：

• 優(yōu)化器估算rows = 1（主鍵唯一，每個(gè)值對(duì)應(yīng) 1 行），實(shí)際掃描行數(shù)也為 1，偏差 0%。

（2）簡單查詢 + 高選擇性索引

• 單表查詢，使用主鍵、唯一索引或高選擇性普通索引（索引列重復(fù)值少，基數(shù)接近表行數(shù)）；

• 查詢條件為等值查詢（=）或極小范圍查詢（BETWEEN 1 AND 5），數(shù)據(jù)分布均勻。

示例：

user表有 10 萬行，phone列唯一索引（基數(shù) 10 萬），執(zhí)行EXPLAIN SELECT * FROM user WHERE phone = '13800138000'：

• 優(yōu)化器估算rows = 1，實(shí)際掃描行數(shù) 1，偏差 0%；

• 若phone列非唯一（基數(shù) 5 萬，平均每個(gè)值對(duì)應(yīng) 2 行），執(zhí)行EXPLAIN SELECT * FROM user WHERE phone = '13800138000'，估算rows = 2，實(shí)際掃描行數(shù)通常為 1-3 行，偏差 < 50%。

（3）MySQL 8.0 + 的直方圖優(yōu)化

MySQL 8.0 引入 “列值直方圖”，針對(duì)數(shù)據(jù)分布不均勻的列（如電商訂單表的amount列，多數(shù)訂單集中在 100-500 元，少數(shù)大額訂單 > 10000 元），能更精準(zhǔn)估算范圍查詢的rows值。

示例：

order表amount列有直方圖，執(zhí)行EXPLAIN SELECT * FROM order WHERE amount BETWEEN 200 AND 300：

• 無直方圖時(shí)，優(yōu)化器可能按 “平均分布” 估算（如總行數(shù) 10 萬，amount基數(shù) 1000，估算rows = 100）；

• 有直方圖時(shí)，優(yōu)化器能識(shí)別 “200-300 元區(qū)間占比 30%”，估算rows = 30000，與實(shí)際行數(shù)偏差 < 10%。

2. 哪些情況 rows 估算容易不準(zhǔn)？

當(dāng)統(tǒng)計(jì)信息過期、查詢復(fù)雜、數(shù)據(jù)分布極端時(shí)，rows偏差可能超過 10 倍甚至 100 倍，典型場景包括：

（1）統(tǒng)計(jì)信息 “過期或采樣不足”

• 表數(shù)據(jù)頻繁增刪改（如每小時(shí)新增 1 萬行），但未觸發(fā)統(tǒng)計(jì)信息更新（InnoDB 默認(rèn)修改量超 10% 才更新，大表可能延遲）；

• 大表（如 1000 萬行以上）使用默認(rèn)采樣率（InnoDB 持久化統(tǒng)計(jì)信息默認(rèn)采樣innodb_stats_persistent_sample_pages = 20），采樣誤差導(dǎo)致基數(shù)估算偏差。

示例：

1000 萬行的log表，create_time列普通索引，默認(rèn)采樣 20 個(gè)數(shù)據(jù)頁：

• 實(shí)際create_time的基數(shù)為 100 萬（每天新增約 3 萬行，共 300 天數(shù)據(jù)），但采樣時(shí)恰好命中 “某幾天的重復(fù)數(shù)據(jù)”，導(dǎo)致優(yōu)化器估算基數(shù)為 10 萬；

• 執(zhí)行EXPLAIN SELECT * FROM log WHERE create_time BETWEEN '2024-01-01' AND '2024-01-02'，實(shí)際掃描行數(shù) 3 萬，估算rows = 30萬（偏差 10 倍）。

（2）復(fù)雜查詢邏輯

多表 join、子查詢、復(fù)雜條件（OR、NOT IN、函數(shù)操作）會(huì)增加優(yōu)化器的估算難度，導(dǎo)致rows偏差放大：

• 多表 join：優(yōu)化器需估算 “驅(qū)動(dòng)表與被驅(qū)動(dòng)表的匹配行數(shù)”，若其中一個(gè)表的rows估算不準(zhǔn)，會(huì)連鎖影響整體 join 行數(shù)的估算；

• 子查詢：尤其是IN (子查詢)或EXISTS (子查詢)，優(yōu)化器可能簡化子查詢的估算邏輯，導(dǎo)致外層查詢rows偏差；

• 函數(shù)操作：如WHERE DATE(create_time) = '2024-01-01'（索引失效，優(yōu)化器只能按全表掃描估算，rows接近表總行數(shù)，與實(shí)際掃描行數(shù)偏差大）。

示例：

3 表 join 查詢EXPLAIN SELECT * FROM a JOIN b ON ``a.id`` = b.a_id JOIN c ON ``b.id`` = c.b_id WHERE a.status = 1：

• 若a表status=1的實(shí)際行數(shù) 100，但優(yōu)化器估算rows=1000（統(tǒng)計(jì)信息過期），則b表和c表的rows估算會(huì)基于 1000 行驅(qū)動(dòng)，最終整體rows偏差可能達(dá) 10 倍以上。

（3）低選擇性索引或極端數(shù)據(jù)分布

• 低選擇性索引：索引列重復(fù)值多（如gender列，只有 “男 / 女” 兩個(gè)值，基數(shù) = 2），優(yōu)化器按 “表總行數(shù) / 基數(shù)” 估算rows（如 10 萬行表，估算rows=5萬），但實(shí)際某一性別可能占 80%（8 萬行），偏差 60%；

• 極端數(shù)據(jù)分布：如user表age列，90% 的行集中在 18-30 歲，10% 在 30 歲以上，執(zhí)行EXPLAIN SELECT * FROM user WHERE age > 30，優(yōu)化器按 “平均分布” 估算rows=1萬（10 萬 ×10%），但實(shí)際可能因采樣誤差估算為 5 萬（偏差 5 倍）。

（4）存儲(chǔ)引擎特性差異

• MyISAM：統(tǒng)計(jì)信息存儲(chǔ)在內(nèi)存中，表關(guān)閉后會(huì)丟失，重啟 MySQL 后需重新計(jì)算（可能導(dǎo)致臨時(shí)估算偏差）；

• InnoDB：早期版本（<5.6）不支持持久化統(tǒng)計(jì)信息，重啟后統(tǒng)計(jì)信息重置，大表估算偏差顯著；MySQL 5.6 + 支持持久化統(tǒng)計(jì)信息（innodb_stats_persistent = ON），但默認(rèn)采樣率仍可能不足。

三、如何判斷 rows 估算的準(zhǔn)確性？

判斷rows是否 “足夠準(zhǔn)確”，核心是對(duì)比 “執(zhí)行計(jì)劃的rows” 與 “實(shí)際掃描行數(shù)”，常用方法有 3 種：

1. 方法 1：用SHOW PROFILE查看實(shí)際掃描行數(shù)

SHOW PROFILE可查看 SQL 執(zhí)行的詳細(xì)步驟，包括 “實(shí)際掃描的行數(shù)”（Rows_examined）：

-- 1. 開啟profiling

SET profiling = 1;

-- 2. 執(zhí)行目標(biāo)SQL

SELECT * FROM user WHERE age BETWEEN 20 AND 30;

-- 3. 查看profile結(jié)果

SHOW PROFILE FOR QUERY 1;  -- Query 1為SQL的編號(hào)，可通過SHOW PROFILES查看

-- 關(guān)鍵輸出：Rows_examined: 1200（實(shí)際掃描行數(shù)）

-- 對(duì)比執(zhí)行計(jì)劃的rows：若EXPLAIN中rows=1000，偏差20%，屬于可接受范圍；若rows=5000，偏差4倍，需優(yōu)化

2. 方法 2：用EXPLAIN ANALYZE（MySQL 8.0.18+）直接對(duì)比

MySQL 8.0.18 引入EXPLAIN ANALYZE，會(huì)實(shí)際執(zhí)行 SQL（但不返回結(jié)果集），同時(shí)輸出 “估算 rows” 與 “實(shí)際 rows”，是最直觀的判斷方法：

EXPLAIN ANALYZE

SELECT * FROM user WHERE age BETWEEN 20 AND 30;

-- 典型輸出（關(guān)鍵部分）：

-- -> Index Range Scan on user using idx_age over (age between 20 and 30)

--    (cost=120.00 rows=1000) (actual time=0.022..0.150 rows=1200 loops=1)

-- 解讀：估算rows=1000，實(shí)際rows=1200，偏差20%，準(zhǔn)確性可接受

3. 方法 3：小數(shù)據(jù)量手動(dòng)對(duì)比

對(duì)于數(shù)據(jù)量較小的表（如 < 10 萬行），可直接執(zhí)行 SQL 并計(jì)數(shù)，與EXPLAIN的rows對(duì)比：

-- 1. 查看執(zhí)行計(jì)劃的rows

EXPLAIN SELECT * FROM user WHERE age BETWEEN 20 AND 30;  -- 假設(shè)rows=1000

-- 2. 手動(dòng)計(jì)數(shù)實(shí)際行數(shù)

SELECT COUNT(*) FROM user WHERE age BETWEEN 20 AND 30;  -- 假設(shè)結(jié)果=1200

-- 對(duì)比：偏差20%，可接受；若計(jì)數(shù)=5000，偏差5倍，需優(yōu)化

四、優(yōu)化 rows 估算準(zhǔn)確性的實(shí)用策略

當(dāng)rows偏差過大（如超過 10 倍），導(dǎo)致優(yōu)化器選擇錯(cuò)誤執(zhí)行計(jì)劃（如該用索引卻全表掃描）時(shí)，可通過以下策略優(yōu)化：

1. 策略 1：更新統(tǒng)計(jì)信息 —— 最直接的方法

通過ANALYZE TABLE手動(dòng)更新表的統(tǒng)計(jì)信息，適用于統(tǒng)計(jì)信息過期的場景：

-- 1. 基礎(chǔ)更新：更新指定表的統(tǒng)計(jì)信息

ANALYZE TABLE user, order;

-- 2. 進(jìn)階：InnoDB強(qiáng)制全量采樣（大表慎用，可能耗時(shí)）

-- 臨時(shí)設(shè)置采樣頁數(shù)量為表的總數(shù)據(jù)頁數(shù)（需先查詢總頁數(shù)）

SELECT CEIL(data_length / @@innodb_page_size) AS total_pages FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user';

SET innodb_stats_persistent_sample_pages = 1000;  -- 假設(shè)總頁數(shù)1000

ANALYZE TABLE user;

SET innodb_stats_persistent_sample_pages = 20;  -- 恢復(fù)默認(rèn)值，避免后續(xù)性能影響

注意：ANALYZE TABLE會(huì)對(duì)表加 “讀鎖”（InnoDB 在 MySQL 8.0 中已優(yōu)化為輕量級(jí)鎖，不阻塞 DML），大表（如 1000 萬行以上）建議在業(yè)務(wù)低峰期執(zhí)行。

2. 策略 2：調(diào)整統(tǒng)計(jì)信息采樣率 —— 針對(duì)大表

InnoDB 的統(tǒng)計(jì)信息采樣率由以下參數(shù)控制，可根據(jù)表大小調(diào)整：

• innodb_stats_persistent_sample_pages：持久化統(tǒng)計(jì)信息的采樣頁數(shù)量（默認(rèn) 20），大表可增大至 100-1000，提升基數(shù)估算準(zhǔn)確性；

• innodb_stats_transient_sample_pages：臨時(shí)統(tǒng)計(jì)信息的采樣頁數(shù)量（默認(rèn) 8），若禁用持久化統(tǒng)計(jì)信息（innodb_stats_persistent = OFF），需調(diào)整此參數(shù)。

示例：

對(duì) 1000 萬行的log表，永久調(diào)整采樣頁數(shù)量：

-- 1. 全局調(diào)整（需重啟MySQL生效）

SET GLOBAL innodb_stats_persistent_sample_pages = 200;

-- 修改配置文件my.cnf，避免重啟失效

innodb_stats_persistent_sample_pages = 200

-- 2. 僅對(duì)指定表調(diào)整（MySQL 8.0+支持）

ALTER TABLE log SET STATISTICS_SAMPLE_PAGES = 200;

3. 策略 3：優(yōu)化查詢語句 —— 降低估算復(fù)雜度

復(fù)雜查詢是rows偏差的主要誘因，可通過簡化查詢邏輯提升準(zhǔn)確性：

• 避免函數(shù)操作索引列：如將DATE(create_time) = '2024-01-01'改為create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'，利用索引精準(zhǔn)估算；

• 拆分復(fù)雜 join：將 3 表以上 join 拆分為 “子查詢 + 關(guān)聯(lián)”，或用STRAIGHT_JOIN強(qiáng)制指定 join 順序（減少優(yōu)化器的估算誤差）；

• 替換低選擇性索引：如gender列不適合建索引，直接用全表掃描，避免優(yōu)化器基于低選擇性索引做錯(cuò)誤估算。

4. 策略 4：使用直方圖優(yōu)化數(shù)據(jù)分布估算

MySQL 8.0 + 支持為列創(chuàng)建直方圖，針對(duì)數(shù)據(jù)分布不均勻的列（如amount、create_time），能顯著提升范圍查詢的rows準(zhǔn)確性：

-- 1. 為order表的amount列創(chuàng)建直方圖

ANALYZE TABLE order UPDATE HISTOGRAM ON amount;

-- 2. 查看直方圖信息

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE TABLE_NAME = 'order' AND COLUMN_NAME = 'amount';

-- 3. 執(zhí)行范圍查詢，查看rows估算

EXPLAIN ANALYZE SELECT * FROM order WHERE amount BETWEEN 200 AND 300;

-- 此時(shí)估算rows與實(shí)際rows偏差通常<10%

5. 策略 5：升級(jí) MySQL 版本 —— 享受原生優(yōu)化

MySQL 新版本對(duì)統(tǒng)計(jì)信息和估算算法持續(xù)優(yōu)化：

• MySQL 8.0：引入直方圖、動(dòng)態(tài)采樣率、更精準(zhǔn)的 join 行數(shù)估算；

• MySQL 5.7：優(yōu)化 InnoDB 持久化統(tǒng)計(jì)信息，減少重啟后的偏差；

• 若使用 MySQL 5.6 及以下版本，升級(jí)到 8.0 可顯著提升rows估算準(zhǔn)確性。

五、總結(jié)：理性看待 rows 準(zhǔn)確性，聚焦 “優(yōu)化器選擇” 而非 “絕對(duì)精確”

MySQL 執(zhí)行計(jì)劃中的rows是 “估算值”，其核心價(jià)值是幫助優(yōu)化器選擇 “成本最低的執(zhí)行計(jì)劃”，而非提供 “精確的掃描行數(shù)”。實(shí)際調(diào)優(yōu)中，需把握以下原則：

1. 可接受偏差范圍：若rows與實(shí)際行數(shù)偏差 < 10 倍，且優(yōu)化器選擇了正確的執(zhí)行計(jì)劃（如用索引而非全表掃描），無需過度優(yōu)化；

2. 優(yōu)先解決 “嚴(yán)重偏差”：僅當(dāng)rows偏差導(dǎo)致優(yōu)化器選擇錯(cuò)誤執(zhí)行計(jì)劃（如該用主鍵索引卻全表掃描）時(shí)，才需通過更新統(tǒng)計(jì)信息、調(diào)整采樣率等方式優(yōu)化；

3. 結(jié)合其他指標(biāo)判斷：rows需與執(zhí)行計(jì)劃的type（訪問類型，如ref、range、ALL）、key（使用的索引）、Extra（額外信息，如Using index）結(jié)合，綜合評(píng)估 SQL 性能，而非單一依賴rows。

最終，掌握rows的估算原理與優(yōu)化方法，能讓開發(fā)者更高效地利用執(zhí)行計(jì)劃定位 SQL 性能瓶頸，實(shí)現(xiàn) “精準(zhǔn)調(diào)優(yōu)” 而非 “盲目優(yōu)化”。

推薦學(xué)習(xí)書籍 《CDA一級(jí)教材》適合CDA一級(jí)考生備考，也適合業(yè)務(wù)及數(shù)據(jù)分析崗位的從業(yè)者提升自我。完整電子版已上線CDA網(wǎng)校，累計(jì)已有10萬+在讀~ !

免費(fèi)加入閱讀：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0