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

What this article shares with you is about what slow query sentences there are in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

View problems through SHOW FULL PROCESSLIST

SHOW FULL PROCESSLIST is equivalent to select * from information_schema.processlist can list running connection threads

Processlist

Description:

Id connects to id. You can close the connection using kill+ to connect to id (kill 9339)

User displays the current user

Host displays the connected client IP and port

Db displays the database connected by the process

Command displays the current execution status of the current connection, sleep, query, connect

Time displays the duration of the current state (seconds)

State displays the execution status of the currently connected sql statements, such as copying to tmp table, sorting result, sending data, etc.

Info displays sql statements, and if you find more time-consuming statements, you can copy them and use explain analysis.

Slow query log

Slow log is a SQL statement used by MySQL to record response time exceeding the set threshold (long_query_time). Slow log is not enabled by default and needs to be configured manually.

Here are a few common attributes to keep in mind:

Slow_query_log: whether to enable slow query (ON is enabled, OFF is disabled)

Long_query_time: slow query threshold, which means that if the execution time of the SQL statement exceeds this value, it will be recorded. The default is 10s.

Slow_query_log_file: the file path stored in the slow log

Log_queries_not_using_indexes: records do not use index query statements (ON is on, OFF is off)

Log_output: log storage method (FILE means to write logs to files, and TABLE means to write logs to the database. The default value is FILE. If you store logs in the database, you can query them through select * from mysql.slow_log. Generally, it is recommended to save files with relatively high performance requirements.)

We can query the property values we set through the show variables like'% keyword%'.

Slow

We have two ways to set our properties, one is the set global property = value (restart fails), the other is configuration file (restart takes effect)

Command method:

Set global slow_query_log=1; set global long_query_time=1; set global slow_query_log_file='mysql-slow.log'

Configuration file method:

Slow_query_log = 'ON' slow_query_log_file = D:/Tools/mysql-8.0.16/slow.log long_query_time = 1 log-queries-not-using-indexes

Pt-qurey-digest parsing slow query statement

Percona-toolkit contains many useful and powerful mysql toolkits, and pt-qurey-digest is just one of them for analyzing slow query logs. You need to download it from the official website, and the method is easy to use:

. / pt-query-digest slow2.log > > slow2.txt

An analysis result can be obtained:

# Query 9: 0.00 QPS, 0.00x concurrency ID 0xF914D8CC2938CE6CAA13F8E57DF04B2F at byte 499246 # This item is included in the report because it matches-- limit.# Scores: v 08T03:56:12 to M = 0.22 # Time range: 2019-07-08T03:56:12 to 2019-07-12T00:46:28 # Attribute pct total min max avg 95% stddev median # = = # Count 8 69 # Exec time 1 147s 1s 3s 3s 685ms 2s # Lock time 0 140ms 2ms 22ms 2ms 3ms 2ms 2ms # Rows sent 0 0 0 # Rows examine 0 23.96M 225.33k 482.77k 355.65k 462.39k 81.66k 345.04k # Query size 2 17.72k 263 263 263 0 263 # String:# Databases xxxx # Hosts xx.xxx.xxx.xxx# Users root# Query_time distribution# 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s # 10s+ # Tables# SHOW TABLE STATUS FROM `xxxx` LIKE 'xxxxx_track_exec_channel'\ G # SHOW CREATE TABLE `xxxx`.`xxxxxxxx _ exec_ channel`\ G # SHOW TABLE STATUS FROM `xxx` LIKE' xxxxx_TRACK_ASSIGN'\ G # SHOW CREATE TABLE `xxx`.`xxxxx _ EFFECTIVE_TRACK_ ASSIGN`\ G # SHOW TABLE STATUS FROM `xxx` LIKE 'xxxx_task_exec'\ G # SHOW CREATE TABLE `xxx`.`xxxxx _ task_ exec`\ G UPDATExxxxxx_effective_track_exec_channel a SET EXEC_CHANNEL_CODE= (SELECT GROUP_CONCAT (DISTINCT) (channel_id) FROM xxxxxx_EFFECTIVE_TRACK_ASSIGN WHERE status in (1BI 2 4) AND id IN (SELECT assgin_id FROM xxxxxx_task_exec WHERE task_id=a.task_id))\ G

Explain parses SQL statements

The above points outline several ways to get slow query SQL statements, now we need to use explain to analyze the reasons for the slow lookup of SQL statements. Explain is also very easy to use. Just add EXPLAIN before statements such as SELECT | UPDATE.

Explain

Id

Table execution order, replicated sql statements are often divided into many steps, the larger the sequence number, the first execution, and the same execution order of id from top to bottom.

Select_type

The operation type of the data read operation:

SIMPLE (simple SELECT, no UNION or subqueries, etc.)

PRIMARY (outermost query in a subquery. If the query contains any complex subsections, the outermost select is marked PRIMARY)

UNION (the second or subsequent SELECT statement in UNION)

DEPENDENT UNION (the second or subsequent SELECT statement in UNION, depending on the external query)

UNION RESULT (the result of UNION, all subsequent select starting with the second select in the union statement)

SUBQUERY (the first SELECT in the subquery, the result does not depend on the external query)

DEPENDENT SUBQUERY (the first SELECT in a subquery, dependent on an external query)

DERIVED (SELECT of derived table, subquery of FROM clause)

UNCACHEABLE SUBQUERY (the result of a subquery cannot be cached and the first line of the outer link must be reevaluated)

Table

The data comes from that table, and temporary virtual tables are used for complex queries such as associations.

Type

The way data is retrieved

System: the table has only one row of records

Const: find it by index and find it all at once

Eq_ref: unique index scan

Ref: non-unique row index scan

Range: finding by range

Index: traversing index trees

All: full table scan

Possible_keys

Show indexes that may be used

Key

The index actually used

Key_len

The length of the index, generally speaking, the shorter the better.

Ref

The comparison between the column and the index indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

Rows

Estimate the number of result records found

Extra

Details of SQL query

Using where: indicates using where conditional filtering

Using temporary: temporary table was used to store the results

Using filesort: indicates that mysql uses an external index to sort the data. It is not read in the order of the index in the table.

Using index: indicates that an override index is used in the select statement to take values directly from the index

Using join buffer: connection cache used

Using index condition: indicates that the column of the query has a non-indexed column

The above are the slow query sentences in MySQL, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.