Plan Visualization for MySQL
Copyright © 2022 PawSQL
Overview
The execution plan is an internal execution strategy generated by the database when executing SQL queries. It describes how the SQL is executed step-by-step internally in the database and is an important basis for diagnosing SQL performance issues and tuning. As the complexity of database applications increases, SQL execution plans become more and more complicated along with the SQL. Relying on plaintext execution plans makes it very challenging to grasp the overall execution plan, locate key paths and bottlenecks, bringing great difficulties to DBAs' performance tuning work.
The visual execution plan tool intuitively presents the execution plan in a flowchart, clearly showing the plan structure. It uses visual elements to highlight key operations and paths, while associating SQL statement source code for integrated analysis. This can greatly improve the efficiency for DBAs to understand and analyze execution plans, quickly locate performance bottlenecks, and perform precise optimization on SQL or indexes.
Commercial databases usually develop their own execution plan visualization tools to help database users understand how SQL is executed and quickly locate SQL performance issues. However, open source databases, despite their huge user base, have very few execution plan visualization tools available. In particular, there are currently no suitable MySQL execution plan visualization tools in the market.
PawSQL Plan Visualizer (PPV)
PawSQL's execution plan visualization tool PawSQL Plan Visualizer (PawEV) is developed based on the open source components pev/pev2. Its main functions include:
Support for multiple databases, including MySQL/PostgreSQL/openGauss/Oracle, etc.
Support for multiple input formats, such as MySQL's json/tree/analyze formats, Oracle's form formats
Interactive analysis based on highlighting by execution time, cost, row count
Metrics statistics based on database objects and operators
PPV's Support for MySQL
Supported Versions and Formats
| MySQL Version | Supported Input Formats | Displayed Information |
|---|---|---|
| >=5.6, <8.0.16 | format=json | Estimated cost/row count |
| >=8.0.16, <8.0.18 | format=json/format=tree | Estimated cost/row count |
| >=8.0.18 | format=json/format=tree/analyze | Actual execution time/Actual row count/Estimated cost/Estimated row count |
Creating Visualized Plans
By submitting the text output of MySQL execution plans to PawSQL Explain Visualizer, PawSQL clearly displays the execution plans in a tree structure.
The icons in the upper right corner of the operators provide hints on some issues, such as longer execution time, higher estimated costs, significant deviations between estimated and actual number of rows executed, etc. With these hints, DBAs can easily locate problematic modules for detailed troubleshooting.
Interactive Analysis
Click the summary information in the header to quickly locate nodes with the longest execution time or highest cost.
After expanding each operator, it will show its specific execution time, number of rows, and other information.
Clicking the time, cost, row count will display the percentage of each node in the form of a progress bar.
Statistics Based on Objects and Operators
The right side of the page contains statistical analysis based on objects (tables/indexes) and operators in the execution plan, including execution time, estimated costs, etc.
Integration with Optimization Features
For optimization tasks with performance validation enabled, PawSQL will obtain the execution plans before and after optimization, and visually compare them using PEV.
About PawSQL
PawSQL focuses on automating and intelligizing database performance optimization, supporting MySQL, PostgreSQL, openGauss, Oracle etc. The SQL optimization products provided include:
PawSQL Cloud, online automated SQL optimization tool, supports SQL review, intelligent query rewriting, cost-based index recommendation, suitable for database administrators and data application developers.
PawSQL Advisor, IntelliJ plugin, suitable for data application developers, can be installed by searching for "PawSQL Advisor" in IDEA/DataGrip application market.
PawSQL Engine, the backend optimization engine of PawSQL products, can be independently installed and deployed in docker image, and provides SQL optimization services through http/json interfaces.
Contact Us
Website: https://docs.pawsql.com
Email: service@pawsql.com
Twitter: https://twitter.com/pawsql
Scan to follow PawSQL on Wechat
