스키마 설계와 관리

서론

 

논리적, 물리적으로 우수한 설계는 고성능 쿼리의 기반이 된다. 스키마 설계는 중요하지만, 때때로 트레이드 오프를 피하기 어렵기도 하다. 가령, 비정규화된 스키마는 일부 쿼리의 속도를 높일 수 있지만, 다른 쿼리의 성능에 영향을 줄 수 있다. 카운터 및 요약 테이블을 추가하는 것이 쿼리 최적화에 도움이 될 수는 있지만, 유지 보수에 비용이 많이 들 수 있다.

 

따라서, 이번 장에서는 MySQL 에서 스키마 설계 시 어떤 사항들을 유의해야 하는지 살펴보고자 한다.

 

최적의 데이터 유형 선택

 

MySQL은 다양한 데이터 유형을 지원하고, 성능 향상과 리소스의 낭비를 줄이기 위해서는 적절한 데이터 유형을 선택해야 한다. 다만, 아래의 간단한 지침들은 데이터 유형과는 무관하게 더 나은 설계를 하는 데에 도움을 준다.

1. 작을 수록 좋다.

데이터를 올바르게 나타낼 수 있는 가장 작은 데이터 유형을 선택하는 것이 좋다. 데이터 유형이 작을수록 디스크, 메모리, CPU 캐시의 공간을 덜 사용하기에 조금 더 빠르고 일반적으로 처리하는데 더 적은 CPU 주기를 필요로 한다.  

2. 단순할수록 좋다.

더 단순한 데이터 유형에 대한 작업을 처리하는데 더 적은 CPU 주기가 필요하다. 예를 들어, 날짜 컬럼에 문자열 타입을 사용하게 된다면, 해당 컬럼으로 정렬을 하는 것이 날짜 타입의 컬럼을 정렬하는 것보다 많은 비용이 소모된다.

3. 가급적 NULL을 피한다.

NULL을 저장할 의도가 아니라면, NOT NULL (N-N) 지정을 하는 것이 좋다. NULL 이 허용되는 열은 그렇지 않은 열에 비해 더 많은 스토리지 공간을 사용하며, MySQL 내부에서 특별한 처리가 필요하다. 

 

위 지침들이 지켜졌다면, 스키마 설계를 위해 데이터 유형을 선택할 차례이다. MySQL의 많은 데이터 유형은 동일한 종류의 데이터를 저장할 수 있지만 저장할 수 있는 값의 범위나 허용되는 정밀도, 필요한 물리적 공간 등이 다르다. 여기서부터는 기본 데이터 유형들에 대해 다뤄본다.

 

정수

 

MySQL 이 정수를 저장하는 경우, TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT 중 하나가 사용된다. 여기에는 각각 8 / 16 / 24 / 32 / 64 비트의 저장 공간이 필요하며, -2^(N-1) ~ 2^(N-1)-1 까지의 값을 저장할 수 있다. 

정수 유형은 선택적으로 UNSIGNED 속성을 가질 수 있다. 이 속성은 음수를 허용하지 않고 양수의 상한선을 약 두 배로 만든다. 가령, UNSIGNED TINYINT 는 -128 ~ 127 대신 0 ~ 255 사이의 값을 가질 수 있다. SIGNED 와 UNSIGNED는 동일한 스토리지 공간을 사용하고 동일한 성능을 가지므로 서비스에 맞는 가장 알맞은 유형을 선택하면 된다.

추가로, MySQL 에서 INT(11) 과 같이 정수 유형의 너비를 지정할 수도 있는데, 이는 단순히 MySQL 클라이언트가 표시 목적으로 사용하는 것으로 저장 공간과는 무관하다.

 

실수

 

실수는 소수 부분이 있는 숫자이긴 하나, BIGINT 에도 들어갈 수 없는 거대한 수를 저장할 때도 이용될 수 있다. 

FLOAT과 DOUBLE 유형은 표준 부동 소수점 연산으로 근사 계산을 지원한다. 부동 소수점 결과가 어떻게 계산되는지는 플랫폼의 부동 소수점 구현에 달려있다. 

부동소수점 유형은 일반적으로 동일한 범위의 값을 저장하는 데 DECIMAL 보다 적은 공간을 사용한다. FLOAT 은 4바이트 스토리지를 사용하며, DOUBLE은 8바이트를 사용하여 FLOAT보다 정밀도가 높고 값 범위가 더 크다. 참고로 MySQL은 부동소수점 유형에 대한 내부 계산에 DOUBLE을 사용한다.

추가 공간 요구 사항이나 재무 데이터를 저장할 때와 같이 정확한 결과가 필요할 경우 DECIMAL을 사용해야 한다. 다만, 일부 대용량 사례에서는 BIGINT를 대신 사용하고, 최소 통화량의 배수로 데이터를 저장하기도 한다.

 

문자열

 

- VARCHAR

 

VARCHAR은 가변 길이 문자열을 저장하며, 가장 일반적인 문자열 데이터 유형이다. 필요한만큼 공간을 사용하기 때문에 고정 길이 유형보다 저장 공간이 덜 필요할 수 있다. 그러나 행의 길이가 가변적이기에 업데이트를 할 때 행이 커질 수 있고, 이에 따라 MySQL 내부적으로 추가 작업이 발생할 수 있다. 행이 커지고 더 이상 원래 위치에 맞지 않게 되면 InnoDB는 행을 맞추기 위해 페이지 분할을 해야할 수 있고, 어떤 스토리지 엔진은 데이터를 업데이트하지 못할 수도 있다. 따라서 VARCHAR은 최대 열 길이가 평균 길이보다 훨씬 길거나, 필드에 대한 업데이트가 드물어서 조각화가 문제되지 않을 때, UTF-8과 같은 복잡한 문자셋을 사용할 때, 사용하는 것이 좋다. 

 

- CHAR

 

CHAR은 고정 길이이다. CHAR은 데이터를 저장할 때, 후행 공백을 제거한다. CHAR은 매우 짧은 문자열만을 저장하려는 경우나 모든 값의 길이가 거의 동일한 경우에 유용한다.

가령,  'Y' 나 'N' 값만 보유하도록 설계된 CHAR(1)은 단일 바이트 문자 집합에서 1바이트만 사용하지만 VARCHAR(1)은 길이 정보에 대한 바이트 때문에 2바이트를 사용한다. 또한, 사용자 암호의 MD5 값은 자주 바뀌어 조각화가 싶지만 모든 문자열의 길이가 동일하므로 CHAR이 적합하다. 

 

- BLOB & TEXT

 

BLOB 과 TEXT는 대용량 데이터를 각각 바이너리, 문자열로 저장하도록 설계된 문자열 데이터 유형이다. MySQL은 각 BLOB 및 TEXT 값을 고유한 ID를 지닌 개체로 처리한다. InnoDB 는 크기가 클 때 별도의 외부 스토리지 영역을 사용할 수 있고, 각 값에는 1~4 바이트의 저장 공간이 필요하다. 이들의 독특한 특징 중 하나는 정렬이 데이터 일부를 중심으로 이루어진다는 점이다. 문자열 전체를 기준으로 정렬하지 않고, 서버 내 변수 max_sort_length 를 기준으로 처음 몇 글자만으로 정렬을 한다. 

 

하지만, 이 데이터 유형은 근래 잘 사용되지 않는데, 이것들을 사용할 경우 데이터가 너무 커져서 스키마 변경과 같은 작업 성능이 급격히 떨어진다. 따라서 S3 와 같은 별도의 스토리지에 이미지나 파일 데이터를 저장하고, 테이블에는 그 데이터로의 접근 경로를 저장하는 것을 권장한다.

 

- ENUM

 

때로는 문자열 대신 ENUM을 사용할 수 있다. MySQL은 ENUM 내 값의 수에 따라 내부적으로 1 또는 2 바이트로 압축하여 매우 작게 저장한다. 놀라운 점은 정렬 역시도 ENUM 자체 문자열 값이 아닌 내부 정수 값으로 이루어진다는 사실이다. 따라서 ENUM 열의 정렬은 예상과는 다른 결과값으로 도출될 수 있다. ( FIELD() 함수를 사용할 수는 있으나 이 경우 정렬에 인덱스를 사용할 수 없다 )

추가로, ENUM은 값을 저장하는데에 있어서 효율적이지만 문자열 조인에 있어서는 성능이 떨어진다. 따라서 문자열 조인이 필요할 경우 정수 기본 키와 함께 '룩업 테이블'을 만들어서 사용하는 것이 일반적인 설계 관행이다. 또한, ENUM 이 자주 변경되는 경우 스키마 변경이 잦게 일어나게 되고 마이그레이션이 매번 필요하게 된다. 이는 자동화가 되어있지 않은 서비스의 경우, 운영 상의 불편함을 초래하기도 한다.

 

날짜

 

- DATETIME

 

이 유형은 1 마이크로초의 정밀도로 1000년 ~ 9999년까지 광범위한 값을 저장할 수 있다. 시간대에 관계 없이 YYMMDDHHMMSS 형식의 정수로 압축된 날짜와 시간을 저장하며, 이것은 8바이트의 저장공간을 사용한다. 

 

- TIMESTAMP

 

