mysql performance_schema几个性能关注点

蔚落 2021-11-04 15:12 337阅读 0赞

events\_statements\_summary\_by\_digest：按照事件的语句进行聚合。

表结构如下：

CREATE TABLE \`events\_statements\_summary\_by\_digest\` (  
  \`SCHEMA\_NAME\` varchar(64) DEFAULT NULL COMMENT '库名',  
  \`DIGEST\` varchar(32) DEFAULT NULL COMMENT '对SQL\_TEXT做MD5产生的32位字符串。如果为consumer表中没有打开statement\_digest选项，则为NULL',  
  \`DIGEST\_TEXT\` longtext COMMENT '将语句中值部分用问号代替，用于SQL语句归类。如果为consumer表中没有打开statement\_digest选项，则为NULL。',  
  \`COUNT\_STAR\` bigint(20) unsigned NOT NULL COMMENT '事件计数',  
  \`SUM\_TIMER\_WAIT\` bigint(20) unsigned NOT NULL COMMENT '总的等待时间',  
  \`MIN\_TIMER\_WAIT\` bigint(20) unsigned NOT NULL COMMENT '最小等待时间',  
  \`AVG\_TIMER\_WAIT\` bigint(20) unsigned NOT NULL COMMENT '平均等待时间',  
  \`MAX\_TIMER\_WAIT\` bigint(20) unsigned NOT NULL COMMENT '最大等待时间',  
  \`SUM\_LOCK\_TIME\` bigint(20) unsigned NOT NULL COMMENT '锁时间总时长',  
  \`SUM\_ERRORS\` bigint(20) unsigned NOT NULL COMMENT '错误数的总',  
  \`SUM\_WARNINGS\` bigint(20) unsigned NOT NULL COMMENT '警告的总数',  
  \`SUM\_ROWS\_AFFECTED\` bigint(20) unsigned NOT NULL COMMENT '影响的总数目',  
  \`SUM\_ROWS\_SENT\` bigint(20) unsigned NOT NULL COMMENT '返回总数目',  
  \`SUM\_ROWS\_EXAMINED\` bigint(20) unsigned NOT NULL COMMENT '总的扫描的数目',  
  \`SUM\_CREATED\_TMP\_DISK\_TABLES\` bigint(20) unsigned NOT NULL COMMENT '创建磁盘临时表的总数目',  
  \`SUM\_CREATED\_TMP\_TABLES\` bigint(20) unsigned NOT NULL COMMENT '创建临时表的总数目',  
  \`SUM\_SELECT\_FULL\_JOIN\` bigint(20) unsigned NOT NULL COMMENT '第一个表全表扫描的总数目',  
  \`SUM\_SELECT\_FULL\_RANGE\_JOIN\` bigint(20) unsigned NOT NULL COMMENT '总的采用range方式扫描的数目',  
  \`SUM\_SELECT\_RANGE\` bigint(20) unsigned NOT NULL COMMENT '第一个表采用range方式扫描的总数目',  
  \`SUM\_SELECT\_RANGE\_CHECK\` bigint(20) unsigned NOT NULL COMMENT '',  
  \`SUM\_SELECT\_SCAN\` bigint(20) unsigned NOT NULL COMMENT '第一个表位全表扫描的总数目',  
  \`SUM\_SORT\_MERGE\_PASSES\` bigint(20) unsigned NOT NULL COMMENT '',  
  \`SUM\_SORT\_RANGE\` bigint(20) unsigned NOT NULL COMMENT '范围排序总数',  
  \`SUM\_SORT\_ROWS\` bigint(20) unsigned NOT NULL COMMENT '排序的记录总数目',  
  \`SUM\_SORT\_SCAN\` bigint(20) unsigned NOT NULL COMMENT '第一个表排序扫描总数目',  
  \`SUM\_NO\_INDEX\_USED\` bigint(20) unsigned NOT NULL COMMENT '没有使用索引总数',  
  \`SUM\_NO\_GOOD\_INDEX\_USED\` bigint(20) unsigned NOT NULL COMMENT '',  
  \`FIRST\_SEEN\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '第一次执行时间',  
  \`LAST\_SEEN\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后一次执行时间'  
) ENGINE=PERFORMANCE\_SCHEMA DEFAULT CHARSET=utf8;

关于SQL维度的统计信息主要集中在events\_statements\_summary\_by\_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息：  
哪个SQL执行最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest  
 ORDER BY COUNT\_STAR desc LIMIT 1\\G  
   
   
   
   
   
哪个SQL平均响应时间最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,AVG\_TIMER\_WAIT,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest   
ORDER BY AVG\_TIMER\_WAIT desc LIMIT 1\\G  
   
   
   
   
哪个SQL扫描的行数最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,AVG\_TIMER\_WAIT,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest  
 ORDER BY SUM\_ROWS\_EXAMINED desc LIMIT 1\\G  
   
   
   
哪个SQL使用的临时表最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,AVG\_TIMER\_WAIT,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest   
ORDER BY SUM\_CREATED\_TMP\_DISK\_TABLES、SUM\_CREATED\_TMP\_TABLES desc LIMIT 1\\G  
   
   
   
哪个SQL返回的结果集最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,AVG\_TIMER\_WAIT,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest   
ORDER BY SUM\_ROWS\_SENT desc LIMIT 1\\G  
   
   
   
哪个SQL排序数最多：  
SELECT SCHEMA\_NAME,DIGEST\_TEXT,COUNT\_STAR,AVG\_TIMER\_WAIT,SUM\_ROWS\_SENT,SUM\_ROWS\_EXAMINED,FIRST\_SEEN,LAST\_SEEN FROM events\_statements\_summary\_by\_digest   
ORDER BY SUM\_SORT\_ROWS desc LIMIT 1\\G  
   
   
   
哪个表、文件逻辑IO最多（热数据）：  
SELECT FILE\_NAME,EVENT\_NAME,COUNT\_READ,SUM\_NUMBER\_OF\_BYTES\_READ,COUNT\_WRITE,SUM\_NUMBER\_OF\_BYTES\_WRITE FROM file\_summary\_by\_instance ORDER BY  
 SUM\_NUMBER\_OF\_BYTES\_READ+SUM\_NUMBER\_OF\_BYTES\_WRITE DESC LIMIT 2\\G  
   
   
   
哪个索引使用最多：  
SELECT OBJECT\_NAME, INDEX\_NAME, COUNT\_FETCH, COUNT\_INSERT, COUNT\_UPDATE, COUNT\_DELETE FROM table\_io\_waits\_summary\_by\_index\_usage ORDER BY   
SUM\_TIMER\_WAIT DESC limit 1;  
   
   
   
哪个索引没有使用过：  
SELECT OBJECT\_SCHEMA, OBJECT\_NAME, INDEX\_NAME FROM table\_io\_waits\_summary\_by\_index\_usage WHERE INDEX\_NAME IS NOT NULL AND COUNT\_STAR = 0   
AND OBJECT\_SCHEMA <> 'mysql' ORDER BY OBJECT\_SCHEMA,OBJECT\_NAME;  
   
   
   
哪个等待事件消耗的时间最多：  
SELECT EVENT\_NAME, COUNT\_STAR, SUM\_TIMER\_WAIT, AVG\_TIMER\_WAIT FROM events\_waits\_summary\_global\_by\_event\_name WHERE event\_name != 'idle'   
ORDER BY SUM\_TIMER\_WAIT DESC LIMIT 1;

先写几个。