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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

示例：

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

• 優(yōu)化器估算rows = 1（主鍵唯一，每個值對應(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 萬，平均每個值對應(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 引入 “列值直方圖”，針對數(shù)據(jù)分布不均勻的列（如電商訂單表的amount列，多數(shù)訂單集中在 100-500 元，少數(shù)大額訂單 > 10000 元），能更精準(zhǔn)估算范圍查詢的rows值。

示例：

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

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

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

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

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

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

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

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

示例：

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

• 實(shí)際create_time的基數(shù)為 100 萬（每天新增約 3 萬行，共 300 天數(shù)據(jù)），但采樣時恰好命中 “某幾天的重復(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ù)操作）會增加優(yōu)化器的估算難度，導(dǎo)致rows偏差放大：

• 多表 join：優(yōu)化器需估算 “驅(qū)動表與被驅(qū)動表的匹配行數(shù)”，若其中一個表的rows估算不準(zhǔn)，會連鎖影響整體 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)計信息過期），則b表和c表的rows估算會基于 1000 行驅(qū)動，最終整體rows偏差可能達(dá) 10 倍以上。

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

• 低選擇性索引：索引列重復(fù)值多（如gender列，只有 “男 / 女” 兩個值，基數(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）存儲引擎特性差異

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

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

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

判斷rows是否 “足夠準(zhǔn)確”，核心是對比 “執(zhí)行計劃的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的編號，可通過SHOW PROFILES查看

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

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

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

MySQL 8.0.18 引入EXPLAIN ANALYZE，會實(shí)際執(zhí)行 SQL（但不返回結(jié)果集），同時輸出 “估算 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ù)量手動對比

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

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

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

-- 2. 手動計數(shù)實(shí)際行數(shù)

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

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

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

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

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

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

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

ANALYZE TABLE user, order;

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

-- 臨時設(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會對表加 “讀鎖”（InnoDB 在 MySQL 8.0 中已優(yōu)化為輕量級鎖，不阻塞 DML），大表（如 1000 萬行以上）建議在業(yè)務(wù)低峰期執(zhí)行。

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

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

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

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

示例：

對 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. 僅對指定表調(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)化器基于低選擇性索引做錯誤估算。

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

MySQL 8.0 + 支持為列創(chuàng)建直方圖，針對數(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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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