맛의 완성은 데코레이션 DB의 완성은 튜닝 데이터베이스 튜닝

이재학 l 웹젠의 DBA

안 타까운 일이지만 불과 몇 년 전만 해도 많은 개발자들이 데이터베이스의 튜닝에 별다른 관심을 보이지 않았다. 그나마 다행스러운 것은 최근 들어 데이터베이스 관련 커뮤니티에 튜닝과 관련된 내용의 글이 자주 등장한다는 점이다. 이는 최근 만들어진 데이터베이스에 문제가 있거나 갑자기 데이터베이스 튜닝에 대한 이슈가 부각 되었다기보다는 오랫동안 묵혀둔 병이 이제야 그 증상을 나타내기 시작하고 있다고 생각하는 편이 옳을 것이다. 사람이나 데이터베이스나 조기 진단과 처방만큼 든든한 것이 없다. 적은 지면을 통해 튜닝에 대한 모든 지식을 전달할 수는 없겠지만 이 글이 데이터베이스 튜닝에 대한 관심에 불을 댕기는 도화선이 되어 주기를 바란다.

튜닝(Tuning)은 ‘조율’ 또는 ‘미세조정’이라는 사전적 의미를 가지고 있지만 데이터베이스 세계에서는 그 의미가 조금 다르다. 조율이나 조정이라기보다는 ‘최적화’가 더 가까운 뜻으로 쓰이고 있기 때문이다. 그럼 데이터베이스 튜닝을 통해 무엇을 최적화 할 수 있는 것일까? 그 대상은 매우 많다. 업무를 최적화 할 수도 있고, 설계나 하드웨어적인 병목 현상을 해결하여 최적화 하거나 SQL을 최적화 할 수도 있다.

이러한 최적화는 단지 처리 속도를 높이는 것에 그치는 것이 아니라 사용자가 필요한 때에 원하는 정보를 제공하는데 그 목적이 있다고 할 수 있다.

사 용 중인 데이터베이스를 튜닝하여 적시성을 확보하려면 먼저 시스템이 느려지는 원인을 파악해야 한다. 하지만 실제로 성능 저하의 원인을 파악하는 것은 그리 쉬운 일이 아니다. 그래서 쉬운 방법으로 하드웨어를 증설하거나 괜한 DBMS 제품을 의심해 보기도 하지만 성능 저하의 원인을 DBMS에 떠넘기기는 곤란하다. 일례로 비슷한 규모의 동종 업체라도 비싼 장비를 가지고도 데이터베이스의 문제를 해결하지 못하는 반면, 어떤 업체는 아주 저렴한 장비로 훌륭하게 데이터베이스를 활용하고 있는 경우를 들 수 있다.

