타이어 쇼핑몰을 맡았다. 일반 쇼핑몰과 다른 것이 하나 있었는데 상품 속성이 많았다.
너비
편평비
휠 크기
하중 지수
속도 기호
제조사
패턴
계절여덟 개이고 전부 검색 조건이다. 차에 맞는 것을 찾아야 하니 205/55R16 처럼 앞의 셋은 필수였다.
Table of contents
Open Table of contents
배경 — 컬럼으로 넣고 시작했다
처음엔 상품 테이블에 컬럼으로 넣었다.
width, aspect_ratio, rim, load_index, speed_rating, ...검색은 단순했다.
WHERE width = 205 AND aspect_ratio = 55 AND rim = 16인덱스도 걸기 쉬웠다. 속성이 정해져 있는 동안은 이 방식이 가장 단순했다.
문제는 휠도 팔게 되면서 시작됐다. 휠은 크기와 볼트 수와 오프셋과 색상이라 타이어 컬럼이 안 맞는다.
tire_width, tire_ratio, ...
wheel_bolt, wheel_offset, ...일단 컬럼을 더 넣었다. 타이어 행은 휠 컬럼이 비고 휠 행은 타이어 컬럼이 빈다. 테이블 절반이 NULL 이 됐다. 종류가 하나 더 늘면 또 컬럼을 넣어야 한다.
컬럼을 계속 늘리는 데는 물리적인 끝이 있다. MySQL 의 하드 리밋은 표당 4096 컬럼이고 InnoDB 는 1017 컬럼이다. 그런데 실제로 먼저 걸리는 것은 그 수가 아니라 행 크기다. 표 하나의 행은 65,535 바이트를 넘을 수 없다. InnoDB 는 여기에 더해 페이지의 절반까지만 행 안에 담는다.
VARCHAR 를 몇십 개 넣다 보면 ERROR 1118 (42000): Row size too large 가 뜬다. 오류 문구가 일러 주는 회피책은 TEXT 나 BLOB 으로 바꾸는 것이다. 그것들은 내용을 행 밖에 두고 행에는 9~12 바이트만 쓴다. 다만 검색 조건으로 쓸 컬럼을 TEXT 로 바꿀 수는 없어서 회피책이 못 됐다.
속성을 행으로 뺐다
구조를 바꿨다.
product_attribute
product_id
attr_key
attr_value속성을 행으로 빼니 새 상품 종류가 와도 테이블을 안 고치고 행만 추가하면 됐다. NULL 컬럼도 없어졌다.
대신 검색이 복잡해졌다.
SELECT p.* FROM products p
JOIN product_attribute a1 ON a1.product_id = p.id
AND a1.attr_key = 'width' AND a1.attr_value = '205'
JOIN product_attribute a2 ON a2.product_id = p.id
AND a2.attr_key = 'ratio' AND a2.attr_value = '55'
JOIN product_attribute a3 ON ...조건마다 조인이 하나씩 는다. 세 조건이면 조인 셋이고 여덟 개를 다 걸면 조인 여덟이라 느렸다.
조인을 늘리는 대신 다른 방법을 썼다. 조건에 맞는 행을 전부 모은다. 그다음 GROUP BY product_id HAVING COUNT(*) = 3 으로 세 개가 다 걸린 상품만 남긴다. 조건이 몇 개든 쿼리 모양이 그대로라 HAVING 의 숫자만 바뀐다.
값이 전부 문자열이었다
행으로 담으면서 값을 한 컬럼에 넣었다.
attr_value VARCHAR숫자도 문자열로 들어가서 범위 검색이 이상해졌다.
WHERE attr_value > '100' -- 문자열 비교'99' 가 '100' 보다 크다고 나온다. 타이어는 휠 크기 16 이상 18 이하처럼 범위로 찾는 경우가 있어서 문제가 됐다.
CAST(attr_value AS SIGNED) 로 감싸는 것도 해 봤는데 그러면 인덱스를 못 탄다. 컬럼에 함수를 씌운 자리는 인덱스가 안 걸린다.
attr_value_text
attr_value_num컬럼을 나누고 숫자는 숫자 컬럼에 넣었다. 그러면 속성마다 타입을 알아야 해서 정의 테이블을 하나 더 만들었다.
attribute_def
attr_key
attr_name
attr_type text / number
is_searchable속성 자체를 자료로 둔 것이다. 부수 효과가 있었다. 이 정의로 검색 화면을 그릴 수 있었다. 숫자면 범위 입력이고 텍스트면 목록 선택이다. 속성이 늘면 화면도 따라 는다.
섞어 쓴 두 방식
검색이 느린 문제는 남아서 자주 쓰는 것만 컬럼으로 되돌렸다. 어느 것이 자주 쓰이는지는 검색 조건을 로그로 남겨 세어 봤다.
너비·편평비·휠 크기 대부분
나머지 가끔앞의 셋만 상품 테이블 컬럼으로 두고 나머지는 속성 테이블에 뒀다. 자주 검색하는 것은 빠르고 가끔 쓰는 것은 유연한 형태가 됐다.
이러면 같은 값이 두 곳에 있어 어긋날 수 있다. 저장은 속성 테이블 한 곳에서 하고 컬럼은 거기서 복사하게 했는데 쓰는 곳이 하나면 어긋날 자리가 준다.
그래도 완전하지는 않아서 직접 컬럼을 고치면 어긋난다. 주기적으로 대조하는 것을 넣었다.
SELECT p.id FROM products p JOIN product_attribute a ON ...
WHERE p.width <> a.attr_value_num;0이 아니면 어긋난 것이다.
정리
- 종류마다 속성이 다르면 컬럼으로 감당이 안 된다
- 컬럼을 늘리면
NULL이 절반이 되고 종류마다 또 는다 - InnoDB 는 표당 1017 컬럼, 행은 페이지 절반까지다
VARCHAR를 늘리다 보면ERROR 1118이 뜬다TEXT로 바꾸면 행 밖에 담기지만 검색 컬럼에는 못 쓴다- 속성을 행으로 빼면 종류가 늘어도 구조를 안 고친다
- 대신 조건마다 조인이 늘어 느려진다
GROUP BY와HAVING COUNT(*)로 조인 수를 고정한다- 값이 전부 문자열이면
'100' < '99'가 된다 CAST로 감싸면 인덱스를 못 탄다. 타입별 컬럼을 둔다- 속성 정의를 자료로 두면 검색 화면도 거기서 나온다
- 자주 쓰는 것만 컬럼으로 되돌려 속도를 얻고 주기적으로 대조한다