快捷導(dǎo)航

Mysql創(chuàng)建json字段索引的兩種方式

更新時(shí)間：2023年06月08日 15:16:57 作者：怪咖@

本文主要介紹了Mysql創(chuàng)建json字段索引的兩種方式，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、前言

JSON 數(shù)據(jù)類型是在mysql5.7版本后新增的，同 TEXT，BLOB 字段一樣，JSON 字段不允許直接創(chuàng)建索引。即使支持，實(shí)際意義也不大，因?yàn)槲覀円话闶腔谖臋n中的元素進(jìn)行查詢，很少會(huì)基于整個(gè) JSON 文檔?；诖藛栴}，在MySQL 8.0.17及以后的版本中，InnoDB存儲(chǔ)引擎支持JSON數(shù)組上的多值索引。除此之外還可以通過MySQL 5.7 引入的虛擬列，然后在虛擬列當(dāng)中使用索引。

二、通過虛擬列添加索引（Secondary Indexes and Generated Columns）

InnoDB支持在虛擬生成的列上建立二級(jí)索引。不支持其他索引類型（主鍵索引）。在虛擬列上定義的二級(jí)索引有時(shí)也稱為“虛擬索引”。
二級(jí)索引可以在一個(gè)或多個(gè)虛擬列上創(chuàng)建，也可以在虛擬列與常規(guī)列或存儲(chǔ)生成列的組合上創(chuàng)建。包含虛擬列的二級(jí)索引可以定義為UNIQUE。
當(dāng)在虛擬列上使用輔助索引時(shí)，由于在INSERT和UPDATE操作期間在輔助索引（輔助又叫二級(jí)索引）記錄中實(shí)現(xiàn)虛擬列值時(shí)執(zhí)行計(jì)算，因此需要考慮額外的寫成本。即使有額外的寫成本，虛擬列上的二級(jí)索引也可能比生成的存儲(chǔ)列更可取，生成的存儲(chǔ)列在集群索引中具體化，從而導(dǎo)致需要更多磁盤空間和內(nèi)存的更大的表。如果沒有在虛擬列上定義二級(jí)索引，則會(huì)產(chǎn)生額外的讀取成本，因?yàn)槊看螜z查列的行時(shí)都必須計(jì)算虛擬列值。

語法：ALTER TABLE 表名稱 add column 虛擬列名稱虛擬列類型 GENERATED ALWAYS as (表達(dá)式) [VIRTUAL | STORED];

MySQL 在處理虛擬列存儲(chǔ)問題的時(shí)候有兩種方式：

VIRTUAL（默認(rèn)）：不存儲(chǔ)列值，在讀取表的時(shí)候自動(dòng)計(jì)算并返回，不消耗任何存儲(chǔ)，這種存儲(chǔ)方式僅 InnoDB 支持設(shè)置索引。
STORED：在插入或更新時(shí)計(jì)算存儲(chǔ)列值，存儲(chǔ)的虛擬列需要存儲(chǔ)空間，并且 MyISAM 也可以設(shè)置索引。

創(chuàng)建虛擬列可以在創(chuàng)建表的時(shí)候指定也可以在創(chuàng)建表過后指定。

如下示例就是通過創(chuàng)建表的時(shí)候指定的虛擬列，通過(c->"$.id")表達(dá)式創(chuàng)建了一個(gè)虛擬列g，并且對(duì)虛擬列g創(chuàng)建了索引，通過以下執(zhí)行計(jì)劃可以看出索引在查詢的時(shí)候已經(jīng)生效了。

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)

EXPLAIN執(zhí)行計(jì)劃解析：

SHOW WARNINGS可以顯示上一個(gè)命令的警告信息，以及真正執(zhí)行的sql語句。
->>等價(jià)于json_unquote(json_extract())

在MySQL 8.0.21及更高版本中，還可以使用 JSON_VALUE()函數(shù)在JSON列上創(chuàng)建索引，該函數(shù)帶有一個(gè)表達(dá)式，可用于優(yōu)化使用該表達(dá)式的查詢。

三、多值索引（Using multi-valued Indexes）

