MySQL优化器特性(八)索引范围扫描成本计算

俊达2年前技术文章1409

range

执行计划中的range表示索引范围扫描。索引范围扫描的执行过程大致如下:

1、根据where条件中索引字段的条件,定位到索引结构中的第一条满足条件的记录。

2、根据索引中记录的rowid,到表中获取数据。处理其它条件。

3、访问索引结构中的下一条记录,继续处理。直到满足range条件的所有记录都处理完成。

一条SQL中可能存在多个range范围(通过or条件相连, 如select * from tab where k1 in (a,b,c)就有3个range范围),需要依次处理每一个range。


range.png


range查询有几种不同的情况,mysql在计算成本时有所差异:

情况1:非覆盖索引。就是上面描述的那种情况。

情况2:覆盖索引,非主键。这种情况下,只需要扫描索引,不需要回表获取数据。

情况3:在主键上进行范围扫描。和覆盖索引类似。但是mysql在计算执行计划的成本时和覆盖索引有所差异。


一个例子

这个例子中,查询使用了索引范围扫描(range),cost为16.01,访问的记录数为35行。

mysql> explain format=tree select * from test_ror where a <= 1;
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on test_ror using idx_a over (NULL < a <= 1), with index condition: (test_ror.a <= 1)  (cost=16.01 rows=35)
 |
     
explain format=json select * from test_ror where a <= 1;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "16.01"
    },
    "table": {
      "table_name": "test_ror",
      "access_type": "range",
      "possible_keys": [
        "idx_a"
      ],
      "key": "idx_a",
      "used_key_parts": [
        "a"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 35,
      "rows_produced_per_join": 35,
      "filtered": "100.00",
      "index_condition": "(`test`.`test_ror`.`a` <= 1)",
      "cost_info": {
        "read_cost": "12.51",
        "eval_cost": "3.50",
        "prefix_cost": "16.01",
        "data_read_per_join": "840"
      },
      "used_columns": [
        "id",
        "a",
        "b",
        "c"
      ]
    }
  }
}



成本分为两部分,read_cost 12.51,eval_cost 3.5。从上一篇文章的分析中,我们知道eval_cost通过公式得到:

row_eval_cost = rows * row_evaluation_cost = 35 * 0.1 = 3.5


相关成本计算在函数test_quick_select中进行。optimizer_trace中记录了优化器评估各个访问路径的成本,本文中我们会大量使用optimizer_trace中显示的信息来验证成本计算公式。

表扫描成本


sql: select * from test_ror where a <=1

"range_analysis": {
    "table_scan": {
        "rows": 105,
        "cost": 12.85
    }



表扫描成本由几部分组成:

  • row_evaluate_cost + 1:rows * row_evaluate_cost + 1 =  105*0.1 + 1 = 11.5

  • table_scan_cost:in_mem_pages * memory_block_read_cost + disk_pages * io_block_read_cost = 1*0.25 + 0*1 = 0.25

  • 常数 1.1

上面3个部分相加,得到表扫描的成本为 11.5 + 0.25 + 1.1 = 12.85。



索引范围扫描成本评估

评估索引范围扫描的成本,需要先评估满足索引条件的记录数,然后根据记录数来评估索引范围扫描的成本。

我们先看一下索引成本计算的公式,后续再来看如何评估记录数。

索引范围扫描成本估算的代码调用路径:get_key_scans_params -> check_quick_select -> multi_range_read_info_const

 if (*flags & HA_MRR_INDEX_ONLY)
      *cost = index_scan_cost(keyno, static_cast<double>(n_ranges),
                              static_cast<double>(total_rows));
    else
      *cost = read_cost(keyno, static_cast<double>(n_ranges),
                        static_cast<double>(total_rows));
    cost->add_cpu(
        cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);


索引扫描分两种情况,

如果是覆盖索引,则计算index_scan_cost,具体计算方式已经在前面介绍了。

如果是非覆盖索引,则计算读取索引和表的成本。

索引范围扫描的总成本为IO成本加上解析行的CPU成本,再加上常数0.01

cost = index_scan_cost(ranges, rows) + row_evaluate_cost(rows) + 0.01  (覆盖索引)

cost = read_cost(ranges, rows) + row_evaluate_cost(rows) + 0.01  (非覆盖索引)



非覆盖索引

读取非覆盖索引的成本公式:

io_cost = read_time(index, ranges, rows) * page_read_cost(1.0)

成本跟range的数量和记录数都有关系。

Cost_estimate handler::read_cost(uint index, double ranges, double rows) {
  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;
}


计算方式:

在我们的测试SQL中(select * from test_ror whrere a <=1),ranges = 1, rows = 35

io_cost = (1 + 35) * 0.25

eval_cost = 35 * 0.1

cost = io_cost + eval_cost = (1 + 35) * 0.25 + 35 * 0.1 + 0.01 = 9 + 3.5 + 0.01 = 12.51



最终cost


对于range查询,最终的cost为

scan_total_cost = scan_read_cost + row_evaluate_cost(prefix_rowcount * rows_after_filtering)

    = 12.51 + (1 * 35) * 0.1 = 12.51 + 3.5  = 16.01


相关代码如下:

make_join_plan -> choose_table_order -> greedy_search -> best_extension_by_limited_search -> best_access_path

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


相关文章

MySQL优化器特性(六)表扫描成本计算

全表扫描成本使用optimizer_trace,或者使用explain format=tree, 或者explain format=json,可以查看查询的costmysql> exp...

 MySQL运维实战(1.3)安装部署:源码编译安装

MySQL运维实战(1.3)安装部署:源码编译安装

源码编译安装通常不需要自己编译mysql源码,编译的mysql和二进制包的内容基本一致。当然有些时候可能会需要采用源码编译的方式安装,安装一些非标准版本的mysql安装一些社区的patch、bugfi...

MySQL运维实战(4.9) SQL_MODE之NO_UNSIGNED_SUBTRACTION

在mysql数据库中,unsigned表示不存负数,如果unsigned类型的字段作运算,得到的结果为负数,SQL会报错。mysql> create table t...

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

MySQL优化器特性(三)表关联之BKA(Batched Key Access)优化

单表range查询时,可以使用MRR优化,先对rowid进行排序,然后再回表查询数据。在表关联的时候,也可以使用类似的优化方法,先根据关联条件取出被关联表的rowid,将rowid缓存在join bu...

MySQL运维实战(3.2) 常见数据库连接失败问题排查

如果数据库连接失败,可以从如下几方面来排查:1、客户端到服务端的网络是否畅通,服务端端口是否能连通。使用ping、telnet等工具探测服务端的端口是否能访问。[root@box3 ~]#&...

MySQL运维实战(7)建立复制

建立复制的基本步骤1、主库开启binlog主库需要配置的关键参数server_id:主备库需要设置为不同。log_bin:binlog文件的前缀,可以指定绝对路径,也可以只指定文件名。若不指定路径,b...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。