Set up MySQL query optimization from four details

It is well known that query optimization is an unavoidable topic in any database. For database engineers, optimization is the most challenging job. MySQL Open source database is no exception. In fact, the author believes that the database optimization is not as much as we imagine the suffering. Under normal circumstances, you can start from the following four details to do a good job in the MySQL database query optimization.

First, use the Explain keyword to evaluate the flaw in the query statement

As the following illustration shows, the author now executes a simple select query in the database to query all the information from a table. Now the database administrator wants to know what the database does when it executes this statement, or whether it is possible to further optimize the query. If you want to know this information, you can add a explain keyword to the query.

A SELECT query statement enables you to query data from a table from a database. But how efficient is the execution of this statement? Is there room for optimization? The content is not available from the simple query statement above. To get more detailed information, you need to add the Explain keyword. As shown in the following illustration:

After adding the Explain keyword, the system does not query the data in the table, but only displays some information during the query. This information is very helpful for our subsequent database query optimization. From the above information we can see that the user is just a simple query. In this query, no indexes, keywords, etc. are used, and where condition statements are not used. This query statement is not very reasonable. Although it can find the final and correct results, its query efficiency may not be obvious. This database expert can be optimized based on the information shown above. What happens if we now add a where statement to a query statement? The following figure shows.

At this point in the last extra field, the system displays the where statement is already in use. In database optimization, we need to catch the null field in the result or the field of the blank content. These places are often the focus of our optimization. As shown in the figure above, we can optimize this SELECT statement by setting the keyword or index in the table to improve the efficiency of the query.

Second, use the same type of column in data comparison to improve query efficiency

In a data query, you sometimes add criteria to a conditional statement. If you have two tables now: the user profile table and the User Rights table, which are associated by the user number. Now you need to query for each user what kind of permissions, at this point by the user number as the query criteria to query. Now assume that the user ID in the User profile table is of type char, and that the user number in the User Rights table is varchar type. Both of these data types are characters, but they are not the same type. What is the efficiency of queries on this table now? The first thing to be sure of is that although they are different types of character data, they are only compatible with each other. In the end, you can still get the right results. With this in mind, we'll consider whether we can optimize the query.

Let's assume again. The data types of the user numbers for both tables are now char. Now the two tables are associated with the query, the results are the same? The result of our tests is that the results of the query are the same, but the time spent is different. And as the amount of data increases, two of queries will be more and more time apart. As you can see from here, although the two query statements are equivalent, their queries are more efficient.

In MySQL databases, although compatible data types can be compared to each other. However, the efficiency of its query will be affected. From the perspective of improving the efficiency of database query, the author suggests that it is better to compare columns with the same type in query conditional statements. Under the same conditions, the same column type can provide better performance than different types of columns. This is particularly important in databases where the volume of data is higher.

However, this optimization involves the column type of the datasheet. This should be considered when designing a datasheet. For the above case, we can set a user ID column specifically in two tables. You can use a sequence of integer types to have the system automatically numbered. The User ID column is then used to compare through the query, rather than through the original user number column. In contrast, it is more efficient to operate queries.

Third, use the wildcard character at the beginning of the LIKE keyword with caution

In the actual work, I found that a lot of database administrators have a bad habit. When they use such keywords as like, wildcards can be used randomly. For example, users now need to find all product information that has a "look" prefix. Users in the query, will habitually use the following statements to query: like "%look%." Instead of querying for product information prefixed with the look, this conditional statement will query for a record of the word look in all the name names.

Although the end result may be the same. But the query efficiency of the two is different. In fact, this is largely due to the improper design of the client application. If the client application is designed, the system displays a% symbol by default. As shown in the following figure.

So the design is good, so that the system can support fuzzy query. But the user in the actual operation, can have the problem. If the user does not enter the word look before the% in the query, enter the word look after%. Because when you query, the cursor is automatically positioned behind the% number. Typically, the user does not adjust the position of the cursor when entering. At this point there is the situation mentioned above.

For this reason, I suggest that you should be very cautious if you need to use wildcard characters after such keywords as like. Especially when looking up records from a large amount of data, the wildcard position must be in place. Try not to use wildcards when you can start with different wildcard characters.

Use other forms as far as possible instead of the LIKE keyword

The above mentioned the need to pay attention to the position of the wildcard character when using the LIKE keyword. In fact, from the query efficiency, we not only need to pay attention to the location of the wildcard, and can not use the LIKE keyword Best not. In fact, in SQL statements, you can use other ways to replace the LIKE keyword. If there is a product table now, its number is 6 digits. Now you need to query the product number that starts with 9. What's the operation?

One is to use the LIKE keyword, such as "9%". Notice the location of the wildcard character. This conditional statement can find the desired result. But from the perspective of performance optimization, this statement is not a good way to handle. We can do that in a few ways.

The second is achieved by comparing symbols. This can be accomplished using value>=900000 and value<=999999. Although the results of both queries are the same. But the time of the query is much shorter than this one with the like symbol.