Mysql optimization tool (recommended)

Preface

While browsing GitHub today, I found this automated tool called sora for optimizing and rewriting SQL. I thought it was pretty good, so I downloaded it and studied it. This tool supports many functions and can be used as an auxiliary tool in our daily development. Now I recommend it to you~~~

Github portal: https://github.com/XiaoMi/soar

background

In our daily development, optimizing SQL is always one of our daily development tasks. Routine SQL optimization can not only improve program performance, but also reduce the probability of online failures.

Currently commonly used SQL optimization methods include but are not limited to: business layer optimization, SQL logic optimization, index optimization, etc. Index optimization usually achieves the purpose of SQL optimization by adjusting indexes or adding new indexes. Index optimization can often produce huge results in a short period of time. If we can transform index optimization into a tool-based and standardized process and reduce the workload of manual intervention, it will undoubtedly greatly improve our work efficiency.

SOAR (SQL Optimizer And Rewriter) is an automated tool for optimizing and rewriting SQL. Developed and maintained by the database team of Xiaomi's artificial intelligence and cloud platform.

The comparison with other excellent products in the industry is as follows:

As can be seen from the above figure, the supported functions are rich, and its functional features are as follows:

• Cross-platform support (supports Linux, Mac environments, and theoretically also Windows environments, but not fully tested)
• Currently only supports SQL optimization of MySQL syntax family protocol
• Supports statement optimization based on heuristic algorithms
• Supports multi-column index optimization for complex queries (UPDATE, INSERT, DELETE, SELECT)
• Support EXPLAIN information-rich interpretation
• Support SQL fingerprinting, compression and beautification
• Supports merging multiple ALTER requests for the same table
• Support SQL rewriting of custom rules

That’s all I have to say. Since it is a SQL optimization tool, it is useless to just talk about it. Let’s use it first and see the effect.

Install

There are two ways to install it, as follows:

Download the binary installation package

$ wget https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.linux-amd64 -O soar
chmod a+x soar

It is recommended to download the latest version directly, otherwise there will be bugs.

Just add the downloaded binary file to the environment variable (Google it if you don’t know how, I won’t explain it here).

Test it out:

$ echo 'select * from user' | soar.darwin-amd64 (enter according to your own binary file name)
# Query: AC4262B5AF150CB5
 
★ ★ ★ ☆ ☆ 75 points ```sql
SELECT
 *
FROM
 USER
```
 
## Outermost SELECT does not specify WHERE condition* **Item:** CLA.001
 
* **Severity:** L4
 
* **Content:** The SELECT statement has no WHERE clause and may examine more rows than expected (full table scan). For SELECT COUNT(\*) type requests, if precision is not required, it is recommended to use SHOW TABLE STATUS or EXPLAIN instead.
 
## It is not recommended to use SELECT * Type query * **Item:** COL.001
 
* **Severity:** L1
 
* **Content:** Using the \* wildcard to select all columns will cause the meaning and behavior of queries to change when the table structure changes, possibly causing the query to return more data.

Source installation

Dependencies:

1. Go 1.10+
2. git

Advanced Dependencies (for developers only)

• The mysql client version needs to be the same as the MySQL version in the container to avoid connection failures due to authentication reasons
• Docker MySQL Server test container management
• govendor Go package management
• retool relies on external code quality static checking tool binary file management

Generate binary files:

go get -d github.com/XiaoMi/soar
cd ${GOPATH}/src/github.com/XiaoMi/soar && make
The generated binary file is the same as above, just put it into the environment variable. I didn't try it here, so you have to figure it out by yourself~~~

Easy to use

0. Pre-preparation

Prepare a table as follows:

CREATE TABLE `users` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `username` varchar(64) NOT NULL DEFAULT '',
 `nickname` varchar(255) DEFAULT '',
 `password` varchar(256) NOT NULL DEFAULT '',
 `salt` varchar(48) NOT NULL DEFAULT '',
 `avatar` varchar(128) DEFAULT NULL,
 `uptime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

1. Enter the SQL statement directly (do not run)

$ echo "select * from users" | soar.darwin-amd64
$ # Query: 30AFCB1E1344BEBD
 
★ ★ ★ ☆ ☆ 75 points ```sql
SELECT
 *
