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 개체 필드 인덱스

Syntax

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

참고: 여기 CAST 구문 외부에는 두 개의 단일 대괄호 레이어가 있습니다! , 하나 적으면 오류가 보고됩니다!

테스트 사례

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 배열 객체 인덱스

Syntax

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

참고: 여기 CAST 구문 외부에는 두 개의 단일 괄호 레이어가 있습니다! 하나라도 적게 쓰면 오류가 보고됩니다!

테스트 케이스

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

실행 계획을 보고 인덱스를 사용할 수 있는지 확인합니다.

결합된 인덱스에 다중 값 인덱스 생성

Syntax

구문은 일반 구문과 유사합니다. 결합된 인덱스이며 가장 왼쪽 일치 원칙도 따릅니다.

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;

실행 계획을 보고 인덱스를 사용할 수 있는지 확인하세요.

다중 값 인덱스의 제한 사항

• 다중 값 인덱스는 값만 포함할 수 있습니다. of one attribute

• 인덱스는 현재 세 가지 구문만 지원합니다.

현재 다중 값 인덱스에는 MEMBER OF, JSON_CONTAINS() 및 JSON_OVERLAB()만 사용할 수 있습니다.

• 인덱스 값을 배열로 변환해야 합니다

(CAST( custinfo -> '$.key' AS UNSIGNED array)). 구문에서 배열을 생략할 수 있습니다. 필수사항은 추가하지 않으면 배열 구조가 아니기 때문입니다. 배열 구조가 아니면 위의 세 가지 구문을 직접 사용할 수 없기 때문에 사전에 JSON_ARRAY() 및 기타 방법을 통해 변환해야 합니다. 사용할 수 있습니다. 그러면 인덱스가 실패하게 됩니다! 따라서 인덱싱할 필드가 단일 값 필드인지 배열 필드인지에 관계없이 array 키워드를 추가해야 합니다.

• 이 인덱스는 테이블 연관을 지원하지 않습니다.

• 접두사 인덱스와 결합할 수 없습니다.

• 다중 값 인덱스의 온라인 생성을 지원하지 않습니다.

이 문장은 다음을 의미합니다. 작업에서 ALGORITHM=COPY를 사용한다는 것은 새 테이블 구조를 생성한 다음 거기에 데이터를 복사하여 인덱스를 생성하는 것을 의미합니다. 따라서 이 프로세스 중에는 DML 작업이 허용되지 않습니다.

• 다중 값 인덱스에는 문자 집합 유형 필드에 대한 명확한 요구 사항이 있습니다.

이진 문자 집합의 데이터 정렬은 이진이어야 합니다.

utf8mb4 문자 집합의 데이터 정렬은 utf8mb4_0900_as_cs여야 합니다.

다른 문자 집합 또는 정렬 어떤 규칙도 다중 값 인덱스를 생성할 수 없으며, 생성 시 오류가 보고됩니다. 현재 버전에서는 이를 지원하지 않습니다.

응용 시나리오

다중값 인덱스의 응용 시나리오는 매우 광범위합니다! 그와 함께라면 더 이상 많은 관계 테이블을 사용할 수 없습니다! 간단한 예를 들어 보겠습니다. 사용자 태그 많은 시나리오에서 사용자에게는 키 1, 부자 2, 잘생긴 3과 같은 다양한 태그가 제공됩니다. 후속 통계 또는 쿼리 필터링을 보다 효율적으로 만들기 위해 이 태그를 직접 사용할 수는 없습니다. 필드. 인덱스가 없으면 쿼리 효율성이 높지 않기 때문에 사용자-태그 관계를 저장하기 위해 연관 테이블을 사용하는 경우가 많습니다. 하지만 이제 다중 값 인덱스를 사용하면 태그를 필드로 저장할 수 있습니다!

이것은 작은 장면 중 하나일 뿐입니다. 사용자는 이를 무엇이든 변경할 수 있으며, 레이블은 속성 값이 여러 개 있는 한 다른 속성으로 변경할 수도 있습니다. 다대다 관계에서 이 속성을 다른 테이블과 연결할 필요가 없는 경우 다중 값 인덱스를 사용할 수 있습니다! 다중 값 인덱스는 테이블 연결을 지원하지 않으므로 테이블 연결에 이 필드를 사용해야 하는 경우에는 적합하지 않습니다.

위 내용은 MySQL에서 JSON 필드에 대한 인덱스를 생성하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!