MySQL執(zhí)行計(jì)劃中rows的計(jì)算邏輯：從原理到實(shí)踐

MySQL 執(zhí)行計(jì)劃中 rows 的計(jì)算邏輯：從原理到實(shí)踐

在 MySQL 數(shù)據(jù)庫的查詢優(yōu)化中，執(zhí)行計(jì)劃（EXPLAIN輸出）是開發(fā)者和 DBA 分析查詢性能的核心工具。其中，rows列作為執(zhí)行計(jì)劃的關(guān)鍵指標(biāo)，代表優(yōu)化器估計(jì)的當(dāng)前操作需要掃描的行數(shù)。這個(gè)數(shù)值直接反映了查詢的效率 —— 行數(shù)越少，查詢通常越高效。但很多人對這個(gè)數(shù)值的計(jì)算邏輯感到困惑：它究竟是怎么來的？為何有時(shí)會與實(shí)際掃描行數(shù)相差較大？本文將從原理到實(shí)踐，全面解析 MySQL 執(zhí)行計(jì)劃中rows的計(jì)算邏輯。

一、rows 的核心作用：優(yōu)化器的 “決策依據(jù)”

在深入計(jì)算邏輯前，需先明確rows的本質(zhì)：它是 MySQL 優(yōu)化器基于統(tǒng)計(jì)信息和數(shù)據(jù)分布規(guī)律，對查詢執(zhí)行過程中需要訪問的行數(shù)的 “預(yù)測值”，而非實(shí)際執(zhí)行時(shí)的真實(shí)掃描行數(shù)。

優(yōu)化器的核心任務(wù)是選擇最優(yōu)執(zhí)行計(jì)劃（如全表掃描還是索引掃描、多表連接的順序等），而rows值是優(yōu)化器判斷成本的重要依據(jù)。例如，對于一個(gè)簡單的過濾查詢，優(yōu)化器會比較 “全表掃描估計(jì)的 rows” 和 “索引掃描估計(jì)的 rows”，選擇成本更低的方案。因此，rows的準(zhǔn)確性直接影響優(yōu)化器決策的合理性。

二、rows 計(jì)算的核心依據(jù)：統(tǒng)計(jì)信息

MySQL 優(yōu)化器之所以能 “估計(jì)” 行數(shù)，依賴于數(shù)據(jù)庫收集的統(tǒng)計(jì)信息（statistics）。這些信息存儲在系統(tǒng)表中（如mysql.innodb_index_stats、mysql.innodb_table_stats），記錄了表、索引、列的數(shù)據(jù)分布特征。統(tǒng)計(jì)信息是rows計(jì)算的 “原始數(shù)據(jù)”，主要包括以下關(guān)鍵指標(biāo)：

1. 表級統(tǒng)計(jì)信息

• 總行數(shù)（table_rows）：表中記錄的估計(jì)總數(shù)量。對于 InnoDB 表，這個(gè)值并非實(shí)時(shí)精確值，而是通過采樣計(jì)算的近似值（具體采樣邏輯后文詳述）。

• 數(shù)據(jù)長度（data_length）：表數(shù)據(jù)占用的存儲空間，用于輔助判斷表的 “大小”。

2. 索引級統(tǒng)計(jì)信息

• 基數(shù)（Cardinality）：索引列中不重復(fù)值的估計(jì)數(shù)量。例如，一個(gè)性別列（值為 “男”“女”）的基數(shù)約為 2，而一個(gè)用戶 ID 列的基數(shù)接近表的總行數(shù)。基數(shù)直接影響索引的 “選擇性”（Selectivity，即不重復(fù)值占總行數(shù)的比例），選擇性越高（基數(shù)越大），索引過濾效果越好。

• 索引深度（index_depth）：索引 B + 樹的高度，影響索引掃描的 IO 成本。

3. 列級統(tǒng)計(jì)信息

• 值分布（value distribution）：部分列的直方圖（Histogram）統(tǒng)計(jì)，記錄列值的分布范圍和頻率。例如，一個(gè)訂單金額列的直方圖可能顯示 “0-100 元占 60%，100-500 元占 30%” 等分布特征，幫助優(yōu)化器更精準(zhǔn)地估計(jì)過濾條件后的行數(shù)。

三、不同操作場景下 rows 的具體計(jì)算邏輯

rows的計(jì)算方式并非一成不變，而是根據(jù)查詢操作的類型（如全表掃描、索引掃描、連接查詢）動態(tài)調(diào)整。以下是常見場景的計(jì)算邏輯：

1. 全表掃描（type: ALL）

當(dāng)查詢無法使用索引（如無合適索引、條件過于寬泛）時(shí)，優(yōu)化器會選擇全表掃描，此時(shí)rows的估計(jì)值主要基于表級統(tǒng)計(jì)信息中的總行數(shù)（table_rows）。

例如，對于一張user表，table_rows統(tǒng)計(jì)值為 10000，執(zhí)行EXPLAIN SELECT * FROM user時(shí)，rows列通常會接近 10000。但需注意：table_rows是 InnoDB 通過采樣計(jì)算的近似值（默認(rèn)采樣 8 個(gè)數(shù)據(jù)頁），若表數(shù)據(jù)分布不均或采樣偏差，rows可能與實(shí)際總行數(shù)存在差異。

