JSON·ENUM·TEXT와 생성 칼럼 2

긴 글과 파일을 담는 TEXT와 BLOB(Binary Large Object, 대용량 바이너리 객체)은 값이 커서 행 안에 두지 않고 따로 저장될 수 있다. 그 저장 방식이 조회 비용과 인덱스·기본값 제약을 만든다. 뒤에서는 JSON이나 다른 칼럼에서 계산한 값을 칼럼처럼 쓰고 인덱스까지 걸게 해 주는 생성 칼럼(generated column)을 본다.

TEXT와 BLOB

TEXT는 문자 집합(character set)과 콜레이션(collation)을 따르는 긴 글, BLOB은 바이트를 그대로 비교하는 긴 이진 데이터다. 둘 다 TINY·기본·MEDIUM·LONG 네 크기가 있고, TEXT는 약 64KB, LONGTEXT는 약 4GB까지 담는다.

InnoDB의 기본 행 포맷(row format)인 DYNAMIC은 한 행이 페이지(page)에 다 들어가지 않으면, 긴 TEXT·BLOB·VARCHAR 값을 통째로 행 밖의 별도 페이지에 두고 행에는 그 위치를 가리키는 20바이트 포인터만 남긴다. 이것을 오프 페이지(off-page) 저장이라 한다. 옛 COMPACT·REDUNDANT 행 포맷은 앞 768바이트를 행에 남기고 나머지만 밖에 뒀다. DYNAMIC에서도 40바이트 이하인 TEXT·BLOB 값은 항상 행 안에 둔다. 그래서 큰 값을 읽으면 페이지를 더 읽어야 하고, 필요 없는 큰 칼럼까지 끌고 오는 SELECT *가 특히 비싸다.

그 밖에 알아 둘 제약이 셋 있다.

• 인덱스를 걸려면 INDEX (body(100))처럼 앞부분 길이를 정한 접두어 인덱스(prefix index)만 된다.
• 기본값은 DEFAULT ('')처럼 괄호로 감싼 식으로만 줄 수 있고, DEFAULT ''는 오류다. 이 타입들은 8.0.13부터 허용된 식 형태의 기본값만 받기 때문이다.
• 정렬·그룹핑은 앞쪽 max_sort_length(기본 1024바이트)까지만 보고 비교한다. 그래서 앞부분이 같은 값은 정렬·그룹핑에서 같은 값으로 취급될 수 있다.

예전에는 결과에 TEXT·BLOB이 있으면 내부 임시 테이블(temporary table)이 무조건 디스크로 갔다. 데이터를 메모리에만 두는 옛 MEMORY 엔진이 이 타입을 못 담았기 때문이다. 8.4의 기본 내부 임시 테이블 엔진인 TempTable은 이 타입을 지원하므로, 이제 그것만으로 디스크로 가지는 않고, 쓰는 메모리가 temptable_max_ram 같은 한도를 넘을 때 디스크로 옮긴다.

생성 칼럼

생성 칼럼은 같은 행의 다른 칼럼으로 계산한 값을 담는 칼럼이다. 두 종류가 있다. VIRTUAL(기본값)은 값을 저장하지 않고 읽을 때 계산하므로 공간을 쓰지 않는다. STORED는 INSERT·UPDATE 때 계산해 저장하므로 공간을 쓰지만 읽을 때 계산이 없다. InnoDB는 가상 칼럼(virtual column)에도 세컨더리 인덱스(secondary index)를 걸 수 있다. 이때 인덱스에는 계산된 값이 저장된다.

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  attrs JSON,
  brand VARCHAR(50) AS (attrs->>'$.brand') VIRTUAL,  -- JSON에서 값을 뽑는 생성 칼럼
  INDEX idx_brand (brand)
);
SELECT id FROM products WHERE brand = 'acme';

attrs->>'$.brand'는 JSON 칼럼 attrs에서 brand 키의 값을 따옴표 없는 문자열로 꺼내는 식이다. 이렇게 하면 JSON 안의 값으로도 일반 인덱스처럼 찾을 수 있다. WHERE attrs->>'$.brand' = 'acme'처럼 생성 칼럼 이름 대신 같은 식을 조건에 써도, 옵티마이저(optimizer)가 알아보고 idx_brand를 쓴다. 생성 칼럼의 식에는 결정적인(같은 입력이면 늘 같은 결과인) 내장 함수·연산자와 같은 행의 칼럼만 쓸 수 있다. 서브쿼리(subquery, 쿼리 안에 넣은 또 다른 SELECT), 변수, 스토어드 함수(stored function)는 쓸 수 없고, AUTO_INCREMENT 칼럼을 재료로 삼을 수도 없다. 칼럼을 따로 만들지 않고 식에 바로 인덱스를 거는 8.0의 함수 키 파트(functional key part)도 안에서는 숨은 가상 칼럼으로 구현된다(특수 인덱스).

면접 포인트

• TEXT·BLOB: DYNAMIC에서 긴 값은 오프 페이지 + 20바이트 포인터. SELECT *로 큰 칼럼까지 끌고 오지 않는다.
• 인덱스는 접두어 인덱스만, 기본값은 괄호로 감싼 식만, 정렬은 max_sort_length까지만.
• 8.4 TempTable은 BLOB·TEXT를 지원해 그것만으로 디스크 임시 테이블이 되지 않는다.
• 생성 칼럼: VIRTUAL(기본, 저장 안 함)·STORED(저장). 가상 칼럼에도 세컨더리 인덱스 가능, JSON 값 인덱싱에 쓴다.