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)] | (expr)} [ASC | DESC]

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

index_type:
    USING {BTREE | HASH}

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

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

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

如果指定的索引前缀超过最大列数据类型大小，CREATE INDEX将索引处理如下：

以下语句创建了使用 name 列的前 10 个字符来创建索引（假设 name 是非二进制字符串类型）：

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

如果该列中的名称通常在前 10 个字符不同，使用这个索引进行查找不应该比使用整个 name 列创建的索引慢。同时，使用列前缀来创建索引可以使得索引文件非常小，可能还能加速INSERT 操作。

一个“normal”索引索引列值或列前缀值。例如，在以下表中，给定 t1 行的索引条目包括完整的 col1 值和 col2 值的前 10 个字符：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

可以将表达式值作为索引键部分使用。使用表达式值索引使得可以索引不存储在表中的值。示例：

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

索引可以混合非函数和函数键部分。

ASC 和 DESC 对函数键部分支持。

函数键部分必须遵守以下规则。如果键部分定义包含不允许的构造，出现错误。

对于CREATE TABLE ... LIKE，目标表保留原始表中的功能键部分。

实现功能索引的方式是隐藏虚拟生成列，这些结果：

UNIQUE 可以用于包含函数键部分的索引。但是，主键不能包含函数键部分。主键要求生成列被存储，而函数键部分实现为虚拟生成列，不是存储生成列。

SPATIAL 和 FULLTEXT 索引不能包含函数键部分。

如果表中没有主键，InnoDB 自动将第一个 UNIQUE NOT NULL 索引提升为主键。这不支持具有函数键部分的 UNIQUE NOT NULL 索引。

非功能索引在出现重复索引时发出警告。包含函数键部分的索引不具备这个特性。

要删除引用函数键部分的列，首先必须删除索引否则会出错。

非功能性键部分支持前缀长度指定，但功能性键部分不可能。解决方案是使用SUBSTRING()(或CAST()，后面在本节中描述）。要将功能键部分包含SUBSTRING()函数用于查询，WHERE子句必须包含与之相同的参数。在以下示例中，只有第二个SELECT可以使用索引，因为只有该查询中的参数与索引指定相匹配：

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

功能键部分使得无法索引的值可被索引，例如JSON值。然而，这必须正确地进行以达到期望效果。例如，这种语法不工作：

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);

语法失败是因为：

要索引JSON 列，可以尝试使用以下方式：

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

隐藏生成的列被分配VARCHAR(30) 数据类型，可以索引。但是，这种方法在尝试使用索引时产生了新的问题：

因此，索引表达式在前一个表定义和WHERE子句表达式之间存在字符集不匹配问题：

SELECT * FROM employees WHERE data->>'$.name' = 'James';

查询中表达式和索引不同，索引不被使用。为了支持这种场景下函数键部分，优化器自动在查找索引时去除CAST()，但只当索引表达式的排序规则与查询表达式的排序规则匹配时。要使索引键部分被使用，可以采取以下两种解决方案（它们在效果上有所不同）：

请注意，虽然优化器支持自动去除CAST()与生成列索引，但以下方法不起作用，因为它在有索引和无索引时结果不同（Bug#27337092）：

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)

一个UNIQUE索引创建了约束，使得索引中的所有值都必须是唯一的。如果你尝试添加一个与现有行匹配的新行，会出现错误。指定列的前缀值时，列值在该前缀长度内必须唯一。一个UNIQUE索引允许多个NULL值出现在可以包含NULL的列中。

如果表有一个PRIMARY KEY或UNIQUE NOT NULL索引，且该索引只有一个整数类型的列，你可以在SELECT语句中使用_rowid，如下所示：

FULLTEXT 索引仅支持在 InnoDB 和 MyISAM 表中，并且只能包含CHAR、VARCHAR 和TEXT 列。索引总是对整个列进行操作，前缀索引不支持，如果指定了前缀长度将被忽略。详细信息请参阅第14.9节，“全文搜索函数”。

InnoDB 支持多值索引。多值索引是定义在一个列上的数组值的次要索引。一个“normal” 索引对每个数据记录都有一个索引记录（1:1）。多值索引可以对同一个数据记录有多个索引记录（N:1）。多值索引旨在为JSON 数组提供索引。例如，对于以下 JSON 文档中的 zip 码数组定义了多值索引，创建了每个 zip 码的索引记录，每个索引记录都引用同一个数据记录。

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

Creating multi-valued 索引

您可以在CREATE TABLE、ALTER TABLE或CREATE INDEX语句中创建多值索引。这需要在索引定义中使用CAST(... AS ... ARRAY)，将同类型的标量值在JSON数组转换为SQL数据类型数组。然后，会生成一个虚拟列，其中包含SQL数据类型数组中的值；最后，创建了一个函数索引（也称为虚拟索引），该函数索引是基于SQL数据类型数组中值的虚拟列形成的多值索引。

以下列表中的示例展示了在JSON列custinfo中创建多值索引zips的三个不同方式，每种情况都将JSON数组转换为SQL数据类型数组UNSIGNED整数值。

多值索引也可以作为复合索引的一部分定义。这个示例展示了一个包含两个单值部分（对id和modified列）和一个多值部分（对custinfo列）的复合索引：

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

只能在复合索引中使用一个多值键部分。多值键部分可以与其他键部分的顺序无关紧要。在其他字面上，前面展示的ALTER TABLE语句也可以使用comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified))(或任何其他顺序)仍然有效。

Using multi-valued 索引

优化器使用多值索引来获取记录，以下函数在WHERE子句中指定时：

我们可以通过使用以下CREATE TABLE和INSERT语句来创建和填充customers表：

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

首先，我们对customers表执行三个查询，每个查询使用MEMBER OF()、JSON_CONTAINS()和JSON_OVERLAPS()，结果如下：

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

然后，我们对前三个查询运行EXPLAIN：

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

上述三个查询都不能使用任何键。要解决这个问题，我们可以在 JSON 列 (custinfo) 中的 zipcode 数组添加多值索引，像这样：

mysql> ALTER TABLE customers
    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

当我们再次运行上述 EXPLAIN 语句时，我们现在可以观察到查询可以（并且实际上也使用）刚刚创建的索引 zips :

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

多值索引可以定义为唯一键。如果定义为唯一键，尝试插入已经存在于多值索引中的值将返回重复键错误。如果已经存在重复值，尝试添加唯一多值索引失败，如下所示：

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

Characteristics of Multi-Valued 索引

多值索引具有以下特征：

Limitations and Restrictions on Multi-valued 索引

多值索引受以下限制和约束：

支持空间列的存储引擎有：MyISAM、InnoDB、NDB 和ARCHIVE，例如：POINT 和GEOMETRY。(第13.4节，“空间数据类型”，描述了空间数据类型。）然而，对于引擎的空间列索引支持不同。空间列和非空间列的索引根据以下规则可用。

空间列上的索引具有这些特征：

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

索引选项列表后，可以给出索引选项。一个index_option值可以是以下任意一种：

ALGORITHM和LOCK子句可以影响表复制方法和读写表时的并发性，用于在修改索引时。它们的含义与ALTER TABLE语句相同。更多信息，请参阅第15.1.9节，“ALTER TABLE 语句”

NDB 集群支持使用标准 MySQL 服务器相同的ALGORITHM=INPLACE语法进行在线操作。更多信息请见第25.6.12节，“NDB 集群中的ALTER TABLE 在线操作”。