赞
踩
1.概述
设计 MySQL
的大叔提出了一个 optimizer trace
的功能,这个功能可以让我们方便的查看优化器生成执行计划的整个过程,这个功能的开启与关闭由系统变量 optimizer_trace
决定。
如果想打开这个功能,必须首先把 enabled
的值改为 on
,就像这样: SET optimizer_trace="enabled=on";
然后我们就可以输入我们想要查看优化过程的查询语句,当该查询语句执行完成后,就可以到 information_schema
数据库下的 OPTIMIZER_TRACE
表中查看完整的优化过程。这个 OPTIMIZER_TRACE
表有4个列,分别是:
(1). QUERY
:表示我们的查询语句。
(2). TRACE
:表示优化过程的JSON格式文本。
(3). MISSING_BYTES_BEYOND_MAX_MEM_SIZE
:由于优化过程可能会输出很多,如果超过某个限制时,多余的文本将不会被显示,这个字段展示了被忽略的文本字节数。
(4). INSUFFICIENT_PRIVILEGES
:表示是否没有权限查看优化过程,默认值是0
,只有某些特殊情况下才会是 1
,我们暂时不关心这个字段的值。
现在我们有一个搜索条件比较多的查询语句,它的执行计划如下:EXPLAIN SELECT * FROM t1 WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('a', 'b', 'c') AND common_field = 'abc';
可以看到该查询可能使用到的索引有3
个,那么为什么优化器最终选择了 idx_key1
而不选择其他的索引或者直接全表扫描呢?这时候就可以通过 otpimzer trace
功能来查看优化器的具体工作过程:
(1).SET optimizer_trace="enabled=on";
(2). SELECT * FROM t1 WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('a', 'b', 'c') AND common_field = 'abc';
(3). SELECT * FROM information_schema.OPTIMIZER_TRACE\G
QUERY: SELECT * FROM t1 WHERE key1 > 'z' AND key2 < 1000000 AND key3 IN ('a', 'b', 'c') AND common_field = 'abc' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key_part1` AS `key_part1`,`t1`.`key_part2` AS `key_part2`,`t1`.`key_part3` AS `key_part3`,`t1`.`common_field` AS `common_field` from `t1` where ((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "range_analysis": { "table_scan": { "rows": 3, "cost": 2.65 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_key2", "usable": true, "key_parts": [ "key2" ] }, { "index": "idx_key1", "usable": true, "key_parts": [ "key1", "id" ] }, { "index": "idx_key3", "usable": true, "key_parts": [ "key3", "id" ] }, { "index": "idx_key_part", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "idx_key2", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_key1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_key3", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_key2", "ranges": [ "NULL < key2 < 1000000" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 3, "cost": 1.31, "chosen": true }, { "index": "idx_key1", "ranges": [ "'z' < key1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 1, "cost": 0.61, "chosen": true }, { "index": "idx_key3", "ranges": [ "key3 = 'a'", "key3 = 'b'", "key3 = 'c'" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 3, "cost": 1.81, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "idx_key1", "rows": 1, "ranges": [ "'z' < key1" ] }, "rows_for_plan": 1, "cost_for_plan": 0.61, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1, "access_type": "range", "range_details": { "used_index": "idx_key1" }, "resulting_rows": 1, "cost": 0.71, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.71, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" } ] } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))", "final_table_condition ": "((`t1`.`key1` > 'z') and (`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" } ] }, { "refine_plan": [ { "table": "`t1`", "pushed_index_condition": "(`t1`.`key1` > 'z')", "table_condition_attached": "((`t1`.`key2` < 1000000) and (`t1`.`key3` in ('a','b','c')) and (`t1`.`common_field` = 'abc'))" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0
优化过程大致分为了三个阶段:
prepare
阶段
optimize
阶段
execute
阶段
我们所说的基于成本的优化主要集中在 optimize
阶段,对于单表查询来说,我们主要关注 optimize
阶段的 “rows_estimation
” 这个过程,这个过程深入分析了对单表查询的各种执行方案的成本;对于多表连接查询来说,我们更多需要关注 “considered_execution_plans
” 这个过程,这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划,也就是我们使用 EXPLAIN
语句所展现出的那种方案。如果有小伙伴对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解,大家可以尝试使用 optimizer trace
功能来详细了解每一种执行方案对应的成本,相信这个功能能让大家更深入的了解 MySQL
查询优化器。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。