Mysql慢查询分析

# Mysql慢查询分析
## 内置慢查询分析工具mysqldumpslow
在开启了mysql慢查询日之后，可以通过mysql内置的分析工具来进行日志的分析工作。

```shell
[root@master mariadb]# mysqldumpslow --help
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, ae, c, l, r, e, t), 'at' is default  # 筛选输出哪种sql
                al: average lock time    # 平均锁定时间最长的sql
                ar: average rows sent   # 平均输出最多行的sql
                at: average query time # 平均查询时间最长的sql
                 c: count                      # 执行次数最多的sql
                 l: lock time                  # 锁定时间最长的sql
                 r: rows sent                 # 查询结果最多的sql
                 t: query time               # 查询时间最长的sql
  -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
```

### 示例
假定慢查询日志为/var/log/mysql/slow.log
```
# 查看平均查询时间最长的10条sql，执行时间由长到短排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

```

## 使用ELK
[使用ELK](https://blog.ihoiwan.com/project-9/doc-50/ "使用ELK")