Shulou(Shulou.com)06/01 Report--

Recently, Xiaomi officially announced open source SOAR.

As of today, the project has received 350 "star" and 44 "fork" (GitHub project address: https://github.com/XiaoMi/soar)

Introduction to SOAR

SOAR, or SQL Optimizer And Rewriter, is an intelligent optimization and rewriting tool for SQL, produced by Xiaomi operation and maintenance DBA team

SOAR architecture

SOAR is mainly composed of five modules: syntax parser, integrated environment, optimization suggestion, rewriting logic, and toolset.

Syntax parsing and grammar checking

After a SQL is passed to SOAR in the form of files, standard input or command-line parameters, it first enters the syntax parser and chooses vitess's syntax parsing library as the syntax parsing library of SOAR. However, with the increasing demand at any time, we find that some complex requirements are more complex to use vitess syntax parsing to achieve. So refer to other database products of IOM, so introduce the syntax parser of TiDB as a supplement. Later, it was found that there were still some blind spots in these two parsing libraries, so the return result of MySQL execution was introduced as a supplement to the SQL dialect of Duoduo version. You can also see that the implementation of SOAR is loose and pluggable here in the syntax parser. SOAR does not directly maintain a huge grammar parsing library, it integrates a variety of excellent syntax parsing libraries together, each drawing on its strengths.

Integrated environment

The integrated environment can be divided into online environment and test environment, which are used to solve the SQL optimization needs of users in different scenarios. A common situation is that the existing table structure needs to optimize the query SQL scenario. The table structure and sufficient sampling data can be exported from the online environment to the test environment, where you can safely perform a variety of high-risk operations without worrying about data corruption. Another common situation is to build a completely new database that needs to verify whether there is a possibility of optimization in the provided data dictionary. In this case, it's quite possible that you don't need to know where the online environment is, you just want to try it first, and change it right if you report it wrong. Of course, there are more combinations of scenario requirements, which will be described in a single category in the integrated environment.

Optimization suggestion

At present, the optimization suggestions that SOAR can provide are based on heuristic rules (usually called experience), index optimization based on index optimization algorithm, and interpretation based on EXPLAIN information.

Rewrite logic

The optimization suggestions mentioned above are the main features of the early implementation, the early features are only on the suggestions, and some beginner users will not necessarily rewrite the suggestions when they see them. In order to further simplify the cost of SQL optimization, SOAR further exploits the function of automatic SQL rewriting. SQL equivalent rewriting in dozens of common scenarios is now available, but there is still a lot of room for improvement compared with SQL optimization recommendations. The function and logic of this section will be described in detail in the chapter of rewriting logic.

Tool set

In addition to SQL optimization and rewriting, in order to facilitate users and beautify the output presentation, SOAR also provides some auxiliary gadgets, such as markdown to HTML tool, SQL formatting output tool and so on. You can find how to use these gadgets in common commands.

Functional features of SOAR

The functional features of SOAR are as follows:

● cross-platform support (supports Linux, Mac and Windows environments in theory, but has not been fully tested)

● supports sentence optimization based on heuristic algorithm

● supports multi-column index optimization for complex queries (UPDATE, INSERT, DELETE, SELECT)

● supports rich interpretation of EXPLAIN information.

● supports SQL fingerprinting, compression and beautification

● supports merging of multiple ALTER requests in the same table

● supports SQL rewriting of custom rules

In addition, compared with other excellent products in the industry, Xiaomi's SOAR also has great advantages:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.