MySQL 9.0 is an Innovation Release, but as you may have read in my Quick Peek, I found little innovation. However, one new item caught my eye, and it could be a way to track query performance.
MySQL 9.0 EXPLAIN enhancement
Here is the syntax that will be referenced in the post.
{EXPLAIN | DESCRIBE | DESC} ANALYZE FORMAT = JSON INTO variable [schema_spec] select_statement
The new part is the EXPLAIN ANALYZE FORMAT = JSON INTO a variable. You can now save the output from the EXPLAIN ANALYZE into a JSON document. Big whoopie? No, this new option is the basis for storing data on query performance. You can then use that information to track historical performance and feed machine learning. Store the results over several runs over a period of time, and you will be able to answer The Hated Question.
The Hated Question
Anyone who works with a database hates the ‘Is this query running slower than it used to?’ type questions. Firstly, there is probably no benchmark time for how fast the query in question used to run. Secondly, there are many reasons why performance can be perceived as poor. And yes, darn it, over time, as data grows, loads on the server increase, and other factors are added in, a query can run slower.
Using EXPLAIN on a query is the most common way of examining how the server wants to run your query, using historical information on the data. Historical information can be good, but it is an estimate. Like a lousy estimate from a building contractor, a wrong estimate can lead to expensive overruns and things taking much longer to complete than initially planned. EXPLAIN ANALYZE runs the query to get the exact data on the performance of a query.
What is needed is a way to store a query and its performance parameters. Now that MySQL 9.0 introduced a way to store the output from EXPLAIN ANALYSE into a JSON document, we can take the first steps to save performance metrics. The information on query performance can be extracted from that JSON document and then stored for future comparison.
Which explain_json_format version?
We need to change a setting from the default to take advantage of this new addition to EXPLAIN. MySQL has two explain_json_format settings, 1 or 2. You have to use 2 to get the following to work. If not, you will be explicitly told to change the settings. By default, it is set to NULL. There are slight differences between setting 1 and 2 that I will skip, as what we want is in setting 2.
|
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 |
mysql> set explain_json_format_version=2; Query OK, 0 rows affected (0.00 sec) mysql> explain analyze format=json into @exp select * from city where District='Texas' and Population > 5000 order by NameG Query OK, 0 rows affected (0.01 sec) mysql> select @expG *************************** 1. row *************************** @exp: { "query": "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where ((`world`.`city`.`District` = 'Texas') and (`world`.`city`.`Population` > 5000)) order by `world`.`city`.`Name`", "inputs": [ { "inputs": [ { "operation": "Table scan on city", "table_name": "city", "access_type": "table", "actual_rows": 4079.0, "schema_name": "world", "actual_loops": 1, "used_columns": [ "ID", "Name", "CountryCode", "District", "Population" ], "estimated_rows": 4046.0, "actual_last_row_ms": 3.9765349999999997, "actual_first_row_ms": 0.381938, "estimated_total_cost": 410.85 } ], "condition": "((city.District = 'Texas') and (city.Population > 5000))", "operation": "Filter: ((city.District = 'Texas') and (city.Population > 5000))", "access_type": "filter", "actual_rows": 26.0, "actual_loops": 1, "estimated_rows": 4046.0, "filter_columns": [ "world.city.District", "world.city.Population" ], "actual_last_row_ms": 4.76715, "actual_first_row_ms": 4.4423010000000005, "estimated_total_cost": 410.85 } ], "operation": "Sort: city.`Name`", "query_type": "select", "access_type": "sort", "actual_rows": 26.0, "sort_fields": [ "city.`Name`" ], "actual_loops": 1, "estimated_rows": 4046.0, "actual_last_row_ms": 4.868095, "actual_first_row_ms": 4.863844, "estimated_total_cost": 410.85 } 1 row in set (0.00 sec) mysql> |
The query output with the actual statistics is now stowed away in the @exp variable. We can then use JSON functions to extract that data. For example. How many rows had to be read to supply the answer?
|
1 2 3 4 5 6 7 8 9 |
mysql> select json_pretty(json_extract(@exp,'$.actual_rows')); +-------------------------------------------------+ | json_pretty(json_extract(@exp,'$.actual_rows')) | +-------------------------------------------------+ | 26.0 | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> |
If the query were run through an MD5 hashing function, it could be used as an index. But that is a detail to be taken up later.
|
1 2 3 4 5 6 |
mysql> select json_pretty(json_extract(@exp,'$.query'))G *************************** 1. row *************************** json_pretty(json_extract(@exp,'$.query')): "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where ((`world`.`city`.`District` = 'Texas') and (`world`.`city`.`Population` > 5000)) order by `world`.`city`.`Name`" 1 row in set (0.00 sec) mysql> |
Moving data out of that variable and into the table uses regular JSON functions.
|
1 2 3 4 5 6 7 8 9 |
mysql> insert into foo (id, query) values (9,json_extract(@exp,"$.query")); Query OK, 1 row affected (0.07 sec) mysql> select query from foo where id=9G *************************** 1. row *************************** query: "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where ((`world`.`city`.`District` = 'Texas') and (`world`.`city`.`Population` > 5000)) order by `world`.`city`.`Name`" 1 row in set (0.00 sec) mysql> |
Conclusion
The above is just an idea I have kicking around. I am not sure if it is worth the time and effort to start a small proof of concept, but I would love your feedback.
Disclaimer: MySQL 9.0 is an Innovation Release that provides the public with an opportunity to see features that may or may not be included in future releases of the MySQL Community Edition. These releases are short-lived. Do not use 9.0 in production.
Percona delivers secure, tested, open source software complete with advanced features like backup, monitoring, and encryption only otherwise found in MySQL Enterprise Edition.
Discover Why Customers Choose Percona for MySQL
About the Author
David StokesDavid Stokes is a Technology Evangelist for Percona Corporation, is the author of MySQL & JSON - A Practical Programming Guide, and resides in Texas.
