开发者社区> 在下uptown> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【MySQL】搜集慢sql分析工具

简介: 基于slowquery.log分析并提供sql脱敏聚合能力
+关注继续查看


浅谈慢SQL


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



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


image.png


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


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



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


效果如下`:


image.png

实现思路


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


服务层拦截


  • 如果是Java服务的化比较好处理,毕竟可以从orm框架上做一些文章,搞一些intercepter拦截sql分析。
  • 问题是我们的服务经过漫长的迭代并不是只有Java语言,还有一些老旧的Python服务怎么处理,Python的服务都是自己封装的一些公用curd方法,并没有用开源框架,必然需要手动处理,工作量暴增。


数据库层拦截


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

show variables like '%query%'


image.png



所有符合查询耗时条件的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实现的图形化界面实现的,效果应该算是最好的


image.png



image.png


image.png


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


GO吗? GO!


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



image.png


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


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



最终实现


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


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


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

image.png



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


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


image.png

image.png


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





版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql sql_mode 汇总整理
-- ANSI 使sql符合标准sql This mode changes syntax and behavior to conform more closely to standard S...
728 0
Mysql SQL Mode详解
Mysql SQL Mode简介 MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
1352 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
819 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1463 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1654 0
+关注
在下uptown
练习时长两年半的夹娃练习生
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
MySQL 5.7让优化更轻松
立即下载
MySQL 5.7优化不求人
立即下载