MySQL Cost-Based Optimizer

最新推荐文章于 2024-06-01 21:41:48 发布

weixin_30649859

最新推荐文章于 2024-06-01 21:41:48 发布

阅读量370

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/hyming011/p/8251784.html

本文详细介绍了MySQL的成本估算模型，包括I/O操作消耗(io_cost)、CPU操作消耗(cpu_cost)等不同类型的成本，并深入探讨了io_cost的具体计算方法，如表扫描、索引扫描和读取成本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL Cost-Based Optimizer 模型

MySQL Cost_estimate 定义了多种消耗类型

1. io_cost cost of I/O operations IO操作消耗
2. cpu_cost cost of CPU operations CPU操作消耗
3. import_cost cost of remote operations 远程操作消耗
4. mem_cost memory used (bytes) 内存消耗

KEY_COMPARE_COST= 0.1
MEMORY_TEMPTABLE_CREATE_COST= 2.0
MEMORY_TEMPTABLE_ROW_COST= 0.2
DISK_TEMPTABLE_CREATE_COST= 40.0
DISK_TEMPTABLE_ROW_COST= 1.0
ROW_EVALUATE_COST= 0.2
MEMORY_BLOCK_READ_COST= 1.0
IO_BLOCK_READ_COST= 1.0

io_block_read_cost
memory_block_read_cost

disk_temptable_create_cost
disk_temptable_row_cost
key_compare_cost
memory_temptable_create_cost
memory_temptable_row_cost
row_evaluate_cost

MySQL目前版本只使用了 io_cost,cpu_cost

io_cost 定义了以下三种方法: 表扫、索引扫、读取消耗

Cost_estimate handler::table_scan_cost()
{
/*
This function returns a Cost_estimate object. The function should be
implemented in a way that allows the compiler to use "return value
optimization" to avoid creating the temporary object for the return value
and use of the copy constructor.
*/

const double io_cost= scan_time() * table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

Cost_estimate handler::index_scan_cost(uint index, double ranges, double rows)
{
/*
This function returns a Cost_estimate object. The function should be
implemented in a way that allows the compiler to use "return value
optimization" to avoid creating the temporary object for the return value
and use of the copy constructor.
*/

DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);

const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

Cost_estimate handler::read_cost(uint index, double ranges, double rows)
{
/*
This function returns a Cost_estimate object. The function should be
implemented in a way that allows the compiler to use "return value
optimization" to avoid creating the temporary object for the return value
and use of the copy constructor.
*/

DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);

const double io_cost= read_time(index, static_cast<uint>(ranges),
static_cast<ha_rows>(rows)) *
table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

cpu_cost

MySQL Cost-Based Optimizer 模型

MySQL Cost_estimate 定义了多种消耗类型

io_block_read_cost
memory_block_read_cost

disk_temptable_create_cost
disk_temptable_row_cost
key_compare_cost
memory_temptable_create_cost
memory_temptable_row_cost
row_evaluate_cost

MySQL目前版本只使用了 io_cost,cpu_cost

io_cost 定义了以下三种方法: 表扫、索引扫、读取消耗

const double io_cost= scan_time() * table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);

const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);

const double io_cost= read_time(index, static_cast<uint>(ranges),
static_cast<ha_rows>(rows)) *
table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}

转载于:https://www.cnblogs.com/hyming011/p/8251784.html