从存储原理到实操，玩转MySQL-JSON

GnightJump
2,469 阅读9分钟

本文主要探究MySQL的NoSQL特性，主要是其对JSON的支持功能的探索

在实际业务中，存在许多可变的字段，使用JSON类型替代text能更好的管理用户信息。

使用MySQL的JSON类型，能便捷的管理数据，同时具备自动验证的能力。

文章主要介绍MySQL的JSON类型：存储原理、性能对比、常见问题、gorm相关操作

特性介绍

当前我们使用的数据库版本主要是 MySQL 5.7 和 MySQL 8.0 ( 使用select version()可以查看当前的数据库版本 )，先简单介绍下二者的特性：

MySQL 5.7（2015年）

  • InnoDB 增强特性， 优化了对临时表的DDL操作
  • 优化器， 查询优化器的重构和增强
  • JSON支持， 引入了众多 JSON 函数，多版本支持程度有所区别
  • 复制优化， 支持多源复制，优化dump，提供master吞吐量

MySQL 8.0（2018年）

  • 强化NoSQL能力，优化JSON支持，添加了两个JSON聚合函数 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() (5.7.22版本也支持该函数)，支持默认值
  • 窗口函数，可用来实现若干新的查询方式，会将结果集按指定的规则进行分区，每个分区可以看作是一个窗口，根据其所属分区内的行数据进行函数计算，无需group by。
  • 默认字符集为 utf8mb4 编码，替代原先latin-1
  • 高可用性，InnoDB 集群提供集成的原生 HA 解决方案
  • 支持隐形索引（Invisible index） ，隐藏索引用于对比查询性能，优化器不会使用隐形索引，不影响索引维护
  • 支持降序索引（descending index）， 原先声明desc也是使用升序索引，可避免group by隐式排序
  • 移除 Query Cache 模块，不再支持Query Cache相关的变量和操作。

底层原理

主要介绍 MySQL 5.7 的JSON实现原理。

JSON存储方式

后续基于mysql-5.7.8-rc展开叙述。

MySQL采用二进制格式存储的 JSON 值，在磁盘存的是doc对象，内含type & value。

doc ::= type value
type ::=
  0x00 |       // small JSON object
  0x01 |       // large JSON object
  0x02 |       // small JSON array
  0x03 |       // large JSON array
  0x04 |       // literal (true/false/null)
  0x05 |       // int16
  0x06 |       // uint16
  0x07 |       // int32
  0x08 |       // uint32
  0x09 |       // int64
  0x0a |       // uint64
  0x0b |       // double
  0x0c |       // utf8mb4 string
  0x0f         // custom data (any MySQL data type)
value ::=
  object  |
  array   |
  literal |
  number  |
  string  |
  custom-data
  • type主要是标识类型（大json对象、小json对象、大json数组、小json数组、literal、int16、uint16、int32、uint32、int64、uint64、double、string、custom自定义类型）；
  • value包含object、array、literal、number、string、custom-data（与type类型对应）；
  • 当需要读取JSON值的时候，二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。当需要操作JSON值的时候，从二进制形式转换到内存中的结构化DOM，并使用JSON值的递归树表示与解析树紧密对应；
  • json对象存储是分层次的，支持同类型的嵌套；

image.png

下面简单说明下object、array、string的内部结构，参考来自源码sql/json_binary.h、sql/json_binary.cc:

JSON-string:

string ::= data-length utf8mb4-data

string采用utf8mb4存储：

image.png

JSON-object、array：

Each JSON value (scalar, object or array) has a one byte type identifier followed by the actual value.

If the value is a JSON object, its binary representation will have a header that contains:

  • the member count

  • the size of the binary value in bytes

  • a list of pointers to each key

  • a list of pointers to each value

If the value is a JSON array, the binary representation will have a header with

  • the element count

  • the size of the binary value in bytes

  • a list of pointers to each value

  • MySQL对JSON对象存储是分段的，存储的最前面为存放当前对象的元素个数，以及整体占的大小。
  • JSON对象的Key索引是有顺序的，先按长度排序，长度相同的按照code point排序。这点可以在修改JSON参数的时候体现，直接在navicat上添加一个Key，保存后会发现key的位置发生了变化。
  • key-entry和value-entry内部存储了对象内的偏移量和大小，当type确定的时候key-entry和value-entry有固定的长度，只需要知道数据起始位置及总数目，就可以快速定位到对应的entry（由于key-entry本身已经是排序过的，可以使用二分法快速定位）、再通过计算偏移量跳转到指定的数值。
  • 搜索的时候，只需要反序列化路径上涉及到的元素，对于读场景很友好。

