Mysql enables slow query (description of SQL statements) and mysqlexplain

Mysql enables slow query (description of SQL statements) and mysqlexplain

Today, database operations are increasingly becoming the performance bottleneck of the entire application, especially for Web applications. Concerning the database performance, this is not just something that DBAs need to worry about, but it is something that our programmers need to pay attention. When designing the database table structure and operating the database (especially the SQL statements used in table queries), 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> enabling method: configure the my. cnf directory

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 to query records

2. EXPLAIN slow query the SELECT query in the 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

Description of the explain Column

• Table: displays the data of this row about which table

• Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, index, and all.

• Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the where statement for the relevant domain.

• Key: actually used index. If it is null, no index is used. In rare cases, mysql selects an optimized index. In this case, you can use index (indexname) in the select statement to force 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.

• Ref: indicates which column of the index is used. If possible, it is a constant.

• Rows: the number of rows that mysql deems necessary to check to return the requested data

• Extra: extra information about how mysql parses the query. Example: using temporary and using filesort, which means mysql cannot use indexes at all, and the result is that the retrieval will be slow.

Calculation of key_len

1. If not null is not set for all index fields, a byte is required.

2. For a fixed length field, int occupies four bytes, date occupies three bytes, and char (n) occupies n characters.

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

4. For different character sets, each character occupies a different number of bytes. Latin1 encoding: one character occupies one byte, and gbk encoding. One character occupies two bytes. The UTF-8 encoding occupies three bytes.

3. indexing principles

• The leftmost prefix matching principle is very important. mysql always matches the right until it encounters a Range Query (>, <, between, like) to stop matching, for example, if a = 1 and B = 2 and c> 3 and d = 4 is created for an index in the order of (a, B, c, d), d cannot use the index, if an index (a, B, d, c) is created, the order of a, B, and d can be adjusted as needed.

• = And in can be out of order. For example, a = 1 and B = 2 and c = 3 (a, B, c) indexes can be created in any order, the mysql query optimizer helps you optimize it into a recognizable form of indexes.

• Select a column with a high degree of discrimination as the index. The formula for differentiation is count (distinct column)/count (*), indicating the proportion of fields that are not repeated. The larger the proportion, the fewer records we scan, the differentiation of the unique key is 1, while some state and gender fields may be 0 in front of big data. Someone may ask, is there any experience with this proportion? Different use cases make it hard to determine this value. Generally, we require more than 0.1 join fields, that is, to scan 10 records on average.

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

• Do not create new indexes. For example, if the table already has an index of a and now you want to add an index of (a, B), you only need to modify the original index.