精华内容
下载资源
问答
  • MySQL如何建立JSON字段索引

    千次阅读 2019-03-27 16:29:15
    MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的...

    转载地址:https://yq.aliyun.com/articles/303208?utm_content=m_37669

    概述

    MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。
    示例数据
    我们将基于下面的JSON对象进行演示

    {
        "id": 1,  
        "name": "Sally",  
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
         }
     }
    

    表的基本结构

     
    CREATE TABLE `players` (  
        `id` INT UNSIGNED NOT NULL,
        `player_and_games` JSON NOT NULL,
        PRIMARY KEY (`id`)
    );
     
    

    如果只是基于上面的表的结构我们是无法对JSON字段中的Key进行索引的。接下来我们演示如何借助虚拟字段对其进行索引
    增加虚拟字段
    虚拟列语法如下

    <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
    [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
    

    在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

    如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式

    加完虚拟列的建表语句如下:

    CREATE TABLE `players` (  
       `id` INT UNSIGNED NOT NULL,
       `player_and_games` JSON NOT NULL,
       `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
       PRIMARY KEY (`id`)
    );
    

    Note: 利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段,我把它定义成不可以为空。在实际的工作中,一定要集合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。
    我们插入数据

    INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
        "id": 1,  
        "name": "Sally",
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
          }
       }'
    );
     
    

    查看表里的数据

    SELECT * FROM `players`;
     
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    | id | player_and_games                                                                                                                                                                                           | names_virtual |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    |  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
    |  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
    |  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
    |  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
    |  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
    |  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    

    查看表Players的字段

    SHOW COLUMNS FROM `players`;
     
    +------------------+------------------+------+-----+---------+-------------------+
    | Field            | Type             | Null | Key | Default | Extra             |
    +------------------+------------------+------+-----+---------+-------------------+
    | id               | int(10) unsigned | NO   | PRI | NULL    |                   |
    | player_and_games | json             | NO   |     | NULL    |                   |
    | names_virtual    | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
    +------------------+------------------+------+-----+---------+-------------------+
    

    我们看到虚拟字段names_virtual的类型是VIRTUAL GENERATED。MySQL只是在数据字典里保存该字段元数据,并没有真正的存储该字段的值。这样表的大小并没有增加。我们可以利用索引把这个字段上的值进行物理存储。
    在虚拟字段上加索引
    再添加索引之前,让我们先看下面查询的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ALL
    possible_keys: NULL  
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 6
         filtered: 16.67
            Extra: Using where
    

    添加索引

    CREATE INDEX `names_idx` ON `players`(`names_virtual`);  
     
    

    再执行上面的查询语句,我们将得到不一样的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ref
    possible_keys: names_idx  
              key: names_idx
          key_len: 22
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    

    如我们所见,最新的执行计划走了新建的索引。
    小结
    本文介绍了如何在MySQL 5.7中保存JSON文档。为了高效的检索JSON中内容,我们可以利用5.7的虚拟字段来对JSON的不同的KEY来建索引。极大的提高检索的速度。

    展开全文
  • MySQL如何索引JSON字段.pdf
  • MySQL 8.0.17开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录。跟普通索引一样,也可以在`EXPLAIN`中查看到。

    版权说明: 本文由博主keep丶原创,转载请注明出处。
    原文地址: https://blog.csdn.net/qq_38688267/article/details/119383103
    环境说明: 本文测试用的MySQL版本为8.0.25,截至2021.8.5,MySQL最新版本为8.0.26,这两个版本在多值索引相关内容方面没有改动。

    多值索引简介

      从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录。此类索引特定的语法定义:CAST(expression AS type ARRAY),例如CAST(data->'$.zipcode' AS UNSIGNED ARRAY)。 跟普通索引一样,也可以在EXPLAIN中查看到。

    创建多值索引

      跟其他索引一样,多值索引可以在建表时添加,也可以通过ALTER TABLE或者CREATE INDEX创建。

    JSON对象字段索引

    语法

      ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );

       注意:这里在CAST语法外面有两层单括号!,如果少写一个会报错!

    测试案例

    PS:文中的案例是参考官方文档中的案例,只是作为测试,所以在命名等方面并不怎么规范,实际开发过程中要严格遵守公司团队的开发规范,不要偷懒!

    DROP TABLE IF EXISTS `customers`;
    /*建表语句*/
    CREATE TABLE customers ( 
    	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    	custinfo JSON NOT NULL 
    );
    /*插入写测试数据*/
    INSERT INTO customers
    VALUES
    	( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
    	( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
    	( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
    	( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
    	( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
    /*添加多值索引*/
    ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
    /*测试 MEMBER OF 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	94507 MEMBER OF ( custinfo -> '$.key' );
    /*测试 JSON_CONTAINS 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	JSON_CONTAINS(
    		custinfo -> '$.key',
    	CAST( '[94582]' AS JSON ));
    /*测试 JSON_OVERLAPS 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	JSON_OVERLAPS (
    		custinfo -> '$.key',
    	CAST( '[94477]' AS JSON ));
    
    • 查看执行计划发现可以使用到索引:
      在这里插入图片描述

    • 如果需要给字符类型创建多值索引,则必须是utf8mb4字符集且排序规则是utf8mb4_0900_as_cs,否则报错该版本不支持:
      如果要为binary二进制字符串创建多值索引的话,则排序规则必须是binary,否则报错不支持。

      在这里插入图片描述

    • 修改排序规则后可成功添加索引:
      在这里插入图片描述

    JSON数组对象索引

    语法

      ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );

       注意:这里在CAST语法外面有两层单括号!,如果少写一个会报错!

    测试案例

    DROP TABLE IF EXISTS `customers`;
    /*建表语句*/
    CREATE TABLE customers ( 
    	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    	custinfo JSON NOT NULL 
    );
    /*插入写测试数据*/
    INSERT INTO customers
    VALUES
    	( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
    	( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
    	( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
    	( NULL, NOW(), '[{"key":94536}]'),
    	( NULL, NOW(), '[{"key":94507},{"key":94582}]');
    /*添加多值索引*/
    ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
    /*测试 MEMBER OF 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	94507 MEMBER OF ( custinfo -> '$[*].key' );
    /*测试 JSON_CONTAINS 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	JSON_CONTAINS(
    		custinfo -> '$[*].key',
    	CAST( '[94582, 94507]' AS JSON ));
    /*测试 JSON_OVERLAPS 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	JSON_OVERLAPS (
    		custinfo -> '$[*].key',
    	CAST( '[94477, 94582]' AS JSON ));
    
    • 查看执行计划发现可以使用到索引:

    在组合索引中创建多值索引

    语法

      语法跟普通组合索引差不多,同样也遵守最左匹配原则:
      ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
        ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );

       注意:这里在CAST语法外面需要使用小括号括起来!

    测试案例

    DROP TABLE IF EXISTS `customers`;
    /*建表语句*/
    CREATE TABLE customers ( 
    	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    	age tinyint(4) not null,
    	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    	custinfo JSON NOT NULL 
    );
    /*插入写测试数据*/
    INSERT INTO customers
    VALUES
    	( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
    	( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
    	( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
    	( NULL, 24, NOW(), '[{"key":94536}]'),
    	( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
    /*添加多值索引*/
    alter table customers DROP INDEX idx_age_custinfo$list_modified ;
    ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
    ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified  );
    ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
    /*测试 MEMBER OF 语法*/
    SELECT
    	* 
    FROM
    	customers 
    WHERE
    	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;
    
    • 查看执行计划发现可以使用到索引:
      在这里插入图片描述

    多值索引的局限

    • 一个多值索引只允许包含一个属性的值

    • 该索引目前只支持三个语法
      目前只有MEMBER OFJSON_CONTAINS()JSON_OVERLAB()三种语法可以使用到多值索引。

    • 索引值必须转成数组
      ( CAST( custinfo -> '$.key' AS UNSIGNED array)),语法中的array是可以不加的,之所以要强制加是因为如果不加就不是数组结构,不是数组结构就没法直接使用上述三个语法,需要通过JSON_ARRAY()等方法转换后才能使用,这样就会导致索引失效!因此不管需要加索引的字段是单个值的字段还是数组字段,都要加上array关键字。

    • 该索引不支持用于表关联

    • 不能结合前缀索引

    • 不支持在线创建多值索引
      这句话的意思是该操作使用 ALGORITHM=COPY,即通过新建一张表结构,再将数据复制过去的方式实现索引的创建。因此该过程中不允许DML操作。

    • 多值索引对字符集类型字段有明确的要求:

      • binary字符集的排序规则必须是binary
      • utf8mb4字符集的排序规则必须是utf8mb4_0900_as_cs
      • 其他任何字符集或排序规则都不能创建多值索引,创建时会报错当前版本不支持。

    应用场景

      多值索引的应用场景非常广泛!有了他之后很多关联关系表都可以不用了!举个简单的例子:用户标签,很多场景下会给用户贴上各种标签,比如1高 2富 3帅,为了后续的更高效的做统计或筛选查询,我们不能直接将这个标签作为一个字段存储,因为没有索引查询效率不高,所以很多时候会使用一张关联关系表来存储用户-标签的关系。但是现在有了多值索引,我们就可以将标签作为一个字段存储了!

      这只是其中一个小场景,类似的场景非常多,用户可以换成任何事物,标签也可以换成其他任何属性,只要是这个事物存在多种属性值就行,存在一个多对多关系,那么在没有需要这个属性与其他表做表关联的请况下),都可以使用多值索引实现!多值索引不支持表关联,因此如果需要用该字段在做表关联的话就不合适了。

      关于JSON函数,还有其他的博客介绍:

    展开全文
  • 摘要: 概述 MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。 概述 MySQL从...

    转自:https://yq.aliyun.com/articles/303208?utm_content=m_37669

    摘要: 概述 MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。

    概述

    MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

    示例数据

    我们将基于下面的JSON对象进行演示

    {
        "id": 1,  
        "name": "Sally",  
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
         }
     }
    

    表的基本结构

    
    CREATE TABLE `players` (  
        `id` INT UNSIGNED NOT NULL,
        `player_and_games` JSON NOT NULL,
        PRIMARY KEY (`id`)
    );
    
    

    如果只是基于上面的表的结构我们是无法对JSON字段中的Key进行索引的。接下来我们演示如何借助虚拟字段对其进行索引

    增加虚拟字段

    虚拟列语法如下

    <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
    [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
    

    在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

    如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式

    加完虚拟列的建表语句如下:

    CREATE TABLE `players` (  
       `id` INT UNSIGNED NOT NULL,
       `player_and_games` JSON NOT NULL,
       `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
       PRIMARY KEY (`id`)
    );
    

    Note: 利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段,我把它定义成不可以为空。在实际的工作中,一定要集合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。

    我们插入数据

    INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
        "id": 1,  
        "name": "Sally",
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
          }
       }'
    );
    ...
    
    

    查看表里的数据

    SELECT * FROM `players`;
    
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    | id | player_and_games                                                                                                                                                                                           | names_virtual |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    |  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
    |  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
    |  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
    |  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
    |  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
    |  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    

    查看表Players的字段

    SHOW COLUMNS FROM `players`;
    
    +------------------+------------------+------+-----+---------+-------------------+
    | Field            | Type             | Null | Key | Default | Extra             |
    +------------------+------------------+------+-----+---------+-------------------+
    | id               | int(10) unsigned | NO   | PRI | NULL    |                   |
    | player_and_games | json             | NO   |     | NULL    |                   |
    | names_virtual    | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
    +------------------+------------------+------+-----+---------+-------------------+
    

    我们看到虚拟字段names_virtual的类型是VIRTUAL GENERATED。MySQL只是在数据字典里保存该字段元数据,并没有真正的存储该字段的值。这样表的大小并没有增加。我们可以利用索引把这个字段上的值进行物理存储。

    在虚拟字段上加索引

    再添加索引之前,让我们先看下面查询的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ALL
    possible_keys: NULL  
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 6
         filtered: 16.67
            Extra: Using where
    

    添加索引

    CREATE INDEX `names_idx` ON `players`(`names_virtual`);  
    
    

    再执行上面的查询语句,我们将得到不一样的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ref
    possible_keys: names_idx  
              key: names_idx
          key_len: 22
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    

    如我们所见,最新的执行计划走了新建的索引。

    小结

    本文介绍了如何在MySQL 5.7中保存JSON文档。为了高效的检索JSON中内容,我们可以利用5.7的虚拟字段来对JSON的不同的KEY来建索引。极大的提高检索的速度。

    展开全文
  • MySQL如何索引JSON字段

    万次阅读 2019-03-17 17:18:17
    MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的...

         转载地址:https://yq.aliyun.com/articles/303208?utm_content=m_37669

     

    概述

    MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

    示例数据

    我们将基于下面的JSON对象进行演示

    {
        "id": 1,  
        "name": "Sally",  
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
         }
     }
    

    表的基本结构

    
    CREATE TABLE `players` (  
        `id` INT UNSIGNED NOT NULL,
        `player_and_games` JSON NOT NULL,
        PRIMARY KEY (`id`)
    );
    
    

    如果只是基于上面的表的结构我们是无法对JSON字段中的Key进行索引的。接下来我们演示如何借助虚拟字段对其进行索引

    增加虚拟字段

    虚拟列语法如下

    <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
    [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
    

    在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

    如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式

    加完虚拟列的建表语句如下:

    CREATE TABLE `players` (  
       `id` INT UNSIGNED NOT NULL,
       `player_and_games` JSON NOT NULL,
       `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
       PRIMARY KEY (`id`)
    );
    

    Note: 利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段,我把它定义成不可以为空。在实际的工作中,一定要集合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。

    我们插入数据

    INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
        "id": 1,  
        "name": "Sally",
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
          }
       }'
    );
    ...
    
    

    查看表里的数据

    SELECT * FROM `players`;
    
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    | id | player_and_games                                                                                                                                                                                           | names_virtual |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    |  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
    |  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
    |  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
    |  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
    |  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
    |  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
    +----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
    

    查看表Players的字段

    SHOW COLUMNS FROM `players`;
    
    +------------------+------------------+------+-----+---------+-------------------+
    | Field            | Type             | Null | Key | Default | Extra             |
    +------------------+------------------+------+-----+---------+-------------------+
    | id               | int(10) unsigned | NO   | PRI | NULL    |                   |
    | player_and_games | json             | NO   |     | NULL    |                   |
    | names_virtual    | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
    +------------------+------------------+------+-----+---------+-------------------+
    

    我们看到虚拟字段names_virtual的类型是VIRTUAL GENERATED。MySQL只是在数据字典里保存该字段元数据,并没有真正的存储该字段的值。这样表的大小并没有增加。我们可以利用索引把这个字段上的值进行物理存储。

    在虚拟字段上加索引

    再添加索引之前,让我们先看下面查询的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ALL
    possible_keys: NULL  
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 6
         filtered: 16.67
            Extra: Using where
    

    添加索引

    CREATE INDEX `names_idx` ON `players`(`names_virtual`);  
    
    

    再执行上面的查询语句,我们将得到不一样的执行计划

    EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: players
       partitions: NULL
             type: ref
    possible_keys: names_idx  
              key: names_idx
          key_len: 22
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    

    如我们所见,最新的执行计划走了新建的索引。

     

    小结

    本文介绍了如何在MySQL 5.7中保存JSON文档。为了高效的检索JSON中内容,我们可以利用5.7的虚拟字段来对JSON的不同的KEY来建索引。极大的提高检索的速度。

    展开全文
  • mysql json字段的使用与意义

    万次阅读 2016-01-28 15:43:57
    mysql 5.7.8开始支持json字段类型,并提供了不少内置函数,通过计算列,甚至还可以直接索引json中的数据! 如果还不了解基础的可参看 mysql json快速入门 为何说json原生支持非常关键呢,不是可以自己在客户端...
  • Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式。在Json列插入或者...
  • mysql5.7.8之后开始原生支持json....Json字段的使用场景 在读laravel手册举例子时,我们经常会看到 $user->is_admin 来判断用户是否为管理员,但是在用户表中,admin往往只占很小一部分.如果单开一个is_adm...
  • Mysql 8.0 json 索引

    2021-08-27 13:50:18
    Mysql 8.0,数据存放json字段类型,量大,查询会全表扫描,需要加函数索引, as后面, 根据不同的类型 会变化 create index idx_project_id on event_topic_data(( CAST(json_extract(json_data,'$.project_id') AS ...
  • CREATE TABLE t_json(id INT PRIMARY KEY, NAME VARCHAR(20) , info JSON); --------------------------------------------------------------------------------------------------------------------------------...
  • mysql 5.7中有很多新的特性,但平时可能很少用到,这里列举2个实用的功能:虚拟列及json字段类型 一、先创建一个测试表: 1 2 3 4 5 6 7 8 9 droptableif exists ...
  • '$[1].userId' ) AS userId1, JSON_EXTRACT( JSON_EXTRACT( batch_param, '$.users' ), '$[*].userId' ) AS userIds FROM batch_records WHERE id =6 查询结果: 附: $表示整个json对象,在索引数据时用下标...
  • mysql json数组 索引 index动态

    万次阅读 2018-01-24 16:48:47
    需求:select jsonArray[index],index from table;错误示范select jsonArray->"$[index]",index from table; select json_extract...原理&正确方法可以用字符串拼接函数 CONCAT 生成json_extract 的第二个参数
  • How can I sort a query based on the average Rating in a field in my table, the field itself is JSON text, structured like:[{"Type":1,"Rating":5},{"Type":2,"Rating":5},{"Type":3,"Rating":5}]I need my q...
  • MySQL JSON类型字段操作

    2020-04-20 18:08:58
    MySQLJSON类型字段的操作
  • 本文详细介绍MySQLJSON字段类型的实用方法,包括JSON对象和JSON数组的查询修改等操作。
  • SpringBoot中MyBatis 处理 MySQL5.7 的json字段数据

    万次阅读 多人点赞 2019-04-02 16:10:44
    最近学习过程中遇到一个需要将订单数据存入数据库需求,项目是使用SpringBoot+MyBatis框架,数据库是MySQL,订单数据格式如下: { all_price: 32 beizhu: "暂无" store_id: "1" goods: [ {goods_id: 2,goods_...
  • SQL解析Json字段

    万次阅读 2020-01-19 19:18:16
    存储在JSON型中的JSON文本会被转换成一个支持快速读取的文档元素,这样在使用时不需要再解析文本,并且可以直接通过键和索引访问其中的子对象而无需读取全部文本。 JSON型中可存储的JSON文本的大小不会超过mysql....
  • MySQL5.7 JSON字段性能测试 表结构 JSON字段表 CREATE TABLE `invt_bin_dim_test` ( ​ `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ​ `dim` json DEFAULT NULL, ​ PRIMARY KEY (`id`) ​ ) ENGINE=...
  • JSON类型是MySQL5.7.8中新加入的一种数据类型,并在后续版本尤其是MySQL8.0中得到了大幅增强,现在的JSON类型的功能十分强大,合理使用能让我们的开发更加有效!
  • MySQL大数据表增加字段索引实现

    万次阅读 2017-11-16 14:44:15
    但是直接添加会导致mysql崩溃或者锁表时间太长影响用户操作,所以需要利用其他的方法进行添加,这篇文章主要给大家介绍了MySQL中大数据表增加字段,增加索引的实现过程,需要的朋友可以参考借鉴。 普通的添加字段...
  • 1、mysql5.7开始支持json类型字段; 2、mybatis暂不支持json类型字段的处理,需要自己做处理 第一步:建表 CREATE TABLE rules_json( id INT PRIMARY KEY AUTO_INCREMENT, sys_name VARCHAR(16) NOT NULL ...
  • 不同于oracle,在mysql的Innodb存储引擎中,对索引的总长度有限制。在mysql 5.7中(https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html),默认为3072。 Ifinnodb_large_prefixis enabled (the ...
  • JSON类型的列不能直接创建索引,但可以间接创建索引,可以定义一个 Generated Column列,该列提取JSON字段的部分信息,然后在Generated Column列上创建索引,如以下示例所示: mysql> CREATE TABLE jemp ( -&...
  • 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 SELECT COUNT(*...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,303
精华内容 9,721
关键字:

json字段索引mysql

mysql 订阅