mysql的dba_hist_sql*

小鱼儿 2022-01-30 02:45 230阅读 0赞

01.schema\_tables\_with\_full\_table\_scans,x$schema\_tables\_with\_full\_table\_scans  
查询执行过全扫描访问的表，默认情况下按照表扫描的行数进行降序排序。数据来源：performance\_schema.table\_io\_waits\_summary\_by\_index\_usage

视图查询语句文本

SELECT object\_schema,  
  object\_name,  
  count\_read AS rows\_full\_scanned,  
  sys.format\_time(sum\_timer\_wait) AS latency  
FROM performance\_schema.table\_io\_waits\_summary\_by\_index\_usage  
WHERE index\_name IS NULL  
AND count\_read > 0  
ORDER BY count\_read DESC;  
下面我们看看使用该视图查询返回的结果

object\_schema：schema名称

OBJECT\_NAME：表名

rows\_full\_scanned：全表扫描的总数据行数

latency：完整的表扫描操作的总延迟时间（执行时间）

02.statement\_analysis,x$statement\_analysis  
查看语句汇总统计信息，这些视图模仿MySQL企业版监控的查询分析视图列出语句的聚合统计信息，默认情况下按照总延迟时间(执行时间)降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest

视图查询语句文本

SELECT sys.format\_statement(DIGEST\_TEXT) AS query,  
  SCHEMA\_NAME AS db,  
  IF(SUM\_NO\_GOOD\_INDEX\_USED > 0 OR SUM\_NO\_INDEX\_USED > 0, '\*', '') AS full\_scan,  
  COUNT\_STAR AS exec\_count,  
  SUM\_ERRORS AS err\_count,  
  SUM\_WARNINGS AS warn\_count,  
  sys.format\_time(SUM\_TIMER\_WAIT) AS total\_latency,  
  sys.format\_time(MAX\_TIMER\_WAIT) AS max\_latency,  
  sys.format\_time(AVG\_TIMER\_WAIT) AS avg\_latency,  
  sys.format\_time(SUM\_LOCK\_TIME) AS lock\_latency,  
  SUM\_ROWS\_SENT AS rows\_sent,  
  ROUND(IFNULL(SUM\_ROWS\_SENT / NULLIF(COUNT\_STAR, 0), 0)) AS rows\_sent\_avg,  
  SUM\_ROWS\_EXAMINED AS rows\_examined,  
  ROUND(IFNULL(SUM\_ROWS\_EXAMINED / NULLIF(COUNT\_STAR, 0), 0))  AS rows\_examined\_avg,  
  SUM\_ROWS\_AFFECTED AS rows\_affected,  
  ROUND(IFNULL(SUM\_ROWS\_AFFECTED / NULLIF(COUNT\_STAR, 0), 0))  AS rows\_affected\_avg,  
  SUM\_CREATED\_TMP\_TABLES AS tmp\_tables,  
  SUM\_CREATED\_TMP\_DISK\_TABLES AS tmp\_disk\_tables,  
  SUM\_SORT\_ROWS AS rows\_sorted,  
  SUM\_SORT\_MERGE\_PASSES AS sort\_merge\_passes,  
  DIGEST AS digest,  
  FIRST\_SEEN AS first\_seen,  
  LAST\_SEEN as last\_seen  
FROM performance\_schema.events\_statements\_summary\_by\_digest  
ORDER BY SUM\_TIMER\_WAIT DESC;  
下面我们看看使用该视图查询返回的结果

