13.1.14 CREATE INDEX 语句

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

通常，在使用CREATE TABLE创建 table 本身时，会在 table 上创建所有索引。参见第 13.1.18 节“ CREATE TABLE 语句”。该指南对于InnoDBtable 尤为重要，因为该 table 的主键确定数据文件中行的物理布局。 CREATE INDEX使您可以向现有 table 添加索引。

CREATE INDEXMap 到ALTER TABLE语句以创建索引。参见第 13.1.8 节“ ALTER TABLE 语句”。 CREATE INDEX不能用于创建PRIMARY KEY;使用ALTER TABLE代替。有关索引的更多信息，请参见第 8.3.1 节“ MySQL 如何使用索引”。

InnoDB支持虚拟列上的二级索引。有关更多信息，请参见第 13.1.18.8 节“辅助索引和生成的列”。

启用innodb_stats_persistent设置后，在InnoDBtable 上创建索引后运行ANALYZE TABLE语句。

格式为(key_part1, key_part2, ...)的索引规范会创建包含多个关键部分的索引。索引键值是通过合并给定键部分的值而形成的。例如(col1, col2, col3)指定了一个多列索引，其索引键由col1，col2和col3的值组成。

• key_part *规范可以以ASC或DESC结尾。这些关键字允许将来用于指定升序或降序索引值存储的扩展。目前，它们已被解析但被忽略；索引值始终按升序存储。

以下各节描述了CREATE INDEX语句的不同方面：

• 列前缀关键部分

• Unique Indexes

• Full-Text Indexes

• Spatial Indexes

• Index Options

• table 复制和锁定选项

列前缀关键部分

对于字符串列，可以使用col_name(length)语法指定仅使用列值的开头部分的索引来创建索引前缀长度：

• 可以为CHAR，VARCHAR，BINARY和VARBINARY键部分指定前缀。

• 必须为BLOB和TEXT关键部分指定前缀。此外，只能为InnoDB，MyISAM和BLACKHOLEtable 构建索引BLOB和TEXT列。

• 前缀限制以字节为单位。但是，对于CREATE TABLE，ALTER TABLE和CREATE INDEX语句中的索引规范，前缀* length *被解释为非二进制字符串类型(CHAR，VARCHAR，TEXT)的字符数和二进制字符串类型(BINARY，VARBINARY，BLOB)的字节数。为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑到这一点。

前缀支持和前缀长度(如果支持)取决于存储引擎。例如，对于InnoDB个 table，前缀最长可以为 767 个字节，如果启用了innodb_large_prefix选项，则前缀可以为 3072 个字节。对于MyISAMtable，前缀长度限制为 1000 个字节。 NDB存储引擎不支持前缀(请参阅第 21.1.7.6 节，“ NDB 群集中不支持或缺少的功能”)。

从 MySQL 5.7.17 开始，如果指定的索引前缀超过了最大列数据类型的大小，则CREATE INDEX如下处理索引：

• 对于非唯一索引，将发生错误(如果启用了严格的 SQL 模式)，或者将索引长度减小到最大列数据类型大小之内，并且会产生警告(如果未启用严格的 SQL 模式)。

• 对于唯一索引，无论采用哪种 SQL 模式，都会发生错误，因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。

此处显示的语句使用name列的前 10 个字符创建索引(假设name具有非二进制字符串类型)：

CREATE INDEX part_of_name ON customer (name(10));

如果该列中的名称通常前 10 个字符不同，则使用此索引执行的查找不应比使用从整个name列创建的索引慢很多。此外，对索引使用列前缀可以使索引文件更小，这可以节省大量磁盘空间，也可以加快INSERT操作。

Unique Indexes

UNIQUE索引会创建约束，以使索引中的所有值都必须不同。如果您尝试添加键值与现有行匹配的新行，则会发生错误。如果您为UNIQUE索引中的列指定前缀值，则列值在前缀长度内必须唯一。 UNIQUE索引允许包含NULL的列具有多个NULL值。

如果 table 的PRIMARY KEY或UNIQUE NOT NULL索引由具有整数类型的单个列组成，则可以使用_rowid来引用SELECT语句中的索引列，如下所示：

• 如果存在由单个整数列组成的PRIMARY KEY，则_rowid指代PRIMARY KEY列。如果存在PRIMARY KEY但不包含单个整数列，则不能使用_rowid。

• 否则，_rowid引用第一个UNIQUE NOT NULL索引中的列(如果该索引包含单个整数列)。如果第一个UNIQUE NOT NULL索引不包含单个整数列，则不能使用_rowid。

Full-Text Indexes

FULLTEXT索引仅支持InnoDB和MyISAMtable，并且只能包含CHAR，VARCHAR和TEXT列。索引总是在整个列上进行；不支持列前缀索引，并且如果指定则忽略任何前缀长度。有关操作的详细信息，请参见第 12.9 节“全文搜索功能”。

Spatial Indexes

MyISAM，InnoDB，NDB和ARCHIVE存储引擎支持诸如POINT和GEOMETRY之类的空间列。 (第 11.4 节“空间数据类型”描述了空间数据类型。)但是，在引擎之间对空间列索引的支持也有所不同。根据以下规则，可以使用空间列上的空间索引和非空间索引。

空间列(使用SPATIAL INDEX创建)上的空间索引具有以下 Feature：

• 仅适用于MyISAM和InnoDBtable。为其他存储引擎指定SPATIAL INDEX会导致错误。

