欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間:2023年06月08日 15:16:57   作者:怪 咖@  
本文主要介紹了Mysql創(chuàng)建json字段索引的兩種方式,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

一、前言

JSON 數(shù)據(jù)類型是在mysql5.7版本后新增的,同 TEXT,BLOB 字段一樣,JSON 字段不允許直接創(chuàng)建索引。即使支持,實際意義也不大,因為我們一般是基于文檔中的元素進行查詢,很少會基于整個 JSON 文檔?;诖藛栴},在MySQL 8.0.17及以后的版本中,InnoDB存儲引擎支持JSON數(shù)組上的多值索引。除此之外還可以通過MySQL 5.7 引入的虛擬列,然后在虛擬列當中使用索引。

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

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

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

MySQL 在處理 虛擬列存儲問題的時候有兩種方式:

  • VIRTUAL(默認):不存儲列值,在讀取表的時候自動計算并返回,不消耗任何存儲,這種存儲方式僅 InnoDB 支持設置索引。
  • STORED:在插入或更新時計算存儲列值,存儲的虛擬列需要存儲空間,并且 MyISAM 也可以設置索引。

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

如下示例就是通過創(chuàng)建表的時候指定的虛擬列,通過(c->"$.id")表達式創(chuàng)建 了一個虛擬列g,并且對虛擬列g創(chuàng)建了索引,通過以下執(zhí)行計劃可以看出索引在查詢 的時候已經(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í)行計劃解析:

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

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

三、多值索引(Using multi-valued Indexes)

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

例如,在下面的JSON文檔中,我們要對zipcode添加一個索引:

{
    "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語法外面有兩層單括號!,如果少寫一個會報錯!

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

準備好測試數(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條件下使用了以下三個函數(shù):

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

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

JSON_OVERLAPS():該函數(shù)用于比較兩個 JSON 文檔。如果兩個文檔具有共同的鍵值對(key-value)或數(shù)組元素(不要求全部一樣,只要一個鍵值對一樣就可以),則返回 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í)行結果如下,可以看到是使用了索引的:

使用的時候需要注意的:

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

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

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

四、官網(wǎng)地址

關于虛擬列索引官網(wǎng)敘述:https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index

關于多值索引官網(wǎng)敘述:https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

到此這篇關于Mysql創(chuàng)建json字段索引的兩種方式的文章就介紹到這了,更多相關Mysql創(chuàng)建json字段索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!

相關文章

  • MySQL本地版本升級超詳細教程(從5.5.20升到8.0.21)

    MySQL本地版本升級超詳細教程(從5.5.20升到8.0.21)

    MySQL是一款廣泛使用的關系型數(shù)據(jù)庫管理系統(tǒng),但是舊版本的客戶端可能會受到一些限制,下面這篇文章主要給大家介紹了關于MySQL本地版本升級超詳細教程,本文是從5.5.20升到8.0.21的相關資料,需要的朋友可以參考下
    2023-04-04
  • mysql 5.7.18 安裝教程及問題匯總

    mysql 5.7.18 安裝教程及問題匯總

    這篇文章主要介紹了mysql 5.7.18 安裝教程及問題匯總,非常不錯,具有參考借鑒價值,需要的朋友可以參考下
    2017-04-04
  • MySQL數(shù)據(jù)庫中刪除重復記錄簡單步驟

    MySQL數(shù)據(jù)庫中刪除重復記錄簡單步驟

    這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫中刪除重復記錄的相關資料,在使用數(shù)據(jù)庫時,出現(xiàn)重復數(shù)據(jù)是常有的情況,但有些情況是允許數(shù)據(jù)重復的,而有些情況是不允許的,當出現(xiàn)不允許的情況,我們就需要對重復數(shù)據(jù)進行刪除處理,需要的朋友可以參考下
    2023-08-08
  • mysql server 8.0.3安裝配置方法圖文教程

    mysql server 8.0.3安裝配置方法圖文教程

    這篇文章主要為大家詳細介紹了mysql server 8.0.3安裝配置方法圖文教程,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2018-09-09
  • mysql 5.7.21 winx64安裝配置方法圖文教程

    mysql 5.7.21 winx64安裝配置方法圖文教程

    這篇文章主要為大家詳細介紹了mysql 5.7.21 winx64安裝配置方法圖文教程,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2018-02-02
  • mysql報錯1033 Incorrect information in file: ‘xxx.frm’問題的解決方法

    mysql報錯1033 Incorrect information in file: ‘xxx.frm’問題的解決方法

    這篇文章主要介紹了關于mysql報錯1033 Incorrect information in file: 'xxx.frm'問題的解決方法,文中通過示例代碼介紹的很詳細,需要的朋友可以參考借鑒,下面來一起看看吧。
    2017-03-03
  • MYSQL拒絕訪問報錯not allowed to connect

    MYSQL拒絕訪問報錯not allowed to connect

    MYSQL拒絕訪問報錯not allowed to connect,下面有個可行的方法,可以在其它任何的主機上以root身份登錄
    2014-07-07
  • 尋找sql注入的網(wǎng)站的方法(必看)

    尋找sql注入的網(wǎng)站的方法(必看)

    下面小編就為大家?guī)硪黄獙ふ襰ql注入的網(wǎng)站的方法(必看)。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧
    2017-08-08
  • mysql 開放外網(wǎng)訪問權限的方法

    mysql 開放外網(wǎng)訪問權限的方法

    今天小編就為大家分享一篇mysql 開放外網(wǎng)訪問權限的方法,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2018-05-05
  • mysql分表和分區(qū)的區(qū)別淺析

    mysql分表和分區(qū)的區(qū)別淺析

    這篇文章主要介紹了mysql分表和分區(qū)的區(qū)別淺析,并對它們之間的相同做了分析,需要的朋友可以參考下
    2014-07-07

最新評論