Shulou(Shulou.com)06/01 Report--

This article focuses on "how to optimize MySQL", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to optimize MySQL.

1 、 EXPLAIN

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

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

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

5. 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.

7. Replace union with union all as much as possible

The main difference between union and union all is that the former needs to merge the result sets and then carry out unique filtering operation, which will involve sorting, increasing a large number of CPU operations, and increasing resource consumption and delay. Of course, the prerequisite for union all is that there is no duplicate data in both result sets.

8. Do not use ORDER BY RAND ()

9. Distinguish between in and exists, not in and not exists

10. Segmented query

In some user selection pages, the time range selected by some users may be too large, resulting in slow query. The main reason is that there are too many scan lines. At this time, you can use the program, segment query, loop traversal, merge the results to display.

At this point, I believe you have a deeper understanding of "how to optimize MySQL", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.