--查看优化器跟踪是否状态
mysql> SHOW VARIABLES LIKE '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
--开启跟踪
mysql> SET session optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
--执行一条sql
mysql> SELECT * FROM test_stat where c=10 and d=12;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 4 | 10 | 12 | 200 | 104 |
| 1 | 5 | 10 | 12 | 100 | 105 |
+---+---+------+------+------+------+
2 rows in set (0.05 sec)
--查看上述执行sql的trace跟踪文件
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE where query ='SELECT * FROM test_stat where c=10 and d=12';
+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| SELECT * FROM test_stat where c=10 and d=12 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test_stat`.`a` AS `a`,`test_stat`.`b` AS `b`,`test_stat`.`c` AS `c`,`test_stat`.`d` AS `d`,`test_stat`.`e` AS `e`,`test_stat`.`f` AS `f` from `test_stat` where ((`test_stat`.`c` = 10) and (`test_stat`.`d` = 12))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`test_stat`.`c` = 10) and (`test_stat`.`d` = 12))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test_stat`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`test_stat`",
"field": "c",
"equals": "10",
"null_rejecting": false
},
{
"table": "`test_stat`",
"field": "d",
"equals": "12",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`test_stat`",
"range_analysis": {
"table_scan": {
"rows": 5,
"cost": 4.1
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i2uniq",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i1",
"usable": true,
"key_parts": [
"c",
"d",
"a",
"b"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "i1",
"ranges": [
"10 <= c <= 10 AND 12 <= d <= 12"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 3.41,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "i1",
"rows": 2,
"ranges": [
"10 <= c <= 10 AND 12 <= d <= 12"
]
},
"rows_for_plan": 2,
"cost_for_plan": 3.41,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test_stat`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "i1",
"rows": 2,
"cost": 2.4,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "i1"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2,
"cost_for_plan": 2.4,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`test_stat`.`d` = 12) and (`test_stat`.`c` = 10))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test_stat`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`test_stat`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
} | 0 | 0 |
+---------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.00 sec)
--关闭跟踪
mysql> SET session optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)
--查看文件可以导出的目录,为/var/lib/mysql-files/、 为''是可以导出到任意目录、为null是不可以导出到任意目录
mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.06 sec)
--上述查看不方便的话,可以输出到文件里看
mysql> SELECT TRACE INTO DUMPFILE '/var/lib/mysql-files/test_stat_cd_trace.sql' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE where query ='SELECT * FROM test_stat where c=10 and d=12';
Query OK, 1 row affected (0.00 sec)
[root@hostmysql-m mysql-files]# more /var/lib/mysql-files/test_stat_cd_trace.sql
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test_stat`.`a` AS `a`,`test_stat`.`b` AS `b`,`test_stat`.`c` AS `c`,`test_stat`.`d` AS `d`,`test_stat`.`e` AS `e`,
`test_stat`.`f` AS `f` from `test_stat` where ((`test_stat`.`c` = 10) and (`test_stat`.`d` = 12))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`test_stat`.`c` = 10) and (`test_stat`.`d` = 12))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(10, `test_stat`.`c`) and multiple equal(12, `test_stat`.`d`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test_stat`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`test_stat`",
"field": "c",
"equals": "10",
"null_rejecting": false
},
{
"table": "`test_stat`",
"field": "d",
"equals": "12",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`test_stat`",
"range_analysis": {
"table_scan": {
"rows": 5,
"cost": 4.1
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i2uniq",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i1",
"usable": true,
"key_parts": [
"c",
"d",
"a",
"b"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "i1",
"ranges": [
"10 <= c <= 10 AND 12 <= d <= 12"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 3.41,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "i1",
"rows": 2,
"ranges": [
"10 <= c <= 10 AND 12 <= d <= 12"
]
},
"rows_for_plan": 2,
"cost_for_plan": 3.41,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test_stat`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "i1",
"rows": 2,
"cost": 2.4,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "i1"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2,
"cost_for_plan": 2.4,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`test_stat`.`d` = 12) and (`test_stat`.`c` = 10))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test_stat`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`test_stat`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
--附:test_stat表的结构及执行计划
mysql> show create table test_stat\G
*************************** 1. row ***************************
Table: test_stat
Create Table: CREATE TABLE `test_stat` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
`e` int(11) DEFAULT NULL,
`f` int(11) DEFAULT NULL,
PRIMARY KEY (`a`,`b`),
UNIQUE KEY `i2uniq` (`e`,`f`),
KEY `i1` (`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain SELECT * FROM test_stat where c=10 and d=12;
+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | test_stat | NULL | ref | i1 | i1 | 10 | const,const | 2 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)