文档章节

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

Mr_Qi
 Mr_Qi
发布于 03/12 12:51
字数 755
阅读 635
收藏 24

背景

上文我们简要介绍了一些关于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

Mr_Qi

粉丝 273
博文 350
码字总数 359193
作品 0
南京
程序员
加载中

评论(1)

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

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

david_yeung
2015/11/10
0
0
**关于mysql5.7版本新特性介绍 ------数据类型 JSON**

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

红隐
2017/12/22
0
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
0
Mysql5.7学习之json类型(四)

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

Mr_Qi
03/20
0
0
MySQL5.7 JSON实现简介

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

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

没有更多内容

加载失败,请刷新页面

加载更多

Bash各类扩展详解

Bash各类扩展详解 Bash中主要包括大括号扩展、波浪号扩展、变量扩展、子命令扩展、文件名扩展和算数扩展。这些扩展组合在一起为Bash带来了极大的易用性。掌握这些扩展的用法和功能,能够为B...

小陶小陶
37分钟前
1
0
EventBus原理深度解析

一、问题描述 在工作中,经常会遇见使用异步的方式来发送事件,或者触发另外一个动作:经常用到的框架是MQ(分布式方式通知)。如果是同一个jvm里面通知的话,就可以使用EventBus。由于Event...

yangjianzhou
今天
5
0
OpenCV图像处理实例:libuv+cvui显示摄像头视频

#include <iostream>#include <opencv2/opencv.hpp>#define CVUI_IMPLEMENTATION#include <cvui.h>extern "C"{#include <uv.h>}using namespace std;#define WINDOW_NAM......

IOTService
今天
1
0
openJDK之JDK9的String

1.openJDK8的String 先来看下openJDK8的String的底层,如下图1.1所示: 图1.1 底层上使用的是char[],即char数组 每个char占16个bit,Character.SIZE的值是16。 2.openJDK9中的String 图2.1...

克虏伯
今天
1
0
UEFI 模式下如何安装 Ubuntu 16.04

作者:知乎用户 链接:https://www.zhihu.com/question/52092661/answer/259583475 来源:知乎 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。 针对UEFI模式下安装U...

寻知者
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部