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

This article mainly introduces what are the skills of optimizing MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

Reasons for slow execution of SQL

Hardware problems such as slow network speed, insufficient memory, low throughput of Ipool O, full disk space, etc.

No index or index invalidation

There are too many data records in the data table

Server tuning and various parameter settings may also affect

Efficiency of SQL written by developers

Other

1. EXPLAIN analyzes your SELECT query

In many cases, using the EXPLAIN keyword will let you know how MySQL handles your SQL statements, which can help you analyze your queries and perhaps find optimization methods and potential performance problems as soon as possible. For the specific use of EXPLAIN and the meaning of each parameter, please refer to the relevant documentation.

2. SELECT query must specify the field name.

The query of SELECT * will add a lot of unnecessary consumption (such as CPU, Iripo, etc.), at the same time, it is possible to increase the use of overlay indexes. Therefore, when querying SELECT, it is required to specify the corresponding field name directly after the query.

3. When querying a piece of data, use LIMIT 1

Reduce redundant queries, because after specifying limit 1, the query will no longer continue to query a piece of data, so that the type column in EXPLAIN reaches the const type, and the query statement is better.

4. Index the WHERE field of the search

In general, we set a primary key for each table, and the index is not necessarily given to the primary key. If there is a field in your table that you will always use to do WHERE query search, and it is read more than write, then, please build an index for it. If you are interested in learning more about indexing principles, you can consult relevant materials.

5. Never use ORDER BY RAND ()

If you want to take data at random, maybe the first one will tell you directly, take it with random numbers, remember, this is when you have to control your brain to keep thinking in this direction and stop this terrible thought. Because of this query, there is no benefit to the performance of the database (consuming CPU). One of the better solutions is to find the number N of the data first, and then query it with LIMIT N, 1.

6. Make sure that each table has a primary key ID

We should make it a habit that every time we design a new table, we should design an ID field for it and make it the primary key, preferably int (some also use UUID), and set the ID field to the AUTO_INCREMENT flag.

8. Use NOT NULL whenever possible

Do not think that NULL does not need space, the fact is that NULL also needs extra space, perhaps, many have not noticed but have encountered, the NULL field in the query comparison, is more troublesome. Of course, if you really need NULL, there's nothing you can do about it, use it, otherwise, it's recommended to use NOT NULL.

8. Select the appropriate storage engine

In MySQL, there are MyISAM and InnoDB storage engines, both of which have their own advantages and disadvantages, so we need to understand the difference between them and then make the most appropriate choice, such as InnoDB supports transactions but MyISAM does not, MyISAM query is faster than InnoDB, etc.; in short, if you don't know what to choose, use InnoDB.

9. Save the IP address as UNSIGNED INT

When you need to store IP addresses, many people's first idea will be to store VARCHAR (15) string types, rather than using INT integers; if you use integers to store, it only takes 4 bytes, and you can have fixed-length fields, which will give you an advantage in query.

10. Try not to judge the null value of the field in the WHERE query

We all know that when we null a field, it will be slow, because this judgment will cause the engine to abandon all existing indexes and do a full table scan search.

11. Try not to use LIKE fuzzy queries with% prefix

Fuzzy query is often encountered in daily development, but I believe many people search directly LIKE'% key_word%' or LIKE'% key_word', both of which will lead to index invalidation and full table scan search. If you solve the above fuzzy query, the answer is to use "use full-text index". If you are interested in the specific usage, you can check the information yourself.

12. Avoid expression operations on fields in WHERE queries

For example, the query statement SELECT id FROM table WHERE num * 2 = 50 position, such a query, does an arithmetic operation on the field num by 2, which will cause the index to fail.

14. Reduce unnecessary sorting

Sorting operations consume more CPU resources, so reducing unnecessary sorting can reduce the response time of SQL when the cache hit ratio is high enough.

14. It is recommended to use JOIN instead of sub-query.

Some people will say that the performance of JOIN is not very good, but it still has a great performance advantage compared to subqueries. Specifically, you can learn about the problems related to the execution plan of the subquery.

15. Avoid implicit type conversion

Type conversion mainly refers to the type conversion that occurs when the type of the field is inconsistent with the type of the parameter passed in the WHERE clause. This is because if the data types and field types we pass in are inconsistent, MySQL may convert the data we pass, or leave it to the storage engine without processing. In this way, the index may not be used, resulting in execution plan problems.

16. Avoid inconsistent field types in multi-table queries

When we need a multi-table federated query, when we design the table structure, we try to keep the associated fields of the table consistent with the table, and set the index. At the same time, when multi-table join query, try to use the table with small result set as the driving table.

17. It is recommended to enable query caching

Most MySQL servers have query caching enabled, which is one of the most effective ways to improve performance, because query caching is automatically handled by the MySQL database engine, and when many of the same queries are executed multiple times, these query results are placed in a cache, so that subsequent identical queries do not have to manipulate the table, but directly access the cached results.

18. Use UNION instead of temporary tables

UNION queries can merge two or more SELECT query results into a single query, eliminating the need to create temporary tables. It is important to note that all SELECT statements that use UNION have the same number of fields.

19. Be careful with IN query

Both IN and NOT IN queries should be cautious, as they may result in full table scans, while for consecutive values, use BETWEEN instead of IN.

These are all the contents of this article entitled "what are the techniques for optimizing MySQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.