第26章：MySQL JSON、全文索引与复杂数据查询

1. 项目背景业务场景某电商平台的产品库中每个商品有几十个不固定的属性——手机有屏幕尺寸、内存、摄像头像素衣服有尺码、颜色、面料生鲜有产地、保质期、存储温度。传统的关系模型需要为每一种商品类型建一张独立的属性表——这导致表数量爆炸且每次新增商品类型都要加表、加字段、改代码。开发团队决定改用 JSON 字段存储可变属性——方案很灵活但随之而来的是新问题如何对 JSON 中的属性建立索引加速查询如何支持全文搜索商品描述中的关键词痛点MySQL 不是 NoSQL 数据库但现代业务中半结构化和全文搜索的需求越来越强烈JSON 字段查询慢WHERE JSON_EXTRACT(attrs, $.color) red不走索引——每次都是全表扫描。JSON 函数误用-vs-的区别返回 JSON 类型 vs 返回字符串类型——混用导致比较逻辑错误。全文索引的中文支持MySQL 内置 FULLTEXT 对中文分词支持极差——默认按空格分词——中文没有空格——所以整句话被当成一个 token——搜索几乎无效。生成列索引的维护成本用 VARCHAR(32) 的生成列建索引——但 JSON 中的值可能是数组或对象——无法映射到标量列。本章教会你 JSON 字段的查询、索引、生成列优化以及 FULLTEXT 全文索引的配置和中文分词方案。2. 项目设计【场景小胖正试着在商品表里加一个属性字段挠头】小胖“大师我们的商品属性太复杂了——每类商品属性都不一样。DBA 说不要为了每种商品建一张属性表——建议我用 JSON 字段。但用了之后我发现查询超级慢——WHERE attrs-$.color red一条查询要 5 秒。”大师“因为 JSON 字段本身不能直接建普通索引。attrs-$.color是一个表达式——MySQL 不知道这个表达式的值分布在哪些行中——所以只能全表扫描。解决方法有两个——第一用多值索引Multi-Valued IndexMySQL 8.0.17 支持可以对 JSON 数组中的值建索引。第二用生成列——把你要频繁查询的 JSON 路径提取为一个虚拟列——然后在这个虚拟列上建普通 BTree 索引。”技术映射JSON 字段索引 生成列Generated Column 普通索引或 Multi-Valued Index针对 JSON 数组。直接对 JSON 路径表达式建索引是不支持的。小白“生成列是 VIRTUAL 好还是 STORED 好”大师“VIRTUAL 不占用存储空间——每次查询时计算——CPU 开销小但是查询时需要计算。STORED 占用存储空间——就像普通列一样——写入时有额外成本但是查询时不需计算。对于高频查询的 JSON 属性——建 VIRTUAL 生成列 索引是性价比最高的方案——不额外占用磁盘空间——索引帮你把计算一次完成。”小胖“那全文搜索呢我们的商品描述需要支持关键词搜索——用户输入’无糖 有机’要能找到相关商品。MySQL 的 FULLTEXT 是不是可以”大师“MySQL 的 FULLTEXT 索引对英文很好——因为它按空格分词。但对中文——你需要额外的分词器。MySQL 内置的 ngram parserWITH PARSER ngram会把中文按 N 个字符一组切割——默认ngram_token_size2即每两个字一组。这样’无糖有机食品’会被切成’无糖’、‘糖有’、‘有机’、‘机食’、‘食品’——其中’有机’能被搜到。但是’糖有’、机食’这样的噪声 token 也会增加索引体积。”3. 项目实战3.1 环境准备USEecommerce;-- 创建支持 JSON 和全文索引的商品表DROPTABLEIFEXISTSproducts_full;CREATETABLEproducts_full(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(200)NOTNULL,categoryVARCHAR(50)NOTNULL,descriptionTEXT,attrs JSON,priceDECIMAL(12,2)NOTNULL,-- 生成列提取 JSON 中的颜色属性用于索引attr_colorVARCHAR(50)GENERATED ALWAYSAS(attrs-$.color)STORED,-- 生成列提取品牌attr_brandVARCHAR(100)GENERATED ALWAYSAS(attrs-$.brand)STORED,-- 生成列提取内存大小整数attr_ramINTUNSIGNEDGENERATED ALWAYSAS(CAST(attrs-$.ramASUNSIGNED))STORED,created_atDATETIME(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3),INDEXidx_category(category),INDEXidx_attr_color(attr_color),INDEXidx_attr_brand(attr_brand),INDEXidx_price(price),FULLTEXTINDEXft_description(description)WITHPARSER ngram)ENGINEInnoDBDEFAULTCHARSETutf8mb4;3.2 分步实现步骤一JSON 字段的基础操作与查询-- 步骤目标掌握 JSON 插入、查询、修改的常用语法-- 插入商品数据INSERTINTOproducts_full(name,category,description,attrs,price)VALUES(iPhone 16 Pro,手机,全新 iPhone 16 Pro钛金属机身A18 Pro 芯片超视网膜显示屏,{color: 深空黑, brand: Apple, ram: 8, storage: 256, camera: {main: 48MP, ultra: 12MP}},8999.00),(小米 15 Ultra,手机,小米旗舰机徕卡影像骁龙 8 Gen4 处理器快充,{color: 陶瓷白, brand: 小米, ram: 16, storage: 512, camera: {main: 50MP, tele: 200MP}},6499.00),(纯棉圆领T恤,服装,100% 有机棉亲肤透气四季百搭基础款,{color: 白色, brand: 优衣库, size: [S, M, L, XL]},99.00),(有机全麦面包,食品,无糖全麦吐司高纤维低GI早餐健康选择,{brand: 全麦主义, weight: 400g, shelf_life: 7天},25.90);-- JSON 查询语法-- 1. 提取标量值返回字符串类型——推荐用于比较SELECTname,attrs-$.colorAScolorFROMproducts_fullWHEREattrs-$.colorISNOTNULL;-- attrs-$.color 返回字符串不含引号可直接比较-- 2. 提取 JSON 对象返回 JSON 类型SELECTname,attrs-$.cameraAScameraFROMproducts_fullWHEREcategory手机;-- attrs-$.camera 返回 JSON 对象含 JSON 格式-- 3. JSON_CONTAINS——检查数组是否包含某个值SELECTname,attrs-$.sizeFROMproducts_fullWHEREJSON_CONTAINS(attrs-$.size,M);-- 找出 size 数组中包含 M 的商品-- 4. JSON_EXTRACT / JSON_UNQUOTE——复杂路径提取SELECTname,JSON_EXTRACT(attrs,$.camera.main)ASmain_camera,JSON_UNQUOTE(JSON_EXTRACT(attrs,$.camera.tele))AStele_cameraFROMproducts_fullWHEREcategory手机;步骤二生成列 索引——加速 JSON 属性查询-- 步骤目标对比直接 JSON 查询 vs 生成列索引的查询性能-- 方案 A直接 JSON 查询全表扫描EXPLAINSELECT*FROMproducts_fullWHEREattrs-$.color深空黑;-- typeALL, rows全表-- 即使我们已经在 attr_color 上建了索引——但这个查询没有使用 attr_color-- 因为 attrs-$.color ≠ attr_color优化器无法推导出等价关系-- 方案 B生成列查询走索引EXPLAINSELECT*FROMproducts_fullWHEREattr_color深空黑;-- typeref, keyidx_attr_color, rows1-- 直接查询生成列——索引正常工作-- 方案 C给 JSON 路径表达式本身创建多值索引 -- MySQL 8.0.17 支持CREATEINDEXidx_sizeONproducts_full((CAST(attrs-$.sizeASCHAR(50)ARRAY)));-- 查询 size 数组是否包含 MEXPLAINSELECT*FROMproducts_fullWHEREMMEMBEROF(attrs-$.size);-- typeref, keyidx_size——多值索引生效-- 性能对比 -- 创建一张大表——百万级 JSON 数据-- 分别测试三种方案的 QPS步骤三全文索引——英文、中文、布尔搜索-- 步骤目标配置 ngram 分词器并验证中文搜索效果-- 查看 ngram 配置SHOWVARIABLESLIKEngram_token_size;-- 默认 2每 2 个字一组可以改为 1单字索引——更精确但索引更大-- 1. 自然语言模式 SELECTname,description,MATCH(description)AGAINST(有机)ASrelevanceFROMproducts_fullWHEREMATCH(description)AGAINST(有机);-- 返回所有 description 中包含有机的商品-- relevance 是相关性评分——越高越相关-- 注意ngram_token_size2 时——有 机 被切成一组——能匹配-- 2. 布尔模式支持 /- 语法-- 搜索包含手机但不包含小米的商品SELECTname,descriptionFROMproducts_fullWHEREMATCH(description)AGAINST(手机 -小米INBOOLEANMODE);-- 搜索包含全麦或有机的商品SELECTname,descriptionFROMproducts_fullWHEREMATCH(description)AGAINST(全麦 有机INBOOLEANMODE);-- 3. 查询扩展模式自动同义词扩展SELECTname,descriptionFROMproducts_fullWHEREMATCH(description)AGAINST(手机WITHQUERY EXPANSION);-- 第一次搜索手机用匹配到的行提取相关词做第二次扩展搜索-- 4. 全文索引的性能对比 -- 用普通 LIKE 查询EXPLAINSELECT*FROMproducts_fullWHEREdescriptionLIKE%有机%;-- typeALL全表扫描-- 用 FULLTEXTEXPLAINSELECT*FROMproducts_fullWHEREMATCH(description)AGAINST(有机);-- typeFULLTEXT全文索引扫描——比 LIKE 快几个数量级步骤四JSON_TABLE——将 JSON 数组转换为关系行-- 步骤目标使用 JSON_TABLE 将 JSON 数组展开为行进行 JOIN 查询-- 场景每个商品的 attrs JSON 中有多个颜色变体INSERTINTOproducts_full(name,category,attrs,price)VALUES(运动鞋 Air Max,鞋类,{brand: Nike, color_variants: [{color: 黑白, stock: 100}, {color: 红白, stock: 50}, {color: 蓝白, stock: 30}]},899.00);-- 用 JSON_TABLE 展开 color_variants 数组SELECTp.name,jt.color,jt.stockFROMproducts_full pCROSSJOINJSON_TABLE(p.attrs,$.color_variants[*]COLUMNS(colorVARCHAR(50)PATH$.color,stockINTPATH$.stock))ASjtWHEREp.category鞋类;-- 结果每个颜色一行共 3 行-- name: 运动鞋 Air Max, color: 黑白, stock: 100-- name: 运动鞋 Air Max, color: 红白, stock: 50-- name: 运动鞋 Air Max, color: 蓝白, stock: 30-- 也可以在 JSON_TABLE 展开后做聚合SELECTp.name,SUM(jt.stock)AStotal_stockFROMproducts_full pCROSSJOINJSON_TABLE(p.attrs,$.color_variants[*]COLUMNS(stockINTPATH$.stock))ASjtWHEREp.category鞋类GROUPBYp.name;步骤五空间索引入门——GIS 查询-- 步骤目标创建包含地理坐标的商品表并用空间索引查询附近门店DROPTABLEIFEXISTSstores;CREATETABLEstores(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(100)NOTNULL,locationPOINTNOTNULLSRID4326,-- WGS 84 坐标系SPATIALINDEXidx_location(location))ENGINEInnoDB;-- 插入门店坐标经度, 纬度INSERTINTOstores(name,location)VALUES(北京朝阳店,ST_GeomFromText(POINT(116.46 39.92),4326)),(北京海淀店,ST_GeomFromText(POINT(116.30 39.96),4326)),(上海浦东店,ST_GeomFromText(POINT(121.54 31.24),4326)),(广州天河店,ST_GeomFromText(POINT(113.36 23.13),4326));-- 查询离北京天安门116.40, 39.915 公里内的门店SELECTname,ST_Distance_Sphere(location,ST_GeomFromText(POINT(116.40 39.91),4326))/1000ASdistance_kmFROMstoresWHEREST_Distance_Sphere(location,ST_GeomFromText(POINT(116.40 39.91),4326))5000ORDERBYdistance_km;-- 预期北京朝阳店 ~6km, 北京海淀店 ~11km海淀可能超出 5km 范围-- 用空间索引加速MBR 包围盒过滤EXPLAINSELECTnameFROMstoresWHEREMBRContains(ST_Buffer(ST_GeomFromText(POINT(116.40 39.91),4326),0.05),location);3.3 测试验证-- 验证清单-- 1. 确认生成列索引正常SHOWINDEXFROMproducts_fullWHEREKey_nameIN(idx_attr_color,idx_attr_brand);EXPLAINSELECT*FROMproducts_fullWHEREattr_color深空黑;-- typeref-- 2. 确认全文索引可命中中文SELECTname,MATCH(description)AGAINST(无糖 健康)ASscoreFROMproducts_fullWHEREMATCH(description)AGAINST(无糖 健康);-- 应返回有机全麦面包——且 score 0-- 3. 确认 JSON_TABLE 正确展开数组SELECTCOUNT(*)FROM(SELECTp.idFROMproducts_full pCROSSJOINJSON_TABLE(p.attrs,$.size[*]COLUMNS(sVARCHAR(10)PATH$))ASjt)t;-- 应返回包含 size 数组的商品的行数-- 4. 确认空间查询SELECTCOUNT(*)FROMstoresWHEREST_Distance_Sphere(location,ST_GeomFromText(POINT(116.40 39.91),4326))10000;-- 至少应返回北京的两家门店4. 项目总结优点 缺点维度优点缺点/局限JSON 生成列索引半结构化数据的灵活性 BTree 索引的性能每增加一个查询维度就需要一个新的生成列修改生成列需 ALTER TABLEMulti-Valued Index直接对 JSON 数组建索引无需展开为行MySQL 8.0.17 才有仅支持简单类型数组FULLTEXT ngram中文分词可用开箱即用无需外部组件ngram 分词精度远不如专业分词器如 jieba、IK索引体积大JSON_TABLE将 JSON 数组转化为标准行——可 JOIN、可聚合无索引下推优化——大 JSON 数组展开性能一般SPATIAL 索引地理查询可用空间索引加速 MBR 过滤不支持所有 GIS 函数如 ST_Distance_Sphere 不能走索引适用场景商品属性库可变属性用 JSON 高频查询字段用生成列索引。用户行为日志JSON 数组存储用户点击的商品序列——Multi-Valued Index 支持数组包含查询。CMS 全文搜索文章/商品描述用 FULLTEXT ngram 实现站内搜索数据量 100 万时足够。LBS 门店搜索POINT 字段 SPATIAL 索引 MBRContains 做周边门店查询。配置管理JSON 存储各租户的个性化配置——结构灵活、无需 DDL。不适用场景海量全文搜索亿级文档Elasticsearch 在分词、相关性排序、高亮等方面远超 MySQL FULLTEXT。复杂 GIS 分析PostGISPostgreSQL 扩展在 GIS 函数和性能上远超 MySQL。JSON 作为主存储模型应用层完全无 Schema——应使用 MongoDB 等文档数据库——MySQL 的 JSON 更适合部分字段的灵活性。注意事项生成列上建索引——生成列定义中不能引用其他表的列或子查询只能引用本表列和 MySQL 内置函数。FULLTEXT 索引 ngram 的最小 token 长度ngram_token_size2意味着长度 2 的搜索词被忽略需要一个独立的小词表或改设为 1。ST_Distance_Sphere不走 SPATIAL 索引先用MBRContains做粗过滤——再用ST_Distance_Sphere精算。常见踩坑经验故障案例一FULLTEXT 索引搜索小米——查不出小米手机。根因ngram_token_size2——“小米手机被切成了小米”、“米手”、“手机”——但innodb_ft_min_token_size3默认 3——2 字 token 小米被丢弃了。修复SET GLOBAL innodb_ft_min_token_size 1;需要重建全文索引。故障案例二生成列索引查询结果和直接 JSON 查询结果不一致。根因生成列用的是attrs-$.color返回字符串查询条件用了attrs-$.color返回 JSON——类型不匹配导致部分行被过滤。修复统一使用-进行字符串比较。故障案例三JSON_CONTAINS在大数组上的查询 CPU 100%。根因JSON_CONTAINS在内存中逐元素扫描 JSON 数组——对于长度 1000 的数组——CPU 开销很大。修复用 Multi-Valued Index 或把大数组拆到独立的关联表中。思考题为什么 FULLTEXT 搜索手机能查出小米手机——但搜索手机 小米时排序结果中小米手机排在第一位而不是iPhone 手机壳相关性评分是怎么算的Multi-Valued Index 和生成列索引在存储结构和查询优化上有什么本质区别答案提示第 1 题——相关性评分基于 TF-IDF词频 × 逆文档频率小米和手机都在小米手机中出现——两个词都有 TF 贡献——得分更高第 2 题答案——Multi-Valued Index 在索引中一个索引行对应 JSON 数组中的多个值一对多映射生成列索引是一个索引行对应一个标量值一对一映射。延伸阅读与资源Java 工程师进阶从 JVM 生产排障到OpenJDK原理NumPy 从入门到生产落地全链路实战指南科学计算/向量化Redis 8 实战精讲从 CRUD 到源码构建高可用缓存系统Redis 实战修炼与原理进阶Python 3实战精进从脚本到高并发订单引擎python入门Rquests从菜鸟脚本到企业级SDK的网络实战圣经Milvus向量数据库实战修炼从 0 到 1精通向量检索与生产落地MongoDB 实战进阶与内核修炼后端工程师的 AI 转型第一课Ollama 与私有化大模型实战10倍开发者的 Dify 魔法书从零构建全栈 AI 应用后端工程师转型AI第一课-Ollama 与私有化大模型实战大型语言模型(LLM) vLLM 高性能推理落地实战Agent开发之LlamaIndex 实战修炼与源码进阶大语言模型Transformers 实战修炼与源码剖析