mysqldumpslow分析慢日志

[root@localhost home]# /usr/local/mysql/bin/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, 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

awk '/# Time: 2021-02-04T08/,/# Time: 2021-02-04T20/' slow.log > slow202010204.log 
返回查询时间最多20条语句
/usr/local/mysql/bin/mysqldumpslow -t 20 -s t slow202010204.log 
返回检索记录数最多20条语句
/usr/local/mysql/bin/mysqldumpslow -t 20 -s r slow202010204.log 
返回按查询时间所有的语句
/usr/local/mysql/bin/mysqldumpslow -s t slow202010204.log 
把分析的日志重定向文件中
/usr/local/mysql/bin/mysqldumpslow -s t slow202010204.log > slowlog20200204.sql

查询按检索数据最多的全表扫描前20的sql语句：
SELECT
	QUERY,
	db,
	exec_count,
	total_latency,
	rows_examined
FROM
	sys.x$statements_with_full_table_scans
ORDER BY
	rows_examined DESC
LIMIT 20


查询按排序做多的20条语句：
SELECT
	QUERY,
	db,
	exec_count,
	total_latency,
  rows_sorted
FROM
	x$statements_with_sorting
ORDER BY
	rows_sorted DESC
LIMIT 20

查询按总延迟做多的20条语句：
SELECT
	QUERY,
	db,
	exec_count,
	total_latency,
	rows_examined
FROM
	x$statement_analysis
ORDER BY
	total_latency DESC
LIMIT 20