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

如果指定的索引前缀超过了最大列数据类型大小，则 CREATE INDEX 将按如下方式处理该索引

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

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

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

“普通” 索引会对列值或列值的前缀进行索引。例如，在下表中，给定 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 子句必须包含参数相同的 SUBSTRING()。在以下示例中，只有第二个 SELECT 能够使用该索引，因为这是唯一一个 SUBSTRING() 的参数与索引规范匹配的查询

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 列建立索引，您可以尝试使用 CAST() 函数，如下所示

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 的列，UNIQUE 索引允许多个 NULL 值。

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

FULLTEXT 索引仅支持 InnoDB 和 MyISAM 表，并且只能包含 CHAR、VARCHAR 和 TEXT 列。索引始终基于整个列进行；不支持列前缀索引，并且如果指定了任何前缀长度，则将忽略该长度。有关操作的详细信息，请参阅 第 14.9 节“全文搜索函数”。

InnoDB 支持多值索引。多值索引是在存储值数组的列上定义的二级索引。“普通”索引对于每个数据记录都有一个索引记录 (1:1)。多值索引对于单个数据记录可以有多个索引记录 (N:1)。多值索引旨在对 JSON 数组进行索引。例如，在以下 JSON 文档中的邮政编码数组上定义的多值索引为每个邮政编码创建一个索引记录，其中每个索引记录都引用相同的数据记录。

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

创建多值索引

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

以下列表中的示例展示了三种不同的方法，可以在名为 customers 的表中名为 custinfo 的 JSON 列上的数组 $.zipcode 上创建多值索引 zips。在每种情况下，JSON 数组都被转换为 UNSIGNED 整数值的 SQL 数据类型数组。

多值索引也可以定义为复合索引的一部分。此示例展示了一个复合索引，它包含两个单值部分（用于 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))（或任何其他顺序）并且仍然有效。

使用多值索引

当在 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

多值索引的特征

多值索引还具有以下特征

多值索引的限制

多值索引受以下限制

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

空间列上的空间索引具有以下特征

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

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

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

NDB Cluster 支持使用与标准 MySQL Server 相同的 ALGORITHM=INPLACE 语法进行在线操作。有关更多信息，请参见 第 25.6.12 节“使用 ALTER TABLE 在 NDB Cluster 中进行在线操作”。