MySQL slow query

One . Slow query Introduction

MySQL The slow query log of is MySQL A type of logging provided , It is used to record in MySQL The response time in exceeds the specified threshold SQL sentence , Running time exceeds long_query_time It's worth it SQL, Will be recorded in the slow query log .

By default ,MySQL The database does not start the slow query log , Manual opening required . If it's not for tuning , It is generally not recommended to open , Because opening slow query log will bring some performance impact more or less .

stay SQL Server We use SQL Profile To record SQL The implementation of , stay Oracle We can use AWR、ASH Report to analyze history SQL The implementation of , A similar tuning approach maps to MySQL in , This corresponds to the slow query log .

Two . Parameter Introduction

slow_query_log： Open slow query log or not ,1 Open for indication ,0 Means closing .

slow_query_log_file：MySQL Slow query log storage path .

long_query_time ： Slow query threshold (s), When the query time is longer than the set threshold , Log .

log_queries_not_using_indexes： Queries that are not indexed are also recorded in the slow query log .

log_throttle_queries_not_using_indexes： Indicates that... Is allowed to be recorded every minute slow_log Not indexed sql Number of statements (0 For unlimited , If it is a fixed value , It may not be recorded sql).

log_output： How to store logs .’FILE’ Means to save to a file ,‘TABLE’ Indicates that it is stored in the system table . because FILE The mode overhead is relatively low , So the default is FILE.

log_slow_admin_statements = 1：  Record ALTER TABLE And so on 

log_slow_slave_statements = 1： Record slow queries generated from the server 

min_examined_row_limit = 100 ：SQL The number of scanning lines is greater than or equal to 100 Lines will be recorded 

Open slow query log or not

show variables like '%slow_query_log%';

Slow query threshold (s), When the query time is longer than the set threshold , Log

show variables like '%long_query_time%';

3、 ... and . Open slow query log

--  Slow query threshold (s), When the query time is longer than the set threshold , Log .
set global long_query_time = 2;   

--  Queries that are not indexed are also recorded in the slow query log 
set global log_queries_not_using_indexes = 1;    
--  Indicates that... Is allowed to be recorded every minute slow_log Not indexed sql Number of statements (0 For unlimited , If it is a fixed value , It may not be recorded sql).
set global log_throttle_queries_not_using_indexes = 0;

-- QL The number of scanning lines is greater than or equal to 100 Lines will be recorded 
set global min_examined_row_limit = 100;
--  Turn on 
 set global slow_query_log = 1;