Documentation Home
MySQL 5.7 参考手册
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.1 数据定义语句  /  13.1.14 CREATE INDEX 语句

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。请参阅第 13.1.18 节，“CREATE TABLE 语句”。该指南对于表尤其重要，因为 InnoDB表中的主键决定了数据文件中行的物理布局。 CREATE INDEX使您能够向现有表添加索引。

CREATE INDEX映射到 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 设置后，在该表上创建索引后运行该表 的ANALYZE TABLE语句 。InnoDB

表单的索引规范 创建具有多个关键部分的索引。索引键值是通过连接给定键部分的值形成的。例如， 指定一个多列索引，其索引键由 、 和 中的 值组成 。 (key_part1, key_part2, ...)(col1, col2, col3)col1col2col3

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

以下部分描述了该 CREATE INDEX声明的不同方面：

列前缀关键部分

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

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

  • 对于非唯一索引，要么发生错误（如果启用了严格 SQL 模式），要么索引长度减少到位于最大列数据类型大小内并产生警告（如果未启用严格 SQL 模式）。

  • 对于唯一索引，无论 SQL 模式如何，都会发生错误，因为减少索引长度可能会启用不满足指定唯一性要求的非唯一条目的插入。

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

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

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

唯一索引

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

如果一个表有一个PRIMARY KEYor UNIQUE NOT NULL索引，该索引由具有整数类型的单个列组成，您可以 _rowid在语句中使用引用索引列 SELECT，如下所示：

  • _rowidPRIMARY KEY如果存在PRIMARY KEY由单个整数列组成的列，则指该列。如果有一个PRIMARY KEY但它不是由单个整数列组成， _rowid则不能使用。

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

全文索引

FULLTEXTInnoDB只有和 表支持索引， MyISAM并且只能包含 CHAR、 VARCHAR和 TEXT列。索引总是发生在整个列上；不支持列前缀索引，如果指定，任何前缀长度都会被忽略。有关操作的详细信息，请参见 第 12.10 节，“全文搜索功能”。

空间索引

、MyISAM、 和 存储引擎支持空间列InnoDB， 例如和 。（第 11.4 节，“空间数据类型”，描述了空间数据类型。）但是，对空间列索引的支持因引擎而异。空间列上的空间和非空间索引可根据以下规则使用。 NDBARCHIVEPOINTGEOMETRY

空间列上的空间索引（使用创建 SPATIAL INDEX）具有以下特征：

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

  • 索引列必须是NOT NULL.

  • 列前缀长度是被禁止的。每列的全宽都被索引。

空间列上的非空间索引（使用 、 或 创建 INDEX）UNIQUE具有 PRIMARY KEY以下特征：

  • 允许用于支持空间列的任何存储引擎，但ARCHIVE.

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

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

  • 非SPATIAL索引的索引类型取决于存储引擎。目前使用的是B-tree。

  • 允许只对、 和 表 具有NULL 值的列。InnoDBMyISAMMEMORY

指数期权

