Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the difference between oracle index unique scan/index range scan and mysql range/const/ref/eq_ref. The content of the article is of high quality, so Xiaobian shares it with you for reference. I hope you have a certain understanding of relevant knowledge after reading this article.

Oracle index unique scan/index range scan and mysql range/const/ref/eq_ref type

About ORACLE index unique scan and index range scan difference is whether the index is unique, if = operation predicate has a unique index then use unique scan otherwise use range scan

But this law depends on whether it doesn't hold true in MYSQL

performed as follows

kkkm2 id is the primary key

mysql> explain extended select * from kkkm2 where id=2;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

| 1 | SIMPLE | kkkm2 | const | PRIMARY,key_t | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

We found out that he used type const, which means yes, to query a record, and converted it to constant.

mysql> show warnings;

+-------+------+-------------------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+-------------------------------------------------------------------------------------+

| Note | 1003 | /* select#1 */ select '2' AS `id`,'gaopeng2' AS `name2` from `test`.` kkkm2` where 1 |

+-------+------+-------------------------------------------------------------------------------------+

indeed

But if we update,

mysql> explain update kkkm2 set name2='gaopeng1000' where id=1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| 1 | SIMPLE | kkkm2 | range | PRIMARY,key_t | PRIMARY | 4 | const | 1 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

1 row in set (0.03 sec)

Here comes the question, why am I obviously the primary key, why is the execution plan type range? ORACLE is definitely INDEX UNIQUE SCAN here

MYSQL uses range here.

The feeling eq_ref gives depends on the more appropriate type, only scan well, but look at the documentation explained as follows:

eq_ref can be used for indexed columns that are compared using the = operator. The comparison

value can be a constant or an expression that uses columns from tables that are read before this

table. In the following examples, MySQL can use an eq_ref join to process ref_table:

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1;

Here MySQL can use an eq_ref join to process ref_table explicitly states that eq_ref is used for join, not applicable to single tables

It is used when the join field of the driven table has a unique index.

ref, in fact, it is also used to join in the case of non-unique indexes of the driven table, and applies to the case of non-unique predicates of single tables as follows:

All rows with matching index values are read from this table for each combination of rows from the

previous tables. refis used if the join uses only a left most prefix of the key or if the key is not a

PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the

key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the =or operator. In the

following examples, MySQL can use a ref join to process ref_table:

SELECT * FROM ref_tableWHERE key_column=expr;

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1;

If only = operation is considered:

Feel mysql ref and oracle index range scan similar, whether single table or jion can be used,

This applies when the index is not unique.

But eq_ref of mysql is different from index unique scan of oracle because eq_ref is only used in join

eq_ref does not occur in the case of a single-table predicate query using a unique index, and the driven table is unique

Type Const or Type Range

if>

< 等范围操作，出现的一定是type range了，这个和ORACLE一样一旦唯一键出现了范围 条件出现的一定是INDEX RANGE SCAN。 range描述如下： Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type. range scan be used when a key column is compared to a constant using any of the =, , >

, >=,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.