mysql慢日志问题排查

1.下载慢日志文件

show variables like '%query%';

变量	值
long_query_time	0.100000（执行时间超过0.1秒的会记录到慢日志文件）
slow_query_log	ON （表明慢查询日志打开）
slow_query_log_file	/export/data/mysql/log/slow.log（慢日志文件路径）

可以根据slow_query_log_file的值来下载慢日志文件

2.使用mysqldumpslow分析慢日志文件

mysqldumpslow slow.log

结果默认按照单个语句最长执行时间倒序排序，类似这样：

Count: 47  Time=5.09s (239s)  Lock=0.00s (0s)  Rows=38.0 (1786), regular_purch_rw[regular_purch_rw]@3hosts
  select
  t.id, t.plan_id, t.pre_period_id, t.order_id, t.period_num, t.price, t.coupon_price, t.pay_mode, t.receive_id, t.regular_discount,
  t.distribute_time, t.status, t.sub_status, t.created, t.modified,t.freight, t.coupon_id, t.coupon_batch_key, t.order_time, t.isupdatepromise, t.wright_pushed
  from
  regular_purchase_plan_period_info as t
  where
  order_id is NULL
  and sub_status in (N,N)
  and status = N
  and pay_mode = N
  and distribute_time between 'S' and 'S'
  and wright_pushed != N

Count: 46  Time=4.35s (199s)  Lock=0.00s (0s)  Rows=0.0 (0), regular_purch_rw[regular_purch_rw]@4hosts
  select
  id, plan_id, father_order_id, period_num, price, coupon_id, coupon_price, regular_discount,
  freight, distribute_time, pay_mode, business_type, status, sub_status, order_time,
  is_update_promise, wright_pushed, pin, name, province_code, city_code, county_code,
  town_code, province_name, city_name, county_name, town_name, address, mobile, ext_map,
  yn, created, modified
  from
  plan_detail
  where
  sub_status in (N,N)
  and status = N
  and pay_mode = N
  and distribute_time between 'S' and 'S'
  and wright_pushed != N
  and yn = N

count:执行次数
time：执行最长时间（累计耗费总时长）
lock：等待锁的时间
rows：发送给客户端的行总数(扫描的行总数),
用户
抽象sql语句

下面介绍一下mysqldumpslow的详细用法

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

mysqldumpslow可以按照不同的方式排序，使用-s选项；
可以只列出topn的sql，使用-t选项

3. 逐个sql语句分析

根据mysqldumpslow给出的信息，可以进行单个sql针对性的分析。

• 单次执行过长，可以使用explain进行分析，看是否使用索引，是都使用合适的索引。
• 执行次数过多，可以考虑减少重复执行，减轻单台机器压力（读写分离，读走从库之类的操作）。
• 锁持有时间过长，看是否有长事务，是否事务没有提交，是否锁竞争激烈，是否有死锁，是否加锁顺序不合理
• 扫描行数过多，还是看索引。
• 返回用户行数过多，看是否能将sql拆分，返回合理的行数。