MySQLはJSON内部フィールドを抽出し、数値としてダンプします

1. 属性値はJson形式であり、Json操作関数を使用して処理する必要があります。
2. フィールドの内容が標準化されておらず、乱雑である
3. コンテンツの抽出とフォーマットに時間がかかりすぎます。まだ 900 万件以上のレコードが残っています。

最終実行結果の比較

データインポートの比較

要約する

これは単純なデータ移行の統計です。データ量は多くありませんが、問題は中間ステップの処理と検討にあります。

SQL最適化やインデックス最適化に関する内容はありませんので、お手柔らかにお願いします。

背景

ユーザーの眼科属性テーブルのレコード数は約 986w です。目的は、約 29w のレコードの属性値 (json 形式) の 8 つのフィールドを数値に解析し、チャート分析用の統計テーブルのレコードにダンプすることです。

以下の構造とデータは主に私が作成したものなので、真に受けないでください。

ユーザー眼科属性テーブルの構造は以下のとおりです。

CREATE TABLE `プロパティ` (
  `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 'フィールド値',
  主キー (`id`)、
  一意のキー `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) BTREE を使用、
  キー `idxUserIdRoleIdRecorderIdName` (`userId`,`roleId`,`recorderId`,`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Attributes';

問題分析

1. 属性値はJson形式であり、Json操作関数を使用して処理する必要があります。

属性値はJson形式なので、次のようになります。比較的大きな Json ですが、必要なフィールド値は 8 つだけであり、これらが抽出されてさまざまな統計指標に分類されます。

{......
    "視力"： {
        「ネイキッドアイ」: {
            "左": "0.9",
            "右": "0.6"
        },
        "修正"： {
            "左": "1",
            "右": "1"
        }
    },
    ......
    「軸長さ」: {
        "左": "21",
        "右": "12"
    },
    "コルネア半径": {
        "左": "34",
        "右": "33"
    },
    ......
}

したがって、Json 操作関数 json_extract(value,'$.key1.key2') を使用する必要があります。

ただし、この関数によって抽出された値は "" で囲まれることに注意してください。たとえば、上記のレコードに対して json_extract(value,'$.sight.nakedEye.left') を実行した結果は「22」になります。フィールド値が空の文字列である可能性もありますが、その場合、結果は「」になります。

したがって、結果内の "" を削除するには、replace 関数を使用する必要があります。フィールドを抽出するための最終的な式は、replace(json_extract(value,'$.sight.nakedEye.left'),'"','') です。

フィールドが存在しない場合、つまり外側のサイトが存在しないか、内側の左が存在しない場合には、結果は NULL になります。

2. フィールドの内容が標準化されておらず、乱雑である

理想的には、入力されたすべての数字が標準化されているため、上記の手順の後に、それらを抽出して新しいテーブルに直接インポートできます。

しかし、現実は残酷で、埋められたものはめちゃくちゃです。例えば：

数字 + 注記: 1 (協調性が低い)、1-\+ (これは高いか低いかを示すことを意味していると思います)
数字 + 単位: 上記と同様、1mm
最も多い値または間隔: 22.52/42.45、1-5
プレーンテキストの説明: 非協力的、記録できない
テキストと数字の混合説明: 前回より 10 多い、<1、1 未満、BD234/KD23

商品とビジネスのマッチングを探す以外に方法はありません。幸い、マッチング数は 4,000 強とそれほど多くありません。ざっと見るだけでも、だいたいの目安はつかめるでしょう。以下の解決策が得られました。

数字で始まる: 数字で始まるデータはすべて正しく記録されたデータであり、テキストの説明は省略できます。
複数の値または間隔：最初の数値だけを取得します
プレーンテキスト: データがないことを示しているため、除外されます。
テキストと数字の混合: 各問題を個別に分析し、他のものを削除した後にどれだけ残っているかを確認します。

具体的にはどうすればいいのでしょうか？

ステップ1: 通常の数値データと空のデータを除外する

WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // これはすでに null を除外できます AND `nakedEyeLeft` != ''

ステップ2: 数値が含まれていない場合は、NULLまたは空の文字列に設定します

SET nakedEyeLeft = IF(nakedEyeLeft NOT 正規表現 '[0-9]', '', nakedEyeLeft)

ステップ3: 数字で始まるデータの最初の値を抽出する

SET nakedEyeLeft = IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0)

これらを組み合わせると、

SET nakedEyeLeft = IF(nakedEyeLeft NOT 正規表現 '[0-9]'''、''' 
                      IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0))
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // これはすでに null を除外できます AND `nakedEyeLeft` != ''

PS: 1 つのフィールドを処理する SQL は単純に見えますが、一度に 8 つのフィールドがバッチで処理されるため、結合された SQL は非常に長くなります。

間違ったフィールドに入力しないように注意してください。

最後に残っているのは、4 番目のカテゴリです。テキストと数字が混在し、項目が 40 個以上あります。

いくつかは単純に見え、<1 や 1 未満などの正規表現を使用して自動的に処理できます。

レコードの増加値は、最後のレコードを見つけて計算する必要があります。最後のレコードと比較して 10 増加します。

残りのものはやや複雑で、BD234/KD23のように使用可能なデータを抽出するために人手による処理が必要です。

これを見た皆さんもちょっと面倒だなと思うのではないでしょうか？

歯を食いしばってやったつもりだったのですが、業者側は0として処理しろと言ってきました。後から0だと分かったら、ページから再度保存できます。

数字で始まるかどうかを判断する必要はなく、+ 0 を追加するだけです。数字で始まる場合は最初の数字が保持され、それ以外の場合は = 0 になります。

最終的なデータ形式の SQL は次のとおりです。

UPDATEプロパティ 
SET nakedEyeLeft = IF(nakedEyeLeft NOT 正規表現 '[0-9]'''、''、nakedEyeLeft + 0)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // これはすでに null を除外できます AND `nakedEyeLeft` != '';

3. コンテンツの抽出とフォーマットに時間がかかりすぎます。まだ 900 万件以上のレコードが残っています。

プロパティテーブルには 900 万を超えるデータ項目がありますが、必要なレコードの既知の条件は名前、所有者タイプ、およびタイプのみであり、既存のインデックスと一致させる方法はありません。

直接検索する場合は、テーブル全体をスキャンし、データを抽出してフォーマットする必要があります。さらに、他のテーブルを関連付け、統計指標の他のフィールドを補足する必要もあります。

この場合、統計テーブルを直接インポートすると、2 つのテーブルと関連テーブルが長時間ロックされ、その間は変更や挿入ができなくなるため、あまり現実的ではありません。

走査線の数を減らす

方法 1: name、ownerType、type にインデックスを追加して、スキャンされるレコードの数を 200,000 に減らします。

しかし、問題は、900 万のデータにインデックスを追加した後、インデックスを削除する必要があることです (ビジネス条件で必要ないため)。これにより、2 つの変動が発生します。

テーブルをロックするその後の処理時間と相まって、この問題は依然として非常に深刻です。

方法 2:ターゲットテーブルに関連付けることができる、レコード数が少ないテーブルを駆動テーブルとして使用します。

CREATE TABLE `プロパティ` (
  `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='Attributes';

テーブル内の ownerId はレコードテーブルに関連付けることができ、さらに前の条件 name、ownerType、type も関連付けられるため、``idxOwnerIdOwnerTypeNameType (ownerType、ownerId、name、type)'' にヒットします。

テーブル「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
) エンジン=InnoDB デフォルト文字セット=utf8mb4 コメント='record';

