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的值组成。

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

列前缀关键部分

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

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

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

此处显示的语句使用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语句中的索引列，如下所示：

Full-Text Indexes

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

Spatial Indexes

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

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

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

Index Options

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

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

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

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

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)

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

Note

对于NDBtable 列上的索引，只能为唯一索引或主键指定USING选项。 USING HASH防止创建有序索引；否则，在NDBtable 上创建唯一索引或主键将自动导致同时创建有序索引和哈希索引，每个索引都索引同一组列。

对于包含NDBtable 的一个或多个NULL列的唯一索引，哈希索引只能用于查找 Literals 值，这意味着IS [NOT] NULL条件需要对 table 进行全面扫描。一种解决方法是确保始终使用在 table 上使用一个或多个NULL列的唯一索引，以使其包含有序索引的方式进行创建；也就是说，在创建索引时避免使用USING HASH。

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

Note

不建议使用ON tbl_name子句前使用* index_type 选项；在将来的 MySQL 版本中，将不再支持在此位置使用该选项。如果在较早和较晚的位置都给出了 index_type *选项，则最后一个选项适用。

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则阻止创建隐式有序索引。

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

索引定义可以包含最多 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 的在线操作”。

首页