mysql INFORMATION_SCHEMA.OPTIMIZER_TRACE 表

1 QUERY

QUERY: select count(*) from test_innodb

2 TRACE

{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select count(0) AS `count(*)` from test_innodb"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "test_innodb",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "test_innodb",
                "table_scan": {
                  "rows": 121287,
                  "cost": 481
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "test_innodb",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 121287,
                      "cost": 481,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 121287,
                    "cost": 481,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 121287,
                "cost_for_plan": 24738.4,
                "estimated_join_cardinality": 121287
              }
            ]
          },
          {
            "best_join_order": ["test_innodb"]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "test_innodb",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}

3 MISSING_BYTES_BEYOND_MAX_MEM_SIZE

json 会超出一定大小之后就会阶段，这个字段记录阶段之后还有多少字节未记录

4 INSUFFICIENT_PRIVILEGES

如果用户没有权限使用 trace 工具，这个地方的值就是 1

参考

https://dev.mysql.com/doc/internals/en/information-schema-optimizer-trace-table.html