MySQL 比 EXPLAIN 更详细的信息获取：Optimizer Trace

1
2
3
4

SET SESSION OPTIMIZER_TRACE="enabled=on";
SELECT *,`updated_at` < DATE_SUB(NOW(), INTERVAL 1 MINUTE) AS expired FROM `sessions` WHERE `id` = 1 LIMIT 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `sessions`.`id` AS `id`,`sessions`.`user_id` AS `user_id`,`sessions`.`ip_address` AS `ip_address`,`sessions`.`payload` AS `payload`,`sessions`.`device_id` AS `device_id`,`sessions`.`created_at` AS `created_at`,`sessions`.`updated_at` AS `updated_at`,(`sessions`.`updated_at` < (now() - interval 1 minute)) AS `expired` from `sessions` where (`sessions`.`id` = '1111111111111111111111111111111111111111') limit 1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`sessions`.`id` = '1111111111111111111111111111111111111111')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`sessions`.`id` = '1111111111111111111111111111111111111111')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`sessions`.`id` = '1111111111111111111111111111111111111111')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`sessions`.`id` = '1111111111111111111111111111111111111111')"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`sessions`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`sessions`",
                "field": "id",
                "equals": "'1111111111111111111111111111111111111111'",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`sessions`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": true
              }
            ]
          }
        ],
        "empty_result": {
          "cause": "no matching row in const table"
        }
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}