多值的索引從MySQL 8.0.17開始，InnoDB支持多值索引。多值索引是在存儲(chǔ)值數(shù)組的列上定義的二級(jí)索引。“普通”索引對(duì)每個(gè)數(shù)據(jù)記錄有一個(gè)索引記錄(1:1)。一個(gè)多值索引對(duì)于一個(gè)數(shù)據(jù)記錄(N:1)可以有多個(gè)索引記錄。多值索引用于索引JSON數(shù)組。

例如，在下面的JSON文檔中，我們要對(duì)zipcode添加一個(gè)索引：

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

三種創(chuàng)建多值索引的方式： CREATE TABLE, ALTER TABLE, or CREATE INDEX

方式一：CREATE TABLE

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

方式二：ALTER TABLE

語法：ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );
注意：這里在CAST語法外面有兩層單括號(hào)！，如果少寫一個(gè)會(huì)報(bào)錯(cuò)！

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 INDEX

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

準(zhǔn)備好測(cè)試數(shù)據(jù)，然后使用上面任意一種方式創(chuàng)建出來索引：

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]}' );

想要多值索引生效的條件是 where條件下使用了以下三個(gè)函數(shù)：

MEMBER OF()：查看數(shù)組是否有某個(gè)元素，如果有則該函數(shù)返回 1，否則返回 0。
語法：元素 value MEMBER OF(json_array)

JSON_CONTAINS()：該函數(shù)用于檢驗(yàn)指定 JSON 文檔是否包含在目標(biāo) JSON 文檔中，或者是否在目標(biāo)文檔的指定路徑上找到指定元素（如果提供了 path參數(shù)）。如果指定 JSON 文檔包含在目標(biāo) JSON 文檔中，該函數(shù)返回 1，否則返回 0。
語法：JSON_CONTAINS(target, candidate[, path])

JSON_OVERLAPS()：該函數(shù)用于比較兩個(gè) JSON 文檔。如果兩個(gè)文檔具有共同的鍵值對(duì)（key-value）或數(shù)組元素（不要求全部一樣，只要一個(gè)鍵值對(duì)一樣就可以），則返回 1，否則返回 0。
語法：JSON_OVERLAPS(json_doc1, json_doc2)

EXPLAIN SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
EXPLAIN SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
EXPLAIN SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

執(zhí)行結(jié)果如下，可以看到是使用了索引的：

使用的時(shí)候需要注意的：

多值索引可以定義為唯一鍵，不能作為主鍵，和外鍵。
可以作為組合索引使用
不支持utf8mb4編碼配合utf8mb4_0900_as_cs排序規(guī)則使用，不支持默認(rèn)的二進(jìn)制排序規(guī)則和字符集。
多值索引不能是覆蓋索引。
不能為多值索引定義索引前綴。

覆蓋索引：索引是高效找到行的一個(gè)方法，當(dāng)能通過檢索索引就可以讀取想要的數(shù)據(jù)，那就不需要再到數(shù)據(jù)表中讀取行了。如果一個(gè)索引包含了（或覆蓋了）滿足查詢語句中字段與條件的數(shù)據(jù)就叫做覆蓋索引。

前綴索引：所謂前綴索引說白了就是對(duì)文本的前幾個(gè)字符建立索引（具體是幾個(gè)字符在建立索引時(shí)指定），這樣建立起來的索引更小，所以查詢更快。這有點(diǎn)類似于 Oracle 中對(duì)字段使用 Left 函數(shù)來建立函數(shù)索引，只不過 MySQL 的這個(gè)前綴索引在查詢時(shí)是內(nèi)部自動(dòng)完成匹配的，并不需要使用 Left 函數(shù)。
那么為什么不對(duì)整個(gè)字段建立索引呢？一般來說使用前綴索引，可能都是因?yàn)檎麄€(gè)字段的數(shù)據(jù)量太大，沒有必要針對(duì)整個(gè)字段建立索引，前綴索引僅僅是選擇一個(gè)字段的部分字符作為索引，這樣一方面可以節(jié)約索引空間，另一方面則可以提高索引效率，當(dāng)然很明顯，這種方式也會(huì)降低索引的選擇性。