MySQL Query performance optimization Five (optimized for specific types of queries)

This article describes how to optimize a particular type of query.

1. Optimize the count () query
The count () aggregate function, and how to optimize queries that use the function, is probably one of the top 10 topics in MySQL that is most likely to be misunderstood

Count () is a special function that has two very different functions. It can count the number of values in a column or count the number of rows.
The statistic column value requires the column value to be non-null. (No statistics null, that is, a null value count of 0)

Another use of count () is to count the number of rows in the result set. When MySQL confirms that the expression value of parentheses cannot be null, it is actually a statistic
Number of rows. The simplest thing is that when we use COUNT (*), in this case the wildcard character * does not extend as we would guess to all
column, in fact, it ignores all columns and directly counts all rows.

If the same column is counted differently in the same query, it is worth the amount
Select SUM (if (color = ' Blue ', 1, 0)) as blue, sum (if (color = ' red ', 1, 0)) as red from items

Select count (color= ' blue ' or null) blue, COUNT (color= ' red ' or null) red, from items

2. Optimize related queries
This topic is discussed more frequently, which needs to be specifically mentioned here:
Make sure that there is an index on the column in the on or using clause. When creating an index, consider the order of the associations.
When table A and Table B are associated with column C, if the optimizer's association order is b,a, then there is no need for the corresponding
The column is indexed. In general, there is no other reason than to create an index on the corresponding column of the second table in the association order.

ensure that any group by and order by expressions involve only the columns in one table. This makes it possible for MySQL to use the index to optimize the process

when upgrading MySQL, it is important to note that there are other things that may change, such as the association syntax, operator precedence, and so on.

3. Refine the subquery
instead, use associations as much as possible.

4. Optimize GROUP BY and distinct
in many scenarios, MySQL uses the same method to optimize both queries, in fact, the MySQL optimizer will handle the internal
these two types of queries are converted to each other. They can all use indexes to optimize, which is also the most effective optimization method.

Optimizing group by with Rollup
A variant of the group query is to ask MySQL to do a super-aggregation of the returned grouped results again. You can use the WITH Rollup
to achieve this optimization. The best approach is to transfer the WITH rollup functionality to the application processing as much as possible.

5. Optimize limit and offset
when the system needs paging, we usually use the limit plus offset method, plus the appropriate ORDER BY clause.
if there is a corresponding index, usually the efficiency will be good, otherwise, MySQL will need to do a lot of file sorting.

when offset is large, the query can be bad. To optimize this query, either limit the number of pages in the page or optimize the performance of large offset queries.

one of the simplest ways to optimize this type of paging query is to use an index overlay scan instead of querying all of the columns.
the required columns are then returned based on an association operation. This can greatly increase the efficiency when the offsets are large:

Mysql> SELECTfilm_id, description fromSakila.filmORDER  byTitle LIMIT -,5;--after optimizationMysql> SELECTfilm.film_id, Film.description -  fromSakila.film - INNER JOIN( - SELECTfilm_id fromSakila.film - ORDER  byTitle LIMIT -,5 　　  -) asLim USING (film_id);

This "deferred query" will greatly improve the efficiency of the query, it allows MySQL to scan as few pages as possible, to obtain access to the records before
Queries the required columns based on the associated column back to the original table. This technique can also be used to optimize the limit clause in an associated query.

Sometimes it is possible to convert a limit query to a known location query, allowing MySQL to obtain the corresponding results through a range scan.

6. Optimize Sql_calc_found_rows
When paging, another common technique is to add the sql_calc_found_rows hint (hint) to the limit statement.
This allows you to get the number of rows that are satisfied after the limit is removed, so it can be used as the total number of pages. It seems that MySQL has done
Very "advanced" optimizations, such as predicting the total number of rows by some means. But in fact, MySQL only has to scan all the rows that meet the criteria
You will know the number of rows later, so with this hint, MySQL will scan all rows that meet the criteria, regardless of whether or not, and then
Discard unwanted rows instead of terminating the scan after the number of rows that have been limit is satisfied. So the cost of this hint can be very high.

7. Optimize UNION query
MySQL always executes union queries by creating and populating temporary tables. So many optimization strategies are not in the Union query
Very good to use. It is often necessary to manually push the Where,limit,order by clause into each sub-query of the Union in order to
The optimizer can take advantage of these conditions for optimization.

It is important to use UNION ALL unless you really need the server to de-duplicate rows.
Without the all keyword, MySQL will add the distinct option to the temp table, which will cause the entire temporary table
Data unique check. The price is very high. Even with the all keyword, MySQL will still use temporary tables to store the results.
In fact, MySQL always puts the result into a temporary table, then reads it out and returns it to the client, which is often unnecessary.

8. Static query Analysis

9. Using user-defined variables
Set @one: = 1

MySQL Query performance optimization Five (optimized for specific types of queries)