MySQL show profile diagnostic SQL and configuration optimization

Show profile brief introduction

          Show Profile yes MySQL Provided can be used to analyze the current session ,SQL Statement resource consumption Tools for situation , Can be used for SQL The measurement of tuning . In the current session . By default, it is show profile It's off , Open and save the latest 15 The result of the run For slow SQL, adopt profile Tools for detailed analysis . You can get SQL In the process of execution All resource costs . Such as IO expenses ,CPU expenses , Memory overhead, etc

Usually at work , We will pass some Mysql Provide some syntax to use Show profile ;

# Check whether the database supports show Profile, If you inquire profiling The result is off, You can start it by yourself with the following statement .
show variables like '%profiling%';

################### Turn on profile################
set profiling = on;
################## close profile################
set profiling = off;


######### see show profiles  Execute record after opening 
show PROFILES;

#  Return the following properties 
# Query_ID  After opening profiling Number of records queried 
# Duration  On behalf of this query SQL The duration 


#  The diagnosis SQL（ Will show a sql The specific lifecycle of the query ）
show profile cpu ,block io for query 1;
#  Return the following properties 
# ALL : Show all expenses 
# BLOCK IO  Display block IO expenses 
# CONTEXT SWITCHES  : The overhead of context switching 
# CPU ： Show CPU Related expenses 
# IPC： Show the cost of sending and receiving 
# MEMORY: Show memory related overhead 
# PAGE FAULTS  The cost of displaying page errors 
# SOURCE: Show source_function ,source_file Aspect cost 
# SWAPS: Show the cost of switching times 

# converting HEAP to MyISM ： Query result is too large , There is not enough memory to write to the disk 
# creating temp table : Create a temporary table , Copy data into temporary tables , Delete after use （ It's time consuming ）.
# copying to temp table on disk : Copy the temporary table in memory to disk （ It's very dangerous , Must be optimized ）
# locked : Deadlock 
#  In addition to using show profile  You can also query through the global log （ Never use it in the production environment ！！！！！！, It can only be used in the test environment ）（ Not recommended , Suggested pass show profile）
#  Check the global log on status  show variables like '%general_log%'