1,const

Indicates that the execution step returns at most one row of data. const usually appears in the equivalent query of the primary key or unique index, such as the equivalent query of the primary key id of the t table:

It can be considered that it is as fast as a rocket to locate a record by comparing the equivalence between the primary key or the unique secondary index column and the constant. Therefore, they define this access method to locate a record by the primary key or the unique secondary index column as: const, which means constant level, with negligible cost. However, this const access method is only valid when the primary key column or unique secondary index column is compared with a constant.

If the primary key or the only secondary index is composed of multiple columns, each column in the index needs to be compared with the constant equivalently, so the const access method is effective (this is because only when all columns in the index adopt the equivalently comparison can a unique record be located). For example, if the primary key consists of (a,b,c), const will appear in the execution plan type column only when "where a= and b= and c = * *".

In addition, for a unique secondary index, querying the column as a NULL value is a special case, such as this:

SELECT * FROM single_table WHERE key2 IS NULL;

Because the unique secondary index column does not limit the number of NULL values, the above statement may access multiple records, that is to say, the upper statement cannot be executed using the const access method.

What's more, we can see in the Alibaba java development specification manual:

"consts means that there is at most one matching row (primary key or unique index) in a single table, and the data can be read in the optimization stage"

The data will be read in the optimization phase, as shown in the following example:

mysql> explain select num,created_time from t_operater_record where id=493490480924;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+

id is the primary key. There is no 493490480924 value. There are no matching rows in const table shown in extra. Obviously, the data has been read here.

2,ref

Sometimes we compare the equivalence between a common secondary index column and a constant, such as this:

mysql> explain select * from t_operater_record where updated_time='2010-10-23 07:17:27';
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_operater_record | NULL       | ref  | i_updated_time | i_updated_time | 5       | const |    3 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

MySQL compares this search condition as two level index columns and constant equivalence. The access method of using two level index to execute query is called ref.

For the ordinary secondary index, it is possible to match multiple consecutive records after the equivalence comparison of index columns, instead of only matching one record at most like the primary key or the unique secondary index. Therefore, the ref access method is less than const, but the efficiency is still high when the number of matching records is less. The efficiency of this visit list is similar to that of high-speed rail.

In addition, we need to pay attention to the following two situations:

a. When the value of the secondary index column is NULL: whether it is a common secondary index or a unique secondary index, the number of index columns containing NULL values is not limited. Therefore, we can only use the access method of ref instead of const when we use the search condition of key IS NULL.

b. For a multi column two-level index, as long as the left most continuous index column is the equivalent comparison with a constant, the ref access method may be used, such as the following queries:

SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

However, if the left most consecutive index columns are not all equivalent comparisons, their access methods cannot be called ref, for example:

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

3,eq_ref

eq_ref type generally means that when a table is associated, the associated column on the associated table walks through the primary key or unique index.
For example, table ABCD associate lock_test table. The associated columns are the primary key columns of the two tables:

When the above SQL is executed, the jiang table is the driving table, lock_test is the driven table, the associated column of the driven table is the primary key id, and the type is eq_ref.

MySQL calls the query execution method that uses the primary key value or the value of the unique secondary index column to perform the equivalent search on the driven table in the connection query as: eq_ref.

4,Ref_or_null

For example, execute the following statement:

select * from lock_test where num=110 or num is null;

Indicates that the index is gone (there is an index on the num column), but the null value is also accessed. REF may appear when using a secondary index equivalent query and querying the null value of the secondary index at the same time_ Or_ null.

5,range:

const, ref, ref introduced earlier_ Or_ Null is only possible when the index column is compared with a constant. But sometimes the search conditions we face are more complex than these, such as the following query:

mysql> explain select * from t_operater_record where updated_time between '2010-10-23 07:17:27' and '2010-12-23 07:17:27';
mysql> explain select * from t_operater_record where updated_time='2010-10-23 07:17:27' or updated_time='2004-05-24 20:36:50';

