MySQL查看历史会话执行过的SQL
MySQL查看历史会话执行过的SQL
网上找了下基本都是使用脚本，运行定时任务获取sql内容记录到文本里边。
我想要的效果是使用mysql系统视图来查看，其实可以实现。
通过查询视图performance_schema.events_statements_history_long来实现。
该视图启用的时候默认保留@@performance_schema_events_statements_history_long_size条记录。
防爬虫：https://www.cnblogs.com/PiscesCanon/p/17296822.html
默认该视图功能是禁用的：
(root@localhost 16:42:56) [(none)]> select * from performance_schema.setup_consumers where name='events_statements_history_long'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_history_long | NO | +--------------------------------+---------+ 1 row in set (0.00 sec)
可通过两种方式开启。
方式1：（What are the Recommended Configuration of the Performance Schema? (文档 ID 2229601.1)）：
[mysqld]
performance_schema_consumer_events_statements_history_long = ON
需要重启数据库生效。
方式2（重启生效）：
（Can't Filter on MYSQL_ERRNO or MESSAGE_TEXT Against Table events_statements_history_long (文档 ID 2759463.1)）
（How To Find Queries Generating Errors Or Warnings And How To Investigate the MEM Event "SQL Statement Generates Errors Or Warnings"? (文档 ID 2070773.1)）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';
(root@localhost 17:01:44) [(none)]> select * from performance_schema.setup_consumers where name='events_statements_history_long'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_history_long | YES | +--------------------------------+---------+ 1 row in set (0.00 sec)
可以同时采用两种方式，达到无需重启永久生效的效果。
简单的查询（近10000条SQL内超过1s的），条件可根据需要自行修改。
注意：示例SQL的SQL_TEXT字段是做了处理（去掉换行，两空格以上换一个空格，取前100字符），要全文本可自行去掉处理表达式。
select THREAD_ID AS ThreadID, CURRENT_SCHEMA, date_format(date_sub(NOW(), interval (select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='UPTIME') - TIMER_START * 10e-13 second), '%Y-%m-%d %H:%i:%s') AS StartTime, date_format(date_sub(NOW(), interval (select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='UPTIME') - TIMER_END * 10e-13 second), '%Y-%m-%d %H:%i:%s') AS EndTime, FORMAT_PICO_TIME(TIMER_WAIT) AS ExecDuration, substring(regexp_replace(replace(SQL_TEXT,'\n',''),' +', ' '),1,100) SQL_TEXT, MESSAGE_TEXT from performance_schema.events_statements_history_long where 1 = 1 and SQL_TEXT is not null and TIMER_WAIT > 1000000000000 order by TIMER_WAIT;
(root@localhost 17:28:11) [zkm](34)> select THREAD_ID AS ThreadID, -> CURRENT_SCHEMA, -> date_format(date_sub(NOW(), interval (select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='UPTIME') - TIMER_START * 10e-13 second), '%Y-%m-%d %H:%i:%s') AS StartTime, -> date_format(date_sub(NOW(), interval (select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='UPTIME') - TIMER_END * 10e-13 second), '%Y-%m-%d %H:%i:%s') AS EndTime, -> FORMAT_PICO_TIME(TIMER_WAIT) AS ExecDuration, -> substring(regexp_replace(replace(SQL_TEXT,'\n',''),' +', ' '),1,100) SQL_TEXT, -> MESSAGE_TEXT -> from performance_schema.events_statements_history_long -> where 1 = 1 -> and SQL_TEXT is not null -> and TIMER_WAIT > 1000000000000 -> order by TIMER_WAIT; +----------+----------------+---------------------+---------------------+--------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ | ThreadID | CURRENT_SCHEMA | StartTime | EndTime | ExecDuration | SQL_TEXT | MESSAGE_TEXT | +----------+----------------+---------------------+---------------------+--------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ | 68 | zkm | 2024-12-18 17:39:09 | 2024-12-18 17:39:10 | 1.05 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 68 | zkm | 2024-12-18 17:38:58 | 2024-12-18 17:38:59 | 1.21 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 68 | zkm | 2024-12-18 17:38:54 | 2024-12-18 17:38:56 | 1.27 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 68 | zkm | 2024-12-18 17:38:52 | 2024-12-18 17:38:53 | 1.28 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 68 | zkm | 2024-12-18 17:39:06 | 2024-12-18 17:39:08 | 1.37 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 68 | zkm | 2024-12-18 17:39:05 | 2024-12-18 17:39:06 | 1.48 s | INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(s | NULL | | 109 | zkm | 2024-12-19 16:06:39 | 2024-12-19 16:06:42 | 2.40 s | delete from zkm where id=1 and c3=23 | NULL | | 206 | zkm | 2024-12-23 14:21:16 | 2024-12-23 14:21:18 | 2.44 s | update zkm set id=id+0 where c3=123 | Rows matched: 1 Changed: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 16:06:32 | 2024-12-19 16:06:35 | 2.54 s | delete from zkm where id=1 and c3=123 | NULL | | 248 | zkm | 2024-12-23 17:19:34 | 2024-12-23 17:19:38 | 4.02 s | create index idx_hahahah on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 15:57:44 | 2024-12-19 15:57:48 | 4.23 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 207 | zkm | 2024-12-23 16:32:07 | 2024-12-23 16:32:11 | 4.45 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 207 | zkm | 2024-12-23 16:31:07 | 2024-12-23 16:31:12 | 4.68 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 248 | zkm | 2024-12-23 17:18:37 | 2024-12-23 17:18:42 | 4.74 s | create index idx_z_id1 on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 16:06:51 | 2024-12-19 16:06:56 | 4.78 s | create index idx_z_dtime on zkm(dtime) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 16:06:57 | 2024-12-19 16:07:02 | 4.80 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 16:06:03 | 2024-12-19 16:06:08 | 5.07 s | create index idx_z_dtime on zkm(dtime) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 16:06:09 | 2024-12-19 16:06:14 | 5.49 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 15:57:37 | 2024-12-19 15:57:43 | 5.57 s | create index idx_z_dtime on zkm(dtime) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 15:07:53 | 2024-12-19 15:08:00 | 6.28 s | create index idx_z_id on zkm(id) | Records: 0 Duplicates: 0 Warnings: 0 | | 109 | zkm | 2024-12-19 15:07:45 | 2024-12-19 15:07:52 | 7.21 s | create index idx_z_dtime on zkm(dtime) | Records: 0 Duplicates: 0 Warnings: 0 | | 68 | zkm | 2024-12-18 16:41:20 | 2024-12-18 17:39:26 | 58.10 min | CALL GenerateData(1000000) | NULL | +----------+----------------+---------------------+---------------------+--------------+------------------------------------------------------------------------------------------------------+------------------------------------------+ 22 rows in set (0.05 sec)
至此。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？