20 optimization summary of mysql

The content of this article is about the optimization summary of mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Preface

Nowadays, database operations have increasingly become the performance bottleneck of the entire application, especially for Web applications. Therefore, I have compiled some suggestions for MySQL optimization. I hope these optimization techniques will be useful to you. If you can’t summarize them, you are welcome to add them.

Reasons for slow SQL execution

1. Slow network speed, insufficient memory, low I/O throughput, full disk space and other hardware problems

2. No index or index failure

3. There are too many data records in the data table

4. Server tuning and various parameter settings It may also affect the efficiency of SQL written by

6. Others

1. EXPLAIN Analyze your SELECT query

In many cases, using the EXPLAIN keyword can let you know how MySQL processes your SQL statement. This can help you analyze your query statement, so that you may be able to find optimizations as soon as possible. methods and potential performance issues. For the specific use of EXPLAIN and the meaning of each parameter, please refer to the relevant documents.

2. The SELECT query must specify the field name.

SELECT * query will add a lot of unnecessary consumption (such as CPU, I/O, etc.). At the same time, it may also increase the use of covering indexes. . Therefore, when performing a SELECT query, it is required to directly specify the corresponding field name to be queried at the end.

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

to reduce redundant queries, because after specifying limit 1, the query will no longer continue after querying a piece of data, making the type column in EXPLAIN To reach the const type, the query statement is better.

4. Create an index for the searched WHERE field

Generally, we will set a primary key for each table, and the index is not necessarily the primary key. If there is a field in your table that you always use for WHERE queries and searches, and it is more read than written, then please create an index for it. If you are interested in learning more about the principles of indexing, Relevant information can be found.

5. Never use ORDER BY RAND()

If you want to get data randomly, maybe the first one will tell you directly to use random numbers. Remember, at this time you must Control your brain to continue thinking in this direction and stop this terrible thought. Because this kind of query has no benefit to the performance of the database (consuming CPU). One of the better solutions is to first find the number N of data, and then use LIMIT N, 1 to query like this.

6. Ensure that each table has a primary key ID

We should develop a habit. Every time we create a new table, we should design an ID field for it, and Make it the primary key, preferably of type INT (some also use UUID), and set the ID field to the AUTO_INCREMENT flag.

8. Use NOT NULL as much as possible

Don’t think that NULL does not need space. The fact is that NULL also needs extra space. Maybe many people have not noticed but have encountered it. NULL fields are in When querying and comparing, it is more troublesome. Of course, if you really need NULL, then no problem, just use it. Otherwise, it is recommended to use NOT NULL.

8. Choose the appropriate storage engine

There are two storage engines, MyISAM and InnoDB, in MySQL. Both have their own pros and cons, so we need to understand the differences between the two and then make the best decision. Suitable choices, for example, InnoDB supports transactions but MyISAM does not, MyISAM queries are faster than InnoDB, etc.; in short, if you don't know what to choose, then use InnoDB.

9. Save the IP address as UNSIGNED INT

When encountering the need to store the IP address, many people’s first thought will be to store the VARCHAR (15) string type, and You wouldn't think of using INT integer type to store it; if you use integer type to store it, it only takes 4 bytes, and you can have fixed-length fields, and this will bring you advantages in querying.

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

We all know that when we judge a field as null, it will be slower. This is because of this The judgment will cause the engine to abandon the use of all existing indexes and perform a full table scan search.

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

Fuzzy query, we often encounter it in daily development, but I believe many people directly LIKE ' %key_word%' or LIKE '%key_word' are searched like this. These two search methods will cause the index to fail and require a full table scan search. If you want to 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 performing expression operations on fields during WHERE queries.

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

14. Reduce unnecessary sorting

Sorting operations will consume more CPU resources, so reducing unnecessary sorting can reduce SQL performance when the cache hit rate is high and I/O is sufficient. Response time.

14. It is recommended to use JOIN instead of subquery

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

15. Avoid implicit type conversion

Type conversion mainly refers to the type conversion that occurs when the type of the field in the WHERE clause is inconsistent with the type of the parameter passed in; this is Because if the data type we pass in is inconsistent with the field type, MySQL may perform a type conversion operation on the data we pass in, or it may not process it and directly hand it over to the storage engine for processing. In this way, the index may not be able to be processed. Execution plan problems caused by usage conditions.

16. Avoid inconsistent field types in multi-table queries

When we encounter the need for multi-table joint queries, when we design the table structure, try to keep the associated fields of the table consistent with the table, and Indexes must be set. At the same time, when performing multi-table connection queries, try to use the table with a small result set as the driving table.

17. It is recommended to turn on the query cache

Most MySQL servers have the query cache turned on. This is one of the most effective ways to improve performance, because the query cache is automatically processed by the MySQL database engine. When many of the same queries are executed multiple times, these query results will be placed in a cache, so that subsequent identical queries do not need to operate the table but directly access the cached results.

18. Use UNION instead of temporary table

UNION query can merge two or more SELECT query results into one query, so there is no need to create a temporary table to complete. It should be noted that the number of fields in all SELECT statements using UNION must be the same.

19. Use IN queries with caution

Be careful with IN and NOT IN queries, because they may lead to a full table scan. For continuous values, do not use IN if you can use BETWEEN.

20. Welcome to add

Conclusion

This is mainly to consider optimization from the perspective of query, as well as some sub-table, partition technology and read-write separation; the above optimization If the above mentioned points are not in place, please understand. There are many places where MySQL can be optimized. Other optimization suggestions are welcome, thank you.

The above is the detailed content of 20 optimization summary of mysql. For more information, please follow other related articles on the PHP Chinese website!