在关键部分列表之后，可以给出索引选项。index_option值可以是以下任何一项 ：

  • KEY_BLOCK_SIZE [=] value

    对于MyISAM表， KEY_BLOCK_SIZE可选择指定用于索引键块的大小（以字节为单位）。该值被视为提示；如有必要，可以使用不同的尺寸。为单个索引定义指定的KEY_BLOCK_SIZE值会覆盖表级KEY_BLOCK_SIZE值。

    KEY_BLOCK_SIZE在表的索引级别不支持InnoDB。请参阅第 13.1.18 节，“CREATE TABLE 语句”。

  • index_type

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

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

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

    表 13.1 每个存储引擎的索引类型

    存储引擎 允许的索引类型
    InnoDB BTREE
    MyISAM BTREE
    MEMORY/HEAP HASH,BTREE
    NDB HASH, BTREE(见正文注释)

    该index_type子句不能用于FULLTEXT INDEX或 SPATIAL INDEX规范。全文索引的实现依赖于存储引擎。空间索引作为 R 树索引实现。

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

    笔记

    对于NDB表列上的索引，USING只能为唯一索引或主键指定该选项。 USING HASH防止创建有序索引；否则，在表上创建唯一索引或主键NDB会自动导致创建有序索引和散列索引，每个索引都索引相同的列集。

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

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

    笔记

    在弃用子句之前 使用index_type选项；您应该期望在未来的 MySQL 版本中删除对在这个位置使用该选项的支持。如果 在前面和后面的位置都给出了一个选项，则应用最后一个选项。 ON tbl_nameindex_type

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

    下表显示了支持该 index_type选项的存储引擎的索引特征。

    表 13.2 InnoDB 存储引擎索引特征

    索引类 索引类型 存储空值 允许多个 NULL 值 IS NULL 扫描类型 IS NOT NULL 扫描类型
    首要的关键 BTREE 不 不 不适用 不适用
    独特的 BTREE 是的 是的 指数 指数
    钥匙 BTREE 是的 是的 指数 指数
    FULLTEXT 不适用 是的 是的 桌子 桌子
    SPATIAL 不适用 不 不 不适用 不适用

    表 13.3 MyISAM 存储引擎索引特征

    索引类 索引类型 存储空值 允许多个 NULL 值 IS NULL 扫描类型 IS NOT NULL 扫描类型
    首要的关键 BTREE 不 不 不适用 不适用
    独特的 BTREE 是的 是的 指数 指数
    钥匙 BTREE 是的 是的 指数 指数
    FULLTEXT 不适用 是的 是的 桌子 桌子
    SPATIAL 不适用 不 不 不适用 不适用

    表 13.4 MEMORY 存储引擎索引特征

    索引类 索引类型 存储空值 允许多个 NULL 值 IS NULL 扫描类型 IS NOT NULL 扫描类型
    首要的关键 BTREE 不 不 不适用 不适用
    独特的 BTREE 是的 是的 指数 指数
    钥匙 BTREE 是的 是的 指数 指数
    首要的关键 HASH 不 不 不适用 不适用
    独特的 HASH 是的 是的 指数 指数
    钥匙 HASH 是的 是的 指数 指数

    表 13.5 NDB 存储引擎索引特征

    索引类 索引类型 存储空值 允许多个 NULL 值 IS NULL 扫描类型 IS NOT NULL 扫描类型
    首要的关键 BTREE 不 不 指数 指数
    独特的 BTREE 是的 是的 指数 指数
    钥匙 BTREE 是的 是的 指数 指数
    首要的关键 HASH 不 不 表（见注1） 表（见注1）
    独特的 HASH 是的 是的 表（见注1） 表（见注1）
    钥匙 HASH 是的 是的 表（见注1） 表（见注1）

    表注：

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

  • WITH PARSER parser_name

    此选项只能与 FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理，它会将解析器插件与索引相关联。 InnoDB并 MyISAM支持全文解析器插件。如果您有一个MyISAM 带有关联的全文解析器插件的表，您可以将该表转换为InnoDB使用 ALTER TABLE. 有关详细信息，请参阅 全文解析器插件和 编写全文解析器插件。

  • COMMENT 'string'

    索引定义可以包括最多 1024 个字符的可选注释。

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

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

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

    MERGE_THRESHOLD也可以使用 CREATE TABLEand ALTER TABLE语句在索引级别和表级别定义。有关详细信息，请参阅 第 14.8.12 节，“为索引页配置合并阈值”。

表格复制和锁定选项

ALGORITHMLOCK可以给出和子句来影响表的复制方法和在修改表的索引时读取和写入表的并发级别。它们的含义与 ALTER TABLE语句相同。有关详细信息，请参阅第 13.1.8 节，“ALTER TABLE 语句”

NDB Cluster 以前CREATE INDEX使用不再支持的替代语法支持在线操作。ALGORITHM=INPLACENDB Cluster 现在支持使用与标准 MySQL 服务器相同的语法进行在线操作。有关更多信息，请参阅 第 21.6.12 节，“在 NDB Cluster 中使用 ALTER TABLE 进行在线操作”。