浅析MySQL代价模型：告别盲目使用EXPLAIN，提前预知索引优化策略

创作时间:

作者:

@小白创作中心

浅析MySQL代价模型：告别盲目使用EXPLAIN，提前预知索引优化策略

引用

来源

https://www.cnblogs.com/Jcloud/p/18183936

在MySQL中，索引的选择和优化一直是一个技术难题。本文将深入探讨MySQL的代价模型，帮助读者理解数据库如何选择最优的索引策略，并通过源码分析展示具体的计算方法。

在 MySQL 中，当我们为表创建了一个或多个索引后，通常需要在索引定义完成后，根据具体的数据情况执行 EXPLAIN 命令，才能观察到数据库实际使用哪个索引、是否使用索引。这使得我们在添加新索引之前，无法提前预知数据库是否能使用期望的索引。更为糟糕的是，有时甚至在添加新的索引后，数据库在某些查询中会使用它，而在其他查询中则不会使用，这种情况下，我们无法确定索引是否发挥了预期的作用，让人感到非常苦恼。这种情况基本上意味着 MySQL 并没有为我们选择最优的索引，而我们不得不在茫茫数据中摸索，试图找到问题的症结所在。我们可能会尝试调整索引，甚至删除索引，然后重新添加，希望 MySQL 能从中找到最优的索引选择。然而，这样的过程既耗时又费力，而且往往收效甚微。

如果在添加索引之前，我们能够预知索引的使用情况，那么对于表设计将大有裨益。我们可以在设计表结构时，更加明确地知道应该选择哪些索引，如何优化索引，以提高查询效率。我们不再需要依赖盲目尝试和猜测，而是可以基于实际的数据和查询情况，做出更加明智的决策。因此，对于 MySQL 用户来说，能够预知索引走势的需求非常迫切。我们希望能有一种方法，能够让我们在添加索引之前，就清楚地了解 MySQL 将如何使用索引，以便我们能够更好地优化表结构，提高查询效率。这将极大地减轻我们的工作负担，提高我们的工作效率，让我们能够更加专注于业务逻辑的处理，而不是在索引的海洋中挣扎。

为了解决这个问题，我们可以深入研究 MySQL 的索引选择机制。实际上，这个机制的核心就是代价模型，它通过一个公式来决定索引的选择策略。相对于 MySQL 其他复杂的概念，代价模型实现起来要简单得多。熟悉代价模型之后，我们可以预先了解 MySQL 在执行查询时会如何选择索引，从而更有效地进行索引优化。在接下来的文章中，我将结合近期进行索引优化的具体案例，来详细解释如何运用代价模型来优化索引。

MySQL数据库主要由4层组成：

连接层：客户端和连接服务，主要完成一些类似于连接处理、授权管理、以及相关的安全方案。
服务层：主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化以及内部函数的执行。
引擎层：负责MySQL中数据的存储和提取，服务器通过AP1与存储引擎进行通信。
存储层：将数据存储文件系统上，并完成与存储引擎的交互。

索引策略选择在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版本，后续的代价计算公式将基于此版本进行参考。

/*
  在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;

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  
}

热门推荐

“无陪护病房”，探索医疗护理服务新模式