MySQL优化器特性（六）表扫描成本计算

mysql> explain format=tree select * from test_ror;
+---------------------------------------------------+
| EXPLAIN                                           |
+---------------------------------------------------+
| -> Table scan on test_ror  (cost=10.75 rows=105)
 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain format=json select * from test_ror;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.75"
    },
    "table": {
      "table_name": "test_ror",
      "access_type": "ALL",
      "rows_examined_per_scan": 105,
      "rows_produced_per_join": 105,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "10.50",
        "prefix_cost": "10.75",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "id",
        "a",
        "b",
        "c"
      ]
    }
  }
}

    double scan_read_cost = calculate_scan_cost(
        tab, idx, best_ref, prefix_rowcount, found_condition, disable_jbuf,
        &rows_after_filtering, &trace_access_scan);

    /*
      We estimate the cost of evaluating WHERE clause for found
      records as row_evaluate_cost(prefix_rowcount * rows_after_filtering).
      This cost plus scan_cost gives us total cost of using
      TABLE/INDEX/RANGE SCAN.
    */
    const double scan_total_cost =
        scan_read_cost +
        cost_model->row_evaluate_cost(prefix_rowcount * rows_after_filtering);

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;
}

double ha_innobase::scan_time() {
  ulint stat_clustered_index_size;
  stat_clustered_index_size = m_prebuilt->table->stat_clustered_index_size;
  return ((double)stat_clustered_index_size);
}

mysql> select * from mysql.innodb_table_stats where database_name='test' and table_name='test_ror';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | test_ror   | 2023-05-06 15:07:16 |    105 |                    1 |                        3 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

double Cost_model_table::page_read_cost(double pages) const {
  assert(m_initialized);
  assert(pages >= 0.0);

  // 表cache在内存中的比例，范围是0 - 1，0表示所有数据都不在内存中。1表示所有数据都缓存在内存中
  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;
  assert(pages_on_disk >= 0.0);

  // buffer_blaock_read_cost：从内存中读取page的成本
  // io_block_read_cost：从磁盘上读取page的成本
  const double cost =
      buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk);

  return cost;
}

double handler::table_in_memory_estimate() const {
  assert(stats.table_in_mem_estimate == IN_MEMORY_ESTIMATE_UNKNOWN ||
         (stats.table_in_mem_estimate >= 0.0 &&
          stats.table_in_mem_estimate <= 1.0));

  /*
    If the storage engine has supplied information about how much of the
    table that is currently in a memory buffer, then use this estimate.
  */
  if (stats.table_in_mem_estimate != IN_MEMORY_ESTIMATE_UNKNOWN)
    return stats.table_in_mem_estimate;

  /*
    The storage engine has not provided any information about how much of
    this index is in memory, use an heuristic to produce an estimate.
  */
  return estimate_in_memory_buffer(stats.data_file_length);
}

double handler::estimate_in_memory_buffer(ulonglong table_index_size) const {
  /*
    The storage engine has not provided any information about how much of
    the table/index is in memory. In this case we use a heuristic:

    - if the size of the table/index is less than 20 percent (pick any
      number) of the memory buffer, then the entire table/index is likely in
      memory.
    - if the size of the table/index is larger than the memory buffer, then
      assume nothing of the table/index is in memory.
    - if the size of the table/index is larger than 20 percent but less than
      the memory buffer size, then use a linear function of the table/index
      size that goes from 1.0 to 0.0.
  */

  longlong memory_buf_size = get_memory_buffer_size();
  if (memory_buf_size <= 0) memory_buf_size = 100 * 1024 * 1024;  // 100 MB

  const double table_index_in_memory_limit = 0.2;

  const double percent_of_mem =
      static_cast<double>(table_index_size) / memory_buf_size;

  double in_mem_est;

  if (percent_of_mem < table_index_in_memory_limit)  // Less than 20 percent
    in_mem_est = 1.0;
  else if (percent_of_mem > 1.0)  // Larger than buffer
    in_mem_est = 0.0;
  else {
    in_mem_est = 1.0 - (percent_of_mem - table_index_in_memory_limit) /
                           (1.0 - table_index_in_memory_limit);
  }
  return in_mem_est;
}

/** Estimate what percentage of an index's pages are cached in the buffer pool
@param[in]      index   index whose pages to look up
@return a real number in [0.0, 1.0] designating the percentage of cached pages
*/
inline double index_pct_cached(const dict_index_t *index) {
const ulint n_leaf = index->stat_n_leaf_pages;
if (n_leaf == 0) {
return (0.0);
}
const uint64_t n_in_mem =
buf_stat_per_index->get(index_id_t(index->space, index->id));
const double ratio = static_cast<double>(n_in_mem) / n_leaf;
return (std::max(std::min(ratio, 1.0), 0.0));
}

  double row_evaluate_cost(double rows) const {
    return rows * m_server_cost_constants->row_evaluate_cost();
  }