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]>