MySQL performance analysis memo

Time:2020-7-10

[TOC]

Last revision time: 15:08:59, October 21, 2019

Emergency

show full processlist

Check the current thread processing, confirm which statements are executing and how the execution is

Special attention should be paid to those with long execution time. If problems are identified, they can be usedkill {id}Kill the connection

show full processlistEquivalent to the following statement

select id, db, user, host, command, time, state, info
from information_schema.processlist
order by time desc;

be careful:

  • show processlistThe root account can see the connections of all accounts. If it is a normal account, it can only see its own connection
  • If the display length is too long, it can be used\G, i.eshow full processlist\G, to output the results vertically for easy viewing

Extension

\gEquivalent to semicolon

\GIs to display the table to vertical output, easy to view

Slow query log

Slow query log is used to record SQL commands whose execution time exceeds a specified threshold

Confirm the opening condition

mysql> show variables like 'slow_query_log%';
+---------------------+------------------------------------------------+
| Variable_name       | Value                                          |
+---------------------+------------------------------------------------+
| slow_query_log      | ON                                             |
| slow_query_log_file | C:\laragon\data\mysql\DESKTOP-C1GGBS1-slow.log |
+---------------------+------------------------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

Opening mode

configuration file

###############Slow query log################
#Open slow query log
slow_query_log=1
#Logging location
log_output=file
#Slow query log file
slow_query_log_file=/var/run/mysqld/mysqld-slow.log
#Slow query time threshold
long_query_time=10

Command mode (invalid after mysqld instance restart)

--Slow query logging must be turned on globally
set global slow_query_log=1;

--Set slow query time threshold
set global long_query_time=1;

If you want to analyze the execution of some statements, you can consider setting the slow query time threshold of the current session to 0

set long_query_time=0;

be careful

When the database is dragged down (high load), any simple statement may run out of time

Slow query log analysis tool

mysqldumpslow

In the actual production environment, if you want to manually analyze the log, find and analyze SQL, it is obviously an individual effort. MySQL provides the log analysis tool mysqldumpslow

#Analyze slow logs
mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log

#Parameter description
--Verbose version
--Debug
--Help help
 
-V version
-D debug mode
-S order, the default is' at '
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              Al: average lock time
              Ar: average rows sent
              At: average query time
               c: Count access count
               l: Lock time
               r: Rows sent return record
               t: Query time
-R reverses the order, and the default file is in reverse order. reverse the sort order (largest last instead of first)
-T num displays the first n bars
-A do not convert numbers in SQL to N and strings to s. don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-Grep: only consider stmts that include this string
-H hostname MySQL machine name or IP; host name of DB server for*- slow.log  filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-Don't subtract lock time from total time

Examples

Get the maximum number of 10 SQL statements that return the recordset.
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
 
Get the 10 most visited SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

Get the top 10 queries that contain left join by time.
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
 
In addition, it is recommended to use these commands in combination with | and more, otherwise the screen may be swiped.
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

SQL Explain

For the slow execution statements in the slow query log, theExecution plan of SQL statement

Explain can help you understand:

  • Read order of data table
  • The type of the select clause
  • The access type of the data table
  • Available indexespossible_keys
  • Actual index usedkey
  • Index length usedken_len
  • Join matching criteria for the previous table
  • The number of rows queried by the optimizer
  • Additional information (such as using external sort, whether to use temporary table)

give an example

MySQL performance analysis memo

Explain result column analysis

<u>Field ID</u>

SQL execution order is based on

  • ID execution from large to small
  • At the same time, ID is executed from top to bottom

<u>Field select_ Type (query type)</u>

Query type explain
SIMPLE Simple query
Contains no union query or subquery
PRIMARY Outermost query
If the query contains any complex sub parts, the outermost query is marked as primary
SUBQUERY Subquery
Subqueries are included in select or where
DEPENDENT SUBQUERY !! Subquery, but depends on the results of the outer query
Pay attention to the confirmation to avoid large table driving small table
DERIVED Subquery
Subqueries contained in the from list are marked derived
UNION union
Union if the second select appears after the union, it is marked as a union
UNION RESULT Use the results of the union
Select that gets the result from the union table

About Union, the following paragraph is written on the Internet, but I don’t understand it personally

Union if the second select appears after the union, it is marked as a union: if the union is included in the subquery of the from clause, the outer select is marked as: derived


<u>Field table</u>

Data table accessed


<u>Field partitions</u>

Matching partitions


<u>Field type (access method)</u>

Query access mode, performance: all < index < range < index_ merge < ref < eq_ ref < system/const

Generally speaking, at least the access mode should be range, preferably ref level

