为什么在此说明中不使用任何键? [英] Why are no keys used in this EXPLAIN?

问题描述

我希望这个查询使用键.

I was expecting this query to use a key.

mysql> DESCRIBE TABLE Foo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

mysql> EXPLAIN SELECT id FROM Foo WHERE name='foo';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Foo在name上具有唯一索引，那么为什么SELECT中不使用该索引?

Foo has a unique index on name, so why isn't the index being used in the SELECT?

推荐答案

来自MySQL手册页，标题为

From the MySQL Manual page entitled EXPLAIN Output Format:

在读取 const表后发现不可能的地方(JSON属性: 消息)

Impossible WHERE noticed after reading const tables (JSON property: message)

MySQL已读取所有const(和系统)表，并注意WHERE 子句始终为假.

MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

和const表的定义，摘自常量表和常量表:

and the definition of const tables, from the Page entitled Constants and Constant Tables:

MySQL常量不仅仅是查询中的文字. 它也可以是常量表的内容，该表定义为 如下:

A MySQL constant is something more than a mere literal in the query. It can also be the contents of a constant table, which is defined as follows:

零行或仅一行的表

受WHERE条件限制的表表达式， 包含形式为column = constant的表达式，对于所有 该表的主键的列，或其中任何一个的所有列的列 表格的唯一键(前提是唯一列也是 定义为NOT NULL).

A table expression that is restricted with a WHERE condition, containing expressions of the form column = constant, for all the columns of the table's primary key, or for all the columns of any of the table's unique keys (provided that the unique columns are also defined as NOT NULL).

第二个参考是一页长一半的页面.请参考.

The second reference is a page and half long. Please refer to it.

const

const

const

该表最多有一个匹配行，该行从 查询.由于只有一行，因此该列中的值 该行可以被优化器的其余部分视为常量. const表非常快，因为它们只能读取一次.

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

const 索引到恒定值.在以下查询中，tbl_name可以是 用作const表:

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

SELECT * FROM tbl_name WHERE primary_key = 1;

SELECT * FROM tbl_name WHERE primary_key=1;

选择*从tbl_name中，primary_key_part1 = 1且 primary_key_part2 = 2;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

这篇关于为什么在此说明中不使用任何键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！