浅谈慢SQL

相信每个做业务的程序员都会受到过慢sql的困扰，开发新功能的时候库里总共没几条数据，毫秒级查询笑嘻嘻，上线之后各种页面loading卡顿。。。

通常每个公司都应该有对应的搜集分析慢sql的工具，尤其是做saas服务的要实时监控慢sql及时推送预警并改正。不过并不是每家公司都会有。毕竟现在大部分公司的首要功能是活下去。

不是以saas产品为主线的公司都让寒气吹傻了，疯狂迭代需求还来不及，谁还管这些不痛不痒的小工具（不要误会，我在自我介绍）。

就拿我们公司来说，很早期从saas转型成私有化，一直缺这么个小工具。直到有一天saas个人版出现了卡顿情况，组长最后从阿里云mysql监控平台上琳琅满目的慢sql图表里得出结论，罪魁祸首是几条慢sql导致的。

于是做了个决定一定要搞一个工具，低配版的也行，起码要有抽象sql聚合的能力（脱敏处理，sql的参数替换成问号可以类比orm中正向操作是在执行sql前用参数替换掉问号，反向就是把要执行的sql参数再替换成问号）

效果如下:

实现思路

搭建慢SQL分析工具首先要有数据源，得想办法拦截sql并分析它，摆在面前的总共两个大方向，服务层拦截和数据库层拦截。

服务层拦截

• 如果是Java服务的化比较好处理，毕竟可以从orm框架上做一些文章，搞一些intercepter拦截sql分析。

• 问题是我们的服务经过漫长的迭代并不是只有Java语言，还有一些老旧的Python服务怎么处理，Python的服务都是自己封装的一些公用curd方法，并没有用开源框架，必然需要手动处理，工作量暴增。

数据库层拦截

mysql自带了慢日志查询，可以打开慢查询设置，分析mysql打出来的慢sql日志

show variables like '%query%'

所有符合查询耗时条件的sql都会被收集到指定的路径下，不断追加写入到slow-query.log文件中。如此一来监听这个slow-query.log就可以了。日志格式如下：

/opt/bitnami/mysql/bin/mysqld, Version: 5.7.26-log (MySQL Community Server (GPL)). started with:\
Tcp port: 3306  Unix socket: /opt/bitnami/mysql/tmp/mysql.sock\
Time                 Id Command    Argument\
\
# Time: 2021-06-26T00:00:05.250595+08:00\
# User@Host: calarm[calarm] @  [10.244.0.176]  Id: 405911\
# Query_time: 4.977888  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 15973877\
use calarm;\
SET timestamp=1624636805;\
select count(1) FROM msg_info where trigger_time<date_add(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), interval -1 DAY);\
# Time: 2021-06-26T00:00:08.236660+08:00\
# User@Host: calarm[calarm] @  [10.244.0.176]  Id: 405815\
# Query_time: 2.170010  Lock_time: 0.000138 Rows_sent: 0  Rows_examined: 100000\
SET timestamp=1624636808;\
delete FROM msg_info where trigger_time<date_add(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'), interval -1 DAY) limit 100000;

落地方案

首先这个需求按理说应该非常常见才是，于是乎花了将近一下午游走在各大代码平台，github、gitee、google、stackoverflow去找现成的方案。找来找去就两个比较靠谱。

PHP称霸武林

源代码地址：https://github.com/hcymysql/slowquery

这个工具核心还是Percona pt-query-digest的一个分析SQL的工具结合一些php实现的图形化界面实现的，效果应该算是最好的

各方各面都挺好，唯独没用脱敏SQL功能，而且部署起来是真滴费死劲了，一来php系统从来没接触过，搜了搜发现部署php还要搞一套专属运行环境，实验的时候搞了个php-nginx的容器疯狂操作也没操作明白。就暂时当作一个备选方案吧，实在没有办法再来用这个。

GO吗? GO!

源代码地址：https://github.com/qieangel2013/SqlReview

虽然没有华丽的图表，但是就脱敏sql而言，看起来非常吻合我们的需求，但是开源出来的源代码相当臃肿的，甚至有一些kafka的推送功能、格式化后的数据会持久化到数据库、提供了打分功能。

本来就不会Golang，根本跑不起来服务，看的我晕头转向的，之前倒是想学一手golang来着，后来也没能坚持下来，所以决定正好可以借此机会过一遍。把里面重要的功能摘出来。

最终实现

这个需求最重要就是根据脱敏后的抽象sql聚合，图形化界面之类的都好说，于是决定用gin框架打一个小服务，通过一点一点的拆解，拿到了核心抽象sql的方法fingerprint.go这个文件。

用文件流一行一行的读取慢sql，通过方法转成抽象sql，统计各项指标，画一个前端页面就能实现比较简单版的功能了，抽象sql和真实sql做一层下转。

自从大学毕业之后已经很久很久没碰前端代码了。打开layui官网发现竟然已经停止运营了！

泪目了，这么经典的前端组件库。最后用layui画了个简单页面。

代码仓库: https://github.com/SplitfireUptown/azeroth.git

是不是还可以，虽然界面很丑陋，但是五脏俱全，后续有时间需要再完善一下。。。提供下折线图、筛选日期（现在是分析整个文件）、优化分析速度之类的。总之做一个好用的工具还是很不容易的。