JSON 검색 인덱스

JSON 문서를 저장했다는 이유만으로 검색이 빨라지지는 않습니다.

공개 범위가 특정 값인 게시글과 태그에 MySQL이 포함된 게시글을 자주 찾으면 매 행의 JSON을 해석하는 비용이 쌓입니다.

문자열 LIKE로 배열을 찾으면 mysql2나 설명문까지 잘못 맞을 수 있습니다.

스칼라 경로는 생성 열로 투영해 평범한 B+Tree 인덱스를 만듭니다.

JSON 배열 원소 포함 여부는 MVI(Multi-Valued Index, 다중 값 인덱스)를 사용할 수 있습니다.

일반 B+Tree는 한 행에서 한 스칼라 키를 만들지만 MVI는 배열 원소마다 인덱스 항목을 만들어 한 행이 여러 키로 검색됩니다.

두 방식 모두 문서 유연성의 일부를 물리 스키마로 승격하는 결정이므로 실제 쿼리 유형과 카디널리티를 근거로 최소한만 추가합니다.

post_search_document는 post_id와 search_metadata JSON을 보관합니다.

공개 범위와 publishedOn은 스칼라 생성 열, 태그는 문자열 배열입니다.

문서 검사는 필수와 원소 유형을 보장합니다.

공개 범위 목록 쿼리, 게시 날짜 범위, 단일 태그 MEMBER OF가 워크로드입니다.

복합 정렬이나 태그 간 관계가 필요해지면 JSON 인덱스를 계속 늘리지 않고 정규 연결 테이블을 검토합니다.

JSON 경로 검색의 한계

WHERE JSON_UNQUOTE(metadata->'$.visibility')='PUBLIC'은 별도 표현식 인덱스가 없으면 모든 행을 평가합니다.

CAST(메타데이터 AS CHAR) LIKE '%MySQL%'은 배열 포함 여부가 아니라 문서 문자열 포함을 검사해 거짓 양성을 만듭니다.

DROP TABLE IF EXISTS post_search_bad;
CREATE TABLE post_search_bad(
 post_id BIGINT UNSIGNED PRIMARY KEY,metadata JSON NOT NULL
) ENGINE=InnoDB;
INSERT INTO post_search_bad VALUES
 (1,JSON_OBJECT('visibility','PUBLIC','tags',JSON_ARRAY('mysql','index'))),
 (2,JSON_OBJECT('visibility','MEMBERS_ONLY','tags',JSON_ARRAY('mysql2'))),
 (3,JSON_OBJECT('visibility','MEMBERS_ONLY','description','not a tag: mysql'));
EXPLAIN SELECT post_id FROM post_search_bad
WHERE JSON_UNQUOTE(metadata->'$.visibility')='PUBLIC';
SELECT post_id FROM post_search_bad
WHERE CAST(metadata AS CHAR) LIKE '%mysql%';

인덱스 없는 공개 범위 조건은 각 행의 JSON 값을 평가해야 합니다. 이 fixture에서 LIKE는 배열 원소뿐 아니라 mysql2와 설명문도 일치시키므로 세 행이 대상입니다. 실제 접근 방식·추정 행 수는 EXPLAIN에서 확인합니다.

fixture rows=3
visibility EXPLAIN access/rows=not measured here
exact mysql tag rows=1
LIKE mysql rows=3
false positives=2

JSON 함수 자체가 나쁜 것이 아니라 인덱스 표현식과 쿼리 표현식이 일치하지 않는 것이 문제입니다.

읽기 주체마다 JSON_UNQUOTE, CAST 길이, 정렬 규칙이 다르면 같은 경로를 여러 방식으로 해석합니다.

생성 열은 이름과 SQL 유형을 부여해 그 해석을 한곳에 고정합니다.

visibility_code VARCHAR(20), published_on DATE처럼 범위를 선언하고 문서와 함께 자동 갱신합니다.

배열을 스칼라 문자열로 압축하지 않기

태그를 쉼표 조인해 인덱스하면 이스케이프, 순서, 부분 문자열 문제가 생깁니다.

MVI는 배열의 각 스칼라를 인덱스 키로 다룹니다.

원소가 객체이거나 중첩 배열이라면 지원 방식과 쿼리 요구를 다시 검토합니다.

