8.3.4 列索引
索引的最常见类型涉及单个列,该列将来自该列的值的副本存储在数据结构中,从而允许快速查找具有相应列值的行。 B 树数据结构使索引可以在WHERE
子句中快速找到与诸如=
,>
,≤
,BETWEEN
,IN
等的运算符相对应的特定值,一组值或一系列值。
每个存储引擎定义每个 table 的最大索引数和最大索引长度。参见第 14 章,InnoDB 存储引擎和第 15 章,备用存储引擎。所有存储引擎每个 table 至少支持 16 个索引,并且总索引长度至少为 256 个字节。大多数存储引擎都有更高的限制。
有关列索引的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”。
Index Prefixes
使用字符串列的索引规范中的col_name(N)
语法,可以创建仅使用该列的前* N
*字符的索引。以这种方式仅索引列值的前缀可以使索引文件小得多。为BLOB或TEXT列构建索引时,必须为索引指定前缀长度。例如:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
前缀最长可以为 1000 个字节(对于InnoDB
个 table,则为 767 个字节,除非您设置了innodb_large_prefix个)。
Note
前缀限制以字节为单位,而CREATE TABLE,ALTER TABLE和CREATE INDEX语句中的前缀长度被解释为非二进制字符串类型(CHAR,VARCHAR,TEXT)的字符数和二进制字符串类型(BINARY,VARBINARY,BLOB的字节数) )。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。
如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。
有关索引前缀的其他信息,请参见第 13.1.14 节“ CREATE INDEX 语句”。
FULLTEXT Indexes
FULLTEXT
索引用于全文搜索。仅InnoDB和MyISAM存储引擎支持FULLTEXT
索引,并且仅支持CHAR,VARCHAR和TEXT列。索引始终在整个列上进行,并且不支持列前缀索引。有关详细信息,请参见第 12.9 节“全文搜索功能”。
优化适用于针对单个InnoDB
table 的某些类型的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
您可以在空间数据类型上创建索引。 MyISAM
和InnoDB
支持有关空间类型的 R 树索引。其他存储引擎使用 B 树来索引空间类型(ARCHIVE
除外,后者不支持空间类型索引)。
MEMORY 存储引擎中的索引
MEMORY
存储引擎默认使用HASH
索引,但也支持BTREE
索引。