mysql generated字段_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条测试数据,数据大致长下面这样:

efc1edadd3257a3c2bbd3dc6dead5dea.png

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

26226f0f64e3c3536fb8132e89ba31e2.png

这显然是一个全表扫描!

三、前缀索引

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

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

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

d3821fdda020ba37699e843192b87310.png

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

c4fdd614f545659f1edf6d62068f5289.png

依然会全表扫描。

四、虚拟列

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`);

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

d6ec48d67294b2011061ff01f8a7a54e.png

当然,sql语句也可以改成:

explain select count(0) from t_people where second_name='杨';

这样看上去更直观,效果不变。

五、json检索

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

4fa0a8eabfe1a2b0651ffd8c3cc23bd1.png

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

711681b81ebcd78917cdc988c71f8b37.png

分析执行计划,可以看到前缀索引“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个虚拟列后,数据长这样:

ef28560cca38025b5864f06f21a144cb.png

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

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

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

最后看下执行计划:

6fd46b5e2691a755d91b855e146898e1.png

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

参考文章:

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值