Comprehensive analysis of the details of MySQL

The main content of this article is "Comprehensive Analysis of the details of MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "Comprehensive Analysis of the details of MySQL"!

1. EXPLAIN

To do MySQL optimization, we need to make good use of EXPLAIN to view the SQL execution plan.

Here's a simple example of tagging (1, type, 2, 3, 4, 5) the data column and join type that we want to focus on. A good sql statement should at least reach the range level. Put an end to all levels

Key column, the index name used. If no index is selected, the value is. Mandatory indexing can be adopted.

Key_len column, index length

Rows column, number of rows scanned. The value is an estimated value.

Extra column, detailed description. Note that common unfriendly values are: Using filesort, Using temporary

2. IN in the SQL statement should not contain too many values.

MySQL optimizes IN accordingly, that is, all the constants in IN are stored in an array that is sorted. However, if the number is higher, the consumption is also relatively large. Another example: select id from table_name where num in (1 between 2) for consecutive values, don't use in if you can, or use a connection instead.

3. The SELECT statement must specify the field name.

SELECT * adds a lot of unnecessary consumption (cpu, io, memory, network bandwidth); increases the possibility of using overlay indexes; and front breaks also need to be updated when the table structure changes. Therefore, it is required to put the field name directly after the select.

4. Use limit 1 when only one piece of data is needed

This is to make the type column in EXPLAIN reach the const type

If the index is not used in the sort field, sort as little as possible

6. If other fields in the restriction do not have an index, use or as little as possible

If one of the fields on both sides of the or is not an index field, and the other condition is not an index field, it will cause the query not to move the index. In many cases, using union all or union (when necessary) instead of "or" will get better results. I believe you have a deeper understanding of "comprehensive analysis of MySQL details". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!