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 的系统变量）时对其他业务带来影响。