|
ref可以用于使用=或<=>操作符的带索引的列
- mysql> explain select * from test ,test2 where test.bnet_id=test2.aid;
- +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
- | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 68505 | Using where |
- | 1 | SIMPLE | test2 | ref | idx_aid | idx_aid | 5 | test.test.bnet_id | 34266 | Using index condition |
- +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
test表bnet_id不是索引，test2表aid为索引列
ref_or_null： 类似ref，但是添加了可以专门搜索null值的行
- mysql> explain select * from test where bnet_id=1 or bnet_id is null;
- +----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
- | 1 | SIMPLE | test | ref_or_null | idx_bnet | idx_bnet | 9 | const | 2 | Using index condition |
- +----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
前提为bnet_id列为索引，且bnet_id列有null值
index_merge： 该访问类型使用了索引合并优化方法，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素
- mysql> explain select * from test where id = 1 or bnet_id = 1;
- +----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
- | 1 | SIMPLE | test | index_merge | PRIMARY,idx_bnet | PRIMARY,idx_bnet | 8,9 | NULL | 2 | Using union(PRIMARY,idx_bnet); Using where |
- +----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
前提条件为id列和bnet_id列都有单列索引。如果出现index_merge，并且这类SQL后期使用较频繁，可以考虑把单列索引换为组合索引，这样效率更高
range： 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL （编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
