mysql> select version();
+------------+
| version() |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00 sec)
mysql> create table a (c1 int);
Query OK, 0 rows affected (0.32 sec)
mysql> insert into a values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into a values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into a values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into a values(4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into a values(5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into a values(6);
Query OK, 1 row affected (0.01 sec)
mysql> explain select * from a where c1=6;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> create index ind_a_c1 on a(c1);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from a where c1=6;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | a | ref | ind_a_c1 | ind_a_c1 | 5 | const | 1 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
#开启trace
mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
#设置trace大小
mysql> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)
#增加trace中注释
mysql> set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where c1=6;
+------+
| c1 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from a where c1=6
TRACE: {
"steps": [
{
"join_preparation": {#优化准备工作
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `a`.`c1` AS `c1` from `a` where (`a`.`c1` = 6)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {#优化的主要阶段,包括逻辑优化和物理优化两个阶段
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`a`.`c1` = 6)",
"steps": [
{
"transformation": "equality_propagation", #逻辑优化,等式处理
"resulting_condition": "multiple equal(6, `a`.`c1`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(6, `a`.`c1`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(6, `a`.`c1`)"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [#逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式
{
"table": "`a`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [ #逻辑优化,找出备选的索引
{
"table": "`a`",
"field": "c1",
"equals": "6",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */