JSON·ENUM·TEXT와 생성 칼럼

문자열·숫자로 딱 떨어지지 않는 값을 담는 타입들이다. JSON은 구조가 제각각인 속성을, ENUM은 정해진 값 목록을, TEXT와 BLOB(Binary Large Object, 대용량 바이너리 객체)은 긴 글과 파일을 담는다. 모두 편하지만 인덱스·정렬·스키마 변경에서 일반 칼럼과 다르게 동작하므로, 그 차이를 알고 써야 한다. 이 문서는 JSON과 ENUM을 보고, TEXT·BLOB의 저장 방식과 값을 뽑아 인덱스를 걸게 해 주는 생성 칼럼(generated column)은 2편에서 본다.

JSON 타입

JSON 문서를 VARCHAR나 TEXT에 문자열로 넣어도 되지만, JSON 타입은 세 가지가 다르다. 첫째, 넣을 때 올바른 JSON인지 검사해 깨진 문서를 거부한다. 둘째, 문자열 그대로가 아니라 내부 바이너리 형식으로 바꿔 저장한다. 셋째, 그 형식 덕분에 문서 전체를 다시 해석하지 않고 키나 배열 위치로 안쪽 값을 바로 찾아 읽는다.

저장할 때 MySQL은 문서를 다듬는다. 객체 안에 같은 키가 두 번 나오면 마지막 값만 남기고, 찾기 쉽도록 키 순서를 다시 정렬한다. 그래서 넣은 문자열과 꺼낸 문자열이 글자 단위로 같다고 기대하면 안 된다. 문서 하나를 저장하는 공간은 가장 큰 바이너리 타입인 LONGBLOB에 같은 내용을 넣을 때와 비슷하고, 문서 하나는 max_allowed_packet¹ 크기를 넘을 수 없다.

값 일부만 바꾸는 부분 업데이트도 된다. UPDATE에서 같은 칼럼에 JSON_SET()·JSON_REPLACE()·JSON_REMOVE()를 쓰고, 새 값이 옛 값보다 크지 않으면 InnoDB가 문서 전체를 새로 쓰지 않고 그 자리에서 고친다. 새 키를 추가하거나 값이 커지면 문서 전체를 다시 쓴다. 바이너리 로그(binary log)에도 바뀐 부분만 적게 하려면 binlog_row_value_options=PARTIAL_JSON을 켠다. 그러면 큰 문서의 값 하나를 고칠 때 로그 크기와 레플리카(replica)로 보내는 데이터 양이 줄어든다.

JSON 칼럼 자체에는 인덱스를 걸 수 없다. 자주 찾는 값은 생성 칼럼으로 뽑아 인덱스를 걸고(2편), 배열 원소로 찾으려면 멀티 밸류 인덱스(multi-valued index)를 쓴다(특수 인덱스).

그러면 언제 JSON 칼럼을 고를까. 상품 종류마다 속성이 달라 칼럼으로 다 만들 수 없고, 주로 행 단위로 통째로 읽는 부가 정보라면 JSON이 맞다. 반대로 WHERE·조인·정렬에 자주 쓰이거나, NOT NULL·외래 키(foreign key) 같은 제약을 걸어야 하는 값은 일반 칼럼으로 뺀다. JSON 안의 값은 DB가 타입과 필수 여부를 지켜 주지 않기 때문이다.

ENUM과 SET

ENUM('small','medium','large')는 목록에 있는 값 하나만 받는 문자열 타입이다. 안에서는 문자열 대신 목록 순번(1부터)을 저장하므로 값 255개까지는 1바이트, 65,535개까지는 2바이트면 된다. 목록에 없는 값은 엄격 모드(strict mode)에서 오류로 막힌다.

작고 값 검사까지 해 주지만 함정이 있다.

• 정렬은 글자순이 아니라 목록 순번순이다. ENUM('b','a')에서는 'b'가 먼저 나온다.
• 숫자처럼 보이는 값(ENUM('0','1','2'))은 쓰지 않는다. 따옴표 없는 숫자 2를 넣으면 값 '2'가 아니라 두 번째 순번인 '1'로 저장된다.
• 목록 끝에 값을 추가하는 것은 저장 크기가 그대로면 바로 끝난다(ALGORITHM=INSTANT). 중간에 끼우거나 순서를 바꾸면 저장된 순번의 뜻이 달라지므로, 모든 행을 새 순번으로 다시 쓰는 테이블 복사가 필요하고 큰 테이블에서는 오래 걸린다.

그래서 값이 자주 늘거나 이름을 바꿀 일이 있는 목록은 별도 코드 테이블(허용 값을 행으로 담아 두고 외래 키로 참조하는 작은 테이블)이나 VARCHAR + CHECK 제약 조건(constraint)이 관리하기 쉽다. 값을 추가하거나 이름을 바꿀 때 테이블 구조를 바꾸지 않아도 되기 때문이다. SET은 목록에서 여러 개를 동시에 고르는 타입이다. 최대 64개 값을 담는데, 목록의 값마다 비트 하나를 배정해 고른 값의 비트를 켜는 방식이라 1~8바이트면 된다. 대신 특정 값이 든 행을 찾으려면 FIND_IN_SET() 같은 함수 조건을 써야 해서 인덱스를 쓰기 어렵다.

면접 포인트

• JSON 타입: 유효성 검사 + 바이너리 형식 + 안쪽 값 바로 읽기. 칼럼 자체 인덱스는 불가 → 생성 칼럼 인덱스·멀티 밸류 인덱스.
• 자주 조건·조인에 쓰거나 제약이 필요한 값은 JSON에 넣지 말고 칼럼으로.
• JSON 부분 업데이트: JSON_SET·JSON_REPLACE·JSON_REMOVE로 값이 커지지 않을 때 제자리 수정.
• ENUM: 순번 저장(1~2바이트), 정렬은 순번순, 끝에 추가는 INSTANT·중간 추가는 테이블 복사. 숫자 모양 값 금지.

참고

1. 서버와 클라이언트가 한 번에 주고받을 수 있는 패킷의 최대 크기를 정하는 설정. 문서 하나가 이보다 크면 보내지도 받지도 못한다. ↩︎