MySQL--------SQL優(yōu)化審核工具實(shí)戰(zhàn)

1. 背景

   SQLAdvisor是由美團(tuán)點(diǎn)評(píng)公司技術(shù)工程部DBA團(tuán)隊(duì)（北京）開發(fā)維護(hù)的一個(gè)分析SQL給出索引優(yōu)化建議的工具。它基于MySQL原生態(tài)詞法解析，結(jié)合分析SQL中的where條件、聚合條件、多表Join關(guān)系 給出索引優(yōu)化建議。目前SQLAdvisor在美團(tuán)點(diǎn)評(píng)內(nèi)部廣泛應(yīng)用，公司內(nèi)部對(duì)SQLAdvisor的開發(fā)全面轉(zhuǎn)到github上，開源和內(nèi)部使用保持一致。

    在數(shù)據(jù)庫運(yùn)維過程中，優(yōu)化SQL是業(yè)務(wù)團(tuán)隊(duì)與DBA團(tuán)隊(duì)的日常任務(wù)。例行SQL優(yōu)化，不僅可以提升程序性能，還能夠降低線上故障的概率。

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

２. 架構(gòu)流程圖

3. 環(huán)境

   * os version

[root@SQLAdvisor ~]# cat /etc/redhat-release 
CentOS release 6.8 (Final)

[root@SQLAdvisor ~]# uname -r
2.6.32-642.3.1.el6.x86_64

[root@SQLAdvisor ~]# uname -n
SQLAdvisor

[root@SQLAdvisor ~]# getenforce 
Disabled

   * mysql version

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.7.18 |
+---------------+--------+
1 row in set (0.00 sec)

4. 安裝SQLAdvisor

   * 獲取最新代碼

[root@SQLAdvisor ~]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
Initialized empty Git repository in /root/SQLAdvisor/.git/
remote: Counting objects: 1460, done.
remote: Total 1460 (delta 0), reused 0 (delta 0), pack-reused 1460
Receiving objects: 100% (1460/1460), 19.92 MiB | 209 KiB/s, done.
Resolving deltas: 100% (368/368), done.

   * 安裝依賴項(xiàng)

[root@SQLAdvisor ~]# yum -y  install cmake libaio-devel libffi-devel glib2 glib2-devel

[root@SQLAdvisor ~]# yum -y  install  http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
 
[root@SQLAdvisor ~]# yum -y  install Percona-Server-shared-56 

[root@SQLAdvisor ~]# ln -s /usr/lib64/libperconaserverclient_r.so.18 /usr/lib64/libperconaserverclient_r.so

   * 編譯依賴項(xiàng)sqlparser

[root@SQLAdvisor ~]# cd SQLAdvisor/

[root@SQLAdvisor SQLAdvisor]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

[root@SQLAdvisor SQLAdvisor]# make && make install

   * 安裝SQLAdvisor

[root@SQLAdvisor SQLAdvisor]# cd sqladvisor/

