我有一个具有这种结构的表:
mysql> describe user_groups;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | MUL | NULL | auto_increment |
| user_id | int(11) unsigned | NO | PRI | NULL | |
| group_id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+----------------+
该表的主键是:PRIMARY(user_id, group_id) 我有一个简单的查询:
mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | UserGroup | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
它有using where;
,根据它有额外的磁盘搜索,我知道using where;
概念,而且我也知道这并不可怕,但从概念上我想知道为什么这个查询有using where;
,虽然条件直接在主键上并且它没有顺序或任何导致using where;
. 我该如何解决这个问题using where;
?
请参阅文档。
简而言之,您的查询按预期工作并且没有执行额外的磁盘搜索。