MySQL跟踪SQL&慢查询分析工具
简介
之前的工作一直使用的SQL SERVER, 用过的都知道,SQL SERVER有配套的SQL跟踪工具SQL Profiler,开发或者定位BUG过程中,可以在操作页面的时候,实时查看数据库执行的SQL语句,十分方便。最近的项目使用MySQL,没有类似的功能,感觉到十分的不爽,网上也没有找到合适的免费工具,所以自己研究做了一个简单工具。
功能
- 实时查询MySql执行的SQL语句
- 查看性能异常的SQL(执行超过2秒)
技术方案
- 前端vue,样式bootstrap
- 后台dotnet core mvc
先看一下的效果:
实现原理
Mysql支持输出日志,通过以下命令查看当前状态
-
show VARIABLES like ‘%general_log%’ //是否开启输出所有日志
- show VARIABLES like ‘%slow_query_log%’ //是否开启慢SQL日志
- show VARIABLES like ‘%log_output%’ //查看日志输出方式(默认file,还支持table)
-
show VARIABLES like ‘%long_query_time%’ //查看多少秒定义为慢SQL
下面我们将所有日志、慢SQL日志打开,日志输出修改为table,定义执行2秒以上的为慢SQL
- set global log_output=’table’ //日志输出到table(默认file)
- set global general_log=on; //打开输出所有日志
- set global slow_query_log=on; //打开慢SQL日志
- set global long_query_time=2 //设置2秒以上为慢查询
- repair table mysql.general_log //修复日志表(如果general_log表报错的情况下执行)
注意:以上的设置,数据库重启后将失效,永久改变配置需要修改my.conf文件
现在日志文件都存在数据库表里面了,剩下的工作就是取数并展示出来就行了。本项目后台使用的MVC取数,然后VUE动态绑定,Bootstrap渲染样式。
前端代码
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="utf-8"> <title>开发工具</title> <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script> <script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script> <script src="https://cdn.staticfile.org/vue-resource/1.5.1/vue-resource.min.js"></script> </head> <body> <div id="app"> <ul id="myTab" class="nav nav-tabs"> <li class="active"> <a href="#trace" data-toggle="tab"> SQL跟踪 </a> </li> <li> <a href="#slow" data-toggle="tab"> 性能异常SQL </a> </li> </ul> <hr /> <div id="myTabContent" class="tab-content"> <div id="trace" class="tab-pane fade in active"> <div> <input id="btnStart" class="btn btn-primary" type="button" value="开始" v-show="startShow" v-on:click="start" /> <input id="btnPause" class="btn btn-primary" type="button" value="暂停" v-show="pauseShow" v-on:click="pause" /> <input id="btnClear" class="btn btn-primary" type="button" value="清空" v-show="clearShow" v-on:click="clear" /> </div> <hr /> <div class="table-responsive"> <table class="table table-striped table-bordered"> <thead> <tr> <th>时间</th> <th>执行语句</th> </tr> </thead> <tbody> <tr v-for="log in logs"> <td> {{log.time}} </td> <td> @*<input class="btn btn-danger" type="button" value="复制" name="copy" />*@ {{log.sql}} </td> </tr> </tbody> </table> </div> </div> <div id="slow" class="tab-pane fade"> <div class="table-responsive"> <table class="table table-striped table-bordered"> <thead> <tr> <th>执行时长(时:分:秒,毫秒)</th> <th>锁定时长(时:分:秒,毫秒)</th> <th>开始时间</th> <th>数据库</th> <th>操作者</th> <th>执行语句</th> </tr> </thead> <tbody> <tr v-for="query in slowQuerys"> <td> {{query.queryTime}} </td> <td> @*<input class="btn btn-danger" type="button" value="复制" name="copy" />*@ {{query.lockTime }} </td> <td> {{query.startTime }} </td> <td> {{query.db }} </td> <td> {{query.userHost}} </td> <td> {{query.sql}} </td> </tr> </tbody> </table> </div> </div> </div> </div> <script> new Vue({ el: '#app', data: { startShow: true, pauseShow: false, clearShow: true, logs: [], slowQuerys: [] }, methods: { start: function () { this.timer = setInterval(this.trace, 5000); this.pauseShow = true; this.startShow = false; }, pause: function () { clearInterval(this.timer); this.pauseShow = false; this.startShow = true; }, clear: function () { this.logs = null; }, trace: function () { //发送 post 请求 this.$http.post('/home/start', {}, { emulateJSON: true }).then(function (res) { this.logs = res.body; }, function (res) { console.log(logs); }); } }, created: function () { }, mounted: function () { this.$http.post('/home/slow', {}, { emulateJSON: true }).then(function (res) { this.slowQuerys = res.body; }, function (res) { console.log(this.slowQuerys); }); }, destroyed: function () { clearInterval(this.time) } }); </script> </body> </html>
后端代码
using Ade.Tools.Models; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text.RegularExpressions; using MySql.Data.MySqlClient; using Microsoft.Extensions.Caching.Memory; namespace Ade.Tools.Controllers { public class HomeController : Controller { public IConfiguration Configuration { get; set; } public HomeController(IConfiguration configuration) { this.Configuration = configuration; this.ConnStr = Configuration["Sql:DefaultConnection"]; } public static DateTime StartTime { get; set; } = DateTime.MinValue; public static List<string> TableNames { get; set; } public string ConnStr { get; set; } public JsonResult Start() { if (StartTime == DateTime.MinValue) { StartTime = DateTime.Now; } int size = int.Parse(Configuration["Size"]); string[] blackList = Configuration["Blacklist"].Split(","); List<string> tableNames = GetTableNames(); List<LogItem> logItems = new List<LogItem>(); List<LogItemDTO> logItemDTOs = new List<LogItemDTO>(); using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { //string sqlStart = "set global log_output='table';set global general_log=on; repair table mysql.general_log;"; //Dapper.SqlMapper.Execute(mySqlConnection, sqlStart); logItemDTOs = Dapper.SqlMapper.Query<LogItemDTO>(mySqlConnection, $" select * from mysql.general_log " + $"where event_time>'{StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' " + $"order by event_time desc ") //+ $"limit {size} " .ToList(); } logItemDTOs.ForEach(e => { LogItem logItem = new LogItem() { Time = e.event_time.ToString("yyyy-MM-dd HH:mm:ss.fff"), CommondType = e.command_type, ServerId = e.server_id, ThreadId = e.thread_id, UserHost = e.user_host, Sql = System.Text.Encoding.Default.GetString(e.argument) }; if (tableNames.Any(a => logItem.Sql.Contains(a)) && !blackList.Any(b => logItem.Sql.Contains(b)) ) { logItems.Add(logItem); } }); return new JsonResult(logItems); } public JsonResult Slow() { List<SlowQuery> slowQueries = new List<SlowQuery>(); using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { string sql = "select * from mysql.slow_log order by query_time desc"; List<SlowQueryDTO> slowDtos = Dapper.SqlMapper.Query<SlowQueryDTO>(mySqlConnection, sql).ToList(); slowDtos.ForEach(e => { slowQueries.Add(new SlowQuery() { DB = e.db, LockTime = DateTime.Parse(e.lock_time.ToString()).ToString("HH:mm:ss.fffff"), QueryTime = DateTime.Parse(e.query_time.ToString()).ToString("HH:mm:ss.fffff"), RowsExamined = e.rows_examined, RowsSent = e.rows_sent, Sql = System.Text.Encoding.Default.GetString( (byte[])e.sql_text), StartTime = e.start_time.ToString("yyyy-MM-dd HH:mm:ss"), UserHost = e.user_host }); }); } return new JsonResult(slowQueries); } public string On() { using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { string sql = "set global log_output='table';set global general_log=on; repair table mysql.general_log;"; Dapper.SqlMapper.Execute(mySqlConnection, sql); } return "ok"; } public string Off() { using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { string sql = "set global general_log=off;"; Dapper.SqlMapper.Execute(mySqlConnection, sql); } return "ok"; } public string Clear() { using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { string sql = $@" SET GLOBAL general_log = 'OFF'; RENAME TABLE general_log TO general_log_temp; DELETE FROM `general_log_temp`; RENAME TABLE general_log_temp TO general_log; SET GLOBAL general_log = 'ON'; "; Dapper.SqlMapper.Execute(mySqlConnection, sql); } return "ok"; } public IActionResult Index() { return View(); } private List<string> GetTableNames() { MemoryCache memoryCache = new MemoryCache(new MemoryCacheOptions()); var cacheKey = "MySqlProfile_TableNames"; List<string> tableNames = memoryCache.Get <List<string>>(cacheKey); if (tableNames != null) { return tableNames; } string[] traceDbs = Configuration["TraceDatabaseNames"].Split(","); string sqlTables = "SELECT distinct TABLE_NAME FROM information_schema.columns"; foreach (var db in traceDbs) { if (!sqlTables.Contains("WHERE")) { sqlTables += " WHERE table_schema='" + db + "'"; } else { sqlTables += " OR table_schema='" + db + "'"; } } using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr)) { // WHERE table_schema='mice' tableNames = Dapper.SqlMapper.Query<string>(mySqlConnection, sqlTables).ToList(); } memoryCache.Set(cacheKey, tableNames, TimeSpan.FromMinutes(30)); return tableNames; } } }
源代码
修改完appsettings.json文件里面的连接字符串以及其他配置(详情自己看注释,懒得写了),就可以使用了。
https://github.com/holdengong/MysqlProfiler
最后一点
开启日志会产生大量的文件,需要注意定时清理
- SET GLOBAL general_log = ‘OFF’; // 关闭日志
- RENAME TABLE general_log TO general_log_temp; //表重命名
- DELETE FROM
general_log_temp
; //删除所有数据 - RENAME TABLE general_log_temp TO general_log; //重命名回来
- SET GLOBAL general_log = ‘ON’; //开启日志
原文地址:https://www.cnblogs.com/holdengong/p/10115450.html
相关推荐
-
[Asp.net] C# 操作Excel的几种方式 优缺点比较 C#
2019-9-18
-
WPF 打印界面(控件)到A4纸 C#
2019-10-11
-
微服务中的异步消息通讯 C#
2019-6-30
-
开源框架Autofac使用入门 C#
2019-9-18
-
.Net Core 三大Redis客户端对比和使用心得 C#
2019-9-3
-
Visual Studio Code 配置指南 C#
2019-10-10
-
.NET中的线程与异步(笔记) C#
2019-6-10
-
一篇文章搞懂到底什么是渲染流水线 C#
2019-11-1
-
在 ABP vNext 中编写仓储单元测试的问题一则 C#
2019-10-3
-
Unity 使用Plugins接入安卓SDK 基础篇 C#
2019-7-3