Skip to main content

5.列索引

最常见的索引类型涉及单个列，在数据结构中存储该列的值的副本，从而允许快速查找具有相应列值的行。 B 树数据结构可以让索引快速查找特定值、一组值或一个值范围，对应于 WHERE 子句中的 =、>、≤、BETWEEN、IN 等运算符。

每个表的最大索引数和最大索引长度是按存储引擎定义的。 所有存储引擎都支持每个表至少 16 个索引，索引总长度至少为 256 字节。 大多数存储引擎都有更高的限制。

索引前缀​

通过在字符串列的索引规范中使用 col_name(N) 语法，可以创建仅使用该列的前 N 个字符的索引。 以这种方式仅对列值的前缀建立索引可以使索引文件小得多。 当对 BLOB 或 TEXT 列建立索引时，必须指定索引的前缀长度。 例如：

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

对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，前缀最长可达 767 字节。 对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，前缀长度限制为 3072 字节。 对于 MyISAM 表，前缀长度限制为 1000 字节。

注意： 前缀限制以字节为单位，而 CREATE TABLE、ALTER TABLE 和 CREATE INDEX 语句中的前缀长度被解释为非二进制字符串类型（CHAR、VARCHAR、TEXT）的字符数和二进制字符串类型（BINARY、 VARBINARY、BLOB）。 使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑这一点。

如果搜索项超过索引前缀长度，则使用索引排除不匹配的行，并检查剩余行是否可能匹配。

全文索引​

FULLTEXT 索引用于全文搜索。 仅 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且仅支持 CHAR、VARCHAR 和 TEXT 列。 索引始终在整个列上进行，并且不支持列前缀索引。

  • 优化适用于针对单个 InnoDB 表的某些类型的 FULLTEXT 查询。 具有这些特征的查询特别有效：
  • 仅返回文档 ID 或文档 ID 和搜索排名的 FULLTEXT 查询。
  • FULLTEXT 查询按分数降序对匹配行进行排序，并应用 LIMIT 子句来获取前 N 个匹配行。 要应用此优化，必须没有 WHERE 子句，而只能有一个按降序排列的 ORDER BY 子句。

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

对于包含全文表达式的查询，MySQL 在查询执行的优化阶段评估这些表达式。 优化器不仅仅查看全文表达式并进行估计，它实际上在制定执行计划的过程中对它们进行评估。

此行为的含义是，全文查询的 EXPLAIN 通常比在优化阶段不进行表达式求值的非全文查询慢。

由于优化期间发生匹配，全文查询的 EXPLAIN 可能会在 Extra 列中显示已优化的 Select 表； 在这种情况下，在以后的执行过程中不需要发生表访问。

空间索引​

可以创建空间数据类型的索引。 MyISAM 和 InnoDB 支持空间类型的 R 树索引。 其他存储引擎使用 B 树来索引空间类型（ARCHIVE 除外，它不支持空间类型索引）。

MEMORY存储引擎中的索引​

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