mysql profile及其对应表使用

--mysql的profile可用于查看一个sql的具体消耗show profile all for query 1\G;--profiling has a default value of 0 (OFF)mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+|           0 |+-------------+mysql> SET profiling = 1;--profile 的查询id可能通过如下sql找到mysql> show profiles;      +----------+-------------+-------------------------+| Query_ID | Duration    | Query                   |+----------+-------------+-------------------------+|        1 |  0.03872950 | select count(*) from t1 ||        2 | 10.93732000 | select count(*) from t2 |-- 变量 profiling_history_size 记录了保存profile记录的条数mysql> select @@profiling_history_size;+--------------------------+| @@profiling_history_size |+--------------------------+|                       15 |+--------------------------+MariaDB [test]> show profile all for query 1\G;*************************** 1. row ***************************             Status: starting           Duration: 0.000080           CPU_user: 0.000000         CPU_system: 0.000000  Context_voluntary: 0Context_involuntary: 0       Block_ops_in: 0      Block_ops_out: 0      Messages_sent: 0  Messages_received: 0  Page_faults_major: 0  Page_faults_minor: 0              Swaps: 0    Source_function: NULL        Source_file: NULL        Source_line: NULL*************************** 2. row ***************************             Status: checking permissions           Duration: 0.000008           CPU_user: 0.000000         CPU_system: 0.000000  Context_voluntary: 0Context_involuntary: 0       Block_ops_in: 0      Block_ops_out: 0      Messages_sent: 0  Messages_received: 0  Page_faults_major: 0  Page_faults_minor: 0              Swaps: 0    Source_function: check_access        Source_file: sql_parse.cc        Source_line: 6051--在mysql5.7之后，profile信息将逐渐被废弃，mysql推荐使用performance schema--setup_actors用于记录哪些信息会被记录As of MySQL 5.7.8, the setup_actors table can be used to limit the collection of historical events by host, user, or account to reduce runtime overhead and the amount of data collected in history tablesmysql> SELECT * FROM performance_schema.setup_actors; +------+------+------+---------+---------+| HOST | USER | ROLE | ENABLED | HISTORY |+------+------+------+---------+---------+| %    | %    | %    | YES     | YES     |+------+------+------+---------+---------+--当然也可以修改它UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','test_user','%','YES','YES');--其中还有一些初始化的表，具体信息请参阅mysql官方文档mysql> SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'setup%' and table_schema='performance_schema';  +--------------------+-------------------+| table_schema       | table_name        |+--------------------+-------------------+| performance_schema | setup_actors      || performance_schema | setup_consumers   || performance_schema | setup_instruments || performance_schema | setup_objects     || performance_schema | setup_timers      |+--------------------+-------------------+5 rows in set (0.00 sec)--开启以下性能记录 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';--查看指定sql的event_idmysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%select count(*) from t1%';+----------+----------+-------------------------+| EVENT_ID | Duration | SQL_TEXT                |+----------+----------+-------------------------+|       78 | 0.022121 | select count(*) from t1 |+----------+----------+-------------------------+--查看sql消耗如下：mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=78; +--------------------------------+----------+| Stage                          | Duration |+--------------------------------+----------+| stage/sql/starting             | 0.000093 || stage/sql/checking permissions | 0.000007 || stage/sql/Opening tables       | 0.000024 || stage/sql/init                 | 0.000018 || stage/sql/System lock          | 0.000013 || stage/sql/optimizing           | 0.021755 || stage/sql/executing            | 0.000015 || stage/sql/end                  | 0.000004 || stage/sql/query end            | 0.000016 || stage/sql/closing tables       | 0.000015 || stage/sql/freeing items        | 0.000153 || stage/sql/cleaning up          | 0.000001 |+--------------------------------+----------+