mysql性能问题定位
 
 使用mysql作为基础的应用,可能会遇到一些数据库方面的性能问题,我们可以通过一些方法进行问题定位。以下介绍可以定位性能问题的四种方法,欢迎拍砖。
一、开启慢查询日志:
记录执行查询时间大于long_query_time的sql,long_query_time默认为2s;
show variables like ‘%slow%’
得到图中所示信息,这里可以查看到慢查询日志是否开启,慢查询日志文件的存放目录。
开启慢查询日志的方法:
1、vi  /etc/my.cnf(这个是的默认读取配置文件目录,一般会将my.cnf文件放在这下面)
[mysqld]下添加
slow_query_log=ON
long_query_time=1(sql语句执行时间超过该参数值,则会打印在慢查询日志中),默认执行时间超过2s的sql会打印在慢查询日志中。
修改了my.cnf中的配置项,需要重启数据库。
2、不重启数据库的情况下,执行 set global slow_query_log=ON,可以开启慢查询日志。
开启慢查询日志后,跟踪慢查询日志文件中的慢查询sql,再具体分析,通过调整sql写法,或者添加正确的索引,可以看到意想不到的性能效果。
二、分析慢查询sql:
1、Explain 打印执行计划
执行的selcet语句前面加上explain,可以告诉你mysql如何执行该条语句。
这里需要额外注意type、key、rows 、extra列展示的内容。
其中,
Type=all,表示使用的是全表扫描,在数据量大的情况下,全表扫描是非常耗性能的,这个需要特别注意;
Type=index,表示使用索引扫描,只会遍历索引树;
Type=range,表示使用索引范围扫描,常见于between 、>、<等的查询。
Type=ref,非唯一性索引扫,返回匹配某个单独值得所有行
Type=eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配
Type=const/system   读常量,最多只会有一条记录匹配,由于是常量,实际上只须要读一次
Type=null 不需要扫描表
访问类型从上到下由差变为最好。
key表示select中使用到的索引,如果为null,表示没有使用索引,从查询效率上讲,使用索引比不使用索引快。但并不是所有的都要加索引,索引也存在不足,这里就不详解。
rows表示执行该条sql所需要扫描的行数,这个没有绝对值可参考,一般来说越小越好,如果100万数据量的数据库,rows是70万,通过这个可以判断sql的查询性能很差,如果100万条数据量的数据库,rows是1万,从我个人的角度,还是能接受的。
extra
一些十分重要的额外信息,重点关注出现关键字:
Using filesort:当Query 中包含order by 操作,而且无法利用索引完成排序操作的时候,MySQL Query Optimizer 不得不选择相应的排序算法来实现。
Using temporary:在某些操作中必须使用临时表时,在 Extra 信息中就会出现Using temporary ,主要常见于 GROUP BY 和 ORDER BY 等操作中
当执行计划Extra 出现Using filesort 、Using temporary 时,可以考虑是否需要进行sql优化和调整索引,最后再调整my.cnf 中与排序或者临时表相关的参数,如sort_buffer_size或者tmp_table_size.
2、show full processlist 查看哪条sql一直占用进程
Time表示执行当前操作所耗费的时间,单位为秒(s);
State表面当前线程的状态,Info表示正在执行的操作;
这里如果发现time值比较大,state一直处于一个状态,那么从Info中我们可以获得耗时长的操作,再具体分析;
注意观察State中出现关于lock关键字的状态。
这个命令可以很直观地看到正在执行的sql,及其当前状态,操作比较方便。
3、show profile 定位sql在数据库中资源占用情况(注意,一个是show profiles,一个是show profile)
Show profiles主要展示在当前会话中,profiling_history_size条sql执行的时间、query_id,默认为15条,最大为100条,不能设为0。
SHOW VARIABLES LIKE ‘%profiling_history_size%’
 
 
SHOW VARIABLES LIKE ‘%profiling’或者select @@profiling 查看profiling是否开启
set profiling=1 开启profiling
执行一条sql
再执行show profiles,会把最近执行的sql给展示出来,
从图中找到刚刚执行的sql,query_id是120,执行时间是0.00079725s
如果我们想看sql在各个阶段所消耗时间,则使用如下
SHOW PROFILE FOR QUERY 120
各个阶段所消耗时间一目了然。
show profile具体写法为 Show profile TYPE for query n,n为query_id,TYPE可写可不写
TYPE的取值有为ALL、BLOCK IO、CONTEXT SWITCHES、CPU、IPC、MEMORY、PAGE FAULTS、SOURCE、SWAPS
如上述例子中
SHOW PROFILE CPU,MEMORY FOR QUERY 120
不加for query n这句,则展示执行show profile之前执行过一条语句。
4、Mysqladmin 查询整个数据库的状态
在mysql的bin目录下,执行
./mysqladmin -u用户名 -p密码 proc stat
这里添加proc就如同show full processlist功效
stat展示当前数据库的状态
threads 表示当前线程数,Opens 当前打开的表数目,Queries per second 每秒执行的查询数,数据库性能越好,这个值就越高