文章目录
- 零、前言
- 一、 查看最近的top sql
- 1.1 数据准备(如果已有数据可跳过此操作)
- 1.2 查询events_statements_summary_by_digest表
- 二、查看最近执行失败的SQL
- 2.1 开启第一个会话,执行错误sql
- 2.2 开启第二个会话,通过错误号找记录
- 2.2.1 通过events_statements_history表查询
- 2.2.2 通过events_statements_summary_by_digest表查询
- 三、总结
- 四、参考
零、前言
虚竹哥会组织大家一起学习同一篇文章,所以有什么问题都可以在群里问,群里的小伙伴可以迅速地帮到你,一个人可以走得很快,一群人可以走得很远,有一起学习交流的战友,是多么幸运的事情。
我的学习策略很简单,题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍,那意味着 SQL 已经筑基成功了。后面的进阶学习,可以继续跟着我,一起走向架构师之路。
今天的学习内容是:SQL进阶-查询优化- performance_schema系列实战四:查看最近的SQL执行信息
一、 查看最近的top sql
我们优化慢SQL的思路:优先优化执行次数最多的,然后是执行时间最长的。
可以使用events_statements_summary_by_digest表来查询经过统计之后的top sql
1.1 数据准备(如果已有数据可跳过此操作)
使用sysbench准备初始化数据
创建测试数据库sysbenchdemo
create database sysbenchdemo;
准备测试数据:
sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=8 \
--table-size=100000 \
--time=180 prepare
1.2 查询events_statements_summary_by_digest表
登录mysql数据库
use performance_schema;
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) as sum_time,
sys.format_time(MIN_TIMER_WAIT) as min_time,
sys.format_time(AVG_TIMER_WAIT) as avg_time,
sys.format_time(MAX_TIMER_WAIT) as max_time,
sys.format_time(SUM_LOCK_TIME) as sum_lock_time,
SUM_ROWS_AFFECTED,
SUM_ROWS_SENT,SUM_ROWS_EXAMINED
from events_statements_summary_by_digest
where SCHEMA_NAME is not null order by COUNT_STAR desc limit 10;
特别字段说明:
- DIGEST_TEXT 提供SQL语句文本
- COUNT_STAR 是执行此类型查询的次数
- UM_TIMER_WAIT 是执行此查询所用的总时间
- AVG_TIMER_WAIT 是每次执行的平均时间
- 大多数其他列是各个查询的值的总和
注意:DIGEST_TEXT的sql语句是有大小限制的,只会存储1024个字节,所以performance_schema提供的数据可以给慢查询日志分析做一个补充分析的依据。
二、查看最近执行失败的SQL
有这么一个场景:由于代码不规范,没有把sql报错的信息记录到错误日志中。那要如何看到具体的SQL文本,看看是否有哪里写错了。
实战模拟一个语法错误的SQL,使用events_statements_history_long或者events_statements_history表查询发生语法错误的SQL语句。
2.1 开启第一个会话,执行错误sql
登录mysql数据库
use performance_schema;
select * from;
2.2 开启第二个会话,通过错误号找记录
查询错误号为1064的记录
登录mysql数据库
use performance_schema;
2.2.1 通过events_statements_history表查询
select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,
sys.format_time(LOCK_TIME) as lock_time,
SQL_TEXT,CURRENT_SCHEMA,MESSAGE_TEXT,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,MYSQL_ERRNO
from events_statements_history where MYSQL_ERRNO=1064;
通过上面的sql语句,查找到了报错的语句。
但实际情况可能不知道错误号是多少,可以查询发生错误次数不为0的语句记录。
select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,
sys.format_time(LOCK_TIME) as lock_time,
SQL_TEXT,CURRENT_SCHEMA,MESSAGE_TEXT,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,MYSQL_ERRNO,errors
from events_statements_history
where errors>0;
如图所示,找到了错误的语句。
2.2.2 通过events_statements_summary_by_digest表查询
在会话1再执行一两个错误语句:
select * ;
select * from sysbenchdemo.sbtest4 where id between 100 and 2000 and xx=1;
在第二个会话中,在events_statements_summary_by_digest表中查询发生错误次数大于0的记录。
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,
sys.format_time(AVG_TIMER_WAIT) as avg_time,
sys.format_time(MAX_TIMER_WAIT) as max_time,
sys.format_time(SUM_LOCK_TIME) as sum_lock_time,
SUM_ERRORS,FIRST_SEEN,LAST_SEEN
from events_statements_summary_by_digest where SUM_ERRORS!=0
order by LAST_SEEN desc;
从图上可知,events_statements_summary_by_digest表中不记录具体的错误信息,只做错误语句统计。
需要查询到具体的错误信息(如:具体的错误代码,具体的错误提示信息以及具体的错误SQL文本等),还需要查询events_statements_history或者events_statements_history_long表
三、总结
通过本文学习,掌握了通过performance_schema库查看最近的top sql 和最近执行失败的sql。在查询优化的路上又前进了一大步,给自己点个赞吧,哈哈,也给虚竹哥也点个赞吧。
四、参考
应用示例荟萃 | performance_schema全方位介绍(下)
我是虚竹哥,我们明天见~