专栏首页SEian.G学习记录MySQL如何给JSON列添加索引(二)

MySQL如何给JSON列添加索引(二)

上一篇文章《MySQL 8.0 JSON增强到底有多强?(一)》,我们简单介绍了MySQL中JSON数据类型,相信大家对JSON数据类型有了一定的了解,那么今天我们来简单看下如何在JSON列上添加索引?

InnoDB支持虚拟生成列的二级索引。不支持其他索引类型。在虚拟列上定义的二级索引有时称为“虚拟索引”。

二级索引可以在一个或多个虚拟列上创建,也可以在虚拟列和常规列或存储的生成列的组合上创建。包含虚拟列的二级索引可以定义为UNIQUE。

在虚拟生成的列上创建辅助索引时,生成的列值将在索引的记录中具体化。如果索引是覆盖索引(包含查询检索到的所有列的索引),则从索引结构中的物化值检索生成的列值,而不是“动态”计算。

在虚拟列上使用二级索引时,由于在操作期间INSERT和 UPDATE操作期间在二级索引记录中实现虚拟列值时要执行计算,因此要考虑额外的写入成本。即使有额外的写入成本,虚拟列上的二级索引也可能比生成的存储列更好,后者在聚簇索引中实现,从而导致需要更多磁盘空间和内存的较大表。如果未在虚拟列上定义二级索引,则读取会产生额外成本,因为每次检查列的行时都必须计算虚拟列值。

对索引的虚拟列的值进行MVCC记录,以避免在回滚或清除操作期间对生成的列值进行不必要的重新计算。对于 COMPACT和REDUNDANT格式,记录值的数据长度受索引键限制767字节,对于DYNAMIC和 COMPRESSED列格式,受索引键限制3072字节。

在虚拟列上添加或删除二级索引是就地操作。

通过索引生成列以提供JSON列索引

JSON 不能直接对列进行索引。要创建间接引用此类列的索引,可以定义一个生成列,该列提取应建立索引的信息,然后在生成的列上创建索引,如下所示:

说明:8.0和5.7都支持在生成列上添加索引

mysql>CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.01 sec)
 
查看表结构:
 
8.0表结构:
mysql >show create table jemp\G
*************************** 1. row ***************************
       Table: jemp
Create Table: CREATE TABLE `jemp` (
  `c` json DEFAULT NULL,
  `g` int GENERATED ALWAYS AS (json_extract(`c`,_utf8mb3'$.id')) VIRTUAL,
  KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
5.7表结构:
mysql>show create table jemp\G
*************************** 1. row ***************************
       Table: jemp
Create Table: CREATE TABLE `jemp` (
  `c` json DEFAULT NULL,
  `g` int(11) GENERATED ALWAYS AS (json_extract(`c`,'$.id')) VIRTUAL,
  KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 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.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
 
mysql >select * from jemp;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "1", "name": "Fred"}   |    1 |
| {"id": "2", "name": "Wilma"}  |    2 |
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+
4 rows in set (0.00 sec)
 
 
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)

通过上述查看执行计划,可以看到使用到了我们在生成列上创建的索引;