image.png

  • 由此可以看出对于变长的值（主要是增大）更新，会影响到前后的数值，频繁的更新变长的键值对，会有一定的性能障碍。

字符集

JSON值生成的字符串有一个字符集utf8mb4和一个排序规则 utf8mb4_bin，由于排序规则的字符集的关系，排序是区分大小写的，该特性也适用于 JSON的null、true和 false，它们必须始终以小写形式编写。（但是 SQL 语句是可以不区分，只是存储上区分）

JSON索引

JSON底层是采用二进制存储的，在MySQL 5.7中不支持直接索引，但是支持在生成的列上创建一个索引，选取JSON某个列为标量创建索引。

MySQL NDB Cluster7.5及更高版本支持从JSON列上创建索引，每个表最多支持3JSON列 。

MySQL 8.0还支持函数索引，即定义多个列的函数表达式并作为索引使用。

MySQL 5.7 针对JSON的索引做了优化，具体方式就是通过生成列来实现JSON某个字段的索引。通俗的来说就是针对JSON指定的列抽取出来，通过冗余该字段的方式来实现索引。

目前支持两种生成列形式，即Virtual Generated Column（虚拟生成列）和Stored Generated Column（存储生成列），支持在生成列上定义二级索引（不能与普通列定义联合索引），仅支持本表的非生成列上定义生成列。

  • Virtual Generated Column不会将这一生成列的数据持久化到磁盘上（仅将虚拟列的元数据信息存在于相关系统表中），不支持针对虚拟列进行Update & Insert 的操作。在对应普通列Insert和Update操作时会消耗额外的写负载，因为更新虚拟生成列索引时需要将衍生列值计算出来，并写到索引里；这样就避免了每次读取数据行时都需要进行一次衍生计算。
  • Stored Generated Column 会将数据持久化到磁盘上，在存储生成列上定义索引其实和普通列上定义索引无区别，性能上也不如虚拟索引，会导致聚簇索引变得更大更占空间。

语法如下：

<column_name> <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

注意： 对于虚拟生成列与存储生成列二者是不可以互相转化的，默认生成列采用的是虚拟生成列。

实际效果

以user表为例，结构如下：

attachment是一个json，包含了一个sign_time的列，现在共有100w的数据，需要找出指定sign_time对应的行信息。

无索引的情况：

建立虚拟生成列的索引：

-- 定义虚拟生成列
ALTER TABLE `user` ADD COLUMN `v_sign_time` BIGINT ( 20 ) 
GENERATED ALWAYS AS ( attachment -> '$.sign_time' ) Virtual NULL AFTER attachment;
-- 定义索引
ALTER TABLE `user` ADD INDEX `idx_sign_time` (`v_sign_time`);

通过explain我们也能看出虚拟生成列索引的效果：

性能对比

更新对比

使用text存储JSON字段，依照以下步骤模拟业务中常用的处理流程(MySQL 8.0.31)： 读取对象 -> 反序列化对象 -> 修改数据 -> 更新数据库

func BenchmarkTypeJSON(t *testing.B) {
   start := time.Now() // 获取当前时间
   for i := 0; i < 100; i++ {
      db := client.DB
      sql := types.JSONSet("attachment").Set("auto_renewal", false)
      // UPDATE `user` SET `attachment`=JSON_SET(`attachment`,'$.auto_renewal', false) WHERE id = 1
      db.Model(&model.User{}).Where("id = ?", 1).UpdateColumn("attachment", sql)
   }
   elapsed := time.Since(start)
   fmt.Println("test json time: ", elapsed)
}

func BenchmarkTypeText(t *testing.B) {
   start := time.Now() // 获取当前时间
   for i := 0; i < 100; i++ {
      db := client.DB
      user := &model.UserText{}
      // 获取数据
      db.Find(&user).Where("id = ?", 1)
      attachment := map[string]interface{}{}
      _ = json.Unmarshal([]byte(user.Attachment), &attachment)
      attachment["auto_renewal"] = false
      // 修改数据
      attStr, _ := json.Marshal(attachment)
      db.Model(user).Where("id = ?", 1).Update("attachment", string(attStr))
   }
   elapsed := time.Since(start)
   fmt.Println("test text time: ", elapsed)
}

