尽管存在INDEX，但MySQL EXPLAIN显示ALL类型 [英] MySQL EXPLAIN showing ALL type although INDEX exists

问题描述

我使用EXPLAIN

EXPLAIN SELECT form.id           AS `Reference No.`, 
               department.name   AS `Department`, 
               section.name      AS `Section` 
FROM   form 
       JOIN department 
         ON form.deptid = department.id 
       JOIN section 
         ON form.sectid = section.id 

第一行中键入ALL是否表示会出现性能问题?

Does type ALL in the first row indicate that there is going to be performance issues?

+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+
| id | select_type |   table    |  type  | possible_keys |   key   | key_len |         ref          | rows |
+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+
|  1 | SIMPLE      | form       | ALL    | deptid,sectid |         |         |                      |  779 |
|  1 | SIMPLE      | department | eq_ref | PRIMARY       | PRIMARY |       4 | wfs-test.form.deptid |    1 |
|  1 | SIMPLE      | section    | eq_ref | PRIMARY       | PRIMARY |       4 | wfs-test.form.sectid |    1 |
+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+

推荐答案

MySQL从表form获取数据时，没有理由使用索引.由于查询没有任何WHERE子句，因此表from中的所有行都可能包含在最终结果集中.而且，因为没有ORDER BY子句，所以行的任何顺序都足够好.这就是为什么MySQL直接从表中获取行而不查询任何索引的原因.

There is no reason for MySQL to use the index when it gets data from table form. Because the query doesn't have any WHERE clause, potentially all the rows from table from will be included in the final result set. More, because there is no ORDER BY clause, any order of the rows is good enough. This is why MySQL gets the rows directly from the table, without consulting any index.

如果存在包含(某些)与WHERE条件有关的字段的索引(并且按正确顺序排列的字段是最左侧的列)，则添加WHERE条件可能会触发索引的使用.包括在索引中.)

Adding a WHERE condition could trigger the use of an index if an index that contains (some of) the fields involved in the WHERE conditions exist (and the fields, put in the correct order, are the leftmost columns included in the index).

当从表form中选择的所有字段都包含在索引中时，在表form中的字段上添加ORDER BY子句(不包含WHERE)也可能触发索引的使用.它将类型从ALL更改为index，这意味着它将对索引(而不是数据行)进行完全扫描以获取所需的数据.尽管这仍然是完整扫描，但full index scan的运行速度通常比full table scan快，因为从存储中加载和解析的数据较少(索引通常小于表数据).

Adding an ORDER BY clause (without WHERE) on fields from table form could also trigger the use of an index when all the fields selected from table form are contained in the index. It will change the type from ALL to index which means it will do a full scan of the index instead of the data rows to get the data it needs. While this is still a full scan, a full index scan usually runs faster than a full table scan because less data is loaded from the storage and parsed (the index is usually smaller than the table data).

有关此问题的更多信息，请参见

More information about this can be found in the MySQL documentation.

整个部分"8.2.1优化SELECT语句" 值得一读，以更好地了解如何编写更快的查询.

The entire section "8.2.1 Optimizing SELECT Statements" is worth reading to better understand how to write faster queries.

这篇关于尽管存在INDEX，但MySQL EXPLAIN显示ALL类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！