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

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

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

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

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

“普通” 索引对列值或列值的前缀进行索引。例如，在下表中，给定 t1 行的索引条目包括完整的 col1 值和由其前 10 个字符组成的 col2 值的前缀

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()，但以下方法无效，因为它在使用和不使用索引时会产生不同的结果（错误 #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 语句中引用该索引列，如下所示

仅 InnoDB 和 MyISAM 表支持 FULLTEXT 索引，并且只能包含 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 的表的 JSON 列 custinfo 上的数组 $.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 服务器相同的 ALGORITHM=INPLACE 语法进行在线操作。有关更多信息，请参见第 25.6.12 节，“NDB Cluster 中使用 ALTER TABLE 进行在线操作”。