本篇文章介紹一個(gè)非常核心的數(shù)據(jù)庫問題。MySQL 選擇最佳執(zhí)行路徑（即“查詢優(yōu)化”）的過程是由其查詢優(yōu)化器（Query Optimizer） 完成的。

簡單來說，優(yōu)化器的目標(biāo)是：在多種可能的執(zhí)行方案中，選擇一個(gè)它認(rèn)為執(zhí)行成本（Cost）最低的方案。

這個(gè)決策過程非常復(fù)雜，但可以概括為以下幾個(gè)核心步驟和原則：

一、核心流程：優(yōu)化器如何工作？

1.解析與重寫查詢

• MySQL 首先解析 SQL 語句，生成一個(gè)解析樹。
• 然后對解析樹進(jìn)行重寫，包括一些語義優(yōu)化，例如：
  • 視圖展開：將視圖引用替換為視圖的定義。
  • 簡化條件：移除不必要的括號(hào)、簡化表達(dá)式（如 5=5 AND a>0 被簡化為 a>0）。
  • 常量傳遞：利用等式的傳遞性進(jìn)行簡化（如 a = 5 AND b = a => a = 5 AND b = 5）。

2.生成可能的執(zhí)行計(jì)劃

• 對于同一個(gè)查詢，通常有多種執(zhí)行方式。例如：
  • 表訪問方式：應(yīng)該全表掃描（ALL）還是使用索引？（const, ref, range, index）。
  • 多表連接（JOIN）的順序：先讀哪張表，后讀哪張表？(A JOIN B 還是 B JOIN A)。
  • 多表連接的算法：使用 Nested-Loop Join、Hash Join (MySQL 8.0+)，還是 Batched Key Access Join？
  • 子查詢優(yōu)化：將子查詢轉(zhuǎn)換為更高效的 JOIN 操作。
  • 索引合并：是否需要對多個(gè)索引的掃描結(jié)果進(jìn)行合并（index_merge）。

優(yōu)化器會(huì)枚舉出許多（但不是全部，因?yàn)楦F舉所有可能代價(jià)太高）可能的執(zhí)行路徑。

3.基于成本（Cost）評估執(zhí)行計(jì)劃

• 這是最核心的一步。優(yōu)化器是一個(gè)基于成本的優(yōu)化器（Cost-Based Optimizer, CBO）。
• 它會(huì)為每個(gè)執(zhí)行計(jì)劃計(jì)算一個(gè)預(yù)估成本（Cost）。成本是一個(gè)相對值，主要基于以下統(tǒng)計(jì)信息：
  • I/O 成本：將數(shù)據(jù)從磁盤加載到內(nèi)存的代價(jià)。全表掃描的成本主要來自這里。
  • CPU 成本：處理數(shù)據(jù)（比較記錄、排序、計(jì)算等）的代價(jià)。
  • 內(nèi)存/資源成本：使用臨時(shí)表、排序等的代價(jià)。

4.選擇成本最低的計(jì)劃

• 優(yōu)化器會(huì)比較所有生成的執(zhí)行計(jì)劃的預(yù)估成本，并選擇它認(rèn)為成本最低的那個(gè)。
• 最終，這個(gè)被選中的計(jì)劃會(huì)被交給執(zhí)行引擎去執(zhí)行。

二、優(yōu)化器依賴的關(guān)鍵信息

優(yōu)化器的成本計(jì)算并非憑空猜測，它嚴(yán)重依賴于數(shù)據(jù)庫的統(tǒng)計(jì)信息：

• 表統(tǒng)計(jì)信息
  • TABLE_ROWS：表的粗略行數(shù)。
  • DATA_LENGTH：表的數(shù)據(jù)大小。
  • 這些信息可以通過 ANALYZE TABLE table_name; 命令來更新，存儲(chǔ)在 information_schema.TABLES 中。
• 索引統(tǒng)計(jì)信息（至關(guān)重要）
• 基數(shù)（Cardinality）：索引中唯一值的估計(jì)值。這是一個(gè)非常關(guān)鍵的指標(biāo)。
  • 高選擇性（High Cardinality）：索引列的唯一值很多（如主鍵、用戶名），意味著索引非常有效。
  • 低選擇性（Low Cardinality）：索引列的唯一值很少（如性別、狀態(tài)標(biāo)志），使用索引可能不如全表掃描。
  • 索引信息存儲(chǔ)在 information_schema.STATISTICS 中。