\# 不带x$前缀的视图  
admin@localhost : sys 12:46:07> select \* from statement\_analysis limit 1\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        query: ALTER TABLE \`test\` ADD INDEX \`i\_k\` ( \`test\` )   
          db: xiaoboluo  
    full\_scan:   
  exec\_count: 2  
    err\_count: 2  
  warn\_count: 0  
total\_latency: 56.56 m  
  max\_latency: 43.62 m  
  avg\_latency: 28.28 m  
lock\_latency: 0 ps  
    rows\_sent: 0  
rows\_sent\_avg: 0  
rows\_examined: 0  
rows\_examined\_avg: 0  
rows\_affected: 0  
rows\_affected\_avg: 0  
  tmp\_tables: 0  
tmp\_disk\_tables: 0  
  rows\_sorted: 0  
sort\_merge\_passes: 0  
      digest: f359a4a8407ee79ea1d84480fdd04f62  
  first\_seen: 2017-09-07 11:44:35  
    last\_seen: 2017-09-07 12:36:47  
1 row in set (0.14 sec)  
   
\# 带x$前缀的视图  
admin@localhost : sys 12:46:34> select \* from x$statement\_analysis limit 1\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        query: ALTER TABLE \`test\` ADD INDEX \`i\_k\` ( \`test\` )   
          db: xiaoboluo  
    full\_scan:   
  exec\_count: 2  
    err\_count: 2  
  warn\_count: 0  
total\_latency: 3393877088372000  
  max\_latency: 2617456143674000  
  avg\_latency: 1696938544186000  
lock\_latency: 0  
    rows\_sent: 0  
rows\_sent\_avg: 0  
rows\_examined: 0  
rows\_examined\_avg: 0  
rows\_affected: 0  
rows\_affected\_avg: 0  
  tmp\_tables: 0  
tmp\_disk\_tables: 0  
  rows\_sorted: 0  
sort\_merge\_passes: 0  
      digest: f359a4a8407ee79ea1d84480fdd04f62  
  first\_seen: 2017-09-07 11:44:35  
    last\_seen: 2017-09-07 12:36:47  
1 row in set (0.01 sec)  
视图字段含义如下：

query：经过标准化转换的语句字符串，不带x$的视图默认长度限制为64字节，带x$的视图默认长度限制为1024字节

db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL

full\_scan：语句全表扫描查询的总次数

exec\_count：语句执行的总次数

err\_count：语句发生的错误总次数

warn\_count：语句发生的警告总次数

total\_latency：语句的总延迟时间（执行时间）

max\_latency：单个语句的最大延迟时间（执行时间）

avg\_latency：每个语句的平均延迟时间（执行时间）

lock\_latency：语句的总锁等待时间

rows\_sent：语句返回客户端的总数据行数

rows\_sent\_avg：每个语句返回客户端的平均数据行数

rows\_examined：语句从存储引擎读取的总数据数

rows\_examined\_avg：每个语句从存储引擎检查的平均数据行数

rows\_affected：语句影响的总数据行数

rows\_affected\_avg：每个语句影响的平均数据行数

tmp\_tables：语句执行时创建的内部内存临时表的总数

tmp\_disk\_tables：语句执行时创建的内部磁盘临时表的总数

rows\_sorted：语句执行时出现排序的总数据行数

sort\_merge\_passes：语句执行时出现排序合并的总次数

digest：语句摘要计算的md5 hash值

first\_seen：该语句第一次出现的时间

last\_seen：该语句最近一次出现的时间

03.statements\_with\_errors\_or\_warnings,x$statements\_with\_errors\_or\_warnings  
查看产生错误或警告的语句，默认情况下，按照错误数量和警告数量降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest

PS：这里大家注意了，语法错误或者产生警告的语句通常错误日志中不记录，慢查询日志中也不记录，只有查询日志中会记录所有的语句，但不携带语句执行状态的信息，所以无法判断是否是执行有错误或者有警告的语句，通过该视图可以查询到语句执行的状态信息，以后开发执行了某个语句有语法错误来问你想查看具体的语句文本的时候，别再说MySQL不支持查看啦。

视图查询语句文本

SELECT sys.format\_statement(DIGEST\_TEXT) AS query,  
  SCHEMA\_NAME as db,  
  COUNT\_STAR AS exec\_count,  
  SUM\_ERRORS AS errors,  
  IFNULL(SUM\_ERRORS / NULLIF(COUNT\_STAR, 0), 0) \* 100 as error\_pct,  
  SUM\_WARNINGS AS warnings,  
  IFNULL(SUM\_WARNINGS / NULLIF(COUNT\_STAR, 0), 0) \* 100 as warning\_pct,  
  FIRST\_SEEN as first\_seen,  
  LAST\_SEEN as last\_seen,  
  DIGEST AS digest  
FROM performance\_schema.events\_statements\_summary\_by\_digest  
WHERE SUM\_ERRORS > 0  
OR SUM\_WARNINGS > 0  
ORDER BY SUM\_ERRORS DESC, SUM\_WARNINGS DESC;  
下面我们看看使用该视图查询返回的结果

\# 不带x$前缀的视图  
admin@localhost : sys 12:47:36> select \* from statements\_with\_errors\_or\_warnings limit 1\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  query: SELECT \* FROM \`test\` LIMIT ? FOR UPDATE   
    db: xiaoboluo  
exec\_count: 5  
errors: 3  
error\_pct: 60.0000  
warnings: 0  
warning\_pct: 0.0000  
first\_seen: 2017-09-07 11:29:44  
last\_seen: 2017-09-07 12:45:58  
digest: 9f50f1fc79fc6ea678dec6576b7d7faa  
1 row in set (0.00 sec)  
   
\# 带x$前缀的视图  
admin@localhost : sys 12:47:45> select \* from x$statements\_with\_errors\_or\_warnings limit 1\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  query: SELECT \* FROM \`test\` LIMIT ? FOR UPDATE   
    db: xiaoboluo  
exec\_count: 5  
errors: 3  
error\_pct: 60.0000  
warnings: 0  
warning\_pct: 0.0000  
first\_seen: 2017-09-07 11:29:44  
last\_seen: 2017-09-07 12:45:58  
digest: 9f50f1fc79fc6ea678dec6576b7d7faa  
1 row in set (0.00 sec)  
视图字段含义如下：

query：经过标准化转换的语句字符串

db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL

exec\_count：语句执行的总次数

errors：语句发生的错误总次数

error\_pct：语句产生错误的次数与语句总执行次数的百分比

warnings：语句发生的警告总次数

warning\_pct：语句产生警告的与语句总执行次数的百分比

first\_seen：该语句第一次出现的时间

last\_seen：该语句最近一次出现的时间

digest：语句摘要计算的md5 hash值

04.statements\_with\_full\_table\_scans,x$statements\_with\_full\_table\_scans  
查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认情况下按照全表扫描次数与语句总次数百分比和语句总延迟时间(执行时间)降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest

视图查询语句文本

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$前缀的视图  
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)  
\# 带x$前缀的视图，要注意：从这里可以明显看到带x$的视图的query字段值较长，\\  
该长度受系统变量performance\_schema\_max\_digest\_length的值控制，默认为1024字节，\\  
而不带x$的视图该字段进一步使用了sys.format\_statement()函数进行截断，\\  
该函数的截断长度限制受sys.sys\_config配置表中的statement\_truncate\_len 配置值控制，默认值为64字节。\\  
所以，你会看到对于query语句文本，两者的输出长度有很大差别，如果你需要通过这些文本来甄别语句，那么请留意这个差异  
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)  
视图字段含义如下：

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值

05.statements\_with\_runtimes\_in\_95th\_percentile,x$statements\_with\_runtimes\_in\_95th\_percentile  
查看平均执行时间值大于95%的平均执行时间的语句（可近似地认为是平均执行时间超长的语句），默认情况下按照语句平均延迟(执行时间)降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest、sys.x$ps\_digest\_95th\_percentile\_by\_avg\_us

两个视图都使用两个辅助视图sys.x$ps\_digest\_avg\_latency\_distribution和sys.x$ps\_digest\_95th\_percentile\_by\_avg\_us   
\* x$ps\_digest\_avg\_latency\_distribution视图对performance\_schema.events\_statements\_summary\_by\_digest表中的avg\_timer\_wait列转换为微秒单位，然后使用round()函数以微秒为单位转换为整型值并命名为avg\_us列，根据avg\_us分组并使用count()统计行数并命名为cnt列   
\* x$ps\_digest\_95th\_percentile\_by\_avg\_us视图在内部通过调用两个(两次)x$ps\_digest\_avg\_latency\_distribution视图形成联结表查询形式，使用联结条件ON s1.avg\_us <= s2.avg\_us的形式，按照s2.avg\_us分组，并根据SUM(s1.cnt)/select COUNT()...performance\_schema.events\_statements\_summary\_by\_digest > 0.95进行having分组后再过滤，实际上该视图最终返回的是每个语句平均执行时间相对于整个performance\_schema.events\_statements\_summary\_by\_digest表统计值的直方图   
\* statements\_with\_runtimes\_in\_95th\_percentile,x$statements\_with\_runtimes\_in\_95th\_percentile视图内部再次调用x$ps\_digest\_95th\_percentile\_by\_avg\_us视图与performance\_schema.events\_statements\_summary\_by\_digest表联结打印直方图分布值大于0.95的performance\_schema.events\_statements\_summary\_by\_digest表中的原始统计信息

视图查询语句文本

SELECT sys.format\_statement(DIGEST\_TEXT) AS query,  
  SCHEMA\_NAME as db,  
  IF(SUM\_NO\_GOOD\_INDEX\_USED > 0 OR SUM\_NO\_INDEX\_USED > 0, '\*', '') AS full\_scan,  
  COUNT\_STAR AS exec\_count,  
  SUM\_ERRORS AS err\_count,  
  SUM\_WARNINGS AS warn\_count,  
  sys.format\_time(SUM\_TIMER\_WAIT) AS total\_latency,  
  sys.format\_time(MAX\_TIMER\_WAIT) AS max\_latency,  
  sys.format\_time(AVG\_TIMER\_WAIT) AS avg\_latency,  
  SUM\_ROWS\_SENT AS rows\_sent,  
  ROUND(IFNULL(SUM\_ROWS\_SENT / NULLIF(COUNT\_STAR, 0), 0)) AS rows\_sent\_avg,  
  SUM\_ROWS\_EXAMINED AS rows\_examined,  
  ROUND(IFNULL(SUM\_ROWS\_EXAMINED / NULLIF(COUNT\_STAR, 0), 0)) 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 stmts  
JOIN sys.x$ps\_digest\_95th\_percentile\_by\_avg\_us AS top\_percentile  
ON ROUND(stmts.avg\_timer\_wait/1000000) >= top\_percentile.avg\_us  
ORDER BY AVG\_TIMER\_WAIT DESC;  
下面我们看看使用该视图查询返回的结果

\# 不带x$前缀的视图  
admin@localhost : sys 12:53:06> select \* from statements\_with\_runtimes\_in\_95th\_percentile limit 1\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        query: ALTER TABLE \`test\` ADD INDEX \`i\_k\` ( \`test\` )   
          db: xiaoboluo  
    full\_scan:   
  exec\_count: 2  
    err\_count: 2  
  warn\_count: 0  
total\_latency: 56.56 m  
  max\_latency: 43.62 m  
  avg\_latency: 28.28 m  
    rows\_sent: 0  
rows\_sent\_avg: 0  
rows\_examined: 0  
rows\_examined\_avg: 0  
  first\_seen: 2017-09-07 11:44:35  
    last\_seen: 2017-09-07 12:36:47  
      digest: f359a4a8407ee79ea1d84480fdd04f62  
1 row in set (0.01 sec)  
   
\# 带x$前缀的视图  
admin@localhost : sys 12:53:10> select \* from x$statements\_with\_runtimes\_in\_95th\_percentile limit 1\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        query: ALTER TABLE \`test\` ADD INDEX \`i\_k\` ( \`test\` )   
          db: xiaoboluo  
    full\_scan:   
  exec\_count: 2  
    err\_count: 2  
  warn\_count: 0  
total\_latency: 3393877088372000  
  max\_latency: 2617456143674000  
  avg\_latency: 1696938544186000  
    rows\_sent: 0  
rows\_sent\_avg: 0  
rows\_examined: 0  
rows\_examined\_avg: 0  
  first\_seen: 2017-09-07 11:44:35  
    last\_seen: 2017-09-07 12:36:47  
      digest: f359a4a8407ee79ea1d84480fdd04f62  
1 row in set (0.01 sec)  
视图字段含义如下：

query：经过标准化转换的语句字符串

db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL

full\_scan：语句全表扫描查询的总次数

exec\_count：语句执行的总次数

err\_count：语句发生的错误总次数

warn\_count：语句发生的警告总次数

total\_latency：语句执行的总延迟时间（执行时间）

max\_latency：单个语句的最大延迟时间（执行时间）

avg\_latency：每个语句的平均延迟时间（执行时间）

rows\_sent：语句执行从表返回给客户端的总数据行数

rows\_sent\_avg：每个语句执行从表中返回客户端的平均数据行数

rows\_examined：语句执行从存储引擎检查的总数据行数

rows\_examined\_avg：每个语句执行从存储引擎检查的平均数据行数

first\_seen：该语句第一次出现的时间

last\_seen：该语句最近一次出现的时间

digest：语句摘要计算的md5 hash值

06.statements\_with\_sorting,x$statements\_with\_sorting  
查看执行了文件排序的语句，默认情况下按照语句总延迟时间(执行时间)降序排序，数据来源：performance\_schema.events\_statements\_summary\_by\_digest

视图查询语句文本

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$前缀的视图  
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)  
\# 带x$前缀的视图  
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)  
视图字段含义如下：

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值

07.statements\_with\_temp\_tables,x$statements\_with\_temp\_tables  
查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest

视图查询语句文本

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$前缀的视图  
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)  
\# 带x$前缀的视图  
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)  
视图字段含义如下：

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值

本期内容就介绍到这里，本期内容参考链接如下：

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-tables-with-full-table-scans.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statement-analysis.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-errors-or-warnings.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-full-table-scans.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-runtimes-in-95th-percentile.html

https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-sorting.html  
\---------------------   
作者：woqutechteam   
来源：CSDN   
原文：https://blog.csdn.net/woqutechteam/article/details/82492152   
版权声明：本文为博主原创文章，转载请附上博文链接！