user_summary_by_statement_latency,x$user_summary_by_statement_latency

• 按照用户分组的总体语句统计信息，默认情况下按照语句总延迟时间(执行时间)降序排序。数据来源：events_statements_summary_by_user_by_event_name

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

  • user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
  • total：对应用户的语句总数量
  • total_latency：对应用户的语句总等待时间(执行时间)
  • max_latency：对应用户的语句单次最大等待时间(执行时间)
  • lock_latency：对应用户的语句锁等待的总时间
  • rows_sent：对应用户的语句返回给客户端的总数据行数
  • rows_examined：对应用户的语句从存储引擎读取的总数据行数
  • rows_affected：对应用户的语句影响的总数据行数
  • full_scans：对应用户的语句的全表扫描总次数

• 视图定义语句

# user_summary_by_statement_latency
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW user_summary_by_statement_latency (
  user,  total,  total_latency,  max_latency,  lock_latency,  rows_sent,  rows_examined,  rows_affected,  full_scans
) AS
SELECT IF(user IS NULL, 'background', user) AS user,
      SUM(count_star) AS total,
      sys.format_time(SUM(sum_timer_wait)) AS total_latency,
      sys.format_time(SUM(max_timer_wait)) AS max_latency,
      sys.format_time(SUM(sum_lock_time)) AS lock_latency,
      SUM(sum_rows_sent) AS rows_sent,
      SUM(sum_rows_examined) AS rows_examined,
      SUM(sum_rows_affected) AS rows_affected,
      SUM(sum_no_index_used) + SUM(sum_no_good_index_used) AS full_scans
  FROM performance_schema.events_statements_summary_by_user_by_event_name
GROUP BY IF(user IS NULL, 'background', user)
ORDER BY SUM(sum_timer_wait) DESC;

# x$user_summary_by_statement_latency
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$user_summary_by_statement_latency (
  user,  total,  total_latency,  max_latency,  lock_latency,  rows_sent,  rows_examined,  rows_affected,  full_scans
) AS
SELECT IF(user IS NULL, 'background', user) AS user,
      SUM(count_star) AS total,
      SUM(sum_timer_wait) AS total_latency,
      SUM(max_timer_wait) AS max_latency,
      SUM(sum_lock_time) AS lock_latency,
      SUM(sum_rows_sent) AS rows_sent,
      SUM(sum_rows_examined) AS rows_examined,
      SUM(sum_rows_affected) AS rows_affected,
      SUM(sum_no_index_used) + SUM(sum_no_good_index_used) AS full_scans
  FROM performance_schema.events_statements_summary_by_user_by_event_name
GROUP BY IF(user IS NULL, 'background', user)
ORDER BY SUM(sum_timer_wait) DESC;

• 视图查询信息示例

admin@localhost : sys 12:57:13> select * from user_summary_by_statement_latency limit 3;
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user      | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| admin      | 45487 | 1.05 h        | 45.66 m    | 19.02 s      |      6065 |      17578842 |          1544 |        258 |
| qfsys      |    9 | 929.43 ms    | 928.68 ms  | 0 ps        |        5 |            0 |            0 |          0 |
| background |    0 | 0 ps          | 0 ps        | 0 ps        |        0 |            0 |            0 |          0 |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)

admin@localhost : sys 12:57:34> select * from x$user_summary_by_statement_latency limit 3;
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| user      | total | total_latency    | max_latency      | lock_latency  | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
| admin      | 45562 | 3762457232413000 | 2739502018445000 | 19019928000000 |      6068 |      17579421 |          1544 |        259 |
| qfsys      |    9 |    929429421000 |    928682487000 |              0 |        5 |            0 |            0 |          0 |
| background |    0 |                0 |                0 |              0 |        0 |            0 |            0 |          0 |
+------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-latency.html

---

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

下一篇： user_summary_by_statement_type,x$user_summary_by_statement_type视图