查詢優(yōu)化器是 MySQL 的核心子系統(tǒng)之一，成本計(jì)算又是查詢優(yōu)化器的核心邏輯。

全表掃描成本作為參照物，用于和表的其它訪問(wèn)方式的成本做對(duì)比。任何一種訪問(wèn)方式，只要成本超過(guò)了全表掃描成本，就不會(huì)被使用。

基于全表掃描成本的重要地位，要講清楚 MySQL 的成本計(jì)算邏輯，從全表掃描成本計(jì)算開(kāi)始是個(gè)不錯(cuò)的選擇。

本文內(nèi)容基于 MySQL 8.0.29 源碼。

正文

1、概述

我們先來(lái)看一下代碼里成本計(jì)算的定義：

class Cost_estimate {
 private:
  // cost of I/O operations
  double io_cost;
  // cost of CPU operations
  double cpu_cost;
  // cost of remote operations
  double import_cost;
  // memory used (bytes)
  double mem_cost;
  ......
}

從上面代碼可以看到，MySQL 成本計(jì)算模型定義了四種成本：

• IO 成本：從磁盤(pán)或內(nèi)存讀取數(shù)據(jù)頁(yè)的成本。
• CPU 成本：訪問(wèn)記錄需要消耗的 CPU 成本。
• 導(dǎo)入成本：這一項(xiàng)直到 MySQL 8.0.29 都還沒(méi)有被使用，先忽略。
• 內(nèi)存成本：這一項(xiàng)指的是占用內(nèi)存字節(jié)數(shù)，計(jì)算 MRR(Multi Range Read)方式讀取數(shù)據(jù)的成本時(shí)才會(huì)用到，也先忽略。

全表掃描的成本就只剩 IO 成本、CPU 成本這兩項(xiàng)了。

2、計(jì)算公式

我們先從整體計(jì)算公式開(kāi)始，然后逐步拆解。

全表掃描成本 = io_cost + 1.1 + cpu_cost + 1。

io_cost 后面的 1.1 是硬編碼直接加到 IO 成本上的;cpu_cost 后面的 1 也是硬編碼的，直接加到 CPU 成本上。代碼里長(zhǎng)這樣：

int test_quick_select(...) {
  ......
  double scan_time =
    cost_model->row_evaluate_cost(static_cast<double>(records)) 
    + 1 /* cpu_cost 后面的 + 1 */; 
  Cost_estimate cost_est = table->file->table_scan_cost();
  
  // io_cost 后面的 + 1.1
  cost_est.add_io(1.1);
  ......
}

關(guān)于這兩個(gè)硬編碼的值，代碼里沒(méi)有注釋為什么要加，不過(guò)它們是個(gè)固定值，不影響我們理解成本計(jì)算邏輯，先忽略它們。

io_cost = cluster_page_count * avg_single_page_cost。

cluster_page_count 是主鍵索引數(shù)據(jù)頁(yè)數(shù)量，從表的統(tǒng)計(jì)信息中得到，在統(tǒng)計(jì)信息小節(jié)會(huì)介紹。

avg_single_page_cost 是讀取一個(gè)數(shù)據(jù)頁(yè)的平均成本，通過(guò)計(jì)算得到，公式如下：

avg_single_page_cost = pages_in_memory_percent * 0.25 + pages_on_disk_percent * 1.0。

pages_in_memory_percent 是主鍵索引已經(jīng)加載到 Buffer Pool 中的葉結(jié)點(diǎn)占所有葉結(jié)點(diǎn)的比例，用小數(shù)表示(取值范圍 0.0 ~ 1.0)，例如：80% 表示為 0.8。數(shù)據(jù)頁(yè)在內(nèi)存中的比例小節(jié)會(huì)介紹具體計(jì)算邏輯。

pages_on_disk_percent 是主鍵索引在磁盤(pán)文件中的葉結(jié)點(diǎn)占所有葉結(jié)點(diǎn)的比例，通過(guò) 1 - pages_in_memory_percent 計(jì)算得到。

