对于字符串列，可以创建仅使用列值前缀的索引，使用col_name(length)语法来指定索引前缀长度：For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

如果指定的索引前缀超过了最大列数据类型的大小，CREATE INDEX 将按照以下方式处理索引：If a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:

此处显示的语句创建了一个索引，使用 name 列的前 10 个字符（假设 name 是非二进制字符串类型）：The statement shown here creates an index using the first 10 characters of the name column (assuming that name has a nonbinary string type):

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

如果列中的姓名通常在前 10 个字符就不同，那么使用此索引执行的查找操作应该不会比使用整个 name 列创建的索引慢太多。此外，为索引使用列前缀可以使索引文件更小，这可能节省大量磁盘空间，还可能加快 INSERT 操作的速度。If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

A “普通”索引索引列值或列值的前缀。例如，在下表中，给定t1行的索引条目包括完整的col1值以及由值的前 10 个字符组成的前缀：col2解决方案 1. 将索引表达式分配相同的排序规则为A “normal” index indexes column values or prefixes of column values. For example, in the following table, the index entry for a given t1 row includes the full col1 value and a prefix of the col2 value consisting of its first 10 characters:

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

用于索引表达式值的函数键部分也可以用来代替列或列前缀值。使用函数键部分可以对表中未直接存储的值进行索引。例如：Functional key parts that index expression values canalso be used in place of column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:

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);

具有多个键部分的索引可以混合使用非函数键部分和函数键部分。An index with multiple key parts can mix nonfunctional and functional key parts.

ASC 和 DESC 支持用于功能键部分。ASC and DESC are supported for functional key parts.

功能键部分必须遵守以下规则。如果键部分定义包含不允许的结构，将会发生错误。Functional key parts must adhere to the following rules. An error occurs if a key part definition contains disallowed constructs.

对于 CREATE TABLE ... LIKE，目标表会保留原始表的功能关键部分。For CREATE TABLE ... LIKE, the destination table preserves functional key parts from the original table.

函数索引被实现为隐藏的虚拟生成列，这具有以下影响：Functional indexes are implemented as hidden virtual generated columns, which has these implications:

UNIQUE 支持包含函数键部分的索引。然而，主键不能包含函数键部分。主键要求生成列为存储列，但函数键部分是作为虚拟生成列实现的，而不是存储生成列。UNIQUE is supported for indexes that include functional key parts. However, primary keys cannot include functional key parts. A primary key requires the generated column to be stored, but functional key parts are implemented as virtual generated columns, not stored generated columns.

SPATIAL 和 FULLTEXT 索引不能有函数键部分。SPATIAL and FULLTEXT indexes cannot have functional key parts.

如果表中没有主键，InnoDB 会自动将第一个 UNIQUE NOT NULL 索引提升为主键。对于具有函数键部分的 UNIQUE NOT NULL 索引，这是不支持的。If a table contains no primary key, InnoDB automatically promotes the first UNIQUE NOT NULL index to the primary key. This is not supported for UNIQUE NOT NULL indexes that have functional key parts.

如果存在重复索引，非功能性索引会发出警告。包含功能性关键部分的索引没有此功能。Nonfunctional indexes raise a warning if there are duplicate indexes. Indexes that contain functional key parts do not have this feature.

要删除被功能性关键部分引用的列，必须先删除索引。否则，将发生错误。To remove a column that is referenced by a functional key part, the index must be removed first. Otherwise, an error occurs.

尽管非功能性键部分支持前缀长度的指定，但功能性键部分无法实现此操作。解决方法是使用SUBSTRING()（或本节稍后描述的CAST()）。如果要在查询中使用包含SUBSTRING()函数的功能性键部分，则WHERE子句必须包含具有相同参数的SUBSTRING()。在下面的示例中，只有第二个SELECT能够使用该索引，因为这是唯一一个其SUBSTRING()参数与索引规范匹配的查询：Although nonfunctional key parts support a prefix length specification, this is not possible for functional key parts. The solution is to use SUBSTRING() (or CAST(), as described later in this section). For a functional key part containing the SUBSTRING() function to be used in a query, the WHERE clause must contain SUBSTRING() with the same arguments. In the following example, only the second SELECT is able to use the index because that is the only query in which the arguments to SUBSTRING() match the index specification:

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值。然而，这必须正确操作才能达到预期效果。例如，以下语法是无效的：Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values. However, this must be done correctly to achieve the desired effect. For example, this syntax does not work:

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

