代价模型

mysql 5.7代价计算相对之前的版本有较大的改进。例如

  • 代价模型参数可以动态配置，可以适应不同的硬件
  • 区分考虑数据在内存和在磁盘中的代价
  • 代价精度提升为浮点型
  • jion计算时不仅要考虑condition,还要考虑condition上的filter，具体参见参数condition_fanout_filter

5.7 在代价类型上分为io,cpu和memory, 5.7的代价模型还在完善中，memory的代价虽然已经收集了，但还没有没有计算在最终的代价中。 5.7 在源码上对代价模型进行了大量重构，代价分为server层和engine层。server层主要是cpu的代价，而engine层主要是io的代价。 5.7 引入了两个系统表mysql.server_cost和mysql.engine_cost来分别配置这两个层的代价。 以下分析均基于mysql5.7.10

server_cost

  • row_evaluate_cost (default 0.2) 计算符合条件的行的代价，行数越多，此项代价越大
  • memory_temptable_create_cost (default 2.0) 内存临时表的创建代价
  • memory_temptable_row_cost (default 0.2) 内存临时表的行代价
  • key_compare_cost (default 0.1) 键比较的代价，例如排序
  • disk_temptable_create_cost (default 40.0) 内部myisam或innodb临时表的创建代价
  • disk_temptable_row_cost (default 1.0) 内部myisam或innodb临时表的行代价

由上可以看出创建临时表的代价是很高的，尤其是内部的myisam或innodb临时表。

engine_cost

  • io_block_read_cost (default 1.0) 从磁盘读数据的代价，对innodb来说，表示从磁盘读一个page的代价

  • memory_block_read_cost (default 1.0) 从内存读数据的代价，对innodb来说，表示从buffer pool读一个page的代价

    目前io_block_read_cost和memory_block_read_cost默认值均为1，实际生产中建议酌情调大memory_block_read_cost，特别是对普通硬盘的场景。

代价配置

cost参数可以通过修改mysql.server_cost和mysql.engine_cost来实现。初始这两个表中的记录cost_value项均为NULL, 代价值都取上两节介绍的初始值。 当修改cost_value为非NULL时，代价值按设定的值计算。修改方法如下：

  1. ## 修改io_block_read_cost值为2
  2. UPDATE mysql.engine_cost
  3.   SET cost_value = 2.0
  4.   WHERE cost_name = 'io_block_read_cost';
  5. # FLUSH OPTIMIZER_COSTS 生效，只对新连接有效，老连接无效。
  6. FLUSH OPTIMIZER_COSTS;

另外，在主备环境下，修改cost参数时主备都要修改。因为mysql.server_cost和mysql.engine_cost的更新不会参与复制。

代价分析示例

初始化数据

  1. create table t1(c1 int primary key, c2 int unique,c3 int) engine=innodb;
  3. let $loop=100;
  4. while($loop)
  5. {
  6.   eval insert into t1(c1,c2,c3) values($loop, $loop+1, $loop+2);
  7.   dec $loop;
  8. }
  10. set optimizer_trace = "enabled=on";

cost参数都取默认值，以下示例中会用到row_evaluate_cost(0.2),io_block_read_cost(1.0),io_block_read_cost(1.0),memory_block_read_cost(1.0)

示例1

以下语句选择覆盖索引c2

  1. explain select c1,c2 from t1 where c2 > 10;
  2. id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
  3. 1       SIMPLE  t1      NULL    range   c2      c2      5       NULL    91      100.00  Using where; Using index

