Mysql optimization tool (worth using)

preface

Sora is an automated tool that optimizes and rewrites SQL. Feel pretty good, just download and learn. This tool supports more functions, can be used as our daily development of an auxiliary tool, now I recommend it to you ~ ~ ~

Github portal: 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, common SQL optimization methods include but are not limited to business layer optimization, SQL logic optimization, and index optimization. Index optimization usually achieves the purpose of SQL optimization by adjusting index or adding index. Index optimizations can often produce huge results in a short period of time. If index optimization can be transformed into a tool, standardized process, reduce the workload of manual intervention, will undoubtedly improve our work efficiency.

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

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

SOAR	sqlcheck	pt-query-advisor	SQL Advisor	Inception	sqlautoreview
Heuristic suggestion	✔ ️	✔ ️	✔ ️	❌	✔ ️	✔ ️
Index recommendations	✔ ️	❌	❌	✔ ️	❌	✔ ️
Query rewriting	✔ ️	❌	❌	❌	❌	❌
Execution plan Display	✔ ️	❌	❌	❌	❌	❌
Profiling	✔ ️	❌	❌	❌	❌	❌
Trace	✔ ️	❌	❌	❌	❌	❌
SQL Online Execution	❌	❌	❌	❌	✔ ️	❌
The data backup	❌	❌	❌	❌	✔ ️	❌

As can be seen from the figure above, the supported functions are as follows:

Cross-platform support (Linux, Mac, Windows theoretically supported, but not fully tested)
Currently, only SQL optimization for the MySQL syntax family protocol is supported
Supports statement optimization based on heuristic algorithms
Multi-column index optimization for complex queries (UPDATE, INSERT, DELETE, SELECT)
Support EXPLAIN rich information interpretation
Supports SQL fingerprinting, compression, and beautification
Multiple ALTER requests from the same table can be merged
Support SQL rewriting of custom rules

So much about it, since it is SQL optimization tool, light is useless, we still use it to see the effect.

The installation

There are two installation methods, as follows:

1. Download the binary installation package

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

It is recommended to download the latest version directly, if there is a bug.

Just download the binaries and add them to the environment variables.

Test it out:

$ echo 'select * from user'| soar execution. Darwin - amd64 (according to your own binary file name to input)
# Query: AC4262B5AF150CB5★ ★ ★ ★ ★ ★ 75分 SQL SELECT * FROM USER
#The outermost SELECT does not specify WHERE conditions** *Item:** cla.001 ** *Severity:** L4 ** *Content:** SELECT statement has no WHERE clause and may check more rows than expected (full table scan). For SELECT COUNT(\*) requests that do not require precision, SHOW TABLE STATUS or EXPLAIN are recommended instead.
## SELECT * is not recommended** *Item:** col.001 ** *Severity:** L1 **Content:** Selecting all columns using \* wildcards when the table structure changes will cause the meaning and behavior of the query to change, possibly causing the query to return more data.Copy the code

1. The source code to install

Dependent environment:

1. Go 1.10+
2. git
Copy the code

Advanced dependencies (developers only)

The mysql client version must be the same as that in the container to avoid connection failure due to authentication
Docker MySQL Server test container management
Govendor Go package management
Retool relies on external code quality static checking tool binary file management

Generate binaries:

go get -d github.com/XiaoMi/soar
cd ${GOPATH}/src/github.com/XiaoMi/soar && make
Copy the code

The generated binary file is the same as above, just put the environment variables directly, here I did not try, up to you to step on the hole ~ ~ ~

Simple to use

0. Prepare

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
Copy the code

1. Enter the SQL statement directly (not run)

$ echo "select * from users" | soar.darwin-amd64
$ # Query: 30AFCB1E1344BEBD★ ★ ★ ★ ★ ★ 75分 SQL SELECT * FROM users
#The outermost SELECT does not specify WHERE conditions** *Item:** cla.001 ** *Severity:** L4 ** *Content:** SELECT statement has no WHERE clause and may check more rows than expected (full table scan). For SELECT COUNT(\*) requests that do not require precision, SHOW TABLE STATUS or EXPLAIN are recommended instead.
## SELECT * is not recommended** *Item:** col.001 ** *Severity:** L1 **Content:** Selecting all columns using \* wildcards when the table structure changes will cause the meaning and behavior of the query to change, possibly causing the query to return more data.Copy the code

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

2. The connection`mysql`generate`EXPLAIN`Analysis report

You can configure the mysql configuration in the configuration file as follows:

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

test-dsn:
    addr:     127.0. 01.:3306
    schema:   asong
    user:     root
    password: root1997
    disable:  false
Copy the code

With that configured, let’s 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分 ' 'SQL SELECT id, username, nickname, PASSWORD, salt, avatar, uptime FROM users WHERE username = 'asong1111' ```
## the Explain information| id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability |  Extra | |---|---|---|---|---|---|---|---|---|---|---|---|---| | 1 | SIMPLE | *users* | NULL | const | username | The username | 258 | const | 1 | ☠ ️ 100.00% * * * * | ☠ ️ O (n) * * * * | NULL |


### Explain information

#### SelectType Information interpretation** *SIMPLE**: SIMPLE SELECT(without using UNION, subquery, etc.).
#### Type** *const**: const is used when comparing the PRIMARY KEY with a constant value. SELECT * FROM TBL WHERE col = 1Copy the code

EXPLAIN information analysis reports are included in the results. This is friendly for beginners, because we are not familiar with the fields of Explain parsing, with it we can perfectly analyze SQL problems, isn’t it great?

3. Grammar check

The SOAR tool does not only analyze SQL statements, it also checks the syntax of SQL statements to find problems. Here is 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)
Copy the code

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

4. SQL beautification

In our daily development, we often look at the code written by others, because the level is different, so some SQL statements will be very messy, so this tool comes in handy, we can make our SQL statements beautiful, easier for us to understand oh.

$ 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';
Copy the code

Doesn’t that make it a little bit more intuitive?

At the end

Since I’m new to this tool, there are more ways I haven’t found out yet. Github Portal: github.com/XiaoMi/soar…

Well, that’s the end of this article, and we’ll see you next time. I hope to be useful to you, and welcome to point out the wrong place, can add my Golang communication group, we learn to communicate together.

At the end, I will send you a small welfare. Recently, I was reading the book [micro-service architecture design mode], which is very good. I also collected a PDF, which can be downloaded by myself if you need it. Access: Follow the public account: [Golang Dreamworks], background reply: [micro service], can be obtained.

I have translated a GIN Chinese document, which will be maintained regularly. If you need it, you can download it by replying to [GIN] in the background.

Translated a Machinery Chinese document, will be regularly maintained, there is a need for friends to respond to the background [Machinery] can be obtained.

I am Asong, an ordinary program ape, let GI gradually become stronger together. I built my owngolangCommunication group, you need to add mevxI’ll pull you into the group. We welcome your attention, and we’ll see you next time

Mysql optimization tool (worth using)

preface

background

The installation

Simple to use

0. Prepare

1. Enter the SQL statement directly (not run)

2. The connectionmysqlgenerateEXPLAINAnalysis report

3. Grammar check

4. SQL beautification

At the end

Related Posts

Slow pagination scenarios? MySQL > select * from user

Why is Redis a single thread, and high concurrency fast three reasons explain

Serverless application engine SAE and Spectral testing to jointly fight the epidemic

2. The connection`mysql`generate`EXPLAIN`Analysis report