0.25 是成本常數(shù) memory_block_read_cost 的默認(rèn)值，表示從 Buffer Pool 中的一個(gè)數(shù)據(jù)頁(yè)讀取數(shù)據(jù)的成本。

1.0 是成本常數(shù) io_block_read_cost 的默認(rèn)值，表示把磁盤(pán)文件中的一個(gè)數(shù)據(jù)頁(yè)加載到 Buffer Pool 的成本，加上從 Buffer Pool 中的該數(shù)據(jù)頁(yè)讀取數(shù)據(jù)的成本。

cpu_cost = n_rows * 0.1。

n_rows 是表中記錄的數(shù)量，從表的統(tǒng)計(jì)信息中得到，在統(tǒng)計(jì)信息小節(jié)會(huì)介紹。

0.1 是成本常數(shù) row_evaluate_cost 的默認(rèn)值，表示訪問(wèn)一條記錄的 CPU 成本。

有了上面這些公式，我們通過(guò)一個(gè)具體例子走一遍全表掃描成本計(jì)算的過(guò)程。

假設(shè)一個(gè)表有 600 條記錄，主鍵索引數(shù)據(jù)頁(yè)的數(shù)量為 3，主鍵索引數(shù)據(jù)頁(yè)已經(jīng)全部加載到 Buffer Pool(pages_in_memory_percent = 1.0)，下面我們開(kāi)始計(jì)算過(guò)程：

pages_on_disk_percent = 1 - pages_in_memory_percent(1.0) = 0.0。

avg_single_page_cost = pages_in_memory_percent(1.0) * 0.25 + pages_on_disk_percent(0.0) * 1.0 = 0.25。

io_cost = cluster_page_count(3) * avg_single_page_cost(0.25) = 0.75。

cpu_cost = n_rows(600) * 0.1 = 60。

全表掃描成本 = io_cost(0.75) + 1.1 + cpu_cost(60) + 1 = 62.85。

3、統(tǒng)計(jì)信息

全表掃描成本計(jì)算過(guò)程中，用到了主鍵索引數(shù)據(jù)頁(yè)數(shù)量、表中記錄數(shù)量，這兩個(gè)數(shù)據(jù)都來(lái)源 InnoDB 的表統(tǒng)計(jì)信息。

SELECT
  table_name, n_rows, clustered_index_size
FROM mysql.innodb_table_stats
WHERE database_name = 'sakila'
AND table_name = 'city'

+------------+--------+----------------------+
| table_name | n_rows | clustered_index_size |
+------------+--------+----------------------+
| city       | 600    | 3                    |
+------------+--------+----------------------+

-- 也可以通過(guò)這個(gè) SQL 查詢
SELECT
  NAME, NUM_ROWS, CLUST_INDEX_SIZE 
FROM information_schema.INNODB_TABLESTATS
WHERE NAME = 'sakila/city'

+-------------+----------+------------------+
| NAME        | NUM_ROWS | CLUST_INDEX_SIZE |
+-------------+----------+------------------+
| sakila/city | 600      | 3                |
+-------------+----------+------------------+

clustered_index_size 就是主鍵索引數(shù)據(jù)頁(yè)數(shù)量，n_rows 是表中記錄數(shù)量。

4、數(shù)據(jù)頁(yè)在內(nèi)存中的比例

avg_single_page_cost = pages_in_memory_percent * 0.25 + pages_on_disk_percent * 1.0。

上面的公式用于計(jì)算讀取一個(gè)數(shù)據(jù)頁(yè)的平均成本，pages_in_memory_percent 是主鍵索引已經(jīng)加載到 Buffer Pool 中的葉結(jié)點(diǎn)占所有葉結(jié)點(diǎn)的比例。

計(jì)算代碼如下：

