statements_with_temp_tables,x$statements_with_temp_tables

• 查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序。数据来源：events_statements_summary_by_digest

• statements_with_temp_tables和x$statements_with_temp_tables视图字段含义如下：

  • query：经过标准化转换的语句字符串
  • db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
  • exec_count：语句执行的总次数
  • total_latency：语句执行的总等待时间（执行时间）
  • memory_tmp_tables：语句执行时创建内部内存临时表的总数量
  • disk_tmp_tables：语句执行时创建的内部磁盘临时表的总数量
  • avg_tmp_tables_per_query：对于使用了内存临时表的语句，每个语句使用内存临时表的平均数量(SUM_CREATED_TMP_TABLES/COUNT_STAR)
  • tmp_tables_to_disk_pct：内存临时表的总数量与磁盘临时表的总数量百分比，表示磁盘临时表的转换率(SUM_CREATED_TMP_DISK_TABLES/SUM_CREATED_TMP_TABLES)
  • first_seen：该语句第一次出现的时间
  • last_seen：该语句最近一次出现的时间
  • digest：语句摘要计算的md5 hash值

• 视图定义语句

# statements_with_temp_tables
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW statements_with_temp_tables (
  query,  db,  exec_count,  total_latency,  memory_tmp_tables,  disk_tmp_tables,  avg_tmp_tables_per_query,  tmp_tables_to_disk_pct,  first_seen,  last_seen,  digest
) AS
SELECT sys.format_statement(DIGEST_TEXT) AS query,
      SCHEMA_NAME as db,
      COUNT_STAR AS exec_count,
      sys.format_time(SUM_TIMER_WAIT) as total_latency,
      SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
      SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
      ROUND(IFNULL(SUM_CREATED_TMP_TABLES / NULLIF(COUNT_STAR, 0), 0)) AS avg_tmp_tables_per_query,
      ROUND(IFNULL(SUM_CREATED_TMP_DISK_TABLES / NULLIF(SUM_CREATED_TMP_TABLES, 0), 0) * 100) AS tmp_tables_to_disk_pct,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC;

# x$statements_with_temp_tables
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$statements_with_temp_tables (
  query,  db,  exec_count,  total_latency,  memory_tmp_tables,  disk_tmp_tables,  avg_tmp_tables_per_query,  tmp_tables_to_disk_pct,  first_seen,  last_seen,  digest
) AS
SELECT DIGEST_TEXT AS query,
      SCHEMA_NAME as db,
      COUNT_STAR AS exec_count,
      SUM_TIMER_WAIT as total_latency,
      SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
      SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
      ROUND(IFNULL(SUM_CREATED_TMP_TABLES / NULLIF(COUNT_STAR, 0), 0)) AS avg_tmp_tables_per_query,
      ROUND(IFNULL(SUM_CREATED_TMP_DISK_TABLES / NULLIF(SUM_CREATED_TMP_TABLES, 0), 0) * 100) AS tmp_tables_to_disk_pct,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC;

• 视图查询信息示例

admin@localhost : sys 12:54:26> select * from statements_with_temp_tables limit 1\G;
*************************** 1. row ***************************
                  query: SELECT `performance_schema` .  ... name` . `SUM_TIMER_WAIT` DESC 
                      db: sys
              exec_count: 2
          total_latency: 1.53 s
      memory_tmp_tables: 458
        disk_tmp_tables: 38
avg_tmp_tables_per_query: 229
  tmp_tables_to_disk_pct: 8
              first_seen: 2017-09-07 11:18:31
              last_seen: 2017-09-07 11:19:43
                  digest: 6f58edd9cee71845f592cf5347f8ecd7
1 row in set (0.00 sec)

ERROR: 
No query specified

admin@localhost : sys 12:54:28> select * from x$statements_with_temp_tables limit 1\G;
*************************** 1. row ***************************
                  query: SELECT `performance_schema` . `events_waits_summary_global_by_event_name` . `EVENT_NAME` AS `events` , `performance_schema` . `events_waits_summary_global_by_event_name` . \
`COUNT_STAR` AS `total` , `performance_schema` . `events_waits_summary_global_by_event_name` . `SUM_TIMER_WAIT` AS `total_latency` , `performance_schema` . \
`events_waits_summary_global_by_event_name` . `AVG_TIMER_WAIT` AS `avg_latency` , `performance_schema` . `events_waits_summary_global_by_event_name` . `MAX_TIMER_WAIT` AS `max_latency` \
FROM `performance_schema` . `events_waits_summary_global_by_event_name` WHERE ( ( `performance_schema` . `events_waits_summary_global_by_event_name` . `EVENT_NAME` != ? ) AND\
 ( `performance_schema` . `events_waits_summary_global_by_event_name` . `SUM_TIMER_WAIT` > ? ) ) ORDER BY `performance_schema` . `events_waits_summary_global_by_event_name` . \
`SUM_TIMER_WAIT` DESC 
                      db: sys
              exec_count: 2
          total_latency: 1529225370000
      memory_tmp_tables: 458
        disk_tmp_tables: 38
avg_tmp_tables_per_query: 229
  tmp_tables_to_disk_pct: 8
              first_seen: 2017-09-07 11:18:31
              last_seen: 2017-09-07 11:19:43
                  digest: 6f58edd9cee71845f592cf5347f8ecd7
1 row in set (0.00 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html

---

上一篇： statements_with_sorting,x$statements_with_sorting视图 |

下一篇： user_summary,x$user_summary视图