• 索引列必须为NOT NULL。

• 禁止使用列前缀长度。索引每列的全宽。

空间列(使用INDEX，UNIQUE或PRIMARY KEY创建)上的非空间索引具有以下 Feature：

• 允许用于支持除ARCHIVE以外的空间列的任何存储引擎。

• 除非索引是主键，否则列可以是NULL。

• 对于非SPATIAL索引中除POINT列以外的每个空间列，必须指定列前缀长度。 (这与对索引BLOB列的要求相同。)前缀长度以字节为单位。

• 非SPATIAL索引的索引类型取决于存储引擎。当前，使用 B 树。

• 允许仅对InnoDB，MyISAM和MEMORYtable 具有NULL值的列。

Index Options

在关键 Component 列 table 之后，可以给出索引选项。 * index_option *值可以是以下任意值：

• KEY_BLOCK_SIZE [=] value

对于MyISAM个 table，KEY_BLOCK_SIZE(可选)以字节为单位指定用于索引键块的大小。该值被视为提示；如有必要，可以使用其他大小。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖 table 级别的KEY_BLOCK_SIZE值。

InnoDBtable 的索引级别不支持KEY_BLOCK_SIZE。参见第 13.1.18 节“ CREATE TABLE 语句”。

• index_type

一些存储引擎允许您在创建索引时指定索引类型。例如：

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index ON lookup (id) USING BTREE;

table13.1，“每个存储引擎的索引类型”显示了不同存储引擎支持的允许的索引类型值。如果列出了多个索引类型，则在没有给出索引类型说明符的情况下，第一个是默认值。table 中未列出的存储引擎在索引定义中不支持* index_type *子句。

table13.1 每个存储引擎的索引类型

Storage Engine	允许的索引类型
InnoDB	BTREE
MyISAM	BTREE
MEMORY/ HEAP	HASH , BTREE
NDB	HASH，BTREE(请参见 LiteralsComments)

• index_type *子句不能用于FULLTEXT INDEX或SPATIAL INDEX规范。全文索引实现取决于存储引擎。空间索引被实现为 R 树索引。

NDB存储引擎将BTREE索引实现为 T 树索引。

如果指定的索引类型对于给定的存储引擎无效，但是该引擎可以使用另一种索引类型而不影响查询结果，则引擎将使用可用的类型。解析器将RTREE识别为类型名称，但是当前无法为任何存储引擎指定该名称。

TYPE type_name被识别为USING type_name的同义词。但是，USING是首选形式。

下 table 显示了支持* index_type *选项的存储引擎的索引 Feature。

table13.2 InnoDB 存储引擎索引 Feature

Index Class	Index Type	存储空值	允许多个 NULL 值	IS NULL 扫描类型	IS NOT NULL 扫描类型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

table13.3 MyISAM 存储引擎索引 Feature

Index Class	Index Type	存储空值	允许多个 NULL 值	IS NULL 扫描类型	IS NOT NULL 扫描类型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

table13.4 MEMORY 存储引擎索引 Feature

Index Class	Index Type	存储空值	允许多个 NULL 值	IS NULL 扫描类型	IS NOT NULL 扫描类型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	N/A	N/A
Unique	HASH	Yes	Yes	Index	Index
Key	HASH	Yes	Yes	Index	Index

table13.5 NDB 存储引擎索引 Feature

Index Class	Index Type	存储空值	允许多个 NULL 值	IS NULL 扫描类型	IS NOT NULL 扫描类型
Primary key	BTREE	No	No	Index	Index
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	table(见注 1)	table(见注 1)
Unique	HASH	Yes	Yes	table(见注 1)	table(见注 1)
Key	HASH	Yes	Yes	table(见注 1)	table(见注 1)

Table note:

1.如果指定USING HASH则阻止创建隐式有序索引。

• WITH PARSER parser_name

此选项只能与FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理，它将解析器插件与索引关联。 InnoDB和MyISAM支持全文分析器插件。有关更多信息，请参见全文解析器插件和第 28.2.4.4 节“编写全文分析器插件”。

• COMMENT 'string'

索引定义可以包含最多 1024 个字符的可选 Comments。

可以使用CREATE INDEX语句的* index_option * COMMENT子句为各个索引配置MERGE_THRESHOLD索引页。例如：

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

如果在删除行或通过更新操作缩短行时索引页的页面已满百分比降至MERGE_THRESHOLD值以下，则InnoDB尝试将索引页与相邻的索引页合并。 MERGE_THRESHOLD的默认值为 50，这是以前的硬编码值。

还可使用CREATE TABLE和ALTER TABLE语句在索引级别和 table 级别定义MERGE_THRESHOLD。有关更多信息，请参见第 14.8.12 节“为索引页面配置合并阈值”。

table 复制和锁定选项

可以提供ALGORITHM和LOCK子句以影响 table 复制方法以及在修改 table 索引时用于读写 table 的并发级别。它们的含义与ALTER TABLE语句的含义相同。有关更多信息，请参见第 13.1.8 节“ ALTER TABLE 语句”

NDB 群集以前使用不再支持的替代语法支持联机CREATE INDEX操作。现在，NDB Cluster 支持使用与标准 MySQL Server 相同的ALGORITHM=INPLACE语法进行联机操作。有关更多信息，请参见第 21.5.11 节“ NDB 集群中使用 ALTER TABLE 的在线操作”。