Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


MySQL 8.3 Reference Manual  /  ...  /  CREATE INDEX Statement

15.1.15 创建索引语句

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}

通常，您在创建表时使用 CREATE TABLE 创建所有索引。请参阅 第 15.1.20 节，“CREATE TABLE 语句”。这些建议尤其适用于 InnoDB 表，其中主键确定数据文件中的行物理布局。CREATE INDEX 允许您将索引添加到现有表中。

CREATE INDEX 映射到 ALTER TABLE 语句以创建索引。请参阅 第 15.1.9 节，“ALTER TABLE 语句”。CREATE INDEX 不能用于创建 PRIMARY KEY；请改用 ALTER TABLE。有关索引的更多信息，请参阅 第 10.3.1 节，“MySQL 如何使用索引”。

InnoDB 支持虚拟列的次要索引。有关更多信息，请参阅 第 15.1.20.9 节，“次要索引和生成列”。

当 innodb_stats_persistent 设置启用时，请在 InnoDB 表上创建索引后运行 ANALYZE TABLE 语句。

表达式 expr 对于 key_part 规范也可以采用形式 (CAST json_expression AS type ARRAY)，以在 JSON 列上创建多值索引。请参阅 多值索引。

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

一个 key_part 规范可以以 ASC 或 DESC 结尾，以指定索引值是否以升序或降序存储。如果没有指定顺序，默认为升序。

ASC 和 DESC 不支持 HASH 索引、多值索引或 SPATIAL 索引。

以下部分描述了 CREATE INDEX 语句的不同方面：

列前缀键部分

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

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

  • 对于非唯一索引，如果启用了严格 SQL 模式，将发生错误；否则，索引长度将被减少以在最大列数据类型大小内，并生成警告（如果未启用严格 SQL 模式）。

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

以下语句创建了一个使用名称列的前 10 个字符的索引（假设名称列具有非二进制字符串类型）：

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

如果列中的名称通常在前 10 个字符中不同，那么使用该索引进行查找的速度可能不会比使用整个名称列创建的索引慢很多。此外，使用列前缀创建索引可以使索引文件变得非常小，从而节省了大量磁盘空间，也可能加速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 支持功能键部分。

功能键部分必须遵守以下规则。如果键部分定义包含不允许的构造，将发生错误。

  • 在索引定义中，使用括号括起表达式，以区别于列或列前缀。例如，这是允许的；表达式括在括号中：

    INDEX ((col1 + col2), (col3 - col4))

    这将产生错误；表达式未括在括号中：

    INDEX (col1 + col2, col3 - col4)

  • 功能键部分不能仅由列名组成。例如，这是不允许的：

    INDEX ((col1), (col2))

    相反，写入键部分作为非功能键部分，不带括号：

    INDEX (col1, col2)

  • 功能键部分表达式不能引用列前缀。为解决这个问题，请参阅本节后面的 SUBSTRING() 和 CAST() 讨论。

  • 功能键部分不允许在外键规范中。

对于 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_UNQUOTE(JSON_EXTRACT(...))。

  • JSON_UNQUOTE() 返回一个具有 LONGTEXT 数据类型的值，因此隐藏的生成列被分配了相同的数据类型。

  • MySQL 无法对 LONGTEXT 列进行索引，除非在键部分指定了前缀长度，并且不允许在函数键部分中使用前缀长度。

要索引 JSON 列，可以尝试使用 CAST() 函数，如下所示：

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

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

  • CAST() 返回一个字符串，使用服务器默认的排序规则 utf8mb4_0900_ai_ci。

  • JSON_UNQUOTE() 返回一个字符串，使用硬编码的排序规则 utf8mb4_bin。

因此，在前面的表定义中索引表达式和以下查询中的 WHERE 子句表达式之间存在排序规则不匹配的问题：

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

索引不会被使用，因为查询和索引中的表达式不同。为了支持这种场景中的函数键部分，优化器会自动删除 CAST()，但 仅当索引表达式的排序规则与查询表达式的排序规则匹配时。

  • 解决方案 1. 将索引表达式的排序规则设置为与 JSON_UNQUOTE() 相同：

    CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE data->>'$.name' = 'James';

    运算符 ->> 等同于 JSON_UNQUOTE(JSON_EXTRACT(...))，JSON_UNQUOTE() 返回一个字符串，使用排序规则 utf8mb4_bin。因此，比较是区分大小写的，只有一行匹配：

    +------------------------------------+
| data                               |
+------------------------------------+
| {"name": "James", "salary": 10000} |
+------------------------------------+

  • 解决方案 2. 在查询中指定完整的表达式：

    CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

    CAST() 返回一个字符串，使用排序规则 utf8mb4_0900_ai_ci，因此比较是不区分大小写的，两个行匹配：

    +------------------------------------+
