MySQL 성능최적화 (3)

2018-04-14 23:45:00

3장 스키마 최적화와 인덱싱

최적의 데이터 타입 고르기

MySQL 은 꽤 다양한 데이터 타입을 지원하는데, 데이터를 저장하는 데 적합한 데이터 타입을 고르는 것은 좋은 성능을 얻는데 필수적이다. 데이터 타입 선택의 주요 원칙은 아래와 같다.

대체로 작을수록 좋다
단순한게 최고다 : 날짜와 시간은 문자열로 저장하지 말고, MySQL 의 내장 형식에 저장해야 하며 IP 주소는 정수를 이용해 저장해야 한다.
가능하면 NULL 을 쓰지 말자 : NULL 허용 컬럼으로 인해 인덱스, 인덱스 통계, 값 비교 등이 더 복잡해진다. 게다가 NULL 허용 컬럼은 저장 공간도 더 많이 사용하며, MySQL 내부에서 특별하게 처리해 줄 필요가 있다. NULL 대신 0 이나 특수한 어떤 값, 빈 문자열 등을 사용하는 것을 고려해보자. 대체로 NULL 컬럼을 NOT NULL 로 바꾼다고 성능이 크게 개선되지는 않으니 기존 스키마에서 NULL 허용 컬럼을 찾아 수정하지는 말자. 하지만 컬럼을 인덱싱할 계획이라면 최대한 NULL 을 허용하지 말자

컬럼에 어떤 데이터 타입을 쓸지 결정할 때는 숫자, 문자열, 시간 같은 개괄적인 범위의 타입부터 결정한 후에 구체적인 데이터 타입을 선택하자. MySQL의 데이터 타입은 같은 데이터 종류를 다루면서 저장할 수 있는 값의 범위와 필요한 물리적 공간의 양이 다르고, 어떤 데이터 타입은 특별한 행동 양식을 보이기도 한다.

한 예로 DATETIME 과 TIMESTAMP 컬럼은 똑같은 종류의 데이터인 날짜/시간을 초 단위로 저장할 수 있다. 그러나 TIMESTAMP 는 DATETIME 보다 저장 공간을 절반 밖에 사용하지 않고, 서버측 시간대를 사용하며, 자동 업데이트라는 특별한 기능이 있다. 반면 허용되는 값의 범위가 훨씬 작고 때로는 특수한 기능이 약점이 되기도 한다.

1. 정수 타입

정수를 저장한다면 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 중 하나를 사용하자. 이 타입들은 순서대로 8, 16, 24, 32, 64비트의 저장 공간이 필요하다. 정수 타입에는 UNSIGNED 속성을 사용할 수도 있으며, 이 속성은 음수 값을 허용하지 않는 대신 저장 가능한 양수 값의 한도를 거의 두배로 늘려준다.

MySQL 에선 INT(11)과 같이 정수 타입의 ‘길이’를 명시할 수 있다. 하지만 이 기능은 값의 유효 범위를 제한하진 않고, MySQL 클라이언트 같은 프로그램의 화면 출력용으로 예약하는 문자의 개수를 정할 뿐이므로 대부분의 응용프로그램에선 별다른 의미가 없다. 저장이나 계산 측면에선 INT(1) 과 INT(20) 은 동일하다.

2. 실수 타입

실수는 소수부가 있는 숫자다. 하지만 단지 수소부 때문에 실수가 있는 건 아니다. BIGINT 로 표현할 수 없을 만큼 큰 정수도 DECIMAL 을 이용하면 저장 가능하다. MySQL 에서는 정확한 타입과 부정확한 타입을 모두 지원한다.

DECIMAL 타입은 소수부를 정확하게 저장하는 용도로 사용한다. MySQL 5.0 이상에선 DECIMAL 타입을 통해 정확한 계산을 지원한다. (DECIMAL 하나에 최대 65개의 수를 저장할 수 있다) DECIMAL 을 사용하면 저장 공간과 연산 비용이 더 들기 때문에 금융 데이터를 저장하는 경우처럼 소수부에 대한 정확한 결과 값이 필요할 때에만 DECIMAL 을 사용하자.

3. 문자열 타입

VARCHAR 타입과 CHAR 타입

스토리지 엔진에 따라 저장하는 방식과 서버가 스토리지 엔진에서 값을 꺼낼 때 다른 스토리지 포맷으로 변환할 수도 있으니 주의하자.

VARCHAR: VARCHAR 는 가변 길이의 문자열을 저장하는 가장 흔한 문자열 데이터 타입이다. VARCHAR 는 필요한 만큼만 공간을 사용하기 때문에 고정 길이 타입보다 저장 공간을 적게 사용하기도 한다. VARCHAR 는 값의 길이를 기록하기 위해 추가로 1~2 바이트를 사용한다. 칼럼의 최대 길이가 255 바이트 이하면 1바이트를 사용하고, 최대 길이가 그보다 길면 2바이트를 사용한다.
VARCHAR 는 공간을 절약하므로 성능에 도움이 된다. 하지만 행이 가변 길이이므로 행을 업데이트할 때 행의 길이가 길어지기도 하고, 이 때문에 추가적인 작업이 필요할 수도 있다. 그리고 MySQL 버전 5.0 이상에서는 문자열 뒤에 붙은 공백을 보존한다. 하지만 4.1 버전이나 이전 버전의 경우엔 뒤에 붙는 공백을 제거한다.
CHAR: CHAR 는 고정 길이이므로 MySQL 에선 항상 지정된 문자 개수에 맞춰 충분한 공간을 할당한다. CHAR 값을 저장할 때 MySQL 에서는 뒤에 붙은 공백을 제거한다. CHAR 는 아주 짧은 문자열을 저장하고자 할 때나 모든 값이 거의 같은 길이일 때 유용하다. 예를 들어 MD5 의 경우 값의 길이가 언제나 같기 때문에 이 값을 저장할 땐 CHAR 가 적합하다. 고정 길이의 행은 단편화가 거의 발생하지 않으므로 자주 바뀌는 데이터를 저장할 때라면 CHAR 가 VARCHAR 보다 낫다. 아주 짧은 컬럼에도 CHAR 를 쓰는 것이 VARCHAR 를 쓰는 것보다 효율적이다. Y 와 N 값만 저장하는 것처럼 한 바이트 문자셋이라면 CHAR(1) 는 1바이트만 쓰지만 VARCHAR(1) 에선 길이 바이트 때문에 2바이트를 쓴다.
BINARY 와 VARBINARY: 바이너리 문자열은 전통적인 문자열과 매우 비슷하지만 문자가 아닌 바이너리를 저장하며 여백을 채우는 방법이 다르다. MySQL 은 바이너리 값에 공백이 아닌 \0 (값이 0 인 바이트)을 덧붙이고 값을 가져올 때도 여백 값을 잘라내지 않는다. 이 타입은 바이너리 데이터를 저장해야 하며, MySQL 에서 값을 문자가 아닌 바이트로써 비교하고 싶을 때 유용하게 사용할 수 있다. MySQL 에서는 문자 그대로 BINARY 문자열을 각 바이트의 숫자 값에 따라 한번에 한 바이트씩 비교하므로 문자열 비교보다 간단하고 훨씬 빠르다.

BLOB 타입과 TEXT 타입

BLOB 과 TEXT 는 각각 커다란 바이너리 문자열과 문자열을 저장하려고 고안된 데이터 타입이다. InnoDB 에선 BLOB 또는 TEXT 의 값이 큰 경우 별도의 ‘외부’ 저장 공간을 사용한다. BLOB 과 TEXT 의 유일한 차이점은 BLOB 은 문자 셋이나 콜레이션이 없는 바이너리를 저장하는 반면, TEXT 타입은 문자 셋과 콜레이션이 있다는 것밖에 없다.

MySQL 은 BLOB 과 TEXT 를 다른 타입과 다른 방법으로 정렬한다. 문자열을 통쨰로 정렬하지는 않으며, 컬럼에서 max_sort_length 바이트 값만큼 문자열을 가져와서 정렬한다. MySQL 은 이런 데이터 타입의 값 전체를 가지고 인덱스를 만들진 못하고, 정렬을 하는 데 인덱스를 사용할 수도 없다.

문자열 타입 대신 ENUM 사용

ENUM 컬럼은 독립적인 문자열 값들을 65,535개 까지 저장한다. MySQL 은 ENUM 값을 매우 효율적으로 저장한다.

ENUM 의 가장 큰 문제는 문자열 목록이 고정되며, 문자열을 추가하거나 지우려면 ALTER TABLE 을 사용해야 한다는 것이다. 또한 MySQL 에선 ENUM 컬럼의 값을 정수로 저장하고, 값을 가져올 때는 이를 문자열 표현으로 변환하기 때문에 추가적인 비용이 든다.

4. 날짜 및 시간 타입

DATETIME

이 타입은 매우 큰 범위의 값, 즉 1001년부터 9999년까지의 값을 1초 단위로 저장할 수 있다. DATETIME 은 날짜와 시각을 YYYYMMDDHHMMSS` 포맷의 정수 값으로 묶는데, 시간대에는 영향을 받지 않는다. DATETIME 은 8 바이트의 저장 공간을 사용한다.

TIMESTAMP

이름이 암시하듯, TIMESTAMP 타입은 1970년 1월 1일 자정(그리니치 평균시)을 기준으로 몇초가 지났는지를 저장하며, Unix 타임스탬프와 동일하다. TIMESTAMP 는 저장 공간을 4 바이트만 사용하므로 값의 범위가 DATETIME 보다 훨씬 작고, 1970년부터 2038년 사이의 값만을 저장할 수 있다. MySQL 에서는 FROM_UNIXTIME(), UNIX_TIMESTAMP()` 같은 함수를 제공하는데 이 함수를 이용하면 Unix 타임스탬프를 날짜로 바꾸거나 그 날짜를 Unix 타임스탬프로 바꿀 수 있다.

TIMESTAMP 에 출력되는 값은 시간대에 따라 다르다. MySQL 서버, 운영체제, 클라이언트 연결은 모두 시간대 설정이 있다. 또한 TIMESTAMP 에는 DATETIME 에는 없는 특별한 특성들이 있다. 기본적으로 TIMESTAMP 컬럼에 값을 지정하지 않은 채로 행을 추가하면 첫 번째 TIMESTAMP 컬럼에 현재 시각을 넣는다. 또, UPDATE 실행 시 TIMESTAMP 값을 명시적으로 할당하지 않고 실행하면 TIMESTAMP 컬럼의 값이 자동으로 업데이트 된다. (즉, TIMESTAMP 는 기본적으로 NOT NULL)

5. 비트(Bit)로 구성된 데이터 타입

대부분의 응용프로그램에선 Bit 타입을 쓰지 않는 편이 낫다. Bit 타입을 사용할 만한 응용프로그램의 예로는 권한을 저장하는 접근 제어 목록(ACL)이 있다.

6. 식별자 고르기

식별자(ID) 컬럼에 맞는 데이터 타입을 고르는 일은 매우 중요하다. 다른 컬럼에 비해 식별자 컬럼은 다른 값과 비교되거나 값을 조회하는데 사용되는 경우가 많다. 또한 다른 테이블에서 외부 키로 사용되기도 한다. 따라서 연관된 테이블 끼리는 같은 데이터 타입을 쓰는 편이 좋다. UNSIGNED 과 같은 특성까지 타입이 정확히 일치해야 한다.

동일하지 않은 데이터 타입을 섞어 쓸 경우 성능 문제가 발생할 수 있고, 성능상의 문제가 없더라도 값을 비교할 경우 암시적으로 형변환을 하기 때문에 찾기 힘든 오류가 발생할 수 있다. 이런 문제는 느닷없이 나타나게 되는데 이 때쯤에는 다른 데이터 타입끼리 비교한다는 사실조차 잊어버리고 있을 것이다.

정수 타입

빠른데다 AUTO_INCREMENT 도 잘 동작하므로 대체로 식별자에는 정수를 사용하는 것이 가장 좋다.

ENUM 과 SET

식별자로는 적합하지 않다.

문자열 타입

되도록 식별자에는 문자열 타입을 쓰지 말자. 문자열 타입은 공간을 많이 차지하며, 보통 정수 타입보다 느리다. MyISAM 테이블에서 문자열 식별자를 사용할 땐 특히나 주의해야 한다. 기본적으로 MyISAM 은 문자열에 압축된 인덱스를 사용하는데 이로 인해 조회가 굉장히 느리다. MD5(), SHA1(), UUID() 로 만든 문자열처럼 완전히 ‘무작위’ 인 문자열을 사용할 경우에도 매우 신중해야 한다. 이런 함수로 만들어진 값들은 방대한 영역에 걸쳐 무작위로 분산되는데 이로 인해 INSERT 나 특정 타입의 SELECT 쿼리가 느려질 수 있다.

무작위 문자열은, 삽입되는 값이 임의 위치의 인덱스로 가야 하기 때문에 INSERT 쿼리를 느리게 만들며, 클러스터 스토리지 엔진에 대해 페이지 분할, 임의 디스크 접근, 클러스터 인덱스 단편화 등을 일으킨다.
무작위 문자열은 논리적으로 가까운 행들이 디스크와 메모리 상에선 넓게 퍼져 있게 만들기 때문에 SELECT 쿼리를 느리게 한다.
무작위 값은 참조되는 값들이 모여 있지 못하게 만들며, 캐시는 참조되는 값들이 모여 있어야 효과를 낼 수 있으므로 무작위 값은 모든 타입의 쿼리에 대해 캐시 성능을 떨어뜨린다. 데이터 집합이 전부 접근 빈도가 비슷하다면, 데이터 일부를 메모리에 캐시하는 정도론 큰 이득을 얻을 수 없다. 그리고 작업 집합(워킹셋)이 메모리에 통째로 들어가지 못할 정도로 크면 캐시 미스와 플러시가 엄청나게 일어날 것이다.

UUID 값을 저장한다면 대시 기호를 제거한 후 UNHEX() 함수를 써서 UUID 값을 16바이트짜리 숫자로 변환한 다음 BINARY(16) 컬럼에 저장하는 것이 좋다. 그렇게 저장한 값은 HEX() 함수를 써서 16진수 포맷으로 찾아내면 된다.

인덱싱 기초

인덱스는 MySQL 이 데이터를 효율적으로 조회할 수 있도록 돕는 데이터 구조다. 인덱스는 테이블에서 지정된 하나 이상의 컬럼 값을 포함하고 있다. 두 개 이상의 컬럼을 묶어 인덱스로 만들 땐 컬럼의 순서가 매우 중요하다. MySQL 은 인덱스의 최좌측 프리픽스에 대해서만 효율적으로 검색할 수 있기 때문이다.

인덱스의 종류

인덱스의 종류는 많으며, 각 인덱스는 서로 다른 용도로 작동하도록 설계되어 있다. 인덱스는 서버 계층이 아닌 스토리지 엔진 계층에서 구현되므로 표준화되어 있지 않다.

1. B-트리 인덱스

스토리지 엔진은 B-트리 인덱스를 다양한 방식으로 디스크에 저장하는데, 저장 방식에 따라 성능 차이가 크다. 예를 들어, MyISAM 의 경우 인덱스를 작게 만드는 프리픽스 압축 기법을 쓰는데, 압축된 인덱스로는 최적화를 일부 적용할 수 없으므로 InnoDB 에서는 인덱스를 압축하지 않고 놔둔다.

B-트리는 모든 값을 순서대로 저장하며, 각 리프 페이지는 루트에서 같은 거리만큼 떨어져 있다. B-트리 인덱스를 사용하면 스토리지 엔진에서 원하는 데이터를 찾기 위해 테이블 전체를 뒤지지 않아도 되므로 데이터의 접근 속도가 빨라진다.

B-트리 인덱스는 루트 노드에서 시작한다. 루트 노드의 슬롯은 자식 노드를 가리키는 포인터를 보관하며, 스토리지 엔진은 이 포인터를 따라간다. 스토리지 엔진은 노드 페이지 안의 값을 살펴본 뒤 올바른 포인터를 찾아가는데, 이 값에는 자식 노드의 상한값과 하한값이 정의되어 있다.

결국 스토리지 엔진은 리프 페이지에 성공적으로 도달하거나 원하는 값이 존재하지 않는다고 판단하게 된다. (리프 페이지는 다른 페이지에 대한 포인터가 아닌 인덱스된 데이터에 대한 포인터를 가지므로 특별하다.)