The above two queries are range queries on index columns, and both queries can access data in the way of secondary index + return table. MySQL calls this access method of range matching by index: range. (the index in range matching with index mentioned here can be clustered index or secondary index).

6,index

Assumption table single_table has union index (key_part1, key_part2, key_part3), see the following query:

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

Because of key_part2 is not a union index idx_key_part is the leftmost index column, so we can't use ref or range access method to execute this statement. But this query meets the following two conditions:

Its query list has only three columns: key_part1, key_part2, key_part3, index idx_key_part contains these three columns.
Only key in search criteria_ Part2 column. This column is also included in the index idx_key_part.

That is to say, we can traverse IDX directly_ key_ Compare the records of the leaf node of the part index with the key_ Part2 = whether the condition 'ABC' is true, match the key of the successful secondary index record_ part1, key_ part2, key_ The value of the part3 column is added directly to the result set. Because the secondary index record is much smaller than the clustered index record (the clustered index record needs to store all user-defined columns and so-called hidden columns, while the secondary index record only needs to store index columns and primary keys), and this process does not need to return tables, so the cost of directly traversing the secondary index is much smaller than directly traversing the clustered index. MySQL uses this kind of traversal The execution mode of index records of calendar Level 2 is called: index.

For example: t_operater_record table has union index (Num, status, created_ In the following query, type displays index:

mysql> explain select num,created_time from t_operater_record where status in(33,43);
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table             | partitions | type  | possible_keys | key                       | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index | NULL          | i_num_status_created_time | 11      | NULL | 1990233 |    20.00 | Using where; Using index |
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

7,index_merge

Most of the time, MySQL only uses a single secondary index when executing a query. But there are also special cases. In these special cases, multiple secondary indexes may be used in a query. MySQL calls this execution method of using multiple indexes to complete a query as index merge. There are three specific index merge algorithms.

7.1 integration

Intersection means intersection. This means that a query can use multiple secondary indexes to get the intersection of the results from multiple secondary indexes.

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

For example, for the above query, mysql may access the table data with a single key 1 or key 3 index, or it may access the table data with an intersection index consolidation method. The specific method is determined by the estimated cost.

MySQL may only use cross section index consolidation in certain situations:

Situation 1:
The second level index column is the case of equivalent matching. If the secondary index is a union index, then every column in the union index must match equally, and it cannot match only some columns.
For example, the next query may use idx_key1 and idx_key_part these two secondary indexes are used to merge the Intersection indexes:

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

Practical examples:

mysql> show index from t_operater_record;
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_operater_record |          0 | PRIMARY                   |            1 | id           | A         |     1990233 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_updated_time            |            1 | updated_time | A         |     1037127 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_oper                    |            1 | operationer  | A         |      683749 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            1 | num          | A         |       10106 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            2 | status       | A         |      627083 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            3 | created_time | A         |      998627 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and num=7.34 and status=11 and created_time='2004-05-24 20:36:50';;
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys                            | key                                      | key_len | ref  | rows | filtered | Extra                                                                  |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | i_updated_time,i_num_status_created_time | i_num_status_created_time,i_updated_time | 11,5    | NULL |    1 |   100.00 | Using intersect(i_num_status_created_time,i_updated_time); Using where |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+

However, the following two queries can't be used for cross section index consolidation:

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';



mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and num=7.34 and status=11;
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys                            | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_operater_record | NULL       | ref  | i_updated_time,i_num_status_created_time | i_updated_time | 5       | const |    3 |     1.67 | Using where |
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Case 2:
The primary key column can be a range match. For example, the following query may use primary key and idx_key1 performs the operation of cross section index merging:

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';


mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and id>300000;
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | PRIMARY,i_updated_time | i_updated_time,PRIMARY | 13,8    | NULL |    1 |   100.00 | Using intersect(i_updated_time,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

Why is that? All of a sudden, these two rules make people look confused. In fact, for InnoDB's secondary index, records are first sorted by index columns. If it is a joint index, records are sorted by columns. The records of the secondary index are composed of index column + primary key. There may be many records with the same value of the secondary index column. The records with the same value of these index columns are sorted according to the value of the primary key. Therefore, it is important to use Intersection index merging when the secondary index columns are all equal value matching, because only in this case can the result set queried according to the secondary index be sorted according to the primary key value.

The Intersection index merging will intersect the primary key values queried from multiple secondary indexes. If the result sets queried from each secondary index are already sorted according to the primary key, then the Intersection process is easy.

For the above case 2, the records of the secondary index have the primary key value, so you can use the_ The primary key value obtained in key1 is filtered directly by the condition ID > 100, which is so simple.

Of course, case 1 and case 2 mentioned above are only necessary conditions for cross-section index merging, not sufficient conditions.

7.2 union merger

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection is the meaning of intersection, which is applicable to the use of AND connection between search conditions with different indexes; Union is the meaning of Union, which is applicable to the use of OR connection between search conditions with different indexes. Similar to intersection index merging, MySQL may use Union index merging in some specific cases:

Situation 1:
The second level index column is the case of equal value matching. For the joint index, each column in the joint index must be equal value matching, and there cannot be only some matching columns.
For example, the next query may use idx_key1 and idx_key_part these two secondary indexes are used to merge Union indexes:

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');


mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' or (num=7.34 and status=11 and created_time='2004-05-24 20:36:50');
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys                            | key                                      | key_len | ref  | rows | filtered | Extra                                                              |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | i_updated_time,i_num_status_created_time | i_updated_time,i_num_status_created_time | 5,11    | NULL |    6 |   100.00 | Using union(i_updated_time,i_num_status_created_time); Using where |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

However, the following two queries cannot be combined with Union index:

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

Case 2:
The primary key column can be a range match:

mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' or id<100000;
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | PRIMARY,i_updated_time | i_updated_time,PRIMARY | 5,8     | NULL |    4 |   100.00 | Using union(i_updated_time,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Situation 3:
Search criteria merged using the Intersection index.

In fact, this situation is well understood, that is, some parts of the search criteria use the Intersection of the primary key set obtained by the Intersection index combination method and the primary key set obtained by other methods. For example, this query:

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

The optimizer may execute the query in this way:

First from index idx according to search criteria key1 ='a 'and Key3 ='b'_ Key1 and idx_ In Key3, an Intersection index is used to merge to get a set of primary keys.
Then according to the search criteria key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' gets another set of primary keys from the union index.
Union index merging is used to merge the above two primary key sets, and then return the results to the user.

Of course, if the query conditions meet these conditions, Union index consolidation may not be adopted, depending on the mood of the optimizer.

7.3 sort-union

The usage conditions of Union index consolidation are too strict, so it is necessary to ensure that each secondary index column can be used under the condition of equivalent matching. For example, the following query cannot be used for Union index consolidation:

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

This is because according to key1 < 'a' from idx_ The primary key values of the secondary index records obtained in the key1 index are not in good order. According to Key3 > 'Z', idx_ The primary key values of the secondary index records obtained in the Key3 index are not in good order, but the two conditions key1 < 'a' and Key3 > 'Z' make us particularly moved, so we can do this:

First, according to the condition of key1 < 'a', from idx_ Obtain records in the secondary index of key1, and sort them according to the primary key value of the records
Then according to the condition of Key3 > 'Z', the idx_ Obtain records in the secondary index of Key3, and sort them according to the primary key value of the records
Because the above two secondary index primary key values are in order, the rest of the operations are the same as Union index consolidation.

We first sort the primary key values of the secondary index records, and then carry out the sort Union index consolidation. Obviously, this sort Union index consolidation is one step more than the simple Union index consolidation to sort the primary key values of the secondary index records.

Index merge considerations:
1. Using a federated index instead of an Intersection index merge is often the best practice:

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

It is not because of IDX that this query can be executed in the way of cross section index merging_ Key1 and idx_key3 is two separate B + tree indexes. If you make a joint index for these two columns, you can use the joint index directly to get things done. Why do you need to merge indexes.