A tool for automatic SQL optimization and rewriting—soar

SOAR (SQL Optimizer And Rewriter) is an automated tool for optimizing and rewriting SQL, which is currently developed and maintained by the database team of Xiaomi AI and Cloud Platform.

Peculiarity:

Cross-platform support (Linux, Mac, Windows are theoretically supported, but not fully tested)
Currently, only SQL optimizations for the MySQL syntax family protocol are supported
Heuristic-based sentence optimization is supported
Multi-column index optimization for complex queries (UPDATE, INSERT, DELETE, SELECT)
Support EXPLAIN for informative interpretation
SQL fingerprinting, compression, and beautification are supported
Multiple ALTER requests can be merged in the same table
SQL overrides for custom rules are supported

The following code is the metadata structure of the heuristic rule, which consists of 7 parts: rule code, danger level, rule summary, rule explanation, SQL example, suggestion location, and rule function. Each SQL statement will be checked one by one by hundreds of heuristic rules after syntax parsing, and the hit rule will be saved in a variable called heuristicSuggest and passed down, and the output will be combined with other optimization suggestions. The core part of this, and the part with the most code, is in heuristic.go, which contains all the functions implemented by heuristic rules. All heuristic rule lists are saved in the rules.go file.

Rule review rule metadata structure type Rule struct {Item string ‘json:”Item”‘ // Rule code Severity string ‘json:”Severity”‘ // Danger level: L[0-8], the higher the number, the higher the levelSummary string ‘json:”Summary”‘ // Rule SummaryContent string ‘ json:”Content”‘ // Rule ExplanationCase string ‘json:”Case”‘ // SQL examplePosition int ‘json:”Position”‘ // Suggested SQL character position, default 0 indicates global recommendationFunc func(*Query4Audit) Rule ‘json:”-“‘ // Function name}