目錄
- 背景
- 問題分析
- 1、屬性值是 Json 格式的，需要使用 Json 操作函數(shù)處理
- 2、字段內(nèi)容不規(guī)范，亂七八糟
- 3.又要抽取內(nèi)容、又要格式化，記錄還有 900w+，太慢了
- 最后執(zhí)行結(jié)果比較
- 總結(jié)
這只是一次簡單數(shù)據(jù)遷移的統(tǒng)計，數(shù)據(jù)量不大，麻煩的是一些中間步驟處理和思量。
沒有 SQL 優(yōu)化、索引優(yōu)化的內(nèi)容，大家輕噴。
背景
用戶眼科屬性表記錄數(shù)大概 986w，目的是把大概 29w 記錄的屬性值（json 格式）的其中八個字段解析為數(shù)字，轉(zhuǎn)儲為統(tǒng)計表的記錄，用于圖表分析。
以下結(jié)構(gòu)、數(shù)據(jù)都大部分我瞎謅的，不可當(dāng)真
用戶眼科屬性表結(jié)構(gòu)如下
CREATE TABLE `property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ownerId` int(11) NOT NULL COMMENT '記錄ID或者模板ID',
`ownerType` tinyint(4) NOT NULL COMMENT '類型。0：記錄 1：模板',
`recorderId` bigint(20) NOT NULL DEFAULT '0' COMMENT '記錄者ID',
`userId` bigint(20) NOT NULL DEFAULT '0' COMMENT '用戶ID',
`roleId` bigint(20) NOT NULL DEFAULT '0' COMMENT '角色ID',
`type` tinyint(4) NOT NULL COMMENT '字段類型。0：文本 1：備選項 2：時間 3：圖片 4：ICD10 9：新圖片',
`name` varchar(128) NOT NULL DEFAULT '' COMMENT '字段名稱',
`value` mediumtext NOT NULL COMMENT '字段值',
PRIMARY KEY (`id`),
UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE,
KEY `idxUserIdRoleIdRecorderIdName` (`userId`,`roleId`,`recorderId`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='屬性';
問題分析
1、屬性值是 Json 格式的，需要使用 Json 操作函數(shù)處理
因為屬性值是 Json 格式的，如下。較大的一個 Json，但是只需要其中 8 個字段值，提取出來分門別類歸為不同統(tǒng)計指標(biāo)下。
{ ......
"sight": {
"nakedEye": {
"left": "0.9",
"right": "0.6"
},
"correction": {
"left": "1",
"right": "1"
}
},
......
"axialLength": {
"left": "21",
"right": "12"
},
"korneaRadius": {
"left": "34",
"right": "33"
},
......
}
所以，需要用到 Json 操作函數(shù)：json_extract(value,'$.key1.key2')。
但是需要注意的是這個函數(shù)提取的值是帶""。比如對上述記錄執(zhí)行json_extract(value,'$.sight.nakedEye.left')的結(jié)果是"22"；也可能字段值是空字符串，那結(jié)果就是""。
所以，需要使用 replace函數(shù)把結(jié)果中的 "" 刪除掉，最后提取字段的表達式就是：replace(json_extract(value,'$.sight.nakedEye.left'),'"','')。
如果字段不存在的話，結(jié)果就是 NULL；無論是外層 sight 不存在，或是內(nèi)層 left 不存在。
2、字段內(nèi)容不規(guī)范，亂七八糟
理想下，填寫的都是規(guī)范數(shù)字，那經(jīng)過上面那一步就可以提取完直接導(dǎo)入新表。
但是，現(xiàn)實很殘酷，填的東西那叫一個亂七八糟。比如：
- 數(shù)字 + 備注：1(配合欠佳)、1-\+（我猜這是想表示偏高或偏低）
- 數(shù)字 + 單位：跟上面相似，1mm
- 多數(shù)值或區(qū)間：22.52/42.45、1-5
- 純文本描述：不配合、無法記錄
- 文本、數(shù)字混雜描述：較上次增長 10、1、小于1、BD234/KD23
沒辦法，找產(chǎn)品和業(yè)務(wù)對情況，好在不多，就 4000 多條，大致掃一下心里有數(shù)。得出以下幾條解決方案：
- 數(shù)字開頭：數(shù)字開頭都是正確記錄的數(shù)據(jù)，省略掉文字描述即可
- 多數(shù)值或區(qū)間：取最前面的數(shù)即可
- 純文本：說明沒有數(shù)據(jù)，排除掉
- 文本、數(shù)字混雜：具體問題具體分析，把其他處理掉之后看還有多少
具體怎么做呢？
第一步：排除正常的數(shù)字?jǐn)?shù)據(jù)和空數(shù)據(jù)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個已經(jīng)可以排除 null 了
AND `nakedEyeLeft` != ''
第二步：如果不包含數(shù)字，將其設(shè)置 NULL 或空字符串
SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]', '', nakedEyeLeft)
第三步：提取數(shù)字開頭的數(shù)據(jù)的首個數(shù)值
SET nakedEyeLeft = IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0)
結(jié)合起來就是
SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '',
IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0))
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個已經(jīng)可以排除 null 了
AND `nakedEyeLeft` != ''
PS：處理一個字段的SQL 看著就簡單，但是因為批量一次處理 8 個字段，組合起來就很長。
千萬注意不要寫錯字段。
最后剩下的就是第四類：文本、數(shù)字混雜，40 多條。
有些看著簡單的，可以用正則自動化處理，比如1、小于1。
記錄的增長值，需要查找上次記錄進行計算：較上次增長 10。
剩下有點復(fù)雜的，就需要人為處理，提取出可用數(shù)據(jù)，比如BD234/KD23
不知道看到這里的各位是不是也覺得有些麻煩呢？
我也以為咬著牙搞了，結(jié)果業(yè)務(wù)說直接處理成 0，到時候發(fā)現(xiàn)是 0 的話，可以通過頁面重新保存的。
就不需要判斷是不是數(shù)字打頭了，直接 + 0；如果是數(shù)字打頭，會保留開頭的數(shù)字；否則 = 0。
那最后數(shù)據(jù)格式化SQL：
UPDATE property
SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', nakedEyeLeft + 0)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個已經(jīng)可以排除 null 了
AND `nakedEyeLeft` != '';
3.又要抽取內(nèi)容、又要格式化，記錄還有 900w+，太慢了
property 表有 900w+ 的數(shù)據(jù)，而所需記錄的條件，只有name、ownerType、type是可知的，沒法命中現(xiàn)有的索引。
如果直接查找的話，直接就是全表掃描，外加數(shù)據(jù)提取和格式化；更何況還需要關(guān)聯(lián)其他表，補充統(tǒng)計指標(biāo)的一些其他字段。
這種情況下，直接導(dǎo)入統(tǒng)計表的話，結(jié)果就是把兩張表+關(guān)聯(lián)表一起鎖較長時間，期間沒法更改和插入，這樣不大現(xiàn)實。
減少掃描行數(shù)
做法一：給 name、ownerType、type 加上索引，將掃描記錄縮減到 20 w。
但是問題是900w 數(shù)據(jù)加索引，用完需要刪除索引（因為不是業(yè)務(wù)情況需要），就會導(dǎo)致兩次波動；
再加上后續(xù)處理鎖表時長，問題還是很大。
做法二：將一個記錄較少的表做驅(qū)動表，這個表可以關(guān)聯(lián)目標(biāo)表。
CREATE TABLE `property` (
`ownerId` int(11) NOT NULL COMMENT '記錄ID或者模板ID',
`ownerType` tinyint(4) NOT NULL COMMENT '類型。0：記錄 1：模板',
`type` tinyint(4) NOT NULL COMMENT '字段類型。0：文本 1：備選項 2：時間 3：圖片 4：ICD10 9：新圖片',
`name` varchar(128) NOT NULL DEFAULT '' COMMENT '字段名稱',
`value` mediumtext NOT NULL COMMENT '字段值',
省略其他字段
UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='屬性';
表中ownerId 可以關(guān)聯(lián)到記錄表，加上之前的條件name、ownerType、type，如此剛好命中 并``idxOwnerIdOwnerTypeNameType (ownerType,ownerId,name,type) 。
CREATE TABLE `medicalrecord` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '記錄名稱',
`type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '記錄類型。',
省略其他字段
KEY `idxName` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='記錄';
記錄表可以通過 name='眼科記錄'命中索引idxName，掃描行數(shù)只有2w，加上屬性表 29w，最后掃描行數(shù)只有 30w 左右，比之全表掃描屬性表少了 30 倍?。。?。
避免數(shù)據(jù)提取和格式化的鎖表時長
因為存在 8 個字段，每個字段都需要提取和格式化，中間還需要進行判斷。這樣子一個 SQL 里面同樣的提取和格式化操作就要多次執(zhí)行了。
所以，為了避免這樣的問題，需要中間表暫存提取和格式化結(jié)果。
CREATE TABLE `propertytmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` mediumtext NOT NULL COMMENT '字段值',
`nakedEyeLeft` varchar(255) DEFAULT NULL COMMENT '視力-裸眼-左眼',
`nakedEyeRight` varchar(255) DEFAULT NULL COMMENT '視力-裸眼-右眼',
`correctionLeft` varchar(255) DEFAULT NULL COMMENT '視力-矯正-左眼',
`correctionRight` varchar(255) DEFAULT NULL COMMENT '視力-矯正-右眼',
`axialLengthLeft` varchar(255) DEFAULT NULL COMMENT '眼軸長度-左眼',
`axialLengthRight` varchar(255) DEFAULT NULL COMMENT '眼軸長度-右眼',
`korneaRadiusLeft` varchar(255) DEFAULT NULL COMMENT '角膜曲率-左眼',
`korneaRadiusRight` varchar(255) DEFAULT NULL COMMENT '角膜曲率-右眼',
`updated` datetime NOT NULL COMMENT '更新時間',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
先將數(shù)據(jù)導(dǎo)入該表，在此基礎(chǔ)上做提取，然后格式化。
最后執(zhí)行結(jié)果比較
數(shù)據(jù)導(dǎo)入比較
結(jié)果：全表掃描屬性表導(dǎo)入中間表（40s），屬性表新增索引+導(dǎo)入（6s + 3s），關(guān)聯(lián)導(dǎo)入（1.4s）。
因為需要關(guān)聯(lián)其他表，并沒有預(yù)測的那么理想。
中間表數(shù)據(jù)提?。?.5s
UPDATE `propertytmp`
SET nakedEyeLeft = REPLACE(json_extract(value,'$.sight.axialLength.left'),'"',''),
nakedEyeLeft = REPLACE(json_extract(value,'$.sight.nakedEye.left'),'"',''),
nakedEyeRight = REPLACE(json_extract(value,'$.sight.nakedEye.right'),'"',''),
correctionLeft = REPLACE(json_extract(value,'$.sight.correction.left'),'"',''),
correctionRight = REPLACE(json_extract(value,'$.sight.correction.right'),'"',''),
axialLengthLeft = REPLACE(json_extract(value,'$.axialLength.left'),'"',''),
axialLengthRight = REPLACE(json_extract(value,'$.axialLength.right'),'"',''),
korneaRadiusLeft = REPLACE(json_extract(value,'$.korneaRadius.left'),'"',''),
korneaRadiusRight = REPLACE(json_extract(value,'$.korneaRadius.right'),'"','');
中間表數(shù)據(jù)格式化：2.3s
正則判斷比我想象的要快啊
UPDATE propertytmp
SET nakedEyeLeft = IF(nakedEyeLeft NOT REGEXP '[0-9]' AND nakedEyeLeft != '', '', nakedEyeLeft + 0),
nakedEyeRight = IF(nakedEyeRight NOT REGEXP '[0-9]' AND nakedEyeRight != '', '', nakedEyeRight + 0),
correctionLeft = IF(correctionLeft NOT REGEXP '[0-9]' AND correctionLeft != '', '', correctionLeft + 0),
correctionRight = IF(correctionRight NOT REGEXP '[0-9]' AND correctionRight != '', '', correctionRight + 0),
axialLengthLeft = IF(axialLengthLeft NOT REGEXP '[0-9]' AND axialLengthLeft != '', '', axialLengthLeft + 0),
axialLengthRight = IF(axialLengthRight NOT REGEXP '[0-9]' AND axialLengthRight != '', '', axialLengthRight + 0),
korneaRadiusLeft = IF(korneaRadiusLeft NOT REGEXP '[0-9]' AND korneaRadiusLeft != '', '', korneaRadiusLeft + 0),
korneaRadiusRight = IF(korneaRadiusRight NOT REGEXP '[0-9]' AND korneaRadiusRight != '', '', korneaRadiusRight + 0)
WHERE (`nakedEyeLeft` REGEXP '[^0-9.]' = 1
AND `nakedEyeLeft` != '')
OR (`nakedEyeRight` REGEXP '[^0-9.]' = 1
AND `nakedEyeRight` != '')
OR (`correctionLeft` REGEXP '[^0-9.]' = 1
AND `correctionLeft` != '')
OR (`correctionRight` REGEXP '[^0-9.]' = 1
AND `correctionRight` != '')
OR (`axialLengthLeft` REGEXP '[^0-9.]' = 1
AND `axialLengthLeft` != '')
OR (`axialLengthRight` REGEXP '[^0-9.]' = 1
AND `axialLengthRight` != '')
OR (`korneaRadiusLeft` REGEXP '[^0-9.]' = 1
AND `korneaRadiusLeft` != '')
OR (`korneaRadiusRight` REGEXP '[^0-9.]' = 1
AND `korneaRadiusRight` != '');
統(tǒng)計指標(biāo)中間表
因為實際導(dǎo)入統(tǒng)計指標(biāo)表時，還需要排除為空數(shù)據(jù)，以及關(guān)聯(lián)其他表做補充。
為了減少對指標(biāo)表的影響，又建了指標(biāo)表的中間表，結(jié)構(gòu)完全一致，ID自增是目標(biāo)表 + 10000。
將屬性中間表的數(shù)據(jù)導(dǎo)入指標(biāo)中間表，最后直接 INSERT ... SELECT FROM，就很快了。
當(dāng)然這步其實有點矯枉過正了，但是為了避免線上的一些波動，還是謹(jǐn)慎一些較好。
總結(jié)
這是一次簡單的數(shù)據(jù)遷移經(jīng)歷記錄。
沒有索引優(yōu)化、SQL優(yōu)化的內(nèi)容，只是覺得大家需要有這種關(guān)注性能和對用戶影響的考慮。
到此這篇關(guān)于MySQL提取Json內(nèi)部字段轉(zhuǎn)儲為數(shù)字的文章就介紹到這了,更多相關(guān)MySQL提取Json轉(zhuǎn)儲為數(shù)字內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 詳解Mysql中的JSON系列操作函數(shù)
- MySQL操作之JSON數(shù)據(jù)類型操作詳解
- mysql查詢字段類型為json時的兩種查詢方式
- mysql5.6及以下版本如何查詢數(shù)據(jù)庫里的json
- MySQL5.7 JSON類型使用詳解
- mysql(5.6及以下)解析json的方法實例詳解
- MySQL5.7中的JSON基本操作指南
