show profile

作用

可以用来分析当前会话中的sql语句执行消耗的资源情况。默认情况下是关闭的。

开启

show variables like 'profiling';查看是否开启 set profiling=1;开启

使用

show profiles;可以查看最近使用的sql语句执行情况：

+----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00169300 | show variables like 'profiling' | | 2 | 0.00014275 | select version() | | 3 | 0.00037900 | show databases | | 4 | 0.00015600 | SELECT DATABASE() | | 5 | 0.00029025 | show databases | | 6 | 0.00014450 | show tables | | 7 | 0.00032650 | show tables | | 8 | 0.00026725 | select * from boys | +----------+------------+---------------------------------+ 8 rows in set, 1 warning (0.00 sec)

show profile cpu,block io for query 8;查询具体的某一条sql（例如queryId为8）执行情况:

+----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000077 | 0.000076 | 0.000000 | 0 | 0 | | checking permissions | 0.000011 | 0.000010 | 0.000000 | 0 | 0 | | Opening tables | 0.000023 | 0.000023 | 0.000000 | 0 | 0 | | init | 0.000020 | 0.000020 | 0.000000 | 0 | 0 | | System lock | 0.000009 | 0.000008 | 0.000000 | 0 | 0 | | optimizing | 0.000005 | 0.000005 | 0.000000 | 0 | 0 | | statistics | 0.000014 | 0.000014 | 0.000000 | 0 | 0 | | preparing | 0.000011 | 0.000011 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000003 | 0.000000 | 0 | 0 | | Sending data | 0.000051 | 0.000052 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000004 | 0.000000 | 0 | 0 | | query end | 0.000008 | 0.000007 | 0.000000 | 0 | 0 | | closing tables | 0.000008 | 0.000008 | 0.000000 | 0 | 0 | | freeing items | 0.000014 | 0.000014 | 0.000000 | 0 | 0 | | cleaning up | 0.000011 | 0.000011 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 15 rows in set, 1 warning (0.00 sec)

需要注意以下，status如果出现以下的字段，则需要优化：

converting HEAP to MyISAMcreate tmp tablecopying to tmp table on disklocked

全局查询日志

记住不要在生产环境中使用该功能，可以选择在测试环境中使用。

开启

set global general_log=1; set global output='TABLE'; 启用之后，执行的sql语句都将会被记录在general_log表当中： select * from mysql.general_log;

+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | 2020-10-08 22:35:04.993412 | root[root] @ localhost [] | 616 | 0 | Query | select * from boys | | 2020-10-08 22:35:20.995027 | root[root] @ localhost [] | 616 | 0 | Query | select * from mysql.general_log | +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 2 rows in set (0.00 sec)