【技术笔记】使用optimizer_trace分析SQL语句执行情况

一般情况下，我们使用explain + SQL进行SQL语句的分析，但是有时候这个并不一定准确，因为SQL的优化器会根据自己的判断对SQL进行优化，另外一个就是SQL的一些统计，比如数据区分度，不一定实时准确，都会影响explain的准确性，这种情况下，就可以使用optimizer_trace作为SQL执行后的一种分析验证措施

 optimizer_trace的步骤如下：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

这里我创建了一个表，并插入了1万条数据，然后使用select order by进行查询，查询完成后，使用select optimizer_trace可以查询到上一个SQL执行情况，optimizer_trace的输出结果如下：

mysql> SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
*************************** 1. row ***************************
                            QUERY: select word from words order by rand() limit
3
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `words`.`word` AS `word` fr
om `words` order by rand() limit 3"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`words`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`words`",
                "table_scan": {
                  "rows": 9980,
                  "cost": 21
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`words`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 9980,
                      "access_type": "scan",
                      "resulting_rows": 9980,
                      "cost": 2017,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 9980,
                "cost_for_plan": 2017,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`words`",
                  "attached": null
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "rand()",
              "items": [
                {
                  "item": "rand()"
                }
              ],
              "resulting_clause_is_simple": false,
              "resulting_clause": "rand()"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`words`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 203,
                "key_length": 0,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 82646
              }
            }
          },
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "intermediate_tmp_table",
                "field": "tmp_field_0"
              }
            ],
            "filesort_priority_queue_optimization": {
              "limit": 3,
              "rows_estimate": 10010,
              "row_size": 16,
              "memory_available": 262144,
              "chosen": true
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 4,
              "examined_rows": 10000,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 96,
              "sort_mode": "<sort_key, rowid>"
            }
          }
        ]
      }
    }
  ]
}

这里面会列出上一次SQL的tracer信息，比如扫描了多少数据，排序的模式，由于这个是执行后的结果信息，所以这个是准确的