mysql 5.7 新特性之 json 类型 创建索引
参考:https://blog.csdn.net/bugs4j/article/details/79932538
mysql原生并不支持json列中的属性索引,但是我们可以通过mysql的虚拟列间接的为json中的某些属性创建索引,原理就是为json中的属性创建虚拟列,然后通过给虚拟列建立索引,从而间接的给属性创建了索引。
在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。
如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式
许你写的格式如下:
fieldname <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
首先我们创建一张带有虚拟列的表新表:
-
CREATE TABLE players (
-
id INT UNSIGNED NOT NULL primary key auto_increment,
-
player JSON NOT NULL,
-
vname VARCHAR(50) GENERATED ALWAYS AS (`player` ->> '$.name') NOT NULL -- name的虚拟列
-
);
利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段,我把它定义成不可以为空。在实际的工作中,需要结合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。
然后我们查一下这个表的列有哪些:
SHOW COLUMNS FROM `players`;
结果如下,会发现vname的附加信息里面显示列类型为虚拟生成列:
然后,我们写一个存储过程,向表中插入八百万条记录,写之前先执行确认开启mysql存储过程:
-
show variables like 'log_bin_trust_function_creators'; -- 查看是否开启存储函数
-
set global log_bin_trust_function_creators=1; -- 开启mysql存储函数
存储过程:
-
delimiter $$
-
create procedure insert_player(in max_num int(10))
-
begin
-
declare i int default 0;
-
declare json_data varchar(2000) default '1';
-
set autocommit= 0;
-
repeat
-
set i=i+1;
-
set json_data = concat(concat('{"name":"yaoming-',i),'","age":34,"gender":"man","type":"basketBall"}');
-
insert into players (id,player) values(null,json_data);
-
until i=max_num end repeat;
-
commit;
-
end $$
调用一下存储过程,插入2000000条记录:
call insert_player(2000000);
插入完毕,总共耗时:
在添加索引之前我们先通过vname直接查询name为yaoming-990099的那条记录,总共耗时3.107s:
我们看一下这条语句的查询计划:
EXPLAIN SELECT * FROM `players` WHERE `vname` = "yaoming-990099"
结果如下:
然后我们为vname添加索引:
CREATE INDEX `name_idx` ON `players`(`vname`);
再次查询这条语句的执行计划:
速度已经快到飞起了!!!
我们在看一下查询计划详情:
发现已经走了索引查询!
但是需要注意的是,这种方法只能只能对json的某个单值属性或者给数组中的某一个特定位置上的元素有效,如果你想给对象数组中的所有元素的某个属性使用索引,那么最后你只能匹配数组中的所有对象的该属性或者使用like,但是使用like的话就不能使用索引,也就是说对于这种情况是不可用的。
相关文章
- 测试环境治理之MYSQL索引优化篇
- 深入理解 MySQL 索引
- 为什么 MySQL 采用 B+ 树作为索引?
- 找到 mysql 数据库中的不良索引
- MySQL 聚簇索引与非聚簇索引的理解
- 【MySQL进阶-03】深入理解mysql的索引分类,覆盖索引,覆盖索引失效,回表,MRR
- MySQL not exists 真的不走索引么
- MySQL查看/修改/删除索引
- 基于 PHP + MySQL 图书库存管理系统【100010765】
- 基于Java(SSM)+MySQL实现(Web)校园二手交易市场【100010081】
- MySQL数据库索引的底层原理(二叉树、平衡二叉树、B-Tree、B+Tree)
- 转 MySQL的一级索引和二级索引介绍
- ubuntu16.04系统彻底卸载mysql,并源码免编译重装MySQL的步骤
- MySQL面试题之为什么要为innodb表设置自增列做主键?
- Mysql B+树索引的推演
- mysql-计算排名
- MySQL 性能优化的最佳20多条经验分享(一)(转)
- mysql 行转列
- MySQl数据库常用的DOS命令
- MySQL第五个学习笔记 该数据表的操作
- mysql导入报错 [Err] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'
- 【转】mysql索引的探究
- (1.2)mysql 索引概念
- MySQL聚簇索引
- mysql 快速迁移数据 mysqldump