MySQL Query optimization Note the following four details

Original: http://bbs.landingbj.com/t-0-244231-1.html

In any database, query optimization is an unavoidable topic. For database engineers, optimization is the most challenging task. MySQL Open source database is no exception. In fact, the author believes that the database optimization is not as much as people think of the suffering. In general, you can do a good job of querying the MySQL database from the following four details.

First, using the Explain keyword to evaluate the flaws in the query statement

As shown, the author now executes a simple select query in the database, querying all the information from a single table. Now the database administrator wants to know what the database does when it executes the statement? or, to be sure, this query statement has no further optimizations possible. If you want to know this information, you can add a explain keyword to the query statement.

The SELECT query statement allows you to query the data in a table from a database. But what is the efficiency of this statement? Is there any room for optimization? The content cannot be obtained from this simple query statement above. To get more detailed information, you need to add the Explain keyword. As shown in the following:

After adding the Explain keyword, the system does not query the data in the table, but only shows some of the information in the query process. This information is very helpful for our subsequent database query optimization. As we can see from the above message, the user simply makes a simple query. In this query, there are no indexes, keywords, etc., and there is no use of the WHERE condition statement. For this reason the query statement is not very reasonable. Although it can find the final correct result, its query efficiency may not be obvious. For this reason, database experts can optimize based on the information shown above. What happens if we now add a where statement to the query statement? as shown in.

At this point in the last extra field, the system will show that the where statement is already in use. In the??? of 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, we can optimize this SELECT statement by setting a keyword or index in the table to improve the efficiency of the query.

Second, the same type of columns are used to improve the query efficiency when comparing data

In the case of a data query, it is sometimes added to the conditional statement to determine the condition. Now there are two tables: the User basic information table and the user permission table, both of which are associated with the user number. Now you need to query what permissions each user corresponds to, and then use the user number as the query criteria to query. Now assume that the user number field in the user's basic information table is of type char, whereas the user number in the User Rights table is a varchar type. Both data types are character-type, but they are not of the same type. What is the efficiency of queries that are associated with a single table? The first thing to make sure is that although they are two different types of character data, they are mutually compatible. Finally, you can still get the right results. With this in mind, let's consider whether we can optimize this query statement.

Let's just assume. Now the data type of the user numbers for both tables is char. Now the two tables are associated with the query, will the results be the same? The result of our tests is that the results of the query are the same, but the time it takes is different. And as the amount of data increases, the time difference between the two queries becomes longer. As you can see from here, although these two query statements are equivalent, their query efficiency is different.

In the MySQL database, although mutually compatible data types can be compared to each other. However, the efficiency of the 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 condition statements. Under the same conditions, the same column types can provide better performance than columns of different types. This is especially important in databases where data volumes are relatively large.

However, this optimization involves the column type of the data table. This is something that needs to be considered when designing a data table for this purpose. For this case, we can set a user ID column specifically in two tables. You can use a sequence of integer types to allow the system to be automatically numbered. The User ID column is then compared at query time instead of the original user number column. In contrast, this is a much more efficient way to operate queries.

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

In the actual work, the author found that many database administrators have a bad habit. Wildcard characters are used when they use keywords like. Now the user needs to find all product information prefixed with "look". When the user queries, it is customary to query using the following statement: Like "%look%". Instead of querying for product information prefixed with look, this conditional statement will look up a record of the word look in all the names.

Although the final result may be the same. But the query efficiency of the two is different. In fact, this is due in large part to the improper design of the client application. The system displays a% symbol by default when the client application is designed. As shown in.

This design is intended to be good, allowing the system to support fuzzy queries. But the user in the actual operation, you can have problems. If the user does not enter the word look in front of the% number in the query, it will enter the word look after%. Because at query time, the cursor is automatically positioned after the% number. Typically, the user does not adjust the position of the cursor when it is entered. At this point, there is the above-mentioned situation.

For this reason, I suggest that if you need to use a wildcard character after a keyword like, you should be very cautious. Especially when looking for records from a large amount of data, the location of this wildcard must be used in the right place. Try not to use wildcards at the beginning if you have different wildcard characters.

Iv. try to use other forms instead of the LIKE keyword

It is mentioned above that you need to be aware of the wildcard position when using the LIKE keyword. In fact, from the query efficiency point of view, we need not only pay attention to the location of the wildcard, and can not use the LIKE keyword is best not used. In fact, in SQL statements, you can use other methods instead of the LIKE keyword. If there is now a product table, it is numbered 6 bits. You now need to query the product number that starts with 9. How does this work?

One is possible by using the LIKE keyword, such as "9%". Note the location of this wildcard. This conditional statement can be used to find the desired result. However, from the perspective of performance optimization, this statement is not a good way to handle. We can also do it in some way.

The second is to achieve by comparing symbols. This can be achieved by 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 sign.

MySQL Query optimization Note the following four details