Mysql優(yōu)化神器(推薦)

更新時(shí)間：2020年12月21日 09:47:05 作者：Sunshine-松

這篇文章主要介紹了Mysql優(yōu)化神器(推薦)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

今天逛github時(shí)，發(fā)現(xiàn)了這款對(duì) SQL 進(jìn)行優(yōu)化和改寫(xiě)的自動(dòng)化工具sora。感覺(jué)挺不錯(cuò)的，就下載學(xué)習(xí)了一下。這個(gè)工具支持的功能比較多，可以作為我們?nèi)粘ｉ_(kāi)發(fā)中的一款輔助工具，現(xiàn)在我就把它推薦給你們～～～

github傳送門(mén)：https://github.com/XiaoMi/soar

背景

在我們?nèi)粘ｉ_(kāi)發(fā)中，優(yōu)化SQL總是我們?nèi)粘ｉ_(kāi)發(fā)任務(wù)之一。例行 SQL 優(yōu)化，不僅可以提升程序性能，還能夠降低線(xiàn)上故障的概率。

目前常用的 SQL 優(yōu)化方式包括但不限于：業(yè)務(wù)層優(yōu)化、SQL邏輯優(yōu)化、索引優(yōu)化等。其中索引優(yōu)化通常通過(guò)調(diào)整索引或新增索引從而達(dá)到 SQL 優(yōu)化的目的。索引優(yōu)化往往可以在短時(shí)間內(nèi)產(chǎn)生非常巨大的效果。如果能夠?qū)⑺饕齼?yōu)化轉(zhuǎn)化成工具化、標(biāo)準(zhǔn)化的流程，減少人工介入的工作量，無(wú)疑會(huì)大大提高我們的工作效率。

SOAR(SQL Optimizer And Rewriter) 是一個(gè)對(duì) SQL 進(jìn)行優(yōu)化和改寫(xiě)的自動(dòng)化工具。由小米人工智能與云平臺(tái)的數(shù)據(jù)庫(kù)團(tuán)隊(duì)開(kāi)發(fā)與維護(hù)。

與業(yè)內(nèi)其他優(yōu)秀產(chǎn)品對(duì)比如下：

SOAR	sqlcheck	pt-query-advisor	SQL Advisor	Inception	sqlautoreview
啟發(fā)式建議	✔️	✔️	✔️	❌	✔️	✔️
索引建議	✔️	❌	❌	✔️	❌	✔️
查詢(xún)重寫(xiě)	✔️	❌	❌	❌	❌	❌
執(zhí)行計(jì)劃展示	✔️	❌	❌	❌	❌	❌
Profiling	✔️	❌	❌	❌	❌	❌
Trace	✔️	❌	❌	❌	❌	❌
SQL在線(xiàn)執(zhí)行	❌	❌	❌	❌	✔️	❌
數(shù)據(jù)備份	❌	❌	❌	❌	✔️	❌

從上圖可以看出，支持的功能豐富，其功能特點(diǎn)如下：

跨平臺(tái)支持（支持 Linux, Mac 環(huán)境，Windows 環(huán)境理論上也支持，不過(guò)未全面測(cè)試）
目前只支持 MySQL 語(yǔ)法族協(xié)議的 SQL 優(yōu)化
支持基于啟發(fā)式算法的語(yǔ)句優(yōu)化
支持復(fù)雜查詢(xún)的多列索引優(yōu)化（UPDATE, INSERT, DELETE, SELECT）
支持 EXPLAIN 信息豐富解讀
支持 SQL 指紋、壓縮和美化
支持同一張表多條 ALTER 請(qǐng)求合并
支持自定義規(guī)則的 SQL 改寫(xiě)

就介紹這么多吧，既然是SQL優(yōu)化工具，光說(shuō)是沒(méi)有用的，我們還是先用起來(lái)看看效果吧。

安裝

這里有兩種安裝方式，如下：

下載二進(jìn)制安裝包

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

這里建議直接下載最新版，要不會(huì)有bug。

下載好的二進(jìn)制文件添加到環(huán)境變量中即可(不會(huì)的谷歌一下吧，這里就不講了)。

測(cè)試一下：

$ echo 'select * from user' | soar.darwin-amd64(根據(jù)你自己的二進(jìn)制文件名來(lái)輸入)
# Query: AC4262B5AF150CB5
 
★ ★ ★ ☆ ☆ 75分
 
```sql
SELECT
 *
FROM
 USER
```
 
## 最外層 SELECT 未指定 WHERE 條件
 
* **Item:** CLA.001
 
* **Severity:** L4
 
* **Content:** SELECT 語(yǔ)句沒(méi)有 WHERE 子句，可能檢查比預(yù)期更多的行(全表掃描)。對(duì)于 SELECT COUNT(\*) 類(lèi)型的請(qǐng)求如果不要求精度，建議使用 SHOW TABLE STATUS 或 EXPLAIN 替代。
 
## 不建議使用 SELECT * 類(lèi)型查詢(xún)
 
* **Item:** COL.001
 
* **Severity:** L1
 
* **Content:** 當(dāng)表結(jié)構(gòu)變更時(shí)，使用 \* 通配符選擇所有列將導(dǎo)致查詢(xún)的含義和行為會(huì)發(fā)生更改，可能導(dǎo)致查詢(xún)返回更多的數(shù)據(jù)。

源碼安裝

依賴(lài)環(huán)境：

1. Go 1.10+
2. git

高級(jí)依賴(lài)（僅面向開(kāi)發(fā)人員）

