8.3.5 Column Indexes

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.

最普通的索引类型包含单列索引，利用数据结构存储对应的值得备份，它可以快速查找到对应的列值。B-tree 数据结构使得索引可以快速查找到具体的一个值、一组值或一定范围的值。相应的操作是在where语句中利用比如, >, ≤, BETWEEN, IN等等操作符

The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 15, The InnoDB Storage Engine, and Chapter 16, 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.

每张表最大的索引数量和最大的索引长度是依赖于各自的存储引擎。详情请见 Chapter 15, The InnoDB Storage Engine, 和 Chapter 16, Alternative Storage Engines。所有的存储引擎每张表最少支持16个索引且索引长度至少支持256字节。大部分的存储引擎有更高的限制。

For additional information about column indexes, see Section 13.1.15, “CREATE INDEX Statement”.

• Index Prefixes

• FULLTEXT Indexes

• Spatial Indexes

• Indexes in the MEMORY Storage Engine

Index Prefixes

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

对于string类型的列，可以利用col_name(N)语法创建索引，这样就可以仅仅利用列值得前N个字符进行建立索引。利用列值前缀建立索引，可以把索引文件创建的更小。当需要在BLOB或TEXT列上建立索引时，一定要用前缀索引的方式。比如：

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

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.

在InnoDB存储引擎中，如果行格式为REDUNDANT 或 COMPACT 前缀能达到767字节长度，如果格式是DYNAMIC 或COMPRESSED前缀长度限制最大3072个字节。对于MyISAM引擎的表，前缀长度限制为1000字节

Note

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.

注意

前缀长度限制是以字节为单位衡量的，但是在 CREATE TABLE, ALTER TABLE, and CREATE INDEX 语句中，对于非二进制String类型（CHAR, VARCHAR, TEXT）是以字符数量理解的。对于二进制String类型(BINARY, VARBINARY, BLOB)是以字节数理解的。考虑到这一点，当指定非二进制字符类型的列前缀时表示的是多字节的字符

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.

For additional information about index prefixes, see Section 13.1.15, “CREATE INDEX Statement”.

如果搜索短语超过索引前缀的长度，则索引通常用来排除不匹配的行，然后对剩下的行进行可能的匹配

关于其他索引前缀信息，请参考Section 13.1.15, “CREATE INDEX Statement”

FULLTEXT Indexes

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 12.10, “Full-Text Search Functions”.

Optimizations are applied to certain kinds of FULLTEXT queries against single InnoDB tables. Queries with these characteristics are particularly efficient:

• FULLTEXT queries that only return the document ID, or the document ID and the search rank.

• 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 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.

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.

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 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.

FULLTEXT 索引

FULLTEXT索引被用于全文搜索。仅仅支持在InnoDB和MyISAM存储引擎中的CHAR, VARCHAR, 和TEXT 类型的列,全文索引总是在整个列上，而且不支持前缀索引。详细信息请查看Section 12.10, “Full-Text Search Functions”.

对某些InnoDB的表，可以对特定种类的FULLTEXT查询进行优化。下面场景查询尤其有效：

• FULLTEXT 查询仅仅返回文档ID或者文档ID进行排序
• FULLTEXT 查询按排序分数降序并取前N条数据的查询，这种情况不能有where子句，仅仅只能有一个ORDER BY 的降序
• FULLTEXT 查询根据搜索词检索count(*)的值，同样不能带有where子句。代码就像WHERE MATCH(text) AGAINST ('other_text'), 这样，不带任何其他>0的比较操作

对于全文表达式查找，MySQL 会在查询执行器的优化阶段进行评估这些表达式。优化器不仅仅评估表达式，实际在生成执行计划阶段就进行了评估

涉及到全文索引的执行计划是明显比不涉及全文索引的慢，因为在优化阶段不需要进行表达式评估

对于全文索引的解释执行，可能在额外的列上展示已经优化的表；在这种情况下后续的执行就不需要再访问表

Spatial Indexes

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).

空间索引

可以在空间数据类型上创建索引。在MyISAM和InnoDB中支持空间类型的是R-tree索引。其他的存储引擎用的是B-tree(不包括ARCHIVE类型，因为它不支持空间索引)

Indexes in the MEMORY Storage Engine

The MEMORY storage engine uses HASH indexes by default, but also supports BTREE indexes.

对于MEMORY存储引擎默认用的是HASH索引，但是也支持BTREE索引