专栏首页菩提树下的杨过mysql虚拟列(Generated Columns)及JSON字段类型的使用

mysql虚拟列(Generated Columns)及JSON字段类型的使用

mysql 5.7中有很多新的特性,但平时可能很少用到,这里列举2个实用的功能:虚拟列及json字段类型

一、先创建一个测试表:

drop table  if exists t_people;

CREATE TABLE t_people(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `profile` json not null ,
  `created_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `updated_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id));

注:这里profile是一个json类型的字段,另db编码采用utf8mb4

二、生成测试数据

delimiter //

-- 写一段存储过程,方便后面生成测试数据
create procedure batchInsert()
 begin 
  declare i int;
  declare v_name varchar(50);
  declare v_profile varchar(100);
  set i=0;
  while i<100000 do
  	 set v_name = concat(substring('赵钱孙李周吴郑王张杨',floor(1+(rand()*10)),1),substring('菩提树下的杨过',floor(1+(rand()*7)),1),substring('我爱北京天安门',floor(1+(rand()*7)),1),i);
  	 set v_profile  = concat("{\"phone\":\"",concat('13',floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9))) , "\",\"age\":",i,"}");
  	 insert into t_people(`name`,profile) values(v_name,v_profile);
	 set i=i+1;
  end while;
 end; //

注:这段存储过程不是本文重点,看不懂的同学不用深研,大概意思就是name随机生成,profile随机生成一个类似{"phone":"13xxxxxx","age":x}的内容。

调用一下这个存储过程,生成100000条测试数据,数据大致长下面这样:

需求来了,假如我们要查姓“张”的人有多少个?

这显然是一个全表扫描!

三、前缀索引

肯定有同学想到了,在name上建一个前缀索引,只对name的第1个字做索引

alter table t_people add key ix_name(name(1));

确实是个好办法,效果也不错

但是需求总是变化的,如果想查第2个字是“杨”的人有多少?

依然会全表扫描。

四、虚拟列

alter table t_people add second_name varchar(3) generated always as(substring(name,2,1)) stored;

创建了一个虚拟列second_name,其值是substring(name,2,1),即name中的第2个字,最后的stored表示,数据写入时这个列的值就会计算(详情可参考最后的参考链接)

注:虚拟列并不是真正的列,insert时也无法指定字段值。

然后在这个列上创建索引:

alter table t_people add index ix_second_name(`second_name`);

再来看下执行计划,索引生效了,扫描行数也明显下降。

五、json检索

又来新需求了:要查profile中手机号为13589135467,并且姓“吴”的人

注意:profile->"$.phone"=xxx 就是json字段的检索语法

分析执行计划,可以看到前缀索引“ix_name”生效了,但还有优化空间,仍然可以借助虚拟列,创建2个虚拟列phone、first_name,并创建联合索引。

alter table t_people add first_name varchar(3) generated always as(substring(name,1,1)) stored;
alter table t_people add phone varchar(20) generated always as(profile->"$.phone") stored;
alter table t_people add index ix_phone_firstname(phone,first_name);

加了这2个虚拟列后,数据长这样: 

注:phone列提取出来后,前后会带上引号。

刚才的需求,可以改写sql:

select * from t_people where phone='\"13589135467\"' and name like '吴%';

最后看下执行计划:  

扫描行数下降到个位数,效果十分明显。

参考文章:

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

https://dev.mysql.com/doc/refman/5.7/en/json.html

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • scala 学习笔记(04) OOP(上)主从构造器/私有属性/伴生对象(单例静态类)/apply方法/嵌套类

    一、主从构造器 java中构造函数没有主、从之分,只有构造器重载,但在scala中,每个类都有一个主构造器,在定义class时,如果啥也没写,默认有一个xxx(...

    菩提树下的杨过
  • 使用NUnit在.Net编程中进行单元测试

    原文地址:http://www.microsoft.com/china/community/Column/59.mspx 引言: 举一个可能会发生在...

    菩提树下的杨过
  • lombok在IntelliJ IDEA下的使用

    lombok是一款可以精减java代码、提升开发人员生产效率的辅助工具,利用注解在编译期自动生成setter/getter/toString()/constru...

    菩提树下的杨过
  • [Python] 程序结构与控制流

    如果不需要执行任何操作,可以省略条件语句的else和elif子句。如果特定子句下不存在要执行的语句,可以使用pass语句。

    py3study
  • 阿里 RPC 框架 DUBBO 初体验

    最近研究了一下阿里开源的分布式RPC框架dubbo,楼主写了一个 demo,体验了一下dubbo的功能。

    haifeiWu
  • 深入理解Dubbo源码(二),分析Java SPI与Dubbo SPI的实现源码

    我在上一篇说了句:为什么我能短短几个晚上的时间就能看懂。dubbo不是这么容易完全看懂的,实际上我从国庆之前就开始一点点去了解dubbo,当前我所说的看懂,也只...

    Java艺术
  • 利用Theano理解深度学习——Auto Encoder

    注:本系列是基于参考文献中的内容,并对其进行整理,注释形成的一系列关于深度学习的基本理论与实践的材料,基本内容与参考文献保持一致,并对这个专题起名为“利用The...

    zhaozhiyong
  • 2.0时代,如何做好等保?

    等保全称叫信息安全等级保护,是对信息和信息载体按照重要性等级分级别进行保护的一种工作。今天我们就来说一说等保的历史发展,以及个人信息保护相关的一些内容。

    云大学小编
  • 如何在小程序中实现人脸识别功能

    本文将介绍在小程序端,使用腾讯云云智AI应用服务来进行人脸识别检测分析,实现人脸识别等功能。

    123456a134
  • 【三剑客之一】Dubbo 遇到初恋

    很多时候,其实我们使用这个技术的时候,可能都是因为项目需要,所以,我们就用了,但是,至于为什么我们需要用到这个技术,可能自身并不是很了解的,但是,其实了解技术的...

    好好学java

扫码关注云+社区

领取腾讯云代金券