statements_with_sorting,x$statements_with_sorting

• 查看执行了文件排序的语句，默认情况下按照语句总延迟时间(执行时间)降序排序，数据来源：events_statements_summary_by_digest

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

  • query：经过标准化转换的语句字符串
  • db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
  • exec_count：语句执行的总次数
  • total_latency：语句执行的总等待时间（执行时间）
  • sort_merge_passes：语句执行发生的语句排序合并的总次数
  • avg_sort_merges：针对发生排序合并的语句，每个语句的平均排序合并次数(SUM_SORT_MERGE_PASSES/COUNT_STAR)
  • sorts_using_scans：语句排序执行全表扫描的总次数
  • sort_using_range：语句排序执行范围扫描的总次数
  • rows_sorted：语句执行发生排序的总数据行数
  • avg_rows_sorted：针对发生排序的语句，每个语句的平均排序数据行数(SUM_SORT_ROWS/COUNT_STAR)
  • first_seen：该语句第一次出现的时间
  • last_seen：该语句最近一次出现的时间
  • digest：语句摘要计算的md5 hash值

• 视图定义语句

# statements_with_sorting
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW statements_with_sorting (
  query,  db,  exec_count,  total_latency,  sort_merge_passes,  avg_sort_merges,  sorts_using_scans,  sort_using_range,  rows_sorted,  avg_rows_sorted,  first_seen,  last_seen,  digest
) AS
SELECT sys.format_statement(DIGEST_TEXT) AS query,
      SCHEMA_NAME db,
      COUNT_STAR AS exec_count,
      sys.format_time(SUM_TIMER_WAIT) AS total_latency,
      SUM_SORT_MERGE_PASSES AS sort_merge_passes,
      ROUND(IFNULL(SUM_SORT_MERGE_PASSES / NULLIF(COUNT_STAR, 0), 0)) AS avg_sort_merges,
      SUM_SORT_SCAN AS sorts_using_scans,
      SUM_SORT_RANGE AS sort_using_range,
      SUM_SORT_ROWS AS rows_sorted,
      ROUND(IFNULL(SUM_SORT_ROWS / NULLIF(COUNT_STAR, 0), 0)) AS avg_rows_sorted,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 0
ORDER BY SUM_TIMER_WAIT DESC;

# x$statements_with_sorting
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$statements_with_sorting (
  query,  db,  exec_count,  total_latency,  sort_merge_passes,  avg_sort_merges,  sorts_using_scans,  sort_using_range,  rows_sorted,  avg_rows_sorted,  first_seen,  last_seen,  digest
) AS
SELECT DIGEST_TEXT AS query,
      SCHEMA_NAME db,
      COUNT_STAR AS exec_count,
      SUM_TIMER_WAIT AS total_latency,
      SUM_SORT_MERGE_PASSES AS sort_merge_passes,
      ROUND(IFNULL(SUM_SORT_MERGE_PASSES / NULLIF(COUNT_STAR, 0), 0)) AS avg_sort_merges,
      SUM_SORT_SCAN AS sorts_using_scans,
      SUM_SORT_RANGE AS sort_using_range,
      SUM_SORT_ROWS AS rows_sorted,
      ROUND(IFNULL(SUM_SORT_ROWS / NULLIF(COUNT_STAR, 0), 0)) AS avg_rows_sorted,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 0
ORDER BY SUM_TIMER_WAIT DESC;

• 视图查询信息示例

admin@localhost : sys 12:53:16> select * from statements_with_sorting limit 1\G;
*************************** 1. row ***************************
            query: SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC 
              db: sys
      exec_count: 4
    total_latency: 46.53 s
sort_merge_passes: 48
  avg_sort_merges: 12
sorts_using_scans: 16
sort_using_range: 0
      rows_sorted: 415391
  avg_rows_sorted: 103848
      first_seen: 2017-09-07 12:36:58
        last_seen: 2017-09-07 12:38:37
          digest: 59abe341d11b5307fbd8419b0b9a7bc3
1 row in set (0.00 sec)

ERROR: 
No query specified

admin@localhost : sys 12:53:35> select * from x$statements_with_sorting limit 1\G;
*************************** 1. row ***************************
            query: SELECT IF ( ( `locate` ( ? , `ibp` . `TABLE_NAME` ) = ? ) , ? , REPLACE ( `substring_index` ( `ibp` . `TABLE_NAME` , ?, ... ) , ?, ... ) ) AS `object_schema` , REPLACE ( `substring_index` \
( `ibp` . `TABLE_NAME` , ? , - (?) ) , ?, ... ) AS `object_name` , SUM ( IF ( ( `ibp` . `COMPRESSED_SIZE` = ? ) , ? , `ibp` . `COMPRESSED_SIZE` ) ) AS `allocated` , SUM ( `ibp` . `DATA_SIZE` ) AS `data` , \
COUNT ( `ibp` . `PAGE_NUMBER` ) AS `pages` , COUNT ( IF ( ( `ibp` . `IS_HASHED` = ? ) , ?, ... ) ) AS `pages_hashed` , COUNT ( IF ( ( `ibp` . `IS_OLD` = ? ) , ?, ... ) ) AS `pages_old` , `round` \
( `ifnull` ( ( SUM ( `ibp` . `NUMBER_RECORDS` ) / `nullif` ( COUNT ( DISTINCTROW `ibp` . `INDEX_NAME` ) , ? ) ) , ? ) , ? ) AS `rows_cached` FROM `information_schema` . `innodb_buffer_page` `ibp` WHERE \
( `ibp` . `TABLE_NAME` IS NOT NULL ) GROUP BY `object_schema` , `object_name` ORDER BY SUM ( IF ( ( `ibp` . `COMPRESSED_SIZE` = ? ) , ? , `ibp` . `COMPRESSED_SIZE` ) ) DESC 
              db: sys
      exec_count: 4
    total_latency: 46527032553000
sort_merge_passes: 48
  avg_sort_merges: 12
sorts_using_scans: 16
sort_using_range: 0
      rows_sorted: 415391
  avg_rows_sorted: 103848
      first_seen: 2017-09-07 12:36:58
        last_seen: 2017-09-07 12:38:37
          digest: 59abe341d11b5307fbd8419b0b9a7bc3
1 row in set (0.00 sec)

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

---

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

下一篇： statements_with_temp_tables,x$statements_with_temp_tables视图