mysql 客戶(hù)端版本需要與容器中MySQL版本相同，避免出現(xiàn)由于認(rèn)證原因?qū)е聼o(wú)法連接問(wèn)題
docker MySQL Server測(cè)試容器管理
govendor Go包管理
retool 依賴(lài)外部代碼質(zhì)量靜態(tài)檢查工具二進(jìn)制文件管理

生成二進(jìn)制文件：

go get -d github.com/XiaoMi/soar
cd ${GOPATH}/src/github.com/XiaoMi/soar && make
生成的二進(jìn)制文件與上面一樣，直接放入環(huán)境變量即可，這里我沒(méi)有嘗試，靠你們自己踩坑了呦～～～

簡(jiǎn)單使用

0. 前置準(zhǔn)備

準(zhǔn)備一個(gè)table，如下：

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. 直接輸入sql語(yǔ)句(不運(yùn)行)

$ echo "select * from users" | soar.darwin-amd64
$ # Query: 30AFCB1E1344BEBD
 
★ ★ ★ ☆ ☆ 75分
 
```sql
SELECT
 *
FROM
 users
```
## 最外層 SELECT 未指定 WHERE 條件
 
* **Item:** CLA.001
 
* **Severity:** L4
 
* **Content:** SELECT 語(yǔ)句沒(méi)有 WHERE 子句，可能檢查比預(yù)期更多的行(全表掃描)。對(duì)于 SELECT COUNT(\*) 類(lèi)型的請(qǐng)求如果不要求精度，建議使用 SHOW TABLE STATUS 或 EXPLAIN 替代。
 
## 不建議使用 SELECT * 類(lèi)型查詢(xún)
 
* **Item:** COL.001
 
* **Severity:** L1
 
* **Content:** 當(dāng)表結(jié)構(gòu)變更時(shí)，使用 \* 通配符選擇所有列將導(dǎo)致查詢(xún)的含義和行為會(huì)發(fā)生更改，可能導(dǎo)致查詢(xún)返回更多的數(shù)據(jù)。

現(xiàn)在是完全根據(jù)SQL語(yǔ)句進(jìn)行分析的，因?yàn)闆](méi)有連接到mysql?？梢钥吹剑o出的報(bào)告也很詳細(xì)，但是只是空殼子，僅憑SQL語(yǔ)句給出的分析并不是準(zhǔn)確的，所以我們開(kāi)始接下來(lái)的應(yīng)用。

2. 連接mysql生成EXPLAIN分析報(bào)告

我們可以在配置文件中配置好mysql相關(guān)的配置，操作如下：

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

配置好了，我們來(lái)實(shí)踐一下子吧：

$ echo "SELECT id,username,nickname,password,salt,avatar,uptime FROM users WHERE username = 'asong1111'" | soar.darwin-amd64 -test-dsn="root:root1997@127.0.0.1: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'
```
 
## Explain信息
 
| 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信息解讀
 
#### SelectType信息解讀
 
* **SIMPLE**: 簡(jiǎn)單SELECT(不使用UNION或子查詢(xún)等).
 
#### Type信息解讀
 
* **const**: const用于使用常數(shù)值比較PRIMARY KEY時(shí), 當(dāng)查詢(xún)的表僅有一行時(shí), 使用system. 例:SELECT * FROM tbl WHERE col = 1.

這回結(jié)果中多了EXPLAIN信息分析報(bào)告。這對(duì)于剛開(kāi)始入門(mén)的小伙伴們是友好的，因?yàn)槲覀儗?duì)Explain解析的字段并不熟悉，有了它我們可以完美的分析SQL中的問(wèn)題，是不是很棒。

3. 語(yǔ)法檢查

soar工具不僅僅可以進(jìn)行sql語(yǔ)句分析，還可以進(jìn)行對(duì)sql語(yǔ)法進(jìn)行檢查，找出其中的問(wèn)題，來(lái)看個(gè)例子：

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

這里select關(guān)鍵字少了一個(gè)t，運(yùn)行該指令幫助我們一下就定位了問(wèn)題，當(dāng)我們的sql語(yǔ)句很長(zhǎng)時(shí)，就可以使用該指令來(lái)輔助我們檢查SQL語(yǔ)句是否正確。

4. SQL美化

我們?nèi)粘ｉ_(kāi)發(fā)時(shí)，經(jīng)常會(huì)看其他人寫(xiě)的代碼，因?yàn)樗讲灰粯?，所以有些SQL語(yǔ)句會(huì)寫(xiě)的很亂，所以這個(gè)工具就派上用場(chǎng)了，我們可以把我們的SQL語(yǔ)句變得漂亮一些，更容易我們理解哦。

$ 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';

這樣看起來(lái)是不是更直觀(guān)了呢～～。

結(jié)尾

因?yàn)槲乙膊攀莿偸褂眠@個(gè)工具，更多的玩法我還沒(méi)有發(fā)現(xiàn)，以后補(bǔ)充。更多玩法可以自己研究一下，github傳送門(mén)：https://github.com/XiaoMi/soar。官方文檔其實(shí)很粗糙，更多方法解鎖還要靠自己研究，畢竟源碼已經(jīng)給我們了，對(duì)于學(xué)習(xí)go也有一定幫助，當(dāng)作一個(gè)小項(xiàng)目慢慢優(yōu)化豈不是更好呢～～。

到此這篇關(guān)于Mysql優(yōu)化神器(推薦)的文章就介紹到這了,更多相關(guān)Mysql優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: