专栏首页MySQLBeginnerMySQL 5.7新特性| Json Column和Generated Column(下)

MySQL 5.7新特性| Json Column和Generated Column(下)

JSON 字段索引以及 Generated 字段

JSON 字段类型在当前的版本中自身没有索引,那么在生产中是非常可怕的,JSON 字段的增、删、改、查效率可想而知,基本没法用,也许是基于此,MySQL5.7 中提供了 Generated 字段类型,网上有叫生成列或是计算列的。这里先来了解一下什么是 Generated Column。

1、Generated Column 介绍

Generated Column 是 MySQL 5.7.6 引入的新特性,所谓 Cenerated Column,就是数据库中这一列由其他列计算而得。引用官方参考手册中的例子予以说明:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

Generated Column 有两种,即 Virtual Generated Column 和 Stored Generated Column,前者只将 Generated Column 保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将 Generated Column 持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得到的数据,需要更多的磁盘空间,与 Virtual Column 相比并没有优势,因此,MySQL 5.7中,不指定 Generated Column 的类型,默认是 Virtual Column。虽然一般情况下都应该使用 Virtal Generated Column,但是,目前使用 Virtual Generated Column 还有很多限制:不能用作主键、不能作为主键、不能创建全文索引和空间索引等,但是在后续的版本中可能支持,所以如果使用 Generated Column 字段做索引的话,还是使用 Stored Generated Column 吧,在使用Generated Column 做索引上,JSON 字段索引的解决方案,官方也是推荐使用 Stored Generated Column。使用Stored Generated Column 建表语句如下,只是在加了个单词:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED)
);

2、Generated Column 注意事项

Generated Column 是不能进行写操作的,它是自动生成的;在创建的时候要考虑这个列的计算公式是否合理,不合理的话创建的时候不会报错,使用时插入值就会报错;Generated Column 依赖的列在删除的时候会提示报错,必须先删除 Generated Column 才能再去删除它依赖的列;Generated Column 定义不合法,如我们将 generated column 定义为 "x列+y列",很明显,x列或y列都是数值型,如果我们将x列或y列定义(或修改)为字符型,则预期会报错,但实际上我们可以正常创建,但是在插入的时候是会报错的。

3、利用Generated Column 给 JSON 字段添加索引

正常情况下,JSON 字段的相关查询是扫描全表的,因为JSON字段本身不能创建索引的,我们利用 Generated Column 特性,对 JSON 字段中相关 key 作为 Generated Column 来做生成列,然后对 Generated Column 做索引:

ALTER TABLE json_test ADD COLUMN age INT AS 
(JSON_EXTRACT(user_info,'$.age')) STORED,
 ADD KEY idx_age (age); 

前后对比图如下:

可以很明显的看出,使用 Generated Column 并添加索引后,查询 JSON 字段中的值使用索引。

结束语

MySQL5.7 中 Generated Column 和 JSON Column 的出现,使一些场景替代 MongoDB 等 NoSQL 提供了可能,虽然整体上还没有 MongoDB 等做的那么强大,但相信以后使用这两种类型的场景会越来越多, 同时对 DBA 的挑战也越来越大,希望密集使用 JSON 类型业务使用独立的 MySQL 实例来运行,以免 JSON 成为大字段(存储在 JSON文档的大小 JSON 列被限制为值 max_allowed_packet 的系统变量)时对其他业务带来影响。

本文分享自微信公众号 - MySQLBeginner(MySQLBeginner)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-12-07

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python爬虫实例:爬取B站《工作细胞》短评——异步加载信息的爬取

    右边 li 标签中的就是短评信息,一共20条。一般我们加载大量数据的时候,都会做分页,但是这个页面没有,只有一个滚动条。

    丹枫无迹
  • 3.Elasticsearch面向文档

    在应用程序中对象很少只是一个简单的键和值的列表。通常,它们拥有更复杂的数据结构,可能包括日期、地理信息、其他对象或者数组等。

    IT云清
  • 2.Elasticsearch和Java交互 -- java操作es

    如果你正在使用 Java,在代码中你可以使用 Elasticsearch 内置的两个客户端:

    IT云清
  • Elasticsearch中删除数据

    允许从基于其id的特定索引中删除一个JSON文档,下面的示例从twitter中删除类型为_doc的JSON文档,其id为1:

    IT云清
  • Struts2中的Ajax请求

    1. 使用Stream result的方式以流的形式写出到客户端。(这种方式我没有亲自做实验,下面的例子参考的是Struts2的官方文档)

    TheOneGIS
  • 15.Elasticsearch更新文档1---全文更新---restAPI

    在 Elasticsearch 中文档是 不可改变 的,不能修改它们。 相反,如果想要更新现有的文档,需要 重建索引 或者进行替换, 我们可以使用相同的 ind...

    IT云清
  • 实战项目二: 实现CSDN自动点赞

    环境: Python3.6.5 编译器: Sublime Text 3 代码: GitHub 联系方式: ke.zb@qq.com 第三方库: sele...

    明天依旧可好
  • json转map

    IT云清
  • MongoDB导入Shapefile数据

    两种解决方案: 一、将整个shapefile转为GeoJSON然后直接导入mongoDB数据库中 首先,将shapefile数据转为WGS84地理坐标,...

    TheOneGIS
  • 爬来爬去(四):脉脉职言

    脉脉是一个实名职场社交平台。之前爬了脉脉职言版块,大概爬了4027条评论,本文对爬取过程给出详细说明,对于评论内容仅做可视化分析,之前存了一堆这方面的文章,今天...

    量化小白

扫码关注云+社区

领取腾讯云代金券