当EXPLAIN在SELECT包含一个或多个使用->or->> 运算符的一个 或其他SQL语句上使用时 ,这些表达式将使用JSON_EXTRACT()和(如果需要)转换为它们的等效项JSON_UNQUOTE(),如SHOW WARNINGS输出所示:

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(`wjqdb`.`jemp`.`c`,'$.name')) AS `c->>"$.name"` from `wjqdb`.`jemp` where (`wjqdb`.`jemp`.`g` > 2) order by json_extract(`wjqdb`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

在MySQL 8.0.21和更高版本中,还可以JSON使用JSON_VALUE()带有表达式的函数在列上创建索引,该表达式可用于优化使用该表达式的查询;

后面文章我们会介绍如何在 JSON数组上创建索引以及JSON数据类型涉及到的函数等,敬请期待。。。

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEianG

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-05-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 8.0 新特性:多值索引 --如何给JSON数组添加索引(三)

    上一篇文章《MySQL如何给JSON列添加索引(二)》中,我们介绍了如何给JSON列添加索引,那么接下来,我们看下如何给JSON数组添加索引?

    SEian.G
  • Mysql如何给字符串添加索引(前缀索引)

    在日常开发中,我们经常给字符串添加索引,那么给字段添加索引有什么技巧吗,我们看看下面的例子,我们给一个邮箱添加索引,应该如何添加呢

    小土豆Yuki
  • MySQL调优系列——Mysql如何为表字段添加索引?

    须臾之余
  • mysql快速给列加索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ...

    用户1220053
  • mysql 创建 主键索引 唯一索引 全文索引 多列索引 添加索引

    ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    bear_fish
  • MFC 如何给ComboBox下拉框控件添加item,如何添加顺序索引的数据项?

    首先参考博客:https://blog.csdn.net/smtrobot/article/details/49306727

    acoolgiser
  • MySQL · 最佳实践 · 如何索引JSON字段

    原文地址: http://mysql.taobao.org/monthly/2017/12/09/

    二狗不要跑
  • MySQL8.0 JSON函数之创建与返回JSON属性(四)

    经过前面三篇的文章的介绍,相信大家已经对MySQL JSON数据类型有了一定的了解,为了在业务中更好的使用JSON类型,今天我们来具体介绍一下JSON函数的使用...

    SEian.G
  • oracle mysql5.7 Json函数

    oracle mysql 5.7.8 之后增加了对json数据格式的函数处理,可更加灵活的在数据库中操作json数据,如可变属性、自定义表单等等都使用使用该方式...

    兜兜毛毛
  • 如何给DataGrid添加自动增长列

    如何给DataGrid添加自动增长列 我想我们都知道在数据库中如何添加自增长列,我们可以将这个自增长列绑定到DataGrid上使得用户方便的知道现在是第几行,今...

    阿新
  • MySQL8.0 JSON函数之搜索JSON值(五)

    之前的几篇文章介绍了JSON数据类型,相信大家已经对JSON有了一定的了解,上面一篇文章介绍了《MySQL8.0 JSON函数之创建与返回JSON属性(四)》J...

    SEian.G
  • MYSQL8 处理JSON 我不再是豆包,我是干粮

    最近来了一个项目,本身如果用MONGODB 有点大材小用,所以为了避免某些表继续使用text字段来处理JSON 数据的方式,让技术水平上一个档次,并且公司也不在...

    AustinDatabases
  • 【问答】MySQL如何给字段添加注释

    如上面的创建表语句,字段 s_no 和 s_name 后面通过关键字 comment 来给对应的字段添加注释。

    用户1260737
  • 如何用命令行给mySQL添加用户

    命令行netstat -ao | find “3306”, 查看MySQL默认端口号3306对应的进程id(pid)为30016:

    Jerry Wang
  • MySQL 8.0中的新增功能

    原文:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/

    shaonbean
  • MySQL - EXPLAIN详解

    EXPLAIN: 为 SELECT语句中使用到的每个表返回一条信息。它按照MySQL在处理语句时读取它们的顺序列出这些表。MySQL使用循环嵌套算法解析所有连接...

    battcn
  • MySQL 8.0 JSON增强到底有多强?(一)

    我们都知道,从5.7版本开始,MySQL 支持 RFC7159定义的原生JSON数据类型,该类型支持对JSON文档中的数据的有效访问。关于MySQL 8.0 J...

    SEian.G
  • 关于mysql给列加索引这个列值中有null的情况

    在需求中由于要批量查数据,且表中数据量挺大(2300万条记录) 且查询条件的这两个字段没有加索引,为了增加查询速度,现在需要去为这两个字段添加索引。 刚开始...

    居士
  • MySQL 5.7 JSON 实现简介

    本文主要介绍了MySQL在5.7.7之后引入的原生JSON支持的特性,说明了引入JSON类型的好处,并结合具体的示例介绍了MySQL在JSON类型上对外的接口以...

    腾讯云数据库团队

扫码关注云+社区

领取腾讯云代金券