为什么这个查询使用 where 而不是索引? [英] Why is this query using where instead of index?

问题描述

EXPLAIN EXTENDED SELECT  `board` . * 
FROM  `board` 
WHERE  `board`.`category_id` =  '5'
AND  `board`.`board_id` =  '0'
AND  `board`.`display` =  '1'
ORDER BY  `board`.`order` ASC

以上查询的输出为

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  board   ref category_id_2   category_id_2   9   const,const,const   4   100.00  Using where

我对此有点困惑，因为我有一个索引，其中包含我使用的列的顺序与它们在查询中使用的顺序相同...:

I'm a little confused by this because I have an index that contains the columns that I'm using in the same order they're used in the query...:

category_id_2   BTREE   No  No 
category_id 33  A       
    board_id    33  A   
    display 33  A   
    order   66  A   

推荐答案

EXPLAIN 的输出有时会产生误导.

The output of EXPLAIN can sometimes be misleading.

例如，filesort 与文件无关，using where 并不意味着您使用了 WHERE 子句，而 using index 可以在没有定义单个索引的情况下显示在表上.

For instance, filesort has nothing to do with files, using where does not mean you are using a WHERE clause, and using index can show up on the tables without a single index defined.

Using where 只是意味着表上有一些限制子句(WHERE 或 ON)，并不是所有的记录都会被返回.请注意，LIMIT 不算作限制子句(尽管可以).

Using where just means there is some restricting clause on the table (WHERE or ON), and not all record will be returned. Note that LIMIT does not count as a restricting clause (though it can be).

Using index 表示所有信息都从索引返回，不查找表中的记录.这只有在查询所需的所有字段都被索引覆盖时才有可能.

Using index means that all information is returned from the index, without seeking the records in the table. This is only possible if all fields required by the query are covered by the index.

既然你选择了*，这是不可能的.category_id、board_id、display 和 order 以外的字段不在索引范围内，应该查找.

Since you are selecting *, this is impossible. Fields other than category_id, board_id, display and order are not covered by the index and should be looked up.

这篇关于为什么这个查询使用 where 而不是索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！