Shulou(Shulou.com)06/01 Report--

Editor to share with you what mysql database slow query means, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

What is slow SQL

Under the specific business environment and technical conditions, we set a threshold, called long_query_time, to count the time spent on the execution of each dml statement. If t > = long_query_time, the dml statement is called slow statement (slow sql).

In MySQL, it provides the ability to detect and record slow query statements.

MySQL's slow log is a kind of log record provided by MySQL, which is used to record statements whose response time in MySQL exceeds the threshold. Specifically, SQL whose running time exceeds the long_query_ time value will be recorded in the slow log (logs can be written to files or database tables. If high performance requirements are required, it is recommended to write files). By default, slow log is not enabled in MySQL database, and the default value of long_query_time is 10 (that is, 10 seconds, usually set to 1 second), that is, statements running for more than 10 seconds are slow query statements.

# ON: enable slow log, and OFF: disable slow log

Slow_query_log

# specifies the threshold for slow query, that is, if the execution time exceeds this threshold, it is a slow query statement, and the default value is 10 seconds

Long_query_time

# write the recorded slow log to the file (note: the default name is hostname.log. If the slow log is written in the specified file, you need to specify the output log format of slow query as file. The related command is: show variables like'% log_output%'; to check the output format)

Slow_query_log_file

# this parameter is set to ON to capture all SQL statements that do not use the index (Note: if you just set log_queries_not_using_indexes to ON and slow_query_log to OFF, this setting will not take effect, that is, the setting takes effect only if the value of slow_query_log is set to ON)

Log_queries_not_using_indexes

Slow query consequence

Influence level

May affect the content

Technical

The database connection pool is full, resulting in large transactions and increased master-slave delay.

Business

Slower prescription, lower success rate, and avalanches of other related businesses

User

Loss of users with declining experience and low loyalty

System

The database was killed or even avalanche by hung, dragging down the service.

Avoid slow query

Sql direction: optimize the writing of sql statements and strictly avoid slow queries (not as far as possible)

Consider adding indexes in where conditional fields with good selectivity

Avoid unnecessary sorting and, if necessary, consider reducing sorting costs by adding indexes to the sort field.

If there is a composite index, the first field in the index must be used as a condition to ensure that the system uses the index, otherwise the index will not be used.

Do not perform functions, arithmetic, or other expression operations on where conditional fields, or you may not be able to use the index correctly.

The index cannot be used by the first fuzzy match of the like condition, such as select id from t where name like'% abc%'.

Logically reduce the amount of data scanned and consider whether all the data returned by sql is necessary. For example, to determine whether there is a record that meets the criteria, use limit 1 instead of count.

Monitoring direction: monitor slow queries, find them in time, and prevent them in advance

Slow query number alarm

Slow query time, slow sql record

Query, analyze and summarize the slow sql records regularly.

The above is all the contents of this article "what is the meaning of slow query in mysql database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.