MySQL8.0 Generated Column 函数索引（四 ) —— JSON列的Generated Column 索引

在Innodbr中JSON 列使用函数索引

JSON类型的列不能直接创建索引，但可以间接创建索引，可以定义一个 Generated Column列，该列提取JSON字段的部分信息，然后在Generated Column列上创建索引，如以下示例所示：

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id")),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

当对包含->或->>运算符的SELECT语句或其他SQL语句使用EXPLAIN时，这些表达式将使用JSON_EXTRACT和JSON_UNQUOTE（如果需要）转换为等价表达式，如此EXPLAIN语句后面的SHOW WARNINGS的输出所示：

mysql> EXPLAIN SELECT c->>"$.name"
     > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
`c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
json_extract(`test`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

此技术还可用于提供间接引用其他类型（例如几何列）的列的索引。

在NDB Cluster中JSON 列使用函数索引

还可以在MySQL NDB集群中使用JSON列的间接索引，条件如下：

• NDB在内部将JSON列作为BLOB处理。这意味着，任何具有一个或多个JSON列的NDB表都必须有主键，否则无法将其记录在二进制日志中。
• NDB存储引擎不支持对 Generated Column列的索引。由于生成列的默认值为VIRTUAL，因此必须显式指定generated column 列的类型为STORED ，然后再创建索引。

这里用于创建表jempn的CREATE TABLE语句是前面创建jemp表的一个版本，通过修改使其与NDB引擎兼容：

CREATE TABLE jempn ( a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, c JSON DEFAULT NULL, g INT GENERATED ALWAYS AS (c->"$.name") STORED, INDEX i (g) ) ENGINE=NDB;

我们可以使用以下INSERT语句填充此表：

INSERT INTO jempn (a, c) VALUES (NULL, '{"id": "1", "name": "Fred"}'), (NULL, '{"id": "2", "name": "Wilma"}'), (NULL, '{"id": "3", "name": "Barney"}'), (NULL, '{"id": "4", "name": "Betty"}');

现在NDB可以使用索引i，如下所示：

mysql> EXPLAIN SELECT c->>"$.name" AS name FROM jempn WHERE g > 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jempn partitions: p0,p1 type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2) 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from `test`.`jempn` where (`test`.`jempn`.`g` > 2) 1 row in set (0.00 sec)

Stored 类型的Generated Column列 使用DataMemory，此类型列上的索引使用IndexMemory。

 

 

https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index