レコードテーブルは、name='ophthalmology record' を通じてインデックス idxName にヒットでき、スキャンされた行数はわずか 20,000 行です。属性テーブルは 290,000 行で、最終的にスキャンされた行数は約 300,000 行で、これは属性テーブルの完全なテーブルスキャンの 30 分の 1 です。！！。

データの抽出とフォーマットのためのテーブルロック時間を回避する

フィールドが8つあるため、各フィールドを抽出してフォーマットする必要があり、途中で判断が必要になります。このように、1 つの SQL ステートメント内の同じ抽出およびフォーマット操作を複数回実行する必要があります。

したがって、このような問題を回避するには、抽出およびフォーマットの結果を一時的に保存するための中間テーブルが必要です。

テーブル `propertytmp` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `value` mediumtext NOT NULL COMMENT 'フィールド値',
  `nakedEyeLeft` varchar(255) デフォルト NULL コメント 'ビジョン - 裸眼 - 左目',
  `nakedEyeRight` varchar(255) デフォルト NULL コメント 'ビジョン - 裸眼 - 右目',
  `correctionLeft` varchar(255) デフォルト NULL コメント 'Vision-Correction-Left Eye',
  `correctionRight` varchar(255) デフォルト NULL コメント 'Vision-Correction-Right Eye',
  `axisLengthLeft` varchar(255) DEFAULT NULL COMMENT '眼軸長 - 左目',
  `axisLengthRight` 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 デフォルト '0',
  主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

