PHP、Go与MySQL中JSON数据处理与索引实践

PHP 在 8.3 的版本中增加了 json_validate() 函数可用于检查一个字符串是否为语法正确的 JSON，同时官方号称比 json_decode() 更有效。

在 8.3 版本之前，如果需要实现 JSON 验证，则需要使用 json_decode() 来辅助实现，并且通过验证最近是否存在 JSON_ERROR_NONE 错误来达到效果：

// PHP < 8.3
function json_validate(string $string): bool {
    json_decode($string);

    return json_last_error() === JSON_ERROR_NONE;
}

var_dump(json_validate('{ "test": { "foo": "bar" } }')); // true

而在 8.3 之后，只需要通过 json_validate() 函数就能直接验证字符串了：

var_dump(json_validate('{ "test": { "foo": "bar" } }')); // true

如果在 Go 语言中需要用到 JSON 验证，官方 encoding/json 包中已经定义了 json.Valid 来实现：

import (
    "encoding/json"
    "fmt"
)

func main() {
    fmt.Println(json.Valid([]byte(`{ "test": { "foo": "bar" } }`))) // true
}

同时我们知道 MySQL 5.7 及更高版本中可以为 JSON 字段的某个 key 设置索引来高效检索，可支持两种 Generated Column，即 Virtual Generated Column 和 Stored Generated Column，前者只将 Generated Column 保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将 Generated Column 持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与 Virtual Column 相比并没有优势，因此，MySQL 5.7 中，不指定 Generated Column 的类型，默认是 Virtual Column。

--- 表结构
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL DEFAULT '',
    `information` JSON,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

--- 创建JSON字段的虚拟列，user 是包含 JSON 字段的表名，information 是 JSON 字段的列名，label 是要索引的键名，label_index 是新添加的虚拟列名。
ALTER TABLE users
ADD COLUMN label_index VARCHAR(255) AS (information->>'$.label') STORED;

--- 为虚拟列创建索引
CREATE INDEX idx_label_index ON users(label_index);

然后可以使用市面上成熟的 ORM 来便捷操作 JSON 字段，在 Laravel 中我们可以先使用 make:model Artisan 命令来生成模型类：

php artisan make:model User

我们再来看看在 Eloquent ORM 中使用 JSON 字段的示例：

// 新增
$user = new User;
$user->name = 'fantasticbin';
$user->information = ['label' => 'cool', 'language' => ['php', 'go']];
$user->save();

// 修改
$user = User::find(1);
$information = $user->information;
$information['label'] = 'nice';
$user->information = $information;
$user->save();

// 查询
$users = User::whereJsonContains('information->label', 'nice')->get();

接着再来看看在 GORM 中如何操作 JSON 字段：

// model 定义
type User struct {
    gorm.Model
    Name string
    Information map[string]any `gorm:"type:json"`
}

// 新增
user := new(User)
user.Name = "fantasticbin"
user.Information = map[string]any{
    "label": "cool",
    "language": []string{"php", "go"},
}
result := db.Create(&user)

// 修改
db.First(&user)
information := user.Information
information["label"] = "nice"
user.Information = information
db.Save(&user)

// 查询
var users []User
db.Where("information->>'$.label' = ?", "nice").Find(&users)

那如果要给 JSON 数组建立索引呢？MySQL 8.0新增了一种索引类型：多值索引；InnoDB 从 MySQL 8.0.17 开始支持多值索引，多值索引是在存储值数组的列上定义的二级索引。

CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL DEFAULT '',
    `information` JSON,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    INDEX idx_label( (CAST(information->'$.label' AS CHAR(10) ARRAY)) )
);

然后我们就可以通过 MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS() 这三个函数进行 JSON 字段数组元素的检索了。

--- 直接筛选单个项
SELECT * FROM `users` WHERE 'nice' MEMBER OF(information->'$.label');
--- 获取包含所有项的记录
SELECT * FROM `users` WHERE JSON_CONTAINS(information->'$.label', CAST('["cool","nice"]' AS JSON));
--- 获取存在其中一项的记录
SELECT * FROM `users` WHERE JSON_OVERLAPS(information->'$.label', CAST('["cool","nice"]' AS JSON));

通过 EXPLAIN 关键字执行这三个语句，也可看到显示已使用到 idx_label 索引。

多值索引虽然对于 JSON 数组很好用，但它也有诸多限制，这里仅列出部分：

每个多值索引仅允许一个多值键部分。
具有多值键部分的索引不支持排序，因此不能用作主键。出于相同的原因，不能使用 ASC 或 DESC 关键字定义多值索引。
多值索引不能是覆盖索引。
多值索引的每条记录的最大值数由可以在单个撤消日志页上存储的数据量决定，即65221字节（64K减去315字节的开销），这意味着最大总数键值的长度也是65221字节。
多值键部分中唯一允许的表达式类型是 JSON 表达式。该表达式无需引用插入到索引列中的 JSON 文档中的现有元素，而本身在语法上必须有效。

总的来说，如果日常使用 JSON 字段中，如果只有一个数组，并且数组子项不是很多也不是很长也不需要考虑排序的情况下，可以通过使用多值索引来提高 JSON 记录检索的效率。

参考文献：
MySQL 8.0 新特性:多值索引 --如何给JSON数组添加索引（三）

PHP、Go与MySQL中JSON数据处理与索引实践

发表评论