MySQL优化：虚拟字段

前言

最近我们一个二手应用的即时通讯模块马上就用不了了，因为当时对接的是腾讯的IM服务，现在组织里说不想再续费了，功能还得保留。

那就能手写聊天模块了，所有都写得差不多的时候，在聊天记录表格里却整了一出尬尴的事儿。建表时根据消息推送方式，只存储了接收者ID，发送者ID却放在消息包的json中。

但是后面很多时候的查询需要两个ID字段联合，也就是要从json的多层路径中找到发送者ID，并作为条件。于是就有了下面通过虚拟字段解决的方法了。

知识点

1. 多层json路径标量读取
2. 虚拟字段的创建
3. 聊天消息的查询优化

原表结构

CREATE TABLE `bqs_chat_message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0' COMMENT '用户ID',
  `data` json DEFAULT NULL,
  `is_get` tinyint(1) DEFAULT '0',
  `delete_time` int(11) DEFAULT '0',
  `create_time` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4;

data字段(Json)

{
    "type":"message",
    "commit_id": 16,
    "message":"SUCCESS",
    "data":{
        "type":"receive",
        "id":"1111",
        "sender_id":1110,
        "self":true,
        "time":"2020年06月01日 14:34",
        "message":{
            "type":"text",     
            "content":"好"
        },
        "group_id":0,
        "cache_key":"U1111",
        "timestamp":1590993240,
        "receiver_id":"1111"
    }
}

多层json路径标量读取

JSON_EXTRACT()函数可以使用JSON路径表达式来提取JSON数据中的值，比如下面的查询将返回data字段中data.sender_id路径下的值，也就是发送者ID

SELECT JSON_EXTRACT(data, '$.data.sender_id') AS sneder_id
FROM bqs_chat_message

虚拟字段创建

ALTER TABLE bqs_chat_message ADD COLUMN sender_id INT(11) AS (cast(json_extract(`data`,'$.data.sender_id') as signed),0) VIRTUAL COMMENT '发送者ID';

查询优化

虽然上面的方法已经实现了虚拟字段创建，但如果data字段的json路径下不存在sender_id时。数据库里显示和查询的结果出来都是null，为了让虚拟字段的类型统一为int，我们需要对不规则的json数据做处理。

也就是如果json中没有sender_id，我们就以0为默认值，这里我们可以通过IFNULL和IF等SQL函数处理，但我这里推荐coalesce函数。

COALESCE() 函数允许我们从一系列提供的参数中返回第一个非空（非NULL）的值，这个函数可以接受两个或更多的参数，并逐一检查它们，直到找到一个非空值为止，然后立即返回该值。如果所有的参数都是NULL，那么COALESCE() 函数也会返回NULL。

ALTER TABLE bqs_chat_message ADD COLUMN sender_id INT(11) AS (coalesce(cast(json_extract(`data`,'$.data.sender_id') as signed),0)) VIRTUAL COMMENT '发送者ID';

写在后面

以上的三个操作已经完全解决了建表上的缺陷，但是要想让查询变得更快，我们还可以将虚拟字段添加为索引，在查询上就不会圈表检索了。

本作品采用《CC 协议》，转载必须注明作者和本文链接