创建表及索引：

CREATE TABLE `cartoon_data` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `category` char(4) GENERATED ALWAYS AS (json_extract(`xml2json`,'$.category')) VIRTUAL,
  `xml2json` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `cartoon_data`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `category` (`category`);

查询 JSON 格式字段 不使用索引 与 使用虚拟列索引 对比：

mysql> SELECT COUNT(*) FROM `cartoon_data` WHERE xml2json->'$.category' = 'cj';
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.63 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM `cartoon_data` WHERE xml2json->'$.category' = 'cj';
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cartoon_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 953622 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT COUNT(*) FROM `cartoon_data` WHERE category = '"cj"';
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.31 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM `cartoon_data` WHERE category = '"cj"';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | cartoon_data | NULL       | ref  | category      | category | 17      | const | 476811 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)