MySQL enables slow query (introduction to using EXPLAIN SQL statement)

Today, database operations are increasingly becoming the performance bottleneck of the entire application, which is especially evident for Web applications. Regarding database performance, it is not just something that DBAs need to worry about, but it is also something that we programmers need to pay attention to. When we design the database table structure and operate the database (especially the SQL statements when looking up the table), we need to pay attention to the performance of data operations.

1. Enable slow query

1> Check whether slow query is enabled

show variables like "%quer%";
slow_query_log = ON # Enabled

2> How to enable: my.cnf directory configuration

slow_query_log=on #Whether to enable slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log #Slow query file location long_query_time=2 #How many seconds does it take for the query to be recorded

2. SELECT queries appearing in the EXPLAIN slow query log

Explanation of the explain column

• table: Displays which table the data in this row is about

• type: This is the important column and shows what type of connection is used. The connection types from best to worst are const, eq_reg, ref, range, index, all

• possible_keys: Displays the indexes that may be applied to this table. If empty, no index is possible. You can select an appropriate statement from the where clause for the relevant domain

• key: The actual index used. If null, no index is used. In rare cases, MySQL may choose an index that is not optimal enough. In this case, you can use use index (indexname) in the select statement to force the use of an index or use ignore index (indexname) to force MySQL to ignore the index.

• key_len: The length of the index used. The shorter the length, the better without losing accuracy.

• ref: shows which column of the index is used, if possible, a constant

• rows: The number of rows that MySQL considers necessary to check to return the requested data

• extra: Additional information about how MySQL parses the query. For example: using temporary and using filesort means that MySQL cannot use the index at all, resulting in slow retrieval.

Calculation of key_len

1. For all index fields, if not set to not null, one byte needs to be added.

2. Fixed-length field: int occupies four bytes, date occupies three bytes, and char(n) occupies n characters.

3. For a varchar(n) field, there are n characters + two bytes.

4. Different character sets use different numbers of bytes per character. In latin1 encoding, one character occupies one byte, in gbk encoding, one character occupies two bytes, and in utf8 encoding, one character occupies three bytes.

3. Several principles for building indexes

• The leftmost prefix matching principle is a very important principle. MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and stops matching. For example, if a = 1 and b = 2 and c > 3 and d = 4 are created in the order of (a, b, c, d), d will not be used in the index. If an index is created in the order of (a, b, d, c), all of them can be used. The order of a, b, d can be adjusted arbitrarily.

• = and in can be in any order, for example, a = 1 and b = 2 and c = 3. You can create an (a,b,c) index in any order, and the MySQL query optimizer will help you optimize it into a form that the index can recognize.

• Try to choose columns with high discrimination as indexes. The formula for discrimination is count(distinct column)/count(*), which indicates the ratio of non-duplicate fields. The larger the ratio, the fewer records we need to scan. The discrimination of a unique key is 1, while some status and gender fields may have a discrimination of 0 in the face of big data. Someone may ask, is there any empirical value for this ratio? This value is difficult to determine due to different usage scenarios. Generally, we require the value of the field to be joined to be above 0.1, which means that an average of 10 records are scanned for each field.

• Index columns cannot be used in calculations or functions, so keep the columns clean.

• Try to expand the index as much as possible and do not create a new index. For example, if there is already an index of a in the table, and you want to add an index of (a,b), you only need to modify the original index.