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

How to optimize the performance of MySQL database through queries? I believe that many novice rookies have not yet learned this skill. Through the summary of this article, I hope you can learn this skill. The following materials are the steps for implementation.

Query is the most commonly used operation in database technology. The process of query operation is relatively simple. First, the SQL statement of the query is issued from the client. After receiving the SQL statement sent by the client, the database server executes the SQL statement, and then returns the query results to the client. Although the process is simple, different query methods and database settings will have a great impact on the performance of the query.

Therefore, this paper discusses the query optimization techniques commonly used in MySQL. The contents discussed include: improving query speed through query buffering; automatic query optimization by MySQL; index-based sorting; unreachable query detection and using a variety of query choices to improve performance.

First, improve the query speed through query buffering

Generally speaking, when we use SQL statement for query, the database server will execute this SQL statement every time it receives the SQL from the client. But when you receive the exact same SQL statement within a certain interval (such as 1 minute), you execute it as well. Although this can ensure the real-time performance of the data, in most of the time, the data does not require complete real-time, that is to say, there can be a certain delay. If so, the cost of performing exactly the same SQL in a short period of time outweighs the gain.

Fortunately, MySQL provides us with query buffering (query buffering can only be used in MySQL version 4.0.1 and above). We can improve query performance to some extent through query buffering.

We can set the query buffer through the my.ini file in the MySQL installation directory. Setup is also very simple, as long as query_cache_type is set to 1. After setting this property, MySQL will query its buffer to see if it has been executed in the same SELECT statement before executing any SELECT statement, and if so, and the execution result does not expire, it directly takes the query result and returns it to the client. However, when writing SQL statements, note that MySQL's query buffers are case-sensitive. Such as the following two SELECT statements:

SELECT * from TABLE1SELECT * FROM TABLE1

The above two SQL statements are completely different SELECT for query buffering. And query buffering does not automatically handle spaces, so you should minimize the use of spaces when writing SQL statements, especially at the beginning and end of SQL (because query buffering does not automatically intercept leading and trailing spaces).

Although query buffering is not set, which can sometimes result in performance losses, there are some SQL statements that need to query data in real time or are not used very often (perhaps once or twice a day). So you need to turn off the buffer. Of course, you can turn off query buffering by setting the value of query_cache_type, but this turns query buffering off permanently. A way to temporarily turn off query buffering is provided in MySQL 5.0:

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

Because the above SQL statement uses SQL_NO_CACHE, the server will not look in the buffer and execute it each time, regardless of whether the SQL statement has been executed or not.

We can also set query_cache_type in my.ini to 2 so that query buffering is used only if SQL_CACHE is used.

SELECT SQL_CALHE * FROM TABLE1

2. Automatic optimization of query by MySQL

Indexes are very important for databases. Indexes can be used to improve performance when querying. However, sometimes using indexes can degrade performance. We can look at the following SALES table:

CREATE TABLE SALES (ID INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,NAME VARCHAR (100) NOT NULL,PRICE FLOAT NOT NULL,SALE_COUNT INT NOT NULL,SALE_DATE DATE NOT NULL,PRIMARY KEY (ID), INDEX (NAME), INDEX (SALE_DATE)

Suppose there are millions of pieces of data in this table, and we want to query the average price of item number 1000 in 2004 and 2005. We can write the following SQL statement:

SELECT AVG (PRICE) FROM SALESWHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31'

If the quantity of this item is very large, it accounts for almost 50% or more of the records in the SALES table. Then it is a bit slow to use the index on the SALE_DATE field to calculate the average. Because if you use an index, you have to sort the index. When there are a large number of records that meet the criteria (such as 50% or more of the records of the entire table), the speed slows down, so it is better to scan the entire table. Therefore, MySQL automatically decides whether to use an index to query based on the percentage of the data that meets the criteria to the data of the entire table.

For MySQL, indexes are not used when the above query results account for about 30% of the records of the entire table, which is based on their experience by MySQL developers. However, the actual scale value will vary depending on the database engine used

III. Sorting based on index

One of the weaknesses of MySQL is its ordering. Although MySQL can query about 15000 records in a second, MySQL can only use at most one index when querying. Therefore, if the WHERE condition already occupies the index, then the index is not used in sorting, which greatly slows down the query. Let's take a look at the following SQL statement:

SELECT * FROM SALES WHERE NAME = "name" ORDER BY SALE_DATE DESC

The index on the NAME field is already used in the WHERE clause of SQL above, so the index will no longer be used when sorting SALE_DATE. To solve this problem, we can create a composite index on the SALES table:

ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)

