MySQL技术内幕系列分享：JSON相关特性详解
MySQL技术内幕系列分享：JSON相关特性详解
本期金句：
随着MySQL官方在对JSON数据处理的支持的逐步完善，在MySQL中使用JSON数据将会变得越来越方便和快捷。
随着互联网应用的迅猛发展，半结构和非结构化数据的存储与应用对所有的数据库系统都提出了新的需求，MySQL也根据自身特点开发出了丰富的处理这类数据的特性。本次分享就详细介绍一下MySQL中是如何处理JSON数据的，重点介绍JSON数据的索引，希望通过这次分享让大家对于MySQL处理JSON数据有一个相对全面的认识。
1 MySQL的JSON数据处理
JSON（JavaScript Object Notation）是主要用于互联网应用服务的轻量级数据交换格式。通常它被认为是一种半结构化数据。
为了适应互联网应用的快速发展，MySQL也从 MySQL 5.7 版本开始支持JSON数据的处理，并在后续版本加入更多对JSON数据的支持；
而在MySQL中使用JSON会带来如下一些好处：
• **数据可以灵活处理：**字段内容可以快速拓展，避免了加列操作；JSON数据是key-value pair的组织形式，其中的信息可以根据需要灵活增减，所以无需像传统表那样需要加减column才能增减新的数据信息。
• 减少非必要的存储空间：避免了稀疏字段的NULL值，减少其所占用的存储空间；JSON数据可以不用耗费空间存储NULL值信息，所以可以节省了部分此类数据的存储空间。
• 支持对JSON元素的索引：相比于字符串格式的JSON，JSON类型支持索引做特定的查询优化；目前官方在8.0版本已经支持了基于生成列的索引以及多值索引，使得对于JSON数据的查询更加快速高效。
• **跨数据类型查询：**结构化数据和非结构化数据联合查询更方便。由于结构化数据和非结构化数据可以放在同一张表里，使得采用一句SQL就可以对这些数据进行联合查询。
在MySQL存取JSON的方法：
• 创建含有JSON字段的表；
• 与其他类型字段一样，直接插入或修改JSON数据；
• 类似字符串类型一样简单查询JSON字段内容；
• 可以直接通过指定JSON字段的属性名称来查询对应的属性值；
• 通过JSON_REMOVE来删除JSON值
• 通过JSON_SET,JSON_INSERT,JSON_REPLACE来update属性值
除此之外，MySQL还提供了以下一些常用函数来处理JSON数据：
• JSON_EXTRACT **：**用来从 JSON 数据中提取所需要的字段内容
• JSON_UNQUOTE **：**作用是去除最外侧的双引号
• MEMBER OF：只能对JSON数组使用，返回1元素存在数组中，0元素不存在数组中
• JSON_CONTAINS：可以对JSON数组和JSON对象使用，针对JSON数组检查一个元素或者多个元素是否存在，对于JSON对象检查指定路径下是否有某个值或者是否有某个路径（Key）
• JSON_OVERLAP：比较两个JSON数组是否至少有一个元素一致，如果是返回1，否则返回0，如果是JSON对象，判断是否是找有一对key value一致
• JSON_KEYS：返回JSON对象的Key，也可以是嵌套JSON对象
2 MySQL 的JSON数据索引
为了更方便快捷的查询JSON数据里面的信息，MySQL还提供了对于JSON数据的索引特性。本节就重点介绍一下MySQL是如何对JSON数据建立索引的。
基于生成列的索引( Indexing JSON via a generated column)
1：MySQL可以通过创建生成列并在上面增加索引的方式来索引JSON数据，例如；
ALTER TABLE customers ADD COLUMN zip_code INTEGER GENERATED ALWAYS as (user_info->"$.zipcodel");
ALTER TABLE customers ADD INDEX zip_code (zip_code) USING BTREE;
2：MySQL8.0.13后的版本可通过直接创建函数索引的方式来索引JSON数据，例如；
ALTER TABLE customers ADD INDEX zip_code (CAST(user_info->"$.zip_code" as INTEGER)) USING BTREE;
如上图所示，用户通过在user_info这个JSON字段上建立generated golumn（生成列），然后在此列上建立如右边的B树索引来实现对user_info里面的zip_code信息的快速搜索。
多值索引（Multi valued indexing）
MySQL8.0.17后的版本可通过直接创建多值索引的方式来索引JSON数组的数据，例如；
• 创建多值索引：
CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
ALTER TABLE customers ADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
• 利用索引查询：
SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode’);
SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->‘$.zipcode’,CAST(‘[94507,94582]’ AS JSON));
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode',CAST('[94507,94582]' AS JSON));
如上图所示，对于JSON数据列user_info中的zip_code信息，因为其有多个值，即：整型数组，用户通过在MySQL中创建如右边所示的B树多值索引来对这些数据进行索引，以达到快速搜索此类数据的目的。
3 Klustron的JSON数据处理
目前，Klustron对于JSON数据也提供了基本的支持，首先，我们来看一下Klustron的整体架构：
如上图所示，我们的Klustron是一款典型的存算分离的分布式数据库系统，计算节点是基于PostgreSQL来实现的，其利用了PG强大的查询优化能力。而存储节点则是基于存储引擎更优的MySQL来实现的。
因此，现阶段（Klustron1.2版本）：暂时只支持PG的JSON函数和运算符来访问JSON数据。
我们正在研发将计算节点和存储节点相结合处理JSON的方法，利用存储节点的索引来加速JSON数据的处理来支持更加高效率的JSON数据存储和查询。
4 Q&A讨论
直播Q1：能简单对比一下PG和MySQL数据的处理和索引吗？
答：据我所知，MySQL在对JSON数据的支持方面相对PG来说是相对进展比较缓慢的。PG很早就提供了对JSON数据的支持，而且能在JSON数据上建B树，HASH，GiST和GIN索引，而MySQL只能建B树索引，所以，就特性来讲，PG在对JSON数据的支持方面是由于MySQL的。而在性能方面，我们还没有做过对比，感兴趣的同学可以做一些测试，估计是各有千秋，不同场景下互有优势。不过，就后续来说，因为有后发优势，可能MySQL在性能方面优化的空间会更大一些。
直播Q2：请问我们该如何选择究竟是用JSON字段还是普通字段呢？
虽然使用JSON数据有之前说的一些好处，但也不是所有场景都适合用JSON的。由于处理JSON数据也会带来一些额外的开销，比如：解析JSON数据以及其中的数据类型转换等，这些都会导致存取数据的时候比普通字段数据多花一些时间。所以，对于是否使用JSON数据需要依据使用场景，数据特点等来考虑，比如：数据的可变性很小，很少会需要变更表结构，而且需要频繁存取，而且对存取性能要求高，这样的场景还是需要使用传统的结构化数据字段。如果是数据结构会经常变化，存取数据不是特别频繁，对性能要求也不是很高，这样的场景就可以考虑使用JSON。