Mysql优化_内置profiling性能分析工具
如果要进行SQL的调优优化和排查，第一步是先让故障重现，但是这个并不是这一分钟有问题，下一秒就OK。
一般的企业一般是DBA数据库工程师从监控里找到问题。DBA会告诉我们让我们来排查问题，那么可能很多种原因，也许是程序内存泄漏、也许是网络、也许是死锁、也许是SQL写的烂。假设是SQL问题我们需要把SQL抓出，一般就是在测试环境跑一下SQL，重现故障。首先打开我们的慢查询日志，这样把有问题的SQL排查出来，然后用EXPLAIN分析一下，一般到了这一步应该会找到相应问题所在。但是如果还是没有解决，SQL在传输网络、链接、是否是死锁等各方面都再有一个更加详细的排查，那么show profile就可以帮忙解决了。
差不多到了这，基本上95%的问题解决了，那么最后如果通过前面问题的分析，解决的问题还是一般般，那么就需要配合DBA看看mysql配置文件各方面的参数节点配置进行调优了。
show profile是什么？是Mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。
默认情况下参数处于关闭状态，开启后保存最近15次的运行结果。
假设一条SQL执行完成之后，10秒钟，这么慢，那么能不能有一张清单告诉你你到底是多少个步骤，比如第一步花了1.34秒，第二步花了2.44秒...累积达到10秒，show profile就有这样的功能。
SQL导致服务器慢要么就是CPU运算复杂要么就是频繁IO。
MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。
MySQL [test_db]> show variables like '%profiling%'; //查看是否开启
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
MySQL [test_db]> set profiling=on; //开启show profiling
Query OK, 0 rows affected, 1 warning (0.00 sec)
reset cache query; //清空SQL缓存
select * from table where... order by desc limit 0,100; //执行SQL
show profiles; //获取系统中保存的所有query的profile概要信息
MySQL [test_db]> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 5 | 0.00099825 | select * from emp order by id desc limit 0,60 |
| 6 | 0.00004150 | select * from emp order by id desc limit 0,60 |
| 7 | 0.00007300 | select * from emp order by id desc limit 0,60 |
| 8 | 0.00010325 | select * from emp order by id desc limit 0,60 |
| 9 | 0.00011000 | select * from emp order by id desc limit 0,60 |
| 10 | 0.00007025 | select * from emp order by id desc limit 0,60 |
| 11 | 0.00005275 | select * from emp order by id desc limit 0,60 |
| 12 | 0.00004050 | select * from emp order by id desc limit 0,60 |
| 13 | 0.00009000 | select * from emp order by id desc limit 0,60 |
| 14 | 0.10676625 | select * from emp order by id desc limit 0,50 |
| 15 | 7.02310450 | select * from emp order by id desc limit 150000 |
| 16 | 3.90775625 | select * from emp order by id desc limit 150000 |
| 17 | 2.96361950 | select * from emp order by id desc limit 150000 |
| 18 | 0.19119000 | set profiling=1 |
| 19 | 0.10549050 | select count(*) from emp |
+----------+------------+-------------------------------------------------+
三列分别是查询ID，执行时间，执行SQL
在获取到概要信息之后，就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中,以19为例
MySQL [test_db]> show profile cpu,block io for query 19;
MySQL [test_db]> MySQL [test_db]> show profile cpu,block io for query 19;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000037 | 0.000000 | 0.000000 | 0 | 0 | //开始连接
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | //找缓存
| starting | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.104519 | 0.000000 | 0.001000 | 232 | 0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | //验证权限
| Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000787 | 0.000000 | 0.000000 | 168 | 0 |
| System lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
上面结果显示了完整的生命周期过程。
如果出现以下四种情况之一，那么就有问题！
converting HEAP to MyISAM查询结果太大，内存不够用了往磁盘上搬了。
Creating tmp table 创建了临时表，拷贝数据到临时表，用完再删除
Copying to tmp table on disk ，把内存中临时表复制到磁盘，很危险！
locked，锁了。
