MySQL 允许你在 JSON 数据上创建索引

MySQL 允许你在 JSON 数据上创建索引

测试用例

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `courses` json DEFAULT NULL,
  `address` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO student (name, age, courses, address) VALUES
('张伟', 20, '[{"credits": 3.0, "course_id": "CS101", "course_name": "计算机基础"}, {"credits": 4.0, "course_id": "MATH202", "course_name": "高等数学"}]', '{"zip": "100000", "city": "北京", "state": "北京市", "street": "北京市朝阳区幸福路123号"}'),
('李娜', 22, '[{"credits": 3.0, "course_id": "BIO301", "course_name": "生物学基础"}]', '{"zip": "200000", "city": "上海", "state": "上海市", "street": "上海市浦东新区花园路456号"}');


mysql> select * from student\G;
*************************** 1. row ***************************
     id: 1
   name: 张伟
    age: 20
courses: [{"credits": 3.0, "course_id": "CS101", "course_name": "计算机基础"}, {"credits": 4.0, "course_id": "MATH202", "course_name":
 "高等数学"}]address: {"zip": "100000", "city": "北京", "state": "北京市", "street": "北京市朝阳区幸福路123号"}
*************************** 2. row ***************************
     id: 2
   name: 李娜
    age: 22
courses: [{"credits": 3.0, "course_id": "BIO301", "course_name": "生物学基础"}]
address: {"zip": "200000", "city": "上海", "state": "上海市", "street": "上海市浦东新区花园路456号"}
2 rows in set (0.00 sec)

需求：

在student表的courses字段中，为JSON数据内的course_id键创建索引。

-- 创建函数索引
ALTER TABLE student
ADD INDEX idx_course_id ((CAST(courses->'$[*].course_id' AS CHAR(50) ARRAY)));

mysql> -- 可以看到已经用到索引
mysql> EXPLAIN SELECT * FROM student
    -> WHERE JSON_CONTAINS(courses->'$[*].course_id', '"CS101"')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range
possible_keys: idx_course_id
          key: idx_course_id
      key_len: 203
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

上述的方式是通过MySQL 8.0 创建函数索引解决。

那么 MySQL 5.7  如何在 JSON 数据上创建索引？

答：使用虚拟列+全文索引：

-- 添加一个虚拟列
ALTER TABLE student
ADD COLUMN course_ids varchar(50) GENERATED ALWAYS AS 
(CONCAT_WS(' ', JSON_UNQUOTE(JSON_EXTRACT(courses, '$[*].course_id')))) STORED;

-- 创建全文索引
ALTER TABLE student
ADD FULLTEXT INDEX idx_course_ids (course_ids) WITH PARSER ngram; 

mysql> -- 使用全文搜索
mysql> EXPLAIN SELECT * FROM student
    -> WHERE MATCH(course_ids) AGAINST ('CS101' IN BOOLEAN MODE)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: fulltext
possible_keys: idx_course_ids
          key: idx_course_ids
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: no_ranking
1 row in set, 1 warning (0.00 sec)

结论

JSON 数据类型是在 MySQL 中存储 JSON 数据的好方法。 它提供验证功能，允许创建索引，并使用 JSON 函数操作 JSON 数据。 它是 TEXT 数据类型的最佳替代品。