The Explain analysis
Usage: Use Navicat tools
Once the SQL is written, click EXPLAIN to use EXPLAIN + SQL directly
Explain is a command used to analyze the SQL execution plan. Application Scenario If the execution of an SQL query is slow, you can use Explain to analyze whether to move the index. After an SQL query is modified, you can use Explain to check whether it meets the expectation
The Explain indicators
Indicator Column Name Description ID Query id
Select_type Query type: displays whether the row is a simple or complex query
Table Table involved in the query
This column is displayed only when the partition keyword is used. For non-partitioned tables, this value is NULL
Type Indicates the type of the query table
Possible_keys Possible index
Key Indicates the selected index
Key_len Selected index length: Generally used to determine how many columns are selected in the federated index
Rows is the estimated number of rows that need to be scanned. For InnoDB, this value is the estimate
Filtered Percentage of rows filtered by criteria
Ref compares the column with the index
Extra Additional Information
The type of the value
Type value interpretation
The System query object table has only one row of data and can only be used for MyISAM and Memory engine tables, which is the best case scenario
Const returns at most one result based on a primary key or unique index query
The eQ_REF table joins and scans based on primary keys or non-null unique indexes
Ref is an equivalent query based on a normal index, or an equivalent join between tables
Fulltext Fulltext search
Ref_or_null The table connection type is REF, but the scanned index column may contain NULL values
Index_merge utilizes multiple indexes
Unique_subquery uses a unique index in the subquery
The index_subQuery subquery uses normal indexes
Range uses indexes for range queries
Index Full index scan
ALL Indicates the full table scan
Possible_keys with key
Possible_keys focuses on key, which is the index chosen by practical queries, while Possible_keys is used to match key to predict the “usage” of the union index. If possible_keys has length 3 and key has length 2, it can be inferred that only the first two are used in the joint index.
Extra is related to index coverage and sorting
Extra common value explanation examples
Explain SELECT * from T1 order by create_time; explain select * from T1 order by create_time;
Explain select * from T1 GROUP BY create_time; select * from T1 GROUP BY create_time; select * from T1 GROUP BY create_time;
Using index explain select a from t1 where a=111; Explain select * from T1 where create_time= ‘2019-06-18 14:38:24’;
Explain select * from T1 WHERE 1<0;
Explain select * from T1 straight_join T2 on ASSOCIATED fields of the driven table in an associated query without index EXPLAIN SELECT * from T1 straight_join T2 on (t1.create_time=t2.create_time);
Explain select * from T1 where a >900 and a like “%9”;
Select Tables Optimized Away Use aggregation functions (Max, min) to access explain Select Max (a) from T1;
Joint index
Federated indexes easily implement the —- leftmost matching principle
The joint index has unique advantages in improving index utilization, quickening grouping and sorting, etc. In practice, the joint index is generally due to the single column index
Question:
What does a federated index look like? How do you retrieve it when you query it? Index (a,b,c); b = 2; c = 3;
What does a federated index look like?
It’s still a B+ tree, it’s not going to be a multi-tree because of the joint index. The index node will store multiple columns, for example, the single-column index node will store name, name… [[name,age],[name,age]…
What happens when you insert a single piece of data into a federated index?
The process of inserting data is actually accompanied by the maintenance of the index (i.e. maintaining the primary key index and maintaining the joint index). When inserting a piece of data, the index comparison will be carried out, comparing the name and then the age. In other words, the data inserted along the index comparison will be in the order of the joint index.
Indexes speed up sorting
If the order by rule is the same as the order by rule, the index can be sorted faster, because there is no sorting at all
The maintenance of the order is spread over each increment, deletion, and query directly using the maintained index sort
Left-most matching query: WHERE conditional query
For the joint index index(name, age), the index is invalid when age filtering is applied. Why? When we maintain the joint index, that is, when we insert a data, we sort it according to the index order, the first name condition is sorted first, and age is not sorted separately. Age is sorted based on name, so the index cannot be used when age is used alone. Mysql selects full table scan instead of index query when parsing SQL where does not comply with the “leftmost matching rule” of the joint index.
A federated index can be used when the fields of a WHERE condition meet the leftmost matching rule
Assuming the joint index index (a, b, c) let’s look at a few leftmost matching cases: wherea, b, and C all match wherea, B (only matches a, B) wherea, C (only matches a) Whereb, C (only matches a) Whereb, C does not match a. What is the query process for C?
If the WHERE condition can go to the index, it can judge which direction the lower level should go to from the upper level. Otherwise, it can only go into the internal data of nodes one by one for comparison. Firstly, all nodes that meet the condition A are screened out, and then these nodes are compared with the condition C one by one.
A range query invalidates the following fields wherea = 1, b > 2, c = 3 (matches only a, b). When using a range query, we can’t use the sorting of subsequent fields in the joint index
Leftmost matching rule: ORDER BY
When the condition of ORDER BY is the same as that of the union index sort, the query can be directly queried without sorting, because the data is already shot in the index ORDER when the data is inserted.
What happens if index sort is not utilized, or cannot be utilized? File sort uses external sort (resort in memory or on disk)
When does an ORDER BY index sort fail? For example, index(name,age) cannot be used for index sort: Order by age,name order by name desc,age ASC Because the index is sorted by name and age, there is no rule to sort by age and name, so it needs to be resorted
If the collation of fields is inconsistent, maintain the collation rules of the index. If you use an inconsistent collation method and the index collation method do not match, you need to resort the fields
Even if the index sort is sequential, but when the query is in reverse order, can also use the index sort, why? Because the reverse order is the inverse of the order, so you only need to get the order, you can take the inverse
If you want to sort by index, the order by must conform to the left-most prefix rule.
Index (a,b,c) where a = 1 order by b,c Where is the filter before the result is returned. When where is filtered, the result is actually sorted according to three fields. At this time, order by is the sorted result.
But when a range query is used in WHERE, order by will not be able to use index sort
Left-most matching query: GROUP BY
Group by can be viewed as two steps: sort first, merge so that it is basically the same as the order by, and merge directly
Usage scenarios for federated indexes
Scenario 1: Multi-condition query improves utilization
SQL > create index(a,b,c); SQL > create index(a,b, C);
Rules to be aware of: Index (a,b, C) where a, B,c, and B, A, C can all be indexed (a, B, C). B: When creating an index, put the highly differentiated fields in the first place. The less differentiated fields may contain several nodes, so multiple nodes may be read. The more differentiated fields can reduce the number of second queries
Scenario 2: Avoid returning tables
Walk the auxiliary index and back to the table, they are more than one of the primary key index scans When walking auxiliary index for less than check out all the columns will be back to the table, so if conditions allow, can build a cover of column joint index, not only the joint index can use index covers, as long as the auxiliary index field can satisfy the query list.
The actual development of index coverage is less, more back to the table (go to the secondary index, in fact, also improve part of the efficiency)
Scenario 3: Index sort
When you create a syndicated index index(a,b,c), the order will be maintained every time you add or delete the index. This is equivalent to creating a sequential index based on a,b, and C (left-most prefix matching).
Where +order by is a special case. Index sort can also be used when the condition where + Order BY meets the leftmost matching rule
Scenario 4: COUNT Statistics
COUNT can also use the upper index, as far as possible in the COUNT index
The joint index failure scenario does not meet the leftmost matching principle and uses range query
The query optimizer can actually help us optimize where condition ordering and choose to use execution plans
SQL optimization in one sentence:
SQL optimization allows the query optimizer to select a matching execution plan based on the programmer’s hospital, ultimately reducing the IO generated in the query
When reviewing, try to pay attention to the following knowledge nodes:
Int (11); varchar(255); Choosing between Database paradigm and anti-paradigm Simple query, associative query, subquery, GROUP BY, HAVING, ORDER BY, LIMIT, GROUP BY, ORDER BY For example, now(), what is an index, what is the data structure of the index (only consider InnoDB), the principle of index speed query, clustered index and non-clustered index concept, the advantages and advantages of index, how to use Explain to analyze the execution plan, slow query log index failure scenarios (most left),! % =, LIKE… , column function calculation, etc.) SQL rewriting scenarios and strategies
This article is part of the “Gold Nuggets For Free!” Event, click to view details of the event