慢sql监控

曾经终败给现在 2023-03-13 12:29 31阅读 0赞

# 1、开启慢sql日志 #

## 1.1 windows ##

window的mysql配置，编辑C:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini，添加如下

#是否开启慢查询日志，1表示开启，0表示关闭
    slow_query_log = 1
    #慢查询日志存储路径
    slow_query_log_file = D:/mysql/log/mysqlslowquery.log
    #慢查询阈值，超过1秒，记录日志
    long_query_time = 1

重启mysql

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70][]

## 1.2 linux ##

配置文件

>vim /etc/my.cnf
    
    #是否开启慢查询日志，1表示开启，0表示关闭
    slow_query_log = 1
    #慢查询日志存储路径
    slow_query_log_file = /opt/mysql/mysqlslowquery.log
    #慢查询阈值，超过1秒，记录日志
    long_query_time = 1

因为mysql的linux用户为mysql，需要将权限授权给mysql

>chown -R mysql:mysql mysql/

## 1.3 查看是否开启成功 ##

# 2、示例 #

## 2.1 创建表 ##

CREATE TABLE `user2` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) NOT NULL COMMENT '姓名',
      `age` int(11) NOT NULL COMMENT '年龄',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

## 2.2 添加数据 ##

添加一条数据

INSERT INTO `user2`(`name`, `age`) VALUES ('测试0', 12); #第一次添加数据

重复添加，直到数据达到百万级以上

insert into user2(name,age) select name,age from user2;

## 2.3 生成慢sql日志 ##

>select count(*) from user;

消耗时间超过1秒，日志记录

# Time: 2020-05-13T12:04:34.134555Z
    # User@Host: root[root] @ localhost [::1]  Id:     3
    # Query_time: 1.213271  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 5242880
    SET timestamp=1589371474;
    select count(*) from user;

慢sql

>update user2 set `name`=CONCAT(`name`,id);
    >select * from user2 where name="测试10";
    >select * from user2 where age=16;

# 3、日志分析工具**mysqldumpslow** #

## **3.1 命令** ##

>mysqldumpslow -h
    Option h requires an argument
    ERROR: bad option
    
    Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
    
    Parse and summarize the MySQL slow query log. Options are
    
      --verbose    verbose
      --debug      debug
      --help       write this text to standard output
    
      -v           verbose
      -d           debug
      -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                    al: average lock time
                    ar: average rows sent
                    at: average query time
                     c: count
                     l: lock time
                     r: rows sent
                     t: query time  
      -r           reverse the sort order (largest last instead of first)
      -t NUM       just show the top n queries
      -a           don't abstract all numbers to N and strings to 'S'
      -n NUM       abstract numbers with at least n digits within names
      -g PATTERN   grep: only consider stmts that include this string
      -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                   default is '*', i.e. match all
      -i NAME      name of server instance (if using mysql.server startup script)
      -l           don't subtract lock time from total time

\-s 排序方式

*  c：访问计数
 *  l：锁定时间
 *  r:返回记录
 *  al：平均锁定时间
 *  ar：平均访问记录数
 *  at：平均查询时间

\-t 是top n的意思，返回多少条数据。

\-g 可以跟上正则匹配模式，大小写不敏感。

## 3.2 示例 ##

### 3.2.1 查询返回记录（返回数据条数）最多的3个sql ###

>mysqldumpslow -s r -t 3 /opt/mysql/mysqlslowquery.log
    
    Reading mysql slow query log from /opt/mysql/mysqlslowquery.log
    Count: 5  Time=2.01s (10s)  Lock=0.00s (0s)  Rows=2.0 (10), root[root]@[111.200.40.10]
      select * from user2 where name="S"
    
    Count: 8  Time=1.20s (9s)  Lock=0.00s (0s)  Rows=1.0 (8), root[root]@[111.200.40.10]
      select count(*) from user2
    
    Count: 2  Time=129.66s (259s)  Lock=21.64s (43s)  Rows=0.0 (0), root[root]@[111.200.40.10]
      update user2 set `name`=CONCAT(`name`,id)

count为查询次数

### 3.2.2 查询平均访问次数最多的3条sql ###

>mysqldumpslow -s ar -t 3 /opt/mysql/mysqlslowquery.log
    
    Reading mysql slow query log from /opt/mysql/mysqlslowquery.log
    Count: 6  Time=2.01s (12s)  Lock=0.00s (0s)  Rows=2.7 (16), root[root]@[111.200.40.10]
      select * from user2 where name="S"
    
    Count: 8  Time=1.20s (9s)  Lock=0.00s (0s)  Rows=1.0 (8), root[root]@[111.200.40.10]
      select count(*) from user2
    
    Count: 2  Time=129.66s (259s)  Lock=21.64s (43s)  Rows=0.0 (0), root[root]@[111.200.40.10]
      update user2 set `name`=CONCAT(`name`,id)

### 3.2.3 查询平均访问次数最多,并且里面含有name字符的3条sql ###

>mysqldumpslow -s ar -t 3 -g 'name' /opt/mysql/mysqlslowquery.log
    
    Reading mysql slow query log from /opt/mysql/mysqlslowquery.log
    Count: 6  Time=2.01s (12s)  Lock=0.00s (0s)  Rows=2.7 (16), root[root]@[111.200.40.10]
      select * from user2 where name="S"
    
    Count: 2  Time=129.66s (259s)  Lock=21.64s (43s)  Rows=0.0 (0), root[root]@[111.200.40.10]
      update user2 set `name`=CONCAT(`name`,id)
    
    Count: 1  Time=17.12s (17s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[111.200.40.10]
      insert into user2(name,age) select name,age from user2

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70]: /images/20230312/94815fed994e4c5a9dfa41cf62835c2d.png