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;