平时我们对语句调优一般用 EXPLAIN 就可以,但是对于一些复杂语句和疑难杂症这个所能给出信息不够详细,从 MySQL 5.6 开始加入了 Optimizer Trace 功能查看优化器生成执行计划的整个过程,使用示例如下:

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

它所返回的信息为一个 JSON,里面会有各种语句运行的详细信息,以上面的为例会列出真实运行的语句

{
  "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": [
        ]
      }
    }
  ]
}

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 从这句可以看出虽然我们写语句时写的是 *,对数据来说这只是给用户的简便写法,在真实运行时语句还是会转变为真实表字段的字段,而不是用 * 去运行。

更多信息参考 Optimizer Trace