示例：SELECT * FROM users WHERE gender = 'F';

• 如果 gender 列的基數(shù)很低（只有 ‘M’/‘F’ 兩個(gè)值），優(yōu)化器知道即使用了索引，也要返回約50%的數(shù)據(jù)。此時(shí)全表掃描的成本可能更低。
• 如果查詢是 SELECT * FROM users WHERE user_id = 123;，user_id 是主鍵，基數(shù)極高，優(yōu)化器會(huì)毫不猶豫地選擇主鍵索引進(jìn)行查找。

三、開發(fā)者如何協(xié)助和干預(yù)優(yōu)化器？

雖然優(yōu)化器很強(qiáng)大，但它的決策基于統(tǒng)計(jì)信息，而統(tǒng)計(jì)信息可能是過時(shí)或不準(zhǔn)確的。開發(fā)者可以這樣做：

• 提供合適的索引（最重要的手段）
  • 為 WHERE, JOIN ... ON, ORDER BY, GROUP BY 子句中的列創(chuàng)建索引。
  • 使用覆蓋索引（Covering Index），即索引包含了查詢所需的所有字段，避免回表操作，極大提升性能。
• 及時(shí)更新統(tǒng)計(jì)信息
  • 在執(zhí)行了大量 INSERT, UPDATE, DELETE 操作后，運(yùn)行 ANALYZE TABLE table_name; 來更新統(tǒng)計(jì)信息，幫助優(yōu)化器做出更準(zhǔn)確的判斷。
• 優(yōu)化SQL寫法
  • 避免使用 SELECT *，只選擇需要的列。
  • 謹(jǐn)慎使用 OR，它常常會(huì)導(dǎo)致索引失效，可以考慮使用 UNION 改寫。
  • 避免在索引列上使用函數(shù)或計(jì)算，這會(huì)導(dǎo)致索引失效（例如 WHERE YEAR(create_time) = 2023 不如 WHERE create_time >= '2023-01-01'）。
• 使用優(yōu)化器提示（Optimizer Hints）
  • 如果你確信優(yōu)化器選錯(cuò)了計(jì)劃，可以使用提示來強(qiáng)制干預(yù)。例如：
  • SELECT /*+ INDEX(table_name index_name) */ ...：強(qiáng)制使用某個(gè)索引。
  • SELECT /*+ NO_INDEX(table_name index_name) */ ...：強(qiáng)制忽略某個(gè)索引。
• 注意： 這是一種高級(jí)且危險(xiǎn)的操作，只有在充分理解和測試后才使用，因?yàn)閿?shù)據(jù)分布變化后，強(qiáng)制提示可能反而會(huì)變差。
• 使用 EXPLAIN 分析計(jì)劃
  • 這是最強(qiáng)大的調(diào)試工具。在SQL語句前加上 EXPLAIN 或 EXPLAIN FORMAT=TREE (MySQL 8.0+)，可以查看優(yōu)化器選擇的執(zhí)行計(jì)劃。
  • 你需要學(xué)會(huì)看 EXPLAIN 的輸出（尤其是 type, key, rows, Extra 字段），來判斷索引是否被有效利用、是否有全表掃描、是否使用了臨時(shí)表等。

總結(jié)

MySQL 通過查詢優(yōu)化器選擇最佳執(zhí)行路徑，其核心是：

1. 基于成本模型：估算不同執(zhí)行計(jì)劃的 I/O、CPU 成本。
2. 依賴統(tǒng)計(jì)信息：表的行數(shù)、索引的基數(shù)等是其決策的依據(jù)。
3. 目標(biāo)是成本最低：選擇它認(rèn)為執(zhí)行最快的方案。

作為開發(fā)者，我們的職責(zé)是：

• 提供準(zhǔn)確的信息：通過創(chuàng)建合適的索引和更新統(tǒng)計(jì)信息來“幫助”優(yōu)化器。
• 驗(yàn)證和干預(yù)：使用 EXPLAIN 工具驗(yàn)證優(yōu)化器的選擇，并在極少數(shù)情況下使用提示進(jìn)行干預(yù)。
• 編寫優(yōu)化器友好的SQL：避免寫出讓優(yōu)化器“困惑”的語句。

到此這篇關(guān)于MySQL執(zhí)行過程中選擇最佳的執(zhí)行路徑的方法(核心流程)的文章就介紹到這了,更多相關(guān)mysql執(zhí)行過程選擇執(zhí)行路徑內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論