Mysql 慢查询和profile分析工具

众所周知，大访问量的情况下，可添加节点或改变架构可有效的缓解数据库压力，不过一切的原点，都是从单台mysql开始的。下面总结一些使用过或者研究过的经验，从配置以及调节索引的方面入手，对mysql进行一些优化。
第一步应该做的就是排查问题，找出瓶颈，所以，先从日志入手
开启慢查询日志
mysql>show variables like “%slow%”; 查看慢查询配置，没有则在my.cnf中添加，如下

log-slow-queries = /data/mysqldata/slowquery.log
    #日志目录


long_query_time = 1
                          #记录下查询时间查过1秒


log-queries-not-using-indexes     #表示记录下没有使用索引的查询

分析日志 – mysqldumpslow
分析日志，可用mysql提供的mysqldumpslow，使用很简单，参数可–help查看

# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序；


#                             ac , at , al , ar 表示相应的倒叙；


# -t：返回前面多少条的数据；


# -g：包含什么，大小写不敏感的；


mysqldumpslow -
s
 r -
t 10
  /
slowquery.
log
     #slow记录最多的10个语句


mysqldumpslow -
s
 t -
t 10
 -
g "left join"
  /
slowquery.
log
     #按照时间排序前10中含有"left join"的

推荐用分析日志工具 – mysqlsla

wget
 http://
hackmysql.com/
scripts/
mysqlsla-2.03.tar.gz

tar
 zvxf mysqlsla-2.03.tar.gz

cd
 mysqlsla-2.03


perl
 Makefile.PL

make


make
 install


mysqlsla /
data/
mysqldata/
slow.log

# mysqlsla会自动判断日志类型，为了方便可以建立一个配置文件“~/.mysqlsla”


# 在文件里写上：top=100，这样会打印出前100条结果。

【说明】

queries total: 总查询次数 unique:去重后的sql数量

sorted by : 输出报表的内容排序

最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

Count, sql的执行次数及占总的slow log数量的百分比.

Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.

95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time, 等待锁的时间.

95% of Lock , 95%的慢sql等待锁时间.

Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.

Rows examined, 扫描的行数量.

Database, 属于哪个数据库

Users, 哪个用户,IP, 占到所有用户执行的sql百分比

Query abstract, 抽象后的sql语句

Query sample, sql语句

Profiling 分析具体的sql语句消耗
此外，MySQL5.0.37版本以上支持了Profiling – 官方手册 。 此工具可用来查询 SQL 会执行多少时间， 并看出 CPU/Memory 使用量，执行过程中 System lock和Table lock 花多少时间等等。从启动profile之后的所有查询包括错误的语句都会记录。关闭会话或者set profiling=0 就关闭。这对帮助排查和分析sql语句，还是比较有用的。

mysql>
 set
 profiling=
1
;
  #开启


mysql>
 select
 *
 from
 li limit
 2
;


+--------+----------+


|
 id      |
 name  |


+--------+----------+


|
 1458
  |
 hello  |


|
 1457
  |
 world |


+--------+----------+


 

mysql>
 show
 profiles;


+--------------+----------------+------------------------------+


|
 Query_ID |
 Duration     |
 Query                            |


+--------------+----------------+------------------------------+


|
        1
        |
 0.00013200
   |
 SELECT
 DATABASE
(
)
     |


|
        2
        |
 0.00044100
   |
 select
 *
 from
 li limit
 2
 |


+--------------+----------------+------------------------------+


 

 

mysql>
 show
 profile for query 2
;


+---------------------------+--------------+


|
 Status
                         |
 Duration |


+---------------------------+--------------+


|
 starting                     |
  0.000058
  |


|
 Opening tables
         |
  0.000011
  |


|
 System lock
               |
  0.000007
  |


|
 Table
 lock
                   |
  0.000014
  |


|
 init                             |
  0.000027
  |


|
 optimizing                |
  0.000003
  |


|
 statistics                   |
  0.000010
  |


|
 preparing                  |
  0.000010
  |


|
 executing                  |
  0.000007
  |


|
 Sending data
            |
  0.000068
  |


|
 end
                            |
  0.000003
  |


|
 query end
                 |
  0.000002
  |
 

|
 freeing items           |
  0.000208
  |


|
 logging slow query |
  0.000002
  |


|
 logging slow query |
  0.000009
  |


|
 cleaning up              |
  0.000002
  |


+---------------------------+--------------+


#此外还有2个语句可以查看更多信息。


show
 profile cpu for query 2
;


show
 profile IPC for query 2
;

总结
慢查询分析是个长期工作，即便系统长时间没有引入新的SQL查询，也可能由于不断更新数据使得数据分布特征发生变化，从而导致索引失效，进而产生慢查询。 另外可以在mysql环境下用命令：show status , show processlist查看mysql运行的状态和正在执行的操作。当然为了智能化，也可以写一些脚本来监测。比如，下面的kill掉sleep的查询， 避免堵死队列

<?php


define
(
'MAX_SLEEP_TIME'
,
 120
)
;


$connect
 =
 mysql_connect
(
'localhost'
,
 'root'
,
 'root'
)
;


$result
 =
 mysql_query
(
"SHOW PROCESSLIST"
,
 $connect
)
;


while
 (
$proc
 =
 mysql_fetch_assoc
(
$result
)
)
{


	if
 (
$proc
[
"Command"
]
 ==
 "Sleep"
 &&
 $proc
[
"Time"
]
 >
 MAX_SLEEP_TIME)
{


		@
mysql_query
(
"KILL "
 .
 $proc
[
"Id"
]
,
 $connect
)
;


	}


}


mysql_close
(
$connect
)
;


?>