mysqlsla慢查询分析工具

谁践踏了优雅 2023-07-25 09:51 42阅读 0赞

## 什么是mysqlsla？ ##

Mysqlsla 是daniel-nichter 用perl 写的一个脚本，专门用于处理分析Mysql的日志而存在。

## mysqlsla 能解决什么问题？ ##

作为一名Mysql DBA，日常工作中处理日志是再正常不过的事情了。 通过Mysql的日志主要分为：General log，slow log，binary log三种。通 过query日志，我们可以分析业务的逻辑，业务特点。通过slow log，我们可以找到服务器的瓶颈。通过binary log，我们可以恢复数据。Mysqlsla 可以处理其中的任意日志，这也是我喜欢它的最主要原因之一。

## mysqlsla安装 ##

下载地址 [https://pan.baidu.com/s/1kfBYu49RtdSOhiT4hlvj\_A][https_pan.baidu.com_s_1kfBYu49RtdSOhiT4hlvj_A]  
tar -xvfz mysqlsla2.03.tar.gz  
cd mysqlsla2.03  
perl Makefile.PL  
make  
make install

## 常见问题 ##

1、没有perl环境会报错，进行yum安装即可。  
yum install perl  
2、Can’t locate ExtUtils/MakeMaker.pm

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

## mysqlsla的使用 ##

前提，需要开启mysql慢查询功能

show variables like ‘%slow%’;![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nYmFva2FuZw_size_16_color_FFFFFF_t_70]  
set global slow\_query\_log=ON; \#开启MySQL慢查询功能  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nYmFva2FuZw_size_16_color_FFFFFF_t_70 1]  
可以看到slow\_query\_log的状态为ON

show variables like “long\_query\_time”; \#查看MySQL慢查询时间设置，默认10秒  
set global long\_query\_time=5; \#修改为记录5秒内的查询  
还可以通过修改mysql配置文件来开启慢查询

slow-query-log = on  #开启MySQL慢查询功能
    slow_query_log_file = /mysql/mysql5.7/data/localhost-slow.log  #设置MySQL慢查询日志路径
    long_query_time = 5  #修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询
    log-queries-not-using-indexes = on  #记录未使用索引的查询

遇到如下错误的解决办法  
![在这里插入图片描述][20200414153716751.png]  
错误原因是系统没有安装DBI组件，也可以yum install perl-DBI

wget http://www.cpan.org/modules/by-module/DBI/DBI-1.643.tar.gz
    tar -zxvf DBI-1.643.tar.gz
    cd DBI-1.643
    perl Makefile.PL 
    make
    make install

![在这里插入图片描述][20200414155744432.png]  
\-lt是-log-type的简写，类型有slow, general, binary, msl, udl，分析慢查询可以指定为slow  
\-sort 指定用什么参数来对结果排序，默认使用t\_sum进行排序。

t_sum:按总时间排序
    c_sum:按总次数排序
    c_sum_p: sql语句执行次数占总执行次数的百分比。

\-top:  
显示sql的数量，默认是10,表示按规则取排序的前多少条

语法介绍，网上找的

* --log-type (-lt) TYPE LOGS
        用于指定解析的是什么类型的日志，不过放心，即便你不指定，它默认也会自动去判断基本的日志，slow，general，binary，msl or udl。
    
    * --db-inheritance
        这个参数还是通过源码发现的，之前没认真读文档。这个参数十分重要，之前对这个参数用的不多，但是如果你不指定，解析出来的日志中，很有可能对应的SQL语句，找不到database来源。
    
    * --explain (-ex)
        默认是disable的，并且必须report格式为standard。作用就是对每一条uniq的SQL进行explain分析，非常实用。
    
    * --grep PATTERN
        正则匹配功能。您可以对你的report做任意的正则匹配。比如：我想查关于‘table_lc’,'table_cl'这两张表的所有分析结果。
        普通的方式，是没有办法查到的，但是您可以这样做：
        mysqlsla --grep '(\btable_lc\b)|(\btable_cl\b)'  db10-037.log
    
    * --meta-filter (-mf) CONDTIONS
        这是filter中的一种，这里简单介绍，详细介绍看filter章节。
        这里的meta，指的是meta-property，meta-property后面介绍。典型的就是：c_sum，指的是SQL数量的总和。
        基本使用情况： [meta][op][value]
        特别要注意这里的op， 没有别的，就三种， >,<,=. 之前自以为事，>=，死活得不到自己想要的结果。
        这里[op]有个特殊用法：t_sum between 10 and 100 ， 可以这样写： 't_sum>10,t_sum<100'
        举例说明： 我想查看ark_db的所有结果
            mysqlsla -mf 'db=ark_db' --top 100 slow_1.log slow_2.log
    
    * --statement-filter (-sf) CONDTIONS
        这是filter中的另一种
        别的不说，直接看使用形式：[+-][type],[type]
        [TYPE] 的值就是 :SELECT,CREATE,DROP,UPDATE,INSERT,etc.
        举例说明：如果我只想查看select，create类型的SQL。
            mysqsla -sf '+SELECT,CREATE' slow.log
        举例说明：如果我想查看除了select以外的所有结果。
            mysqlsla -sf '-SELECT' 类型的SQL。
    
    * --top N
        显示默认降序的top N， default 为10.
    
    * --sort META
        根据Meta的值，进行排序。默认slow log的meta 为t_sum，至于t_sum是什么，请看filter章节。
    
    * --report-format (-rf) FILE
        这里可以自定义报表。但是必须按照固定的格式，以便mysqlsla来解析。
        如：
        (extra command line options)
         HEADER
        (header line format)
        (header line values)
        REPORT
        report line format
        report line values
        这个非常有用，尤其是后期可以用脚本来处理自己自定义的报表，非常方便。这也是其强大功能的一个体现。
    
    * --reports (-R) REPORTS
        可以指定以某种格式进行输出报表。
        固定报表形式有：standard,time-all,print-unique,print-all,dump
        必须注意的地方：如果你选择的是time-all，time-each-query，这里会默认是safetySQL statement filter of"+SELECT,USE"。
        但是，如果你自定义了-statment-fileter，那么这些dml语句就会在生产环境中真实的执行，会污染线上的数据，非常危险，请特别注意和小心。
    
    * --udl-format (-uf) FILE
        可以自定义log，让mysqlsla来解析。这个比较复杂，在后面的章节讲。
    
    * --replay FILE
        可以重新replay file里面的SQL。实用，但是我一般不会用，担心数据安全。
    
    总结： 基本上，以上的命令，可以解决你50%的需求，而且是非常常用的功能，自己去试试吧。
    这里可以简单举几个例子：
    
    1）列出以用户是usr_rx，含有ac_开头的，非SELECT类型的 前100000条记录。 --默认m/P/io 大小写敏感
        mysqlsla --grep '\bac_.*\b' -mf user='usr_rx' -sf '-SELECT' --top 100000  slow.log
    
    2) 列出含有broker_system_messages_bj 或者 broker_system_messages_oth的记录. --关键是需要打上括号。
        mysqlsla --grep '(\bbroker_system_messages_bj\b)|(\bbroker_system_messages_oth.*\b)'  slow.log
    
    3) 列出ajk_propertys的所有查询，且按照SQL的rows_exam排序。
        mysqlsla --grep '\bajk_propertys\b' -sf '+SELECT'  --sort='re_sum'  --db-inheritance slow.log