사실 DBMS와 하드웨어에 대한 성능의 차이는 벤치마킹 사이트(http://www.tpc.org)에서 쉽게 확인할 수 있기 때문에 대부분 쉽게 자기 회사의 규모에 맞는 시스템을 선택할 수 있다. 적당한 혹은 넉넉한 서버의 자원을 가지고도 데이터베이스를 삐걱거리게 만드는 문제의 중심에는 사람이 있다고 보는 것이 옳다. 특히 데이터베이스 설계자는 그 중에서도 가장 핵심적인 인물이다.

코드테이블과 식별자 관계

흔히 데이터베이스의 설계라고 하면 물리적인 설계를 의미한다. 즉, 인덱스, 테이블, 제약조건 등에 대한 정의가 포함된다. 요즘의 데이터베이스라면 코드테이블이 하나쯤은 있는 것이 당연하게 여겨지기도 한다. 예를 들면, (직업코드, 직업명)과 같은 형태이다. ERP(Enterprise Resource Planning)와 같은 시스템의 경우 ‘코드마스터’ 라는 명칭으로 코드테이블이 대부분 존재한다. 이렇게 코드테이블을 두는 이유는 뭘까? 단순히 선배들이 그렇게 해왔기 때문이나 남들이 그렇게 하기 때문에 또는 책에서 그렇게 지시했기 때문이라고 대답할 수는 없는 일이다. 코드 테이블을 두는 이유는 유지보수의 편리성과 성능 향상에 있다.

먼 저 유지보수의 편리성에 대해 이야기 해보자. 뒤에 있는 <그림3>에서 Dept가 ‘부서코드’ 테이블이고 부서의 명칭이 ‘전산실’에서 ‘정보시스템관리실’로 변경되었다고 가정하자. 그럼 위와 같은 경우 Dept 테이블에서 ‘전산실’을 ‘정보시스템관리실’로 변경하면 끝이다. 그러나 Emp 테이블에서 DeptNo(부서번호) 대신 DeptName(부서명)으로 들어갔다면 해당되는 사원의 부서명을 모두 고쳐주어야 한다.

그럼, 성능은 어떻게 향상되는 것일까? 코드는 INT형이고, 코드명은 가변길이 문자형(최대 20) 이라면 INT형을 저장하는 것이 저장 공간을 적게 잡아먹고, 만약 인덱스 등의 객체가 생성된다면 인덱스의 크기도 줄어들 것이다. 예를 들어, 100만 건의 데이터가 있다고 가정하자. INT형이라면 4×1,000,000 바이트이며, 가변길이 문자형이라면 20×1,000,000 바이트가 된다. 쉽게 말해 같은 데이터에 가변길이 문자형을 저장할 경우 500페이지짜리 책을 만들 수 있는 반면, INT형을 사용하면 1,000페이지짜리 책이 만들어 지는 것의 차이와 같은 것이다.

데이터베이스 관련 책을 보면 모델링에는 모델러의 주관적인 생각이 들어간다는 말을 쉽게 접할 수 있다. 사실 맞는 말이지만 ‘주관’이라는 의미는 좋은 의미로 해석될 수도 있지만 나쁜 의미로 해석될 수도 있다. 다시 말해 주관은 실제세계를 표현하는 방법의 차이 정도로 해석할 수도 있지만, 나쁜 의미로 해석하자면 모델러의 능력이라고 볼 수도 있기 때문이다(사실 프로젝트에 투입되는 인원 중 모델이나 설계문서를 검토하여 문제점을 지적해 낼 수 있는 사람이 거의 없기 때문에 검증된 외부업체에 검토를 의뢰하는 것이 좋다). 설계에 따라서 어떻게 다른 구현이 이루어지는지 또한 성능상의 차이가 있는지 예를 통해 알아보자.

<그림 1> 비식별자 관계와 식별자 관계

< 그림 1>과 같이 하나의 대상을 두고도 서로 다른 두 사람이 두 가지 형태의 모델을 도출했다고 가정하자. A, B 모델의 차이는 그림에서 보는 바와 같이 A 모델은 비식별자 관계를 맺고 있고, B 모델은 식별자 관계(외부 키가 기본 키의 일부 또는 전체가 됨)를 맺고 있다. 어떤 것이 더 좋은 모델일까? 현재로써는 알 수 없다. 단지 A 모델을 만든 사람이 왜 A처럼 만들었지 왜 B처럼 만들었는지 확실하게 알고 있다면 두 모델은 문제점이 없다고 봐도 무방하다. 하지만 왜 그렇게 했는지 모른다면 그게 바로 문제이다. ERD(entity Relationship Diagram)처럼 생긴 문서를 던져주고 개발을 지시하면서 개발 시간을 재촉한다면 그 자체가 잘못인 것이다.

<그림 1>에서 두 모델이 실제 물리적으로도 똑같이 구현되어 있고, 두 모델의 최상위에 있는 테이블 A를 코드테이블이라고 가정하자. 만약 G 테이블에서 A 테이블에 있는 특정 컬럼의 데이터를 가져와야 한다면 두 모델에는 어떤 차이가 있을까? 실제 SQL로 만들어 본다면 <리스트 1>과 같을 것이다.

<리스트 1> 코드테이블과 식별자 관계
--A의 경우
SELECT
A.UID_A
, G.UID_G
FROM G
INNER JOIN F ON G.UID_F = F.UID_F
INNER JOIN C ON F.UID_C = C.UID_C
INNER JOIN A ON C.UID_A = A.UID_A

--B의 경우
SELECT
A.UID_A
, G.UID_G
FROM G INNER JOIN A ON G.UID_A = A.UID_A

이 경우 두 모델에서 SQL의 실행 결과는 같다. 그러나 A 모델은 몇 번의 조인을 거쳐야만 비로소 A 테이블의 데이터를 조회할 수 있다. B의 경우는 단 한 번의 조인으로 원하는 데이터를 조회할 수 있다. A 모델은 조인한 만큼의 데이터를 처리해야 하고 원하는 데이터를 찾기 위해 많은 길을 거쳐야 한다. 데이터의 출처 또한 불명확해진다. 실제로 모델의 느낌은 A가 더 복잡함을 알 수 있다. 느낌이 오는가? 어떻게 설계하는지에 따라 성능에 많은 차이가 나고, 데이터의 출처가 아주 명확해 지고 SQL도 단순해진다.

그렇다면 B 모델이 더 좋은 모델이라고 말 할 수 있을까? B 모델은 관리해야 할 컬럼 수가 늘어나고 그 만큼의 추가적인 저장 공간도 더 필요하다. 설계의 기준은 데이터가 어디까지 접근할 필요성이 있는지에 대한 요구사항이 정의되어 있어야 한다. 만약 F 테이블이 A 테이블에 접근해야 하고, G 테이블은 A, C, F 모두 접근해야 하고, 나머지는 접근의 필요성이 없다면 모델은 <그림 2>와 같은 A, B 모델의 혼합된 형태가 될 것이다.

어떤 것이 더 유리한지 판단하는 것은 모델링 또는 설계하는 사람의 능력에 따라 달라진다. 필자도 ‘상황’이 주어지지 않는 이상 어떤 모델이 더 좋다고는 말할 수 없다. 주의해야 할 것은 최상위 테이블인 A가 매우 단순해야 한다는 것이다. 물리적으로 보면 UID_A가 INT형인 경우와 CHAR(50)인 경우는 관계를 맺을 때와 인덱스 크기에 영향을 끼치게 된다. 즉, 최상위 테이블이 가벼우면 가벼울수록 좋다는 것이다. UID_A가 CHAR(50)인 경우 C, F, G 테이블을 보면 각 행(Row)마다 CHAR(50) 만큼의 저장 공간이 필요하다는 것을 알 수 있다. 그래서 일반적으로 코드 테이블 같은 경우는 대리키(설계속성)를 사용하여 이러한 저장 공간과 인덱스의 크기를 줄여 성능과 함께 정보의 질을 높인다.

<그림 2> A, B 모델이 혼합된 형태

관계를 끊을 것인가? 유지 할 것인가?

얼 마 전 필자의 홈페이지에 ‘참조 무결성’ 즉, 관계를 맺는 것은 부하인가? 에 대한 질문이 올라왔다. 필자는 누군가 이러한 생각을 하는 사람이 있다는 자체가 매우 반가웠다. 예전에 여러 업체를 방문하여 관계를 왜 끊어 놓았냐고 하면, ‘개발할 때 데이터 넣기가 힘들어서...’ 라는 답변이 가장 많았다. 그러면서 관계형 데이터베이스에 대한 이야기를 하곤 한다. 더 우스운 것은 많은 시간과 비용을 투자하여 열심히 찾아낸 관계를 물리모델에서는 끊어놓고 데이터가 맞지 않는다고 외부 조인(Outer Join)을 쓰고 있다는 것이다. 이는 관계를 찾아내려고 들인 노력, 시간, 비용을 한 순간에 날려버리는 일이다. 또한 외부 조인으로 DBMS가 불리한 실행계획이 만들어 지기 때문에 성능도 저하된다. 이런 경우 SQL을 튜닝하고 인덱스를 생성하느라 애 먹을 필요가 전혀 없다. 단지 몇 개의 데이터가 맞지 않아서 외부 조인을 한다면 몇 개의 데이터를 찾아내어 수동으로 맞춰주고, 관계를 맺어 무결성을 보장하고, 내부 조인(Inner Join)으로 바꾸면 이 부분에 대한 성능 튜닝은 끝난다. 튜닝 작업을 한 근본적인 원인이 무엇인가? 관계가 맺어있지 않기 때문이다. 그렇다면 관계를 맺을 때와 맺지 않았을 때는 물리적으로 어떤 차이점이 있는지 살펴보자.

<그림 2>와 같이 물리적으로 구현이 되어 있다면 E 테이블에 한 건의 데이터를 입력하면 B 테이블에 데이터가 존재하는지 검사하게 된다. SQL만 본다면 단 한 줄의 문장이지만 DBMS 내부적으로는 B 테이블과 E 테이블을 조인하여 B 테이블에 데이터가 존재하는지 판단하게 된다. 이는 실행계획을 통하여 확인할 수 있다. 다음의 예를 보자.

<그림 3> 사원과 부서의 관계를 나타낸 ERD

<화면 1>과 같이 테이블이 만들어져 있다. 관계를 맺고 있을 때와 관계를 끊었을 경우 Emp 테이블에 데이터 1건을 입력하면 실행계획이 어떻게 만들어지는지 살펴보자.

<화면 1> 관계가 맺어 있는 경우의 실행계획

<화면 2> 관계가 맺어 있지 않은 경우의 실행계획

< 화면 2>를 보면 Dept 테이블과 조인하는 것이 보인다. 즉, 데이터 무결성 체크를 한 것이다. 반면에 <화면 3>에서는 무결성이 보장되지 않아 아무것도 없이 그냥 Emp 테이블에만 데이터를 입력한다. 이 둘의 차이는 성능 측면에서만 본다면 관계를 끊는 것이 도움이 된다. 하지만 우리가 여기서 생각해야 할 것은 성능이 최우선이 아니라는 것이다. ‘최적화’의 대상은 성능이 아니라 바로 ‘정보의 질’이다. 데이터의 무결성은 정보의 정확성과 관련된 것이므로 만약 DBMS에서 무결성이 보장될 수 없다면 애플리케이션에서라도 무결성을 보장하는 것이 옳다. 물론 일반적인 이야기이다. 만약 하나의 트랜잭션을 매우 짧게 실행하고, 사용자가 매우 많은 환경에서는 무결성 때문에 서비스에 영향을 준다면 관계를 끊는 것이 맞다. 물론 데이터 무결성은 어디서나 지켜져야 하며 정확성이 떨어질 수 있음을 감수할 수 있는 환경에서 일 것이다.

<리스트 2> 관계를 끊을 것인가? 유지 할 것인가?
CREATE TABLE dbo.Dept (
DeptNo int NOT NULL ,
DeptName varchar (50) NULL
) ON PRIMARY
GO

CREATE TABLE dbo.Emp (
EmpNo int NOT NULL ,
EmpName varchar (50) NULL ,
DeptNo int NULL
) ON PRIMARY
GO

ALTER TABLE dbo.Emp ADD
CONSTRAINT FK_Emp_Dept FOREIGN KEY
(
DeptNo
) REFERENCES dbo.Dept (
DeptNo
)
GO

--Dept 테이블 데이터 입력
INSERT INTO Dept VALUES(1, ''영업부'')
INSERT INTO Dept VALUES(2, ''총무부'')
GO

--관계가 맺어져 있을 경우
INSERT INTO Emp Values(101, ''이재학'', 1)
GO

--관계 삭제
ALTER TABLE dbo.Emp DROP CONSTRAINT FK_Emp_Dept
GO

--관계를 끊었을 경우
INSERT INTO Emp Values(102, ''박정연'', 1)
GO

개체집합의 정의에 따른 모델 변화

대 부분의 개체집합이나 관계의 정의는 설계단계에서 결정된다. 하지만 데이터베이스를 사용하는 과정에서 업무상황이 바뀌면 당연히 현실을 표현하고 있는 데이터 모델도 따라서 변화되어야 한다. 또한 개체집합을 어떻게 정의하느냐에 따라서도 모델이 변화한다.

<그림4> 수강 ERD

< 그림 4>의 ERD에서는 ‘교실’ 개체집합이 현실상 존재하는 건물(예: 이공관 501호)로 정의했다. 그러나 강의정책이 변해서 개설된 강좌가 가상공간에서 일어나는 ‘사이버강좌’가 되었다면 앞서 정의한 ‘교실’ 개체집합의 범위에서 벗어나 버린다. 다시 말해 ‘교실’ 개체집합의 개념 범위가 확장된 것이다. 그러면 명시적(Mandatory)이었던 관계는 임의(Optional)관계로 바뀐다.

<그림5> 임의(Optional) 관계

여기에서 임의(Optional)는 관계가 명확하지 않음을 의미한다. 그러므로 개체집합의 범위를 확장하여 ‘교실’ 개체집합의 정의에 ‘사이버강좌의 URL’ 을 포함한다면 자연스레 다시 명시적 관계가 형성된다.

명 시적 관계와 임의 관계는 정보의 단절 여부에 따라 적용된다. 즉, 임의 관계가 되면 물리적으로는 NULL 값을 가질 수 있다는 뜻이 된다. NULL 값은 ‘알 수 없는 값’ 이므로 개체 집합 간의 연결을 통하여 얻는 데이터에 대해서 NULL 값을 가지는 데이터는 일관성이 없어질 수도 있게 된다. 즉, 임의 관계의 경우 개설된 강좌는 이공관 501호에서 강의할 수도 있고, 강의를 어디서 받는지 몰라 학생들이 우왕좌왕 할 수도 있다는 뜻이 된다. 이러한 경우 모델러는 강한 책임감으로 문제점을 찾아내어 프로젝트 이해관계자들을 이끌어야 한다.

<그림6> URL 지정으로 임의 관계를 명시적 관계로 만들기

정규화의 이점

데 이터베이스 설계에서 빼놓을 수 없는 것이 바로 정규화이다. 정규화는 무결성을 보장하고 성능을 향상시키기 위해 사용할 수 있는 최고의 설계 솔루션이기 때문이다. 이 솔루션의 핵심은 ‘데이터의 중복을 없애는 것’이라고 할 수 있다. 데이터의 중복을 없애는 것은 데이터베이스에서는 매우 중요한 일이다. 데이터의 중복은 통합된 정보의 취득을 어렵게 만들고, 데이터베이스 시스템의 성능 저하되는 원인이 된다. 물론 인덱스를 제대로 활용하지 못한다거나 하드웨어에서의 병목 등 여러 가지 상황적인 요소도 성능 저하의 원인이 되겠지만 이런 사항들은 비교적 쉽게 개선할 수 있다. 하지만 설계상의 문제로 데이터가 중복되는 것은 SQL이 복잡해지고 성능 저하의 문제를 가져올 뿐 아니라 개선도 어려워진다는 점이 문제인 것이다.

왜 그럴까? 이쯤에서 우리는 기본적인 컴퓨터의 구조를 생각해야 한다. 기초 전산학에는 ‘폰 노이만’의 ‘프로그램 내장방식’이라는 말이 나온다. 기본적으로 CPU는 메인 메모리(RAM)에만 접근할 수 있다는 것이다. 그러므로 CPU가 처리하기 위해서는 데이터를 HDD에서 RAM으로 끌어 올려야만 한다. 이 과정에서 컴퓨터는 매우 큰 비용을 사용하게 된다. HDD가 매우 느린 하드웨어 자원이기 때문이다. 그렇다면 데이터의 중복이 있다고 생각을 해보자. HDD에서 RAM으로 데이터를 올려야 한다. 데이터가 중복되어 있다면 중복된 만큼 HDD를 읽으며 비용을 사용하는 횟수가 늘고 비좁은 RAM의 저장 공간도 더 많이 필요하게 된다. 또한 CPU는 데이터가 중복된 만큼 더 처리를 해야 하는 것이다. 이 과정에서 데이터베이스 시스템의 성능이 떨어지는 것은 물론이고 정보의 질과 궁극적으로는 사용자의 정보 욕구를 만족시키지 못하는 결과를 초래하게 되는 것이다.

비정규화와 통합

비 정규화 테이블은 일반적으로 컬럼의 개수가 많다. 컬럼의 개수가 많다는 이야기는 데이터 행의 크기가 크다는 뜻도 된다. 앞서 살펴본 바와 같이 하나의 데이터 행의 크기가 커지면 최소 입출력 단위에 데이터 행이 많이 들어가지 못한다. 심한 경우 입출력 동작이 늘어나고 잘못하면 데이터 행이 각각의 페이지에 존재할 수도 있다. 비정규화의 문제점에 대해 좀 더 자세히 알아보기 위해 <리스트 3>과 같은 테이블이 만들었다고 가정하자.

<리스트 3> 차량관리비내역 테이블
CREATE TABLE 차량관리비내역(
차량번호 VARCHAR(10)
, 관리부서 NUMBER
, 연료 NUMBER
, 주차비 NUMBER
, 세차비 NUMBER
, 윤활유 NUMBER
, 부품대 NUMBER
, 수리비 NUMBER
, 타이어교환비 NUMBER
, 자동차세 NUMBER
, 환경세 NUMBER
, 보험료 NUMBER)

잘 보면 이와 같은 경우는 명백한 1차 정규화 위배라는 사실을 알 수 있다. 연료, 주차비, 세차비, 윤활유 등은 ‘차량관리비’ 정도로 만들고, ‘구분’ 컬럼을 만들면 된다. 즉, ‘차량관리비’는 다중 값 속성이 되어 다른 테이블로 떨어져 나가게 된다. 또한 여러 컬럼을 통해 다른 개체집합이 있음을 알 수 있다. 그래서 <그림 7>과 같은 논리 ERD가 그려질 것이다.

<그림 7> 차량관리비내역의 논리 ERD

각 각의 테이블에 할당 받은 저장 공간을 효율적으로 사용하려면 테이블을 최대한 좁게 설계해야 한다. 좁게 설계한 데이터 행이어야만 최소 입출력 단위에 꽉꽉 채울 수 있기 때문이다. 이 과정에서 입출력단위가 줄어드는 것 뿐 아니라 여러 가지 이상 현상이 발생하는 것도 미연에 방지하는 효과를 함께 얻을 수 있다.
차량관리비내역에서 ‘검사비’가 추가된다고 가정하자. 비정규화된 경우는 테이블을 변경해야 새로운 컬럼을 추가할 수 있다. 그러나 <그림 7>과 같은 경우 비용과 구분을 추가하는 것으로 검사비 항목을 해결할 수 있다. 최소한의 변경만으로 유연성이 확보될 수 있는 것이다. 또한 인덱스와 같은 추가적인 물리적 객체의 생성도 막을 수 있다. 이것은 관리해야 할 객체의 수가 줄어드는 것을 의미한다.

실제로 가로로 놓느냐 세로로 놓느냐는 업무에 따라 달라질 수 있다. 성능이 매우 중요한 경우에는 세로 보다는 가로로 놓는 편이 효과적일 때가 있다. 세로로 놓는 다는 것은 통합을 의미하며 대부분은 통합이 유리하다. 가로로 펼쳐 놓다 보면 인덱스를 유지하는 비용이 더 커지기 때문이다. 비슷한 예로 다음과 같은 스키마를 들 수 있다.

● 서적(ISBN, 도서명, 페이지수, 저자1, 저자2, 저자3)
● 회원(회원ID, 회원명, 전화번호, 가입일시 계약일시, 결제일시, 발송일시, 방문일시)

<리스트 4> 회원정보 조회
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
가입일시 = ''20060505''
OR 계약일시 = ''20060505''
OR 결제일시 = ''20060505''
OR 발송일시 = ''20060505''
OR 방문일시 = ''20060505''

먼 저 서적의 스키마를 보자. 무엇이 문제인가? 저자1, 저자2, 저자3이 문제이다. 관계형 모델의 주요 원칙 중에 하나인 ‘컬럼의 원자 값’을 위반했기 때문이다. 컬럼의 원자 값이란 하나의 값이 아니라 단 하나의 의미를 가져야 한다는 것으로 보아야 한다. 저자1, 저자2, 저자3과 같이 표현된 것은 ‘저자’의 의미와 ‘순서’의 의미가 있다. 즉, 배열과 같은 의미로 쓰인 것이다. 물론 물리적인 구현은 DBMS의 배열형 컬럼에 대한 지원여부도 크다. 하지만 논리 모델은 논리 모델일 뿐 물리적인 것과는 독립성을 가져야 한다는 것이 원칙이다. 또한 두 모델에 추가 요구사항이 발생하면 어쩔 수 없이 테이블을 변경해야 한다. 서적의 경우 저자 1명이 늘어나면 ‘저자4’ 컬럼이 추가되어야 하며, 회원의 경우도 마찬가지로 ‘탈퇴일시’가 필요하다면 역시 컬럼이 추가되어야 하는 문제가 발생한다.

회원 스키마의 경우는 어떠한가? 서적과는 조금 차이를 보일 것이다. 서적의 경우는 단순히 다중 값 속성을 컬럼으로 늘어뜨린 경우이다. 하지만 회원의 경우에는 이렇게 하기가 어렵다. 회원의 경우에는 <리스트 4>와 같은 SQL을 요구할 수 있다.

 <리스트 4>처럼 회원을 관리하는 경우 회원이 천만 명이라면 어떤 요구가 발생했을 때 천만 건의 데이터를 모두 검색해야 원 하는 결과를 얻을 수 있는 셈이 된다. 그렇다면 인덱스는 어떨 까? 가입일시, 계약일시 등 모든 컬럼에 인덱스를 생성하는 것 역시 쉽지 않은 일이다. 이렇게 많은 데이터를 처리해야 할 때에 는 데이터를 통합하는 것이 가장 좋은 방법이다. <그림 8>과 같은 테이블을 구성하면 SQL이 어떻게 바뀌는지 살펴보자.

<그림 8> 회원 정보 테이블

<리스트 5> 회원 정보 테이블의 SQL
SELECT
회원ID
, 회원명
, 전화번호
FROM 회원
WHERE
발생일시 = ''20060505''
AND 발생구분 IN (''가입'', ''계약'', ''결제'', ''발송'', ''방문'')

  얼핏 보아도 SQL이 상당히 간단해 졌다는 것을 확인할 수 있 다. 인덱스는‘발생일시 + 발생구분’으로 결합 인덱스를 생성하 면 추가 요구사항이 발생해도 쉽게 원하는 결과를 가져올 수 있 다. 즉, ‘탈퇴일시’라는 추가적인 요구사항이 발생하면‘발생구 분’컬럼 값의 도메인에‘탈퇴’가 추가되면 된다. 이는 테이블이 변하는 것이 아니라 값이 변하는 것이기 때문에 테이블 구조는 전혀 변할 필요가 없다. 여기서 놓치지 말아야 할 문제가 있는데 바로 <그림 8>의‘회 원’이라는 명칭이다. 이는 회원이 어떤 행위를 했을 때 하나의 개 체가 생성되는 행위개체집합이다. 그러므로‘회원’은 아닐 것이 다. 최종적으로는 관계의 통합에 의해 발생된 형태로 볼 수 있다. 쉽게 생각해보면 발생구분 한 개의 개체가 생성될 때마다 회원명, 전화번호가 중복되어 저장된다. <그림 8>에서 회원ID가 yasicom인 회원이 탈퇴 했다고 가정하여 데이터를 삽입하면 중 복되는 것을 알 수 있다. 그러므로 이 스키마는 정규화 대상이다. 현재까지 살펴 본 것으로만 판단하면 처음에 소개했던 옆으로 늘어뜨린 스키마구조는 여러 가지로 불리하다. 하지만 추가적인 요구사항이 발생하지 않고, 앞서 예로 든 SQL과 같이 요구가 없 이 하나의 행만 접근하는 경우라면 옆으로 늘어뜨린 비정규화가 더 유리하다. <그림 7>과 같은 경우는‘발생구분’컬럼이 추가됨 으로서 저장 공간이 늘어나는 단점이 있다. 어떤 것이 유리한지 는 현재와 미래의 상황을 고려한 종합적인 판단이 요구된다.

인덱스 종류와 선택

인 덱스는 데이터베이스 물리 설계에서 매우 중요한 위치를 차지한다. 같은 데이터베이스라 하더라도 인덱스 활용 전략에 따라서 매우 많은 성능의 차이를 보이기 때문이다. 여기에서는 인덱스의 종류와 어떤 경우에 어떤 인덱스를 적용해야 하는지 알아보도록 한다.
일반적인 RDBMS에서의 인덱스는 다음과 같이 두 가지 종류로 나뉜다.

● 스파스 인덱스(Sparse Index) : 해당 레코드 존재 페이지를 가리키는 포인터를 저장하는 인덱스
● 덴스 인덱스(Dense Index) : 해당 레코드를 가리키는 포인터를 저장하는 인덱스
* 힙(Heap) : 데이터가 입력되는 순서에 따라 정렬되어 쌓이는 테이블 구조

페 이지에는 여러 개의 레코드가 있으므로 일반적인 경우는 스파스 인덱스가 유리하다. 하지만 레코드의 길이가 페이지의 크기와 비슷한 경우라면 스파스 인덱스는 덴스 구조보다 디스크 액세스를 많이 수행하게 되므로 성능이 떨어진다. 물론 늘 그런 것은 아니며 DBMS가 어떻게 구성되었느냐에 따라 다른 결과가 나타날 수도 있다. 만약 해당 DBMS에서 데이터 행 체인(Row Chain)이 발생된다면 불리할 수 있으나 체인을 지원하지 않고, 행 마이그레이션(Row Migration)만 지원한다면 큰 차이가 없다. 어떤 DBMS는 최소 입/출력 단위에 데이터를 꽉꽉 채울 수 있는 반면, 어떤 DBMS는 데이터를 모두 채울 수 없기 때문에 두 인덱스 방식의 장/단점 비교는 조건이 잘 주어져야 가능하다.

<그림 9> 스파스 인덱스와 덴스 인덱스

상 용 DBMS는 <표 1>과 같이 다양한 종류의 인덱스를 지원한다. 오라클에서는 인덱스 조직 테이블(Indexed-Organized Table)이 지원되는데 이는 MS SQL 서버의 클러스터드 인덱스를 가진 테이블과 비슷한 구조로 생각하면 되며 스파스 인덱스 방식이라고 생각해도 무방하다.

<그림10> 힙(Heap)에서의 트랜잭션 집중

또 한 가지 중요한 사실은 스파스 인덱스와 덴스 인덱스가 혼재되어 있을 경우 나누어 저장하는 것이 효과적이라는 점이다.
데 이터베이스 시스템과 떼어놓을 수 없는 중요한 사항이 바로 트랜잭션이다. 작은 트랜잭션 하나가 잠금을 얻으면 잠금 수준에 따라서 많은 양의 트랜잭션이 대기할 수도 있다. 힙과 같은 구조는 데이터가 들어오는 순서대로 쌓이기 때문에 특정 페이지(최소 입/출력 단위) 잠금이 걸린다면 해당 페이지에 접근하고 있는 트랜잭션은 모두 대기 상태가 된다. 이러한 경우 레코드 단위의 잠금을 걸면 해결되지만, 레코드의 입력이 너무 빈번하게 발생한다면 결국 병목현상이 발생할 수밖에 없다. 그러므로 MS SQL Server와 같은 경우에는 테이블에 클러스터드 인덱스(Sparse Index)를 생성하기를 권한다.
하지만 클러스터드 인덱스는 추가(Insert), 갱신(Update), 삭제(Delete)할 때마다 데이터를 재정렬해야 하기 때문에 부하가 많이 걸린다. 범위 연산이나 한 번에 많은 데이터를 가져와야 하는 경우라면 클러스터드 인덱스를 생성해야 한다. 일반적인 OLTP시스템이라면 클러스터드 인덱스는 권장사항이다. 그러나 특정 행 단위 위주로 접근하는 경우라면 클러스터드 인덱스는 사용하지 않는 편이 좋다. 조회 성능이 중요한 클러스터드 인덱스 생성은 적극 고려해야 할 대상이다.
만약 시간이 된다면 주로 사용하는 인덱스의 순서에 맞추어 테이블의 데이터를 다시 넣어두면 성능을 높이는데 도움이 된다. 또한 추가, 갱신, 삭제 시 인덱스도 같이 변경되어야 하므로 MS SQL Server는 PAD_INDEX, FILLFACTOR 옵션을 인덱스 생성, 변경 시 제공하고 있다. 이 두 옵션은 인덱스의 저장 공간을 비워두어 추가적인 작업(Insert 등)에 대해 인덱스의 재조정을 막는 역할을 한다.

테이블의 수평 분할/수직 분할

커 뮤니티의 질문 게시판에 가보면 테이블의 분할에 대한 질문이 자주 올라오곤 한다. 또한 데이터가 늘어남에 따라 성능이 느려졌다고 판단한 관리자들이 테이블의 분할을 고려하기도 한다. 실제로 이런 이유 때문에 많은 사이트에서 테이블을 이미 분할하여 사용하고 있다. 하지만, 테이블을 분할해서 사용하는 이유에 대해 구체적인 판단 근거를 제시하지 못하는 경우가 대부분이다. DBMS는 총 행의 수가 백건 중 1건을 가져오는 것이나 천만 건 중 1건을 가져오는 것이나 비슷한 시간을 가지도록 설계된다. 이러한 설계는 위에서 살펴본 인덱스에 기반을 두고 있다. 천만 건 중 1건이나 백건 중 1건이나 처리하는 시간이 비슷하다면 왜 테이블 분할을 고려해야 하는 걸까? <그림 11>을 보자.

<그림11> 컬럼별로 서로 다른 트랜잭션 처리 형태

테 이블에서 회원ID, 패스워드, 회원명, 전화번호에 대해서 집중적인 범위 조회가 일어나고, 접속일시는 집중적인 갱신 트랜잭션이 발생한다고 가정해 보자. 만약 회원ID가 yasicom인 회원의 접속일시를 ‘20060505’로 갱신한다면 해당 행에 대해 잠금(Lock)이 걸리게 된다. 그러면 yasicom을 포함하는 범위조회는 잠금이 풀릴 때까지 대기를 해야 한다. 또한 읽는 동안의 일관성을 보장해야 하므로 갱신 트랜잭션도 대기해야 하는 상황이 발생할 수 있다. 이러한 경우라면 성능에 매우 큰 지장을 주기 때문에 수직분할을 하는 것이 바람직하다. <그림 12>는 수직분할의 예이다.

<그림12> 회원 테이블의 수직분할

일 반적으로 수평분할은 데이터가 1억 건을 넘을 경우에나 고려해 볼 만 하다. 물론 컬럼의 수가 많다면 행의 수가 더 적더라도 수평 분할을 고려해야 할 것이다. 데이터베이스가 대용량이라면 고려해야 할 사항들이 매우 많아진다. 예를 들면 인덱스를 다시 생성하는데 걸리는 시간이 길어지고 병렬처리 및 백업/복원 전략도 달라진다. 또, 트랜잭션에 대해서도 고려해야 한다. MS SQL Server의 경우 행에 4,861개 이상의 잠금이 걸리면 잠금의 수준이 높아지고, 다른 트랜잭션은 대기(블록킹)하게 된다. 실제로 잠금은 데이터의 일관성을 보장하기 위해 꼭 필요한 것이다. 대기하는 것 자체가 문제는 아니지만 그로 인해 작업이 큐(Queue)에 쌓이게 되어, 잠금이 풀릴 때 한 번에 많은 양의 부하가 걸리는 원인이 되기도 한다.

문제는 개발자들이 테이블의 분할을 너무 쉽게 생각한다는 데 있다. 테이블을 분할하면 처리과정이 복잡해지는 것은 당연하다. 또한 중복된 데이터를 버려야 하는지 중복된 데이터를 가지고 있어야 하는지에 대한 고려도 필요하다. 분할을 할 때는 반드시 타당한 근거를 제시해야 하며, 단순히 빨라질 것이라는 기대만으로 수평분할을 하게 되면 많은 사람이 고생하게 되므로 충분히 고려를 하는 것이 좋다.

대부분의 경우 데이터베이스 튜닝은 고급기술을 필요로 하지는 않는다. 데이터베이스 문제의 대략 70% 이상은 기본적인 사항을 지키지 않기 때문에 발생한다. 무슨 뜻인지도 모르고 기본이 받쳐주지 않는 상황에서 경험에 의존하는 것도 문제다. 어떤 주먹구구의 선두주자는 사용자 화면마다 테이블을 생성하기도 한다. 테이블 명칭도 ‘매입입력’과 같이 전형적인 프로세스에 의존적인 테이블을 만들고 있다. 현실을 반영하기는커녕 전혀 근거도 없는 자신만의 세계를 구축하기도 한다. 예전에 이렇게 했었는데도 잘만 돌아가더라는 것이 그 이유이다. 그리고는 하드웨어나 DBMS에 핑계를 떠넘기는 것은 쉽게 마주할 수 있는 일들이다.

데이터베이스에서 어떤 결정을 내리기 위해서는 반드시 합당한 근거를 제시하여 올바른 방향을 설정해야 한다. 어떠한 시스템이든지 특정 한 부분이 잘 구성되거나 잘 개발되었다고 하여 시스템의 성능이 향상되는 것은 아니다. 개발 초기부터 명확히 정의하고, 각각의 단계를 차곡차곡 밟아나가며, 물리적인 구현에서는 주어진 시스템의 자원을 최소한으로 사용하여 최대한의 성능을 발휘할 수 있도록 노력해야 한다. 데이터베이스의 설계와 튜닝에 있어서는 어느 하나도 무시할 수가 없다. 아무리 애플리케이션 튜닝이 잘 되었다고 해도 하드웨어가 받쳐주지 못하면 성능을 제대로 발휘할 수 없다. 튜닝에 답은 없다. 어떠한 경우든지 상대적이다.

사고방식을 바꾸는 것은 매우 중요하다. 그러므로 대부분의 튜닝서적은 계몽서의 느낌이 든다. 다들 왜 그렇게 잔소리가 많은지를 알아야 한다. 필자도 중간 중간에 잔소리를 했을 것이다. 눈에 거슬렸다면 필자의 순수한 마음에서 우러난 조언이었다고 생각해주기 바란다.

SQL 문을 튜닝하는 경우 특정 부분만 살짝 바꾸어 주어도 성능이 월등히 향상되는 경우가 많이 있는데 원래 그렇게 해야 할 것을 고친 것이므로 좋아할 필요는 없다. 주어진 시스템의 자원을 최대한 활용하게 하는 것이 튜닝의 목적이다. 궁극적으로 추구해야 할 것은 우리가 개발한 시스템에 사용자가 불만을 가지지 않도록 하는 것이 목적이다. 여기에 나오는 내용들은 모두 사용자의 정보욕구를 만족시키기 위한 것일 뿐이다. 사용자의 정보욕구를 만족시키고 사용자로부터 참 유용한 정보가 많이 나오는 시스템이란 소리를 들었을 때. 바로 그때가 우리가 기뻐해야 할 순간이다.

참고자료

1. 실무 예제 중심의 고급 SQL 서버 개발자 가이드， 피어슨에듀케이션코리아，Ken Henderson
2. Microsoft SQL Server 2000 성능튜닝 Technical Reference, 정보문화사，Steve Adrien Deluca외 3. 강력한 SQL 프로그래밍 을 위 한건nsiqht, Ken Henderson
4. 대용량 데이터베이스 솔루션, 대청，이화식，조광원
5. 데이터 아키텍처, 대청，이화식
6. 데이터 아키텍춰, 형설, 문송천
7. 데이터베이스 처리론，교보，나연묵외
8. 데이터베이스 시스템，인터비전，김형주
9. 데이터베이스 시스템，정익사，이석호
10. 데이터베이스 설계，홍릉과학출판사，조규익
11. 데이터베이스 설계와 구축，한빛미디어，이춘식
12. 데이타베이스 설계，홍릉과학출판사，박석
13. 정보공학 1, 2, 영한출판사，제임스마틴
14. 데이터베이스 관리，박문각，서길수
15. Database Tuning, 브레인코리아, 최용락외

제공 : DB포탈사이트 DBguide.net

출처명: 마이크로 소프트웨어 -[2006년 6월호]