MySQL :: MySQL 8.4 参考手册

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.3 优化和索引 / 10.3.5 列索引

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.3.5 列索引10.3.5 Column Indexes

最常见的索引类型涉及单列，在数据结构中存储该列值的副本，从而可以快速查找具有相应列值的行。B 树数据结构使索引能够快速找到特定值、一组值或一系列值，对应于 WHERE 子句中的操作符，如 =、>、≤、BETWEEN、IN 等。The most common type of index involves a single column, storing copies of the values from that column in a data structure, allowing fast lookups for the rows with the corresponding column values. The B-tree data structure lets the index quickly find a specific value, a set of values, or a range of values, corresponding to operators such as =, >, ≤, BETWEEN, IN, and so on, in a WHERE clause.

The maximum number of indexes per table and the maximum index length is defined per storage engine. See 第 17 章，InnoDB 存储引擎，以及第 18 章，替代存储引擎所有存储引擎每个表至少支持 16 个索引，总索引长度至少为 256 字节。大多数存储引擎的限制更高。The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 17, The InnoDB Storage Engine, and Chapter 18, Alternative Storage Engines. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.

有关列索引的更多信息，请参见第 15.1.15 节，“CREATE INDEX 语句”。For additional information about column indexes, see Section 15.1.15, “CREATE INDEX Statement”.

索引前缀Index Prefixes

使用索引规范中的 col_name(N) 语法针对字符串列，你可以创建一个仅使用该列前 N 个字符的索引。以这种方式只索引列值的前缀可以使索引文件显著更小。当你索引 BLOB 或 TEXT 列时，你必须为索引指定前缀长度。例如：With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. For example:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，前缀长度可以达到 767 字节。对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，前缀长度限制为 3072 字节。对于 MyISAM 表，前缀长度限制为 1000 字节。Prefixes can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. For MyISAM tables, the prefix length limit is 1000 bytes.

Note

前缀限制是以字节为单位测量的，而在 CREATE TABLE、ALTER TABLE 和 CREATE INDEX 语句中，前缀长度对非二进制字符串类型（CHAR、VARCHAR、TEXT）解释为字符数，而对二进制字符串类型（BINARY、VARBINARY、BLOB）解释为字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑这一点。Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行以查找可能的匹配项。If a search term exceeds the index prefix length, the index is used to exclude non-matching rows, and the remaining rows are examined for possible matches.

有关索引前缀的其他信息，请参阅第 15.1.15 节，“CREATE INDEX 语句”。For additional information about index prefixes, see Section 15.1.15, “CREATE INDEX Statement”.

FULLTEXT 索引FULLTEXT Indexes

FULLTEXT 索引用于全文搜索。只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且仅支持 CHAR、VARCHAR 和 TEXT 列。索引始终针对整个列进行，不支持列前缀索引。有关详细信息，请参阅第 14.9 节，“全文搜索函数”。FULLTEXT indexes are used for full-text searches. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes and only for CHAR, VARCHAR, and TEXT columns. Indexing always takes place over the entire column and column prefix indexing is not supported. For details, see Section 14.9, “Full-Text Search Functions”.

优化应用于针对单个InnoDB表的某些类型的FULLTEXT查询。具有以下特征的查询效率特别高：Optimizations are applied to certain kinds of FULLTEXT queries against single InnoDB tables. Queries with these characteristics are particularly efficient:

仅返回文档 ID，或返回文档 ID 和搜索排名的FULLTEXT查询。FULLTEXT queries that only return the document ID, or the document ID and the search rank.
将匹配行按得分降序排序并应用LIMIT子句以获取前 N 个匹配行的FULLTEXT查询。要应用此优化，查询中不能有WHERE子句，并且只能有一个按降序排列的ORDER BY子句。FULLTEXT queries that sort the matching rows in descending order of score and apply a LIMIT clause to take the top N matching rows. For this optimization to apply, there must be no WHERE clauses and only a single ORDER BY clause in descending order.
FULLTEXT 查询仅检索与搜索词匹配的行的 COUNT(*) 值，没有额外的 WHERE 子句。将 WHERE 子句写成 WHERE MATCH(text) AGAINST ('other_text')，不使用任何 > 0 比较运算符。FULLTEXT queries that retrieve only the COUNT(*) value of rows matching a search term, with no additional WHERE clauses. Code the WHERE clause as WHERE MATCH(text) AGAINST ('other_text'), without any > 0 comparison operator.

对于包含全文表达式的查询，MySQL 会在查询执行的优化阶段评估这些表达式。优化器不仅仅是查看全文表达式并做出估计，而是在制定执行计划的过程中实际评估它们。For queries that contain full-text expressions, MySQL evaluates those expressions during the optimization phase of query execution. The optimizer does not just look at full-text expressions and make estimates, it actually evaluates them in the process of developing an execution plan.

这种行为的一个含义是，EXPLAIN 对全文本查询通常比对在优化阶段不进行表达式评估的非全文本查询要慢。An implication of this behavior is that EXPLAIN for full-text queries is typically slower than for non-full-text queries for which no expression evaluation occurs during the optimization phase.

EXPLAIN 对全文本查询可能在 Extra 列中显示 Select tables optimized away，这是由于匹配发生在优化阶段；在这种情况下，后续执行期间不需要访问表。EXPLAIN for full-text queries may show Select tables optimized away in the Extra column due to matching occurring during optimization; in this case, no table access need occur during later execution.

空间索引Spatial Indexes

您可以在空间数据类型上创建索引。MyISAM 和 InnoDB 支持空间类型的 R 树索引。其他存储引擎使用 B 树对空间类型进行索引（ARCHIVE 除外，它不支持空间类型索引）。You can create indexes on spatial data types. MyISAM and InnoDB support R-tree indexes on spatial types. Other storage engines use B-trees for indexing spatial types (except for ARCHIVE, which does not support spatial type indexing).

MEMORY 存储引擎中的索引Indexes in the MEMORY Storage Engine

MEMORY 存储引擎默认使用 HASH 索引，但也支持 BTREE 索引。The MEMORY storage engine uses HASH indexes by default, but also supports BTREE indexes.

首页上一级下一节