mysql profile explain slow_query_log分析优化查询

friendlessstar

在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<wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|Query</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|<br>+----------+------------+-----------------------------------+<br>|</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">1</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">0.00021500</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">| select@@profiling</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|<br>|</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">2</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">0.05522700</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">| select count(*)from abc_posts|<br>+----------+------------+-----------------------------------+<br>2 rows in set (0.00 sec)<br>注意：Query_ID表示刚执行的查询语句<br><br><span style="color:#FF0000">show profile for query 2;</span><br>+--------------------------------+----------+<br>|Status</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|Duration |<br>+--------------------------------+----------+<br>|starting</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000021 |<br>| checking query cache for query | 0.000045 |<br>| checkingpermissions</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000007 |<br>| Openingtables</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000011 |<br>| Systemlock</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000004 |<br>| Tablelock</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000040 |<br>|init</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000012 |<br>|optimizing</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000005 |<br>|statistics</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px">|0.000010 |<br>|preparing</span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr><span style="font-size:18px"></span><wbr style="font-size:18px"><span></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>