mysql profile explain slow_query_log分析优化查询

转载 2012年03月30日 15:25:40
在mysql查询性能分析中最常用的就是explain了,profile查看一些具体的性能也是不错的。同时也可以通过slow_query_log记录的慢日志分析查询性能。
1. profile

我们可以先使用
SELECT @@profiling;
来查看是否已经启用profile,如果profilng值为0,可以通过
SET profiling = 1;
来启用。启用profiling之后,我们执行一条查询语句,比如:
select count(*) from abc_post;
show profiles;

+----------+------------+-----------------------------------+
| Query_ID |Duration 
     |Query                        |
+----------+------------+-----------------------------------+
      0.00021500   | select@@profiling           |
      0.05522700   | select count(*)from abc_posts|
+----------+------------+-----------------------------------+
2 rows in set (0.00 sec)
注意:Query_ID表示刚执行的查询语句

show profile for query 2;
+--------------------------------+----------+
|Status 
                       | Duration |
+--------------------------------+----------+
|starting 
                     | 0.000021 |
| checking query cache for query | 0.000045 |
| checkingpermissions 
         | 0.000007 |
| Openingtables 
               | 0.000011 |
| Systemlock 
                  | 0.000004 |
| Tablelock 
                   | 0.000040 |
|init 
                         | 0.000012 |
|optimizing 
                   | 0.000005 |
|statistics 
                   | 0.000010 |
|preparing 
                    | 0.000010 |
|executing 
                    | 0.000005 |
| Sendingdata 
                 | 0.055021 |
|end 
                          | 0.000007 |
|end 
                          | 0.000004 |
| queryend 
                    | 0.000003 |
| storing result in query cache 
| 0.000004|
| freeingitems 
                | 0.000008 |
| closingtables 
               | 0.000005 |
| logging slowquery 
           | 0.000002 |
| cleaningup 
                  | 0.000003 |
+--------------------------------+----------+
20 rows in set (0.00 sec)

结论:可以看出此条查询语句的执行过程及执行时间,总的时间约为0.05s。这时候我们再执行一次。
select count(*) from abc_posts;
show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration 
  |Query                           |
+----------+------------+----------------------------------+
      1 | 0.00021500 | select@@profiling              |
      2 | 0.05522700 | select count(*) fromabc_posts   |
      3 | 0.00006000 | select count(*) fromabc_posts   |
+----------+------------+----------------------------------+

然后执行show profile for query 3来查看本条语句的执行过程
+--------------------------------+----------+
|Status 
                       | Duration |
+--------------------------------+----------+
|starting 
                     | 0.000016 |
| checking query cache for query | 0.000007 |
| checking privileges on cached 
| 0.000004|
| checkingpermissions 
         | 0.000005 |
| sending cached result to clien | 0.000022 |
| logging slowquery 
           | 0.000003 |
| cleaningup 
                  | 0.000003 |
+--------------------------------+----------+

结论:可以看出此次第二次查询因为前一次的查询生成了cache,所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取,结果查询时间比第一次快多了。

2、explain

explain是用来获取select相关信息,分析sql语句,帮助优化的一个命令。
explain的语法:
explain [extended] select select_options

explain select * from abc_posts;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type |table 
   | type | possible_keys | key  | key_len |ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 |SIMPLE     | dcf_posts | ALL  |NULL         | NULL | NULL   | NULL | 443      |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
当使用EXTENDED关键字时,EXPLAIN产生附加信息,可以用SHOWWARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名,重写并且执行优化规则后SELECT语句是什么样子,并且还可能包括优化过程的其它注解。
如果在SELECT语句前放上关键词EXPLAIN,MySQL将解释它如何处理SELECT,提供有关表如何联接和联接的次序。
借助于EXPLAIN,可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序,
语句应以STRAIGHT_JOIN而不只是SELECT开头。

当使用EXTENDED关键字时,EXPLAIN产生附加信息,可以用SHOWWARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名,
重写并且执行优化规则后SELECT语句是什么样子,并且还可能包括优化过程的其它注解。

3、slow_query_log

优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长),
我们才能进一步分析原因并且优化它。MySQL为我们提供了Slow QueryLog记录功能,它能记录执行时间超过了特定时长的查询。
分析Slow Query Log有助于帮我们找到”有问题”查询。

记录slow queries,首先需要查看mysql server版本号,以及是否配置启用了slow querylog。查看mysql server版本号,
主要是一些功能以及配置依赖于mysql server 版本号

mysql> select version();
+----------------------+
|version() 
          |
+----------------------+
| 5.1.33-community-log |
+----------------------+

show variables like 'long%';  

+-----------------+-----------+
| Variable_name 
  |Value    |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

注:这个long_query_time是用来定义慢于多少秒的才算“慢查询”

set long_query_time=1;  

注: 我设置了1, 也就是执行时间超过1秒的都算慢查询。

show variables like 'slow%';
+---------------------+---------------+
|Variable_name 
        Value    |
+---------------------+---------------+
|slow_launch_time 
            |
|slow_query_log 
        ON      |  
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+

注:slow_query_log判断是否打开日志记录,slow_query_log_file判断设置文件所在路径

set global slow_query_log='ON'  

注:打开日志记录,一旦slow_query_log变量被设置为ON,MySQL会立即开始记录。
/etc/my.cnf 里面可以设置上面MySQL全局变量的初始值。

相关文章推荐

用mysqldumpslow分析mysql的slow query log

mysql有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改,需增加如...

MySQL开启慢查询日志slow_query_log

开启慢查询功能  slow_query_log_file 慢查询日志文件路径  slow_query_log 用于指定是否打开慢查询日志  long_query_time 超过多少秒的查询就写入日志...

MySQL 日志之--慢查询日志(slow-query-log)

MySQL 日志之--慢查询日志(slow-query-log) 慢查询日志: MySQL慢查询日志记录下所有执行超过long_query_time时间的SQL语句,帮你找到执行慢的SQL,方便...
  • lqx0405
  • lqx0405
  • 2015年12月08日 15:51
  • 3468

mysql慢日志 :slow query log 分析数据

1 慢查询日志 配置 2  查看慢查询日志工具:mysqldumpslow mysqlsla 3. 五种MySQL日志分析工具比拼           ...

mysql - 产品数量多的时候, 如何通过mysql的slow query log来处置

mysql服务器在运行的时候会维护几种日志文件,或者说生成吧.Slow Query Log只是其中之一.它主要是记录了执行时间超过一定时间(这个执行时间长短限额是mysql服务器的一个配置选项 - ...

五款常用mysql slow log分析工具的比较

mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具. 启用 slow log 有两种启用方式: 1, 在my.cnf 里 ...
  • klarclm
  • klarclm
  • 2012年05月16日 13:51
  • 307

mysql slow log分析工具的比较

原文地址: http://developer.51cto.com/art/201309/410323_all.htmmysql 中的 slow log 是用来记录执行时间较长(超过 long_quer...

五款常用mysql slow log分析工具的比较分析

mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具 启用 slow log 有两种启用方式: 1, 在my.cnf 里...

五款常用mysql slow log分析工具的比较分析

本文为大家分享了五款常用mysql slow log分析工具的比较分析,感兴趣的同学参考下。 mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql...

MySQL调优三步曲(慢查询、explain profile)

mysql profile explain slow_query_log分析优化查询 在做性能测试中经常会遇到一些sql的问题,其实做性能测试这几年遇到问题最多还是数据库这块,要么就是IO高要么...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql profile explain slow_query_log分析优化查询
举报原因:
原因补充:

(最多只允许输入30个字)