在YearningSQL管理后台,管理员可以看到全部用户的查询记录。
但是如果我们想把这些查询记录公开给全部研发(也是便于大家一起审一审这些查询是否有问题~~),一个比较简单的方法就是借助grafana来实现。
这个操作比较简单。直接上图吧。
我这里的效果如下:
具体SQL如下:
select
date_format(a.`time`, '%Y-%m-%d %H:%i') as "执行时间",
b.realname as "用户",
REPLACE(REPLACE(a.sql, CHAR(10), ' '), CHAR(13),' ') as "sql明细", -- 去掉sql中的换行和回车符号,便于展示
a.source as "实例名",
a.base_name as "库名"
from core_query_records as a
inner join
core_query_orders as b
on a.work_id = b.work_id
where
-- 只查询近一个月的数据(注意:这个查询实际上是有点问题的,列上用了date_format后不能用上索引。
-- 折中办法可以定期归档下这个表,确保不要太大)
date_format(a.`time`, '%Y-%m-%d %H:%i') >= date_format(date_add(now(), interval -30 day), '%Y-%m-%d %H:%i')
order by date_format(a.`time`, '%Y-%m-%d %H:%i') desc
limit 500 -- 这里我只展示最近的500条记录
update 20211210,修改了下sql
SELECT
DATE_FORMAT(a.`time`, '%Y-%m-%d %H:%i') AS "执行时间",
b.realname AS "用户",
ex_time/1000 as "耗时(秒)",
REPLACE(REPLACE(a.sql, CHAR(10), ' '), CHAR(13),' ') AS "sql明细",
b.id_c AS "实例名",
a.base_name AS "库名"
FROM core_query_records AS a
INNER JOIN
core_query_orders AS b
ON a.work_id = b.work_id
WHERE a.time BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59')
ORDER BY
ex_time DESC,
DATE_FORMAT(a.`time`, '%Y-%m-%d %H:%i') DESC
LIMIT 20