MYSQL Index Query optimization

Official documents:
Https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows

Type: Connection type the system table has only a single row of const tables with a maximum of one row of matches, common for primary key or unique index comparisons. If you put the primary key in the where list, MySQL can convert the query to a constant eq_ref every time the merge row with the previous table reads only one row in the table, which is the best except system,const, which is characterized by the use of =, and all parts of the index participate in the join and the cable Index ref is a primary key or a non-null unique key if it matches only a few rows at a time, it is the better one, using = or<=>, either a left overlay index or a non-primary key or a non-unique key fulltext full-text search ref_or_null similar to ref, but including null Index_merge indicates that an index merge optimization (including intersection, Union, and intersection) occurred ), but does not include cross-table and full-text indexing. This is more complicated, and the current understanding is to merge the range index scan of a single table (if the cost estimate is better than the normal range) Unique_subquery in the in subquery, which is value in (select ...). Replace the sub-query with the form "select Unique_key_column".    PS: So not necessarily using subqueries in clauses is inefficient! Index_subquery, but replaces the range of the range constant value with a subquery of the form "select Non_unique_key_column" (Index range Scan), the scan of the index starts at a point, returns the row of the matching value, common to the bet Ween,<,>And so on index A. When the query is indexed, that is, all data can be fetched from the index tree (usingindex in extra); B. Full-table scan of data rows from the index in index order (no Usingi Ndex); c. If the using index in the extra is present in conjunction with the using where, the index is used to find the value of the key; d. If it appears separately, read the index instead of the read line, but not  Used to find all traverse the entire table to find a matching row null:mysql decomposition statements during optimization, execution without even accessing table or index result values from good to Bad order: System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range > Index > All
Explain execution plan classification.
From
72576182
Throwing Questions:

Each of the three tables has approximately 5300 data per table.

Do a statistic to be 17S. There is a lot of room for optimization. The simple way is to add indexes. The specific walk index or the combined index. Look at the business situation here it goes

Single index.

Open test: Index three tables first.

Add index: Three tables similar to Order_number for the on field e_id for the where condition.

Try the join notation first


Query time: 0.022S optimized from 17S to 0.022S.

Take a look at the execution plan:

I did not execute the WHERE condition for this SQL. That is, the previously built E_id_index index is not used.

Mainly look at the execution plan under the type comparison

There is also room for optimization. o Table index does not play the role of indexing to the extreme. Now add where to look

　　

Note: The index of O table has become ref:rows from 5411 to 2705.

Ref: If you match only a few rows at a time, that's a good one, using = or <=>, which can be left overlay index or non-primary key or non-unique key
Index  A. When the query is indexed, that is, all data can be retrieved from the index tree (usingindex in extra);
       B. Full-table scan of data rows from the index in index order (no usingindex);
       C. If the using index in the extra is present with the using where, the index is used to find the value of the key;
 D. In the case of a single occurrence, a read index is used instead of a read row, but not used to find

From the time of return, the difference is not big. But from an optimization point of view, the latter is better.  

And look at the difference in exists, a lot of people say in not walking index go full table scan
Inch

EXISTS

o Table indexes refer to the Eid_index no big difference.
Mainly see O1 index from Un_index into Eq_ref
For Un_index's instructions, see official instructions.


Do not translate
By contrast, exists is more capable of indexing than in performance. Here is just a simple test.
It is not possible to fully save the exists to the advantage of in. Need to go to more business environments to discover, practice.

Let's see if I go to the index. Like many people think that performance is poor, look at the difference between the plan is not big. Here only do simple analysis not to do in-depth.

All like

Left principle


Both of these are used to index the difference is not very large. The single index used here. No composite index is used.


And look at the difference between a select * and a single field







And look at the difference between implicit conversions.
Implicit conversions

The normal wording adds a single citation.

This is a direct upgrade from ref EQ_REF to Const Const. When the amount of data is large, the return time is very different.

Therefore, usually write SQL must pay attention to the format. Don't be lazy.

The effect is the same. Personal understanding in addition to the consumption of Io, do not feel where there is a difference. I don't know why a lot of people say * poor performance. But I can't say why.

For a simple summary of SQL optimizations:

1. Avoid field null with NOT null default ' xxx ' padding. Here null value, I did not go to practice little lazy. Personal understanding that this may be related to the data structure of the index itself

2. The equivalent operation can be used in equivalent operation.

3. Business conditions allow the use of a third-party ES SOLR search engine instead of like.

4. Avoid an implicit conversion

5. Add an on field association to an index

6. Try to use the Where to force the index

7. Try to use exists instead of in. In contrast in high readability. exists performance slightly better than in. Not that exists is definitely better than in, and needs to be practiced in more business environments. Self-experience.

8. Avoid using * to reduce IO consumption

9. Field design char varchar nvarch text/int bigint tinyint can be small.

10. The table design uses the physical foreign key as far as possible to use the logical foreign key.

11. There is no demo here because MySQL does not have a with as CTE notation. There are some in MSSQL Oracle. Performance is relatively good. Walk the memory.

12. Make full use of temporal tables, variable tables,

13.UNION All unaion The difference is that a go-heavy one does not go heavy. Look at the business scenario. You don't have to. Re-process each query result. Avoid UNION ALL

14. If the table has too many associations, you can split it by program. Don't write a thief long in SQL. It is not the length of the SQL write or the Diao. Pretending to be guilty.

15.SQL is very powerful, practice a lot.

MYSQL Index Query optimization