![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL 5.7: オプティマイザ
追加要素にてコストを設定可能
ディスクI/O処理性能
メモリ処理性能
コスト変更前
root@localhost [nyosm]>select @@version; +---------------------+ | @@version | +---------------------+ | 5.7.5-labs-http-log | +---------------------+ 1 row in set (0.00 sec) root@localhost [nyosm]> Configurable Costs root@localhost [nyosm]>SELECT * FROM mysql.engine_cost; +-------------+-------------+--------------------+------------+---------------------+---------+ | engine_name | device_type | cost_name | cost_value | last_update | comment | +-------------+-------------+--------------------+------------+---------------------+---------+ | default | 0 | io_block_read_cost | NULL | 2014-10-09 18:51:46 | NULL | +-------------+-------------+--------------------+------------+---------------------+---------+ 1 row in set (0.00 sec) root@localhost [nyosm]>SELECT * FROM mysql.server_cost; +------------------------------+------------+---------------------+---------+ | cost_name | cost_value | last_update | comment | +------------------------------+------------+---------------------+---------+ | disk_temptable_create_cost | NULL | 2014-10-09 18:51:46 | NULL | | disk_temptable_row_cost | NULL | 2014-10-09 18:51:46 | NULL | | key_compare_cost | NULL | 2014-10-09 18:51:46 | NULL | | memory_temptable_create_cost | NULL | 2014-10-09 18:51:46 | NULL | | memory_temptable_row_cost | NULL | 2014-10-09 18:51:46 | NULL | | row_evaluate_cost | NULL | 2014-10-09 18:51:46 | NULL | +------------------------------+------------+---------------------+---------+ 6 rows in set (0.00 sec) root@localhost [nyosm]>
コスト設定変更前（Default)
root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
-> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
-> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
-> SELECT 'relation' as type,id FROM relationtags WHERE
-> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"union_result": {
"using_temporary_table": true,
"table_name": "<union1,2,3>",
"access_type": "ALL",
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "452.40"
},
"table": {
"table_name": "nodetags",
"access_type": "ref",
"possible_keys": [
"i_nodekeys",
"i_nodevalues"
],
"key": "i_nodevalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 377,
"rows_produced_per_join": 22,
"filtered": 6.0989,
"cost_info": {
"read_cost": "377.00",
"eval_cost": "4.60",
"prefix_cost": "452.40",
"data_read_per_join": "7K"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "37.20"
},
"table": {
"table_name": "waytags",
"access_type": "ref",
"possible_keys": [
"i_waykeys",
"i_wayvalues"
],
"key": "i_wayvalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 31,
"rows_produced_per_join": 0,
"filtered": 0.1613,
"cost_info": {
"read_cost": "31.00",
"eval_cost": "0.01",
"prefix_cost": "37.20",
"data_read_per_join": "15"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "relationtags",
"access_type": "ref",
"possible_keys": [
"i_relationkeys",
"i_relationvalues"
],
"key": "i_relationvalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": 5,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.01",
"prefix_cost": "1.20",
"data_read_per_join": "16"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
}
}
}
]
}
}
}
1 row in set, 1 warning (0.39 sec)
root@localhost [nyosm]>
コスト設定変更
root@localhost [nyosm]>UPDATE mysql.server_cost SET cost_value = 10; Query OK, 6 rows affected (0.19 sec) Rows matched: 6 Changed: 6 Warnings: 0 root@localhost [nyosm]>SELECT * FROM mysql.server_cost; +------------------------------+------------+---------------------+---------+ | cost_name | cost_value | last_update | comment | +------------------------------+------------+---------------------+---------+ | disk_temptable_create_cost | 10 | 2014-12-18 17:26:13 | NULL | | disk_temptable_row_cost | 10 | 2014-12-18 17:26:13 | NULL | | key_compare_cost | 10 | 2014-12-18 17:26:13 | NULL | | memory_temptable_create_cost | 10 | 2014-12-18 17:26:13 | NULL | | memory_temptable_row_cost | 10 | 2014-12-18 17:26:13 | NULL | | row_evaluate_cost | 10 | 2014-12-18 17:26:13 | NULL | +------------------------------+------------+---------------------+---------+ 6 rows in set (0.00 sec) root@localhost [nyosm]> root@localhost [nyosm]>FLUSH OPTIMIZER_COSTS; Query OK, 0 rows affected (0.00 sec) root@localhost [nyosm]>
コストの設定変更後
root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
-> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
-> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
-> SELECT 'relation' as type,id FROM relationtags WHERE
-> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"union_result": {
"using_temporary_table": true,
"table_name": "<union1,2,3>",
"access_type": "ALL",
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "452.40"
},
"table": {
"table_name": "nodetags",
"access_type": "ref",
"possible_keys": [
"i_nodekeys",
"i_nodevalues"
],
"key": "i_nodevalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 377,
"rows_produced_per_join": 22,
"filtered": 6.0989,
"cost_info": {
"read_cost": "377.00",
"eval_cost": "4.60",
"prefix_cost": "452.40",
"data_read_per_join": "7K"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "37.20"
},
"table": {
"table_name": "waytags",
"access_type": "ref",
"possible_keys": [
"i_waykeys",
"i_wayvalues"
],
"key": "i_wayvalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 31,
"rows_produced_per_join": 0,
"filtered": 0.1613,
"cost_info": {
"read_cost": "31.00",
"eval_cost": "0.01",
"prefix_cost": "37.20",
"data_read_per_join": "15"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "relationtags",
"access_type": "ref",
"possible_keys": [
"i_relationkeys",
"i_relationvalues"
],
"key": "i_relationvalues",
"used_key_parts": [
"v"
],
"key_length": "15",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": 5,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.01",
"prefix_cost": "1.20",
"data_read_per_join": "16"
},
"used_columns": [
"id",
"k",
"v"
],
"attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
}
}
}
]
}
}
}
1 row in set, 1 warning (0.00 sec)
root@localhost [nyosm]>
manual:
http://dev.mysql.com/doc/refman/5.7/en/cost-model.html
MySQL 5.6
root@localhost [(none)]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.01 sec)
MySQL5.7
root@localhost [(none)]>
root@localhost [nyosm]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on
1 row in set (0.04 sec)
root@localhost [nyosm]>