SQL Review via Execution Plan Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional SQL review methods in databases with incomplete data dictionaries, such as MySQL and Postgres, are limited in their ability to perform deep reviews of SQL sentences, leading to inaccurate assessments and potential performance issues.
Innovation Solution
A method and device for reviewing SQL sentences involve obtaining slow log data, extracting and parsing SQL sentences, and reviewing them against preset templates to generate a comprehensive review result set, enabling deeper analysis of execution plans and grammar.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If conventional SQL review methods are used in databases with incomplete data dictionaries, then the review process is simple and fast, but the review accuracy is insufficient and cannot perform deep analysis of SQL execution plans
Solution Approach 1:
The patent introduces an execution plan as an intermediary element between the SQL statement and the review process. By obtaining and analyzing the execution plan generated by the database, the review system can indirectly assess SQL performance without requiring complete data dictionary information. The execution plan serves as a mediator that reveals actual query execution behavior, including table scans, index usage, and join operations, enabling deep review capabilities while maintaining compatibility with databases that have incomplete metadata.
2Loss of information
If simple SQL script parsing is used for review, then the review process is quick and easy to implement, but the review depth is limited and cannot identify execution plan issues
Solution Approach 1:
The patent applies preliminary action by obtaining the execution plan before the actual SQL review process. The execution plan is generated in advance by submitting the SQL statement to the database's query optimizer, which analyzes the SQL and produces a detailed execution roadmap. This pre-computed execution plan contains critical information about table access methods, index usage, join strategies, and estimated costs, allowing the review system to perform comprehensive analysis without time-consuming real-time execution testing.
3Reliability
If deep SQL review including execution plan analysis is performed, then review accuracy improves, but the complexity of the review process increases
Solution Approach 1:
The patent segments the SQL review process into distinct modular components: (1) SQL statement parsing and structure analysis, (2) execution plan generation by the database, (3) execution plan interpretation and extraction of key metrics, (4) review rule application against parsed SQL and extracted execution characteristics, and (5) review result compilation. Each module handles a specific aspect of the review, reducing overall system complexity while enabling comprehensive analysis. The execution plan analysis is further segmented into identifying table scan operations, index usage patterns, join methods, and cost estimates, allowing targeted review without overwhelming complexity.
Data Source
AI summary
A method of reviewing SQL includes: obtaining slow log data; extracting a SQL sentence to be reviewed and basic information matched with the SQL sentence from the slow log data; identifying and parsing the SQL sentence to obtain a parsed result; reviewing the parsed result and/or the basic information in accordance with review items in a preset review template one by one to obtain a review result; and generating a review result set according to the review result.