MySQL查找SQL耗时瓶颈 SHOW profiles

2022-10-22 09:37:57

http://blog.csdn.net/k_scott/article/details/8804384

1、首先查看是否开启profiling功能

SHOW VARIABLES LIKE '%pro%';

或者

SELECT @@profiling;

2、开启profiling

SET profiling=1;

3、执行sql语句

例如：

SELECT
table_schema AS 'Db Name',
ROUND( SUM( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
ROUND( SUM( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

4、查看结果

SHOW profiles;

SHOW profile ALL FOR QUERY 94;

94是查询ID号。

SHOW profiles语法：

SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS