sqladvisor 是美团团队打造的 MySQL SQL 语句查询优化工具，官方网站：https://github.com/Meituan-Dianping/SQLAdvisor

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。 主要功能：输出SQL索引优化建议

官方文档默认是基于 centos 编译的，这篇文章是介绍在 Ubuntu 下编译和使用，以及一个小 bug 的修复。

安装依赖

1
2
3
4
5
6
7
8

sudo apt install  cmake libaio-dev libffi-dev libglib2.0-0 libglib2.0-dev

# percona-server-server-5.6
sudo apt-get install gnupg2
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-server-server-5.6

修改CMakeLists.txt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

diff --git a/sqladvisor/CMakeLists.txt b/sqladvisor/CMakeLists.txt
index 3149044..a3008f9 100644
--- a/sqladvisor/CMakeLists.txt
+++ b/sqladvisor/CMakeLists.txt
@@ -6,8 +6,10 @@ include_directories("/usr/local/sqlparser/include")
 include_directories("/usr/local/sqlparser/include/regex")
 include_directories("/usr/lib64/glib-2.0/include")
 include_directories("/usr/include/glib-2.0")
+include_directories("/usr/lib/x86_64-linux-gnu/glib-2.0/include")
 link_directories("/usr/local/sqlparser/lib")
 link_directories("/usr/lib64")
+link_directories("/usr/lib/x86_64-linux-gnu")

 set(TEST_SRC main.cc)
 add_executable(sqladvisor ${TEST_SRC})

编译安装

① 安装 sqlparser

1
2
3

# sqlparser
cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
make && sudo make install

② 编译 sqladvisor

# cd sqladvisor
vim CMakeLists.txt
cmake -DCMAKE_BUILD_TYPE=debug ./
make -j8

③ 小试牛刀

sql.cnf

1
2
3
4
5
6
7

[sqladvisor]
username=admin
password=***
host=127.0.0.1
port=3309
dbname=account
sqls=SELECT `user_id`, `third_user_id`, `third_type`, `device_id`, `password`, `nickname`, `gender`, `avatar`, `phone`, `withdraw_phone`, `signature`, `status`, `birthday`, `occupation`, `last_nickname`, `gpsad_id`, `create_time`, `update_time` FROM `user_info` WHERE (create_time >= '2020-12-24 09:54:35.610708' and gpsad_id <> '' )

潜在的段错误修复

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

diff --git a/sqladvisor/main.cc b/sqladvisor/main.cc
index b39b625..e0780a2 100644
--- a/sqladvisor/main.cc
+++ b/sqladvisor/main.cc
@@ -283,7 +283,7 @@ uint get_join_table_result_set(TABLE_LIST *table) {

     mysql_query(con, "set names utf8");

-    uint result_set_count;
+    uint result_set_count = 0;
     GString *cardinality_sql = g_string_new(NULL);
     if (table->index_field_head != NULL) {
         if (table->index_field_head->index_field->field_name->table_name
@@ -318,7 +318,7 @@ uint get_join_table_result_set(TABLE_LIST *table) {

     MYSQL_ROW row;

-    if ((row = mysql_fetch_row(result))) {
+    if ((row = mysql_fetch_row(result)) && row[EXPLAIN_ROWS]) {
         result_set_count = atoi(row[EXPLAIN_ROWS]);
     }
     g_string_free(cardinality_sql, TRUE);

外包团队写的「天书」SQL语句成功触发了一次段错误，经过 gdb 调试发现是有一处未判空所致，半夜调试的C++代码，这里就不展开讨论了…

分析过程示例

/usr/local/bin/sqladvisor -v 1 -f sql.cnf
2020-12-24 15:56:57 9211 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `user_id` AS `user_id`,`third_user_id` AS `third_user_id`,`third_type` AS `third_type`,`device_id` AS `device_id`,`password` AS `password`,`nickname` AS `nickname`,`gender` AS `gender`,`avatar` AS `avatar`,`phone` AS `phone`,`withdraw_phone` AS `withdraw_phone`,`signature` AS `signature`,`status` AS `status`,`birthday` AS `birthday`,`occupation` AS `occupation`,`last_nickname` AS `last_nickname`,`gpsad_id` AS `gpsad_id`,`create_time` AS `create_time`,`update_time` AS `update_time` from `account`.`user_info` where ((`create_time` >= '2020-12-24 09:54:35.610708') and (`gpsad_id` <> ''))

2020-12-24 15:56:57 9211 [Note] 第2步：开始解析where中的条件:(`create_time` >= '2020-12-24 09:54:35.610708')

2020-12-24 15:56:57 9211 [Note] show index from user_info

2020-12-24 15:56:58 9211 [Note] show table status like 'user_info'

2020-12-24 15:56:58 9211 [Note] select count(*) from ( select `create_time` from `user_info` FORCE INDEX( PRIMARY ) order by user_id DESC limit 10000) `user_info` where (`create_time` >= '2020-12-24 09:54:35.610708')

2020-12-24 15:56:58 9211 [Note] 第3步：表user_info的行数:26291,limit行数:10000,得到where条件中(`create_time` >= '2020-12-24 09:54:35.610708')的选择度:79

2020-12-24 15:56:58 9211 [Note] 第4步：开始解析where中的条件:(`gpsad_id` <> '')

2020-12-24 15:56:58 9211 [Note] show index from user_info

2020-12-24 15:56:58 9211 [Note] show table status like 'user_info'

2020-12-24 15:56:58 9211 [Note] select count(*) from ( select `gpsad_id` from `user_info` FORCE INDEX( PRIMARY ) order by user_id DESC limit 10000) `user_info` where (`gpsad_id` <> '')

2020-12-24 15:56:58 9211 [Note] 第5步：表user_info的行数:26291,limit行数:10000,得到where条件中(`gpsad_id` <> '')的选择度:5

2020-12-24 15:56:58 9211 [Note] 第6步：开始验证 字段create_time是不是主键。表名:user_info

2020-12-24 15:56:58 9211 [Note] show index from user_info where Key_name = 'PRIMARY' and Column_name ='create_time' and Seq_in_index = 1

2020-12-24 15:56:58 9211 [Note] 第7步：字段create_time不是主键。表名:user_info

2020-12-24 15:56:58 9211 [Note] 第8步：开始验证 字段create_time是不是主键。表名:user_info

2020-12-24 15:56:59 9211 [Note] show index from user_info where Key_name = 'PRIMARY' and Column_name ='create_time' and Seq_in_index = 1

2020-12-24 15:56:59 9211 [Note] 第9步：字段create_time不是主键。表名:user_info

2020-12-24 15:56:59 9211 [Note] 第10步：开始验证表中是否已存在相关索引。表名:user_info, 字段名:create_time, 在索引中的位置:1

2020-12-24 15:56:59 9211 [Note] show index from user_info where Column_name ='create_time' and Seq_in_index =1

2020-12-24 15:56:59 9211 [Note] 第11步：索引(create_time)已存在

2020-12-24 15:56:59 9211 [Note] 第12步: SQLAdvisor结束!

若 SQL 语句有优化的地方，sqladvisor 会给出优化建议，还是很棒的。