TOP SQL监控之MySQL篇

慢查询日志slow_log

优点：MySQL自带，记录的慢SQL语句完整；
缺点：需要登录mysql服务器；如果slow_log文件太大，还需要利用其他工具分析日志，比如mysqldumpslow；

performance_shema

优点：MySQL自带，只要有查询权限即可；
缺点：记录的SQL语句可能不完整（SQL语句过长，依稀记得是这样）

授权语句：grant select on performance_schema.* to ‘test‘@‘%‘;

常用查询语句：

****查询平均响应时间最多****
SELECT AVG_TIMER_WAIT/1000000000000 as AVG_TIMER_WAIT_S,essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY AVG_TIMER_WAIT DESC limit 100;

****查询行读最多****
SELECT SUM_ROWS_EXAMINED/COUNT_STAR as AVG_ROWS_EXAMINED,essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY SUM_ROWS_EXAMINED/COUNT_STAR DESC limit 100;

**** 查询执行次数最多 ****
SELECT *
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR??DESC limit 100;

**** 查询排序次数最多 ****
    SELECT SUM_SORT_ROWS/COUNT_STAR as AVG_SORT_ROWS,essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY SUM_SORT_ROWS/COUNT_STAR DESC limit 100;

**** 查询返回结果集最多 ****
SELECT SUM_ROWS_SENT/COUNT_STAR as AVG_ROWS_SENT, essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY SUM_ROWS_SENT/COUNT_STAR??DESC limit 100;

**** 查询是否无索引 ****
SELECT SUM_NO_INDEX_USED/COUNT_STAR as AVG_NO_INDEX_USED, essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY SUM_NO_INDEX_USED/COUNT_STAR??DESC limit 100;

**** 查询锁定时间最多 ****
SELECT SUM_LOCK_TIME/COUNT_STAR/1000000000000 as AVG_LOCK_TIME, essbd.*
FROM performance_schema.events_statements_summary_by_digest essbd
ORDER BY SUM_LOCK_TIME/COUNT_STAR??DESC limit 100;

**** 查询IO最多 ****
SELECT AVG_TIMER_READ/1000000000000 as AVG_TIMER_READ_S,
AVG_TIMER_FETCH /1000000000000 as AVG_TIMER_FETCH_S,tiwsbtt.*
FROM performance_schema.table_io_waits_summary_by_table tiwsbtt
order by AVG_TIMER_READ desc limit 100;

druid monitor

如果服务端是用的druid连接池，那么可以配置druid monitor来进行监控。

优点：从应用层直接进行监控，还可以看到连接池的使用情况；
缺点：需要额外配置druid monitor，如果是微服务框架，貌似每个服务都需要独立去查看监控数据；

原文：https://blog.51cto.com/14437683/2568096