浅析MySQL代价模型:告别盲目使用EXPLAIN,提前预知索引优化策略| 京东零售技术团队
背景
在 MySQL 中,当我们为表创建了一个或多个索引后,通常需要在索引定义完成后,根据具体的数据情况执行 EXPLAIN 命令,才能观察到数据库实际使用哪个索引、是否使用索引。这使得我们在添加新索引之前,无法提前预知数据库是否能使用期望的索引。更为糟糕的是,有时甚至在添加新的索引后,数据库在某些查询中会使用它,而在其他查询中则不会使用,这种情况下,我们无法确定索引是否发挥了预期的作用,让人感到非常苦恼。这种情况基本上意味着 MySQL 并没有为我们选择最优的索引,而我们不得不在茫茫数据中摸索,试图找到问题的症结所在。我们可能会尝试调整索引,甚至删除索引,然后重新添加,希望 MySQL 能从中找到最优的索引选择。然而,这样的过程既耗时又费力,而且往往收效甚微。
如果在添加索引之前,我们能够预知索引的使用情况,那么对于表设计将大有裨益。我们可以在设计表结构时,更加明确地知道应该选择哪些索引,如何优化索引,以提高查询效率。我们不再需要依赖盲目尝试和猜测,而是可以基于实际的数据和查询情况,做出更加明智的决策。因此,对于 MySQL 用户来说,能够预知索引走势的需求非常迫切。我们希望能有一种方法,能够让我们在添加索引之前,就清楚地了解 MySQL 将如何使用索引,以便我们能够更好地优化表结构,提高查询效率。这将极大地减轻我们的工作负担,提高我们的工作效率,让我们能够更加专注于业务逻辑的处理,而不是在索引的海洋中挣扎。
为了解决这个问题,我们可以深入研究 MySQL 的索引选择机制。实际上,这个机制的核心就是代价模型,它通过一个公式来决定索引的选择策略。相对于 MySQL 其他复杂的概念,代价模型实现起来要简单得多。熟悉代价模型之后,我们可以预先了解 MySQL 在执行查询时会如何选择索引,从而更有效地进行索引优化。在接下来的文章中,我将结合近期进行索引优化的具体案例,来详细解释如何运用代价模型来优化索引。
MySQL代价模型浅析
![](https://s3.cn-north-1.jdcloud-oss.com/shendengbucket1/2023-11-21-10-1811Ml9NzTpsRMPNba.png)
MySQL数据库主要由4层组成:
索引策略选择在SQL优化器进行的
SQL 优化器会分析所有可能的执行计划,选择成本最低的执行,这种优化器称之为:CBO(Cost-based Optimizer,基于成本的优化器)。
Cost = Server Cost + Engine Cost = CPU Cost + IO Cost
其中,CPU Cost 表示计算的开销,比如索引键值的比较、记录值的比较、结果集的排序 ...... 这些操作都在 Server 层完成;
IO Cost 表示引擎层 IO 的开销,MySQL 可以通过区分一张表的数据是否在内存中,分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。
源码简读
MySQL的数据源代码采用了5.7.22版本,后续的代价计算公式将基于此版本进行参考。
![](https://s3.cn-north-1.jdcloud-oss.com/shendengbucket1/2023-11-21-12-19gKPjfIZPZegDuFL.png)
![](https://s3.cn-north-1.jdcloud-oss.com/shendengbucket1/2023-11-21-12-19g11Vpk48WZHLFo48GT.png)
opt_costconstants.cc【代价模型——计算所需代价计算系数】
/*
在Server_cost_constants类中定义为静态常量变量的成本常量的值。如果服务器管理员没有在server_cost表中添加新值,则将使用这些默认成本常数值。
5.7版本开始可用从数据库加载常量值,该版本前使用代码中写的常量值
*/
// 计算符合条件的⾏的代价,⾏数越多,此项代价越⼤
const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;
// 键⽐较的代价,例如排序
const double Server_cost_constants::KEY_COMPARE_COST= 0.1;
/*
内存临时表的创建代价
通过基准测试,创建Memory临时表的成本与向表中写入10行的成本一样高。
*/
const double Server_cost_constants::MEMORY_TEMPTABLE_CREATE_COST= 2.0;
// 内存临时表的⾏代价
const double Server_cost_constants::MEMORY_TEMPTABLE_ROW_COST= 0.2;
/*
内部myisam或innodb临时表的创建代价
创建MyISAM表的速度是创建Memory表的20倍。
*/
const double Server_cost_constants::DISK_TEMPTABLE_CREATE_COST= 40.0;
/*
内部myisam或innodb临时表的⾏代价
当行数大于1000时,按顺序生成MyISAM行比生成Memory行慢2倍。然而,没有非常大的表的基准,因此保守地将此系数设置为慢5倍(即成本为1.0)。
*/
const double Server_cost_constants::DISK_TEMPTABLE_ROW_COST= 1.0;
/*
在SE_cost_constants类中定义为静态常量变量的成本常量的值。如果服务器管理员没有在engine_cost表中添加新值,则将使用这些默认成本常数值。
*/
// 从主内存缓冲池读取块的成本
const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;
// 从IO设备(磁盘)读取块的成本
const double SE_cost_constants::IO_BLOCK_READ_COST= 1.0;
opt_costmodel.cc【代价模型——部分涉及方法】
double Cost_model_table::page_read_cost(double pages) const
{
DBUG_ASSERT(m_initialized);
DBUG_ASSERT(pages >= 0.0);
// 估算聚集索引内存中页面数占其所有页面数的比率
const double in_mem= m_table->file->table_in_memory_estimate();
const double pages_in_mem= pages * in_mem;
const double pages_on_disk= pages - pages_in_mem;
DBUG_ASSERT(pages_on_disk >= 0.0);
const double cost= buffer_block_read_cost(pages_in_mem) +
io_block_read_cost(pages_on_disk);
return cost;
}
double Cost_model_table::page_read_cost_index(uint index, double pages) const
{
DBUG_ASSERT(m_initialized);
DBUG_ASSERT