planner: support explain plan in JSON format

[fzzf678]

Enhancement

JSON is a language-independent format for data exchange and easy to decode. With this format, optimizer plan can be easily decoded, as well as easy integration with 3rd party tools. It was supported in MySQL since 5.6. In order to improve scalability and compatibility, we need a JSON format output of explain result.

Notice: this json format is not same with MySQL‘s, we use format = tidb_json distinguish

TODO

• executeInfo and operatorInfo can be show as JSON format rather than string, but we need add JSON tag for each operator
• *: support JSON format output in explain statement #39253 (comment)

[adamf-db]

+1 to this enhancement.

[dveeden]

+1 to this enhancement.

GitHub uses the reactions to the issue description when filtering and sorting. Please add a 👍 to the issue description to vote for this issue.

[dveeden]

I would suggest to change the title to "planner: support MySQL compatible JSON explain format".

The benefits and disadvantages for FORMAT=JSON:

• Compatible with MySQL and IDE's etc
• Might not show the same level of detail as this format wasn't made for TiDB and there might not be a good or logical place for some info.

The benefits and disadvantages of FORMAT=TIDB_JSON

• Full details on TiDB specific things
• IDE's might need changes to use this format and the output of this format.

Note that MySQL also has FORMAT=TREE, which we also don't support in TiDB. Maybe that should be another issue?

[dveeden]

This shows the executeInfo in the TIDB_JSON being a string instead of an object.

It is now like this:

"executeInfo": "time:127.2µs, loops:2, RU:0.000000, build_hash_table:{total:40.7µs, fetch:32.7µs, build:8.09µs}, probe:{concurrency:5, total:25.2µs, max:20.6µs, probe:16.4µs, fetch:8.81µs}",

It probably should be like this:

"executeInfo": {"time":"127.2µs", "loops":2, "RU":0.000000, "build_hash_table":{"total":"40.7µs", "fetch":"32.7µs", "build":"8.09µs"}, "probe":{"concurrency":5, "total":"25.2µs", "max":"20.6µs", "probe":"16.4µs", "fetch":"8.81µs"}",

(not sure if the values should be strings here with the unit (µs) or if they unit should be part of the key (e.g. "fetch_ms")

[dveeden]

Do you want a MySQL compatible JSON explain output or the TiDB explain output, but with executeInfo, etc encoded as JSON object instead of a string?

[adamf-db]

For now, the TIDB explain output as JSON, including the full executeInfo encoded as JSON instead of a string.

[adamf-db]

Also, I agree with encoding the unit either in the key name or as another k:v at the same depth, eg { "time": { "count": 12.684, "unit": "milliseconds"} but I think that's for another issue.

[dveeden]

In #47401 I make some changes that do some of this. Note that this is just a early draft, not something that's ready and/or complete.

Example:

[adamf-db]

@dveeden that looks good to me - JSON all the way down.