MySQL Query Statement Optimization idea

The optimization ideas and principles of query statement are mainly mentioned in the following aspects:
1. Optimize the query that needs to be optimized;
2. Positioning the Optimization object performance bottleneck;
3. Clear objectives for optimization;
4. Starting from Explain;
5. Multi-Use profile
6. Always drive large result sets with small result sets;
7. Complete the sorting in the index as much as possible;
8. Only take out the columns you need;
9. Use only the most effective filter conditions;
10. Avoid complicated joins and subqueries as much as possible

About explain

Usage: Explain select * from Tables1 where 1 ...

Let's take a look at the explanations of the various information presented to us in the MySQL explain feature:
Id:query the serial number of the query in the execution plan selected by Optimizer;
Select_type: The type of query used, mainly with the following types of queries
◇dependent subquery: The first select in the inner layer of a subquery, depends on the result set of the external query;
◇dependent Union: Union in the subquery, and all after the second select from the UnionSELECT, which also relies on the result set of the external query;
◇primary: The outermost query in the subquery, note is not the primary key query;
◇simple: A query other than a subquery or union;
◇subquery: Subquery The first select of the inner layer query, the result is not dependent on the external query result set;
◇uncacheable subquery: Subquery that the result set cannot be cached;
All select after the second select starts in the ◇union:union statement, the first select is primary
The results of merging in ◇union Result:union;
Table: Displays the names of the tables in the database accessed by this step;
Type: tells us how to access the table, mainly contains the following set of types;
◇all: Full table scan
◇const: Read constant, and at most only one record match, because is a constant, so actually only need to read once;
◇eq_ref: There will be at most one matching result, usually accessed through a primary key or a unique key index;
◇fulltext:
◇index: Full index scan;
◇index_merge: Two (or more) indexes are used in the query, and then the table data is read after the index results are merge.
◇index_subquery: The returned result field combination in a subquery is an index (or combination of indexes), but not a primary key or a unique index;
◇rang: Index range scan;
The query is referenced by the driver table index in the ◇ref:join statement;
◇ref_or_null: The only difference from ref is a query that adds a null value in addition to the index reference query;
◇system: System table, only one row of data in the table;
◇unique_subquery: The returned result field combination in a subquery is a primary KEY or a unique constraint;
Possible_keys: The index that the query can take advantage of. If no index is available, it will be displayed as NULL, which is important for tuning the index at the time of optimization;
Key:mysql Query Optimizer The index selected from the Possible_keys;
Key_len: The index key length that is selected to use the index;
REF: Lists whether a constant (const) or a field of a table (if it is a join) is filtered (via key)
Of
Rows:mysql Query Optimizer The number of result set records that are estimated by the statistical information collected by the system;
Extra: The additional details that are implemented in each step of the query are likely to be the following:
◇distinct: Find the Distinct value, so when MySQL finds the first match, it stops the query for that value and turns it into a query for the other values later;
◇full scan on null key: An optimization method in subqueries, mainly encountered in the use of NULL values that cannot be accessed through the index;
◇impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer The statistical information collected to determine the impossibility of the existence of results;
The ◇no tables:query statement uses the from DUAL or does not contain any FROM clause;
◇not exists: In some left connections, MySQL query Optimizer uses the optimization method to change the composition of the original query, which can partially reduce the number of data accesses;
◇range checked for each record (index MAP:N): As described in the official MySQL manual, when MySQL Query Optimizer does not find a good index to use, if you find that the column values from the preceding table are known, Some indexes may be used. For each row combination of the preceding table, MySQL checks to see if theUse the range or Index_merge access method to request a line.
◇select tables Optimized away: When we use some aggregate functions to access a field that exists in the index, MySQL Query Optimizer navigates directly to the desired data row through the index to complete the
Polling Of course, the premise is that you cannot have GROUP by operations in Query. When using min () or Max ();
◇using Filesort: When our query contains an order by operation, and the index cannot be used to complete the sort operation, MySQL Query Optimizer has to choose the appropriate sorting algorithm to implement.
◇using Index: The required data only need to be in the index can be all obtained without the need to fetch data in the table;
◇using index for group-by: As with data access and using index, the required data needs only to read the index, and when a group BY or DISTINCT clause is used in Query, if the grouping field is also in the index, extra The information will be the Using index for group-by;
◇using Temporary: When MySQL must use temporary tables in certain operations, a using temporary appears in the Extra information. It is mostly common in operations such as GROUP by and ORDER by.
◇using Where: If we are not reading all the data of the table, or we can get all the required data by simply indexing, the Using where information will appear;
◇using where with pushed condition: This is a message that appears only in the Ndbcluster storage engine, and it needs to be used by turning on the Condition pushdown optimization feature. Control parameters
For Engine_conditioN_pushdown

About profiling

Usage:

Open:

Set profiling=1;//Open

SELECT * from tables1 where 1;//random query

Show profiles;//summary information for print query statements

As follows

----------+------------+------------------------------------------------------------+
| query_id | Duration | Query |
+----------+------------+------------------------------------------------------------+
| 1 | 0.00183100 | Show Databases |
| 2 | 0.00007000 | SELECT DATABASE () |
| 3 | 0.00099300 | DESC Test |
| 4 | 0.00048800 | Show Tables |
| 5 | 0.00430400 | Desc test_profiling |
| 6 | 1.90115800 | Select Status,count (*) from test_profiling GROUP by status |
+----------+------------+------------------------------------------------------------

Show profile CPU, block IO for query 4;//show details of ID4 above show profiles content

More features, still need to explore, the above is only a

　　

MySQL Query Statement Optimization idea