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;

关于oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的区别是什么就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工

【AD】炭云:36元/年/1GB内存/20GB SSD空间/500GB流量/5Gbps端口/KVM/香港/国际线路LUMEN