mysql8 json 索引总结

表结构如下所示：

CREATE TABLE `test_json` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int NOT NULL,
  `test_json_array` json NOT NULL,
  `test_json_object` json NOT NULL,
  `test_json_array_object` json NOT NULL,
  `custinfo` json DEFAULT NULL COMMENT 'json数据',
  PRIMARY KEY (`id`),
  KEY `json_more_value_index` ((cast(json_extract(`custinfo`,_utf8mb4'$.zipcode') as unsigned array)),`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据如下所示：

表数据SQL如下所示：

INSERT INTO `test_json` (`id`, `name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`, `custinfo`) VALUES (1, '张三', 18, '[1, 2, 3]', '{\"height\": \"175cm\", \"weight\": \"50kg\"}', '[{\"group\": 1, \"hobby\": \"打篮球\"}, {\"group\": 2, \"hobby\": \"睡觉\"}]', '{\"zipcode\": [94582, 94536]}');
INSERT INTO `test_json` (`id`, `name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`, `custinfo`) VALUES (2, '赵四', 22, '[1, 4, 6]', '{\"height\": \"170cm\", \"weight\": \"57kg\"}', '[{\"group\": 3, \"hobby\": \"打游戏\"}, {\"group\": 4, \"hobby\": \"睡觉\"}]', '{\"zipcode\": [94568, 94507, 94582]}');
INSERT INTO `test_json` (`id`, `name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`, `custinfo`) VALUES (3, '王五', 36, '[4, 9, 7]', '{\"height\": \"120cm\", \"weight\": \"60kg\"}', '[{\"group\": 9, \"hobby\": \"看博客\"}, {\"group\": 6, \"hobby\": \"睡觉\"}]', '{\"zipcode\": [94477, 94507]}');
INSERT INTO `test_json` (`id`, `name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`, `custinfo`) VALUES (4, '李丹', 78, '[3, 5, 7]', '{\"height\": \"130cm\", \"weight\": \"60kg\"}', '[{\"group\": 9, \"hobby\": \"看博客\"}, {\"group\": 6, \"hobby\": \"睡觉\"}]', '{\"zipcode\": [94536]}');
INSERT INTO `test_json` (`id`, `name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`, `custinfo`) VALUES (5, '六六', 13, '[4, 5, 8]', '{\"height\": \"180cm\", \"weight\": \"60kg\"}', '[{\"group\": 9, \"hobby\": \"看博客\"}, {\"group\": 6, \"hobby\": \"睡觉\"}]', '{\"zipcode\": [94507, 94582]}');

创建索引如下所示：也就是多值索引如下所示：

ALTER TABLE test_json ADD INDEX test_json$test_json_array ( age, (CAST( test_json_array -> '$[*]' AS UNSIGNED ARRAY )), name );
ALTER TABLE test_json ADD INDEX test_json$test_json_array_object ((CAST( test_json_array_object -> '$[*].hobby' AS CHAR(64) ARRAY )), age);
ALTER TABLE test_json ADD INDEX `json_more_value_index` ((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)),name, age );

test_json_array数组SQL查询语句如下所示：

select * from test_json t where age = 18 and JSON_OVERLAPS (test_json_array -> '$[*]',CAST( '[1,2]' AS JSON ));

test_json_array数组查询执行计划SQL语句如下所示：

explain select * from test_json t where age = 18 and JSON_OVERLAPS (test_json_array -> '$[*]',CAST( '[1,2]' AS JSON ));

总结：