MySQL 全表扫描成本计算

chujichenxuyuan
发布于 2023-1-16 17:01
浏览
1收藏

作者 | 操盛春

来源 | 一树一溪(ID:lovespring52)

转载请联系授权(微信ID:csch52)

查询优化器是 MySQL 的核心子系统之一,成本计算又是查询优化器的核心逻辑。

全表扫描成本作为参照物,用于和表的其它访问方式的成本做对比。任何一种访问方式,只要成本超过了全表扫描成本,就不会被使用。

基于全表扫描成本的重要地位,要讲清楚 MySQL 的成本计算逻辑,从全表扫描成本计算开始是个不错的选择。

本文内容基于 MySQL 8.0.29 源码。

目录

  • 1. 概述
  • 2. 计算公式
  • 3. 统计信息
  • 4. 数据页在内存中的比例
  • 5. 成本常数
  • 6. 总结

正文

1. 概述

我们先来看一下代码里成本计算的定义:

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 成本计算模型定义了四种成本:

  • ​IO 成本​​:从磁盘或内存读取数据页的成本。
  • ​CPU 成本​​:访问记录需要消耗的 CPU 成本。
  • ​导入成本​​:这一项直到 MySQL 8.0.29 都还没有被使用,先忽略。
  • ​内存成本​​:这一项指的是占用内存字节数,计算 MRR(Multi Range Read)方式读取数据的成本时才会用到,也先忽略。

全表扫描的成本就只剩 IO 成本、CPU 成本这两项了。

2. 计算公式

我们先从整体计算公式开始,然后逐步拆解。

全表扫描成本 = io_cost + 1.1 + cpu_cost + 1

io_cost 后面的 ​​1.1​​​ 是硬编码直接加到 IO 成本上的;cpu_cost 后面的 ​​1​​ 也是硬编码的,直接加到 CPU 成本上。代码里长这样:

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);
  ......
}

关于这两个硬编码的值,代码里没有注释为什么要加,不过它们是个固定值,不影响我们理解成本计算逻辑,先忽略它们。

io_cost = cluster_page_count * avg_single_page_cost

cluster_page_count 是​​主键索引数据页数量​​​,从表的统计信息中得到,在​​统计信息​​小节会介绍。

avg_single_page_cost 是​​读取一个数据页的平均成本​​,通过计算得到,公式如下:

avg_single_page_cost = pages_in_memory_percent * 0.25 + pages_on_disk_percent * 1.0

pages_in_memory_percent 是主键索引已经​​加载到 Buffer Pool​​​ 中的叶结点占​​所有​​​叶结点的​​比例​​​,用小数表示(取值范围 0.0 ~ 1.0),例如:80% 表示为 0.8。​​数据页在内存中的比例​​小节会介绍具体计算逻辑。

pages_on_disk_percent 是主键索引​​在磁盘文件中​​​的叶结点占​​所有​​​叶结点的​​比例​​​,通过 ​​1 - pages_in_memory_percent​​ 计算得到。

​0.25​​​ 是成本常数 ​​memory_block_read_cost​​ 的默认值,表示从 Buffer Pool 中的一个数据页读取数据的成本。

​1.0​​​ 是成本常数 ​​io_block_read_cost​​​ 的默认值,表示把磁盘文件中的一个数据页加载到 Buffer Pool 的成本,​​加上​​从 Buffer Pool 中的该数据页读取数据的成本。

cpu_cost = n_rows * 0.1

n_rows 是表中记录的数量,从表的统计信息中得到,在​​统计信息​​小节会介绍。

​0.1​​​ 是成本常数 ​​row_evaluate_cost​​ 的默认值,表示访问一条记录的 CPU 成本。

有了上面这些公式,我们通过一个具体例子走一遍全表扫描成本计算的过程。

假设一个表有 600 条记录,主键索引数据页的数量为 3,主键索引数据页已经​​全部​​​加载到 Buffer Pool(​​pages_in_memory_percent = 1.0​​),下面我们开始计算过程:

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. 统计信息

全表扫描成本计算过程中,用到了​​主键索引数据页数量​​​、​​表中记录数量​​,这两个数据都来源 InnoDB 的表统计信息。

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                    |
+------------+--------+----------------------+

-- 也可以通过这个 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​​​ 就是主键索引数据页数量,​​n_rows​​ 是表中记录数量。

4. 数据页在内存中的比例

avg_single_page_cost = pages_in_memory_percent * 0.25 + pages_on_disk_percent * 1.0

上面的公式用于计算​​读取一个数据页的平均成本​​​,pages_in_memory_percent 是主键索引已经​​加载到 Buffer Pool 中​​​的叶结点占​​所有​​叶结点的比例。

计算代码如下:

inline double index_pct_cached(const dict_index_t *index){
  // 索引叶结点数量
  const ulint n_leaf = index->stat_n_leaf_pages;
  ......
  // 已经加载到 Buffer Pool 中的叶结点数量
  const uint64_t n_in_mem =
      buf_stat_per_index->get(index_id_t(index->space, index->id));
  // 已加载到 Buffer Pool 中的叶结点 [除以] 索引叶结点数量
  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 在内存中维护了一个哈希表(​​buf_stat_per_index->m_store​​),key 是表名,value 是表的主键索引已经加载到 Buffer Pool 中的叶子结点数量。

每次从磁盘加载某个表的主键索引的一个叶子结点数据页到 Buffer Pool 中,该表在 ​​buf_stat_per_index->m_store​​​ 中对应的 value 值就​​加一​​。

从 Buffer Pool 的 LRU 链表淘汰某个表的主键索引叶子结点时,该表在 ​​buf_stat_per_index->m_store​​​ 中对应的 value 值就​​减一​​。

还有其它场景,buf_stat_per_index->m_store 中的 value 值也会发生变化,不展开了。

5. 成本常数

memory_block_read_cost 和 io_block_read_cost 这两个成本常数从系统表 ​​mysql.engine_cost​​ 中读取:

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​​ 字段值,来调整 memory_block_read_cost 和 io_block_read_cost。

row_evaluate_cost成本常数从系统表 ​​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​​ 字段值,来调整 row_evaluate_cost。

6. 总结

计算全表扫描成本,最重要的无疑是这个公式:全表扫描成本 = io_cost + 1.1 + cpu_cost + 1

io_cost 表示全表扫描 IO 成本,MySQL 会先计算读取一个数据页的平均成本,然后​​乘以​​主键索引的数据页数量,得到 IO 成本。

​计算读取一个数据页的平均成本​​​,关键是要知道主键索引已经加载到 Buffer Pool 中的​​叶子结点数量​​​。InnoDB 通过在内存中维护一个哈希表(​​buf_stat_per_index->m_store​​)来记录这个数量。

分类
标签
已于2023-1-16 17:01:41修改
收藏 1
回复
举报
回复
    相关推荐