MySQL で JSON フィールドのインデックスを作成する方法

複数値インデックスの概要

MySQL 8.0.17 以降、InnoDB は複数値インデックスの作成をサポートします (複数値インデックス)、このインデックスは、JSON ストレージ値配列の列に定義されたセカンダリ インデックスです。単一のデータ レコードに対して複数のインデックス レコードが存在する場合があります。このタイプのインデックスの特定の構文定義:

CAST(expression AS type ARRAY) (例: CAST(data->'$.zipcode' AS UNSIGNED ARRAY))。通常のインデックスと同様に、EXPLAIN でも表示できます。

複数値インデックスの作成

他のインデックスと同様、複数値インデックスはテーブルの作成時に追加することも、ALTER TABLE または CREATE INDEX を通じて作成することもできます。

JSON オブジェクト フィールド インデックス

構文

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

注: CAST 構文の外側には単一括弧の 2 つの層があります。 、1つ少なく書くとエラーが報告されます。

テスト ケース

PS: 記事内のケースは公式ドキュメントのケースを参照しており、単にテスト用であるため、名前やその他の点はあまり標準化されていません。実際の開発プロセスでは厳格でなければなりませんが、会社チームの開発仕様に従い、怠けてはいけません。

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 ));

実行プランをチェックして、インデックスが使用できることを確認します。

文字タイプ、utf8mb4 文字セットである必要があります。また、ソート ルールは utf8mb4_0900_as_cs です。そうでない場合は、エラーが報告されます。このバージョンではサポートされていません:

バイナリの複数値インデックスを作成したい場合バイナリ文字列の場合、並べ替えルールはバイナリである必要があります。そうでない場合はエラーが報告され、サポートされません。

並べ替えルールを変更すると、インデックスを正常に追加できます:

JSON 配列オブジェクト インデックス

構文

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

注: CAST 構文の外側には単一括弧の 2 つの層があります。 1 つ少なく書くと、エラーが報告されます。

テスト ケース

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 ));

実行プランを表示し、インデックスが使用できることを確認します:

作成されました結合インデックス内 複数値インデックス

構文

構文は、通常の結合インデックスの構文と似ており、左端の一致原則にも準拠します:

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

注:ここでは CAST 構文の外側で括弧を使用する必要があります。

テスト ケース

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;

実行計画を表示し、インデックスが使用できることを確認します:

制限事項複数値インデックスの

• 複数値インデックスには 1 つの属性の値のみを含めることができます

• このインデックスは現在 3 つの構文のみをサポートしています

現在、複数値インデックスを使用できる構文は MEMBER OF、JSON_CONTAINS()、および JSON_OVERLAB() の 3 つだけです。

• #インデックス値は配列に変換する必要があります

##( CAST( custinfo -> '$.key' AS UNSIGNED array)) 構文中の配列は省略可能です 強制的に追加する理由は追加しないと配列構造ではないからです 配列構造でない場合は上記3つ構文を直接使用することはできません。JSON_ARRAY() やその他のメソッドを通じて変換する必要があります。後でのみ使用できるため、インデックスが無効になります。したがって、インデックスを作成するフィールドが単一値フィールドであるか配列フィールドであるかに関係なく、array キーワードを追加する必要があります。

#このインデックスはテーブルの関連付けをサポートしていません

• #プレフィックス インデックスと組み合わせることはできません

• 複数値インデックスのオンライン作成はサポートされていません

• この文は、操作で ALGORITHM=COPY が使用されることを意味します。つまり、新しいテーブル構造を作成し、データをコピーしてインデックスを作成します。したがって、このプロセス中は DML 操作は許可されません。

複数値インデックスには、文字セット タイプ フィールドに対する明確な要件があります。

• バイナリ文字セットの照合規則バイナリである必要があります

utf8mb4 文字セットの照合順序は utf8mb4_0900_as_cs

他の文字セットまたは照合順序では複数値インデックスを作成できず、作成時にエラーが報告されます。現在のバージョンではサポートされていません。

アプリケーション シナリオ

多値インデックスのアプリケーション シナリオは非常に幅広いです。彼がいると、多くの関係テーブルが使用できなくなります。簡単な例を見てみましょう: ユーザー タグ。多くのシナリオでは、ユーザーには、1 背が高い、2 お金持ち、3 ハンサムなど、さまざまなタグが与えられます。後続の統計やクエリのフィルタリングをより効率的に行うために、このタグを次のように直接使用することはできません。ストレージでは、インデックスがないとクエリ効率が高くないため、ユーザーとタグの関係を保存するために関連付けテーブルがよく使用されます。しかし、多値インデックスを使用すると、タグをフィールドとして保存できるようになりました。

これは小さなシーンの 1 つにすぎません。同様のシーンが多数あります。ユーザーは任意の名前に変更でき、ラベルも他の属性に変更できます。物が複数の属性値を持つ限り、多対多の関係。この属性を他のテーブルに関連付ける必要がない場合は、複数値のインデックスを使用できます。複数値インデックスはテーブルの関連付けをサポートしていないため、このフィールドをテーブルの関連付けに使用する必要がある場合は適切ではありません。

以上がMySQL で JSON フィールドのインデックスを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。