MySQL 인덱스 크기와 디스크 I/O 최적화: 데이터베이스 성능 향상 가이드

배경

해축갤 프로젝트를 하며 데이터가 200만개에서 1억개로 늘어났을 때 게시물 조회 성능이 급격히 안 좋아져
쿼리 개선, title, content 기준 인덱스를 이것저것 넣어보며 실험을 하고 있었습니다.

인덱스중 title, content 2개를 가지고 B-Tree 인덱스를 생성하려 했다가 문제가 발생했습니다.

구체적인 상황 (like DB, 코드 구조)

해축갤의 게시글 테이블의 구조는 다음과 같습니다.

Post 테이블의 구조 및 데이터 타입

주의해서 봐야 하는 부분은 content 가 varchar(1000) 라는 겁니다.

제가 사용하는 MySQL 버전은 8.0.36 이고 입력한 명령어는 다음과 같습니다.

CREATE INDEX idx_title_content 
ON post (title(100), content(100));

문제

위 쿼리를 실행하게 되면 다음과 같은 에러가 나오게 됩니다.

ERROR 1071 (42000): Specified key was too long;
 max key length is 3072 bytes

즉, 인덱스로 넣을 수 있는 컬럼의 데이터 유형, 크기는 최대 3072 바이트라는것.
위에서 봤듯이 content 는 게시글의 내용이라 varchar(1000) 입니다.

 

String 이 8 byte 고 여기에 1000 을 곱하면 8 * 1,000 = 8,000 byte 입니다.
즉, content 가 길어서 문제가 되는 겁니다.

밑바탕 되는 기본 원리

- 인덱스는 데이터를 빨리 찾기 위한 자료구조이다.

- 그 목적은 데이터 검색시 디스크 접근 횟수를 줄이기.

- DBMS 는 크게 3가지 요인으로 인덱스를 메모리에 로딩한다.
          1. 일반적으로 필요할 때 로딩.
          2. 캐싱 / [[버퍼풀 메커니즘]] 사용 -> 자주 사용되는 인덱스는 MySQL 버퍼풀에 저장함!
          3. 가용 가능한 메모리 등의 자원 상황에 따라 최적화

- 즉, 인덱스를 사용할땐 인덱스가 메모리에 올라가기에 무거우면 안된다.

- 인덱스에서 하나의 노드에 들어가는 키 값이 너무 크면 메모리에 인덱스를 전부 담을 수 없다.

- 이렇게 될 경우 메모리에 load 하지 못한 나머지는 디스크에서 조회해야 한다.

- 그래서 MySQL 은 기본값으로 3072 바이트로 인덱스 컬럼의 크기를 제한한다. (버전 별로 상이함!)

개선/해결 고민 및 선택

저는 다음과 같은 SQL 쿼리문으로 해결했습니다.

CREATE INDEX idx_title_content ON post (title, content(255));

위 쿼리는 title, content 에 대해서 인덱스를 생성하되,

content 에 대해서는 255 byte 까지만 인덱스의 키 값으로 두겠다는 것입니다.

 

이렇게 했던 이유는 해축갤의 비즈니스 특성 때문입니다.
다음은 해축갤의 검색 결과물입니다.

해축갤 '흥민' 검색 결과

흥민이라고 검색했을 때 제목에 흥민이라는 키워드가 없는 게시물들이 존재합니다.
이런 글에 들어가보게 되면 다음과 같습니다.

곧바로 '흥민' 이 나온다

 

곧바로 '흥민' 이 나온다 (2)

제목에서 문장을 시작해, 곧바로 본주제에 대한 키워드를 말하며 문장을 마무리하는 식의 글이 많다는 것입니다.

즉 글 상단부에서 주제의 메인 키워드가 보통 있기 때문에 이런 결정을 내렸씁니다.

5. 결론

- 프로젝트 배경: 해축갤 프로젝트에서 데이터가 200만 개에서 1억 개로 증가하면서,
게시물 조회 성능이 급격히 저하됨.

- 문제 발생: title, content를 포함한 B-Tree 인덱스 생성 시도 중,
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes 오류 발생.

- 문제의 원인: content 컬럼의 데이터 타입이 varchar(1000)으로 설정되어 있어, 인덱스 키의 최대 길이를 초과함.

- 해결 방법:CREATE INDEX idx_title_content ON post (title, content(255)); 명령어를 사용하여 인덱스 생성.
content 컬럼에 대해서는 255 바이트까지만 인덱스의 키 값으로 설정.

- 해결 방법의 근거:해축갤의 검색 패턴과 게시물 내용 분석을 통해,
content의 상단 부분에 주요 키워드가 포함되어 있는 경우가 많음을 확인.
이에 따라 content의 일부만 인덱스에 포함시켜도 검색 효율성이 유지될 수 있음을 판단.

 

이 방법을 통해 인덱스의 크기 문제를 해결하고, 데이터베이스의 검색 성능을 개선할 수 있었습니다.