검색 경로의 스키마 승격

visibility_code는 JSON 내부 중복이 아니라 생성된 투영입니다.

쓰기 주체는 메타데이터만 바꾸고 DB가 열을 계산합니다.

쿼리는 JSON 경로를 반복하지 않고 visibility_code를 사용합니다.

다중 값 인덱스는 한 문서에서 여러 인덱스 항목을 만듭니다.

태그 수가 많을수록 인덱스 크기와 갱신 비용이 증가합니다.

원소 수와 결과 행 수를 따로 세기 스키마가 허용하는 문서 A의 tags는 ["mysql","index"], B의 tags는 ["mysql","json","ops"]입니다. 이 두 문서의 태그 MVI에 대응하는 원소 키는 모두 몇 개인가요? mysql MEMBER OF 조건을 만족하는 문서 수와 같은지도 판단하세요.

maxItems를 스키마에 두고 실제 행 증가를 관찰합니다.

쿼리 문법이 인덱스와 맞는지 EXPLAIN으로 확인하기

배열 포함 조건에는 MEMBER OF·JSON_CONTAINS·JSON_OVERLAPS 등을 사용합니다. 인덱스를 이용할 수 있는지 판단하려면 경로와 원소 타입·길이·비교 규칙이 맞는지도 확인해야 하며, 정확한 포함 의미 자체가 MVI 사용을 요구하지는 않습니다.

옵티마이저와 버전에 따라 지원 조건이 다르므로 DDL을 만들었다는 사실만으로 완료하지 않고 대표 매개변수로 EXPLAIN ANALYZE를 저장합니다.

MySQL 8.4 메모: 다중 값 인덱스는 CAST(JSON 배열 AS CHAR(N) 배열) 표현식을 사용합니다. 원소 유형과 길이가 스키마 검증과 정확히 맞아야 하며 인덱스당 제약을 공식 문서로 확인합니다.

스칼라와 배열 인덱스

DDL은 visibility_code와 published_on을 STORED 생성 열로 정의합니다.

태그 배열은 JSON 스키마로 최대 12개 문자열을 제한하고 MVI 표현식을 추가합니다.

