JSON类型 和 生成列 - 非官方 MySQL 8.0 优化指南 - 学习笔记

MySQL Server 支持无模式的数据存储，功能特性如下：

1. JSON 数据类型。JSON 值在新增 / 更新时会被分析、验证，然后储存为优化了的二进制格式。在读取时不需任何分析或验证。
2. JSON 函数。有 20 多个 SQL 函数用于搜索、操作、创建 JSON 值。
3. 生成列。其工作方式很像函数索引，并能够对 JSON 的一部分进行提取或索引。

优化器在查询 JSON 数据时，会自动通过生成列寻找匹配的索引。
在例子35中，“用户设置” 被储存在了JSON列中，查询 “更新时通知我” 的用户，会使用全表扫描。通过增加一个虚拟生成列，EXPLAIN 展示出能使用到索引了：

例子35：“用户设置” 的无模式表示法

CREATE TABLE users (
  id INT NOT NULL auto_increment,
  username VARCHAR(32) NOT NULL,
  preferences JSON NOT NULL,      -- “用户设置” 使用了 JSON 类型
  PRIMARY KEY (id),
  UNIQUE (username)
);

INSERT INTO users
 (id,username,preferences)
VALUES
 (NULL, 'morgan', '{"layout": "horizontal", "warn_before_delete": false, "notify_on_updates": true}'),
 (NULL, 'wes', '{"layout": "horizontal", "warn_before_delete": false, "notify_on_updates": false}'),
 (NULL, 'jasper', '{"layout": "horizontal", "warn_before_delete": false, "notify_on_updates": false}'),
 (NULL, 'gus', '{"layout": "horizontal", "warn_before_delete": false, "notify_on_updates": false}'),
 (NULL, 'olive', '{"layout": "horizontal", "warn_before_delete": false, "notify_on_updates": false}');

EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE preferences->"$.notify_on_updates" = true;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "users",          
      "access_type": "ALL",         # 查询 “用户设置” 时，使用了全表扫描
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "280"
      },
      "used_columns": [
        "id",
        "username",
        "preferences"
      ],
      "attached_condition": "(json_extract(`test`.`users`.`preferences`,'$.notify_on_updates') = TRUE)"
    }
  }
}

例子36：添加虚拟生成列

-- 添加虚拟列。注意 JSON类型 访问和修改的语法。
ALTER TABLE users 
  ADD notify_on_updates TINYINT AS (preferences->"$.notify_on_updates"),
  ADD INDEX(notify_on_updates);

EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE preferences->"$.notify_on_updates" = true;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.20"
    },
    "table": {
      "table_name": "users",    
      "access_type": "ref",         # 使用到了索引
      "possible_keys": [
        "notify_on_updates"
      ],
      "key": "notify_on_updates",
      "used_key_parts": [
        "notify_on_updates"
      ],
      "key_length": "2",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.20",
        "prefix_cost": "1.20",
        "data_read_per_join": "56"
      },
      "used_columns": [
        "id",
        "username",
        "preferences",
        "notify_on_updates"
      ]
    }
  }
}

译自：
JSON and Generated Columns - The Unofficial MySQL 8.0 Optimizer Guide