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은 스칼라 생성 열로, 태그는 문자열 배열로 둡니다. 문서의 CHECK는 필수 키와 원소의 타입을 보장하고, 대상이 되는 작업은 공개 범위의 목록, 게시 날짜의 범위, 태그 하나의 MEMBER OF입니다. 복합 정렬이나 태그 사이의 관계가 필요해지면 JSON 인덱스를 계속 늘리지 말고 정규 연결 테이블을 검토합니다.

JSON 경로 검색의 한계

WHERE JSON_UNQUOTE(metadata->'$.visibility') = 'PUBLIC'은 그 식에 맞는 인덱스가 없으면 모든 행을 평가합니다. CAST(metadata 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%'
ORDER BY post_id;

post_id
1
2
3

LIKE는 세 행을 모두 돌려주지만, 실제로 mysql 태그를 가진 문서는 1뿐입니다. 2는 mysql2라는 다른 태그를, 3은 태그가 아닌 설명문을 가졌으므로 두 행은 오탐입니다. 인덱스가 없는 공개 범위의 조건은 각 행의 JSON 값을 평가해야 하며, 실제 접근 방식과 추정 행 수는 EXPLAIN의 결과에서 확인합니다.

JSON 함수 자체가 나쁜 것이 아니라, 인덱스의 식과 쿼리의 식이 일치하지 않는 것이 문제입니다. 읽는 쪽마다 JSON_UNQUOTE와 CAST의 길이, 정렬 규칙이 다르면 같은 경로를 여러 방식으로 해석하므로, 생성 열에 이름과 SQL 타입을 주어 그 해석을 한곳에 고정합니다. visibility_code VARCHAR(20), published_on DATE처럼 범위를 선언하면, 문서가 바뀔 때 함께 계산됩니다.

태그를 쉼표로 이어 붙인 문자열로 인덱스하면 이스케이프와 순서, 부분 문자열의 문제가 생깁니다. MVI는 배열의 스칼라 하나하나를 인덱스 키로 다루며, 원소가 객체이거나 중첩된 배열이라면 지원 방식과 쿼리의 요구를 다시 검토합니다.

검색 경로의 스키마 승격

visibility_code는 JSON 안의 값을 복사한 중복이 아니라, 생성된 투영입니다. 쓰기 주체는 메타데이터만 바꾸고 데이터베이스가 열을 계산하며, 쿼리는 JSON 경로를 반복하지 않고 visibility_code를 씁니다.

다중 값 인덱스는 문서 하나에서 인덱스 항목을 여러 개 만들므로, 태그가 많을수록 인덱스의 크기와 갱신의 비용이 커집니다(연습 문제 2). 그래서 스키마에 maxItems를 두고 실제 항목의 증가를 지켜봅니다.

배열 포함의 조건에는 MEMBER OF나 JSON_CONTAINS, JSON_OVERLAPS 등을 씁니다. 인덱스를 쓸 수 있는지는 경로와 원소의 타입, 길이, 비교 규칙이 인덱스의 정의와 맞아야 판단할 수 있으며, 옵티마이저와 버전에 따라 지원하는 조건이 다르므로 DDL을 만든 것으로 끝내지 않고 대표 매개변수로 EXPLAIN ANALYZE를 저장합니다. 다중 값 인덱스는 CAST(JSON 배열 AS CHAR(N) ARRAY) 형태의 식을 쓰므로, 원소의 타입과 길이가 스키마의 검증과 정확히 맞아야 합니다.

스칼라와 배열 인덱스

DDL은 visibility_code와 published_on을 STORED 생성 열로 정의하고, 태그 배열은 JSON 스키마로 최대 12개의 문자열로 제한한 뒤 MVI의 식을 더합니다. 아래 DDL에서는 세 곳을 보면 됩니다. GENERATED ALWAYS AS (...) STORED로 정의한 두 열, JSON_SCHEMA_VALID로 문서의 모양을 검사하는 CHECK, 마지막 줄의 CAST(... AS CHAR(40) ARRAY)를 쓴 다중 값 인덱스입니다. 끝의 두 SELECT는 생성 열로 공개 범위와 날짜를 거르는 조회와, MEMBER OF로 태그 배열에 mysql이 원소로 들어 있는지 보는 조회입니다.

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

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

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

post_id
1

post_id
1

7월의 PUBLIC 게시글은 12장 4편의 시드 게시글 1 하나이고, 정확히 mysql 태그를 가진 문서도 1 하나입니다. 둘째 문서의 mysql2는 원소의 값이 다르므로 MEMBER OF에 걸리지 않아, LIKE에서 보았던 오탐이 사라졌습니다. @other_post_id는 18장 1편에서 만든 두 번째 게시글이며, 두 조회가 각각 복합 B+Tree와 다중 값 인덱스를 고르는지는 같은 SELECT 앞에 EXPLAIN ANALYZE를 붙여 확인합니다.

visibility 경로가 빠진 문서는 필수 키의 CHECK를 지키지 못하며, 이 설명은 생성 열과 CHECK의 평가 순서에 기대지 않습니다. 또 MySQL 8.4의 이 JSON 스키마에서 format: date를 달력의 검사로 보면 안 되며, DATE로의 변환과 0이나 불완전한 날짜의 허용은 SQL 모드에도 영향을 받으므로, 실제 날짜는 수신 단계에서 검증한 뒤 넘깁니다.

태그의 이름을 바꾸려면 문서마다 JSON_SET으로 고쳐 써야 하므로 대량의 재작성이 필요합니다. 태그가 관리되는 엔티티가 되면 tags와 post_tags의 연결로 옮기고, MVI는 작고 독립된 표시 이름의 집합에 씁니다.

메타데이터를 갱신하면 JSON 행과 스칼라 인덱스, 태그 원소의 항목이 함께 갱신됩니다. 그래서 인덱스를 더할 때는 읽기의 지연 시간뿐 아니라 삽입과 갱신의 p95, 인덱스의 바이트, 리두 로그의 양을 변경 전후로 비교합니다.

더 깊이: 다중 값 인덱스가 따르는 규칙

옵티마이저가 다중 값 인덱스로 행을 찾는 것은 WHERE에 MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS()가 있을 때입니다. 한 인덱스에는 다중 값 키 부분을 하나만 둘 수 있고, 정렬을 지원하지 않으므로 기본 키가 될 수 없으며 ASC나 DESC도 붙일 수 없습니다. 커버링 인덱스가 될 수도 없으므로 이 인덱스로 찾은 행은 원래 행을 다시 읽어야 하고, 외래 키와 접두 인덱스에도 쓸 수 없습니다.

비교의 규칙도 정해져 있습니다. MySQL 8.4 매뉴얼에 따르면 다중 값 인덱스가 지원하는 문자 집합과 정렬 규칙은 binary, 그리고 utf8mb4와 utf8mb4_0900_as_cs의 조합 두 가지뿐입니다. as_cs는 대소문자와 악센트를 구별하므로, 이 인덱스에서 MySQL과 mysql은 서로 다른 태그입니다. 이 교재의 열 기본값인 utf8mb4_0900_ai_ci와 비교의 결과가 달라질 수 있으므로, 태그는 저장하기 전에 소문자처럼 한 가지 표기로 정규화해 두는 편이 안전합니다.

행 하나가 만드는 항목에도 규칙과 한도가 있습니다. 배열이 비어 있으면 인덱스에 항목이 하나도 생기지 않아 그 행은 인덱스 스캔으로 찾을 수 없고, 키 부분의 결과가 NULL이면 NULL 항목 하나가 들어갑니다. 행 하나의 키 값은 언두 로그 페이지 하나에 담을 수 있는 65221바이트를 넘을 수 없으며, 넘으면 오류 3905가 납니다. 또 다중 값 인덱스는 온라인으로 만들 수 없어 ALGORITHM=COPY로 테이블을 복사하므로, 운영 중인 큰 테이블에 더할 때는 2장 1편의 보조 인덱스와 다르게 계획해야 합니다.

정확성과 불일치, 크기의 측정

mysql과 mysql2를 함께 넣었으므로 부분 문자열의 오탐이 없는지 확인하고, 생성 열이 JSON 경로와 일치하는지와 태그 배열 길이의 분포를 봅니다. 네 쿼리는 차례로 생성 열과 JSON 경로의 값이 어긋난 행, 태그 개수별 문서 수, 스키마를 다시 적용했을 때 어긋나는 문서, information_schema.statistics에 등록된 인덱스의 열과 식을 봅니다. 첫 쿼리의 <>는 어느 한쪽이 NULL이면 결과도 NULL이 되어 행을 고르지 않으므로, 경로가 빠진 문서는 첫 쿼리가 아니라 셋째 쿼리의 스키마 검사가 찾아냅니다.

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') AS tag_count, COUNT(*) AS 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;

tag_count | posts
2         | 2

투영의 불일치와 스키마의 위반은 빈 결과이고, 두 문서 모두 태그가 두 개입니다. 위의 재검증 스키마는 DDL의 enum과 추가 키의 금지, 원소의 고유성까지 반복하지 않는 부분 점검입니다. 마지막 조회는 스칼라 인덱스의 열과, 열 이름 대신 식을 가진 다중 값 인덱스의 정의를 보여 주며, 식의 표기는 서버가 정규화한 형태라 싣지 않았습니다.

운영 데이터의 분포가 바뀌면 ANALYZE TABLE을 실행한 뒤 계획을 다시 측정합니다. 공개 범위의 선택도가 낮다면 복합 인덱스가 언제나 유리한 것도 아닙니다.

쿼리가 한두 곳뿐이라면 생성 열 없이 식에 직접 인덱스를 거는 함수 기반 인덱스도 가능합니다. 생성 열은 이름과 타입, 통계, 여러 쿼리에서의 재사용이 필요할 때 읽기 쉽고, 투영한 값을 SELECT의 출력으로도 자주 쓰므로 여기서는 생성 열을 골랐습니다.

두 태그를 모두 가진 문서를 찾는 쿼리도 실제 조건식과 값의 분포로 계획을 확인하며, MVI를 두 번 조회해 교집합을 구하는 방식이 늘 선택되지는 않습니다. AND와 OR의 분석이 잦고 태그의 메타데이터와 이름 변경이 필요하다면, 정규화한 연결 테이블이 더 예측하기 쉽습니다.

문서 데이터베이스로 옮겨도 인덱스의 경로와 배열에 따른 항목의 증가, 스키마의 버전과 마이그레이션은 여전히 관리해야 합니다. JSON을 쓴다고 이런 운영의 일이 사라지지 않으며, 최종 선택은 다음 글에서 작업의 양상과 트랜잭션의 범위로 판단합니다.

경로가 안정되고 거르는 빈도가 높다면 생성 열과 MVI를 검토하고, 한 번의 임시 분석을 위해 인덱스를 영구히 더하지는 않습니다. 결정의 기록에는 EXPLAIN의 전후와, 인덱스를 지울 조건을 남깁니다.

정리

• JSON 경로의 조건식은 별도의 물리 구조가 없으면 문서마다 평가해야 합니다.
• 문서 문자열의 LIKE는 배열 원소의 포함이 아니어서, 다른 태그나 설명문까지 오탐합니다.
• 스칼라 경로는 타입이 있는 생성 열과 B+Tree로 검색 규칙을 드러냅니다.
• 배열의 포함 여부는 MVI와 정확한 MEMBER OF로 찾습니다.
• 경로 인덱스의 수와 배열에 따른 항목의 증가는 갱신 비용을 늘리므로 작업의 예산으로 제한합니다.

연습 문제

1. 카테고리를 더한 복합 인덱스

카테고리 스칼라를 메타데이터에 더하고, 실제 쿼리가 공개 범위와 카테고리의 동등 조건을 함께 쓴다면 생성 열과 복합 인덱스를 구현해 보세요.

ALTER TABLE post_search_document DROP CHECK chk_post_search_schema;
ALTER TABLE post_search_document
  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;
ALTER TABLE post_search_document
  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));

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

post_id
1

2. 원소의 수와 결과 행의 수

스키마가 허용하는 문서 A의 태그는 ["mysql","index"], B의 태그는 ["mysql","json","ops"]입니다. 두 문서의 태그 MVI에 대응하는 원소 키는 모두 몇 개인가요? 'mysql' MEMBER OF를 만족하는 문서의 수와 같은가요?

다음 글 저장 구조와 AI 콘텐츠 검수에서는 회원 게시판의 저장 선택을 정리하고, AI가 제안한 콘텐츠를 자동으로 게시하지 않는 검수 단계를 완성합니다.