MariaDB 性能优化：当 JSON 埋点遇上 LEFT JOIN

背景 最近接到产品的一个需求：希望将系统内的埋点数据单独进行统计，并生成报表反馈给客户。

早期埋点日志表的结构：

-- 创建原始版本的日志表
CREATE TABLE `deal_shared_log_v1` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `action_detail` JSON NOT NULL,  -- 所有的请求以json格式存在里边
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入一条演示数据
INSERT INTO `deal_shared_log_v1` (`action_detail`) 
VALUES ('{"id": 1024, "user_id": 5001, "action": "share", "platform": "twitter"}');

与其说这是一张统计表，不如说它是一个纯粹的 Log。在业务早期，我们并没有针对这块做严谨的模型设计，而是直接采用了 Schema-less 的策略，利用 JSON 字段把所有 Payload 存了下来。这在当时极大地提升了开发效率。

性能灾难：隐藏在 JSON 里的外键 随着产品提出报表统计需求，技术债爆发了。

根据业务要求，我们需要拿着这条日志去关联用户信息。这意味着查询 SQL 会带上一个 LEFT JOIN，而致命的是，作为关联外键的 user_id 偏偏被深埋在 JSON 字符串里。

当执行类似 ON deal.id = JSON_UNQUOTE(JSON_EXTRACT(action_detail, '$.id')) 的关联条件时，由于函数计算的存在，常规索引完全失效。面对日益增长的日志量，数据库每次查询都不得不进行全表扫描（Full Table Scan）——把每一行拿出来，现场解析 JSON 然后对比，性能极差。

方案1：引入 MariaDB 虚拟列 (Virtual Column) 为了在不重构早期业务代码、不执行昂贵数据迁移的前提下解决这个性能瓶颈，我们引入了 MariaDB 的“虚拟列”特性。

通过在表层面将 JSON 内部的常用检索字段提取为虚拟列，并为其建立 B+ 树索引，我们成功让数据库重新找回了查询方向：

SQL

-- 针对 v1 表增加虚拟列并建立索引
ALTER TABLE `deal_shared_log_v1`
ADD COLUMN `v_deal_id` INT AS (JSON_UNQUOTE(JSON_EXTRACT(`action_detail`, '$.id'))) VIRTUAL,
ADD INDEX `idx_v_deal_id` (`v_deal_id`);

通过这次改造，原本导致系统卡顿的慢查询瞬间命中了 idx_v_deal_id 索引，查询速度有了质的飞跃。

方案2：横向扩展，增加deal_id相比较虚拟列，直接增加新列会更加简便。但是缺陷是，需要大量重构埋点的代码，需要执行一个全盘的update，以及假设如果以后每次有一个新业务，如果需要进行一个新列的更新，都要重新进行一次这样的操作。

相比较虚拟列的优点：虚拟列在每次写入的时候，都会因为json提取而花费额外的cpu资源。会在写入上，导致更大的性能浪费。