DROP TABLE IF EXISTS post_search_document;
CREATE TABLE post_search_document(
 post_id BIGINT UNSIGNED PRIMARY KEY,
 search_metadata JSON NOT NULL,
 visibility_code VARCHAR(20) GENERATED ALWAYS AS(
  JSON_UNQUOTE(JSON_EXTRACT(search_metadata,'$.visibility'))
 ) STORED,
 published_on DATE GENERATED ALWAYS AS(
  CAST(JSON_UNQUOTE(JSON_EXTRACT(search_metadata,'$.publishedOn')) AS DATE)
 ) STORED,
 FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,
 CONSTRAINT chk_post_search_schema CHECK(JSON_SCHEMA_VALID(
  '{"type":"object","required":["visibility","publishedOn","tags"],"additionalProperties":false,
    "properties":{"visibility":{"type":"string","enum":["PUBLIC","MEMBERS_ONLY","PRIVATE"]},
    "publishedOn":{"type":"string","format":"date"},
    "tags":{"type":"array","minItems":1,"maxItems":12,"uniqueItems":true,
      "items":{"type":"string","minLength":1,"maxLength":40}}}}',search_metadata)),
 INDEX ix_post_visibility_publish(visibility_code,published_on,post_id),
 INDEX ix_post_published(published_on,post_id),
 INDEX ix_post_tag((CAST(search_metadata->'$.tags' AS CHAR(40) ARRAY)))
) ENGINE=InnoDB;

SET @post_id=(SELECT id FROM posts ORDER BY id LIMIT 1);
SET @other_post_id=(SELECT id FROM posts WHERE id<>@post_id ORDER BY id LIMIT 1);
INSERT INTO post_search_document(post_id,search_metadata)
VALUES
 (@post_id,JSON_OBJECT('visibility','PUBLIC','publishedOn','2026-07-01',
   'tags',JSON_ARRAY('mysql','index'))),
 (@other_post_id,JSON_OBJECT('visibility','MEMBERS_ONLY','publishedOn','2026-07-10',
   'tags',JSON_ARRAY('mysql2','image')));

EXPLAIN ANALYZE
SELECT post_id FROM post_search_document
WHERE visibility_code='PUBLIC'
 AND published_on>='2026-07-01' AND published_on<'2026-08-01';

EXPLAIN ANALYZE
SELECT post_id FROM post_search_document
WHERE 'mysql' MEMBER OF(search_metadata->'$.tags');

공개 범위와 게시 날짜 조건은 복합 B+Tree를, 정확한 태그 포함 여부는 다중 값 인덱스를 사용할 수 있습니다.

PUBLIC July rows=1
exact mysql members=1
mysql2 false positives=0
visibility plan=verify with EXPLAIN ANALYZE
tag plan=verify with EXPLAIN ANALYZE

visibility 경로가 누락된 문서는 필수 키 CHECK를 만족하지 못합니다. 여기서는 생성 열과 CHECK의 내부 평가 순서에 의존하지 않습니다.

MySQL 8.4의 이 JSON 스키마에서 format: date를 달력 검사로 간주하면 안 됩니다. DATE 변환과 허용되는 0·불완전 날짜는 SQL 모드에도 영향을 받습니다. 수신 경계에서 실제 날짜를 검증한 뒤 전달합니다.

태그 이름 변경을 문서마다 JSON_SET으로 수행하면 대량 재작성이 필요합니다.

태그가 관리되는 엔티티가 되면 tags와 post_tag 연결로 승격합니다.

MVI는 작은 독립 표시명 집합에 적합합니다.

인덱스 예산에 쓰기 비용 포함하기

메타데이터 갱신은 JSON 행과 스칼라 인덱스, 태그 원소 항목을 갱신합니다.

읽기 지연 시간뿐 아니라 삽입/갱신 p95, 인덱스 바이트, 리두 로그 볼륨을 변경 전/변경 후로 비교합니다.

정확성·불일치·크기 측정

MySQL과 mysql2를 함께 넣어 부분 문자열 거짓 양성이 없는지 확인합니다.

생성 열이 JSON 경로와 일치하는지 검사하고 태그 배열 길이 분포를 집계합니다.

SELECT post_id FROM post_search_document
WHERE 'mysql' MEMBER OF(search_metadata->'$.tags');

SELECT post_id FROM post_search_document
WHERE visibility_code<>JSON_UNQUOTE(search_metadata->'$.visibility')
 OR published_on<>CAST(JSON_UNQUOTE(search_metadata->'$.publishedOn') AS DATE);

SELECT JSON_LENGTH(search_metadata,'$.tags') tag_count,COUNT(*) posts
FROM post_search_document GROUP BY tag_count ORDER BY tag_count;

SELECT post_id FROM post_search_document
WHERE NOT JSON_SCHEMA_VALID(
 '{"type":"object","required":["visibility","publishedOn","tags"],
   "properties":{"visibility":{"type":"string"},"publishedOn":{"type":"string","format":"date"},
   "tags":{"type":"array","maxItems":12,"items":{"type":"string"}}}}',search_metadata);

SELECT table_name,index_name,seq_in_index,column_name,expression
FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='post_search_document'
ORDER BY index_name,seq_in_index;

MySQL 포함 여부는 게시글 한 건만 반환합니다.

투영 불일치와 스키마 위반은 0행입니다.

배열 길이별 건수를 확인하고 통계 정보에서 스칼라·표현식 인덱스 정의를 확인합니다. 위 재검증 스키마는 DDL의 enum·추가 키 금지·고유 원소 등 전체 조건을 반복하지 않는 부분 점검입니다.

운영 데이터 분포가 바뀌면 ANALYZE TABLE 실행 후 계획을 재측정합니다.

공개 범위 선택도가 낮다면 복합 인덱스가 항상 유리하지 않을 수 있습니다.

함수 기반 인덱스와 생성 열 선택

쿼리가 한두 곳뿐이면 직접 표현식 인덱스도 가능합니다.

생성 열은 이름, 타입, 통계와 여러 쿼리 재사용이 필요할 때 읽기 쉽습니다.

투영을 SELECT 출력으로도 자주 쓰므로 여기서는 생성 열을 선택했습니다.

태그 복합 조건

두 태그를 모두 포함하는 쿼리도 실제 조건식과 값 분포로 계획을 확인합니다. MVI 두 번 조회와 교집합 같은 특정 실행 방식이 항상 선택되는 것은 아닙니다.

빈번한 AND/OR 분석과 태그 메타데이터, 이름 변경이 필요하면 정규화된 연결이 더 예측 가능합니다.

NoSQL로 옮기면 사라지지 않는 일

문서 데이터베이스도 인덱스 경로, 배열 행 증가, 스키마 버전과 마이그레이션을 관리해야 합니다.

JSON을 쓴다는 사실만으로 운영 책임이 없어지지 않습니다.

최종 선택은 다음 문서에서 워크로드와 트랜잭션 범위로 판단합니다.

JSON 인덱스 도입 기준

경로가 안정되고 필터 빈도가 높으면 생성 열과 MVI 도입을 검토합니다.

임시 분석 한 번을 위해 인덱스를 영구 추가하지 않습니다.

선택 기록에는 EXPLAIN 전후와 제거 조건을 남깁니다.

연습 문제

카테고리 스칼라를 메타데이터에 추가하고 visibility_code 다음에 category_code를 두는 쿼리가 필요한지 판단하세요.

실제 쿼리가 공개 범위와 카테고리의 동등 조건을 함께 사용한다면 생성 열과 복합 인덱스를 구현합니다.

ALTER TABLE post_search_document
 DROP CHECK chk_post_search_schema,
 ADD CONSTRAINT chk_post_search_schema CHECK(JSON_SCHEMA_VALID(
  '{"type":"object","required":["visibility","publishedOn","tags"],"additionalProperties":false,
    "properties":{"visibility":{"type":"string","enum":["PUBLIC","MEMBERS_ONLY","PRIVATE"]},
    "publishedOn":{"type":"string","format":"date"},
    "tags":{"type":"array","minItems":1,"maxItems":12,"uniqueItems":true,
      "items":{"type":"string","minLength":1,"maxLength":40}},
    "category":{"type":"string","minLength":1,"maxLength":40}}}',search_metadata));

ALTER TABLE post_search_document
 ADD COLUMN category_code VARCHAR(40) GENERATED ALWAYS AS(
  JSON_UNQUOTE(JSON_EXTRACT(search_metadata,'$.category'))
 ) STORED,
 ADD INDEX ix_post_visibility_category_publish(
  visibility_code,category_code,published_on,post_id);

UPDATE post_search_document
SET search_metadata=JSON_SET(search_metadata,'$.category','database');

ALTER TABLE post_search_document
 DROP CHECK chk_post_search_schema,
 ADD CONSTRAINT chk_post_search_schema CHECK(JSON_SCHEMA_VALID(
  '{"type":"object","required":["visibility","publishedOn","tags","category"],"additionalProperties":false,
    "properties":{"visibility":{"type":"string","enum":["PUBLIC","MEMBERS_ONLY","PRIVATE"]},
    "publishedOn":{"type":"string","format":"date"},
    "tags":{"type":"array","minItems":1,"maxItems":12,"uniqueItems":true,
      "items":{"type":"string","minLength":1,"maxLength":40}},
    "category":{"type":"string","minLength":1,"maxLength":40}}}',search_metadata));

EXPLAIN ANALYZE
SELECT post_id FROM post_search_document
WHERE visibility_code='PUBLIC' AND category_code='database'
 AND published_on>='2026-07-01' AND published_on<'2026-08-01';

핵심 정리

• JSON 경로 조건식은 별도 물리 구조가 없으면 문서별 평가가 필요합니다.
• 스칼라 경로는 타입 명시 생성 열과 B+Tree로 검색 규칙을 드러냅니다.
• 배열 포함 여부는 MVI와 정확한 MEMBER OF를 사용해 부분 문자열 오탐을 없앱니다.
• 경로 인덱스 수와 행 증가는 JSON 갱신 비용을 늘리므로 워크로드 예산으로 제한합니다.

마지막 문서에서는 회원 게시판의 저장 선택을 정리하고 AI가 제안한 콘텐츠를 자동 게시하지 않는 검수 단계를 완성합니다.