8.3.4 列索引

索引的最常见类型涉及单个列,该列将来自该列的值的副本存储在数据结构中,从而允许快速查找具有相应列值的行。 B 树数据结构使索引可以在WHERE子句中快速找到与诸如=>BETWEENIN等的运算符相对应的特定值,一组值或一系列值。

每个存储引擎定义每个 table 的最大索引数和最大索引长度。参见第 14 章,InnoDB 存储引擎第 15 章,备用存储引擎。所有存储引擎每个 table 至少支持 16 个索引,并且总索引长度至少为 256 个字节。大多数存储引擎都有更高的限制。

有关列索引的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”

Index Prefixes

使用字符串列的索引规范中的col_name(N)语法,可以创建仅使用该列的前* N *字符的索引。以这种方式仅索引列值的前缀可以使索引文件小得多。为BLOBTEXT列构建索引时,必须为索引指定前缀长度。例如:

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

前缀最长可以为 1000 个字节(对于InnoDB个 table,则为 767 个字节,除非您设置了innodb_large_prefix个)。

Note

前缀限制以字节为单位,而CREATE TABLEALTER TABLECREATE INDEX语句中的前缀长度被解释为非二进制字符串类型(CHARVARCHARTEXT)的字符数和二进制字符串类型(BINARYVARBINARYBLOB的字节数) )。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。

如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。

有关索引前缀的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”

FULLTEXT Indexes

FULLTEXT索引用于全文搜索。仅InnoDBMyISAM存储引擎支持FULLTEXT索引,并且仅支持CHARVARCHARTEXT列。索引始终在整个列上进行,并且不支持列前缀索引。有关详细信息,请参见第 12.9 节“全文搜索功能”

优化适用于针对单个InnoDBtable 的某些类型的FULLTEXT查询。具有以下 Feature 的查询特别有效:

  • FULLTEXT查询仅返回文档 ID 或文档 ID 和搜索排名。

  • FULLTEXT查询以分数的降序对匹配行进行排序,并应用LIMIT子句以获取前 N 个匹配行。为了应用此优化,必须没有WHERE子句,并且只有一个ORDER BY子句按降序排列。

  • FULLTEXT查询仅检索与搜索词匹配的行的COUNT(*)值,而没有其他WHERE子句。将WHERE子句编码为WHERE MATCH(text) AGAINST ('other_text'),而无需任何> 0比较运算符。

对于包含全文 table 达式的查询,MySQL 在查询执行的优化阶段评估这些 table 达式。优化器不仅查看全文 table 达式并进行估计,而且还在制定执行计划的过程中对它们进行评估。

此行为的含义是,对于全文查询,EXPLAIN通常比在优化阶段未进行 table 达式求值的非全文查询的慢。

全文查询的EXPLAIN可能会由于优化期间发生匹配而在Extra列中显示Select tables optimized away;在这种情况下,以后执行期间无需进行 table 访问。

Spatial Indexes

您可以在空间数据类型上创建索引。 MyISAMInnoDB支持有关空间类型的 R 树索引。其他存储引擎使用 B 树来索引空间类型(ARCHIVE除外,后者不支持空间类型索引)。

MEMORY 存储引擎中的索引

MEMORY存储引擎默认使用HASH索引,但也支持BTREE索引。