statements_with_full_table_scans,x$statements_with_full_table_scans

• 查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认情况下按照全表扫描次数与语句总次数百分比和语句总延迟时间(执行时间)降序排序。数据来源：events_statements_summary_by_digest

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

  • query：经过标准化转换的语句字符串
  • db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
  • exec_count：语句执行的总次数
  • total_latency：语句执行的总等待时间（执行时间）
  • no_index_used_count：语句执行没有使用索引扫描表（而是使用全表扫描）的总次数
  • no_good_index_used_count：语句执行没有使用到更好的索引扫描表的总次数
  • no_index_used_pct：语句执行没有使用索引扫描表(而是使用全表扫描)的次数与语句执行总次数的百分比
  • rows_sent：语句执行从表返回给客户端的总数据行数
  • rows_examined：语句执行从存储引擎读取的总数据行数
  • rows_sent_avg：每个语句执行从表中返回客户端的平均数据行数
  • rows_examined_avg：每个语句执行从存储引擎读取的平均数据行数
  • first_seen：该语句第一次出现的时间
  • last_seen：该语句最近一次出现的时间
  • digest：语句摘要计算的md5 hash值

• 视图定义语句

# statements_with_full_table_scans
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW statements_with_full_table_scans (
  query,  db,  exec_count,  total_latency,  no_index_used_count,  no_good_index_used_count,  no_index_used_pct,  rows_sent,
  rows_examined,  rows_sent_avg,  rows_examined_avg,  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_NO_INDEX_USED AS no_index_used_count,
      SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
      ROUND(IFNULL(SUM_NO_INDEX_USED / NULLIF(COUNT_STAR, 0), 0) * 100) AS no_index_used_pct,
      SUM_ROWS_SENT AS rows_sent,
      SUM_ROWS_EXAMINED AS rows_examined,
      ROUND(SUM_ROWS_SENT/COUNT_STAR) AS rows_sent_avg,
      ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS rows_examined_avg,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE (SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0)
  AND DIGEST_TEXT NOT LIKE 'SHOW%'
ORDER BY no_index_used_pct DESC, total_latency DESC;

# x$statements_with_full_table_scans
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$statements_with_full_table_scans (
  query,  db,  exec_count,  total_latency,  no_index_used_count,  no_good_index_used_count,  no_index_used_pct,  rows_sent,
  rows_examined,  rows_sent_avg,  rows_examined_avg,  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_NO_INDEX_USED AS no_index_used_count,
      SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
      ROUND(IFNULL(SUM_NO_INDEX_USED / NULLIF(COUNT_STAR, 0), 0) * 100) AS no_index_used_pct,
      SUM_ROWS_SENT AS rows_sent,
      SUM_ROWS_EXAMINED AS rows_examined,
      ROUND(SUM_ROWS_SENT/COUNT_STAR) AS rows_sent_avg,
      ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS rows_examined_avg,
      FIRST_SEEN as first_seen,
      LAST_SEEN as last_seen,
      DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
WHERE (SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0)
  AND DIGEST_TEXT NOT LIKE 'SHOW%'
ORDER BY no_index_used_pct DESC, total_latency DESC;

• 视图查询信息示例

admin@localhost : sys 12:51:27> select * from statements_with_full_table_scans limit 1\G;
*************************** 1. row ***************************
                  query: SELECT `performance_schema` .  ... ance` . `SUM_TIMER_WAIT` DESC 
                      db: sys
              exec_count: 1
          total_latency: 938.45 us
    no_index_used_count: 1
no_good_index_used_count: 0
      no_index_used_pct: 100
              rows_sent: 3
          rows_examined: 318
          rows_sent_avg: 3
      rows_examined_avg: 318
              first_seen: 2017-09-07 09:34:12
              last_seen: 2017-09-07 09:34:12
                  digest: 5b5b4e15a8703769d9b9e23e9e92d499
1 row in set (0.01 sec)

ERROR: 
No query specified

admin@localhost : sys 12:51:36> select * from x$statements_with_full_table_scans 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
    no_index_used_count: 4
no_good_index_used_count: 0
      no_index_used_pct: 100
              rows_sent: 8
          rows_examined: 942517
          rows_sent_avg: 2
      rows_examined_avg: 235629
              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-full-table-scans.html

---

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

下一篇： statements_with_runtimes_in_95th_percentile,x$statements_with_runtimes_in_95th_percentile视图