이 유형은 1970년 1월 1일 자정, 그리니치 표준시(GMT) 이후 경과된 시간(초)을 저장한다. 이는 Unix 타임 스탬프와 동일하다. TIMESTAMP 는 4바이트의 저장 공간만 사용하므로 1970년 1월 1일 ~ 2038년 1월 19일 까지 DATETIME에 비해서는 훨씬 작은 범위만 저장이 가능하다.
TIMESTAMP에는 DATETIME에 없는 특수 속성도 있다. MySQL 은 열에 값을 지정하지 않고 행을 삽입하며 첫 번째 TIMESTAMP 열이 현재 시간으로 설정되고, 업데이트문에 명시적으로 값을 할당하지 않으면 행 업데이트 시 첫 번째 TIMESTAMP 열의 값이 기본적으로 업데이트된다. 또한, TIMESTAMP 열은 기본적으로 N-N 이다.

 

JSON 데이터

 

시스템 간에 데이터를 교환하기 위한 형식으로 JSON을 사용하는 것이 보편화되면서, MySQL 에도 JSON 구조를 쉽게 조작하기 위해 JSON 데이터 유형이 존재한다. 순수주의자들은 JSON의 키 값들을 컬럼으로 갖는 별도의 테이블을 구성할 것을 외치지만 실제로 SQL로 구성했을 때와 JSON으로 구성했을 때의 조회 성능 차이는 그리 크지는 않다. ( 아주 미세하게 SQL 의 성능이 조금 더 우위이기는 하다. 저자의 실험에 따르면 0.07ms 정도의 차이이다 ) 물론 조회가 매우 잦아지면 유의미한 성능 차이로 이어질 수는 있을 것이다.

따라서 네이티브 SQL 과 JSON 사이의 사용 판단은 데이터 베이스에 JSON을 저장하는 용이성이 성능보다 우월한지 여부에 달려있다. 

 

 

식별자 선택

 

일반적으로 식별자는 행을 참조하는 방법이며 행을 고유하게 만드는 역할을 하는 경우가 많다. PK 나 FK, UNIQ 컬럼의 지정이 대표적인 식별자 지정의 예라고 볼 수 있다.

 

정수 타입

 

정수는 빠르고, AUTO_INCREMENT 와 함께 작동하므로 일반적으로 식별자에 가장 적합하다. 대신 예상되는 데이터 증가에 적합한 크기를 가지고 있어야 한다. 

 

ENUM 과 SET 타입

 

이들은 상태 또는 유형을 포함하는 정적인 "정의 테이블"에서는 적합할 수 있지만 일반적인 테이블의 식별자로는 부적합하다. 특히나 ENUM 은 스토리지 상에서 정수로 저장되어있지만 문자열 컨텍스트에서 비교할 때는 문자열로 변환되는 비용이 들기도 한다.

 

문자열 타입

 

식별자에 대한 문자열 유형은 공간을 많이 차지하고 일반적으로 정수 유형보다 느리므로 가급적으로 사용하지 않는 것이 좋다. 또한, MD5(), SHA1() 또는 UUID()에 의해 생성된 문자열과 같이 완전히 임의의 문자열에 대해서도 매우 주의해야한다. 생성된 각각의 새로운 값은 큰 공간에 임의의 방식으로 배포되기에 INSERT 혹은 일부 SELECT 쿼리가 느려질 수 있다.

랜덤 값은 캐싱이 작동하는 방식인 참조 인접성을 무효화해서 모든 유형의 쿼리에 대해 캐시 능력이 저하된다. 또한, 논리적으로 인접한 행이 디스크와 메모리에 광범위하게 분산되기 때문에 SELECT 쿼리가 느려진다. 나아가 삽입된 값이 인덱스의 임의의 위치에 있어야 하기에 INSERT 성능도 떨어질 수 밖에 없다.

 

MySQL의 스키마 설계 문제

 

이 섹션에서는 MySQL을 사용한 스키마 설계에서 발견된 일반적인 문제들에 대해 다룬다. 

 

너무 많은 열

 

MySQL의 스토리지 엔진 API는 서버와 스토리지 엔진 사이의 행을 행 버퍼 형식으로 복사하여 작동한다. 그 다음 서버는 버퍼를 열로 디코딩한다. 행 버퍼를 디코딩된 열이 있는 행 데이터 구조로 변환하려면 비용이 드는데, 열이 많을수록 비용 소모가 매우 커진다. 실제로 수백 개의 열을 사용하는 테이블에서 아주 일부만의 열에 접근하려고 할 때에도 CPU의 사용률이 매우 높아진다. 

 

너무 많은 조인

 