B-트리는 인덱스된 컬럼을 순서대로 저장하기 때문에 데이터의 특정 범위를 검색할 때 유용하다. B-트리 인덱스를 사용할 수 있는 쿼리의 유형은 다음과 같다. (예시 인덱스는 성, 이름, 생일 순으로 생성된 인덱스가 있다)

값 전부와 일치하는 경우
가장 왼쪽 프리픽스와 일치하는 경우: 성이 동일한 것을 찾을 때
컬럼 프리픽스와 일치하는 경우: 성이 J 로 시작할 때
일정 범위의 값과 일치하는 경우: 성이 A 와 C 인 사이의 사람을 찾을 때
한 부분과 정확히 일치하고 다른 부분에서는 일정 범위의 값과 일치하는 경우: 성이 일치하고 이름이 A~K 범위일 때
인덱스에만 쿼리하기: 커버링 인덱스를 말한다.

B-트리 인덱스에는 다음과 같은 제약이 있다.

인덱스된 컬럼 중 가장 좌측에 있는 것부터 조회하지 않는 경우 인덱스는 쓸모가 없다
인덱스의 컬럼을 건너뛰지는 못한다: 성이 Smith 이면서 특정 날짜에 태어난 사람은 인덱스 사용 불가

2. 해시 인덱스

해시 인덱스는 해시 테이블 기반이며, 인덱스를 구성하는 모든 컬럼을 이용하는 경우에만 유용하다. 스토리지 엔진은 각 행마다 인덱스된 컬럼의 해시 코드를 계산하는데, 이 해시 코드는 작은 값을 가진다. 그리고 키 값이 다른 행이라면 해시 코드 값이 달라지게 된다. 스토리지 엔진은 해시 코드를 인덱스에 저장하며, 각 행을 가리키는 포인터는 해시 테이블에 저장된다.

(그런데 큰 의미는 없는 것이) MySQL 에서 해시 인덱스는 Memory 스토리지 엔진에서만 지원하고 있다.

InnoDB 스토리지 엔진은 어뎁티브 해시 인덱스라는 특별한 기능이 있다. InnoDB 는 매우 자주 접근하는 인덱스 값이 있다는 것을 알게 될 경우 B-트리 인덱스 위에 메모리 해시 인덱스를 만든다. 이로 인해 매우 빠른 해시된 조회 등 해시 인덱스의 특성을 B-트리 인덱스에 일부 부여할 수 있게 된다. 허나 이런 과정은 모두 자동으로 이루어지며 사용자가 제어하거나 설정하지는 못한다.

스토리지 엔진이 해시 인덱스를 지원하지 않으면 스스로 InnoDB 와 비슷한 방식으로 해시 인덱스를 흉내내면 된다. 이렇게 하면 키가 아주 길어도 인덱스 크기는 매우 작은 것 같은 해시 인덱스의 특성을 누리게 된다.

이런 접근법이 유효한 사례로는 URL 조회가 있다. URL 은 일반적으로 B-트리 인덱스를 커지게 하는데 이는 URL 이 아주 길기 때문이다. 보통 이런 식으로 쿼리를 날리게 된다.

1	SELECT id FROM url WHERE url = 'http://www.mysql.com';

하지만 url 컬럼의 인덱스를 제거하고 인덱스된 url_crc 컬럼을 테이블에 추가하면 이렇게 쿼리를 사용할 수 있다.

1 2	SELECT id FROM url WHERE url = 'http://www.mysql.com' AND url_crc=CRC32("http://www.mysql.com");

이 쿼리는 잘 동작하는데 MySQL 쿼리 옵티마이저가 url_crc 컬럼에 대한 작고 선택도가 매우 높은 인덱스가 있다는 것을 알고 해당 값(이 경우엔 1560514994)을 가진 엔트리를 인덱스 조회하기 때문이다.

예닐곱 개의 행이 똑같은 url_crc 값을 갖더라도 정수 비교가 빠르므로 해당 url_crc 값을 가진 행들을 찾아내고 검사하여 전체 URL 값이 정확히 일치하는 행을 찾기는 아주 쉽다. URL 을 전체 인덱스 하는 것에 비하면 훨씬 빠르다.

이 방식의 한 가지 약점은 해시 값을 유지해야 한다는 것이다. 스스로 해도 되고 아니면 MySQL 5.0 이상에선 트리거를 사용하면 된다. 또한 해시 충돌 가능성을 항상 생각하여 WHERE 조건에 해시 값 뿐만 아니라 원본 값도 항상 포함하여 조회해야 한다.

3. 공간(R 트리) 인덱스

생략

4. 전문 인덱스

생략

고성능을 위한 인덱싱 전략

전략1. 컬럼을 격리시키기

MySQL 은 일반적으로 쿼리의 컬럼이 격리되지 않으면 인덱스를 활용하지 못한다. 컬럼을 ‘고립시킨다’ 함은 그 컬럼이 표현식의 일부여선 안 되고 쿼리의 함수 내부에 있어서도 안 된다는 뜻이다.

예를 들어 actor_id 컬럼의 인덱스를 활용하지 못하는 쿼리를 살펴보자.

1	SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

비슷한 예시를 다시 보자

1	SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

이 쿼리는 date_col 의 값을 보고 10일이 지나지 않은 행을 모두 찾지만 TO_DAYS() 함수 떄문에 인덱스를 활용하지 못한다. 이런 쿼리를 짜는 더 나은 방법을 보자.

1	SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

이 쿼리는 인덱스를 활용하는 데 아무런 문제가 없지만 다른 방식으로 개선할 여지가 있다. CURRENT_DATE 에 대한 참조 때문에 쿼리 캐시가 결과를 캐싱하지 못한다. CURRENT_DATE 를 리터럴로 바꾸면 이 문제를 고칠 수 있다.

