mysql Open Slow Query of EXPLAIN SQL Statement

Today, database operation is increasingly becoming the performance bottleneck of the whole application, especially for Web application. With regard to database performance, this is not just something that DBA needs to worry about, but it is something that we programmers need to pay attention to. When we design the database table structure, we need to pay attention to the performance of data operation when operating the database (especially the SQL statement when looking up the table).

1. Open slow query

1 > Check whether slow query is turned on

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

2 > Opening method: my. cnf directory configuration

slow_query_log=on # Whether to turn it on 
slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log # Slow query file location 
long_query_time=2 # How many seconds does the query exceed before recording 

2. SELECT query in EXPLAIN slow query log

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	NULL	ref	user	user	768	const	1	100.00	NULL

Interpretation of explain column

table: Shows which table the 1-row data is about

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

possible_keys: Displays indexes that might be applied to this table. If empty, there is no possible index. You can select 1 appropriate statement from the where statement for the related domain

key: The index actually used. If it is null, no index is used. In rare cases, mysql will choose indexes with insufficient optimization. In this case, you can use use index (indexname) in the select statement to force 1 index or mysql to ignore the index with ignore index (indexname)

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

ref: Shows which 1 column of the index is used, if possible, a constant

rows: The number of rows that mysql thinks must be checked to return the requested data

extra: Additional information on how mysql resolves queries. Examples: using temporary and using filesort, meaning mysql cannot use indexes at all, resulting in slow retrieval

Calculation of key_len

All index fields, if not null is not set, need to add 1 byte.

Fixed-length fields, int takes 4 bytes, date takes 3 bytes, and char (n) takes n characters.

For the become field varchar (n), there are n characters + two bytes.

The number of bytes per character varies from character set to character set. latin1 encoded, 1 character occupies 1 byte, gbk encoded, 1 character occupies 2 bytes, utf8 encoded, 1 character occupies 3 bytes.

3. Several principles of building indexes

The leftmost prefix matching principle, a very important principle, mysql will match 1 straight to the right until it encounters a range query ( > , < , between, like), for example, a = 1 and b = 2 and c > 3 and d = 4 If indexes are established in order (a, b, c, d), indexes are not needed for d, and indexes can be used if indexes are established (a, b, d, c). The order of a, b and d can be adjusted at will.

= and in can be out of order, for example, a = 1 and b = 2 and c = 3 Indexes can be set up in any order, and mysql's query optimizer will help you optimize them into a form that can be recognized by the index.

Try to choose columns with high discrimination as indexes. The formula of discrimination is count (distinct column)/count (*), which indicates the proportion of fields that are not repeated. The larger the proportion, the fewer records we scan. The discrimination of only one key is 1, while the discrimination of one state and gender fields may be 0 in front of big data. Some people may ask, is there any experience value for this proportion? This value is difficult to determine because of different usage scenarios. We all require the fields that need join to be above 0.1, that is, an average of 1 scan and 10 records.

Indexed columns cannot participate in calculation and function use, and keep the columns clean.

Expand the index as much as possible, and don't create new indexes. For example, the table already has an a index, and now you want to add an index (a, b), so you only need to modify the original index.