语法失败，因为：The syntax fails because:

要索引 JSON 列，你可以尝试使用 CAST() 函数如下：To index the JSON column, you could try using the CAST() function as follows:

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

隐藏生成的列被赋予 VARCHAR(30) 数据类型，可以被索引。但这种方法在尝试使用索引时会带来新问题：The hidden generated column is assigned the VARCHAR(30) data type, which can be indexed. But this approach produces a new issue when trying to use the index:

因此，前述表定义中的索引表达式与以下查询中的WHERE：As a result, there is a collation mismatch between the indexed expression in the preceding table definition and the WHERE clause expression in the following query:

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

索引不被使用，因为查询中的表达式和索引表达式不同。为了支持功能性关键部分的此类情景，优化器在寻找索引时会自动剥离CAST()但仅当索引表达式的排序与查询表达式一致时才使用。对于使用带有功能性关键字部分的索引，以下两种解决方案中任一种都可行（尽管它们在效果上有所不同）：The index is not used because the expressions in the query and the index differ. To support this kind of scenario for functional key parts, the optimizer automatically strips CAST() when looking for an index to use, but only if the collation of the indexed expression matches that of the query expression. For an index with a functional key part to be used, either of the following two solutions work (although they differ somewhat in effect):

请注意，虽然优化器支持自动剥离 CAST() 并带有索引生成的列，但以下方法无效，因为它会产生不同的结果（Bug#27337092）：Be aware that although the optimizer supports automatically stripping CAST() with indexed generated columns, the following approach does not work because it produces a different result with and without an index (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 值，且列可以包含 NULLA UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

如果一个表的 PRIMARY KEY UNIQUE NOT NULL 索引，且仅由一个整数类型的列组成，你可以使用 _rowid 来指代索引列 SELECT 语句，具体如下If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:

FULLTEXT索引仅支持 InnoDB 和 MyISAM 表，且仅包含CHAR， VARCHAR， TEXT 列。索引总是覆盖整个列;不支持列前缀索引，且如果指定，任何前缀长度都将被忽略。详见 第 14.9 节，“全文搜索函数”。FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 14.9, “Full-Text Search Functions”, for details of operation.

InnoDB 支持多值索引。多值索引是定义在存储数组数组的列上的次级索引。“normal”索引为每个数据记录（1：1）有一个索引记录。多值索引可以为单个数据记录（N：1）设置多个索引记录。多值索引旨在索引 JSON 数组。例如，在下一个 JSON 文档中，定义在邮政编码数组上的多值索引为每个邮政编码创建一个索引记录，每个索引记录引用的是相同的数据记录。InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A “normal” index has one index record for each data record (1:1). A multi-valued index can have multiple index records for a single data record (N:1). Multi-valued indexes are intended for indexing JSON arrays. For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.

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

创建多值索引Creating multi-valued Indexes

你可以在 CREATE TABLE， ALTER TABLE，或者 CREATE INDEX句。这需要在索引定义中使用CAST(... AS ... ARRAY)在索引定义中，将 JSON SQL 数据类型数组。然后透明地生成一个虚拟列，包含 SQL 数据类型数组中的值;最后，在虚拟列上创建函数索引（也称为虚拟索引）。它是定义在 SQL 数据类型数组中虚拟列值上的函数索引，构成多值索引。You can create a multi-valued index in a CREATE TABLE, ALTER TABLE, or CREATE INDEX statement. This requires using CAST(... AS ... ARRAY) in the index definition, which casts same-typed scalar values in a JSON array to an SQL data type array. A virtual column is then generated transparently with the values in the SQL data type array; finally, a functional index (also referred to as a virtual index) is created on the virtual column. It is the functional index defined on the virtual column of values from the SQL data type array that forms the multi-valued index.

