剖析MYSQL查询之一慢查询

一、安装工具
　　地址：
二、开启mysql慢查询
　　方式一： 通过配置/etc/mysql/my.cnf文件
　　　

[mysqld]
slow_query_log=on
slow_query_log_file=/tmp/slow_query_log.txt
long_query_time=1
slow_launch_time=1

　　重启mysql数据库
　　

sudo /etc/init.d/mysql restart

三、分析慢查询
　　　

pt-query-digest /tmp/slow_query_log.txt

结果：

# A software update is available:
#   * The current version for Percona::Toolkit is 2.2.20.


# 420ms user time, 20ms system time, 38.95M rss, 127.20M vsz
# Current date: Wed Jan  4 13:21:52 2017
# Hostname: wind
# Files: /tmp/slow_query_log.txt
# Overall: 7 total, 2 unique, 0.03 QPS, 0.14x concurrency ________________
# Time range: 2017-01-04 13:13:46 to 13:17:45
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            33s      2s     20s      5s     19s      6s      2s
# Lock time          595us       0   114us    85us   113us    36us    84us
# Rows sent            601       1     100   85.86   97.36   33.72   97.36
# Rows examine      40.05M       0   6.68M   5.72M   6.61M   2.31M   6.61M
# Query size           365      41      54   52.14   51.63    3.91   51.63

# Profile
# Rank Query ID           Response time Calls R/Call  V/M   Item
# ==== ================== ============= ===== ======= ===== ==============
#    1 0xA46990F88417662A 20.0003 61.3%     1 20.0003  0.00 SELECT dual
#    2 0x65E47A879ED591AC 12.6266 38.7%     6  2.1044  0.01 SELECT stock.SC

# Query 1: 0 QPS, 0x concurrency, ID 0xA46990F88417662A at byte 0 ________
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2017-01-04 13:13:46
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14       1
# Exec time     61     20s     20s     20s     20s     20s       0     20s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    11      41      41      41      41      41       0      41
# String:
# Databases    stock
# Hosts        localhost
# Users        stock
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `stock` LIKE 'dual'\G
#    SHOW CREATE TABLE `stock`.`dual`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT sleep(20) from dual
LIMIT 0, 50000\G

# Query 2: 0.09 QPS, 0.20x concurrency, ID 0x65E47A879ED591AC at byte 424
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2017-01-04 13:16:41 to 13:17:45
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         85       6
# Exec time     38     13s      2s      2s      2s      2s   155ms      2s
# Lock time    100   595us    85us   114us    99us   113us    13us   110us
# Rows sent     99     600     100     100     100     100       0     100
# Rows examine 100  40.05M   6.68M   6.68M   6.68M   6.68M       0   6.68M
# Query size    88     324      54      54      54      54       0      54
# String:
# Databases    stock
# Hosts        localhost
# Users        stock
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `stock` LIKE 'SC'\G
#    SHOW CREATE TABLE `stock`.`SC`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM stock.SC
where c_id='100'
LIMIT 0, 50000\G