2. 索引掃描（type: ref、range、index 等）

當(dāng)查詢使用索引時(shí)，rows的計(jì)算依賴于索引的基數(shù)、選擇性及查詢條件，核心公式可簡化為：

估計(jì)掃描行數(shù) = 索引過濾后的基數(shù) × 數(shù)據(jù)分布系數(shù)  

具體場景如下：

• 等值查詢（ref 類型）：如WHERE age = 30，優(yōu)化器會先通過索引統(tǒng)計(jì)獲取age列的基數(shù)，計(jì)算 “值 = 30” 的選擇性（即該值在列中的占比），再用表總行數(shù)乘以選擇性，得到估計(jì)行數(shù)。 例：

  例：user表總行數(shù) 10000，age列基數(shù)為 50（假設(shè)年齡分布在 18-67 歲，共 50 個(gè)可能值），則 “age=30” 的選擇性約為 1/50，估計(jì)行數(shù)為 10000 × (1/50) = 200。

• 范圍查詢（range 類型）：如WHERE price BETWEEN 100 AND 500，優(yōu)化器會結(jié)合索引的直方圖統(tǒng)計(jì)（若啟用）或索引值的分布范圍，估算范圍內(nèi)的值占總基數(shù)的比例，再乘以總行數(shù)。 例：

  例：product表總行數(shù) 50000，price列索引基數(shù)為 10000，若統(tǒng)計(jì)顯示 “100-500 元” 的價(jià)格占比約 20%，則估計(jì)行數(shù)為 50000 × 20% = 10000。

• 索引全掃描（index 類型）：當(dāng)查詢需要掃描整個(gè)索引（如SELECT COUNT(*) FROM user USE INDEX(age_idx)），rows值接近索引的總記錄數(shù)，該值基于索引的統(tǒng)計(jì)信息（如innodb_index_stats中的sum_of_rows）。

3. 連接查詢（JOIN）

多表連接時(shí)，rows的計(jì)算更為復(fù)雜，優(yōu)化器需估計(jì)每一步連接的 “驅(qū)動表” 和 “被驅(qū)動表” 的掃描行數(shù)，核心邏輯基于嵌套循環(huán)連接的成本模型：

總估計(jì)行數(shù) = 驅(qū)動表估計(jì)行數(shù) × 被驅(qū)動表單條驅(qū)動記錄的匹配行數(shù)  

例如，SELECT * FROM order o JOIN user u ON o.user_id = ``u.id，優(yōu)化器會先確定驅(qū)動表（如order表，估計(jì)行數(shù) 1000），再根據(jù)user表中id索引的基數(shù)，計(jì)算每條order記錄匹配的user記錄數(shù)（假設(shè)id是主鍵，基數(shù) = 總行數(shù)，匹配行數(shù) = 1），則總rows估計(jì)為 1000 × 1 = 1000。

四、影響 rows 估計(jì)準(zhǔn)確性的關(guān)鍵因素

實(shí)際使用中，rows估計(jì)值與真實(shí)值的偏差往往源于以下因素，需重點(diǎn)關(guān)注：

1. 統(tǒng)計(jì)信息過時(shí)或缺失

MySQL 的統(tǒng)計(jì)信息默認(rèn)通過采樣更新（InnoDB 默認(rèn)每 10% 數(shù)據(jù)變更觸發(fā)自動更新，或通過ANALYZE TABLE手動更新）。若表數(shù)據(jù)頻繁插入、刪除或更新，統(tǒng)計(jì)信息未及時(shí)更新，會導(dǎo)致table_rows、基數(shù)等指標(biāo)失真，進(jìn)而影響rows估計(jì)。

例如，一張表剛插入 10 萬條新數(shù)據(jù)但未更新統(tǒng)計(jì)信息，優(yōu)化器仍使用舊的table_rows（如 5 萬），此時(shí)rows估計(jì)會遠(yuǎn)小于實(shí)際值。

2. 數(shù)據(jù)分布極端不均

當(dāng)列數(shù)據(jù)存在 “長尾分布”（如某值占比 90%，其他值共占 10%），采樣統(tǒng)計(jì)可能無法準(zhǔn)確捕捉分布特征。例如，status列中 90% 的值為 “normal”，10% 為 “error”，優(yōu)化器基于采樣可能誤判 “status='normal'” 的選擇性為 50%，導(dǎo)致rows估計(jì)偏差。

3. 索引失效或使用不當(dāng)

若索引存在重復(fù)值過多（選擇性低）、索引碎片嚴(yán)重等問題，優(yōu)化器可能低估或高估索引掃描的行數(shù)。例如，對一個(gè)選擇性極低的索引（如性別列）執(zhí)行查詢，優(yōu)化器可能認(rèn)為 “索引掃描 rows” 與 “全表掃描 rows” 接近，進(jìn)而選擇全表掃描，導(dǎo)致執(zhí)行計(jì)劃不符合預(yù)期。

