这是关于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
