MySQL 全表扫描成本计算
查询优化器是 MySQL 的核心子系统之一,成本计算又是查询优化器的核心逻辑。
全表扫描成本作为参照物,用于和表的其它访问方式的成本做对比。任何一种访问方式,只要成本超过了全表扫描成本,就不会被使用。
基于全表扫描成本的重要地位,要讲清楚 MySQL 的成本计算逻辑,从全表扫描成本计算开始是个不错的选择。
本文内容基于 MySQL 8.0.29 源码。
目录 [TOC]
正文
1. 概述
我们先来看一下代码里成本计算的定义:
1class Cost_estimate {
2 private:
3 // cost of I/O operations
4 double io_cost;
5 // cost of CPU operations
6 double cpu_cost;
7 // cost of remote operations
8 double import_cost;
9 // memory used (bytes)
10 double mem_cost;
11 ......
12}
从上面代码可以看到,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 成本上。代码里长这样:
1int test_quick_select(...) {
2 ......
3 double scan_time =
4 cost_model->row_evaluate_cost(static_cast<double>(records))
5 + 1 /* cpu_cost 后面的 + 1 */;
6 Cost_estimate cost_est = table->file->table_scan_cost();
7
8 // io_cost 后面的 + 1.1
9 cost_est.add_io(1.1);
10 ......
11}
关于这两个硬编码的值,代码里没有注释为什么要加,不过它们是个固定值,不影响我们理解成本计算逻辑,先忽略它们。
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 的表统计信息。
1SELECT
2 table_name, n_rows, clustered_index_size
3FROM mysql.innodb_table_stats
4WHERE database_name = 'sakila'
5AND table_name = 'city'
6
7+------------+--------+----------------------+
8| table_name | n_rows | clustered_index_size |
9+------------+--------+----------------------+
10| city | 600 | 3 |
11+------------+--------+----------------------+
12
13-- 也可以通过这个 SQL 查询
14SELECT
15 NAME, NUM_ROWS, CLUST_INDEX_SIZE
16FROM information_schema.INNODB_TABLESTATS
17WHERE NAME = 'sakila/city'
18
19+-------------+----------+------------------+
20| NAME | NUM_ROWS | CLUST_INDEX_SIZE |
21+-------------+----------+------------------+
22| sakila/city | 600 | 3 |
23+-------------+----------+------------------+
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 中
的叶结点占所有
叶结点的比例。
计算代码如下:
1inline double index_pct_cached(const dict_index_t *index) {
2 // 索引叶结点数量
3 const ulint n_leaf = index->stat_n_leaf_pages;
4 ......
5 // 已经加载到 Buffer Pool 中的叶结点数量
6 const uint64_t n_in_mem =
7 buf_stat_per_index->get(index_id_t(index->space, index->id));
8 // 已加载到 Buffer Pool 中的叶结点 [除以] 索引叶结点数量
9 const double ratio = static_cast<double>(n_in_mem) / n_leaf;
10 // 取值只能在 0.0 ~ 1.0 之间
11 return (std::max(std::min(ratio, 1.0), 0.0));
12}
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
中读取:
1SELECT
2 cost_name, cost_value, default_value
3FROM mysql.engine_cost;
4
5+------------------------+------------+---------------+
6| cost_name | cost_value | default_value |
7+------------------------+------------+---------------+
8| io_block_read_cost | <null> | 1.0 |
9| memory_block_read_cost | <null> | 0.25 |
10+------------------------+------------+---------------+
我们可以修改 cost_value
字段值,来调整 memory_block_read_cost 和 io_block_read_cost。
row_evaluate_cost 成本常量从系统表 mysql.server_cost
中读取:
1SELECT
2 cost_name, cost_value, default_value
3FROM mysql.server_cost
4WHERE cost_name = 'row_evaluate_cost';
5
6+-------------------+------------+---------------+
7| cost_name | cost_value | default_value |
8+-------------------+------------+---------------+
9| row_evaluate_cost | <null> | 0.1 |
10+-------------------+------------+---------------+
我们可以修改 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
)来记录这个数量。
欢迎扫码关注公众号,我们一起学习更多 MySQL 知识: