适用范围

操作系统：linux、windows
数据库版本：MySQL 8

概述

当mysql数据库在运行CPU使用率很高时，需要找到是什么SQL消耗了很多的CPU，下面就是本课程中介绍的系统性能视图来找到top sql的一种方法。

视图说明

1）sys.statement_analysis视图

这个视图已经根据SQL运行的总时间进行排序，直接查limit 1 就是执行时间累积最长的第1条SQL，limit 10就是累积时间最长的前10条SQL
如下查询，SELECT COUNT ( * ) FROM t , … WHERE t . id = t1 . id执行了95次，总时间为2.07min排在了第一位。

mysql> select * from sys.statement_analysis limit 3\G;
*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM `t` ,  ... WHERE `t` . `id` = `t1` . `id`
               db: xhy
        full_scan: 
       exec_count: 95
        err_count: 0
       warn_count: 0
    total_latency: 2.07 min
      max_latency: 1.62 s
      avg_latency: 1.31 s
     lock_latency: 11.89 ms
        rows_sent: 95
    rows_sent_avg: 1
    rows_examined: 181789530
rows_examined_avg: 1913574
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 0c73452dccafd67b38aeb4fbd7bdbca846fc0dc001603fd3824d4bcf4b159d8b
       first_seen: 2022-08-25 22:14:15.912862
        last_seen: 2022-08-25 22:17:27.768462
*************************** 2. row ***************************
            query: SELECT COUNT ( * ) FROM `t` ,  ... WHERE `t` . `id` > `t1` . `id`
               db: xhy
        full_scan: *
       exec_count: 1
        err_count: 1
       warn_count: 0
    total_latency: 2.00 min
      max_latency: 2.00 min
      avg_latency: 2.00 min
     lock_latency: 166.00 us
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 233304279
rows_examined_avg: 233304279
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 56e203adeb50c95984ff47329ee4ed5f0f3a43ed6b75e78ff56e2f77e719004b
       first_seen: 2022-08-25 22:13:59.527473
        last_seen: 2022-08-25 22:13:59.527473
*************************** 3. row ***************************
            query: SELECT COUNT ( * ) FROM `test` ... WHERE `t1` . `id` > `t` . `id`
               db: xhy
        full_scan: *
       exec_count: 4
        err_count: 1
       warn_count: 0
    total_latency: 1.35 min
      max_latency: 1.35 min
      avg_latency: 20.27 s
     lock_latency: 788.00 us
        rows_sent: 3
    rows_sent_avg: 1
    rows_examined: 967270
rows_examined_avg: 241818
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 0922d0e3ec6a4f486f383ca8310188715763f3e1169c50e5341b676510125d13
       first_seen: 2022-08-25 21:54:22.490109
        last_seen: 2022-08-25 22:11:12.213559
3 rows in set (0.00 sec)

2）sys.statements_with_runtimes_in_95th_percentile视图

这个视图是根据平均时间取前5%的top sql信息。

mysql> select * from sys.statements_with_runtimes_in_95th_percentile\G;
*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM `t` ,  ... WHERE `t` . `id` > `t1` . `id`
               db: xhy
        full_scan: *
       exec_count: 1
        err_count: 1
       warn_count: 0
    total_latency: 2.00 min
      max_latency: 2.00 min
      avg_latency: 2.00 min
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 233304279
rows_examined_avg: 233304279
       first_seen: 2022-08-25 22:13:59.527473
        last_seen: 2022-08-25 22:13:59.527473
           digest: 56e203adeb50c95984ff47329ee4ed5f0f3a43ed6b75e78ff56e2f77e719004b
*************************** 2. row ***************************
            query: SELECT COUNT ( * ) FROM `test` ... WHERE `t1` . `id` > `t` . `id`
               db: xhy
        full_scan: *
       exec_count: 5
        err_count: 1
       warn_count: 0
    total_latency: 1.35 min
      max_latency: 1.35 min
      avg_latency: 16.22 s
        rows_sent: 4
    rows_sent_avg: 1
    rows_examined: 967270
rows_examined_avg: 193454
       first_seen: 2022-08-25 21:54:22.490109
        last_seen: 2022-08-25 22:24:22.899901
           digest: 0922d0e3ec6a4f486f383ca8310188715763f3e1169c50e5341b676510125d13
2 rows in set (0.01 sec)

ERROR: 
No query specified

3）performance_schema.events_statements_summary_by_digest视图

