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;