查看optimizer_trace, 可以看出全表扫描代价为23.1，通过c2上的索引扫描代价为19.309， 最后选择c2上的索引扫描。

  1.      "rows_estimation": [
  2.        {
  3.          "table": "`t1`",
  4.          "range_analysis": {
  5.            "table_scan": {
  6.              "rows": 100,
  7.              "cost": 23.1
  8.            },
  9.            "potential_range_indexes": [
  10.              {
  11.                "index": "PRIMARY",
  12.                "usable": false,
  13.                "cause": "not_applicable"
  14.              },
  15.              {
  16.                "index": "c2",
  17.                "usable": true,
  18.                "key_parts": [
  19.                  "c2"
  20.                ]
  21.              }
  22.            ],
  23.            "best_covering_index_scan": {
  24.              "index": "c2",
  25.              "cost": 21.109,
  26.              "chosen": true
  27.            },
  28.            "setup_range_conditions": [
  29.            ],
  30.            "group_index_range": {
  31.              "chosen": false,
  32.              "cause": "not_group_by_or_distinct"
  33.            },
  34.            "analyzing_range_alternatives": {
  35.              "range_scan_alternatives": [
  36.                {
  37.                  "index": "c2",
  38.                  "ranges": [
  39.                    "10 < c2"
  40.                  ],
  41.                  "index_dives_for_eq_ranges": true,
  42.                  "rowid_ordered": false,
  43.                  "using_mrr": false,
  44.                  "index_only": true,
  45.                  "rows": 91,
  46.                  "cost": 19.309,
  47.                  "chosen": true
  48.                }
  49.              ],
  50.              "analyzing_roworder_intersect": {
  51.                "usable": false,
  52.                "cause": "too_few_roworder_scans"
  53.              }
  54.            },
  55.            "chosen_range_access_summary": {
  56.              "range_access_plan": {
  57.                "type": "range_scan",
  58.                "index": "c2",
  59.                "rows": 91,
  60.                "ranges": [
  61.                  "10 < c2"
  62.                ]
  63.              },
  64.              "rows_for_plan": 91,
  65.              "cost_for_plan": 19.309,
  66.              "chosen": true
  67.            }
  68.          }
  69.        }
  70.      ]
  71.    },
  72.    {
  73.      "considered_execution_plans": [
  74.        {
  75.          "plan_prefix": [
  76.          ],
  77.          "table": "`t1`",
  78.          "best_access_path": {
  79.            "considered_access_paths": [
  80.              {
  81.                "rows_to_scan": 91,
  82.                "access_type": "range",
  83.                "range_details": {
  84.                  "used_index": "c2"
  85.                },
  86.                "resulting_rows": 91,
  87.                "cost": 37.509,
  88.                "chosen": true
  89.              }
  90.            ]
  91.          },
  92.          "condition_filtering_pct": 100,
  93.          "rows_for_plan": 91,
  94.          "cost_for_plan": 37.509,
  95.          "chosen": true
  96.        }
  97.      ]

全表扫描的代价23.1

包括io和cpu的代价

  1. test_quick_select：
  2.  double scan_time=                                                    
  3.    cost_model->row_evaluate_cost(static_cast<double>(records)) + 1;   
  4.  Cost_estimate cost_est= head->file->table_scan_cost();               
  5.  cost_est.add_io(1.1);//这里加1.1应该是个调节值                                                
  6.  cost_est.add_cpu(scan_time);

其中io代价table_scan_cost会根据buffer pool大小和索引大小来估算page in memory和in disk的比例，分别算出代价。

  1. handler::table_scan_cost()
  2.   ha_innobase::scan_time()*table->cost_model()->page_read_cost(1.0);//1*1=1
  3.   //其中scan_time计算数据所占page数，

page_read_cost计算读取单个page的代价

  1.  buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk);

io代价为1+1.1=2.1

cpu代价为row_evaluate_cost

  1. double row_evaluate_cost(double rows) const
  2. {
  3.   DBUG_ASSERT(m_initialized);
  4.   DBUG_ASSERT(rows >= 0.0);
  6.   return rows * m_server_cost_constants->row_evaluate_cost(); // 100 * 0.2(row_evaluate_cost)=20;
  7. }

cpu代价为20+1=21；

最终代价为2.1+21=23.1

c2索引扫描代价19.309

同样也分为io和cpu代价

  1. multi_range_read_info_const：
  3.   *cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
  4.                           static_cast<double>(total_rows));
  5.   cost->add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);

io代价 1.0987925356750823*1=1.0987925356750823

  1. index_scan_cost:
  2.   const double io_cost= index_only_read_time(index, rows) *  //估算index占page个数  = 1.0987925356750823
  3.   table->cost_model()->page_read_cost_index(index, 1.0);     //根据buffer pool大小和索引大小来估算page in memory和in disk的比例，计算读一个page的代价。 = 1

cpu代价91*0.2+0.01=18.21

  1. cost->add_cpu(cost_model->row_evaluate_cost(
  2.   static_cast<double>(total_rows)) + 0.01);  //这里根据过滤条件算出的total_rows为91