inline double index_pct_cached(const dict_index_t *index) {
  // 索引葉結(jié)點(diǎn)數(shù)量
  const ulint n_leaf = index->stat_n_leaf_pages;
  ......
  // 已經(jīng)加載到 Buffer Pool 中的葉結(jié)點(diǎn)數(shù)量
  const uint64_t n_in_mem =
      buf_stat_per_index->get(index_id_t(index->space, index->id));
  // 已加載到 Buffer Pool 中的葉結(jié)點(diǎn) [除以] 索引葉結(jié)點(diǎn)數(shù)量
  const double ratio = static_cast<double>(n_in_mem) / n_leaf;
  // 取值只能在 0.0 ~ 1.0 之間
  return (std::max(std::min(ratio, 1.0), 0.0));
}

InnoDB 在內(nèi)存中維護(hù)了一個(gè)哈希表(buf_stat_per_index->m_store)，key 是表名，value 是表的主鍵索引已經(jīng)加載到 Buffer Pool 中的葉子結(jié)點(diǎn)數(shù)量。

每次從磁盤(pán)加載某個(gè)表的主鍵索引的一個(gè)葉子結(jié)點(diǎn)數(shù)據(jù)頁(yè)到 Buffer Pool 中，該表在 buf_stat_per_index->m_store 中對(duì)應(yīng)的 value 值就加一。

從 Buffer Pool 的 LRU 鏈表淘汰某個(gè)表的主鍵索引葉子結(jié)點(diǎn)時(shí)，該表在 buf_stat_per_index->m_store 中對(duì)應(yīng)的 value 值就減一。

還有其它場(chǎng)景，buf_stat_per_index->m_store 中的 value 值也會(huì)發(fā)生變化，不展開(kāi)了。

5、成本常數(shù)

memory_block_read_cost 和 io_block_read_cost 這兩個(gè)成本常數(shù)從系統(tǒng)表 mysql.engine_cost 中讀?。?/p>

SELECT
  cost_name, cost_value, default_value
FROM mysql.engine_cost;

+------------------------+------------+---------------+
| cost_name              | cost_value | default_value |
+------------------------+------------+---------------+
| io_block_read_cost     | <null>     | 1.0           |
| memory_block_read_cost | <null>     | 0.25          |
+------------------------+------------+---------------+

我們可以修改 cost_value 字段值，來(lái)調(diào)整 memory_block_read_cost 和 io_block_read_cost。

row_evaluate_cost成本常數(shù)從系統(tǒng)表 mysql.server_cost 中讀取：

SELECT
  cost_name, cost_value, default_value
FROM mysql.server_cost
WHERE cost_name = 'row_evaluate_cost';

+-------------------+------------+---------------+
| cost_name         | cost_value | default_value |
+-------------------+------------+---------------+
| row_evaluate_cost | <null>     | 0.1           |
+-------------------+------------+---------------+

我們可以修改 cost_value 字段值，來(lái)調(diào)整 row_evaluate_cost。

6、總結(jié)

計(jì)算全表掃描成本，最重要的無(wú)疑是這個(gè)公式：全表掃描成本 = io_cost + 1.1 + cpu_cost + 1。

io_cost 表示全表掃描 IO 成本，MySQL 會(huì)先計(jì)算讀取一個(gè)數(shù)據(jù)頁(yè)的平均成本，然后乘以主鍵索引的數(shù)據(jù)頁(yè)數(shù)量，得到 IO 成本。

計(jì)算讀取一個(gè)數(shù)據(jù)頁(yè)的平均成本，關(guān)鍵是要知道主鍵索引已經(jīng)加載到 Buffer Pool 中的葉子結(jié)點(diǎn)數(shù)量。InnoDB 通過(guò)在內(nèi)存中維護(hù)一個(gè)哈希表(buf_stat_per_index->m_store)來(lái)記錄這個(gè)數(shù)量。

本文轉(zhuǎn)載自微信公眾號(hào)「一樹(shù)一溪」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系一樹(shù)一溪公眾號(hào)