mysql 테이블 컬럼수 증가에 따른 성능 이슈

How number of columns affects performance?

-> 팀이동후 DBA로 업무분석하다 처음으로 정리한 내용이었다. 당시 mysql 5.5에 MyISAM이라 시도때도 없이 Lock이 잡히고 새벽에 장애나고 ; 가장 큰 문제는 납작두꺼비처럼 옆으로 퍼져버린 테이블이 문제였는데.. 테이블 정규화를 해야하는데 소스수정범위가 너무 크다보니 개발쪽에 일정잡기가 어려워 결론적으로 현재는 옆으로 더 퍼져있고;;.. 인덱스만 추가하고 function/procedure에서 full scan 안타게 조심해서 쓰고 있다.

한번 선언한 테이블의 정규화는 많은 시간과 비용이 든다는 것을 다시 한번 느끼게 된다.

 

1) innoDB engine에서 테이블에 최대생성 가능한 컬럼과 인덱스

- 컬럼 1017개

- 인덱스 64개

-> 적정 컬럼 수는 40개 내외라고 한다.

 

2) 컬럼을 늘릴 때 발생할 수 있는 이슈

- 테이블에 인덱스 검색을 한다고 하더라도 DB 내부 Logic은 인덱스조건에 해당하는 row의 전체를 읽기 때문에 한번에 읽을 데이터의 값이 커지고 io에 부담을 준다.

DB컬럼은 여러 개의 Linked List로 구성되어있고 인덱스를 사용하더라도 해당열의 전체 값을 가져오기 때문에 컬럼이 늘어날수록 데이터 조회 비용은 늘어날 수밖에 없다.

 

#아래는 (프로게이밍연구실)에서 발췌한 내용임

DB에서 연결리스트를 가장 많이 사용하는 부분이 바로 테이블

컬럼명	캐릭터ID	캐릭터이름	레벨	경험치	게임머니
자료형	INT	NVARCHAR(50)	SMALLINT	INT	BIGINT
크기	4 BYTE	50 BYTE	2 BYTE	4 BYTE	8 BYTE

간단하게 예를 들어 만든 테이블 구조입니다. 위 구조라면 한 행의 크기는 68 BYTE의 작은 테이블이지요.

온라인 게임에서 사용되는 테이블의 크기는 보통 이렇게 작은 데이터들로 이루어지기 때문에 별 문제가 발생하지 않습니다

SELECT 캐릭터ID, 캐릭터이름, 게임머니FROM 캐릭터테이블 WHERE 캐릭터ID = 1

 

이렇게 쿼리를 날린다면 가져오는 데이터는 총 62 BYTE의 데이터를 가져옵니다.

하지만 데이터베이스 엔진의 내부 알고리즘의 움직임에 조금 관심을 가져본다면 ‘어떤 과정을 통해 데이터를 가져오는 것일까?’라는 생각을 해보신다면 재미있는 것을 알 수 있습니다.

 

바로 ‘연결된 리스트’라는 것이 그 재미의 핵심입니다.

 

아무리 인덱스를 사용하여 데이터를 빠르게 가져온다고 해도 연결된 리스트라는 녀석은 내부적으로 한 행의 모든 데이터내용을 싹 읽어버립니다. 게임과 같이 작은 데이터를 다룬다면 큰 문제가 없겠지만 아래처럼 게시판일 경우에는? 얘기가 완전 달라지는 것이죠.

 

컬럼명	글번호	게시자	제목	본문	게시일
자료형	INT	NVARCHAR(50)	VARCHAR(200)	TEXT or Image	SMALLDATETIME
크기	4 BYTE	50 BYTE	200 BYTE	2^31-1 BYTE(2GB)	8 BYTE

 

위에 보이는 것처럼 큰 데이터 열이 있다면 연결된 리스트로 데이터를 읽을 때에 어마어마한 비용이 발생하겠지요?

그런데 위에서 보이는 것과 같이 큰 크기의 열이 있으면 그만큼 많은 페이지를 읽어야 하고 어마어마한 데이터를 ‘사용하지 않더라도 읽어야 하는 것’이죠.

인덱스를 사용하면 빠르게 데이터를 조회할 수 있지만 만약 게시판에 정말 많은 량의 내용을 담은 게시물이 있을 경우 성능을 보장할 수 없습니다.

그렇다면 ‘어떻게 하면 이런 재앙을 막을 수 있을까?’에 대한 연구해봅시다.

개인적으로 저는 저렇게 큰 데이터 열을 별도의 테이블로 분리시켜야 한다고 생각합니다.

‘게시물을 가져올 때에 JOIN을 하게 되니 성능이 더 나빠지지 않느냐!?’ 라고 반박하시는 분께서 계실지 모르겠지만, 인덱스를 사용해서 정확하게 SEEK한 데이터는 무조건 0초 만에 수행됩니다.

 

게시물 본문과 글 번호를 별도의 테이블로 분리한 후 본문을 조회할 때에만 조인하여 가져오는 방식이 제가 생각할 수 있는 최고의 성능을 보장할 수 있는 최선의 방법입니다.

 

혹시라도 이런 고민을 해보신 적 있으시다면 같이 연구해보면 정말 좋을 것 같습니다.

 

3) 성능 테스트 (ex 컬럼count 100개)

- 결과 char, int 컬럼의 경우 100개까지 늘려도 성능에 큰 무리가 없지만

varchar 컬럼의 경우 io가 slowdown되어 성능에 큰 영향을 줌

 

참조사이트:

https://www.percona.com/blog/2009/09/28/how-number-of-columns-affects-performance/