What are the methods to improve MySQL query efficiency and query speed optimization?

1. Use the EXPLAIN keyword to evaluate defects in the query statement

The author executed a simple Select query statement in the database to obtain all the information in a table, as shown in the figure below. Now the database administrator wants to know what work the database did when executing this statement, or whether there is any possibility of further optimization of this query statement. If you want to know this information, you can add an Explain keyword to the query statement.

The data in a table can be queried from the database through the Select query statement. Execution efficiency and room for optimization cannot be derived from this simple query statement. In order to understand more detailed information, you need to add the Explain keyword. As shown in the figure below:

After adding the Explain keyword, the system does not query the data in the table, but only displays some information during the query process. This information is very helpful for our subsequent database query optimization. From the above information, we can see that the user just made a simple query. In this query, no indexes, keywords, or Where statements are used. For this reason, this query statement is not very reasonable. Although it can find the final correct result, its query efficiency may not be very obvious. For this purpose database experts can perform optimizations based on the information shown above. What would be the result if we add a WHERE statement to our query now, as shown in the following image?.

When the Where statement is used in the last Extra field, the system will show that it has been used. During database optimization, we need to capture NULL fields or fields with blank content in the results. These places are often the focus of our optimization. You can optimize this Select statement and improve query efficiency by setting keywords or indexes in the table, as shown in the figure.

2. Use columns of the same type when comparing data to improve query efficiency

When querying data, sometimes judgment conditions are added to the conditional statement. There are now two tables: user basic information table and user permissions table, which are related by user number. To query the permission information of each user, you need to use the user number as the query condition. Now assume that the user number field in the user basic information table is of type CHAR; and the user number field in the user permissions table is of type VARCHAR. Although these two data types are both character types, they are not the same type. Now, how efficient is the query efficiency when performing a correlation query on these two tables? The first thing that needs to be determined is that although they are different types of character data, they are compatible with each other. You can still get the correct result in the end. After clarifying this point, let us consider whether this query statement can be optimized

Let's make another hypothesis. Now the data type of the user number in both tables is CHAR. Now perform a related query on these two tables. Are the results obtained the same? The result of our test is that the query results are the same, but the time spent is different. And as the amount of data increases, the time difference between the two queries will become longer and longer. From here we can know that although the two query statements are equivalent, their query efficiency is different.

Although data types are compatible with each other in MySQL database, comparisons can still be made. However, the efficiency of its query will be affected. In order to improve the efficiency of database query, the author recommends that it is best to compare columns of the same type in the query condition statement. Under the same conditions, the same column type can provide better performance than columns of different types. This is especially important in databases with large amounts of data.

However, this optimization needs to involve the column type of the data table. For this reason, you need to consider this when designing the data table. For the above case, we can specifically add a user ID column to these two tables. You can use a sequence of integer types to allow the system to automatically number. Then compare through this user ID column when querying, instead of comparing through the original user number column. Relatively speaking, the efficiency of query operation will be higher.

3. Use wildcards at the beginning of the Like keyword with caution

In actual work, the author found that many database administrators have a bad habit. When they use keywords such as Like, they use wildcards randomly. For example, now the user needs to find all product information prefixed with "LOOK". When querying, users are usually accustomed to using statements similar to the following: use like “%LOOK%”. This conditional statement will obtain all records containing the word LOOK in the product name, instead of only obtaining product information prefixed with LOOK.

Although the end result may be the same. However, the query efficiency of the two is different. In fact, a large part of this is caused by improper design of client applications. For example, when designing client applications, the system will display a % symbol by default. As shown below.

The intention of this design is good, so that the system can support fuzzy queries. But users may have problems in actual operation. If the user does not enter the word LOOK before % when querying, but enters the word after %. Because when querying, the cursor will automatically be positioned behind the % sign. Normally, users do not adjust the cursor position when typing. At this time, the situation mentioned above occurred.

In order to avoid unexpected situations, the author strongly recommends being very cautious when using keywords such as Like followed by wildcards. Especially when searching for records from a large amount of data, the position of this wildcard must be used in the right place. If you can use different wildcards at the beginning, try not to use wildcards.

4. Try to use other forms to replace the Like keyword

As mentioned above, you need to pay attention to the position of the wildcard when using the Like keyword. In terms of query efficiency, we need to pay attention to the position of wildcards and avoid using the "Like" keyword as much as possible. In fact, in SQL statements, other methods can be used to replace the Like keyword. For example, there is a product table with a 6-digit number. Now we need to query the product number starting with 9. How to do this?

First, you can use the Like keyword, such as LIKE “9%”. Note the position of this wildcard. This conditional statement can find the required results. But from the perspective of performance optimization, this statement is not a good way to handle it. We can also achieve this through some compromises.

The second is achieved by comparing symbols. This sentence can be reformulated as: This can be achieved by using a method with a Value between 900000 and 999999. Although the results of both queries are the same. However, the query time of this statement is much shorter than the above statement using the Like symbol.

The above is the detailed content of What are the methods to improve MySQL query efficiency and query speed optimization?. For more information, please follow other related articles on the PHP Chinese website!