sql慢查询日志的位置
set global slow_query_log_file
set global long_query_time = 1 记录执行时间大于1s的sql
set global log_queries_not_using_indexes = on 记录下没有使用索引的sql
1.通过mysql 自带的mysqldumpslow
查询具体sql 的执行时间,执行次数,
Count: 1 Time=0.05s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
explain select customer_id,first_name,last_name from customer
2.通过pt-query-digest + 慢查询日志的路径
展示其中一条的分析结果
Query 3: 0 QPS, 0x concurrency, ID 0x0F48AC368B665207 at byte 5218 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-01-20 19:49:12
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 1
# Exec time 10 47ms 47ms 47ms 47ms 47ms 0 47ms
# Lock time 0 144us 144us 144us 144us 144us 0 144us
# Rows sent 89 15.67k 15.67k 15.67k 15.67k 15.67k 0 15.67k
# Rows examine 42 15.67k 15.67k 15.67k 15.67k 15.67k 0 15.67k
# Query size 1 21 21 21 21 21 0 21
# String:
# Databases sakila
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `sakila` LIKE 'payment'\G
# SHOW CREATE TABLE `sakila`.`payment`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from payment\G