\#查询记录最多的20个sql语句，并写到select.log中去

mysqlsla -lt slow --sort t_sum --top 20  /data/mysql/127-slow.log >/tmp/select.log
    
    #统计慢查询文件为/data/mysql/127-slow.log的所有select的慢查询sql，并显示执行时间最长的100条sql，并写到sql_select.log中去
    
    mysqlsla -lt slow  -sf "+select" -top 100  /data/mysql/127-slow.log >/tmp/sql_select.log
    
    #统计慢查询文件为/data/mysql/127-slow.log的数据库为mydata的所有select和update的慢查询sql，并查询次数最多的100条sql，并写到sql_num.sql中去
    
    mysqlsla -lt slow  -sf "+select,update" -top 100 -sort c_sum  -db mydata /data/mysql/127-slow.log >/tmp/sql_num.log

[https_pan.baidu.com_s_1kfBYu49RtdSOhiT4hlvj_A]: https://pan.baidu.com/s/1kfBYu49RtdSOhiT4hlvj_A
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nYmFva2FuZw_size_16_color_FFFFFF_t_70]: /images/20230528/d9ecb4f14e944c27ab229890ee403bf7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nYmFva2FuZw_size_16_color_FFFFFF_t_70 1]: /images/20230528/4aa56ce9288549208c2ec4ad4b5d6379.png
[20200414153716751.png]: /images/20230528/034ceca7b40d4a759f6f484efd827cec.png
[20200414155744432.png]: /images/20230528/9767296fad214f198731ccfcae33643a.png