查询优化器是MySQL的核心子系统之一，代价计算是查询优化器的核心逻辑。全表扫描开销作为与其他表访问方式开销比较的参考。只要成本超过全表扫描的成本，任何访问方法都不会被使用。基于全表扫描代价的重要性，从全表扫描的代价计算入手理清MySQL的代价计算逻辑是一个不错的选择。本文内容基于MySQL8.0.29源码。Text1.Overview首先看一下代码中cost计算的定义：//CPU操作成本doublecpu_cost;//远程操作的成本doubleimport_cost;//使用的内存（字节）doublemem_cost;......}从上面的代码可以看出，MySQL的成本计算模型定义了四种成本：IO成本：从磁盘或内存中读取数据页的成本。CPU成本：访问记录所需的CPU成本。importcost：MySQL8.0.29之前没有用到这一项，暂时忽略。Memorycost：此项是指占用内存的字节数。在MRR（MultiRangeRead）方法中计算读取数据的代价时会用到，先忽略。全表扫描的开销只是IO开销和CPU开销。2.计算公式我们先从整体的计算公式开始，然后一步步拆解。全表扫描cost=io_cost+1.1+cpu_cost+1。io_cost后面的1.1是硬编码的，直接加到IOcost上；cpu_cost后面的1也是硬编码的，直接加到CPU成本上。代码如下所示：inttest_quick_select(...){......doublescan_time=cost_model->row_evaluate_cost(static_cast(records))+1/*+1aftercpu_cost*/;cost_estimatecost_est=table->file->table_scan_cost();//+1.1落后于io_costcost_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是主键索引已经加载到BufferPool中的叶子节点占所有叶子节点的比例，用小数表示（取值范围0.0~1.0），例如：80%表示为0.8。RatioofDataPagesinMemory小节会介绍具体的计算逻辑。pages_on_disk_percent是磁盘文件中主键索引的叶子节点占所有叶子节点的比例，按1-pages_in_memory_percent计算。0.25是成本常量memory_block_read_cost的默认值，表示从BufferPool中的一个数据页读取数据的成本。1.0是成本常量io_block_read_cost的默认值，代表从磁盘文件加载一个数据页到BufferPool中的成本，加上从BufferPool中的数据页读取数据的成本。cpu_cost=n_rows*0.1。n_rows是表的记录数，从表的统计中得到，在统计部分会介绍。0.1是成本常量row_evaluate_cost的默认值，代表访问一条记录的CPU成本。有了上面的公式，下面我们通过一个具体的例子来走一遍计算全表扫描开销的过程。假设一张表有600条记录，主键索引数据页数为3，所有主键索引数据页都已经加载到BufferPool中（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表统计。SELECTtable_name,n_rows,clustered_index_sizeFROMmysql.innodb_table_statsWHEREdatabase_name='sakila'ANDtable_name='city'+------------+--------+-----------------------+|表名|n_行|clustered_index_size|+------------+--------+----------------------+|城市|600|3|+------------+--------+-------------------+--你也可以查询SELECTNAME,NUM_ROWS,CLUST_INDEX_SIZEFROMinformation_schema.INNODB_TABLESTATSWHERENAME='sakila/city'+------------+---------+------------------+|姓名|NUM_ROWS|CLUST_INDEX_SIZE|+------------+---------+----------------+|萨基拉/城市|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是主键索引已经加载到BufferPool中的叶子节点占所有叶子节点的比例。计算代码如下：inlinedoubleindex_pct_cached(constdict_index_t*index){//索引叶子节点个数constulintn_leaf=index->??stat_n_leaf_pages;......//已经加载到BufferPool中的叶节点数constuint64_tn_in_mem=buf_stat_per_index->??get(index_id_t(index->??space,index->??id));//加载到BufferPool中的叶子节点[除以]索引叶子节点的数量constdoubleratio=static_cast(n_in_mem)/n_leaf;//值只能在0.0到1.0之间表名，value是主键索引已经加载到BufferPool中的叶子节点的个数。每次将一张表的主键索引的叶子节点数据页从磁盘加载到BufferPool中，buf_stat_per_index->??m_store中该表对应的值加1。当一张表的主键索引叶子节点从BufferPool的LRU链表中淘汰时，buf_stat_per_index->??m_store中该表对应的值减1。在其他场景下，buf_stat_per_index->??m_store中的值也会发生变化，不会扩容。5.成本常量memory_block_read_cost和io_block_read_cost从系统表mysql.engine_cost中读取：SELECTcost_name,cost_value,default_valueFROMmysql.engine_cost;+-----------------------+------------+----------------+|成本名称|成本值|默认值|+-----------------------+------------+--------------+|io_block_read_cost|<空>|1.0||memory_block_read_cost|<空>|0.25|+------------------------+-------------+----------------+我们可以通过修改cost_value字段值来调整memory_block_read_cost和io_block_read_cost。row_evaluate_cost成本常量是从系统表mysql.server_cost中读取的：SELECTcost_name,cost_value,default_valueFROMmysql.server_costWHEREcost_name='row_evaluate_cost';+---------------------+-----------+------------+|成本名称|成本值|默认值|+-----------------+------------+--------------+|row_evaluate_cost|<空>|0.1|+-------------------+------------+---------------+我们可以修改cost_value字段值，来调整row_evaluate_cost。6、总结和计算全表扫描的开销，最重要的无疑是这个公式：全表扫描开销=io_cost+1.1+cpu_cost+1。io_cost表示全表扫描的IO开销。MySQL会先计算读取一个数据页的平均开销，然后乘以主键索引的数据页数，得到IO开销。计算读取一个数据页的平均开销，关键是要知道有多少个主键索引已经加载到BufferPool中的叶节点。InnoDB通过在内存中维护一个哈希表(buf_stat_per_index->??m_store)来记录这个数字。本文转载自微信公众号“一树一溪”，可通过以下二维码关注。转载本文请联系艺书艺熙公众号
