随着应用场景多样化与快速迭代，业务系统常采用半结构化数据类型进行存储与分析。PolarDB列存索引（InMemoryColumnIndex，IMCI）功能推出了完备的虚拟列与列式JSON功能等，可以快速处理大数据和多种数据类型（包括结构化数据与半结构化数据等），并提供高效的数据分析、查询与流计算能力，适用于数据分析、数据仓库与扩展流计算等应用场景。
针对海量半结构化数据计算与分析，本文将以半结构化数据JSON为例，首先简述传统数据库与数据仓库的解决方案，然后详细描述PolarDBIMCI列式JSON、虚拟列、秒级加减列与表列数扩展等技术点，最后给出PolarDBIMCI列式JSON实时分析与扩展流计算解决方案。
业务系统采用JSON类型主要由于半结构化数据的灵活性和高效分析的需求，因此，灵活性与高性能就成为衡量海量半结构化数据分析的解决方案好坏的关键指标。
传统关系型数据库（MySQL、PostgreSQL与ClickHouse等）在处理JSON数据时经常将原始JSON数据编码成二进制数据并存储到表的JSON类型列中，查询时使用JSON函数对整个JSON列数据进行实时解析与计算等。
selectproduct.item->"$.name"fromproduct,purchasewhereproduct.id=purchase.item->"$.id"groupbyproduct.item->"$.name";传统数据库执行上述SQL时，当使用NestedLoopJoin算子时，每一张product表的每一行均需要重复读取purchase表的item整列数据，同时反复解析JSON列数据并抽取出指定字段，使得查询效率不佳。
数据仓库的处理流程通常包括以下步骤：
数据抽取（Extraction）：从各个数据源中抽取需要的数据，包括数据库、文件、Web服务等，并进行清洗、转换和过滤。
数据转换（Transformation）：对抽取的数据进行转换，使其符合数据仓库的数据模型和规范。包括数据清洗、数据整合、数据转换、数据增强和数据聚合等操作。
数据加载（Loading）：将经过转换的数据加载到数据仓库中，包括维度表和事实表。
数据管理（Management）：对数据仓库中的数据进行管理，包括数据备份、数据恢复、数据安全等操作。
数据分析（Analysis）：通过数据仓库中的数据进行多维分析，包括查询、报表、数据挖掘等操作。
业务系统的生产数据一般会经过中间ETL任务按需处理后再导入到数据仓库。
在处理JSON数据时，为了提高查询性能，数仓往往在ETL任务中预先解析JSON数据且按需计算出对应值，然后作为表的单独一列插入到表中，即将JSON数据一些属性按需处理后构成大宽表。查询时不再需要读取和解析整个JSON列数据，直接读取对应普通列即可，节省大量IO资源。此外也可以针对该列建立和使用索引，有效提高查询性能。
但当业务需求变更时，业务系统按需增删改JSON数据的属性后，数仓需要修改中间ETL作业和表结构来适配上游的生产数据，比如重新发布ETL作业、DDL加列或删列等。因此，数据仓库往往需要同时维护ETL作业逻辑和业务表结构，而频繁发布ETL作业常常会影响上游数据消费和下游结果入仓，且在不支持INSTANTDDL时修改大表结构代价相对比较高，容易影响正常查询业务。
总体来说，传统数仓解决方案虽然能够提供高效查询，但缺乏灵活性，维持成本高。
在应对海量半结构化数据的分析场景时，传统数据库与数据仓库均无法同时满足查询性能和灵活架构，因此业界迫切需要新解决方案：PolarDBIMCI。
半结构化数据是介于结构化数据和非结构化数据之间的一种数据形式，具有部分结构化的特点，但不像结构化数据那样具有明确的数据模式。半结构化数据可以通过标签、标记、元数据等方式进行描述和组织，但其结构与组织方式也可以随着数据内容的变化而动态调整。半结构化数据通常存在于Web页面、XML、JSON、NoSQL数据库等场景中，其灵活性和易扩展性使其成为大数据时代中不可或缺的一部分。
PolarDBMySQL版本身是一个关系型数据库管理系统，其存储的数据通常是结构化数据，但也原生支持存储和查询半结构化数据，如XML和JSON格式数据。PolarDBIMCI也全面支持JSON数据类型及其列式函数，采用二进制JSON格式来存储半结构化数据，支持通过列式JSON函数来实现JSON文档的解析、查询、修改和删除等操作，与MySQL语法完全兼容。
PolarDBIMCI采用精简二进制方式存储JSON列存数据，且使用RapidJSON库解析JSON数据，处理过程中按需读取数据且利用列存压缩技术等有效减少IO量，同时充分利用SIMD和向量化及并行等加速运算。
以实际测试数据为例展示列存中JSON用法及其行列存性能对比：
创建表并添加JSON列及其列索引。
createtableproduce(idbigint(20)NOTNULL,attributesjsonDEFAULTNULL)comment='columnar=1';使用列式JSON函数进行查询。
selectcount(*)fromproducewhereattributes->"$.delivery.width">10andattributes->"$.delivery.height">10andattributes->"$.delivery.weight">10;列存执行计划：
存储方式
SQL耗时
行存
9.29sec
IMCI（32核）
0.14sec
测试实验表明PolarDB列存可以高效分析JSON数据，接近行存两个数量级。由于数据集与查询模式均不同，线上业务的性能收益可能存在一定差异，需要以实际情况为准。
虚拟列（VirtualColumns）作为一种特殊类型的列，其值不是通过插入或更新的方式进行存储，而是根据表中其他列的值动态计算、合并或者筛选得出。虚拟列可以用于查询和索引，但不能被直接修改或删除。虚拟列提供了一个快速访问和处理数据的方法，而无需在每次查询时重新计算这些数据，因此常用来优化查询和简化操作。
PolarDBIMCI实现完整虚拟列功能，支持两种GeneratedColumn：VirtualGeneratedColumn（默认）与StoredGeneratedColumn。其中Virtual只会将GeneratedColumn计算后值持久化到列存，但不会持久化到行存，每次行存读取时会重新实时计算；Stored则会将GeneratedColumn计算后值持久化到行存和列存，但会占用更多磁盘空间。在PolarDBIMCI生态中推荐使用默认VirtualGeneratedColumn，在节省磁盘空间同时还有列存高性能。
虚拟列语法：
col_namedata_type[GENERATEDALWAYS]AS(expr)[VIRTUAL|STORED][NOTNULL|NULL][UNIQUE[KEY]][[PRIMARY]KEY][COMMENT'string']以实际测试数据为例展示列存中虚拟列用法及其行列存性能对比：
创建表并添加虚拟列及其列索引。
createtableproduce(idbigint(20)NOTNULL,attributesjsonDEFAULTNULL,`delivery_volume`doubleGENERATEDALWAYSAS(((json_extract(`attributes`,'$.delivery.width')*json_extract(`attributes`,'$.delivery.height'))*json_extract(`attributes`,'$.delivery.weight')))VIRTUAL)comment='columnar=1';分别使用普通列和虚拟列进行查询。
普通列查询
selectcount(*)fromproducewhere(attributes->"$.delivery.width"*attributes->"$.delivery.height"*attributes->"$.delivery.weight")>1000;列存执行计划：
13.43sec
IMCI（1核）
5.72sec
0.24sec
虚拟列查询
selectcount(*)fromproducewheredelivery_volume>1000;列存执行计划：
14.30sec
0.03sec
0.01sec
测试实验表明PolarDB列存的虚拟列功能可以有效提高查询性能。由于数据集与查询模式均不同，线上业务的性能收益可能存在一定差异，需要以实际情况为准。
总之，PolarDBMySQL版的虚拟列是一种灵活且强大的功能，尤其处理半结构化数据（如JSON类型数据等）时可以直接将不规则的数据存储为结构化数据，避免中间ETL额外处理逻辑，并且可以使用传统的SQL查询语言进行查询与分析。虚拟列有助于简化复杂的计算和查询，提高架构灵活性；不仅可以避免行存数据冗余，而且在虚拟列上建立列存索引且充分利用列存pruner机制进行过滤，切实提高查询性能。
当半结构化数据因业务需求而增删JSON列时，业务表则按需通过DDL加列或删列来修改表结构，此时高效增删列就成为必不可少的功能。半结构化数据发生结构变化时，无需每次都更改表结构，可以在查询频率不高时直接通过JSON列式函数实时计算。尤其在PolarDBIMCI的JSON列存版本中，大部分情况下实时计算也能满足查询性能。
PolarDBIMCI实现列存表的秒级加减虚拟列INSTANTDDL功能，可以在瞬间完成加减虚拟列，不会阻塞读写，基本不会影响正常查询业务。
秒级加虚拟列功能：
altertableproduceaddcolumndelivery_volumeDOUBLEAS(attributes->"$.delivery.width"*attributes->"$.delivery.height"*attributes->"$.delivery.weight");秒级删虚拟列功能：
PolarDBIMCI处理半结构化数据时经常会将半结构化数据中一些属性按需转换为表的单独虚拟列，若属性数目过多时就可能突破表的最大列数限制。因此PolarDBIMCI在原生MySQL基础上对InnoDB和列存表的最大列数进行扩展，目前支持最大列数4089列（约4K列）；
针对半结构化数据分析，PolarDBIMCI实现列式JSON与列存虚拟列等技术，本小节将以GitHub实时事件数据（2023年7月份）来验证PolarDBIMCIJSON实时数据分析能力。
查询一周内最流行编程语言：
SELECTrepo_name,count(*)ASstarsFROMgithub_eventsWHERE(type='WatchEvent')AND(actor_loginIN(SELECTactor_loginFROMgithub_eventsWHERE(type='WatchEvent')AND(repo_nameIN('torvalds/linux'))ANDcreated_at>="2023-07-3100:00:00"ANDcreated_at<="2023-07-3123:59:59"))AND(repo_nameNOTIN('torvalds/linux'))ANDcreated_at>="2023-07-3100:00:00"ANDcreated_at<="2023-07-3123:59:59"GROUPBYrepo_nameORDERBYstarsDESCLIMIT10;创建github_events表列索引，并结合上述SQL给其添加actor_login、repo_name与repo_language等虚拟列。
altertablegithub_eventsaddcolumnactor_loginvarchar(256)generatedalwaysas(json_unquote(json_extract(`actor`,'$.login')))virtual,addcolumnrepo_namevarchar(256)generatedalwaysas(json_unquote(json_extract(`repo`,'$.name')))virtual,addcolumnrepo_languagevarchar(32)generatedalwaysas(json_unquote(json_extract(`payload`,'$.pull_request.base.repo.language')))virtual,comment'columnar=1';配置行存缓存为500GB，列存缓存为128GB，在热数据情况下测试结果如下：
SQL1耗时
SQL2耗时
45.01sec
8.57sec
0.79sec
0.54sec
0.04sec
0.07sec
如上表所见，PolarDBIMCI列式JSON实时分析性能远高于行存，可以有效应对海量半结构化数据的分析场景。
结合上述列式JSON、虚拟列、秒级加减列与列数扩展等一系列新功能，PolarDBIMCI提供出一套基于海量半结构化数据分析场景的自动化解决方案：扩展流计算。
PolarDBIMCI扩展流计算主要是通过SQL语句在表结构中用计算表达式或函数来定义数据流处理逻辑并记录为虚拟列，然后扩展流计算框架会自动根据业务数据流实时计算出结果并持久化到列存表中，查询时则应用列存索引快速读取结果值。整个扩展流计算流程均已内置到PolarDBIMCI，用户只需要通过SQLDDL来定义不同数据流处理逻辑（即虚拟列）即可；当业务需求发生变化时同样也只需要通过SQLDDL来增加修改虚拟列。
具体到海量半结构化数据的分析场景，用户可以根据业务需求用JSON函数和JSON列数据属性定义虚拟列（即数据流处理逻辑），并给该虚拟列添加列索引即可，随后业务持续的数据流实时计算和存储将由PolarDBIMCI自动完成，不断更新大宽表。业务查询时可以直接使用指定虚拟列，不但可以应用列存索引而且还能避免重新读取和解析完整JSON列数据等，有效提高查询效率。即使查询时直接使用JSON函数方式，PolarDB优化器也会根据JSON函数和JSON列来查询是否存在匹配的虚拟列，若匹配则会优先选择该虚拟列以提高查询性能。当业务需求变更时，业务系统按需增删改JSON列数据属性后，用户只需使用INSTANTDDL来秒级加减列即可，不需要类似传统数仓那样额外维护ETL作业逻辑，同时秒级加减列功能可以瞬间完成表结构更改。不仅可以灵活应对业务需求变化，基本没有额外维持成本，也不会影响正常业务。若新需求的查询频率不高则可以不更改表结构，查询时可以直接用列存版本JSON函数实时解析，也能够满足大部分业务需求。
总之，应对海量半结构化数据的分析场景时，PolarDBIMCI扩展流计算具有传统数据库的灵活性与数据仓库的高性能。
某视频平台是中国最受欢迎的在线视频平台之一，提供电影、电视剧与综艺节目及直播等视频或功能，用户可以通过付费会员获得更好的观影体验。
在海量会员基础上，某视频平台每天会员交易数据量迅速膨胀。某视频平台会员交易业务的数据独立为实时表，主要用于业务补偿与校验、实时监控订单扭转状态、自动补单与发送权益等，实现无人工处理，自动化处理权益延时到账等。
会员交易业务原数据库系统采用的MySQL分库分表方案，每一库表基于MySQL集群一写多读部署方案，通过增加分区数和只读节点的方式扩展数据库集群来应对业务发展需求，节点间采用Binlog同步。为了应对快速迭代，交易业务大量采用半结构化JSON类型。随着业务快速发展，现有数据库架构难以支撑大表高并发JSON查询，只能通过不断增加数据库和分区数来应对，因此不仅运维复杂和影响业务，其成本也逐步增长。
简化运维和降本增效成为某视频平台会员交易业务数据库新架构的主要目标。新数据库架构采用PolarDBHTAP一体化解决方案，充分利用扩展流计算与列存来提升海量JSON数据分析性能，同时有效简化运维。
某金融电商平台是一家总部位于新加坡，成立于2016年的金融科技公司，在东南亚地区提供消费金融服务。该公司提供的服务包括分期付款、信用贷款、虚拟信用卡、电商平台等，在印度尼西亚、菲律宾、马来西亚、泰国和越南等国家拥有数百万用户，并已经成为当地领先的消费金融服务提供商之一。
该平台的订单业务系统主要分为事务处理与数据分析两大类，业务事务系统大量采用JSON数据来存储各类业务属性，而业务分析系统则需要计算与分析大量业务属性。
订单业务原数据库系统主要采用的MySQL集群处理业务事务，用ClickHouse集群进行业务分析，ApacheFlink用于订阅MySQL数据流并将JSON数据按需转换为结构化数据进而构造出大宽表，再实时插入到ClickHouse集群。由于现有数据库架构复杂性，业务经常遇到中间链接丢失数据、分析实时性不高与查询性能不佳及运维复杂等问题。尤其因业务需求更改而增删改JSON属性时，数据库业务需要不断修改Flink处理逻辑与更改ClickHouse表结构，而ClickHouse无法做到秒级加减列，在修改大表时不仅耗时长且影响查询性能等。随着业务快速发展，系统稳定性、简化运维、分析性能与节约成本成为架构师的当务之急。
一体化实时事务处理和实时数据分析的PolarDBHTAP云原生数据库成为不二之选。新数据库方案具备极简架构，由MySQL、DTS、Flink与ClickHouse等多套系统简化为一套PolarDB。新方案利用PolarDBIMCI扩展流计算代替Flink实现半结构化数据到结构化数据的自动转换、秒级加减列功能简化运维、列数扩展支撑业务极速发展与列存提升分析性能等。