The examples in the following list show the three different ways in which a multi-valued index zips can be created on an array $.zipcode on a JSON影响最近语句生成的第一个值custinfo in a table named customers. In each case, the JSON array is cast to an SQL data type array of UNSIGNED整数值。The examples in the following list show the three different ways in which a multi-valued index zips can be created on an array $.zipcode on a JSON column custinfo in a table named customers. In each case, the JSON array is cast to an SQL data type array of UNSIGNED integer values.

多值索引也可以定义为复合索引的一部分。本示例展示了一个复合索引，其中包含两个单值部分（用于 id 和 modified columns），以及一个多值部分（用于 custinfo 列）：A multi-valued index can also be defined as part of a composite index. This example shows a composite index that includes two single-valued parts (for the id and modified columns), and one multi-valued part (for the custinfo column):

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 TABLEOnly one multi-valued key part can be used in a composite index. The multi-valued key part may be used in any order relative to the other parts of the key. In other words, the ALTER TABLE statement just shown could have used comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (or any other ordering) and still have been valid.

使用多值索引Using multi-valued Indexes

优化器在以下函数指定为 WHERE：The optimizer uses a multi-valued index to fetch records when the following functions are specified in a WHERE clause:

我们可以通过创建并填充 customers使用 CREATE TABLE 和 INSERT 语句来演示：We can demonstrate this by creating and populating the customers table using the following CREATE TABLE and INSERT statements:

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()，每个查询的结果如下所示：First we execute three queries on the customers table, one each using MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS(), with the result from each query shown here:

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：Next, we run EXPLAIN on each of the previous three queries:

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 数组上添加一个多值索引，如下所示：None of the three queries just shown are able to use any keys. To solve this problem, we can add a multi-valued index on the zipcode array in the JSON column (custinfo), like this:

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：When we run the previous EXPLAIN statements again, we can now observe that the queries can (and do) use the index zips that was just created:

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)

多值索引可以被定义为唯一键。如果定义为唯一键，尝试插入已存在于多值索引中的值会返回重复键错误。如果重复值已经存在，尝试添加唯一多值索引会失败，如下所示：A multi-valued index can be defined as a unique key. If defined as a unique key, attempting to insert a value already present in the multi-valued index returns a duplicate key error. If duplicate values are already present, attempting to add a unique multi-valued index fails, as shown here:

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 Indexes

多值索引具有以下附加特征：Multi-valued indexes have the additional characteristics listed here:

多值索引的限制与约束Limitations and Restrictions on Multi-valued Indexes

多值索引受到此处列出的限制和约束的影响：Multi-valued indexes are subject to the limitations and restrictions listed here:

MyISAM、InnoDB、NDB 和 ARCHIVE 存储引擎支持空间列，例如 POINT 和 GEOMETRY。(第 13.4 节，“空间数据类型” 描述了空间数据类型)。然而，不同引擎对空间列的索引支持程度不同。空间列上的空间索引和非空间索引根据以下规则提供。The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as POINT and GEOMETRY. (Section 13.4, “Spatial Data Types”, describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes on spatial columns are available according to the following rules.

空间列上的空间索引具有以下特点：Spatial indexes on spatial columns have these characteristics:

空间列上的非空间索引（使用 INDEX、UNIQUE 或 PRIMARY KEY 创建）具有以下特性：Nonspatial indexes on spatial columns (created with INDEX, UNIQUE, or PRIMARY KEY) have these characteristics:

在键部分列表之后，可以指定索引选项。index_option 的值可以是以下任意选项：Following the key part list, index options can be given. An index_option value can be any of the following:

ALGORITHM 和 LOCK 子句可用于影响表复制方法以及在修改其索引时对表进行读写的并发级别。它们的含义与 ALTER TABLE 语句相同。更多信息，请参见 第 15.1.9 节，“ALTER TABLE 语句”ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the ALTER TABLE statement. For more information, see Section 15.1.9, “ALTER TABLE Statement”

NDB Cluster 支持使用与标准 MySQL Server 相同的 ALGORITHM=INPLACE 语法执行在线操作。更多信息，请参见 第 25.6.12 节，“在 NDB Cluster 中使用 ALTER TABLE 的在线操作”。NDB Cluster supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. See Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.