mysql查询未执行的sql_MySQL 查看最近执行失败的SQL语句

MySQL版本：5.7.31

根据《MySQL性能优化金字塔法则》做的实验，好书推荐！

在performance_schema中语句时间记录表中针对每一条语句的执行状态都记录了较为详细的信息，其中就包含了执行错误信息。

一、查看语句记录功能是否开启

记录语句信息的表一般为performance_schema中的四张表，分别为

events_statements_current，默认记录每个线程最近的一条SQL信息

events_statements_history，默认记录每个线程最近的十条SQL信息

events_statements_history_long ，默认记录每个线程最近的10000条SQL信息

mysql> select * from performance_schema.setup_consumers where name like 'events_statements%';

+--------------------------------+---------+

| NAME | ENABLED |

+--------------------------------+---------+

| events_statements_current | YES |

| events_statements_history | YES |

| events_statements_history_long | NO | -- 未启用

+--------------------------------+---------+

二、开启语句记录功能

生产环境中，每个线程执行的SQL很多，我们启用events_statements_history_long表记录更多的SQL信息(可以通过修改performance_schema.threads针对特定的线程进行记录信息，降低对性能的影响程度)

启用events_statements_history_long表

mysql > update performance_schema.setup_consumers set enabled = 'YES' where name ='events_statements_history_long';

三、模拟执行SQL失败

3.1 情况1:明确知道SQL错误号

会话1，执行明显格式错误的SQL，这里可以看到错误号是1096

mysql> select *;

ERROR 1096 (HY000): No tables used

会话2，可以看到执行的对应内部线程号等具体信息，环境是测试环境，所以刚好查出只有这一条，生产中应该查出来不止一条，会有很多条。

mysql> select * from performance_schema.events_statements_history_long where mysql_errno=1096\G;

*************************** 1. row ***************************

THREAD_ID: 173475

EVENT_ID: 38480

END_EVENT_ID: 38497

EVENT_NAME: statement/sql/select

SOURCE:

TIMER_START: 1050983430258683000

TIMER_END: 1050983430962477000

TIMER_WAIT: 703794000

LOCK_TIME: 0

SQL_TEXT: select *

DIGEST: ed17d00b1d52bf7da4ae01c523c15c5a

DIGEST_TEXT: SELECT *

CURRENT_SCHEMA: performance_schema

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 1096

RETURNED_SQLSTATE: HY000

MESSAGE_TEXT: No tables used

ERRORS: 1

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 0

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

1 row in set (0.01 sec)

3.2 情况2：不知道错误号，只想看最近执行出错的SQL是哪些

mysql>select * from performance_schema.events_statements_history_long where errors != 0\G;

*************************** 1. row ***************************

THREAD_ID: 173475

EVENT_ID: 38480

END_EVENT_ID: 38497

EVENT_NAME: statement/sql/select

SOURCE:

TIMER_START: 1050983430258683000

TIMER_END: 1050983430962477000

TIMER_WAIT: 703794000

LOCK_TIME: 0

SQL_TEXT: select *

DIGEST: ed17d00b1d52bf7da4ae01c523c15c5a

DIGEST_TEXT: SELECT *

CURRENT_SCHEMA: performance_schema

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 1096

RETURNED_SQLSTATE: HY000

MESSAGE_TEXT: No tables used

ERRORS: 1

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 0

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

补充：

表中的TIMER_WAIT与LOCK_TIME可以用sys.format_time()函数进行转换，变成微秒等我们能直观了解的时间大小

内部线程号：THREAD_ID = sys.ps_thread_id(process_id)