共计 1514 个字符,预计需要花费 4 分钟才能阅读完成。
1.前言
Mysql的json类型问世以后,弥补了一些关系型数据库存储对象结构的不足,同时提供了一堆JSON Function
乍一看,还挺全乎,但是笔者发现常用的并不多(就笔者的使用情况而言),笔者用的最多的也就是JSON_EXTRACT,也可以用箭头函数。
2.Json泛滥的困扰
随着表的体量不断扩大,Json类型的字段层出不穷,业务查询时经常要查阅下存储的Json字典,瞅一瞅我要过滤什么条件,最重要的是,这玩意还不能直接走索引!数据量一上来就要凉凉。好在Mysql程序员也发现了这个问题,Mysql提供了Generated Columns的技术,可以理解为给定一个表达式,生成一个列。看看官方的DDL
[success]col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT ‘string‘][/success]
有2种生成列的存储方式VIRTUAL以及STORED,再往下看
[success]
The VIRTUAL
or STORED
keyword indicates how column values are stored, which has implications for column use:
VIRTUAL
: Column values are not stored, but are evaluated when rows are read, immediately after anyBEFORE
triggers. A virtual column takes no storage.InnoDB
supports secondary indexes on virtual columns. See Section 13.1.18.8, “Secondary Indexes and Generated Columns”.STORED
: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.
[/success]
VIRTUAL类型列不需要额外分配存储空间,InnoDB存储引擎支持在此类型上添加第二索引;STORED需要额外的存储空间,但是直接就可以索引,以空间换时间的思想。这里第二索引可以理解为非主键索引,也就是非聚簇索引。使用InoDB存储引擎,我们可以使用VIRTUAL类型的生成列,INSERT和UPDATE相比较普通索引多了一步计算,但是节省了存储空间,而且查询时我可能还更快,因为我是VIRTUAL的,我是提前算好的,普通的索引还有回读的开销。OK,简单看看怎么添加VIRTUAL Columns,假设有张表my_table有个json列send_data
[info]{“id”: 1, “name”: “GouDan”, “age”: 18, “sex”: 1}[/info]
现在以send_data里的id添加的VIRTUAL列,VIRTUAL|STORE默认不写就是VIRTUAL
[info]alter table my_table add data_id int generated always as (send_data->’$.id’)[/info]
到这里,虚拟列data_id就生成了!后续可以根据需要建立合适的索引
3.小结
本文简述了Mysql的生成列技术,通过建立虚拟列构建二级索引对json字段的查询优化非常有效,同时也可以避免经常编写过于复杂的JSON函数嵌套。
生产环境还是物理存储比较靠谱,Mysql计算列目前还是存在问题的,慎用