vlambda博客
学习文章列表

这是关于MYSQL8 获取信息的方式的第六篇，终于到达了慢日志查询的位置，在MYSQL的DBA 的管理员的心目中，pt-query-digest 和 SLOW QUERY LOG 是分析慢查询的唯一的方式。实际上在MYSQL 8 中这样的慢查询的数据获取方式，已经被淘汰了，或者说不合时宜了。

主要的原因是获取信息的时效性的问题，获取慢查询需要去通过PT工具来读取SLOW LOG文件，并输出超过慢查询时间的语句信息。

SELECT

SCHEMA_NAME,

digest,

digest_text,

round(sum_timer_wait/ 1000000000000, 6) as second,

count_star,

now() as check_time

FROM performance_schema.events_statements_summary_by_digest

where digest_text not like 'CREATE%' and digest_text not like 'ALTER%'

ORDER BY sum_timer_wait DESC LIMIT 10;

通过查询的方式将慢查询语句摘录出来，但实际上又出现新的问题，就是每次将数据都搜索出来后，而这就会产生两个问题

1  重复数据的问题

https://blog.monyog.com/troubleshooting-mysql-performance-issues/

https://www.liquidweb.com/kb/mysql-performance-identifying-long-queries/

https://vladmihalcea.com/mysql-query-profiling-performance-schema/

2  如何汇总数据的问题

SELECT

SCHEMA_NAME,

digest,

digest_text,

round(sum_timer_wait/ 1000000000000, 6) as second,

count_star,

now() as check_time

FROM performance_schema.events_statements_summary_by_digest

where digest_text not like 'CREATE%' and digest_text not like 'ALTER%' and schema_name <> 'information_schema' and schema_name <> 'sys'

ORDER BY sum_timer_wait DESC LIMIT 10;

我们通过 digest 的方式进行信息的汇总，因为类似结构的查询语句会产生，  同样的码，在插入时将插入的方式变更为判断并无数据插入数据，有数据更新数据的方式并将这个位置变更为唯一索引。

在我们的performance_schema中 show tables like 'events_statement%',可以看到通过 event_statement 里有很多的从不同的维度对MYSQL 中产生的语句进行分析表，其中主要分为 current,  histogram , history , summary 几大类，他们分别也承载了慢查询中不同的功能。

下面我们就看看MYSQL 怎么来满足查询的需求

1  实时的观测，MYSQL 系统中语句的查询情况，可以称之为实时语句查询监控

select current_schema,SQL_TEXT,timer_wait/1000000000000 as 等待时间,lock_time/1000000000000 as 锁的时间,errors,rows_sent,rows_examined,select_scan,sort_rows

from events_statements_current

where  SQL_TEXT IS NOT NULL;

在MYSQL 8 

2  当查询某些表没有数据如

select * from events_statements_history_long;

这样的情况很可能是是由于你在 setup_consumers 中并没有打开相关的enabled 数据造成的。那么我们需要打开相关表的设置。

update setup_consumers set enabled = 'YES' where name = 'events_statements_history_long' ;

打开后，相关的表就可以接收到数据了

select * from events_statements_history_long;

select event_name,timer_wait/1000000000000 as wait_second,sql_text,digest,errors,rows_affected,rows_sent,created_tmp_disk_tables,select_scan

from events_statements_history_long

where current_schema  in ('sys','performance_schema','information_schema') and event_name like 'statement/sql%';

除此以外，我们还可以通过events_statements_summary_by_digest

来展示目前MYSQL 系统中 TOP 10 最慢的语句有那些。

select digest_text,count_star,(AVG_TIMER_WAIT/1000000000000) as avg_wait_time_second,(MAX_TIMER_WAIT/1000000000000) as max_timer_wait,SUM_ROWS_SENT,SUM_SELECT_SCAN,SUM_NO_INDEX_USED,fIRST_SEEN,LAST_SEEN,QUERY_SAMPLE_TEXT

from events_statements_summary_by_digest

where (AVG_TIMER_WAIT/1000000000000) > 0.1 and digest_text not like 'CREATE%' and digest_text not like 'ALTER%'

ORDER BY LAST_SEEN limit 10;

以上的信息足以满足日常的慢查询分析的需求。除此以外，我们还可以衍生出更多与监控有关的信息集合

1   通过performance_schema  中查询的信息，来发现MYSQL 系统中未使用的索引信息。这样就可以查看索引的使用情况，来判断我们建立的索引在使用中是否存在根本就用不到的情况。

SELECT DISTINCT s.table_schema, s.table_name, s.index_name

--     , i.count_star

  FROM information_schema.statistics AS s

  LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage AS i

         ON (s.table_schema = i.object_schema AND s.table_name = i.object_name AND s.index_name = i.index_Name)

 WHERE s.table_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')

   AND s.index_name != 'PRIMARY'

   AND i.count_star = 0

 ORDER BY s.table_schema, s.table_name, s.index_name；

2  查询MYSQL 查询中出现生成物理磁盘查询的情况

SELECT schema_name, digest_text AS statement, count_star AS cnt

     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables

  FROM performance_schema.events_statements_summary_by_digest

 WHERE sum_created_tmp_disk_tables >= 0 and schema_name not in ('sys','information_schema','performance_schema','mysql')  and schema_name is not null

    ;

    

上面由于要有展示，但此时系统中并未有符合语句所以将 sum_created_tmp_disk_tables  设置成 >= 0 实际上可以改成下面的写法。

SELECT schema_name, digest_text AS statement, count_star AS cnt

     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables

  FROM performance_schema.events_statements_summary_by_digest

 WHERE sum_created_tmp_disk_tables > 0 and schema_name not in ('sys','information_schema','performance_schema','mysql')  and schema_name is not null and 

 digest_text not like 'CREATE%' and  digest_text not like 'ALTER%' and digest_text not like 'explain%' and digest_text not like 'set%' 

    ;

通过以上的一些语句完全可以摆脱之前的SLOW LOG的工作模式，实际上其他的数据库本身也并没有SLOW LOG ，都是通过系统表的方式来获取慢查询语句，如 SQL SERVER ，ORACLE 等。

MYSQL 作为开源流行的数据库，从MYSQL 8 后，完全可以不在使用原有的方式去捕捉慢查询数据。至少不会为设置某些过滤语句的时间在去犯难，所有运行语句的时间都会被记录，我们所做的只是需要过滤这些语句即可。

最后下面的SQL 可以统计整体预计执行的信息并且以，从最慢的SQL 开始，包含各种语句的维度信息

SELECT left(digest_text, 64)

     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms

     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms

     , ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms

     , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms

     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms

     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms

     , ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms

     , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms

     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms

     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms

     , ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms

     , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms

     , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen

     , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen

     , COUNT(*) as cnt

  FROM performance_schema.events_statements_history_long

  JOIN performance_schema.global_status AS isgs

 WHERE isgs.variable_name = 'UPTIME'

 GROUP BY LEFT(digest_text,64)

 ORDER BY tot_exec_ms DESC