MySQL 5.7 Generated Columns 시험

MySQL 5.7 GA 된지 조금 됐음. dev 환경에 깔아서 Generated Columns 기능 좀 건드려봤다.

Generated Column = 다른 컬럼값을 기반으로 계산되는 "가상" 컬럼. STORED (디스크에 저장) vs VIRTUAL (조회시 계산) 두 가지. MariaDB에는 10.2부터 비슷한 기능이 있었는데 MySQL은 이제서야.

CREATE TABLE user (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  full_name VARCHAR(101)
    GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  email VARCHAR(100),
  email_domain VARCHAR(50)
    GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED
);

재밌는건 generated column에도 인덱스 걸린다는 점. VIRTUAL인 경우 MySQL이 secondary index 구조로 저장해서 실제 row엔 없어도 인덱싱은 됨. 그동안 JOIN/WHERE 때문에 computed 값 올리려고 트리거 쓰던거 훨씬 깔끔하게 대체 가능.

ALTER TABLE user ADD INDEX idx_email_domain (email_domain);
-- WHERE email_domain = 'gmail.com' 로 빠르게 검색

JSON 타입이랑 조합하면 더 재밌음. JSON 컬럼 자체엔 인덱스 못 거는데, generated column으로 JSON 내 필드 뽑아서 인덱스 걸 수 있음.

CREATE TABLE log (
  id INT PRIMARY KEY,
  payload JSON,
  user_id INT GENERATED ALWAYS AS (payload->'$.user_id') STORED,
  INDEX idx_user (user_id)
);

주의사항: STORED로 한 경우 insert/update 시 실제 저장되니까 disk 공간 추가. VIRTUAL은 공간 아끼지만 인덱스 쓸 때 여전히 계산 비용이 발생함. expr이 너무 복잡하면 STORED가 답.

5.7 자체는 당분간 도입 안 하려고 했는데 (InnoDB 설정 꽤 바뀌어서 조심스러움) Generated Columns랑 JSON 타입 때문에 4분기 중에는 한 프로젝트라도 올려볼 생각.