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

explainnull

コスト設定変更

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

explain10

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

Comments are closed.

Post Navigation