簡介 之前的工作一直使用的SQL SERVER, 用過的都知道，SQL SERVER有配套的SQL跟蹤工具SQL Profiler，開發或者定位BUG過程中，可以在操作頁面的時候，實時查看資料庫執行的SQL語句，十分方便。最近的項目使用MySQL，沒有類似的功能，感覺到十分的不爽，網上也沒有找到合適 ...
簡介
之前的工作一直使用的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'; //開啟日誌
