MySQL extrahiert interne JSON-Felder und gibt sie als Zahlen aus.

Dies ist nur eine Statistik einer einfachen Datenmigration. Die Datenmenge ist nicht groß, aber das Problem liegt in der Verarbeitung und Berücksichtigung einiger Zwischenschritte.

Es gibt keine Inhalte zur SQL-Optimierung und Indexoptimierung, seien Sie also bitte nachsichtig.

Hintergrund

Die Anzahl der Datensätze in der ophthalmologischen Attributtabelle des Benutzers beträgt etwa 986 W. Der Zweck besteht darin, acht Felder der Attributwerte (JSON-Format) von etwa 29 W-Datensätzen in Zahlen zu zerlegen und sie zur Diagrammanalyse in Datensätze der statistischen Tabelle zu übertragen.

Die folgenden Strukturen und Daten habe ich mir größtenteils ausgedacht, nehmen Sie sie also nicht ernst.

Die Struktur der Attributtabelle für Benutzerophthalmologie ist wie folgt

CREATE TABLE `Eigenschaft` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `ownerId` int(11) NOT NULL COMMENT 'Datensatz-ID oder Vorlagen-ID',
  `ownerType` tinyint(4) NICHT NULL KOMMENTAR 'Typ. 0: Datensatz 1: Vorlage',
  `recorderId` bigint(20) NICHT NULL STANDARD '0' KOMMENTAR 'Recorder-ID',
  `userId` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Benutzer-ID',
  `roleId` bigint(20) NICHT NULL STANDARD '0' KOMMENTAR 'Rollen-ID',
  `Typ` tinyint(4) NICHT NULL KOMMENTAR 'Feldtyp. 0: Text 1: Option 2: Zeit 3: Bild 4: ICD10 9: Neues Bild',
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'Feldname',
  `value` mediumtext NOT NULL COMMENT 'Feldwert',
  Primärschlüssel (`id`),
  EINZIGARTIGER SCHLÜSSEL `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) MIT BTREE,
  SCHLÜSSEL `idxUserIdRoleIdRecorderIdName` (`userId`,`roleId`,`recorderId`,`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Attribute';

Problemanalyse

1. Der Attributwert liegt im JSON-Format vor und muss mit JSON-Operationsfunktionen verarbeitet werden

Weil der Attributwert wie folgt im JSON-Format vorliegt. Ein relativ großes JSON, allerdings werden nur 8 Feldwerte benötigt, die extrahiert und in verschiedene statistische Kennzahlen eingeteilt werden.

{ ......
    "Sicht": {
        "nacktesAuge": {
            "links": "0,9",
            "rechts": "0,6"
        },
        "Korrektur": {
            "links": "1",
            "rechts": "1"
        }
    },
    ......
    "axialLength": {
        "links": "21",
        "rechts": "12"
    },
    "korneaRadius": {
        "links": "34",
        "rechts": "33"
    },
    ......
}

Daher müssen Sie die JSON-Operationsfunktion verwenden: json_extract(value,'$.key1.key2').

Es ist jedoch zu beachten, dass der von dieser Funktion extrahierte Wert in "" eingeschlossen ist. Beispielsweise ist das Ergebnis der Ausführung von json_extract(value,'$.sight.nakedEye.left') für den obigen Datensatz „22“. Es ist auch möglich, dass der Feldwert eine leere Zeichenfolge ist. In diesem Fall ist das Ergebnis „“.

Daher müssen Sie die Funktion „Ersetzen“ verwenden, um das „“ im Ergebnis zu löschen. Der endgültige Ausdruck zum Extrahieren des Felds lautet: replace(json_extract(value,'$.sight.nakedEye.left'),'"','').

Das Ergebnis ist NULL, wenn das Feld nicht existiert; entweder existiert die äußere Sicht nicht oder die innere linke Sicht nicht.

2. Der Feldinhalt ist nicht standardisiert und chaotisch

Im Idealfall sind alle eingetragenen Zahlen standardisiert, sodass sie nach dem obigen Schritt extrahiert und direkt in die neue Tabelle importiert werden können.

Die Realität ist jedoch grausam und die ausgefüllten Dinge sind ein Chaos. Zum Beispiel:

• Zahl + Anmerkungen: 1 (schlechte Koordination), 1-\+ (ich nehme an, dies soll hoch oder niedrig anzeigen)
• Nummer + Einheit: Ähnlich wie oben, 1mm
• Die meisten Werte oder Intervalle: 22,52/42,45, 1-5
• Klartextbeschreibung: Nicht kooperativ, Aufzeichnung nicht möglich
• Gemischte Beschreibung aus Text und Zahlen: 10 mehr als beim letzten Mal, <1, weniger als 1, BD234/KD23

Es bleibt Ihnen nichts anderes übrig, als nach Produkt- und Geschäftsübereinstimmungen zu suchen. Glücklicherweise gibt es davon nicht viele, nur etwas über 4.000. Sie können sich einen Eindruck verschaffen, indem Sie einfach einen kurzen Blick darauf werfen. Es wurden folgende Lösungen erhalten:

• Beginnt mit einer Zahl: Bei Daten, die mit einer Zahl beginnen, handelt es sich um korrekt erfasste Daten, die Textbeschreibung kann weggelassen werden
• Mehrere Werte oder Intervalle: Nehmen Sie einfach die erste Zahl
• Klartext: bedeutet, dass keine Daten vorliegen und daher ausgeschlossen sind.
• Gemischter Text und Zahlen: Analysieren Sie jedes Problem einzeln und sehen Sie, wie viel übrig bleibt, nachdem Sie die anderen entfernt haben.

Wie geht das konkret?

Schritt 1: Normale numerische Daten und leere Daten ausschließen

WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // Dies kann bereits null ausschließen AND `nakedEyeLeft` != ''

Schritt 2: Wenn es keine Zahl enthält, setzen Sie es auf NULL oder eine leere Zeichenfolge

SETze nakedEyeLeft = WENN(nakedEyeLeft NICHT regulärer Ausdruck '[0-9]', '', nakedEyeLeft)

Schritt 3: Extrahieren Sie den ersten Wert der Daten, der mit einer Zahl beginnt

SETZE nakedEyeLeft = WENN((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0)

Zusammen kombiniert,

SETze nakedEyeLeft = WENN(nakedEyeLeft NICHT regulärer Ausdruck '[0-9]''', '', 
                      WENN((nacktesAugeLinks + 0 = 0), nacktesAugeLinks, nacktesAugeLinks + 0))
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // Dies kann bereits null ausschließen AND `nakedEyeLeft` != ''

PS: Das SQL zur Verarbeitung eines Feldes sieht einfach aus, aber da jeweils 8 Felder stapelweise verarbeitet werden, wird das kombinierte SQL sehr lang.

Achten Sie darauf, nicht das falsche Feld auszufüllen.

Als letztes bleibt die vierte Kategorie: gemischter Text und Zahlen mit mehr als 40 Elementen.

Einige davon sehen einfach aus und können mit regulären Ausdrücken automatisch verarbeitet werden, beispielsweise <1 oder kleiner als 1.

Der Wachstumswert des Datensatzes muss durch Ermittlung des letzten Datensatzes berechnet werden: eine Steigerung von 10 im Vergleich zum letzten Datensatz.

Die übrigen sind etwas komplizierter und erfordern eine menschliche Verarbeitung, um nutzbare Daten zu extrahieren, wie z. B. BD234/KD23

Ich frage mich, ob jeder, der das sieht, das auch ein wenig problematisch findet?

Ich dachte, ich hätte es zähneknirschend getan, aber das Unternehmen sagte, ich solle es einfach als 0 verarbeiten. Wenn ich später feststelle, dass es 0 ist, kann ich es über die Seite erneut speichern.

Es muss nicht ermittelt werden, ob es mit einer Zahl beginnt, addieren Sie einfach + 0; beginnt es mit einer Zahl, bleibt die erste Zahl erhalten, andernfalls = 0.

Das endgültige SQL-Datenformat ist:

UPDATE-Eigenschaft 
SETze nakedEyeLeft = WENN(nakedEyeLeft NICHT regulärer Ausdruck '[0-9]''', '', nakedEyeLeft + 0)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // Dies kann bereits null ausschließen AND `nakedEyeLeft` != '';

3. Das Extrahieren von Inhalt und Format dauert zu lange. Es sind immer noch über 9 Millionen Datensätze vorhanden.

Die Eigenschaftentabelle enthält mehr als 9 Millionen Datenelemente, aber die einzigen bekannten Bedingungen für die erforderlichen Datensätze sind Name, Besitzertyp und Typ, und es gibt keine Möglichkeit, eine Übereinstimmung mit dem vorhandenen Index herzustellen.

Bei einer direkten Suche müssen Sie die gesamte Tabelle durchsuchen sowie die Daten extrahieren und formatieren. Darüber hinaus müssen Sie auch andere Tabellen verknüpfen und einige andere Felder mit statistischen Indikatoren ergänzen.

Wenn in diesem Fall die Statistiktabelle direkt importiert wird, führt dies dazu, dass die beiden Tabellen und die zugehörige Tabelle für lange Zeit miteinander verbunden sind und während dieser Zeit keine Änderungen oder Einfügungen vorgenommen werden können, was nicht sehr realistisch ist.

Reduzieren Sie die Anzahl der Scanzeilen

Methode 1: Fügen Sie Indizes zu Name, Besitzertyp und Typ hinzu, um die Anzahl der gescannten Datensätze auf 200.000 zu reduzieren.

Das Problem besteht jedoch darin, dass nach dem Hinzufügen von Indizes zu 9 Millionen Daten die Indizes gelöscht werden müssen (da sie aufgrund der Geschäftsbedingungen nicht erforderlich sind), was zu zwei Schwankungen führt.

Zusammen mit der anschließenden Verarbeitungszeit zum Sperren der Tabelle ist das Problem immer noch sehr ernst.

Methode 2: Verwenden Sie als Treibertabelle eine Tabelle mit weniger Datensätzen, die mit der Zieltabelle verknüpft werden kann.

CREATE TABLE `Eigenschaft` (
  `ownerId` int(11) NOT NULL COMMENT 'Datensatz-ID oder Vorlagen-ID',
  `ownerType` tinyint(4) NICHT NULL KOMMENTAR 'Typ. 0: Datensatz 1: Vorlage',
  `Typ` tinyint(4) NICHT NULL KOMMENTAR 'Feldtyp. 0: Text 1: Option 2: Zeit 3: Bild 4: ICD10 9: Neues Bild',
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'Feldname',
  `value` mediumtext NOT NULL COMMENT 'Feldwert',
    Andere Felder weglassen UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Attribute';

Die OwnerId in der Tabelle kann mit der Datensatztabelle sowie den vorherigen Bedingungen Name, OwnerType und Typ verknüpft werden, sodass nur „idxOwnerIdOwnerTypeNameType (OwnerType, OwnerId, Name, Typ)“ angezeigt wird.

CREATE TABLE `Krankenakte` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Datensatzname',
  `Typ` tinyint(4) NICHT NULL STANDARD '0' KOMMENTAR 'Datensatztyp. ',
    Andere Felder weglassen KEY `idxName` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Datensatz';

Die Datensatztabelle kann den Index idxName über name='ophthalmology record' erreichen, und die Anzahl der gescannten Zeilen beträgt nur 20.000 plus die Attributtabelle 290.000. Die endgültige Anzahl der gescannten Zeilen beträgt also nur etwa 300.000, was 30-mal weniger ist als der vollständige Tabellenscan der Attributtabelle! ! ! .

Vermeiden Sie Tabellensperrzeiten für die Datenextraktion und -formatierung

Da es 8 Felder gibt, muss jedes Feld extrahiert und formatiert werden, und dazwischen ist eine Beurteilung erforderlich. Auf diese Weise müssen dieselben Extraktions- und Formatierungsvorgänge in einer SQL-Anweisung mehrmals ausgeführt werden.

Um solche Probleme zu vermeiden, ist daher eine Zwischentabelle erforderlich, um die Extraktions- und Formatierungsergebnisse vorübergehend zu speichern.

CREATE TABLE `propertytmp` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
   `value` mediumtext NOT NULL COMMENT 'Feldwert',
  `nakedEyeLeft` varchar(255) DEFAULT NULL COMMENT 'Sehvermögen - bloßes Auge - linkes Auge',
  `nakedEyeRight` varchar(255) DEFAULT NULL COMMENT 'Sehvermögen - bloßes Auge - rechtes Auge',
  `correctionLeft` varchar(255) DEFAULT NULL COMMENT 'Sehkorrektur-Linkes Auge',
  `correctionRight` varchar(255) DEFAULT NULL COMMENT 'Sehkorrektur-Rechtes Auge',
  `axialLengthLeft` varchar(255) DEFAULT NULL COMMENT 'Axiallänge - linkes Auge',
  `axialLengthRight` varchar(255) DEFAULT NULL COMMENT 'Axiallänge - rechtes Auge',
  `korneaRadiusLeft` varchar(255) DEFAULT NULL COMMENT 'Hornhautverkrümmung - linkes Auge',
  `korneaRadiusRight` varchar(255) DEFAULT NULL COMMENT 'Hornhautverkrümmung - rechtes Auge',
  `aktualisiert` datetime NICHT NULL KOMMENTAR 'Aktualisierungszeit',
  `gelöscht` tinyint(1) NOT NULL DEFAULT '0',
  PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

Importieren Sie die Daten zunächst in die Tabelle, extrahieren Sie diese darauf basierend und formatieren Sie sie anschließend.

Vergleich der endgültigen Ausführungsergebnisse

Datenimportvergleich

Ergebnisse: Vollständiger Tabellenscan, Import der Attributtabelle in Zwischentabelle (40 s), neuer Index der Attributtabelle + Import (6 s + 3 s), Assoziationsimport (1,4 s).

Da es mit anderen Tabellen verknüpft werden muss, ist es nicht so ideal wie erwartet.

Zwischentabellendatenextraktion: 7,5 s

UPDATE `Eigenschafttmp` 
SETze nakedEyeLeft = REPLACE(json_extract(Wert,'$.sight.axialLength.left'),'"',''),
nakedEyeLeft = ERSETZEN(json_extract(Wert,'$.sight.nakedEye.left'),'"',''),
nakedEyeRight = ERSETZEN(json_extract(Wert,'$.sight.nakedEye.right'),'"',''),
KorrekturLinks = ERSETZEN(json_extract(Wert,'$.sight.correction.left'),'"',''),
KorrekturRechts = ERSETZEN(json_extract(Wert,'$.sight.correction.right'),'"',''),
axialLengthLeft = ERSETZEN(json_extract(Wert,'$.axialLength.left'),'"',''),
axialLengthRight = ERSETZEN(json_extract(Wert,'$.axialLength.right'),'"',''),
korneaRadiusLeft = ERSETZEN(json_extract(Wert,'$.korneaRadius.left'),'"',''),
korneaRadiusRight = ERSETZEN(json_extract(Wert,'$.korneaRadius.right'),'"','');

Zwischenformatierung der Tabellendaten: 2,3 s

Die Regularisierung geht schneller als ich dachte.

UPDATE propertytmp 
SETze nakedEyeLeft = WENN(nakedEyeLeft NICHT REGEXP '[0-9]' UND nakedEyeLeft != '', '', nakedEyeLeft + 0), 
nakedEyeRight = WENN(nakedEyeRight NICHT REGEXP '[0-9]' UND nakedEyeRight != '', '', nakedEyeRight + 0), 
KorrekturLinks = WENN(KorrekturLinks NICHT REGEXP '[0-9]' UND KorrekturLinks != '', '', KorrekturLinks + 0),
KorrekturRechts = WENN(KorrekturRechts NICHT REGEXP '[0-9]' UND KorrekturRechts != '', '', KorrekturRechts + 0),
axialLengthLeft = WENN(axialLengthLeft NICHT REGEXP '[0-9]' UND axialLengthLeft != '', '', axialLengthLeft + 0),
axialLengthRight = WENN(axialLengthRight NICHT REGEXP '[0-9]' UND axialLengthRight != '', '', axialLengthRight + 0),
korneaRadiusLeft = WENN(korneaRadiusLeft NICHT REGEXP '[0-9]' UND korneaRadiusLeft != '', '', korneaRadiusLeft + 0),
korneaRadiusRight = WENN(korneaRadiusRight NICHT REGEXP '[0-9]' UND korneaRadiusRight != '', '', korneaRadiusRight + 0)
WO (`nakedEyeLeft` REGEXP '[^0-9.]' = 1
       UND `nakedEyeLeft` != '')
  ODER (`nakedEyeRight` REGEXP '[^0-9.]' = 1
      UND `nakedEyeRight` != '')
  ODER (`correctionLeft` REGEXP '[^0-9.]' = 1
      UND `KorrekturLinks` != '')
  ODER (`KorrekturRechts` REGEXP '[^0-9.]' = 1
      UND `KorrekturRechts` != '')
  ODER (`axialLengthLeft` REGEXP '[^0-9.]' = 1
      UND `axialLengthLeft` != '')
  ODER (`axialLengthRight` REGEXP '[^0-9.]' = 1
      UND `axialLengthRight` != '')
  ODER (`korneaRadiusLeft` REGEXP '[^0-9.]' = 1
      UND `korneaRadiusLeft` != '')
  ODER (`korneaRadiusRight` REGEXP '[^0-9.]' = 1
      UND `korneaRadiusRight` != '');

Statistische Indikatoren Zwischentabelle

Denn beim eigentlichen Einlesen der Kennzahlentabelle müssen ebenfalls Leerdaten ausgeschlossen und weitere Tabellen zur Ergänzung zugeordnet werden.

Um die Auswirkungen auf die Indikatortabelle zu reduzieren, wurde eine Zwischentabelle der Indikatortabelle mit derselben Struktur und einer ID-Auto-Inkrementierung der Zieltabelle + 10000 erstellt.

Importieren Sie die Daten aus der Attribut-Zwischentabelle in die Indikator-Zwischentabelle und führen Sie dann direkt INSERT ... SELECT FROM aus, was sehr schnell geht.

Natürlich ist dieser Schritt eigentlich etwas übertrieben, aber um gewisse Schwankungen im Internet zu vermeiden, ist es besser, vorsichtig zu sein.

Zusammenfassen

Dies ist ein einfacher Bericht über Erfahrungen mit der Datenmigration.

Es gibt keinen Inhalt zur Indexoptimierung oder SQL-Optimierung. Ich denke nur, dass jeder auf die Leistung achten und die Auswirkungen auf die Benutzer berücksichtigen muss.

Dies ist das Ende dieses Artikels über das Extrahieren interner MySQL-JSON-Felder und deren Ausgabe als Zahlen. Weitere verwandte MySQL-JSON-Dumps als Zahlen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!