Access mode explain
ALL Full table scan, looking through the data table from beginning to end
select * from tb1;
Special: if there is a limit limit, it will not continue to scan down after it is found
select * from tb1 where email = '[email protected]'
select * from tb1 where email = '[email protected]' limit 1;
Although the above two statements will perform a full table scan, the second sentence uses limit. If one is found, the scan will not continue.
INDEX Full index scan, search the index from beginning to end
Because the non primary key index tree is smaller, it is faster than all
RANGE Range lookup on index columns
It is usually to quickly locate an index item on the index tree, and then traverse left / right
INDEX_MERGE Merge index, using multiple single column index search, the final results take intersection or union
For example, union is used and two indexes are used separately
REF Use an index to quickly locate (find one or more values based on the index), which isGeneral indexorPartial prefix of the unique index
EQ_REF Quick location using primary key index or unique index
It usually appears in the join query of multiple tables, and the connection uses primary key or unique index (both can only match one row of records)
CONST Find a row exactly by primary key or unique index
constant
The table has at most one matching row (primary key or unique index) because there is only one row, and the column values in this row can be considered as constants by the rest of the optimizer. Const tables are fast because they are read only once
SYSTEM system
The table has only one row, which is a special case of const join type and can be ignored.

Q. The difference between all and index

A. The difference is that all scans primary key indexes while index scans non primary key indexes

To understand here, the so-called full table scan refers to the primary key index scan

Q. EQ_REFandCONSTThe difference between

A. The same point is to use the primary key / unique index to accurately find the row records

  • CONSTThe query criteria are usuallyIndex column = specific constant value
  • EQ_REFIt is usually used as join condition in multi table associated query

<u>Field possible_ Keys (candidate index)</u>


<u>Field key (actual index used)</u>

If it is a consolidated index (index_ Merge), there may be more than 1 key here


<u>Field key_ Len (uses the actual length of the index)</u>

This field evaluates whether the composite index is fully used or only the leftmost prefix is used

The value displayed in this field is the maximum possible length of the index field, not the actual length used_ Len is calculated according to the table definition, not the intra table retrieval)

Calculation rules

Field type Calculation method
character string char(n) N byte length
varchar(n) If utf8 encoding, it is n3 + 2 bytes
If it is utf8mb4 encoding, it is 4
N + 2 bytes
numerical value tinyint 1 byte
smallint 2 bytes
mediumint 3 bytes
int 4 bytes
bigint 8 bytes
time date 3 bytes
timestamp 4 bytes
datetime 8 bytes

If the corresponding index field is allowed to be null, an additional byte is consumed to store null


<u>Field ref</u>

Represents the lookup condition of the index, which may be const or a field of another table in a union query


<u>Field row (scan rows)</u>

The estimated number of lines to scan. Note that it is estimated

In some cases, if the index statistics deviate greatly, the estimated scan lines will be too large, which will affect the selection of query plan

have access toShow index from table nameTo view index statistics

have access toAnalysis table nameRe index information


<u>Field filtered</u>


<u>Field extra (extra information)</u>

This column contains the details of MySQL resolving the query

value explain
Using filesort MySQL can’t get ordered records directly by index, but sort the results extra
MySQL has two file sorting algorithms, which can be completed in memory or disk
Explain won’t tell you which sort of file MySQL will use
It doesn’t tell you whether sorting will be done in memory or on disk.
Using index Use the overlay index to avoid accessing the table. Don’t confuse the overlay index with the index access type.
Using index condition Index push down optimization, new features in 5.6
Using temporary This means that MySQL uses a temporary table when sorting query results
Using where Where filtering is used
This means that the MySQL server will filter after the storage engine retrieves the rows
Many of the where conditions involve columns in the index, which can be verified by the storage engine when (and if) it reads the index
Therefore, not all queries with a where clause will display “using where”.
Sometimes the appearance of “using where” is a hint that queries can benefit from different indexes.
Range checked for each record(index map: N) This means that there is no good index, the new index will be re evaluated on each row of the join, and N is shown in possible_ The bitmap of the index in the keys column and is redundant.
using join buffer Connection caching is used in table join

Profiling

At the top:Show profile commandIt’s going to be abandoned, notice, justSHOW PROFILEOrder to discard

The alternative is from information_ You can view the profiling data table in the schema, which can be linked with parameters:

  • https://www.cnblogs.com/sideo…
  • https://www.cnblogs.com/Jiang…

Show profile command mode (old)

View the execution time of the statement and the cost of each step

Show profile analysis SQL performance tool (detection data exists in temporary table)

  • Open profile at session levelSET profiling=1;
  • Send SQL
  • View the resource cost results of the profile

    • show profilesView all analysis results (there will be an upper limit)
    • show profileView the analysis results of the last executed statement
    • show profile for query <id>View the detailed analysis results of the specified execution statement
    • show profile cpu, block io for query <id>View details, including CPU, block.io execution time
  • Close profile

For more details, please refer to:

  • https://www.cnblogs.com/ggjuc…

information_schema .profiling

To be used and replenished

Recommended Today

ASP.NET Example of core MVC getting the parameters of the request

preface An HTTP request is a standard IO operation. The request is I, which is the input; the responsive o is the output. Any web development framework is actually doing these two things Accept the request and parse to get the parameters Render according to the parameters and output the response content So let’s learn […]