最终代价1.0987925356750823+18.21=19.309

示例2

以下语句选择了全表扫描

  1. explain select * from t1 where c2 > 10;
  2. id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
  3. 1       SIMPLE  t1      NULL    ALL     c2      NULL    NULL    NULL    100     91.00   Using where

查看optimizer_trace, 可以看出全表扫描代价为23.1，通过c2上的索引扫描代价为110.21， 最后选择全表扫描。

  1.  "rows_estimation": [
  2.               {
  3.                 "table": "`t1`",
  4.                 "range_analysis": {
  5.                   "table_scan": {
  6.                     "rows": 100,
  7.                     "cost": 23.1
  8.                   },
  9.                   "potential_range_indexes": [
  10.                     {
  11.                       "index": "PRIMARY",
  12.                       "usable": false,
  13.                       "cause": "not_applicable"
  14.                     },
  15.                     {
  16.                       "index": "c2",
  17.                       "usable": true,
  18.                       "key_parts": [
  19.                         "c2"
  20.                       ]
  21.                     }
  22.                   ],
  23.                   "setup_range_conditions": [
  24.                   ],
  25.                   "group_index_range": {
  26.                     "chosen": false,
  27.                     "cause": "not_group_by_or_distinct"
  28.                   },
  29.                   "analyzing_range_alternatives": {
  30.                     "range_scan_alternatives": [
  31.                       {
  32.                         "index": "c2",
  33.                         "ranges": [
  34.                           "10 < c2"
  35.                         ],
  36.                         "index_dives_for_eq_ranges": true,
  37.                         "rowid_ordered": false,
  38.                         "using_mrr": false,
  39.                         "index_only": false,
  40.                         "rows": 91,
  41.                         "cost": 110.21,
  42.                         "chosen": false,
  43.                         "cause": "cost"
  44.                       }
  45.                     ],
  46.                     "analyzing_roworder_intersect": {
  47.                       "usable": false,
  48.                       "cause": "too_few_roworder_scans"
  49.                     }
  50.                   }
  51.                 }
  52.               }
  53.             ]
  54.           },
  55.           {
  56.             "considered_execution_plans": [
  57.               {
  58.                 "plan_prefix": [
  59.                 ],
  60.                 "table": "`t1`",
  61.                 "best_access_path": {
  62.                   "considered_access_paths": [
  63.                     {
  64.                       "rows_to_scan": 100,
  65.                       "access_type": "scan",
  66.                       "resulting_rows": 91,
  67.                       "cost": 21,
  68.                       "chosen": true
  69.                     }
  70.                   ]
  71.                 },
  72.                 "condition_filtering_pct": 100,
  73.                 "rows_for_plan": 91,
  74.                 "cost_for_plan": 21,
  75.                 "chosen": true
  76.               }
  77.             ]
  78.           },

全表扫描代价23.1

同上一节分析

c2索引扫描代价为110.21

上一节通过c2索引扫描代价为19.309，因为是覆盖索引不需要回表，所以代价较少。而此例是需要回表的。

  1. multi_range_read_info_const：
  2.     *cost= read_cost(keyno, static_cast<double>(n_ranges),
  3.                      static_cast<double>(total_rows));    
  4.   cost->add_cpu(cost_model->row_evaluate_cost(            
  5.     static_cast<double>(total_rows)) + 0.01);

io代价需回表

  1. read_cost: //92*1=92
  2.   const double io_cost= read_time(index, static_cast<uint>(ranges)
  3.                                 static_cast<ha_rows>(rows)) *
  4.                                 table->cost_model()->page_read_cost(1.0);   
  6. read_time: //91+1=92
  7. virtual double read_time(uint index, uint ranges, ha_rows rows)
  8. { return rows2double(ranges+rows); }

这里回表时计算代价为每行代价为1，默认认为回表时每行都对于聚集索引的一个page.

io代价为92

cpu代价为91*0.2+0.01=18.21

  1. cost->add_cpu(cost_model->row_evaluate_cost(            
  2.     static_cast<double>(total_rows)) + 0.01);

最后代价为92+18.21=110.21

总结

5.7 代价模型优化还在持续改进中，相信后续的版本会越来越好。代价的参数的配置需谨慎，需要大量的测试和验证。