查询优化器是表扫本计 MySQL 的核心子系统之一，成本计算又是描成查询优化器的核心逻辑。 全表扫描成本作为参照物，表扫本计用于和表的描成其它访问方式的成本做对比。任何一种访问方式，表扫本计只要成本超过了全表扫描成本，描成就不会被使用。表扫本计 基于全表扫描成本的描成重要地位，要讲清楚 MySQL 的表扫本计成本计算逻辑，从全表扫描成本计算开始是描成个不错的选择。 本文内容基于 MySQL 8.0.29 源码。表扫本计 正文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 是b2b信息网读取一个数据页的平均成本，通过计算得到，公式如下： 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_sizeFROM mysql.innodb_table_statsWHERE database_name = sakilaAND table_name = city+------------+--------+---------------------- + | table_name | n_rows | clustered_index_size | +------------+--------+---------------------- + | city | 600 | 3 | +------------+--------+---------------------- + -- 也可以通过这个 SQL 查询 SELECT NAME, NUM_ROWS, CLUST_INDEX_SIZEFROM information_schema.INNODB_TABLESTATSWHERE 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_valueFROM 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_valueFROM mysql.server_costWHERE 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)来记录这个数量。 本文转载自微信公众号「一树一溪」，可以通过以下二维码关注。转载本文请联系一树一溪公众号 
| 
喜欢
讨厌