【MySQL】利用show profiles查看SQL执行时间
xixuefeng
MySQL, MySQL-DEV
2018-03-01 23:11:35
927 次浏览
profiling, show profiles
【MySQL】利用show profiles查看SQL执行时间已关闭评论
1:查看当前数据库版本(据说MySQL 5.0.37开始支持show profiles查看SQL执行时间)
1 2 3 4 5 6 7 8 9 |
mysql> select version(); +------------+ | version() | +------------+ | 5.7.19-log | +------------+ 1 row in set mysql> |
2:确认profile是否开启,默认未开启
1 2 3 4 5 6 7 8 9 |
mysql> show variables like "profiling"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set mysql> |
3:启用并确认
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> set profiling = 1; Query OK, 0 rows affected mysql> show variables like "profiling"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set mysql> |
4:执行要看执行时间的SQL语句
1 2 3 4 |
mysql> select distinct table_name from information_schema.tables; ......显示略...... mysql> select table_name from information_schema.tables group by table_name; ......显示略...... |
5:查看以上单条SQL的执行时间
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show profiles; +----------+------------+----------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------------------------------------+ | 1 | 0.00082625 | show variables like "profiling" | | 2 | 0.02806500 | select distinct table_name from information_schema.tables | | 3 | 0.00356600 | select table_name from information_schema.tables group by table_name | +----------+------------+----------------------------------------------------------------------+ 3 rows in set, 1 warning (0.02 sec) mysql> |
6:也可看某一条SQL的执行时间明细(以第三条举例)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
mysql> show profile for query 3; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000029 | | checking permissions | 0.000004 | | Opening tables | 0.000022 | | init | 0.000006 | | System lock | 0.000003 | | optimizing | 0.000002 | | statistics | 0.000006 | | preparing | 0.000006 | | Creating tmp table | 0.000008 | | Sorting result | 0.000003 | | executing | 0.000074 | | checking permissions | 0.000080 | | checking permissions | 0.000165 | | checking permissions | 0.000032 | | checking permissions | 0.000022 | | checking permissions | 0.000090 | | checking permissions | 0.000061 | | checking permissions | 0.000080 | | checking permissions | 0.000015 | | checking permissions | 0.000039 | | checking permissions | 0.000314 | | checking permissions | 0.000196 | | checking permissions | 0.000251 | | checking permissions | 0.000027 | | checking permissions | 0.000231 | | checking permissions | 0.000184 | | checking permissions | 0.000010 | | checking permissions | 0.000015 | | checking permissions | 0.000021 | | checking permissions | 0.000211 | | checking permissions | 0.000011 | | checking permissions | 0.000014 | | Sending data | 0.001099 | | Creating sort index | 0.000189 | | end | 0.000003 | | query end | 0.000003 | | removing tmp table | 0.000003 | | query end | 0.000002 | | closing tables | 0.000002 | | removing tmp table | 0.000007 | | closing tables | 0.000002 | | freeing items | 0.000012 | | cleaning up | 0.000018 | +----------------------+----------+ 43 rows in set, 1 warning (0.00 sec) mysql> |