MySQL 的性能-SQL 执行分析—执行时间分析详解程序员

    Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

     Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain,以及slow query log都无法做到精确分析，

     但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。

1.查看数据库版本方法：show variables like “%version%”;  或者  select version();

    

2.确定支持show profile 后，查看profile是否开启，数据库默认是不开启的。变量profiling是用户变量，每次都得重新启用。

   查看方法： select @@profiling   # 0 代表还是关闭着分析功能，  1 代表开启

　

   

 　或者使用查看命令： show variables like “%pro%” ; 

    开启profiling ： set profiling = 1;

   

   再次查看show variables like “%pro%”; 已经是开启的状态了。

3.可以开始执行一些想要分析的sql语句了，执行完后，show profiles；即可查看所有sql的总的执行时间。

  

 语法：show profile for query Query_ID

 例：

 show profile for query 1

 

以上具体的信息都是从 INFORMATION_SCHEMA.PROFILING 这张表中取得的。这张表记录了所有的各个步骤的执行时间及相关信息。

语法：  mysql> select * from INFORMATION_SCHEMA.PROFILING where query_id = Query_ID;

 

mysql> show profile cpu, block io, memory,swaps,context switches,source for query 6;   #可以查看出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等

 

mysql> show profile all for query 6 查看第6条语句的所有的执行信息。

 测试完毕后，关闭参数：

mysql> set profiling=0

方法二： timestampdiff来查看执行时间。

这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准

set @d=now(); 
select * from comment; 
select timestampdiff(second,@d,now()); 

如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。

 

3.慢查询日志
MySQL 的慢查询日志，顾名思义就是把执行时间超过设定值（默认为10s）的 SQL 记录到日志中。这项功能需要手动开启，但是开启后会造成一定的性能损耗。

 

3.1 查看慢日志是否开启
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启。语法：set global slow_query_log=1

mysql> show variables like ‘%slow_query_log%’;
+———————+——————————————————+
| Variable_name | Value |
+———————+——————————————————+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/var/mysql/xueweihandeMacBook-Air-slow.log |
+———————+——————————————————+
2 rows in set (0.11 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like ‘%slow_query_log%’;
+———————+——————————————————+
| Variable_name | Value |
+———————+——————————————————+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/var/mysql/xueweihandeMacBook-Air-slow.log |
+———————+——————————————————+

3.2 设置超时时间
设置语法：set global long_query_time=4
查看语法：show variables like ‘long_query_time’
注意：修改后，需要重新连接或新开一个会话才能看到修改值。

永久生效，修改 my.cnf

slow_query_log=1
long_query_time=10
slow_query_log_file=/path/mysql_slow.log

3.3 其他参数

3.3.1 log_output
参数是指定日志的存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

3.3.2 log-queries-not-using-indexes
未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

3.3.3 log_slow_admin_statements
表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

3.4 分析工具 mysqldumpslow
MySQL 提供了慢日志分析工具 mysqldumpslow。

-s 表示按照何种方式排序；
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t 是top n的意思，即为返回前面多少条的数据；
-g 后边可以写一个正则匹配模式，大小写不敏感的；

3.4.1 命令示例
得到返回记录集最多的 10 个 SQL：mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的 10 个 SQL：mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现刷屏的情况：mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more