创建表及索引:
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)