成本

  1. IO成本。从磁盘加载到内存过程的时间成本。
  2. CPU成本。读取记录以及检测记录是否满足对应搜索条件、结果集排序等。

查询成本 = IO成本+CPU成本

  1. 加载一个页的代价常数为1.0

  2. 读取一条记录的代价常数为0.2

    成本计算步骤

  3. 根据搜索条件，找出所有可能使用的索引

  4. 计算全表扫描代价
  5. 计算使用不同索引查询的代价
  6. 对比代价，选择最小的

全表扫描代价

由于查询成本 = IO成本+CPU成本，因此需要得知两个信息：

  • 聚簇索引占用页面数：IO成本
  • 表中记录数量：CPU代价

使用 show table status 来查询统计信息得到上面的数据。

索引查询代价
  1. 确定搜索条件限定出的二级索引查询区间，一个区间认为是一次页面读取代价
  2. 确定需要回表的记录数，即估算在扫描区间中包含多少二级索引记录数

估算扫描区间的记录数的步骤分为：

  1. 根据索引定位最左记录
  2. 根据索引定位最右记录
  3. 根据左右记录的父节点计算两个记录之间的页数（如果相差很多可能不在同一个父节点，则递归查祖先节点）
  4. 如果相隔页面数小于10，则遍历数据页，根据数据页PageHeader中的存储的页中记录数来计算总的记录数
  5. 如果相隔页数大于10，则从最左页数遍历十个数据页，计算每页平均记录数，再根据平均数乘以总页数来估算

如果存在多个扫描区间，重复上面步骤计算，在根据成本常数计算时，回表操作的成本计算被认为是页面加载的成本

如果扫描区间太多（默认200），如in中很多参数，造成很多单点区域，则使用索引统计数据成本计算

  1. 使用show table status 统计的row值为表中记录数
  2. 使用show index统计的cardinality值为表中某列不重复的记录数
  3. row/cardinality约等于某列一个值平均重复多少次，即代表一个单点区间对应的记录数

连接查询成本

Mysql的连接查询是嵌套循环查询，驱动表查一次，根据驱动表查询结果，被驱动表可能被查多次。其成本构成为

  1. 驱动表单次查询成本
  2. 被驱动表多次查询成本

公式为：成本值=驱动表单次查询成本+驱动表结果数量*被驱动表单次查询成本
对于内连接而言，还需要计算不同表作为驱动表时的成本
当多表连接时，会产生N！种连接顺序，因此有一些额外的规则来限制成本计算

  1. 当当前计算中的成本已经超过目前记录的最小成本值时，提取结束该顺序的成本计算
  2. 限制最大连接个数的成本计算
  3. 根据一些规则直接取消成本计算