對於字串資料行，可以使用 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 索引中為欄指定前綴值，則欄值必須在前綴長度內是唯一的。UNIQUE 索引允許可以包含 NULL 的欄有多個 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 的表格中，在 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 子句中指定下列函數時，最佳化工具會使用多值索引來擷取記錄：

我們可以透過建立和填入 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

多值索引的特性

多值索引具有下列額外特性：

多值索引的限制與約束

多值索引受限於此處列出的限制和約束：

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 進行線上作業」。