x$ps_digest_95th_percentile_by_avg_us - xiaoboluo768/mysql-system-schema GitHub Wiki
-
帮助视图(辅助试图)，计算语句百分之九十五的平均执行时间分布值，帮助statements_with_runtimes_in_95th_percentile视图输出语句平均执行时间大于95%平均分布值的语句统计信息，默认按照直方图百分比值排序。数据来源：events_statements_summary_by_digest、sys.x$ps_digest_avg_latency_distribution
-
x$ps_digest_95th_percentile_by_avg_us视图字段含义如下
- avg_us：语句平均执行时间(微秒单位)
- percentile：直方图百分比值，代表该语句的平均执行时间的一个分布广度
-
视图定义语句
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$ps_digest_95th_percentile_by_avg_us (
avg_us, percentile
) AS
SELECT s2.avg_us avg_us,
IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest), 0), 0) percentile
FROM sys.x$ps_digest_avg_latency_distribution AS s1
JOIN sys.x$ps_digest_avg_latency_distribution AS s2
ON s1.avg_us <= s2.avg_us
GROUP BY s2.avg_us
HAVING IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest), 0), 0) > 0.95
ORDER BY percentile
LIMIT 1;
- 视图查询信息示例
admin@localhost : sys 12:59:57> select * from x$ps_digest_95th_percentile_by_avg_us;
+--------+------------+
| avg_us | percentile |
+--------+------------+
| 450384 | 0.9528 |
+--------+------------+
1 row in set (0.02 sec)
- 参考链接：辅助视图无参考链接