[root@SQLAdvisor sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./

[root@SQLAdvisor sqladvisor]# make

   * SQLAdvisor Info

[root@SQLAdvisor sqladvisor]# ./sqladvisor --help
Usage:
  sqladvisor [OPTION...] sqladvisor

SQL Advisor Summary

Help Options:
  -?, --help              Show help options

Application Options:
  -f, --defaults-file     sqls file
  -u, --username          username
  -p, --password          password
  -P, --port              port
  -h, --host              host
  -d, --dbname            database name
  -q, --sqls              sqls
  -v, --verbose           1:output logs 0:output nothing

5. 測(cè)試

   * 生成測(cè)試數(shù)據(jù)表

mysql> create database test1 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> create table user(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(64) NOT NULL,
    -> age int,
    -> sex int
    -> )ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.13 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| sex   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

   * 生成測(cè)試數(shù)據(jù)

mysql> insert into user(name,age, sex) select 'lisea', 25, 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into user(name,age, sex) select concat(name, '1'), age+1, sex+1 from user;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into user(name,age, sex) select concat(name, '2'), age+2, sex from user;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into user(name,age, sex) select concat(name, '3'), age+2, sex from user;
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

.
.
.
.
.
.

mysql> insert into user(name,age, sex) select concat(name, '10'), age+2, sex from user;
Query OK, 512 rows affected (0.24 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> insert into user(name,age, sex) select concat(name, '11'), age+4, sex from user;
Query OK, 1024 rows affected (0.79 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
|     2048 |
+----------+
1 row in set (0.01 sec)

   * 命令行傳參調(diào)用測(cè)試SQLAdvisor　[查找非索引行]

[root@SQLAdvisor sqladvisor]# ./sqladvisor -h 127.0.0.1  -P 3306  -u root -p '123' -d test1 -q "select * from user where name = 'lisea'" -v 1
2017-10-27 05:35:49 34059 [Note] 第1步: 對(duì)SQL解析優(yōu)化之后得到的SQL:select `*` AS `*` from `test1`.`user` where (`name` = 'lisea') 

2017-10-27 05:35:49 34059 [Note] 第2步：開始解析where中的條件:(`name` = 'lisea') 

2017-10-27 05:35:49 34059 [Note] show index from user 

2017-10-27 05:35:49 34059 [Note] show table status like 'user' 

2017-10-27 05:35:49 34059 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (`name` = 'lisea')  

2017-10-27 05:35:49 34059 [Note] 第3步：表user的行數(shù):2048,limit行數(shù):1024,得到where條件中(`name` = 'lisea')的選擇度:1024 

2017-10-27 05:35:49 34059 [Note] 第4步：開始驗(yàn)證 字段name是不是主鍵。表名:user 

2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 

2017-10-27 05:35:49 34059 [Note] 第5步：字段name不是主鍵。表名:user 

2017-10-27 05:35:49 34059 [Note] 第6步：開始驗(yàn)證 字段name是不是主鍵。表名:user 

2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 

2017-10-27 05:35:49 34059 [Note] 第7步：字段name不是主鍵。表名:user 

2017-10-27 05:35:49 34059 [Note] 第8步：開始驗(yàn)證表中是否已存在相關(guān)索引。表名:user, 字段名:name, 在索引中的位置:1 

2017-10-27 05:35:49 34059 [Note] show index from user where Column_name ='name' and Seq_in_index =1 

2017-10-27 05:35:49 34059 [Note] 第9步：開始輸出表user索引優(yōu)化建議: 

2017-10-27 05:35:49 34059 [Note] Create_Index_SQL：alter table user add index idx_name(name) 

2017-10-27 05:35:49 34059 [Note] 第10步: SQLAdvisor結(jié)束!

   * 命令行傳參調(diào)用測(cè)試SQLAdvisor　[查找索引行]

[root@SQLAdvisor sqladvisor]# ./sqladvisor -h 127.0.0.1  -P 3306  -u root -p '123' -d test1 -q "select * from user where id = 1" -v 1
2017-10-27 05:36:46 34062 [Note] 第1步: 對(duì)SQL解析優(yōu)化之后得到的SQL:select `*` AS `*` from `test1`.`user` where (`id` = 1) 

2017-10-27 05:36:46 34062 [Note] 第2步：開始解析where中的條件:(`id` = 1) 

2017-10-27 05:36:46 34062 [Note] show index from user 

2017-10-27 05:36:46 34062 [Note] show table status like 'user' 

2017-10-27 05:36:46 34062 [Note] select count(*) from ( select `id` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (`id` = 1)  

2017-10-27 05:36:46 34062 [Note] 第3步：表user的行數(shù):2048,limit行數(shù):1024,得到where條件中(`id` = 1)的選擇度:1024 

2017-10-27 05:36:46 34062 [Note] 第4步：開始驗(yàn)證 字段id是不是主鍵。表名:user 

2017-10-27 05:36:46 34062 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='id' and Seq_in_index = 1 

2017-10-27 05:36:46 34062 [Note] 第5步：字段id是主鍵。表名:user 

2017-10-27 05:36:46 34062 [Note] 第6步：表user 經(jīng)過運(yùn)算得到的索引列首列是主鍵,直接放棄,沒有優(yōu)化建議 

2017-10-27 05:36:46 34062 [Note] 第7步: SQLAdvisor結(jié)束!

   * 配置文件傳參調(diào)用

[root@SQLAdvisor sqladvisor]# cat sql.cnf 
[sqladvisor]
username=root
password=123
host=127.0.0.1
port=3306
dbname=test1
sqls=select * from user where name = 'lisea'

[root@SQLAdvisor sqladvisor]# ./sqladvisor -f sql.cnf -v 1
2017-10-27 05:40:14 34070 [Note] 第1步: 對(duì)SQL解析優(yōu)化之后得到的SQL:select `*` AS `*` from `test1`.`user` where (`name` = 'lisea') 

2017-10-27 05:40:14 34070 [Note] 第2步：開始解析where中的條件:(`name` = 'lisea') 

2017-10-27 05:40:14 34070 [Note] show index from user 

2017-10-27 05:40:14 34070 [Note] show table status like 'user' 

2017-10-27 05:40:14 34070 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (`name` = 'lisea')  

2017-10-27 05:40:14 34070 [Note] 第3步：表user的行數(shù):2048,limit行數(shù):1024,得到where條件中(`name` = 'lisea')的選擇度:1024 

2017-10-27 05:40:14 34070 [Note] 第4步：開始驗(yàn)證 字段name是不是主鍵。表名:user 

2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 

2017-10-27 05:40:14 34070 [Note] 第5步：字段name不是主鍵。表名:user 

2017-10-27 05:40:14 34070 [Note] 第6步：開始驗(yàn)證 字段name是不是主鍵。表名:user 

2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 

2017-10-27 05:40:14 34070 [Note] 第7步：字段name不是主鍵。表名:user 

2017-10-27 05:40:14 34070 [Note] 第8步：開始驗(yàn)證表中是否已存在相關(guān)索引。表名:user, 字段名:name, 在索引中的位置:1 

2017-10-27 05:40:14 34070 [Note] show index from user where Column_name ='name' and Seq_in_index =1 

2017-10-27 05:40:14 34070 [Note] 第9步：開始輸出表user索引優(yōu)化建議: 

2017-10-27 05:40:14 34070 [Note] Create_Index_SQL：alter table user add index idx_name(name) 

2017-10-27 05:40:14 34070 [Note] 第10步: SQLAdvisor結(jié)束!

６. 總結(jié)

以需求驅(qū)動(dòng)技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。