| data                               |
+------------------------------------+
| {"name": "james", "salary": 9000}  |
| {"name": "James", "salary": 10000} |
+------------------------------------+

请注意，虽然优化器支持自动删除 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 索引中的列指定了前缀值，则该列的值必须在前缀长度内唯一。一个 UNIQUE 索引允许多个 NULL 值对于可以包含 NULL 的列。

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

  • _rowid 引用主键列，如果存在一个由单个整数列组成的主键。如果存在主键，但它不由单个整数列组成，_rowid 不能被使用。

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

全文索引

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 数据类型数组值的虚拟列；最后，在虚拟列上创建一个函数索引（也称为虚拟索引）。它是虚拟列上的函数索引定义的多值索引。

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

  • CREATE TABLE only：

    CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
    );

  • CREATE TABLE plus ALTER TABLE：

    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 zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

  • CREATE TABLE plus CREATE INDEX：

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

CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

多值索引也可以作为复合索引的一部分定义。这个示例显示了一个复合索引，其中包括两个单值部分（对于 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 子句中指定以下函数时，使用多值索引来获取记录：

我们可以通过创建和填充 customers 表来演示这一点，使用以下 CREATE TABLE 和 INSERT 语句：

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
多值索引的特征

多值索引具有以下附加特征：

  • DML 操作对多值索引的影响与对普通索引的影响相同，唯一的区别是可能会对单个聚簇索引记录进行多次插入或更新。

  • 多值索引的可空性：

    • 如果多值键部分为空数组，不会将任何条目添加到索引中，并且数据记录不能通过索引扫描访问。

    • 如果多值键部分生成返回 NULL 值，将添加一个包含 NULL 的条目到多值索引中。如果键部分定义为 NOT NULL，将报告错误。

    • 如果类型化数组列设置为 NULL，存储引擎将存储一个包含 NULL 的记录，该记录指向数据记录。

    • JSON 空值不允许在索引数组中。如果任何返回值是 NULL,它将被视为 JSON 空值，并报告 无效的 JSON 值 错误。

  • 因为多值索引是虚拟列上的虚拟索引，因此它们必须遵守与次要索引在虚拟生成列上的相同规则。

  • 对于空数组，不会添加索引记录。

多值索引的限制和限制

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

  • 每个多值索引仅允许一个多值键部分。然而，CAST(... AS ... ARRAY) 表达式可以在 JSON 文档中引用多个数组，如下所示：

    CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)

    在这种情况下，所有匹配 JSON 表达式的值都将被存储在索引中作为单个扁平数组。

  • 具有多值键部分的索引不支持排序，因此不能用作主键。出于同样的原因，多值索引不能使用 ASC 或 DESC 关键字定义。

  • 多值索引不能是覆盖索引。

  • 多值索引每个记录的最大值数由undo日志页面上的数据量确定， 即 65221 字节（64K 减去 315 字节的开销），这意味着键值的总长度也限制为 65221 字节。键的最大数量取决于各种因素，因此无法定义特定的限制。测试表明，多值索引可以容纳高达 1604 个整数键每记录，例如。当达到限制时，将报告类似以下的错误：ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s)。

  • 多值键部分中仅允许 JSON 表达式。该表达式不需要引用 JSON 文档中的现有元素，但必须是语法上正确的。

  • 由于聚集索引记录中的索引记录散布在多值索引中，因此多值索引不支持范围扫描或索引仅扫描。

  • 多值索引不允许在外键规范中使用。

  • 多值索引不能定义索引前缀。

  • 多值索引不能在数据类型为 BINARY 的情况下定义（见 CAST() 函数的描述）。

  • 在线创建多值索引不受支持，这意味着操作使用 ALGORITHM=COPY。见 性能和空间要求。

  • 除了以下两个字符集和排序规则的组合外，多值索引不支持其他字符集和排序规则：

    1. 使用 binary 字符集和默认的 binary 排序规则

    2. 使用 utf8mb4 字符集和默认的 utf8mb4_0900_as_cs 排序规则。

  • 与 InnoDB 表上的其他索引一样，多值索引不能使用 USING HASH 创建；尝试这样做将导致警告：该存储引擎不支持 HASH 索引算法，使用默认存储引擎代替。（USING BTREE 是支持的。）

空间索引

MyISAM、InnoDB、NDB 和 ARCHIVE 存储引擎支持空间列，如 POINT 和 GEOMETRY。（第 13.4 节，“空间数据类型” 描述了空间数据类型。）然而，空间列索引支持在引擎之间有所不同。

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

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

  • 空间列上的索引 必须 是 SPATIAL 索引。SPATIAL 关键字因此是可选的，但隐式地用于创建空间列上的索引。

  • 仅适用于单个空间列。不能在多个空间列上创建空间索引。

  • 索引列必须是 NOT NULL。

  • 列前缀长度被禁止。每个列的完整宽度被索引。

  • 不允许用于主键或唯一索引。

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

  • 适用于除 ARCHIVE 之外的任何支持空间列的存储引擎。

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

  • 非 SPATIAL 索引的索引类型取决于存储引擎。当前，B 树索引被使用。

  • 仅适用于可以具有 NULL 值的列，仅适用于 InnoDB、MyISAM 和 MEMORY 表。

