|
const： 确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快
- mysql> explain select * from test where id =1 ;
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- 1 row in set (0.00 sec)
eq_ref： 对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key
- mysql> explain select * from test,test2 where test.com_key=test2.com_key;
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
- | 1 | SIMPLE | test2 | ALL | IDX(com_key) | NULL | NULL | NULL | 67993 | NULL |
- | 1 | SIMPLE | test | eq_ref | IDX(com_key) | IDX(com_key) | 194 | test.test2.com_key | 1 | NULL |
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
ref： 对于来自前面的表的行组合，所有有匹配索引值的行都从这张表中读取,如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref （编辑：西安站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
