How to enable slow query in MySQL

1. The configuration is enabled.

Linux:

Add

# Enable slow Query
Slow_query_log_file = \ logs \ mysqlslowquery. Log
Slow-query-log = 1
Long_query_time = 5
Log-queries-not-using-Indexes

 

Windows:

Add the following statement in [mysqld] Of My. ini:

First, we need to know which file is the configuration file used by the MySQL service. How can we view it?

 

 

# Enable slow Query
Slow_query_log_file = c: \ logs \ mysqlslowquery. Log
Slow-query-log = 1
Long_query_time = 5
Log-queries-not-using-Indexes

 

2. Viewing Method

Linux:

Use the MySQL built-in command mysqldumpslow to view

Common commands
-S order what to sort by (T, at, l, Al, R, ar etc), 'at' is default
-T num just show the top N queries
-G pattern grep: only consider into ts that include this string

Eg:
S is the order, indicating that the writing is not detailed enough. I used it, including reading the code, mainly including C, T, L, R, AC, AT, Al, AR, they are sorted by the number of queries, time, lock time, and number of returned records. The time-reverse-T added with a is the meaning of Top N, that is, the number of data records in the previous row-G, followed by a RegEx matching pattern, case insensitive

Mysqldumpslow-s C-T 20 host-slow.log
Mysqldumpslow-s r-T 20 host-slow.log
The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.

Mysqldumpslow-T 10-s t-G "left join" host-slow.log returns the first 10 SQL statements containing the left join according to time.

 

 

This is a useful log. It has little impact on performance (assuming that all queries are fast), and emphasizes the most important queries (if indexes are lost or the indexes are not used properly)

# Time: 070927 8:08:52

# User @ host: Root [root] @ [192.168.0.20]

# Query_time: 372 lock_time: 136 rows_sent: 152 rows_examined: 263630
Select ID, name from Manager where ID in (66,10135 );
This is one of the slow query logs. It takes 372 seconds, locks 136 seconds, returns 152 rows, and queries 263630 rows in total.

If there is a lot of log Content, it will be exhausting to look at it with one eye. MySQL comes with an analysis tool, which is used as follows:
Enter the MySQL/bin directory under the command line and enter mysqldumpslow-help or -- help to see the parameters of this tool.
Usage: mysqldumpslow [opts...] [logs...]

Parse and summarize the MySQL slow query log. Options are

-- Verbose

-- Debug

-- Help write this text to standard output

-V verbose

-D debug

-S order what to sort by (T, at, l, Al, R, ar etc), 'at' is default

-R reverse the sort order (largest last instead of first)

-T num just show the top N queries

-A don't abstract all numbers to N and strings to's'

-N num abstract numbers with at least N digits within names

-G pattern grep: only consider into ts that include this string

-H hostname of DB server for *-slow. log filename (can be wildcard ),

Default is '*', I. e. Match All

-I name of server instance (if using MySQL. server startup SCR limit pt)

-L don't subtract lock time from total time

-S is the order, which indicates that the write is not detailed enough. I used it, including reading the code, mainly including
C, T, L, R, and AC, AT, Al, ar are sorted by the number of queries, time, lock time, and number of returned records, respectively, flashback with a added above
-T indicates the Top N, that is, the number of data records returned.
-G. You can write a regular expression matching later. It is case insensitive.

Mysqldumpslow-s C-T 20 host-slow.log
Mysqldumpslow-s r-T 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.
Mysqldumpslow-T 10-s t-G left join host-slow.log
Return the first 10 SQL statements containing the left join according to the time.

 

Windows:

When you enable MySQL slow query for the first time, this record file will be created in the directory you specified. This article is mysqlslowquery. log, the content of this file is roughly as follows (when MySQL slow query is enabled for the first time)
E: \ WEB \ mysql \ bin \ mysqld, version: 5.4.3-beta-Community-log (MySQL Community Server (GPL). Started:
TCP/IP Port: 3306, named pipe: (null)
Time id command argument

You can run the following command to view the number of slow query records:

show global status like '%slow%';