MySQL :: MySQL 9.0 參考手冊 :: 15.1.20.9 次要索引和產生欄

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 次要索引和產生欄

15.1.20.9 次要索引和產生欄

InnoDB 支援虛擬產生欄位的次要索引。不支援其他索引類型。在虛擬欄位上定義的次要索引有時被稱為「虛擬索引」。

次要索引可以建立在一個或多個虛擬欄位上，或者建立在虛擬欄位和常規欄位或儲存的產生欄位的組合上。包含虛擬欄位的次要索引可以定義為 UNIQUE。

當在虛擬產生欄位上建立次要索引時，產生的欄位值會實體化到索引的記錄中。如果索引是覆蓋索引（包含查詢檢索的所有欄位的索引），則會從索引結構中的實體化值檢索產生的欄位值，而不是「即時」計算。

當在虛擬欄位上使用次要索引時，由於在INSERT 和UPDATE 操作期間，在次要索引記錄中實體化虛擬欄位值時會執行計算，因此需要考慮額外的寫入成本。即使有額外的寫入成本，虛擬欄位的次要索引可能比產生的儲存欄位更佳，後者會實體化到叢集索引中，導致表更大，需要更多的磁碟空間和記憶體。如果未在虛擬欄位上定義次要索引，則讀取會有額外的成本，因為每次檢查欄位的行時都必須計算虛擬欄位值。

索引的虛擬欄位的值會進行 MVCC 記錄，以避免在回滾或清除操作期間不必要地重新計算產生的欄位值。記錄的值的資料長度受限於索引鍵的限制：COMPACT 和 REDUNDANT 列格式為 767 位元組，DYNAMIC 和 COMPRESSED 列格式為 3072 位元組。

在虛擬欄位上新增或刪除次要索引是一個就地操作。

為 JSON 欄位索引建立產生欄位的索引

如其他地方所述，JSON 欄位無法直接索引。若要建立間接引用此類欄位的索引，您可以定義一個產生欄位，擷取應編入索引的資訊，然後在產生欄位上建立索引，如下例所示

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

（我們已將此範例中最後一個陳述式的輸出包裝起來，以適合檢視區域。）

當您在包含一個或多個使用 -> 或 ->> 運算子的表達式的 SELECT 或其他 SQL 陳述式上使用 EXPLAIN 時，這些表達式會轉換為使用 JSON_EXTRACT() 和（如果需要）JSON_UNQUOTE() 的等效表達式，如此處在 SHOW WARNINGS 的輸出中所見，緊接著此 EXPLAIN 陳述式之後

mysql> EXPLAIN SELECT c->>"$.name"
     > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
`c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
json_extract(`test`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

請參閱 -> 和 ->> 運算子，以及 JSON_EXTRACT() 和 JSON_UNQUOTE() 函數的說明，以取得其他資訊和範例。

此技術也可用於提供間接引用其他無法直接索引的類型（例如 GEOMETRY 欄位）的索引。

也可以使用 JSON_VALUE() 函數在 JSON 欄位上建立索引，其中使用可用於最佳化使用此表達式的查詢的表達式。請參閱該函數的說明，以取得更多資訊和範例。

NDB Cluster 中的 JSON 欄位和間接索引

在 MySQL NDB Cluster 中也可以使用 JSON 欄位的間接索引，但需符合以下條件

NDB 在內部將 JSON 欄位值作為 BLOB 處理。這表示任何具有一個或多個 JSON 欄位的 NDB 表格都必須具有主鍵，否則無法記錄在二進位記錄中。
NDB 儲存引擎不支援虛擬欄位的索引。由於產生欄位的預設值為 VIRTUAL，因此您必須明確指定要套用間接索引的產生欄位為 STORED。

用於建立此處顯示的表格 jempn 的 CREATE TABLE 陳述式是先前顯示的 jemp 表格的版本，並進行了修改，使其與 NDB 相容

CREATE TABLE jempn (
  a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c JSON DEFAULT NULL,
  g INT GENERATED ALWAYS AS (c->"$.id") STORED,
  INDEX i (g)
) ENGINE=NDB;

我們可以使用以下 INSERT 陳述式來填入此表格

INSERT INTO jempn (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"id": "4", "name": "Betty"}');

現在 NDB 可以使用索引 i，如此處所示

mysql> EXPLAIN SELECT c->>"$.name" AS name
    ->           FROM jempn WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jempn
   partitions: p0,p1,p2,p3
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using pushed condition (`test`.`jempn`.`g` > 2)
1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)   
1 row in set (0.00 sec)

您應該記住，儲存的產生欄位以及此類欄位上的任何索引都會使用 DataMemory。