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的區別是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。