まずデータをテーブルにインポートし、これに基づいてデータを抽出し、フォーマットします。

最終実行結果の比較

データインポートの比較

結果: テーブル全体のスキャン、属性テーブルの中間テーブルへのインポート (40 秒)、属性テーブルの新しいインデックス + インポート (6 秒 + 3 秒)、関連付けのインポート (1.4 秒)。

他のテーブルと関連付ける必要があるため、期待したほど理想的ではありません。

中間テーブルデータの抽出: 7.5秒

`propertytmp` を更新します 
SET nakedEyeLeft = REPLACE(json_extract(value,'$.sight.axisLength.left'),'"',''),、
nakedEyeLeft = REPLACE(json_extract(value,'$.sight.nakedEye.left'),'"',''),
nakedEyeRight = REPLACE(json_extract(value,'$.sight.nakedEye.right'),'"',''),
訂正左 = REPLACE(json_extract(値,'$.sight.correction.left'),'"',''),
訂正右 = REPLACE(json_extract(値,'$.sight.correction.right'),'"',''),
左軸方向長さ = REPLACE(json_extract(値、'$.左軸方向長さ.左')、'"'、'')、
軸長右 = REPLACE(json_extract(値、'$.axisLength.right')、'"'、'')、
korneaRadiusLeft = REPLACE(json_extract(値、'$.korneaRadius.left')、'"'、'')、
korneaRadiusRight = REPLACE(json_extract(値、'$.korneaRadius.right')、'"'、'');

中間テーブルデータのフォーマット: 2.3 秒

正規化は思ったより早いです。

プロパティtmpの更新 
SET nakedEyeLeft = IF(nakedEyeLeft NOT REGEXP '[0-9]' AND nakedEyeLeft != '', '', nakedEyeLeft + 0), 
nakedEyeRight = IF(nakedEyeRight NOT REGEXP '[0-9]' AND nakedEyeRight != '', '', nakedEyeRight + 0), 
訂正左 = IF(訂正左 NOT REGEXP '[0-9]' AND 訂正左 != '', '', 訂正左 + 0),
訂正右 = IF(訂正右 NOT REGEXP '[0-9]' AND 訂正右 != '', '', 訂正右 + 0),
軸長さ左 = IF(軸長さ左 NOT 正規表現 '[0-9]' AND 軸長さ左 != '', '', 軸長さ左 + 0),
軸長右 = IF(軸長右 NOT REGEXP '[0-9]' AND 軸長右 != '', '', 軸長右 + 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` 正規表現 '[^0-9.]' = 1
       AND `nakedEyeLeft` != '')
  または (`nakedEyeRight` 正規表現 '[^0-9.]' = 1
      AND `nakedEyeRight` != '')
  または (`correctionLeft` 正規表現 '[^0-9.]' = 1
      AND `correctionLeft` != '')
  または (`correctionRight` 正規表現 '[^0-9.]' = 1
      AND `correctionRight` != '')
  または (`axisLengthLeft` 正規表現 '[^0-9.]' = 1
      AND `axisLengthLeft` != '')
  または (`axisLengthRight` 正規表現 '[^0-9.]' = 1
      AND `axisLengthRight` != '')
  または (`korneaRadiusLeft` 正規表現 '[^0-9.]' = 1
      AND `korneaRadiusLeft` != '')
  または (`korneaRadiusRight` 正規表現 '[^0-9.]' = 1
      AND `korneaRadiusRight` != '');

統計指標中間表

実際に統計指標テーブルをインポートする際には、空のデータを除外したり、補足のために他のテーブルを関連付けたりする必要もあるからです。

インジケータテーブルへの影響を軽減するために、インジケータテーブルの中間テーブルが同じ構造で作成され、ターゲットテーブルの ID が 10000 に自動増分されました。

属性中間テーブルからインジケーター中間テーブルにデータをインポートし、直接 INSERT ... SELECT FROM を実行すると、非常に高速になります。

もちろん、このステップは実際には少し過剰反応ですが、オンラインでの変動を避けるためには、慎重になる方が良いでしょう。

要約する

これはデータ移行の経験の簡単な記録です。

インデックス最適化やSQL最適化に関する内容はありませんが、パフォーマンスには誰もが注意を払い、ユーザーへの影響を考慮する必要があると思います。

これで、MySQL Json 内部フィールドを抽出して数値としてダンプする方法についての説明は終わりです。MySQL で Json ダンプを数値として抽出する関連の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: