• 售前

  • 售后

热门帖子
入门百科

MySQL提取Json内部字段转储为数字

[复制链接]
123456809 显示全部楼层 发表于 2021-8-14 14:38:17 |阅读模式 打印 上一主题 下一主题
目录


  • 配景
  • 问题分析

    • 1、属性值是 Json 格式的,需要利用 Json 操作函数处置惩罚
    • 2、字段内容不规范,七零八落
    • 3.又要抽取内容、又要格式化,记录还有 900w+,太慢了

  • 末了执行结果比力

    • 数据导入比力

  • 总结
这只是一次简单数据迁徙的统计,数据量不大,贫困的是一些中心步调处置惩罚和思量。
没有 SQL 优化、索引优化的内容,各人轻喷。

配景


用户眼科属性表记录数大概 986w,目的是把大概 29w 记录的属性值(json 格式)的此中八个字段解析为数字,转储为统计表的记录,用于图表分析。
以下结构、数据都大部分我瞎诌的,不可认真
用户眼科属性表结构如下
  1. CREATE TABLE `property` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `ownerId` int(11) NOT NULL COMMENT '记录ID或者模板ID',
  4.   `ownerType` tinyint(4) NOT NULL COMMENT '类型。0:记录 1:模板',
  5.   `recorderId` bigint(20) NOT NULL DEFAULT '0' COMMENT '记录者ID',
  6.   `userId` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID',
  7.   `roleId` bigint(20) NOT NULL DEFAULT '0' COMMENT '角色ID',
  8.   `type` tinyint(4) NOT NULL COMMENT '字段类型。0:文本 1:备选项 2:时间 3:图片 4:ICD10 9:新图片',
  9.   `name` varchar(128) NOT NULL DEFAULT '' COMMENT '字段名称',
  10.   `value` mediumtext NOT NULL COMMENT '字段值',
  11.   PRIMARY KEY (`id`),
  12.   UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE,
  13.   KEY `idxUserIdRoleIdRecorderIdName` (`userId`,`roleId`,`recorderId`,`name`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='属性';
复制代码
问题分析



1、属性值是 Json 格式的,需要利用 Json 操作函数处置惩罚

由于属性值是 Json 格式的,如下。较大的一个 Json,但是只需要此中 8 个字段值,提取出来分门别类归为不同统计指标下。
  1. {   ......
  2.     "sight": {
  3.         "nakedEye": {
  4.             "left": "0.9",
  5.             "right": "0.6"
  6.         },
  7.         "correction": {
  8.             "left": "1",
  9.             "right": "1"
  10.         }
  11.     },
  12.     ......
  13.     "axialLength": {
  14.         "left": "21",
  15.         "right": "12"
  16.     },
  17.     "korneaRadius": {
  18.         "left": "34",
  19.         "right": "33"
  20.     },
  21.     ......
  22. }
复制代码
以是,需要用到 Json 操作函数:json_extract(value,'$.key1.key2')。
但是需要留意的是这个函数提取的值是带""。比如对上述记录执行json_extract(value,'$.sight.nakedEye.left')的结果是"22";也大概字段值是空字符串,那结果就是""。
以是,需要利用 replace函数把结果中的 "" 删撤消,末了提取字段的表达式就是:replace(json_extract(value,'$.sight.nakedEye.left'),'"','')。
如果字段不存在的话,结果就是 NULL;无论是外层 sight 不存在,或是内层 left 不存在。

2、字段内容不规范,七零八落

理想下,填写的都是规范数字,那颠末上面那一步就可以提取完直接导入新表。
但是,现实很残酷,填的东西那叫一个七零八落。比如:
       
  • 数字 + 备注:1(共同欠佳)、1-\+(我猜这是想表现偏高或偏低)   
  • 数字 + 单元:跟上面相似,1mm   
  • 多数值或区间:22.52/42.45、1-5   
  • 纯文本描述:不共同、无法记录   
  • 文本、数字混杂描述:较前次增长 10、<1、小于1、BD234/KD23
没办法,找产品和业务对环境,幸亏不多,就 4000 多条,大抵扫一下内心有数。得出以下几条解决方案:
       
  • 数字开头:数字开头都是正确记录的数据,省略掉文字描述即可   
  • 多数值或区间:取最前面的数即可   
  • 纯文本:分析没有数据,排撤消   
  • 文本、数字混杂:详细问题详细分析,把其他处置惩罚掉之后看还有多少
详细怎么做呢?
第一步:清除正常的数字数据和空数据
  1. WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
  2. AND `nakedEyeLeft` != ''
复制代码
第二步:如果不包罗数字,将其设置 NULL 或空字符串
  1. SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]', '', nakedEyeLeft)
复制代码
第三步:提取数字开头的数据的首个数值
  1. SET nakedEyeLeft = IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0)
