MySQL query optimization (ⅰ)

First, query buffering to improve query speed

In general, when we use SQL statements to query, the database server executes this SQL statement each time it receives a SQL from the client. However, when the exact same SQL statement is received within a certain interval (for example, within 1 minutes), it is also executed. Although this can guarantee the real-time data, but most of the time, the data does not require full real-time, that is, there can be a certain delay. If so, executing exactly the same SQL in a short period of time is not worth the candle.
Fortunately, MySQL provides us with the ability to query buffers (only in MySQL 4.0.1 and above, using query buffering). We can improve query performance to some extent through query buffering.

1. We can set the query buffer through the My.ini file in the MySQL installation directory:

setting is also very simple, just set the Query_cache_type to 1. After setting this property, MySQL will query its buffer before executing any of the SELECT statements to see if it is executed in the same SELECT statement, and if so, the result of the execution is not expired, then the query results are returned to the client. Note, however, that the query buffer for MySQL is case-sensitive when writing SQL statements. such as the following two SELECT statements:

SELECT * from TABLE1
SELECT * from TABLE1

The above two SQL statements are a completely different select for query buffering. and query buffering does not automatically handle whitespace, so when you write SQL statements, you should minimize the use of whitespace, especially in the first and trailing spaces of SQL (because the query buffer does not automatically intercept the leading and trailing spaces).

2. temporarily close the query buffering method:

Although query buffering is not set, it can sometimes result in a performance penalty, but there are some SQL statements that require data to be queried in real time, or not used frequently (perhaps one or two times a day). This will need to turn the buffer off. This can, of course, turn off query buffering by setting the value of Query_cache_type, but this will permanently shut down the query buffer.
A way to temporarily turn off query buffering is provided in MySQL 5.0:Sql_no_cache.

SELECT sql_no_cache field1, Field2 from TABLE1

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

3, temporarily open the query buffer method:

We can also set the Query_cache_type in My.ini to 2 so that query buffering is used only after Sql_cache is used.

SELECT sql_calhe * from TABLE1

Second, MySQL automatic optimization of queries

Indexes are very important to the database. Indexes can be used to improve performance at query time. However, sometimes using indexes can degrade performance. We can look at the following sales table:

CREATETABLESALES
(
IdInt(10) UNSIGNEDNotNullAuto_increment,
NAMEVARCHAR(100)Notnull    price floatnotnull    sale_count intnot< Span style= "color: #0000ff;" >null    sale_date DATE notnull Primarykey Index (NAME) ,
  index (sale_date)
)

Let's say we have millions of data stored in this table, and we're going to look at the average price for the merchandise number 1000 in 2004 and 2005. We can write the following SQL statement:

SELECT AVG(price) from SALES
WHERE ID= sale_date between '2004-01-01 ' and ' 2005-12-31';

If the number of such goods is very large, it accounts for 50% or more of the records of the sales table. It is somewhat 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 the record that satisfies the condition is very long (such as 50% or more of the records of the entire table), the speed slows down, which is not as good as scanning the entire table. As a result, MySQL automatically determines whether the index is used for querying based on the proportion of data that satisfies the condition to the entire table.

For MySQL, the above query results accounted for the entire table of records is about 30% when the index is not used, this ratio is the MySQL developers based on their experience. However, the actual scale value will vary depending on the database engine being used.

Third, index-based sorting

One of MySQL's weaknesses is its sort. Although MySQL can query about 15,000 records in 1 seconds, MySQL will only use one index at a time when querying. Therefore, if the where condition already occupies the index, then the index is not used in the sort, which greatly reduces the speed of the query. We can look at the following SQL statements:

SELECT*from SALES WHERE NAME = ' name ' ORDERby sale_date DESC;

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

ALTERTABLE SALES DROPindex name, ADDindex (NAME, Sale_date)

This allows the speed of the first mate to be raised when queried using the SELECT statement above. Note, however, that when using this method, make sure that there are no sort fields in the WHERE clause, in the example above, you cannot query with sale_date, otherwise the query will slow down if the sort is fast, but there is no separate index on the Sale_date field.

SELECT*from SALES WHERE NAME = ' name1 ' and name = ' name2 '

The above query statement looks for records that have name equal to name1 and equal to name2. Obviously, this is an unreachable query, where the condition must be false. MySQL before executing the SQL statement, it will first parse whether the Where condition is unreachable, and if so, no longer executes the SQL statement. In order 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 indicates that MySQL has operated on sales. Then look at the following statement:

EXPLAIN SELECT*from SALES WHERE name = ' name1 ' and name = ' name2 '

As we can see, the table entry is empty, which means that MySQL does not operate on the sales table.

Iv. using a variety of query options to improve performance

In addition to the normal use of the SELECT statement, MySQL provides us with many options to enhance query performance. As described above, the Sql_no_cache and Sql_cache for controlling query buffering are among the two options. In this section, I'll cover a few common query options.

1. Straight_join: Forced connection sequence

When we connect two or more tables together for querying, we don't care which table the MySQL first joins, and then which table. And all of this is determined by a series of calculations, evaluations, and finally a connection sequence within MySQL. As in the following SQL statements, TABLE1 and TABLE2 are not necessarily who are connected:

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

If developers need to manually intervene in 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 ...

The SQL statement above shows that by Straight_join forcing MySQL to join the table in TABLE1, TABLE2 order. You can determine the connection order by Straight_join If you think it is more efficient to connect in your own order than the MySQL recommended sequence.

2, Intervention index use, improve performance

The use of the index has been mentioned above. In general, when querying, MySQL will decide for itself whether to use an index or not, which index to use.
But in some special cases, we want MySQL to use only one or several indexes, or not to use an index. This requires some query options for the control index of MySQL.

(1) Limit the scope of use of the index:

Sometimes we set up many indexes in the data table, and when MySQL chooses the index, the indexes are within the scope of consideration. But sometimes we want MySQL to consider only a few indexes, not the full index, which requires using the use index to set the query statement.

SELECT*from TABLE1 useINDEX (FIELD1, FIELD2) ...

As you can see from the SQL statements above, MySQL selects indexes only on indexes that are built on FIELD1 and FIELD2, regardless of how many indexes have been established in TABLE1.

(2) Limit the range of indexes that are not used:

You can use ignore index to reverse-Select if we want to consider a lot of indexes and have few indexes that are not used. In the example above, the index is selected for consideration, while using ignore index is an index that is not considered.

SELECT*from TABLE1 IGNORE INDEX (FIELD1, FIELD2) ...

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

(3) Forcing the use of an index:

The two examples above provide a choice for MySQL, which means that MySQL does not necessarily use these indexes. And sometimes we want MySQL to have to use an index ( because MySQL can only use one index at query time, so it can only force MySQL to use an index). This will require the use of force index to complete this function.

SELECT*from TABLE1 Force INDEX (FIELD1) ...

The above SQL statements use only the indexes built on FIELD1, not the indexes on other fields.

3. Using temporal tables to provide query performance

When the data in the result set of our query is relatively long, you can force the result set to be placed in the temporary table through the Sql_buffer_result option, so that the MySQL table lock can be released quickly (so that other SQL statements can query the records). And can provide a large set of records for clients for a long time.

SELECT sql_buffer_result * from TABLE1 WHERE ...

Similar to the Sql_buffer_result option is the Sql_big_result, which is typically used for grouping or distinct keywords, this option notifies MySQL, and if necessary, places the query results in a temporary table, even in a temporary table.

SELECT sql_buffer_result FIELD1, COUNT(*) from TABLE1 GROUPby FIELD1

Reprint: http://do-found.blog.163.com/blog/static/2837184200842810115882/

MySQL query optimization (ⅰ)