對於字串欄位，可以使用 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()，但以下方法無法運作，因為它在使用和不使用索引時會產生不同的結果 (錯誤 #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 來參考索引欄位，如下所示

僅 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 Server 相同的 ALGORITHM=INPLACE 語法來支援線上操作。有關更多資訊，請參閱第 25.6.12 節，「在 NDB Cluster 中使用 ALTER TABLE 的線上操作」。