The basic course of performance optimization for MySQL query

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 the query speed through query buffering; MySQL's automatic query optimization, indexed sorting, the detection of unreachable queries and the use of various query options 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: 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 TABLE1 II, MySQL automatic query optimization

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 following sales table: CREATE Table Sales

(

ID INT (a) UNSIGNED not NULL auto_increment,

NAME VARCHAR not NULL,

Price FLOAT is 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.

Iii. Indexing based sorting

One of the weaknesses of MySQL is its sort. Although MySQL can query about 15,000 records in 1 seconds, MySQL has only one index to use when querying. Therefore, if the where condition already occupies the index, then the index is not used in the sort, which will greatly reduce the speed of the query. We can 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 the SQL above, so the index is no longer used when sorting sale_date. To solve this problem, we can establish a composite index on the sales table: ALTER TABLE SALES DROP index name, ADD index (name, sale_date)

This will increase the speed of the first mate when using the above SELECT statement to query. Note, however, that when you use this method, make sure that there are no sort fields in the Where clause, and in the example you cannot query with sale_date, otherwise the query slows down, although the sort is fast, but there is no separate index on the Sale_date field.

Iv. Detection of unreachable queries

When you execute a SQL statement, you will inevitably encounter some of the required false conditions. The condition of the so-called required leave is that no matter how the data in the table changes, this condition is false. such as where value < value > 200. We can never find a number that is less than 100 and greater than 200.

If you encounter such a query condition, it is superfluous to execute such an SQL statement. Fortunately, MySQL can automatically detect this situation. If we can look at the following SQL statement: SELECT * from SALES WHERE NAME = "name1" and name = "Name2"

The above query statement looks for records where name is equal to name1 and equal to name2. Obviously, this is an unreachable query, where the condition must be false. MySQL does not execute the SQL statement until it executes the SQL statement by parsing whether the where condition is an unreachable query. To verify this. We first test the following SQL using 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 means that MySQL is operating on sales. Look at the following statement: EXPLAIN SELECT * from SALES WHERE NAME = "name1" and name = "Name2"