In this way, the speed of querying using the above SELECT statement will be increased. Note, however, that when using this method, make sure that there are no sort fields in the WHERE clause. In the above example, you cannot query with SALE_DATE, otherwise, although the sorting is fast, there is no separate index on the SALE_DATE field, so the query will slow down again.

4. Detection of unreachable queries

When executing a SQL statement, you will inevitably encounter some conditions that must be false. The so-called must-be condition is that it is false no matter how the data in the table changes. Such as WHERE value 200. We can never find a number that is both less than 100 and greater than 200.

If you encounter such a query condition, it would be unnecessary to execute such a SQL statement. Fortunately, MySQL can automatically detect this situation. For example, we can look at the following SQL statement:

SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"

The above query looks for records where NAME equals both name1 and name2. Obviously, this is an unreachable query, and the WHERE condition must be false. Before executing the SQL statement, MySQL analyzes whether the WHERE condition is an unreachable query, and if so, the SQL statement is no longer executed. To verify this. Let's first test the following SQL with EXPLAIN:

EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"

The above query is a normal query, and we can see that the table item in the execution information data returned using EXPLAIN is SALES. This shows that MySQL is operating on SALES. Take a look at the following statement:

EXPLAIN SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"

We can see that the table entry is empty, which means that MySQL does not operate on the SALES table.

Use a variety of query options to improve performance

In addition to normal use of SELECT statements, MySQL also provides us with a number of options to enhance query performance. SQL_NO_CACHE and SQL_CACHE, which are used to control query buffering, are two of the options described above. In this section, I will introduce several common query options.

1. STRAIGHT_JOIN: force connection order

When we join two or more tables together to query, we don't have to care which table MySQL joins first and which table it joins later. All this is determined by a connection sequence obtained through a series of calculations and evaluations within MySQL. As in the following SQL statement, TABLE1 and TABLE2 are not necessarily who connects to whom:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE...

If developers need to artificially interfere with the order of connections, they have to use the STRAIGHT_JOIN keyword, such as the following SQL statement:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...

As you can see from the SQL statement above, MySQL is forced to join tables in the order of TABLE1 and TABLE2 through STRAIGHT_JOIN. If you think it is more efficient to connect in your own order than in the order recommended by MySQL, you can determine the connection order through STRAIGHT_JOIN.

two。 Interfere with index usage to improve performance

The use of indexes has been mentioned above. In general, when querying, MySQL will decide whether to use an index and which index to use. But in some special cases, we want MySQL to use only one or more indexes, or we don't want to use an index. This requires the use of MySQL's query options to control the index.

Limit the scope of using the index:

Sometimes we build a lot of indexes in the data table, and when MySQL selects the index, these indexes are taken into account. But sometimes we want MySQL to consider only a few indexes, not all of them, which requires USE INDEX to set up the query statement.

SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …

As you can see from the above SQL statement, no matter how many indexes have been built in TABLE1, MySQL only considers the indexes built on FIELD1 and FIELD2 when selecting indexes.

Limit the scope of not using an index

If we have a lot of indexes to consider and few indexes that are not used, we can use IGNORE INDEX for reverse selection. In the above example, the index being considered is selected, while using IGNORE INDEX is selecting the index that is not considered.

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

In the above SQL statement, only the indexes on FIELD1 and FIELD2 in the TABLE1 table are not used.

Force the use of an index

Both of the above examples give MySQL a choice, which means that MySQL does not have to use these indexes. Sometimes we want MySQL to use a certain index (because MySQL can only use one index when querying, it forces MySQL to use only one index). This requires the use of FORCE INDEX to complete this function.

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

The above SQL statement uses only the indexes built on the FIELD1 and not the indexes on other fields.

3. Use temporary tables to provide query performance

When we query a lot of data in the result set, we can use SQL_BUFFER_RESULT. Option forces the result set to be placed in a temporary table so that MySQL's table locks can be quickly released (so that other SQL statements can query these records) and can provide large recordsets to the client for a long time.

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE...

And SQL_BUFFER_RESULT. Similar to the SQL_BIG_RESULT option, which is typically used for grouping or the DISTINCT keyword, this option tells MySQL to put the query results in a temporary table or even sort it in a temporary table if necessary.

SELECT SQL_BUFFER_RESULT FIELD1, COUNT (*) FROM TABLE1 GROUP BY FIELD1

VI. Conclusion

There is also a "28 principle" in programming, that is, 20% of the code takes 80% of the time. The same is true of database application development. The optimization of database application program focuses on the execution efficiency of SQL. The focus of data query optimization is to make the database server read less data from disk and read pages sequentially rather than sequentially.

After reading the above, have you mastered the method of optimizing the performance of MySQL database through query? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.