소위 EAV ( Entity-attribute-Value ) 디자인 패턴은 MySQL 에서 잘 작동하지 않는 디자인 패턴이다. MySQL 은 조인이 최대 61개로 제한되어 있는데에 반해, EAV는 데이터베이스에 많은 셀프 조인을 요구한다. 저자의 경험 상 조인은 테이블을 12개 이하로 사용하는 것이 성능에 문제가 없다.

 

 

위장한 ENUM

 

SET 을 사용하면 열이 정의된 값 집합에서 하나 이상의 값을 보유할 수 있다. 아래 예시에서는 값이 참과 거짓도 아닌 무언가가 될 수 없기에, SET 이 아닌 ENUM 을 사용해야 한다. ( 물론 TINYINT 가 더 좋긴하지만 예시 정도로 봐두자 )

 

CREATE TABLE ... ( is_default set('Y', 'N') NOT NULL default 'N' ..);

 

 

NULL의 지나친 배제

 

"데이터가 없음"을 표시할 때, 앞선 지침을 따르고자 빈 문자열이나 마법의 상수 -1 따위를 넣는 행위보다는 NULL을 사용하는 것이 낫다.  NULL이 아닌 값이 들어가있으면 코드가 복잡해지고 버그가 잦아질 수 있다. 

 

스키마 관리

 

 

스키마 변경 실행은 데이터베이스 엔지니어가 수행해야 하는 가장 일반적인 작업 중 하나이다. 비즈니스가 지속적으로 변화하는 상황에서 수십, 수백 개의 데이터베이스 인스턴스를 실행하는 단계에 이르면 스키마 변경 사항을 적용할 때 전체 조직에 병목이 되지 않고 운영의 중단 없이 안전하게 수행될 수 있게 주의해야 한다. 

 

운영 중의 스키마 변경 실행

 

MySQL 은 버전 5.6에 논블로킹 스키마 변경을 도입했지만, 주요 버전에서 이 기능을 사용하는 것은 한정적이었다. 8.0이 GA 버전이 되면서 MySQL의 기본 DDL에 대한 지원이 크게 확장되었지만, 여전히 일반적이지는 않다. 기본 키 변경, 문자셋 변경, 테이블별 암호화 설정, 외래 키 추가 또는 제거는 모두 기본적으로 INPLACE 변경으로 수행할 수 없는 스키마 변경의 예이다.

8.0 이상에서 기본 DDL에 의해 기술적인 지원이 되더라도 테이블의 크기가 매우 크다면 InnoDB 가 내부적으로 유지하고 있는 테이블 변경 사항의 로그 파일이 너무 커져서 롤백이 발생해서 몇 시간 도는 며칠 간의 작업을 취소할 수 있다. 이를 방지하기 위해 외부 도구를 사용하는 것이 제안된다.

 

외부 도구를 사용한 스키마 변경 실행

 

CI 툴과 오픈 소스 도구와 결합하여 서비스에 영향을 주지 않고 운영 중에 스키마 변경을 자동으로 실행할 수 있다. 이를 위한 두 가지 중요한 옵션은 Percona의 pt-online-schema-change와 Github의 gh-ost 이다. 한 가지 주의할 점은, 스키마 변경을 실행하는 외부 도구는 변경하려는 테이블의 전체 사본을 필요로 한다. 이 도구는 프로세스의 영향을 덜 받으며 쓰기 잠금을 중단하지 않아도 되지만, MySQL의 기본 DDL만이 전체 테이블 복사 없이 테이블 스키마를 변경할 수 있다.

 

pt-online-schema-change는 주로 트리거를 활용하여 새 테이블 버전으로 전환할 때 데이터베이스 가용성에 거의 영향을 미치지 않으면서 모든 크기의 테이블에 대한 스키마를 변경할 수 있다. gh-ost는 서비스 영향 없이 트리거를 전혀 사용하지 않고 스키마 변경 프로세스를 관리하기 위한 솔루션으로 GitHub의 데이터 엔지니어링 팀에서 특별히 만들었다.

 

스키마 변경에서 주의 깊게 고려해야 할 한 가지는 기존 데이터베이스가 외래키를 사용하는지 여부이다. pt-online-schema-change는 외래 키 관계에서 부모 또는 식인 테이블에 대한 스키마 변경을 지원하기 위한 시도를 하지만 복잡한 선택이고 트래이드 오프 ( 일관성 vs 가동 시간 ) 로 가득차 있다.  따라서 외래 키가 존재하는 오래된 데이터베이스가 여러 개이고 그것들을 제거하기 어려운 상황에서 유리하다. 반면, gh-ost는 외래키가 존재하는 경우 전적으로 회피한다. gh-ost의 메인 contributor인 슐로미 노치 ( Shlomi Noach )는 온라인 스키마 변경이 필요한 경우, 외래 키를 전혀 사용하지 않을 것을 제안한다.