*Note*: These statements are deprecated as of MySQL 5.6.7 and will be
removed in a future MySQL release. Use the Performance Schema instead;
see
--上面描述从5.6.7开始该命令将会被移除，用Performance Schema instead代替
--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息
2、开启porfiling
--启用session级别的profiling
root@localhost[sakila]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
--验证修改后的结果
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
--发布SQL查询
root@localhost[sakila]> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 599 |
+----------+
--查看当前session所有已产生的profile
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00253600 | show variables like '%profil%' |
| 2 | 0.00138150 | select count(*) from customer |
+----------+------------+--------------------------------+
2 rows in set, 1 warning (0.01 sec)
--我们看到有2个warning，之前一个，现在一个
root@localhost[sakila]> show warnings; --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+--------------------------------------------------------------------------------------------------------------+
3、获取SQL语句的开销信息
--可以直接使用show profile来查看上一条SQL语句的开销信息
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling
--我们下面的这个show profile查看的是show warnings产生的相应开销
root@localhost[sakila]> show profile;
+----------------+----------+
| Status | Duration |
+----------------+----------+
| starting | 0.000141 |
| query end | 0.000058 |
| closing tables | 0.000014 |
| freeing items | 0.001802 |
| cleaning up | 0.000272 |
+----------------+----------+
--如下面的查询show warnings被添加到profiles
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00253600 | show variables like '%profil%' |
| 2 | 0.00138150 | select count(*) from customer |
| 3 | 0.00228600 | show warnings |
+----------+------------+--------------------------------+
--获取指定查询的开销
root@localhost[sakila]> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables | 0.000047 |
| init | 0.000023 |
| System lock | 0.000035 |
| optimizing | 0.000012 |
| statistics | 0.000019 |
| preparing | 0.000014 |
| executing | 0.000006 |
| Sending data | 0.000990 |
| end | 0.000010 |
| query end | 0.000011 |
| closing tables | 0.000010 |
| freeing items | 0.000016 |
| cleaning up | 0.000029 |
+----------------------+----------+