索引选项

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

  • KEY_BLOCK_SIZE [=] value

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

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

  • index_type

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

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

    表 15.1，“每个存储引擎的索引类型” 显示了不同存储引擎支持的索引类型值。其中多个索引类型列出时，第一个是默认值，当没有指定索引类型时使用。未在表中列出的存储引擎不支持 index_type 子句在索引定义中。

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

    Storage Engine Permissible Index Types
    InnoDB BTREE
    MyISAM BTREE
    MEMORY/HEAP HASH, BTREE
    NDB HASH, BTREE(见正文中的备注)

    The index_type 子句不能用于 FULLTEXT INDEX 规范。全文索引实现取决于存储引擎。空间索引实现为 R-tree 索引。

    如果您指定了一个不适用于给定存储引擎的索引类型，但引擎可以使用另一个索引类型而不影响查询结果，那么引擎将使用可用的类型。解析器将 RTREE 识别为类型名称。这仅适用于 SPATIAL 索引。

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

    Note

    对于 NDB 表列的索引，USING 选项只能用于唯一索引或主键。USING HASH 防止了有序索引的创建；否则，在 NDB 表上创建唯一索引或主键将自动创建两个索引：一个有序索引和一个哈希索引，每个索引都索引相同的列集。

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

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

    Note

    在 ON tbl_name 子句之前使用 index_type 选项现在已弃用；预计在未来 MySQL 版本中将删除对该选项的支持。如果在早期和晚期位置都指定了 index_type 选项，那么最后一个选项将生效。

    TYPE type_name 被识别为 USING type_name 的同义词。然而，USING 是首选形式。

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

    表 15.2 InnoDB 存储引擎索引特征

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE 否 否 N/A N/A
    Unique BTREE 是 是 索引 索引
    Key BTREE 是 是 索引 索引
    FULLTEXT N/A 是 是 表 表
    SPATIAL N/A 否 否 N/A N/A

    表 15.3 MyISAM 存储引擎索引特征

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE 否 否 N/A N/A
    Unique BTREE 是 是 索引 索引
    Key BTREE 是 是 索引 索引
    FULLTEXT N/A 是 是 表 表
    SPATIAL N/A 否 否 N/A N/A

    表 15.4 MEMORY 存储引擎索引特征

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE 否 否 N/A N/A
    Unique BTREE 是 是 索引 索引
    Key BTREE 是 是 索引 索引
    Primary key HASH 否 否 无 无
    Unique HASH 是 是 索引 索引
    Key HASH 是 是 索引 索引

    表 15.5 NDB 存储引擎索引特征

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE 否 否 索引 索引
    Unique BTREE 是 是 索引 索引
    Key BTREE 是 是 索引 索引
    Primary key HASH 否 否 表（见注释 1） 表（见注释 1）
    Unique HASH 是 是 表（见注释 1） 表（见注释 1）
    Key HASH 是 是 表（见注释 1） 表（见注释 1）

    表注释：

    1. 使用 HASH 防止创建隐式有序索引。

  • WITH PARSER parser_name

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

  • COMMENT 'string'

    索引定义可以包括一个可选的注释，长度最多为 1024 个字符。

    Merge 阈值 MERGE_THRESHOLD 对于索引页可以使用 index_option COMMENT 子句的 CREATE 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 TABLE 和 ALTER TABLE 语句在索引级别和表级别定义。有关更多信息，请参阅 第 17.8.11 节，“配置索引页合并阈值”。

  • VISIBLE、INVISIBLE

    指定索引可见性。索引默认可见。不可见索引不会被优化器使用。索引可见性规则适用于主键（显式或隐式）以外的索引。有关更多信息，请参阅 第 10.3.12 节，“不可见索引”。

  • ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 用于指定主存储引擎和次要存储引擎的索引属性。这些选项保留供将来使用。

    允许的值是一个包含有效 JSON 文档的字符串文字或空字符串（''）。无效的 JSON 将被拒绝。

    CREATE INDEX i1 ON t1 (c1) ENGINE_ATTRIBUTE='{"key":"value"}';

    ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值可以重复指定，而不会出错。在这种情况下，将使用最后指定的值。

    ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值不会被服务器检查，也不会在表的存储引擎更改时被清除。

表复制和锁定选项

算法 和 锁 子句可以用来影响表复制方法和读写表时的并发级别，而表的索引正在被修改时。它们与 ALTER TABLE 语句中的含义相同。有关更多信息，请参阅 第 15.1.9 节，“ALTER TABLE 语句”

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


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows