1. Usage of slow query

It keeps track of all SQL statements that have been executed longer than long_query_time, helping us find slow SQL statements that can be optimized.

2. Check whether slow query is enabled

show variables like ‘slow_query%’;

Slow_query_log = off: slow query is not enabled

Slow_query_log_file specifies the directory where slow query logs are stored

3. Open slow query (only open when needed, because it consumes performance, it is recommended to use real-time)

Mysql > update mysql > update mysql > update mysql > update mysql

set global slow_query_log=1; 或者 set global slow_query_log=ON;

Localhost-slow. log exists in /var/lib/mysql. If localhost-slow.log is disabled by default.

Method 2 :(permanent)

CFG file to [mysqld] :

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
Copy the code

4. Set the slow query time

Slow query time: show variables like ‘long_query%’, default is 10 seconds, meaning slow query is longer than 10 seconds.

We now set the slow query log time to 1 second: set long_query_time=1;

Select count(1) from order o where o.user_id in (select u.id where users);

Since we have enabled slow query and set it to slow if it takes more than 1 second, this SQL executes for 24 seconds, so it is a slow query.

We look in the log:

More/var/lib/mysql/localhost – missile. The log,

We can see the time of the query, the users, the time spent, the database used, the SQL statements executed, and so on. In production we can use this approach to look at slow-executing SQL.

6. Query slow_queries: show status like ‘slow_queries’;

After we re-execute the query SQL we just did, the number of slow queries will be 8

More /var/lib/mysql.localhost-slow.

In production, we analyze SQL with high query frequency and slow query. Not every SQL with slow query needs to be analyzed.

7. Slowly query the log analysis tool Mysqldumpslow

Since there are many slow queries in production, it can be cumbersome to use this method to view slow-query SQL. Fortunately, MySQL provides slow query log analysis tool Mysqldumpslow.

It counts the number of occurrences of different slow SQL queries (Count), the maximum execution Time (Time), the total elapsed Time (Time), the Time spent waiting for Lock (Lock), the total number of Rows sent to the client (Rows), the total number of Rows scanned (Rows).

(1) Query the help information for Mysqldumpslow. Go to any folder and run Mysqldumpslow –help

Check the directory where the mysqlDumpslow command is installed: whereis mysqlDumpslow

Description:

  • -s is the order of c, t, l, r, at, al, ar, where a is added to represent the average
  • -t, which stands for top N, is how many previous columns of data are returned
  • -g, which can be followed by a regular matching pattern, case insensitive
  • – r: reverse order

(2) Case: Fetch the two longest SQL

Format: mysqlDumpslow -s t -t 2 Slow log file

mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

Parameter analysis:

  • Number of occurrences (Count),
  • Maximum execution Time,
  • Accumulated total Time (Time),
  • The time to wait for a Lock,
  • Total number of Rows sent to the client,
  • Total number of Rows scanned,
  • The user and the SQL statement itself (for example, limit 1, 20 are limit N,N).

(3) Case: Retrieve the SQL with the most query times and the in keyword

mysqldumpslow -s c -t 1 -g ‘in’ /var/lib/mysql/localhost-slow.log

This way is more convenient and faster!

8.show profile

Usage: Analyzes the resource consumption of statement execution in the current session

(1) Check whether profile is enabled. Mysql is disabled by default because enabling profile consumes high performance

show variables like ‘profiling%’;

(2) Enable profile (session level, closing the current session will restore the original closed state)

set profiling=1; Or the set profiling = ON;

(3) Disable a profile

set profiling=0; Or the set profiling = OFF;

(4) Display the currently executed statement and time

show profiles;

(5) Display the execution time of the current query statement and system resource consumption

show profile cpu,block io for query 4; (Analyze CPU resources and IO operations for SQL with query_id = 4 in show Profiles)

Or simply: show profile for query 4;