So erstellen Sie einen Index für JSON-Felder in MySQL

Einführung in mehrwertige Indizes

Ab MySQL 8.0.17, InnoDB Unterstützt die Erstellung mehrwertiger Indizes. Dabei handelt es sich um sekundäre Indizes, die für die Spalten von JSON-Speicherwert-Arrays definiert sind. Für einen einzelnen Datensatz können mehrere Indexdatensätze vorhanden sein. Die spezifische Syntaxdefinition für diesen Indextyp:

CAST(expression AS type ARRAY), zum Beispiel CAST(data->'$.zipcode' AS UNSIGNED ARRAY). Sie können wie gewöhnliche Indizes auch in EXPLAIN angezeigt werden.

Mehrwertigen Index erstellen

Mehrwertige Indizes können wie andere Indizes beim Erstellen einer Tabelle hinzugefügt oder über ALTER TABLE oder CREATE INDEX erstellt werden.

JSON-Objektfeldindex

Syntax

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );

Hinweis: Es gibt hier zwei Schichten einzelner Klammern außerhalb der CAST-Syntax! , wenn Sie einen weniger schreiben, wird ein Fehler gemeldet!

Testfall

PS: Die Fälle im Artikel beziehen sich auf die Fälle in den offiziellen Dokumenten und werden nur als Tests verwendet, daher ist die Benennung nicht besonders Tatsächlich müssen Sie sich während des Entwicklungsprozesses strikt an die Entwicklungsvorgaben des Unternehmensteams halten und dürfen nicht faul sein!

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
	( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
	( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
	( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
	( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

Sehen Sie sich den Ausführungsplan an und stellen Sie fest, dass der Index verwendet werden kann:

Wenn nötig Erstellen Sie mehrere Werte für den Zeichentyp Index. Es muss sich um den Zeichensatz utf8mb4 handeln und die Sortierung ist utf8mb4_0900_as_cs. Andernfalls wird ein Fehler gemeldet:

Wenn Sie erstellen möchten Ein mehrwertiger Index für eine binäre Binärzeichenfolge. Die Sortierung muss binär sein, andernfalls wird der Fehler nicht unterstützt.

Nachdem die Sortierregeln geändert wurden, kann der Index erfolgreich hinzugefügt werden:

# 🎜🎜#JSON Array-Objektindex

Syntax

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );

Hinweis: Es gibt hier zwei Schichten einzelner Klammern außerhalb der CAST-Syntax! Wenn Sie einen weniger schreiben, wird ein Fehler gemeldet!

Testfall

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, NOW(), '[{"key":94536}]'),
	( NULL, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

Sehen Sie sich den Ausführungsplan an und stellen Sie fest, dass der Index verwendet werden kann:

#🎜 🎜##🎜 🎜#

Erstellen Sie einen mehrwertigen Index in einem kombinierten Index

Syntax

Die Syntax ähnelt der eines normalen kombinierten Index und folgt auch dem Leftmost-Matching-Prinzip:

ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );

Hinweis: Sie müssen hier Klammern außerhalb der CAST-Syntax verwenden!

Testfall

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	age tinyint(4) not null,
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, 24, NOW(), '[{"key":94536}]'),
	( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
alter table customers DROP INDEX idx_age_custinfo$list_modified ;
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified  );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

Sehen Sie sich den Ausführungsplan an und stellen Sie fest, dass der Index verwendet werden kann:

#🎜 🎜##🎜 🎜#

Einschränkungen des mehrwertigen Index

Ein mehrwertiger Index darf nur den Wert eins enthalten attribute

• Dieser Index unterstützt derzeit nur drei Syntaxen

• #🎜 🎜#Derzeit können nur die Syntax MEMBER OF, JSON_CONTAINS () und JSON_OVERLAB() mehrwertige Indizes verwenden.

Der Indexwert muss in ein Array umgewandelt werden

• ( CAST( custinfo -> '$.key' AS UNSIGNED array)) kann das Array in der Syntax weggelassen werden. Der Grund, warum das Hinzufügen erzwungen wird, liegt darin, dass es sich nicht um eine Array-Struktur handelt, wenn es nicht hinzugefügt wird. Wenn es sich nicht um eine Array-Struktur handelt, können die oben genannten drei Methoden nicht direkt verwendet werden. Die Syntax muss vor der Verwendung über JSON_ARRAY () konvertiert werden, was zum Ausfall des Index führt. Unabhängig davon, ob das zu indizierende Feld ein Einzelwertfeld oder ein Array-Feld ist, muss daher das Array-Schlüsselwort hinzugefügt werden.

Dieser Index unterstützt keine Tabellenzuordnung

• #🎜 🎜 #Kann nicht mit Präfixindex kombiniert werden. 🎜🎜## 🎜🎜#Dieser Satz bedeutet, dass die Operation ALGORITHM=COPY verwendet, dh der Index wird erstellt, indem eine neue Tabellenstruktur erstellt und die Daten dann dorthin kopiert werden. Daher sind DML-Operationen während dieses Prozesses nicht zulässig.

• Mehrwertige Indizes haben klare Anforderungen für Zeichensatztypfelder

#🎜 🎜#Die Sortierung des binären Zeichensatzes muss binär sein.

• Die Sortierung des utf8mb4-Zeichensatzes muss utf8mb4_0900_as_cs sein.Kein anderer Zeichensatz oder keine andere Sortierung kann einen mehrwertigen Index erstellen Beim Erstellen wird eine Fehlermeldung angezeigt, dass die aktuelle Version nicht unterstützt wird.

Anwendungsszenarien

Die Anwendungsszenarien mehrwertiger Indizes sind sehr breit! Mit ihm sind viele Beziehungstabellen nicht mehr verwendbar! Nehmen wir ein einfaches Beispiel: Benutzer-Tags In vielen Szenarien erhalten Benutzer verschiedene Tags, z. B. 1 groß, 2 reich, 3 gutaussehend. Um nachfolgende Statistiken oder Filterabfragen effizienter zu gestalten, können wir dieses Tag nicht direkt verwenden Da die Abfrageeffizienz ohne einen Index nicht hoch ist, wird häufig eine Zuordnungstabelle zum Speichern der Benutzer-Tag-Beziehung verwendet. Aber jetzt können wir bei mehrwertigen Indizes das Tag als Feld speichern!

Dies ist nur eine der kleinen Szenen. Der Benutzer kann es in alles ändern, und die Bezeichnung kann auch in jedes andere Attribut geändert werden, solange das Ding mehrere Attributwerte hat Wenn dieses Attribut nicht mit anderen Tabellen verknüpft werden muss, können Sie mehrwertige Indizes verwenden! Mehrwertige Indizes unterstützen keine Tabellenzuordnung und sind daher nicht geeignet, wenn Sie dieses Feld für die Tabellenzuordnung verwenden müssen.

Das obige ist der detaillierte Inhalt vonSo erstellen Sie einen Index für JSON-Felder in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!