performance_schema
中有很多的表(语句信息表,事务信息表等)记录执行的SQL
的具体信息,执行事务的具体信息,其中都会有一个叫做TIMER_START
的字段,这个字段的值易读性很差,官方文档说是皮秒,但也没说怎么转成人类易读的形式,通过以下的方法可以将其转换为人易读的格式。
举例
以performance_schema.events_statements_history_long
表为例,摆上对应的SQL
语句,可以考虑将其写成函数。
SELECT
*,
DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - TIMER_START*10e-13 second) AS 'start_time',
ROUND(timer_wait*10E-10, 3) AS 'wait in (ms)'
FROM
performance_schema.events_statements_history_long
where thread_id=194277
limit 1G;
*************************** 1. row ***************************
THREAD_ID: 194277
EVENT_ID: 323
END_EVENT_ID: 354
EVENT_NAME: statement/sql/select
SOURCE:
TIMER_START: 1122588644870331000
TIMER_END: 1122588645297519000
TIMER_WAIT: 427188000
LOCK_TIME: 327000000
SQL_TEXT: select count(*) from mysql.user
DIGEST: a41461c07eca51bcda21d91cf128cfa6
DIGEST_TEXT: SELECT COUNT ( * ) FROM `mysql` . `user`
CURRENT_SCHEMA: NULL
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
start_time: 2020-10-22 10:07:39.644871 <---- 开始日期时间!!
wait in (ms): 0.427
1 row in set (0.10 sec)
关键字段
DATE_SUB(
NOW(),
INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - TIMER_START*10e-13 second
) AS 'start_time'
DATE_SUB(指定日期A, 需要减去的时间间隔B)
:如现在是2020-10-22 10:15:00.000
(参数A),我指定需要减去的天数为2即INTERVAL 2 DAY
(参数B),就能返回得到2020-10-20 10:15:00.000
结论
timer_*
即从数据库正常启动以来经过的时间,但这个时间不一定准确,官方文档内说这个会基于处理器的啥啥啥进行计算,可能会有波动。- 笔者有一台数据库的
timer_*
字段信息用以上方法计算之后,相差特别大(大的离谱那种,时间多了1年),不知道原因,这台经历过升级,有知道的大佬,欢迎留言。
创建时间转换函数,方便调用
begin;
set global log_bin_trust_function_creators=on;
DELIMITER $$
DROP FUNCTION IF EXISTS timer_to_date$$
CREATE FUNCTION timer_to_date(timer bigint(20) unsigned) RETURNS varchar(50)
BEGIN
DECLARE res_date varchar(50);
DECLARE uptime int;
SET uptime=(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME');
SET res_date=DATE_SUB(NOW(),INTERVAL @uptime - timer*10e-13 second);
return res_date;
END $$
delimiter ;
set global log_bin_trust_function_creators=off;
commit;