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

Generated Column:

Generated Column是MySQL 5.7引入的新特性，所谓Cenerated Column，就是数据库中这一列由其他列计算而得。在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column（虚拟生成的列）和Stored Generated Column（存储生成的列），二者含义如下：
1、Virtual Generated Column（虚拟生成的列）：不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。InnoDB支持Virtual Generated Column，具体参考“https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html”
2、Stored Generated Column（存储生成的列）：存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual Column相比并没有优势。因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column
在表中允许Virtual Column和Stored Column的混合使用
提高效率：由于mysql在普通索引上加函数会造成索引失效，造成查询性能下降，Generated Column（函数索引）刚好可以解决这个问题，可以在Generated Column加上索引来提高效率.

一、先创建一个测试表：

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

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

在这里插入图片描述
当然，sql语句也可以改成：

explain select count(0) from t_people where 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