文档章节

Mysql5.7学习之json类型(二)

Mr_Qi
 Mr_Qi
发布于 03/12 12:51
字数 755
阅读 627
收藏 24
点赞 1
评论 1

背景

上文我们简要介绍了一些关于json的查询语句Mysql5.7学习之json类型(一)

我们也观察到了展示结果和查询其实有双引号的区别。

如果只是这样的查询也太小看mysql了。毕竟随着业务的数量增多 数据极速的膨胀 

能否在json列中使用索引呢???

问题

explain select  * from user where json_extract(data,'$.uid')='wang';

查看执行计划

type为all啥索引都没有 如果我有100w数据岂不……

As noted elsewhere, JSON columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column

 

解析

如果我们常见的查询条件都是某个对应字段 比如$.name

首先我们可以给对应的列加上虚拟列

虚拟列给我们解决了一个困扰许久的问题

很久之前我们就有需要基于数据处理的索引

比如表如下

CREATE TABLE `ontime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FlightDate` date DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FlightDate` (`FlightDate`)
) ENGINE=InnoDB

需要统计一些信息的时候如下

EXPLAIN SELECT carrier, count(*) FROM ontime_sm
        WHERE dayofweek(FlightDate) = 7 group by carrier
   id: 1
  select_type: SIMPLE
        table: ontime_sm
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 151253427
        Extra: Using where; Using temporary; Using filesort
Results:
32 rows in set (1 min 57.93 sec)

有个简单的方法可以建立触发器 在做插入或者更新的时候直接更新某个字段 直接使用该字段进行查询

CREATE DEFINER = CURRENT_USER
TRIGGER ontime_insert
BEFORE INSERT ON ontime_sm_triggers
FOR EACH ROW
SET
NEW.Flight_dayofweek = dayofweek(NEW.FlightDate);

哈哈 当有了虚拟列的时候

直接这样

CREATE TABLE `ontime_sm_virtual` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FlightDate` date DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `Flight_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `Flight_dayofweek` (`Flight_dayofweek`),
) ENGINE=InnoDB

逆天了===》函数列 并且更重要的是支持索引!!!

这样的话我们完全可以

EXPLAIN SELECT carrier, count(*) FROM ontime_sm_virtual  WHERE Flight_dayofweek = 7 group by carrier
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ontime_sm_virtual
   partitions: NULL
         type: ref
possible_keys: Flight_dayofweek
          key: Flight_dayofweek
      key_len: 2
          ref: const
         rows: 165409
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort

这种结果真真是太棒了!!!

 

结论

这个和json结合起来使用真是太棒了吧!!!

以刚才的表为例

我们使用虚拟列创建name_virtual

ALTER TABLE `f6db_20160522`.`user`
ADD COLUMN `virtual_name` varchar(20) GENERATED ALWAYS AS (data->>"$.name") VIRTUAL NULL AFTER `data`;

这样简直完美 

select UID,virtual_name from user where virtual_name is not null

完全看不出来这是个json表呢

© 著作权归作者所有

共有 人打赏支持
Mr_Qi
粉丝 251
博文 298
码字总数 312931
作品 0
南京
程序员
加载中

评论(1)

徒伤悲
徒伤悲
这个特性太棒了
【原创】MySQL5.7 JSON类型使用介绍

JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写。对机器来说易于解析和生成,并且会减少网络带宽的传输。 JSON的格式非常简单:名称...

david_yeung ⋅ 2015/11/10 ⋅ 0

mysql5.7:mysql安装和基于SSL加密的主从复制(详细剖析)

小生博客:http://xsboke.blog.51cto.com 小生 Q Q:1770058260 -------谢谢您的参考,如有疑问,欢迎交流 目录: --------mysql-5.7.13简介及安装 --------配置mysql-5.7.13的ssl加密传输 ...

小生博客 ⋅ 2017/05/07 ⋅ 0

**关于mysql5.7版本新特性介绍 ------数据类型 JSON**

关于mysql5.7版本新特性介绍 ------数据类型 JSON 测试环境: 内容简介: 随着mysql5.7版本的到来,大家对其的热情也越来越高涨,身为mysql圈子里的一员,我本身也对mysql5.7的一些新特性有所...

红隐 ⋅ 2017/12/22 ⋅ 0

MySQL5.7之Json Column和Generated Column

MySQL5.7新增两种字段类型:Json和Generated,Generated型的产生和Json的关系密不可分,如果没有Generated类型,Json类型在强大,生产中可能也无法使用,因为Json不支持索引,但是如果要查询...

lover007 ⋅ 2016/12/07 ⋅ 0

Mysql5.7学习之json类型(四)

背景 既然说到了json的虚拟列 那么必然会涉及到虚拟列的索引的创建 我们可以简单的比较一下和实体表的区别 首先我们使用 Mysql批量填充随机数据方法进行数据的填充 方案 创建表2 直接使用表1...

Mr_Qi ⋅ 03/20 ⋅ 0

MySQL5.7 JSON实现简介

版权声明:本文由吴双桥原创文章,转载请注明出处: 文章原文链接:https://www.qcloud.com/community/article/205 来源:腾云阁 https://www.qcloud.com/community 本文主要介绍在MySQL 5.7...

偶素浅小浅 ⋅ 2016/11/11 ⋅ 0

mysql5.7新特性JSON数据类型解析

废话不多说,直接上实例。 一、json结构 创建测试表 CREATE TABLE ( int(11) unsigned NOT NULL AUTO_INCREMENT, json NOT NULL, json NOT NULL, PRIMARY KEY ()) ENGINE=InnoDB AUTO_INCREM......

hgditren ⋅ 01/03 ⋅ 0

mysql5.7 关于JSON插入的问题(等一个大佬)?

mysql5.7 的JSON 如果初始数据为NULL 无法使用JSONSET插入数据 必须添加一条空的数据才行 比如 {} 才能使用 JSONSET 或者JSONINSERT 怎么样才能避免先插入一条空数据才能使用 JSONSET 或者 ...

蜗牛狠牛 ⋅ 2017/09/29 ⋅ 1

Mybatis-Plus 1.4.7,新增验证字段策略枚举类

Mybatis-Plus是一款MyBatis的增强工具包,简化 CURD 操作。启动加载 XML 配置时注入单表 SQL 操作 ,为简化开发工作、提高生产率而生。Mybatis-Plus 启动注入非拦截实现、性能更优。 Demo S...

青苗 ⋅ 2016/09/28 ⋅ 6

自定义字段的设计与实现(Java实用版)

前言 自定义字段又叫做“开放模型”,用户可以根据自已的需求,添加需要的字段,实现个性化定制。 使用自定义字段的目的,使用自定义字段解决哪些问题 如现有一套CRM系统,客户模块中客户信息...

ytangdigl ⋅ 2017/04/12 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

vim基础-编辑模式-命令模式

编辑模式:可以编辑修改文件。编辑模式下 按“esc”键返回一般模式。 按一次“Insert”键 (一般在键盘回格键右边)作用和“i”一样表示“插入”。按两次“Insert”键表示“替换”,作用为:...

ZHENG-JY ⋅ 15分钟前 ⋅ 0

MaxCompute读取分析OSS非结构化数据的实践经验总结

摘要: 本文背景 很多行业的信息系统中,例如金融行业的信息系统,相当多的数据交互工作是通过传统的文本文件进行交互的。此外,很多系统的业务日志和系统日志由于各种原因并没有进入ELK之类...

阿里云云栖社区 ⋅ 19分钟前 ⋅ 0

Linux操作系统有何优势?Linux学习

  当今世界流行的操作系统有3大类,Linux、Mac OS和Windows操作系统,Linux操作系统因其开源、免费、跨平台、良好的界面等特性,深受广大程序员们的青睐!   Linux操作系统被广泛的应用于...

老男孩Linux培训 ⋅ 21分钟前 ⋅ 0

Spring Cloud Spring Boot mybatis分布式微服务云架构 开发Web应用

静态资源访问 在我们开发Web应用的时候,需要引用大量的js、css、图片等静态资源。 默认配置 Spring Boot默认提供静态资源目录位置需置于classpath下,目录名需符合如下规则: /static /pub...

itcloud ⋅ 25分钟前 ⋅ 0

6月19日任务 设置更改root密码、连接mysql、mysql常用命令

13.1 设置更改root密码 1. /usr/local/mysql/bin/mysql -uroot 设置环境变量 : export PATH=$PATH:/usr/local/mysql/bin/ 永久生效: vim /etc/profile 加入 export PATH=$PATH:/usr/local/m......

吕湘颖 ⋅ 27分钟前 ⋅ 0

MaxCompute读取分析OSS非结构化数据的实践经验总结

摘要: 本文背景 很多行业的信息系统中,例如金融行业的信息系统,相当多的数据交互工作是通过传统的文本文件进行交互的。此外,很多系统的业务日志和系统日志由于各种原因并没有进入ELK之类...

猫耳m ⋅ 28分钟前 ⋅ 0

Spring MVC controller,return重定向redirect:

@RequestMapping(value="/save",method=RequestMethod.POST)public String doSave(Course course) {log.debug("Info of Course");log.debug(ReflectionToStringBuilder.toStr......

颖伙虫 ⋅ 36分钟前 ⋅ 0

JavaSE——线程介绍

声明:本栏目所使用的素材都是凯哥学堂VIP学员所写,学员有权匿名,对文章有最终解释权;凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。 线程: 介绍:管线程叫多任务处理,首先你得知道...

凯哥学堂 ⋅ 39分钟前 ⋅ 0

ORM——使用spring jpa data实现逻辑删除

前言 在业务中是忌讳物理删除数据的,数据的这个对于一个IT公司可以说是最核心的资产,如果删除直接就物理删除,无疑是对核心资产的不重视,可能扯的比较远,本文最主要是想通过spring jpa ...

alexzhu592 ⋅ 45分钟前 ⋅ 0

CDN caching

Incapsula应用感知CDN使用智能分析和频率分析来动态缓存内容,并最大限度地提高效率。确保可直接从RAM获取最常访问的资源,而不依赖于较慢的访问机制。 1、 静态内容缓存 Incapsula缓存静态内...

上树的熊 ⋅ 48分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部