1	SELECT ... WHERE date_col >= DATE_SUB('2018-04-15', INTERVAL 10 DAY);

전략2. 프리픽스 인덱스와 인덱스 선택도

아주 긴 문자열 컬럼, 그러니까 인덱스가 크고 느려지는 원인이 되는 컬럼을 인덱싱해야 할 때도 있다. 이 때, 값 전체가 아닌 첫 문자 몇 개만 인덱싱함으로써 공간을 절약하고 더 나은 성능을 얻을 때도 있다. 이렇게 하면 인덱스가 공간을 덜 쓰지만 선택도가 낮아지기도 한다.

인덱스 선택도는 테이블에 든 행의 개수(#T) 대비 다른 것과 구별되는 인덱스 값의 개수(카디널리티)이며 1 / #T 와 1 사이의 값이다. 선택도가 높은 인덱스가 좋은데 이런 인덱스는 MySQL 이 일치하는 값을 찾을 때 더 많은 행을 필터링 해주기 때문이다. 유니크 인덱스는 선택도가 1이고 그만큼 좋다.

BLOB, TEXT 컬럼이나 아주 긴 VARCHAR 컬럼을 인덱싱한다면 프리픽스 인덱스를 정의해야 한다. MySQL 은 그런 컬럼을 몽땅 인덱싱하도록 허용하지 않기 때문이다.

중요한 건 선택도를 충분히 확보할 정도로 길지만 공간을 절약할 만큼은 짧은 프리픽스를 선택하는 일이다. 프리픽스는 컬럼 전체를 인덱싱한 것과 거의 유사한 정도의 선택도가 나올만큼 충분히 길어야 한다. 달리 말해 프리픽스의 카디널리티가 컬럼 전체의 카디널리티에 근접한 편이 좋다.

알맞은 프리픽스 길이를 계산하는 다른 방법은 전체 컬럼의 선택도를 계산한 후 프리픽스의 선택도가 그 값에 가까워지게 만드는 것이다. 전체 컬럼의 선택도를 알아내는 방법은 이렇다.

1	SELECT COUNT(DICTINCT city) / COUNT(x) FROM sakila.city_demo;

예시 결과가 0.0312 라고 가정해보자

목표 선택도를 0.031 정도로 잡으면 프리픽스는 평균적으로 만족스러울 것이다. 쿼리 하나로 다양한 길이를 평가하는 것도 가능한데 이는 테이블이 아주 클 때 유용하다.

SELECT 
   COUNT(DICTINCT LEFT(city, 3) / COUNT(x) AS sel3,
   COUNT(DICTINCT LEFT(city, 4) / COUNT(x) AS sel4,
   COUNT(DICTINCT LEFT(city, 5) / COUNT(x) AS sel5,
   COUNT(DICTINCT LEFT(city, 6) / COUNT(x) AS sel6,
   COUNT(DICTINCT LEFT(city, 7) / COUNT(x) AS sel7
FROM sakila.city_demo;

sel3: 0.0238
sel4: 0.0293
sel5: 0.0305
sel6: 0.0309
sel7: 0.0310

이 쿼리는 일곱 글자에 가까워지면서 프리픽스의 길이 증가가 조금씩 선택도를 개선시킴을 보여준다.

평균 선택도만 바라보는 건 좋은 생각이 아니다. 최악의 선택도에 대해서도 생각해야 한다. 데이터가 고르지 못하다면 그런 생각이 함정이 될 수 있다.

이제 예제 데이터에서 알맞은 값을 찾았으니 컬럼에 대한 프리픽스 인덱스를 생성하는 법을 알아보자

1	ALTER TABLE sakila.city_demo ADD KEY (city(7));

프리픽스 인덱스의 단점이라면 MySQL 은 프리픽스 인덱스를 ORDER BY 또는 GROUP BY 쿼리에 활용하지도 커버링 인덱스로 활용하지도 못한다.

전략3. 클러스터링 인덱스

테이블에 클러스터 인덱스가 있으면 그 테이블의 행들은 실제로 인덱스의 리프 페이지에 저장된다. ‘클러스터된’ 이란 용어는 인접된 키 값을 가지는 레코드들이 서로 가까운 데 저장된다는 사실을 가리킨다. 테이블 하나는 클러스터링 인덱스 하나만 가질 수 있는데 행들을 동시에 두 곳에 저장할 수는 없기 때문이다. 하나의 열에 Primary Key를 지정하면 자동으로 클러스터형 인덱스가 생성된다.

InnoDB 에서 클러스터 인덱스는 테이블 ‘그 자체’다. 클러스터 인덱스의 각 리프 노드에는 기본 키 값, `InnoDB 가 트랜잭션이나 MVCC 목적으로 사용하는 트랜잭션 아이디와 롤백 포인터, 그리고 나머지 컬럼이 들어 있다.

전략4. InnoDB 에서 기본 키 순서대로 행을 삽입하기

InnoDB 를 사용하고 특별히 클러스터링이 필요치 않다면, 인조 키(surrogate key)를 정의하는 것도 좋은 생각이다. 인조 키는 응용프로그램에서 파생되지 않은 값을 기본 키로 삼는다. 인조 키를 정의하는 제일 쉬운 방법은 보통 AUTO_INCREMENT 컬럼을 이용하는 것이다. 이렇게 하면 행이 순차적으로 삽입되고, 기본 키를 사용해 조인할 때 성능이 나아진다.

무작위(순차적이지 않은) 클러스터 키는 피하는 게 상책이다. 예를 들어 UUID 값은 성능이란 관점에서 보면 좋지 않은 선택이다. UUID 는 클러스터 인덱스가 무작위로 삽입되게 만드는 데 이는 최악의 시나리오이고 쓸만한 데이터 클러스터링을 전혀 제공하지 않는다. UUID 를 insert 할 때, 새 행이 이전 행보다 꼭 기본 키 값이 더 크다는 보장이 없기 때문에 InnoDB 는 항상 인덱스 끝에 새 행을 놓지는 못한다. 이 이야기의 교훈은 InnoDB 를 쓸 땐 기본 키 순서에 따라 데이터를 삽입하려고 애써야 하고, 또한 새 행마다 값이 단조롭게 증가하는 클러스터 키를 사용하려 노력해야 한다는 것이다.

전략5. 커버링 인덱스

인덱스의 리프 노드엔 자기가 인덱스한 값이 있다. 만약 인덱스를 읽어서 자신이 원하는 데이터를 얻을 수 있다면 행을 뭐하러 읽겠는가? 쿼리를 만족시키기 위해 필요한 데이터가 모두 포함된 인덱스를 커버링 인덱스라고 한다. 데이터가 아닌 인덱스만 읽을 때의 장점은 아래와 같다

인덱스 엔트리는 보통 행의 전체 크기보다 훨씬 작다
인덱스는 인덱스 값이 따라 정렬되기 때문에, I/O 바운드 범위 질의는 디스크상의 무작위 위치에서 행을 하나씩 가져오는 것에 비해 I/O 가 적게 들 것이다.
대부분의 스토리지 엔진은 데이터보다 인덱스를 캐싱하는 데 능하다.
커버링 인덱스는 특히 InnoDB 테이블에 도움이 되는데 InnoDB 의 클러스터 인덱스 때문이다. InnoDB 의 보조 인덱스는 행의 기본 키 값을 리프 노드에 담는다. 그래서 쿼리에 필요한 컬럼을 모두 갖는 보조 인덱스는 기본 키까지 들여보지 않는다. 보통 EXPLAIN 으로 쿼리를 확인했을 때 Extra: Using Index 를 보게 된다면 커버링 인덱스를 활용하고 있는 것이다.

대부분의 스토리지 엔진에서 인덱스는 인덱스에 포함된 컬럼에 접근하는 쿼리만 커버할 수 있다. 하지만 InnoDB 는 조금 더 최적화를 한다. InnoDB 의 보조 인덱스는 기본 키 값을 자신의 리프 노드에 간직하고 있다. 이는 보조 인덱스가 InnoDB 가 쿼리를 커버하는 용도로 쓸 수 있는 ‘추가 컬럼’ 을 사실상 갖게 된다는 뜻이다.

예를 들어 sakila.actor 테이블은 InnoDB 를 쓰고 last_name 컬럼에 인덱스가 있다. 그러니 인덱스는 기본 키 칼럼인 actor_id 를 인출하는 쿼리를 커버할 수 있다. 그 기본 키 컬럼이 인덱스의 일부가 아니라도 말이다.

전략6. 중첩 또는 중복된 인덱스

중복 인덱스는 똑같은 컬럼 집합을 똑같은 순서로 배열해 생성한 같은 유형의 인덱스를 말한다. 이런 인덱스는 만들지 않도록 조심하고, 이런 인덱스를 발견한 경우에는 제거해야 한다.

CREATE TABLE test (
	ID INT NOT NULL PRIMARY KEY,
	UNIQUE(ID),
	INDEX(ID)
);

MySQL 은 인덱스를 이용해 고유 제약조건과 기본 키 제약조건을 구현하기 떄문에 위의 코드는 같은 컬럼에 세 개의 인덱스를 생성하고 만다!

중첩된 인덱스는 중복 인덱스와 약간 다르다. (A,B) 에 대한 인덱스가 있다면 이 인덱스의 프리픽스는 (A) 이므로 (A) 에 대한 인덱스는 중첩된 것이다. 이 때는 (A,B) 인덱스를 (A) 하나 짜리 인덱스 대신 쓰면 된다. 대부분의 경우 중첩 인덱스는 만들지 않는 것이 좋으며, 중첩 인덱스를 만들지 않으려면 새 인덱스를 추가하기보다는 기존 인덱스를 확장하는 것이 좋다.

하지만 기존 인덱스를 확장할 경우 인덱스가 커지게 되고 그러다 보면 어떤 쿼리에선 성능이 저하될 수 있으므로 성능을 이유로 중첩된 인덱스를 사용해야 할 때도 있다.

인덱싱 사례 연구

인덱스를 설계하기 위해선 희한하게도 인덱스 기반의 정렬을 써야 하는지, 파일 정렬을 써도 될지부터 결정해야 한다. 인덱스 기반의 정렬은 인덱스와 쿼리를 어떻게 형성해야 할지를 제한한다. 예를 들어, 어떤 쿼리가 인덱스를 이용해 다른 사용자가 준 점수에 따라 사용자들을 정렬하는 경우, WHERE age BETWEEN 18 ANd 25 와 같은 WHERE 절에 인덱스를 쓰지 못한다. MySQL 에서는 쿼리가 범위 제한을 위해 인덱스를 이용하는 경우, 정렬용으로 다른 인덱스를 쓰지 못하기 때문이다. 이런 WHERE 절이 가장 흔히 쓰이는 WHERE 절이라면, 파일 정렬이 필요한 쿼리가 많으리라는 것은 당연한 사실이다.

여러 종류의 필터링 지원하기

country 컬럼은 선택도가 높을 수도 있고 낮을 수도 있다. 어쨌거나 이 컬럼은 대부분의 쿼리에 쓰일 것이다. sex 컬럼은 선택도가 낮은 게 분명하지만 그럼에도 거의 모든 쿼리에서 사용될 것이다. 이를 염두해서 (sex, country) 가 앞에 오는 컬럼 조합들에 쓸 인덱스를 생성하자. 상식적으로는 선택도가 매우 낮은 컬럼을 인덱싱해봐야 소용없는데 선택도가 낮은 컬럼을 인덱스의 시작으로 하는 이유가 있을까?

이렇게 하는 이유는 두 가지다. 하나는 거의 모든 쿼리가 sex 컬럼을 사용하고 있기 때문이고, 두 번째는 나름의 트릭이 있기 때문에 컬럼을 추가하더라도 그리 불리할 게 없기 떄문이다.

트릭이지만, 성별로 결과를 제한하지 않는 쿼리를 던졌더라도 AND sex IN (‘m’, ‘f’) 를 WHERE 절에 넣으면 인덱스를 사용하게 만들 수 있다. 이렇게 하면 어떤 행도 가려내지 않으므로 WHERE 절에 sex 컬럼을 포함시키지 않는 것과 기능적으로 동일하게 동작한다. 하지만 이렇게 하면 MySQL 이 인덱스에서 이 컬럼을 포함하는 더 긴 프리픽스를 사용하게 만든다.

다음으로 WHERE 조건에 어떤 조합이 쓰이게 될지, 그리고 어떤 조합이 적절한 인덱스 없이는 느리게 동작할지를 생각해보자. (sex, country, age), (sex, country, region, age), (sex, country, region, city, age) 등등의 인덱스가 필요할 것 같다. 여기서 IN 트릭을 활용하면 (sex, country, age), (sex, country, region, age) 인덱스는 폐기가 가능하다. 마지막 인덱스로 자주 사용될 쿼리의 커버가 가능하다는 뜻…

하지만 덜 흔한 컬럼에도 맞도록 인덱스를 설계하려면 어떻게 할까? 이런 컬럼들은 선택도가 극히 낮고, 자주 사용되지 않는다면, 인덱스를 만들지 말고 MySQL 에서 행을 몇 개 더 스캔하도록 놔두면 된다.

그런데 age 컬럼은 계속해서 인덱스의 끝에 두고 있음을 알 수 있다. MySQL 은 처음으로 범위 조건을 지정한 컬럼 및 그보다 왼쪽에 있는 컬럼만 인덱스에서 쓸 수 있으므로 MySQL 이 인덱스의 컬럼을 최대한 많이 이용할 수 있도록 만들려고 하는 것이다. 여태까지 언급한 다른 컬럼들은 모두 WHERE 절에 동일성 조건을 이용할 수 있다. 하지만 age는 범위 조건으로 쓰일게 분명하다. 인덱스의 일반 원칙은 인덱스 끝에 범위 조건을 주라는 것이다. 그러면 옵티마이저에서 최대한 많은 부분의 인덱스를 활용할 수 있다.

다수의 범위 조건 피하기

SELECT * FROM test 
WHERE eye_color IN ('a', 'b', 'c')
  AND hair_color IN ('d', 'e', 'f')
  AND last_online > DATE_SUB('2018-01-07', INTERVAL 7 DAY)
  AND age BETWEEN 18 AND 25

MySQL 에서는 last_online 기준의 인덱스와 age 기준의 인덱스 중 하나를 쓸 수 있지만 두 인덱스를 함께 사용할 순 없다. age 제한 없이 last_online 제한만 있거나 last_online 이 age 보다 선택도가 높으면, last_online 을 끝에 두는 인덱스 집합을 하나 더 추가하면 된다.

정렬 최적화하기

1	SELECT columns FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 10;

이 쿼리엔 WHERE 와 ORDER BY 가 모두 있는데 (sex, rating) 인덱스가 없다면 매우 느릴 것이다.

캐시 및 집계 테이블

떄로는 추출 값 (연산을 통해 계산된 값)을 테이블에 중복 저장해두는 것이 성능을 향상시키는 최선의 방법일 때가 있다. 하지만 캐시 테이블이나 집계 테이블 등을 따로 구축해야 할 때도 있다. 이런 방식은 다소간의 정확하지 않은 데이터를 묵인할 수 있는 경우 최선의 방법이며, 가끔 이 방법 외에는 선택권이 없는 경우도 있다. (여기서 캐시 테이블은 논리적으로 중복이지만 인출해내기 쉬운 데이터를 담은 테이블을 말하고, 집계 테이블은 GROUP BY 쿼리에서 얻은 집합적인 데이터를 담은 테이블을 뜻한다)

웹 사이트에서 지난 24시간 동안 새 글이 얼마나 올라왔는지 세어야 한다고 해보자. 사람 많은 사이트에서는 실시간 카운터를 정확히 유지하기란 불가능하다. 그 대신 매 시간마다 집계 테이블을 생성하는 방법을 사용할 수 있다.

이제 캐시 테이블을 살펴보자. 캐시 테이블은 값을 가져오는 쿼리나 검색 쿼리를 최적화할 때 유용하다. 다양한 형식의 쿼리들을 빠르게 만들기 위해 여러 개의 인덱스 조합이 필요하게 될 수도 있는데, 각 요구사항마다의 충돌이 발생할 수 있어서 주 테이블의 몇몇 컬럼을 캐시해둔 캐시 테이블을 생성해야 할 때도 있다. 이 때 사용할 수 있는 유용한 기법 중 하나는 캐시 테이블에는 주 테이블과 다른 스토리지 엔진을 쓰는 것이다. 예를 들어, 주 테이블로 InnoDB 를 쓸 경우 캐시 테이블엔 MyISAM 을 쓰게 되면 인덱스 크기를 줄일 수 있는데다 쿼리에 전문 검색까지 사용할 수 있게 되는 장점이 있다. 때로는 검색을 더 효율적으로 할 수 있는 특화된 시스템(Lucene 이나 Sphinx 검색 엔진)으로 넣을 수도 있다.

카운터 테이블

테이블에 카운트 값을 유지하는 응용프로그램은 카운터를 동시에 갱신할 떄 동시성 문제를 겪을 수 있다.

1
2
3

CREATE TABLE hit_counter (
	cnt int unsigned not null
) ENGING = InnoDB;

웹 사이트 방문마다 카운터를 갱신한다

1	UPDATE hit_counter SET cnt = cnt + 1;

문제는 사실상 이 행 하나가 카운터를 갱신하는 트랜잭션들이 모두 공유하는 전역 ‘뮤텍스’라는 사실이다. 이 때문에 트랜잭션들은 일렬로 줄 세워진다. 행을 여러 개 두고 무작위로 갱신하면 좀 더 동시성을 높일 수 있으나, 이렇게 하려면 테이블을 다음과 같이 변경해야 한다.

CREATE TABLE hit_counter (
	slot tinyint unsigned not null primary key,
	cnt int unsigned not null
) ENGINE=InnoDB;

테이블에 행 100개를 채워넣자. 이제부터는 쿼리에서 무작위로 슬롯(slot)을 고른 뒤 값을 업데이트 할 수 있게 된다.

1	UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

이제 통계를 얻어오려면 단순히 집합 쿼리를 사용하기만 하면 된다.

1	SELECT SUM(cnt) FROM hit_counter;

일반적으로 필요한 기능으로 가끔식(예를 들어 하루애 한번) 카운터를 새로 시작하는 기능이 있다. 이런 기능이 필요한 경우라면 다음과 같이 스키마를 조금만 변경하면 된다.

CREATE TABLE hit_counter (
	day date not null,
	slot tinyint unsigned not null,
	cnt int unsigned not null,
	primary key(day, slot)
) ENGINE=InnoDB;

이런 시나리오에선 행을 미리 생성해두는 대신 ON DUPLICATE KEY UPDATE 명령을 이용할 수 있다.

1
2
3

INSERT INTO daily_hit_counter(day, slot, cnt)
VALUES (CURRENT_DATE, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1

행의 개수를 줄여서 테이블을 작게 유지하고 싶다면 모든 결과 값을 병합하여 슬롯 0에 넣은 뒤 나머지 슬롯은 지워주는 작업을 주기적으로 실행하면 된다.

UPDATE daily_hit_counter as c
  INNER JOIN (
	 SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
	   FROM daily_hit_counter
	   GROUP BY day
  ) AS x USING(day)
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0)
    c.slot = IF(c.slot = x.mslot, 0, c.slot);

DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

스토리지 엔진에 대해

InnoDB 스토리지 엔진의 특징

1. 트랜잭션

InnoDB 는 트랜잭션 및 네 단계의 트랜잭션 고립 수준들을 지원한다

2. 외래 키

MySQL 5.0 에서 InnoDB 는 유일하게 외래 키를 지원하는 표준적인 스토리지 엔진이다. 다른 스토리지 엔진은 CREATE TABLE 문에서 외래 키 옵션을 명시할 수 있지만, 실질적으로 외래 키를 생성하진 않는다.

3. 행 수준 잠금

잠금은 잠금 확장(Lock escalation) 없이, SELECT 를 블록하지 않으면서 행 수준에서 설정되는데, 표준인 논블로킹 SELECT 는 전혀 잠금을 하지 않으므로 동시성이 아주 좋아진다.

4. 다중 버전

InnoDB 는 다중 버전 동시성 제어(multiversion concurrency control)를 이용하므로 기본적으로 SELECT 시에는 예전 데이터를 읽게 된다. MVCC 아키텍처를 이용하면 굉장히 복잡도가 증가하기 떄문에 해당 내용을 명확히 알고 있어야 한다.

5. 기본 키에 따라 클러스터링 함

InnoDB 테이블은 모두 기본 키에 따라 클러스터링되는데, 이를 잘 이용하면 스키마를 설계할 때 이득을 볼 수 있다.

6. 모든 인덱스에 기본 키 컬럼에 포함됨

인덱스는 기본 키에 따라 행을 참조하므로 기본 키를 짧게 해두지 않으면 인덱스의 크기가 엄청나게 증가하게 된다.

7. 캐시가 최적화되어 있음

InnoDB 에서는 데이터와 메모리 모두를 버퍼 풀에 캐싱한다. 또한 해시 인덱스를 자동으로 구축해서 행을 빠르게 가져올 수 있도록 한다.

8. 인덱스를 압축하지 않음

프리픽스 압축을 이용해서 인덱스를 압축하지 않으므로 MyISAM 테이블용 인덱스보다 크기가 더 클 수 있다.

9. 데이터 적재가 느림

MySQL 5.0 까지의 InnoDB 는 데이터 적재 연산을 특별히 최적화하지 않고 있다. 인덱스를 정렬 순서에 따라 구축하지 않고 한번에 한 행씩 구축한다. 그렇기 때문에 데이터 적재가 심각하게 느릴 수 있다.

10. AUTO_INCREMENT 가 블로킹 연산임

5.1 이전의 MySQL 에선 InnoDB 가 AUTO_INCREMENT 값을 새로 만들어낼 때마다 테이블 수준의 잠금을 사용한다

11. COUNT(x) 값이 캐시되지 않음

MySQL performance

Lee Hee Chang

greg.lee 의 프로그래밍과 여러 생각에 대해 끄적이는 공간입니다.