## 代價模型 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時，代價值按設定的值計算。修改方法如下： ~~~ ## 修改io_block_read_cost值為2 UPDATE mysql.engine_cost SET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost'; # FLUSH OPTIMIZER_COSTS 生效，只對新連接有效，老連接無效。 FLUSH OPTIMIZER_COSTS; ~~~ 另外，在主備環境下，修改cost參數時主備都要修改。因為mysql.server_cost和mysql.engine_cost的更新不會參與復制。 ## 代價分析示例 初始化數據 ~~~ create table t1(c1 int primary key, c2 int unique,c3 int) engine=innodb; let $loop=100; while($loop) { eval insert into t1(c1,c2,c3) values($loop, $loop+1, $loop+2); dec $loop; } 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 ~~~ explain select c1,c2 from t1 where c2 > 10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range c2 c2 5 NULL 91 100.00 Using where; Using index ~~~ 查看optimizer_trace, 可以看出全表掃描代價為23.1，通過c2上的索引掃描代價為19.309， 最后選擇c2上的索引掃描。 ~~~ "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 100, "cost": 23.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "c2", "usable": true, "key_parts": [ "c2" ] } ], "best_covering_index_scan": { "index": "c2", "cost": 21.109, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "c2", "ranges": [ "10 < c2" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 91, "cost": 19.309, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "c2", "rows": 91, "ranges": [ "10 < c2" ] }, "rows_for_plan": 91, "cost_for_plan": 19.309, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 91, "access_type": "range", "range_details": { "used_index": "c2" }, "resulting_rows": 91, "cost": 37.509, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 91, "cost_for_plan": 37.509, "chosen": true } ] ~~~ 全表掃描的代價23.1 包括io和cpu的代價 ~~~ test_quick_select： double scan_time= cost_model->row_evaluate_cost(static_cast<double>(records)) + 1; Cost_estimate cost_est= head->file->table_scan_cost(); cost_est.add_io(1.1);//這里加1.1應該是個調節值 cost_est.add_cpu(scan_time); ~~~ 其中io代價table_scan_cost會根據buffer pool大小和索引大小來估算page in memory和in disk的比例，分別算出代價。 ~~~ handler::table_scan_cost() ha_innobase::scan_time()*table->cost_model()->page_read_cost(1.0);//1*1=1 //其中scan_time計算數據所占page數， ~~~ page_read_cost計算讀取單個page的代價 ~~~ buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk); ~~~ io代價為1+1.1=2.1 cpu代價為row_evaluate_cost ~~~ double row_evaluate_cost(double rows) const { DBUG_ASSERT(m_initialized); DBUG_ASSERT(rows >= 0.0); return rows * m_server_cost_constants->row_evaluate_cost(); // 100 * 0.2(row_evaluate_cost)=20; } ~~~ cpu代價為20+1=21； 最終代價為2.1+21=23.1 c2索引掃描代價19.309 同樣也分為io和cpu代價 ~~~ multi_range_read_info_const： *cost= index_scan_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); ~~~ io代價 1.0987925356750823*1=1.0987925356750823 ~~~ index_scan_cost: const double io_cost= index_only_read_time(index, rows) * //估算index占page個數 = 1.0987925356750823 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 ~~~ cost->add_cpu(cost_model->row_evaluate_cost( static_cast<double>(total_rows)) + 0.01); //這里根據過濾條件算出的total_rows為91 ~~~ 最終代價1.0987925356750823+18.21=19.309 ### 示例2 以下語句選擇了全表掃描 ~~~ explain select * from t1 where c2 > 10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL c2 NULL NULL NULL 100 91.00 Using where ~~~ 查看optimizer_trace, 可以看出全表掃描代價為23.1，通過c2上的索引掃描代價為110.21， 最后選擇全表掃描。 ~~~ "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 100, "cost": 23.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "c2", "usable": true, "key_parts": [ "c2" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "c2", "ranges": [ "10 < c2" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 91, "cost": 110.21, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 91, "cost": 21, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 91, "cost_for_plan": 21, "chosen": true } ] }, ~~~ 全表掃描代價23.1 同上一節分析 c2索引掃描代價為110.21 上一節通過c2索引掃描代價為19.309，因為是覆蓋索引不需要回表，所以代價較少。而此例是需要回表的。 ~~~ multi_range_read_info_const： *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); ~~~ io代價需回表 ~~~ read_cost: //92*1=92 const double io_cost= read_time(index, static_cast<uint>(ranges) static_cast<ha_rows>(rows)) * table->cost_model()->page_read_cost(1.0); read_time: //91+1=92 virtual double read_time(uint index, uint ranges, ha_rows rows) { return rows2double(ranges+rows); } ~~~ 這里回表時計算代價為每行代價為1，默認認為回表時每行都對于聚集索引的一個page. io代價為92 cpu代價為91*0.2+0.01=18.21 `cost->add_cpu(cost_model->row_evaluate_cost( static_cast<double>(total_rows)) + 0.01);` 最后代價為92+18.21=110.21 ## 總結 5.7 代價模型優化還在持續改進中，相信后續的版本會越來越好。代價的參數的配置需謹慎，需要大量的測試和驗證。