对比结果：

本次拓展字段不大，还需拓展字段大小的情况，大约有39%的提升

BenchmarkTypeJSON
test json time:  17.3865ms
test json time:  16.276709ms
test json time:  16.057417ms
test json time:  15.872417ms
test json time:  16.167083ms
test json time:  17.072167ms
BenchmarkTypeJSON-10                  1000000000                 0.01708 ns/op
BenchmarkTypeText
test text time:  26.240417ms
test text time:  26.37775ms
test text time:  26.69825ms
test text time:  24.0895ms
test text time:  24.430583ms
test text time:  23.934334ms
test text time:  28.066125ms
BenchmarkTypeText-10                  1000000000                 0.02808 ns/op
BenchmarkTypeJSONSelect

查询对比

对比方法如下

func BenchmarkTypeJSONSelect(t *testing.B) {
   start := time.Now() // 获取当前时间
   for i := 0; i < 100; i++ {
      db := client.DB
      var user *model.User
      // select attachment->'$.UserID' from user where id = 1;
      db = db.Select("attachment->'$.UserID'")
      db.Find(&user, "id = ?", 1)
   }
   elapsed := time.Since(start)
   fmt.Println("test json time: ", elapsed)
}

func BenchmarkTypeTextSelect(t *testing.B) {
   start := time.Now() // 获取当前时间
   for i := 0; i < 100; i++ {
      db := client.DB
      var user *model.UserText
      // select attachment from user_text where id = 1;
      db = db.Select("attachment")
      db.Find(&user, "id = ?", 1)
      attachment := map[string]interface{}{}
      _ = json.Unmarshal([]byte(user.Attachment), &attachment)
   }
   elapsed := time.Since(start)
   fmt.Println("test text time: ", elapsed)
}

对比结果

可以发现采用JSON字段还是具备优势的，效率大概提升32%

BenchmarkTypeJSON
test json time:  18.895375ms
test json time:  15.59925ms
test json time:  14.645458ms
test json time:  14.463167ms
test json time:  13.984917ms
test json time:  14.894084ms
BenchmarkTypeJSON-10          	1000000000	         0.01491 ns/op
BenchmarkTypeText
test text time:  23.704541ms
test text time:  20.954ms
test text time:  22.69775ms
test text time:  23.116875ms
test text time:  19.844834ms
test text time:  22.331292ms
test text time:  21.910209ms
BenchmarkTypeText-10          	1000000000	         0.02192 ns/op
BenchmarkTypeJSONSelect

FAQ

  1. 其他类型与json类型互转规则？

表 11.3 JSON 转换规则

其他类型CAST（其他类型为 JSON）CAST(JSON AS 其他类型)
JSON没变没变
utf8 字符类型 ( utf8mb4, utf8mb3, ascii)该字符串被解析为 JSON 值。JSON 值被序列化为utf8mb4字符串。
其他字符类型其他字符编码被隐式转换为 utf8mb4并按照针对该字符类型的描述进行处理。JSON 值被序列化为utf8mb4字符串，然后转换为其他字符编码。结果可能没有意义。
NULL结果NULL为 JSON 类型的值。不适用。
几何类型通过调用将几何值转换为 JSON 文档 ST_AsGeoJSON()。非法操作。解决方法：将结果传递 给 。CAST(json_val AS CHAR)ST_GeomFromGeoJSON()
所有其他类型生成由单个标量值组成的 JSON 文档。如果 JSON 文档包含目标类型的单个标量值并且该标量值可以转换为目标类型，则成功。否则，返回NULL 并产生警告。

  1. json类型存储及空间上限？

JSON存储所需的空间与LONGBLOB大致相同；存储在JSON列中的任何 JSON 文档的**存储大小**都受限于max_allowed_packet系统变量的值，如果是内存中就可以大于这个值则不受该限制。

  1. json默认值如何设置？

在 MySQL 8.0.13 之前，JSON列不能有非NULL默认值。

注意默认值需要使用小括号create table user(attachment json not null default (''));，否则的话，还是会提示 JSON 字段不允许设置默认值。

