MySQL Query performance optimization

Basic knowledge of queries

The MySQL query process is as follows:

MySQL hits the cache through a hash lookup of the query statement, and it is important to note that the cache is not hit if the query statement is case-insensitive or has extra spaces.

A query typically has a lot of execution, and the query optimizer chooses the optimal query by calculating the cost (random reads).

MySQL handles all queries as joins, and joins are executed in a loop-nested strategy, as shown in:

optimization and limitations of queries

We need to know what optimizations the query optimizer will do, so that you can write queries without having to consider doing them manually, and handing them over to the query optimizer is a better choice, and the optimization type of the query optimizer is as follows:

1. Connection optimization

(1) Reordering tables in joins

(2) converting an outer join into an inner join

2. Sorting optimization

(1) Sorting using indexes

(2) Fast memory sequencing

(3) Sorting files

3. Optimize count ()

(1) count (*) without a WHERE clause: number of rows in the record table

(2) count (column) counts column non-null rows, and column cannot be null when count (column) is optimized to count (*)

4. Optimize in ()

(1) Sorting the data inside (), doing a binary lookup, does not use this optimization for in () subqueries, such as:

5. Algebraic equivalence optimization

(1) Simplifying and normalizing algebraic expressions

(A<b and B=c) and a=5 = B>5 and B=c and a=5

6. Early termination

Early termination means that MySQL will stop processing the query as soon as it satisfies the query's condition.

(1) LIMIT

(2) Impossible query conditions

(3) obtain a unique value or value does not exist

7. Other Optimizations

(1) Index optimization min () and Max ()

(2) Overlay index

(3) Equal transfer

There are certain limitations to the query optimizer optimizations that need to be optimized manually where these query optimizer is not well optimized:

(1) Associate subqueries. such as in () may be poorly optimized, as below is a bad optimization

(2) Union (Union). Sometimes it is not possible to apply the conditions outside the Union to the internal

(3) Index merging. The cost of sorting and merging can be large

(4) Equal delivery. Large in table causes slower optimizations

(5) Parallel execution. Cannot execute a query in multiple CPUs in parallel

(6) For the same table select and update

Write an efficient query

Reasons for low query efficiency:

(1) The application gets more data than needed

(2) MySQL server analyzes more rows than needed

How to refactor the query:

(1) The tradeoff between complex queries and multiple queries. It is sometimes wrong to do as much as possible with as few queries as possible.

(2) Shorten the query, prevent the query for a long time to occupy the table

(3) Decomposition joint

Consider the following optimization methods when writing queries:

1. Optimizing Joins

(1) On or using a column with an index

(2) GROUP by or order by references only one table column

2. Optimize count ()

(1) Always use COUNT (*)

(2) Statistics on small portions of the index

(3) Save statistical results

3. Optimize limit and offset

Offset on the overlay index

4. Refine the subquery

Use joins instead of in, EXISTS, not EXISTS whenever possible

5. Optimize the Union

(1) Always use UNION ALL

(2) Push to union under conditions

6. Optimize GROUP BY and distinct

(1) Index optimization

(2) Order by null skip automatic sorting

The following are common query optimization tips:

MySQL Query performance optimization