4. 復(fù)雜查詢的估計(jì)誤差累積

對于包含子查詢、聚合函數(shù)（GROUP BY）、排序（ORDER BY）的復(fù)雜查詢，優(yōu)化器需對多個(gè)步驟的rows進(jìn)行估計(jì)，誤差會逐步累積，導(dǎo)致最終rows值與實(shí)際偏差較大。

五、優(yōu)化 rows 估計(jì)準(zhǔn)確性的實(shí)踐方法

要讓rows值更接近實(shí)際掃描行數(shù)，需從統(tǒng)計(jì)信息維護(hù)、數(shù)據(jù)設(shè)計(jì)和查詢優(yōu)化三方面入手：

1. 及時(shí)更新統(tǒng)計(jì)信息

• 對頻繁變更的表定期執(zhí)行ANALYZE TABLE table_name，強(qiáng)制更新統(tǒng)計(jì)信息（InnoDB 會重新采樣計(jì)算table_rows、基數(shù)等）。

• 調(diào)整統(tǒng)計(jì)信息采樣參數(shù)：通過innodb_stats_persistent_sample_pages（持久化統(tǒng)計(jì)采樣頁數(shù)量，默認(rèn) 20）或innodb_stats_transient_sample_pages（臨時(shí)統(tǒng)計(jì)采樣頁數(shù)量，默認(rèn) 8）提高采樣精度（需權(quán)衡性能開銷）。

2. 優(yōu)化數(shù)據(jù)分布與索引設(shè)計(jì)

• 避免在低選擇性列上創(chuàng)建索引（如性別、狀態(tài)等），此類索引對rows估計(jì)和查詢效率提升有限。

• 對高選擇性列（如用戶 ID、訂單號）建立索引，并通過SHOW INDEX FROM table_name定期檢查索引基數(shù)是否合理。

3. 結(jié)合實(shí)際執(zhí)行計(jì)劃調(diào)優(yōu)查詢

• 若rows估計(jì)值與實(shí)際掃描行數(shù)偏差較大（可通過SHOW PROFILE或慢查詢?nèi)罩静榭磳?shí)際行數(shù)），嘗試改寫查詢（如調(diào)整過濾條件順序、避免子查詢嵌套過深）。

• 對復(fù)雜連接查詢，通過STRAIGHT_JOIN指定連接順序，避免優(yōu)化器因rows估計(jì)錯(cuò)誤選擇低效連接方式。

六、案例：從 rows 異?？?a href='/map/chaxunyouhua/' style='color:#000;font-size:inherit;'>查詢優(yōu)化

某電商平臺的訂單查詢 SQL 執(zhí)行緩慢，EXPLAIN輸出顯示rows估計(jì)為 1000，但實(shí)際掃描行數(shù)達(dá) 10 萬。排查發(fā)現(xiàn)：

1. 訂單表order近期新增 50 萬條數(shù)據(jù)，但未執(zhí)行ANALYZE TABLE，table_rows仍為舊值 10 萬，導(dǎo)致優(yōu)化器低估總行數(shù)；

2. 過濾條件WHERE create_time > '2023-01-01'使用的create_time索引存在大量碎片，基數(shù)統(tǒng)計(jì)失真，優(yōu)化器誤判范圍查詢的選擇性為 1%（實(shí)際為 20%）。

解決方法：

• 執(zhí)行ANALYZE TABLE order更新統(tǒng)計(jì)信息，table_rows修正為 60 萬；

• 重建create_time索引（ALTER TABLE order REBUILD INDEX create_time_idx），修復(fù)碎片并更新基數(shù)；

• 優(yōu)化后，rows估計(jì)值為 12000（60 萬 × 20%），與實(shí)際掃描行數(shù)（11 萬）接近，優(yōu)化器選擇了更合理的索引掃描計(jì)劃，查詢性能提升 80%。

結(jié)語

MySQL 執(zhí)行計(jì)劃中的rows值是優(yōu)化器基于統(tǒng)計(jì)信息和數(shù)據(jù)分布的 “智能預(yù)測”，其計(jì)算邏輯貫穿查詢執(zhí)行的全流程。理解rows的來源、影響因素及優(yōu)化方法，不僅能幫助開發(fā)者快速定位查詢性能瓶頸，更能深入掌握 MySQL 優(yōu)化器的工作原理。在實(shí)際工作中，需結(jié)合EXPLAIN輸出、統(tǒng)計(jì)信息維護(hù)和數(shù)據(jù)特征分析，讓rows成為查詢優(yōu)化的 “指路明燈”，而非誤導(dǎo)決策的 “迷霧”。

學(xué)習(xí)入口：https://edu.cda.cn/goods/show/3814?targetId=6587&preview=0

推薦學(xué)習(xí)書籍 《CDA一級教材》適合CDA一級考生備考，也適合業(yè)務(wù)及數(shù)據(jù)分析崗位的從業(yè)者提升自我。完整電子版已上線CDA網(wǎng)校，累計(jì)已有10萬+在讀~ 免費(fèi)加入閱讀：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0