我的书签
添加书签
移除书签代价模型
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 代价模型优化还在持续改进中，相信后续的版本会越来越好。代价的参数的配置需谨慎，需要大量的测试和验证。
