温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2021-10-25 16:17:06 来源:亿速云 阅读:189 作者:柒染 栏目:MySQL数据库

这篇文章将为大家详细讲解有关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的区别是什么就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI