High-performance MySQL-detailed optimization of specific types of queries

This section is mainly for some specific types of optimization queries:
(1) count query optimization;
(2) Related query
(3) Subquery
(4) GROUP BY and DISTINCT optimization
(5) LIMIT paging optimization

count query optimization

The role of the COUNT() aggregate function:
(1) Count the number of values ​​in a certain column, and you can also count rows number. It should be noted that when counting column values, the column value must be non-null (not counting NULL)

(2) Count the number of rows in the result set. When the column value cannot be empty, the number of rows in the table is counted. But to ensure that you must use COUNT() to get the number of rows in the result set. Wildcard will directly ignore all column values ​​and directly calculate the number of rows for optimization.

For the MyISAM storage engine, COUNT(*) is very fast when the where query conditions are not limited in a single table, because MyISAM itself has already stored the total number of rows. When there is a where qualification, query statistics are also required.

The following is a simple optimization usage example:
(1) Optimization 1:

##It can be seen that if we directly check the record of id>100 , involving the scanning of more than 20 million rows of records. But due to the COUNT(

) feature, we can use count() - (id

(2) Optimization 2:

In addition, there is another optimization method which is to use covering index.

Related Query Optimization

(1) Ensure that there is an index on the column of the ON or USING clause. When creating an index, you must consider the order of association. When table A and table B are associated with column c, if the optimizer's association order is B, A, you only need to create an index on table A. Unused indexes will occupy storage

(2) Ensure that
expressions in any Group by and order by operations only involve columns in one table. In this wayMySQLit is possible to use index optimization

Subquery

Use subqueries as little as possible, because subqueries will generate temporary tables; unless like count(*) The temporary table is very small.

GROUP BY and DISTINCT optimization

The most effective optimization of GROUP BY and DISTINCT is to use indexes.

When the index cannot be used, group by is completed using two strategies: temporary table or file sorting for grouping.

All columns for grouping must be indexed. For example: For a query like

select product, count(*) from orders group by product;

, an index must be created for product.

LIMIT paging optimization

When performing paging operations, certain data is usually queried through offsets. Then coupled with the explained order by, the performance is generally good.

Be sure to add an index to the order by column.

But for limit 10000, 10, to retrieve the target 10 records, you must first query the previous 10,000 records. The cost is very high. In this case, the simplest way to optimize is to use a covering index.

The above is the detailed content of High-performance MySQL-detailed optimization of specific types of queries. For more information, please follow other related articles on the PHP Chinese website!