系统表介绍
events_statements_history_long 反映了语句包含的事件。为每个线程保留的行数受参数“performance_schema_events_statements_history_long_size”控制。
| 列名 | 含义 |
|---|---|
| THREAD_ID | 线程编号 |
| EVENT_ID | 事件编号 |
| END_EVENT_ID | 阶段结束事件ID 当事件结束后，发生启动修改线程当前事件号时置为空 |
| EVENT_NAME | 事件名称 |
| SOURCE | 包含监测器的源码文件 |
| TIMER_START | 事件启动的时间 |
| TIMER_END | 事件结束的时间 |
| TIMER_WAIT | 事件持续时长 TIMER_END-TIMER_START 单位是皮秒 百万分之一微秒 |
| LOCK_TIME | 锁等待事件 （微秒） |
| SQL_TEXT | SQL文本 |
| DIGEST | 概要 |
| DIGEST_TEXT | 概要文本 |
| CURRENT_SCHEMA | 当前模式 |
| OBJECT_TYPE | 嵌套语句(存储过程类型) |
| OBJECT_SCHEMA | 嵌套语句(存储过程所属模式) |
| OBJECT_NAME | 嵌套语句(存储过程名称) |
| OBJECT_INSTANCE_BEGIN | 对象的内存地址 |
| MYSQL_ERRNO | 语句错误编号 |
| RETURNED_SQLSTATE | 语句SQLSTATE 值 |
| MESSAGE_TEXT | 报错信息 |
| ERRORS | 报错标志（如SQLSTAT以00、01开头值为0 ，其他值为1 ） |
| WARNINGS | 警告数量 |
| ROWS_AFFECTED | 涉及的行数 |
| ROWS_SENT | 语句返回行数 |
| ROWS_EXAMINED | 在server层检查的行数（不包含在内部存储影响处理的数量） |
| CREATED_TMP_DISK_TABLES | 内部创建磁盘临时表的数量 |
| CREATED_TMP_TABLES | 内部创建临时表的数量 |
| SELECT_FULL_JOIN | 由于没有索引引起全表连接的数量 |
| SELECT_FULL_RANGE_JOIN | 在引用表发生大范围扫描的次数 |
| SELECT_RANGE | 在初始表使用范围方式获取记录的次数 |
| SELECT_RANGE_CHECK | 判断是否要使用范围扫描的次数 |
| SELECT_SCAN | 全表扫描次数 |
| SORT_MERGE_PASSES | 再排序次数 |
| SORT_RANGE | 范围扫描时按顺序读取记录的次数 |
| SORT_ROWS | 排序的行数 |
| SORT_SCAN | 全表扫描是按顺序读取的次数 |
| NO_INDEX_USED | 标识是否使用索引 1 未使用 其他 0 |
| NO_GOOD_INDEX_USED | 标识是否使用好的索引 1 使用 其他 0 |
| NESTING_EVENT_ID | 父语句事件编号 |
| NESTING_EVENT_TYPE | 父事件类型 |
| NESTING_EVENT_LEVEL | 嵌套级别 |
实例
mysql> select * from events_statements_history_long limit 10;
+-----------+----------+--------------+----------------------+--------+----------------------+----------------------+------------+-----------+---------------------------------------------------------------+----------------------------------+--------------------------------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | LOCK_TIME | SQL_TEXT | DIGEST | DIGEST_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_LEVEL |
+-----------+----------+--------------+----------------------+--------+----------------------+----------------------+------------+-----------+---------------------------------------------------------------+----------------------------------+--------------------------------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
| 348 | 49 | 49 | statement/sql/update | | 12384992476578342000 | 12384992477078768000 | 500426000 | 159000000 | update performance_schema.setup_consumers set ENABLED ='YES' | 5c3b1c176bde09da3e60c5492ce1ab83 | UPDATE `performance_schema` . `setup_consumers` SET `ENABLED` = ? | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | Rows matched: 15 Changed: 10 Warnings: 0 | 0 | 0 | 15 | 0 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 348 | 51 | 51 | statement/com/Ping | | 12384992506304039000 | 12384992506482238000 | 178199000 | 0 | NULL | NULL | NULL | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 348 | 53 | 53 | statement/sql/select | | 12384992507110418000 | 12384992507276384000 | 165966000 | 0 | SELECT DATABASE() | 2cf2906bafd55ee74be1b693d8234ae8 | SELECT SCHEMA ( ) | performance_schema | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 349 | 44 | 44 | statement/com/Ping | | 12384995237627787000 | 12384995237841881000 | 214094000 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 349 | 46 | 46 | statement/sql/commit | | 12384995238057807000 | 12384995238170719000 | 112912000 | 0 | COMMIT | 98bf18ef8a1606965a0f2ff85fa992a3 | COMMIT | NULL | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 348 | 55 | 55 | statement/com/Ping | | 12384995238845312000 | 12384995238929370000 | 84058000 | 0 | NULL | NULL | NULL | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 348 | 57 | 57 | statement/sql/commit | | 12384995239204205000 | 12384995239310552000 | 106347000 | 0 | COMMIT | 98bf18ef8a1606965a0f2ff85fa992a3 | COMMIT | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 350 | 18 | 18 | statement/com/Ping | | 12384995239546429000 | 12384995239634965000 | 88536000 | 0 | NULL | NULL | NULL | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 350 | 20 | 20 | statement/sql/commit | | 12384995239854460000 | 12384995239949945000 | 95485000 | 0 | COMMIT | 98bf18ef8a1606965a0f2ff85fa992a3 | COMMIT | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 348 | 59 | 59 | statement/com/Ping | | 12385010124017286000 | 12385010124182652000 | 165366000 | 0 | NULL | NULL | NULL | performance_schema | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
+-----------+----------+--------------+----------------------+--------+----------------------+----------------------+------------+-----------+---------------------------------------------------------------+----------------------------------+--------------------------------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
10 rows in set (0.00 sec)
mysql> show variables like 'performance_schema_events_statements_history_long_size' ;
+--------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_statements_history_long_size | 10000 |
+--------------------------------------------------------+-------+
1 row in set (0.00 sec)