这个视图对相同语句进行了合并，主要的将常量替换成了 ？，并且有处理其平均执行时间，总的执行时间等统计信息

mysql> select * from performance_schema.events_statements_summary_by_digest order by avg_timer_wait desc limit 3\G;
*************************** 1. row ***************************
                SCHEMA_NAME: xhy
                     DIGEST: 56e203adeb50c95984ff47329ee4ed5f0f3a43ed6b75e78ff56e2f77e719004b
                DIGEST_TEXT: SELECT COUNT ( * ) FROM `t` , `t` `t1` WHERE `t` . `id` > `t1` . `id`
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 119803684332000
             MIN_TIMER_WAIT: 119803684332000
             AVG_TIMER_WAIT: 119803684332000
             MAX_TIMER_WAIT: 119803684332000
              SUM_LOCK_TIME: 166000000
                 SUM_ERRORS: 1
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 233304279
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 1
            SUM_SELECT_SCAN: 1
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 1
     SUM_NO_GOOD_INDEX_USED: 1
                 FIRST_SEEN: 2022-08-25 22:13:59.527473
                  LAST_SEEN: 2022-08-25 22:13:59.527473
                QUANTILE_95: 120226443461744
                QUANTILE_99: 120226443461744
               QUANTILE_999: 120226443461744
          QUERY_SAMPLE_TEXT: NULL
          QUERY_SAMPLE_SEEN: 0000-00-00 00:00:00.000000
    QUERY_SAMPLE_TIMER_WAIT: 0
*************************** 2. row ***************************
                SCHEMA_NAME: xhy
                     DIGEST: 0922d0e3ec6a4f486f383ca8310188715763f3e1169c50e5341b676510125d13
                DIGEST_TEXT: SELECT COUNT ( * ) FROM `test` `t1` , `t` WHERE `t1` . `id` > `t` . `id`
                 COUNT_STAR: 5
             SUM_TIMER_WAIT: 81100294725000
             MIN_TIMER_WAIT: 269396000
             AVG_TIMER_WAIT: 16220058945000
             MAX_TIMER_WAIT: 81098191546000
              SUM_LOCK_TIME: 957000000
                 SUM_ERRORS: 1
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 4
          SUM_ROWS_EXAMINED: 967270
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 1
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 4
            SUM_SELECT_SCAN: 5
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 5
     SUM_NO_GOOD_INDEX_USED: 4
                 FIRST_SEEN: 2022-08-25 21:54:22.490109
                  LAST_SEEN: 2022-08-25 22:24:22.899901
                QUANTILE_95: 83176377110269
                QUANTILE_99: 83176377110269
               QUANTILE_999: 83176377110269
          QUERY_SAMPLE_TEXT: SELECT COUNT(*) FROM test t1,t where t1.id>t.id
          QUERY_SAMPLE_SEEN: 2022-08-25 22:24:22.899901
    QUERY_SAMPLE_TIMER_WAIT: 376996000
*************************** 3. row ***************************
                SCHEMA_NAME: xhy
                     DIGEST: 6a7dede54bd8f9d1b48e17ef188043bb22cf14aae20d568262c9beafea5fbe13
                DIGEST_TEXT: SELECT COUNT ( * ) FROM `test` `t1` , `t` WHERE `t` . `a` = `t1` . `id` AND `t` . `a` <= ?
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 3694064664000
             MIN_TIMER_WAIT: 3694064664000
             AVG_TIMER_WAIT: 3694064664000
             MAX_TIMER_WAIT: 3694064664000
              SUM_LOCK_TIME: 95000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 3145728
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 1
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 1
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2022-08-25 21:43:19.757658
                  LAST_SEEN: 2022-08-25 21:43:19.757658
                QUANTILE_95: 3801893963205
                QUANTILE_99: 3801893963205
               QUANTILE_999: 3801893963205
          QUERY_SAMPLE_TEXT: select count(*) from test t1,t where t.a=t1.id and t.a<=10000
          QUERY_SAMPLE_SEEN: 2022-08-25 21:43:19.757658
    QUERY_SAMPLE_TIMER_WAIT: 3694064664000
mysql> 

总结

sys.statement_analysis、sys.statements_with_runtimes_in_95th_percentile、performance_schema.events_statements_summary_by_digest
这3个视图都是默认开启的，MySQL安装运行无需调整任何设置就生效的，通过这几个视图可以在无需修改客户任何配置的情况下获取top sql信息。