information_schema.profiling可以用来分析每一条SQL在它执行的各个阶段的用时，注意这个表是session 级的，也就是说如果session1 开启了它；session2没有开启,这个情况下session2 去查询只会返回一个空表
mysql> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.35 | +---------------+--------+ 1 row in set (0.00 sec) mysql> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec) mysql> select count(*) from i_insert_test; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------+ | 1 | 0.00483875 | select * from i_insert_test where id in (1,5) | | 2 | 0.00464025 | explain select * from i_insert_test where id in (3,7) | | 3 | 0.00016200 | SELECT @@profiling | | 4 | 0.00072150 | show variables like 'version' | | 5 | 0.00081350 | show variables like '%profil%' | | 6 | 0.00287725 | select count(*) from i_insert_test | +----------+------------+-------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead | +---------+------+--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show profile; +----------------+----------+ | Status | Duration | +----------------+----------+ | starting | 0.000045 | | query end | 0.000005 | | closing tables | 0.000002 | | freeing items | 0.000042 | | cleaning up | 0.000012 | +----------------+----------+ 5 rows in set, 1 warning (0.00 sec) mysql> show profile for query 6; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000061 | | checking permissions | 0.000012 | | Opening tables | 0.000023 | | init | 0.002015 | | System lock | 0.000018 | | optimizing | 0.000525 | | statistics | 0.000027 | | preparing | 0.000012 | | executing | 0.000003 | | Sending data | 0.000059 | | end | 0.000007 | | query end | 0.000007 | | closing tables | 0.000011 | | freeing items | 0.000083 | | cleaning up | 0.000015 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec) SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS "R/Call" FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC; mysql> set @query_id=6; Query OK, 0 rows affected (0.00 sec) mysql> SELECT STATE, SUM(DURATION) AS Total_R, -> ROUND( -> 100 * SUM(DURATION) / -> (SELECT SUM(DURATION) -> FROM INFORMATION_SCHEMA.PROFILING -> WHERE QUERY_ID = @query_id -> ), 2) AS Pct_R, -> COUNT(*) AS Calls, -> SUM(DURATION) / COUNT(*) AS "R/Call" -> FROM INFORMATION_SCHEMA.PROFILING -> WHERE QUERY_ID = @query_id -> GROUP BY STATE -> ORDER BY Total_R DESC; +----------------------+----------+-------+-------+--------------+ | STATE | Total_R | Pct_R | Calls | R/Call | +----------------------+----------+-------+-------+--------------+ | init | 0.002015 | 70.01 | 1 | 0.0020150000 | | optimizing | 0.000525 | 18.24 | 1 | 0.0005250000 | | freeing items | 0.000083 | 2.88 | 1 | 0.0000830000 | | starting | 0.000061 | 2.12 | 1 | 0.0000610000 | | Sending data | 0.000059 | 2.05 | 1 | 0.0000590000 | | statistics | 0.000027 | 0.94 | 1 | 0.0000270000 | | Opening tables | 0.000023 | 0.80 | 1 | 0.0000230000 | | System lock | 0.000018 | 0.63 | 1 | 0.0000180000 | | cleaning up | 0.000015 | 0.52 | 1 | 0.0000150000 | | preparing | 0.000012 | 0.42 | 1 | 0.0000120000 | | checking permissions | 0.000012 | 0.42 | 1 | 0.0000120000 | | closing tables | 0.000011 | 0.38 | 1 | 0.0000110000 | | end | 0.000007 | 0.24 | 1 | 0.0000070000 | | query end | 0.000007 | 0.24 | 1 | 0.0000070000 | | executing | 0.000003 | 0.10 | 1 | 0.0000030000 | +----------------------+----------+-------+-------+--------------+ 15 rows in set (0.00 sec) mysql> set profiling=off; Query OK, 0 rows affected, 1 warning (0.00 sec)