FROM
 users
```
## Outermost SELECT does not specify WHERE condition* **Item:** CLA.001
 
* **Severity:** L4
 
* **Content:** The SELECT statement has no WHERE clause and may examine more rows than expected (full table scan). For SELECT COUNT(\*) type requests, if precision is not required, it is recommended to use SHOW TABLE STATUS or EXPLAIN instead.
 
## It is not recommended to use SELECT * Type query * **Item:** COL.001
 
* **Severity:** L1
 
* **Content:** Using the \* wildcard to select all columns will cause the meaning and behavior of queries to change when the table structure changes, possibly causing the query to return more data.

Now the analysis is done entirely based on SQL statements because there is no connection to MySQL. As you can see, the report given is also very detailed, but it is just an empty shell. The analysis given by SQL statements alone is not accurate, so we start the next application.

2. Connect to MySQL to generate EXPLAIN analysis report

We can configure mysql related configuration in the configuration file as follows:

vi soar.yaml
# yaml format config file
online-dsn:
  addr: 127.0.0.1:3306
  schema: asong
  user: root
  password: root1997
  disable: false
 
test-dsn:
  addr: 127.0.0.1:3306
  schema: asong
  user: root
  password: root1997
  disable: false

Now that the configuration is done, let's put it into practice:

$ echo "SELECT id,username,nickname,password,salt,avatar,uptime FROM users WHERE username = 'asong1111'" | soar.darwin-amd64 -test-dsn="root:[email protected]:3306/asong" -allow-online-as-test -log-output=soar.log
$ # Query: D12A420193AD1674
 
★ ★ ★ ★ ★ 100 points ```sql
SELECT
 id, username, nickname, PASSWORD, salt, avatar, uptime
FROM
 users
WHERE
 username = 'asong1111'
```
 
## Explain information | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | scalability | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | *users* | NULL | const | username | username | 258 | const | 1 | ☠️ **100.00%** | ☠️ **O(n)** | NULL |
 
### Explain information interpretation#### SelectType information interpretation* **SIMPLE**: Simple SELECT (does not use UNION or subquery, etc.).
 
#### Type information interpretation* **const**: const is used to compare PRIMARY KEY using constant values. When the query table has only one row, use system. Example: SELECT * FROM tbl WHERE col = 1.

This time, the results include an EXPLAIN information analysis report. This is friendly to beginners, because we are not familiar with the fields parsed by Explain. With it, we can perfectly analyze the problems in SQL, isn't it great?

3. Grammar Check

The soar tool can not only analyze SQL statements, but also check SQL syntax to find problems. Let's take a look at an example:

$ echo "selec * from users" | soar.darwin-amd64 -only-syntax-check
At SQL 1 : line 1 column 5 near "selec * from users" (total length 18)

Here, the select keyword is missing a t. Running this command helps us locate the problem immediately. When our SQL statement is very long, we can use this command to help us check whether the SQL statement is correct.

4. SQL Beautification

In our daily development, we often look at the codes written by others. Because of the different levels, some SQL statements are written very messy, so this tool comes in handy. We can make our SQL statements more beautiful and easier for us to understand.

$ echo "SELECT id,username,nickname,password,salt,avatar,uptime FROM users WHERE username = 'asong1111'" | soar.darwin-amd64 -report-type=pretty
 
SELECT
 id, username, nickname, PASSWORD, salt, avatar, uptime
FROM
 users
WHERE
 username = 'asong1111';

Doesn’t this seem more intuitive?

Conclusion

Because I have just started using this tool, I haven’t discovered more ways to play it yet, and I will add more later. You can study more gameplay by yourself, github portal: https://github.com/XiaoMi/soar. The official documentation is actually very rough, and you have to rely on your own research to unlock more methods. After all, the source code has been given to us, and it is also helpful for learning Go. Wouldn't it be better to treat it as a small project and optimize it slowly?

This is the end of this article about MySQL optimization tool (recommended). For more relevant MySQL optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!