mysql的topsql_TOP SQL监控之MySQL篇

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，如果是微服务框架，貌似每个服务都需要独立去查看监控数据；