JSON 검색 인덱스

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

visibility access=ALL
rows examined=3
exact mysql tag rows=1
LIKE mysql rows=3
false positives=2

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

PUBLIC July rows=1
exact mysql members=1
mysql2 false positives=0
visibility plan=index range
tag plan=multi-valued index lookup

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;