面试题：MySQL中如何处理JSON类型数据及优化查询

Question

假设MySQL数据库中有一张表`user_info`，其中有一个JSON类型的字段`extra_data`，存储用户的额外信息（如爱好列表等）。请描述如何插入一条新数据到该表，并展示如何查询出`extra_data`中爱好包含'篮球'的用户记录，同时说明在这种查询场景下可能用到的优化技巧。

Answer 1

1. 插入新数据：

   INSERT INTO user_info (id, name, extra_data) VALUES (1, '张三', '{"hobbies":["篮球","足球"]}');
   

   上述示例假设user_info表有id（用户ID）、name（用户名）和extra_data字段，实际应用中请根据表结构调整字段名和值。

2. 查询爱好包含“篮球”的用户记录： 在MySQL 8.0及以上版本，可以使用JSON函数进行查询：

   SELECT * FROM user_info WHERE JSON_EXTRACT(extra_data, '$[*].hobbies') LIKE '%"篮球"%';
   

   这里JSON_EXTRACT函数提取extra_data中hobbies数组的值，然后通过LIKE操作符查找包含“篮球”的记录。

3. 优化技巧：

   • 索引优化：
     • MySQL对JSON类型字段的索引支持有限。但如果extra_data中某些固定的属性经常用于查询，可以考虑在这些属性上创建生成列索引。例如，如果hobbies总是以相同的结构存储，可以创建一个生成列来提取hobbies字段，然后对该生成列创建索引。

     ALTER TABLE user_info ADD COLUMN hobbies_extract VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$[*].hobbies'))) VIRTUAL;
     CREATE INDEX idx_hobbies ON user_info (hobbies_extract);
     

     这样查询时可以利用该索引加速查询。
   • 减少数据扫描：
     • 只选择需要的字段，而不是使用SELECT *。例如：

     SELECT id, name FROM user_info WHERE JSON_EXTRACT(extra_data, '$[*].hobbies') LIKE '%"篮球"%';
     

     减少返回的数据量，从而减少I/O和网络传输开销。
   • 查询缓存：
     • 如果查询频率较高，且数据更新不频繁，可以考虑使用MySQL的查询缓存（虽然在高并发写入场景下可能有性能问题）。开启查询缓存后，相同的查询会直接从缓存中获取结果，而不需要再次执行查询语句。在MySQL配置文件（如my.cnf）中设置query_cache_type = 1来开启查询缓存。