复制代码
结合起来就是
  1. SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '',                       IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0))WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
  2. AND `nakedEyeLeft` != ''
复制代码
PS:处置惩罚一个字段的SQL 看着就简单,但是由于批量一次处置惩罚 8 个字段,组合起来就很长。
万万留意不要写错字段。
末了剩下的就是第四类:文本、数字混杂,40 多条。
有些看着简单的,可以用正则主动化处置惩罚,比如<1、小于1。
记录的增长值,需要查找前次记录举行盘算:较前次增长 10。
剩下有点复杂的,就需要人为处置惩罚,提取出可用数据,比如BD234/KD23
不知道看到这里的各位是不是也以为有些贫困呢?
我也以为咬着牙搞了,结果业务说直接处置惩罚成 0,到时候发现是 0 的话,可以通过页面重新生存的。
就不需要判断是不是数字打头了,直接 + 0;如果是数字打头,会保留开头的数字;否则 = 0。
那末了数据格式化SQL:
  1. UPDATE property SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', nakedEyeLeft + 0)WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
  2. AND `nakedEyeLeft` != '';
复制代码
3.又要抽取内容、又要格式化,记录还有 900w+,太慢了

property 表有 900w+ 的数据,而所需记录的条件,只有name、ownerType、type是可知的,没法命中现有的索引。
如果直接查找的话,直接就是全表扫描,外加数据提取和格式化;更何况还需要关联其他表,补充统计指标的一些其他字段。
这种环境下,直接导入统计表的话,结果就是把两张表+关联表一起锁较长时间,期间没法更改和插入,如许不大现实。
减少扫描行数
做法一:给 name、ownerType、type 加上索引,将扫描记录缩减到 20 w。
但是问题是900w 数据加索引,用完需要删除索引(由于不是业务环境需要),就会导致两次波动;
再加上后续处置惩罚锁表时长,问题照旧很大。
做法二:将一个记录较少的表做驱动表,这个表可以关联目的表。
  1. CREATE TABLE `property` (
  2.   `ownerId` int(11) NOT NULL COMMENT '记录ID或者模板ID',
  3.   `ownerType` tinyint(4) NOT NULL COMMENT '类型。0:记录 1:模板',
  4.   `type` tinyint(4) NOT NULL COMMENT '字段类型。0:文本 1:备选项 2:时间 3:图片 4:ICD10 9:新图片',
  5.   `name` varchar(128) NOT NULL DEFAULT '' COMMENT '字段名称',
  6.   `value` mediumtext NOT NULL COMMENT '字段值',
  7.     省略其他字段
  8.   UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='属性';
复制代码
表中ownerId 可以关联到记录表,加上之前的条件name、ownerType、type,云云刚好命中 并``idxOwnerIdOwnerTypeNameType (ownerType,ownerId,name,type) 。
  1. CREATE TABLE `medicalrecord` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '记录名称',
  4.   `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '记录类型。',
  5.     省略其他字段
  6.   KEY `idxName` (`name`) USING BTREE
  7. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='记录';
复制代码
记录表可以通过 name='眼科记录'命中索引idxName,扫描行数只有2w,加上属性表 29w,末了扫描行数只有 30w 左右,比之全表扫描属性表少了 30 倍!!!。
制止数据提取和格式化的锁表时长
由于存在 8 个字段,每个字段都需要提取和格式化,中心还需要举行判断。如许子一个 SQL 内里同样的提取和格式化操作就要多次执行了。
以是,为了制止如许的问题,需要中心表暂存提取和格式化结果。
  1. CREATE TABLE `propertytmp` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.    `value` mediumtext NOT NULL COMMENT '字段值',
  4.   `nakedEyeLeft` varchar(255) DEFAULT NULL COMMENT '视力-裸眼-左眼',
  5.   `nakedEyeRight` varchar(255) DEFAULT NULL COMMENT '视力-裸眼-右眼',
  6.   `correctionLeft` varchar(255) DEFAULT NULL COMMENT '视力-矫正-左眼',
  7.   `correctionRight` varchar(255) DEFAULT NULL COMMENT '视力-矫正-右眼',
  8.   `axialLengthLeft` varchar(255) DEFAULT NULL COMMENT '眼轴长度-左眼',
  9.   `axialLengthRight` varchar(255) DEFAULT NULL COMMENT '眼轴长度-右眼',
  10.   `korneaRadiusLeft` varchar(255) DEFAULT NULL COMMENT '角膜曲率-左眼',
  11.   `korneaRadiusRight` varchar(255) DEFAULT NULL COMMENT '角膜曲率-右眼',
  12.   `updated` datetime NOT NULL COMMENT '更新时间',
  13.   `deleted` tinyint(1) NOT NULL DEFAULT '0',
  14.   PRIMARY KEY (`id`)
  15. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
复制代码
先将数据导入该表,在此基础上做提取,然后格式化。

末了执行结果比力



数据导入比力


结果:全表扫描属性表导入中心表(40s),属性表新增索引+导入(6s + 3s),关联导入(1.4s)。
由于需要关联其他表,并没有推测的那么理想。
中心表数据提取:7.5s
  1. UPDATE `propertytmp`
  2. SET nakedEyeLeft = REPLACE(json_extract(value,'$.sight.axialLength.left'),'"',''),
  3. nakedEyeLeft = REPLACE(json_extract(value,'$.sight.nakedEye.left'),'"',''),
  4. nakedEyeRight = REPLACE(json_extract(value,'$.sight.nakedEye.right'),'"',''),
  5. correctionLeft = REPLACE(json_extract(value,'$.sight.correction.left'),'"',''),
  6. correctionRight = REPLACE(json_extract(value,'$.sight.correction.right'),'"',''),
  7. axialLengthLeft = REPLACE(json_extract(value,'$.axialLength.left'),'"',''),
  8. axialLengthRight = REPLACE(json_extract(value,'$.axialLength.right'),'"',''),
  9. korneaRadiusLeft = REPLACE(json_extract(value,'$.korneaRadius.left'),'"',''),
  10. korneaRadiusRight = REPLACE(json_extract(value,'$.korneaRadius.right'),'"','');
复制代码
中心表数据格式化:2.3s
正则判断比我想象的要快啊
  1. UPDATE propertytmp
  2. SET nakedEyeLeft = IF(nakedEyeLeft NOT REGEXP '[0-9]' AND nakedEyeLeft != '', '', nakedEyeLeft + 0),
  3. nakedEyeRight = IF(nakedEyeRight NOT REGEXP '[0-9]' AND nakedEyeRight != '', '', nakedEyeRight + 0),
  4. correctionLeft = IF(correctionLeft NOT REGEXP '[0-9]' AND correctionLeft != '', '', correctionLeft + 0),
  5. correctionRight = IF(correctionRight NOT REGEXP '[0-9]' AND correctionRight != '', '', correctionRight + 0),
  6. axialLengthLeft = IF(axialLengthLeft NOT REGEXP '[0-9]' AND axialLengthLeft != '', '', axialLengthLeft + 0),
  7. axialLengthRight = IF(axialLengthRight NOT REGEXP '[0-9]' AND axialLengthRight != '', '', axialLengthRight + 0),
  8. korneaRadiusLeft = IF(korneaRadiusLeft NOT REGEXP '[0-9]' AND korneaRadiusLeft != '', '', korneaRadiusLeft + 0),
  9. korneaRadiusRight = IF(korneaRadiusRight NOT REGEXP '[0-9]' AND korneaRadiusRight != '', '', korneaRadiusRight + 0)
  10. WHERE (`nakedEyeLeft` REGEXP '[^0-9.]' = 1
  11.        AND `nakedEyeLeft` != '')
  12.   OR (`nakedEyeRight` REGEXP '[^0-9.]' = 1
  13.       AND `nakedEyeRight` != '')
  14.   OR (`correctionLeft` REGEXP '[^0-9.]' = 1
  15.       AND `correctionLeft` != '')
  16.   OR (`correctionRight` REGEXP '[^0-9.]' = 1
  17.       AND `correctionRight` != '')
  18.   OR (`axialLengthLeft` REGEXP '[^0-9.]' = 1
  19.       AND `axialLengthLeft` != '')
  20.   OR (`axialLengthRight` REGEXP '[^0-9.]' = 1
  21.       AND `axialLengthRight` != '')
  22.   OR (`korneaRadiusLeft` REGEXP '[^0-9.]' = 1
  23.       AND `korneaRadiusLeft` != '')
  24.   OR (`korneaRadiusRight` REGEXP '[^0-9.]' = 1
  25.       AND `korneaRadiusRight` != '');
复制代码
统计指标中心表
由于实际导入统计指标表时,还需要清除为空数据,以及关联其他表做补充。
为了减少对指标表的影响,又建了指标表的中心表,结构完全同等,ID自增是目的表 + 10000。
将属性中心表的数据导入指标中心表,末了直接 INSERT ... SELECT FROM,就很快了。
固然这步实在有点矫枉过正了,但是为了制止线上的一些波动,照旧谨慎一些较好。

总结


这是一次简单的数据迁徙履历记录。
没有索引优化、SQL优化的内容,只是以为各人需要有这种关注性能和对用户影响的思量。
到此这篇关于MySQL提取Json内部字段转储为数字的文章就介绍到这了,更多干系MySQL提取Json转储为数字内容请搜刮草根技能分享从前的文章或继承浏览下面的干系文章盼望各人以后多多支持草根技能分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作