实操环节

本小节主要介绍几个常用的JSON函数及Go语言相关交互操作

常用函数范例

内置函数JSON_SET()，JSON_INSERT()，JSON_REPLACE()，JSON_REMOVE()：

  • JSON_SET() 插入值，如果存在则进行覆盖；
update test_json
    set obj_json = JSON_SET(obj_json, '$.key', 'value')
  • JSON_INSERT()插入值，不会覆盖原有值，原有值不变化；
update test_json
    set obj_json = JSON_INSERT(obj_json, '$.key', 'value')
  • JSON_REPLACE()只会覆盖原来有的值，原有没值也不会插入；
update test_json
    set obj_json = JSON_REPLACE(obj_json, '$.key', 'value')
  • JSON_REMOVE()移除对应值；
update test_json
    set obj_json = JSON_REMOVE(obj_json, '$.key1', '$.key2')

JSON_OBJECT()创建对象：

select json_object('key1',10,'key1',20);
-- 5.7.23 -> key1: 10
-- 8.0.31 -> key1: 20
  • 值得注意的是 mysql 8.0.3 之前默认选择第一个key的值，后面数值是抛弃的；而之后的版本则是选择最后一个key为准。

文档的解释：

This “last duplicate key wins” behavior is suggested by RFC 7159 and is implemented by most JavaScript parsers. (Bug #86866, Bug #26369555)

Prior to MySQL 8.0.3, this “first duplicate key wins” normalization was also performed when inserting values into JSON columns.

GORM 实操

在GORM，操作JSON需要引入自定义类型相关仓库：github.com/go-gorm/dat…

后续操作以该表为例子：

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `attachment` json DEFAULT NULL COMMENT '主播权益',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='创作者权益表'

对应go的结构体：

import (
   "gorm.io/datatypes"
)

type User struct {
   Id         uint64         `gorm:"column:id;"`
   Attachment datatypes.JSON `gorm:"column:attachment"`
}

func (*User) TableName() string {
   return "user"
}

添加对象

INSERT INTO user (attachment) VALUES (CAST('{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "auto_renewal": false}' AS JSON))

func Create() {
   db := client.DB
   //  INSERT INTO `user` (`attachment`) VALUES (CAST('{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "auto_renewal": false}' AS JSON))
   db.Create(&model.User{
      Attachment: []byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "auto_renewal": false}`),
   })
}

获取非null的key

SELECT * FROM user WHERE JSON_EXTRACT(attachment,'$.tags') IS NOT NULL

datatypes.JSONQuery("column").HasKey("key")

func HasKey() {
   db := client.DB
   var users []*model.User
   // SELECT * FROM `user` WHERE JSON_EXTRACT(`attachment`,'$.tags') IS NOT NULL
   db.Find(&users, datatypes.JSONQuery("attachment").HasKey("tags"))
   for _, u := range users {
      fmt.Println(u)
   }
}

获取指定key-value

SELECT * FROM user WHERE JSON_EXTRACT(attachment,'$.auto_renewal') = false

datatypes.JSONQuery("column").Equals(value, "key")

func Equals() {
   db := client.DB
   var users []*model.User
   // SELECT * FROM `user` WHERE JSON_EXTRACT(`attachment`,'$.auto_renewal') = false
   db.Find(&users, datatypes.JSONQuery("attachment").Equals(false, "auto_renewal"))
   for _, u := range users {
      fmt.Println(u)
   }
}
// 如果不存在该key也不会返回，默认是null而不是false

更新对象

UPDATE user SET attachment=JSON_SET(attachment,'.autorenewal′,true,′.auto_renewal',true,'.tags[0]','tag0') WHERE id = 1

datatypes.JSONSet("column").Set("key", value).Set("tags[0]", "tag0")

func Update() {
   db := client.DB
   sql := types.JSONSet("attachment").Set("auto_renewal", true).Set("tags[0]", "tag0")
   // UPDATE `user` SET `attachment`=JSON_SET(`attachment`,'$.auto_renewal',true,'$.tags[0]','tag0') WHERE id = 1
   db.Model(&model.User{}).Where("id = ?", 1).UpdateColumn("attachment", sql)
}
// 如果原本是null，则会添加该key
avatar
文章
阅读
粉丝