mysql profile explain slow_query_log分析优化查询

在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                        |
+----------+------------+-----------------------------------+
|  1     |  0.00021500   | select@@profiling           |
|  2     |  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   |      2      |
|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全局变量的初始值。