oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區(qū)別是什么

這篇文章將為大家詳細(xì)講解有關(guān)oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區(qū)別是什么，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

關(guān)于oracle index unique scan/index range scan和mysql range/const/ref/eq_ref type的區(qū)別


   關(guān)于ORACLE index unique scan和index range scan區(qū)別在于是否索引是唯一的，如果=操作謂詞有唯一索引則使用unique scan否則則使用range scan
但是這種定律視乎在MYSQL中不在成立

如下執(zhí)行
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)


我們發(fā)現(xiàn)他使用了type const這個代表是查詢一條記錄并且進(jìn)行了轉(zhuǎn)換為了常量
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message                                                                             |
+-------+------+-------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '2' AS `id`,'gaopeng2' AS `name2` from `test`.`kkkm2` where 1 |
+-------+------+-------------------------------------------------------------------------------------+
確實如此


但是如果我們進(jìn)行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)


這里問題來了，為什么我明明是主鍵為什么執(zhí)行計劃是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的，對于單表不適用
他用在被驅(qū)動表的連接字段有唯一索引的情況下。






而ref呢，實際上他也是用于連接用在被驅(qū)動表是非唯一索引的情況，并且適用于單表謂詞非唯一的情況  如下：
   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的
情況下并且被驅(qū)動表是唯一的情況下，在單表謂詞查詢使用唯一索引的情況eq_ref并不會出現(xiàn)，
出現(xiàn)的是type const或者type range


如果> < 等范圍操作，出現(xiàn)的一定是type range了，這個和ORACLE一樣一旦唯一鍵出現(xiàn)了范圍
條件出現(xiàn)的一定是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;

關(guān)于oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區(qū)別是什么就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。