Performance optimization for MySQL queries

Queries are the most commonly used operations in database technology. The query operation process is relatively simple, first from the client issued a query SQL statement, the database server after receiving the SQL statement sent by the client, execute the SQL statement, and then return the results of the query to the client. Although the process is very simple, but different query methods and database settings, the query performance will have a very important impact.

Therefore, this paper discusses the query optimization techniques commonly used in MySQL. The contents of the discussion are as follows: Improve query speed by query buffering, automatic optimization of query by MySQL, sorting based on index, detection of unreachable query and use of various query choices to improve performance.

First, through query buffer to improve query speed

In general, when we use SQL statements to query, the database server executes the SQL statement each time it receives a client-sent SQL. But when you receive exactly the same SQL statement within a certain interval (for example, within 1 minutes), you execute it as well. Although this can guarantee the real-time data, but most of the time, the data does not require full real-time, that is, can have a certain delay. If so, executing exactly the same SQL in a short time can be a bit of a loss.

Luckily MySQL provides us with the ability to query buffering (only use query buffering in MySQL 4.0.1 and above). We can improve query performance to some extent through query buffering.

We can set the query buffer by My.ini files in the MySQL installation directory. The setting is also very simple, just set the Query_cache_type to 1. When this property is set, MySQL queries its buffer to see if the same SELECT statement has been executed, and if so, and if the execution result does not expire, the query results are returned to the client directly before executing any SELECT statement. However, when writing SQL statements, note that the MySQL query buffer is case-sensitive. The following two SELECT statements are as follows:SELECT * from TABLE1
SELECT * FROM TABLE1

The two SQL statements above are a completely different select for query buffering. and query buffering does not automatically process spaces, so when writing SQL statements, you should minimize the use of space, especially in the SQL first and end of the space (because the query buffer does not automatically intercept the head and tail spaces).

While no query buffering is set, there may sometimes be performance losses, but there are some SQL statements that need to be queried in real time or infrequently (perhaps one or two times a day). That would require a buffer to be turned off. Of course, this can be done by setting the value of Query_cache_type to turn off query buffering, but this will permanently shut down the query buffer. A way to temporarily turn off query buffering is provided in MySQL 5.0:SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

The above SQL statement is Sql_no_cache, so the server does not look in the buffer, regardless of whether the SQL statement is executed, and executes it every time.

We can also set the Query_cache_type in My.ini to 2 so that query buffering is used only when Sql_cache is used. SELECT SQL_CALHE * FROM TABLE1the automatic optimization of MySQL query

Indexes are very important for databases. You can use indexes to improve performance when querying. But sometimes using indexes can degrade performance. We can look at the sales table as follows: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)
)

Let's say we have millions of data in this table, and we're looking for the average price in 2004 and 2005 for items with commodity number 1000. We can write the following SQL statement:SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

If the number of this product is very large, almost accounted for the sales table 50% or more of the record. Then using the index on the Sale_date field to calculate the average is a bit slow. Because if you use an index, you have to sort the index. When the records that meet the criteria are very long (such as 50% or more of the records in the entire table), the speed slows down so that the entire table is scanned. As a result, MySQL automatically determines whether to use the index to query automatically based on the proportion of data that satisfies the criteria for the entire table.

For MySQL, the above query results for the entire table record proportion is about 30% when the index is not used, this ratio is the MySQL developers based on their experience. However, the actual scaling values vary according to the database engine being used.