oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的区别是什么

开发技术作者：柒染 2024-04-02 14:50:01

这篇文章将为大家详细讲解有关oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的区别是什么，文章内容质量较高，因此小编分享给大家...

这篇文章将为大家详细讲解有关oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的区别是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

关于oracle index unique scan/index range scan和mysql range/const/ref/eq_ref type的区别

关于ORACLE index unique scan和index range scan区别在于是否索引是唯一的，如果=操作谓词有唯一索引则使用unique scan否则则使用range scan
但是这种定律视乎在MYSQL中不在成立

如下执行
kkkm2 id为主键

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)

我们发现他使用了type const这个代表是查询一条记录并且进行了转换为了常量
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '2' AS `id`,'gaopeng2' AS `name2` from `test`.`kkkm2` where 1 |
+-------+------+-------------------------------------------------------------------------------------+
确实如此

但是如果我们进行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)

这里问题来了，为什么我明明是主键为什么执行计划是type是range呢？ORACLE在这里肯定是INDEX UNIQUE SCAN，
但是MYSQL这里使用range。
给人的感觉eq_ref视乎是更合适的type，唯一扫描嘛，但是看看文档解释如下：
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;

这里 MySQL can use an eq_ref join to process ref_table明确说了eq_ref是用于join的，对于单表不适用
他用在被驱动表的连接字段有唯一索引的情况下。

而ref呢，实际上他也是用于连接用在被驱动表是非唯一索引的情况，并且适用于单表谓词非唯一的情况如下：
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;

如果只考虑 = 操作：
感觉mysql的ref 和 oracle的index range scan类似，不管单表或者jion都可以使用，
适用于索引是非唯一的情况。
但是mysql的eq_ref 和oracle的index unique scan 并不同，因为eq_ref只会用在join的
情况下并且被驱动表是唯一的情况下，在单表谓词查询使用唯一索引的情况eq_ref并不会出现，
出现的是type const或者type range

如果> < 等范围操作，出现的一定是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 =, <>, >, >=, <,
<=, IS NULL, <=>, BETWEEN, or IN () operators:
SELECT * FROM tbl_name
WHERE key_column= 10;

SELECT * FROM tbl_name
WHERE key_columnBETWEEN 10 and 20;