Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL slow log query instance analysis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. the concept of slow query log

For SQL and index optimization problems, we will use explain to analyze the SQL statement. But real enterprise projects have thousands of SQL, and it is impossible for us to analyze them one by one from scratch. Where can we get those long-running and performance-consuming SQL???

We can open the slow log:

Estimate a time limit (20ms, 100ms) according to the specific business and concurrency. Open the business after setting it, and open the slow query log after the stress test. You will see the SQL that exceeds the execution time, and then use explain to analyze these time-consuming SQL statements.

The steps are as follows:

Turn on the slow log switch slow_query_log

Set a reasonable limit of slow query time that is acceptable to the business

Pressure testing performs all kinds of business.

Check the slow query log to find all the time-consuming SQL statements

Use explain to analyze these time-consuming SQL statements for targeted optimization

MySQL can set the slow query log, when the SQL execution time exceeds our set time, then these SQL will be recorded in the slow query log, and then we will look at the log and use explain to analyze the execution plan of these SQL to determine why it is inefficient and that the index is not used. Or is there something wrong with the index itself? Or the index is used, but because the amount of data in the table is too large, it takes a long time, so we can divide the table into multiple small tables and so on.

The parameters related to the slow log are as follows:

(many global switches defined by MySQL are stored in global variables, and you can use show/set variables to view or set the values of global variables.)

The slow log switch is turned off by default

Path to slow log: defaults to / var/lib/mysql/

The slow query log records all SQL statements whose execution time exceeds the value set by the parameter long_query_time (in seconds), which can be viewed with the command on MySQL, as follows:

This value can be modified:

Second, slow query log practice 1. Turn on the slow log switch slow_query_log

When the slow log switch is enabled, the error indicates that slow_query_log is a variable of global (there are also variables that only affect the current session, such as long_query_time, profiling). The modification will affect all session, that is, all clients accessing the current MySQL server.

The slow log switch was turned on successfully!

two。 Set a reasonable upper limit of slow query time acceptable to the business long_query_time

View another session

It is found that the default is 10s, so long_query_time only affects the current session

3. Pressure testing performs all kinds of business.

It has exceeded the long_query_time=0.1s set by us.

4. View slow query log

Path: / var/lib/mysql/

5. Use explain to analyze these time-consuming SQL statements for targeted optimization

I searched the whole table and scanned the whole primary key index tree.

We should add an index to password, and then remember that password is in string format, because indexes are not available if type conversion is involved

3. Show profiles to check the specific running time of sql

MySQL usually only shows the time of two decimal places.

Turn on the profiling switch to show a more detailed time

No error is reported, indicating that the profiling variable only affects the current session

This is the end of "MySQL slow log query instance analysis". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.