'SQL' 카테고리의 글 목록 (3 Page)

'SQL'에 해당되는 글 52건

2005.05.13 [펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용
2005.03.02 [펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자
2005.02.16 mysql 4.0.x 이하 버전 .. 한글셋팅..
2005.02.02 [mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용
2005.01.14 [펌] MySQL의 SQL
2005.01.14 STOP WORD 2
2004.10.27 [MySQL] 다수의 테이블에서 데이터 동시 삭제하기
2004.09.22 [펌] [오라클] 인덱스
2004.09.16 [mysql] ifnull
2004.09.02 오라클 설치 잘 나온 사이트
2004.07.06 replication 사용하기.. version - 4.0.17
2004.06.30 [펌] 데이터베이스 관리

[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용

SQL 2005. 5. 13. 10:50

데이터 모델 정규화/반정규화의 실전 프로젝트 적용

이춘식

정규화를 잘 이해하여 데이터 모델링을 해야 하는 프로젝트 모델러가 이를 정확하게 이해하지 못하는 경우가 종종 있다. 검증되어 있고 체계화된 이론적 기반 위에 데이터베이스라는 기초를 건축하지 않으면 그 데이터베이스는 모래 위에 세운 집처럼 금방 무너지고 말 것이다. 정규화의 이론은 건축물의 기초공사를 해야 하는 사상에 해당한다. 그저 어렴풋이, 알듯 모를듯 희미한 기억의 지식으로 튼튼하고 견고한 데이터 모델을 만들어 낼 수 없다.

“붕어빵에 붕어가 없다!”고 한다. 데이터 모델링을 학교나 학원에서 배운 사람이나 시스템 구축 프로젝트에서 데이터 모델링을 경험한 사람치고 정규화에 대한 이야기를 듣거나 이야기하지 않은 사람은 없을 것이다. 그만큼 정규화의 이론은 데이터를 분석하여 데이터 모델로 만들고 그것을 다시 데이터베이스화하는 이론의 뿌리가 되는 중요한 것이다. 그러나 붕어빵에 붕어가 없듯이 정규화에 대한 언급은 누구나 하지만 정규화에 대한 내용을 정확하게 이해하고 실전에 적용할 수 있는 사람은 의외로 극히 드물다는 사실을 즉시해야 하고 사태의 심각성을 인식할 필요가 있다.
정규화에 관련된 이론을 배운다고 하면 대부분 과목, 수강신청, 교수 등 항상 정해진 샘플 사례에 표시 방법도 과목코드->과목명과 같이 설명되어 실전 프로젝트에서 사용하는 표기법(notation)과 동 떨어져 있다. 따라서 학습할 때는 개념적으로 이해한다고 하더라도 혹은 적어도 시험문제가 출제되면 100점은 맞는다고 하더라도, 실전 프로젝트에서는 무엇을 어떻게 왜 그렇게 해야 하는지 도무지 이해하지 못하는 경우가 대부분이다. 잘 정리된 이론은 실세계에서 응용되어 다른 창조물을 도출할 수 있을 때 비로소 지식가치의 효용이 있다. 하지만 불행히도 정규화의 이론은 그 내용이 너무 훌륭함에도 불구하고 실전에 반영하는 방법을 정확하게 알지 못해 그의 가치를 제대로 적용하지 못하는 경우가 자주 나타나는 것이다. 이제 우리의 눈을 희미하게 하는 이론의 틀을 깨고 개념을 명확하게 하여 실전에서 곧 바로 적용할 수 있는 참된 지식가치로 정규화의 이론을 활용해 보자.

정규화 규칙은 어디에 쓰는 물건인가?

“그런데 실전 프로젝트에서는 정규화를 적용한 적이 없습니다!”라고 반문하는 독자도 있을 것이다. 맞는 이야기이다. 프로젝트에서는 정규화라고 하는 태스크(task)로 일을 진행하지는 않는다. 다만, 프로젝트에서 데이터 모델링을 할 때는 논리 모델->물리 모델 2단계로 수행하거나 개념 모델->논리 모델->물리 모델 3단계 또는 객체지향 분석설계에서는 클래스 다이어그램->OR(객체-관계형) 맵핑->물리 모델로 하거나 업무가 익숙하고 시스템의 규모가 작은 경우 곧 바로 물리적인 데이터 모델링을 하는 경우로 진행한다. “그렇다면 데이터베이스에서 그렇게 중요하다고 하는 정규화 방법은 활용되지 않는가?”라고 반문할 수 있다.

정규화 규칙은 실제 프로젝트에서 두 가지 성격으로 중요하게 반영이 된다.

첫 번째는 엔티티 타입을 오브젝트 분석 방법에 의해 도출할지라도 분석 방법의 배경에는 이미 중복 제거 및 주식별자에 의한 종속과 속성에 의한 종속 등 제3정규화 규칙이 모델링 작업의 기초에 관여한다고 봐도 된다. 즉 숙련된 데이터 모델러는 이미 정규화에 대한 개념이 확보된 상태에서 각각의 오브젝트를 엔티티 타입으로 선정하며 새로운 엔티티 타입으로 분리될 때도 각 속성의 집합 개념과 종속성의 개념을 적용하여 분리시켜 나간다.
두 번째는 정규화 방법을 프로젝트에서 적절하게 활용하기 위해서는 오브젝트별로 엔티티 타입을 분석해가면서 각각의 오브젝트가 적절하게 도출이 되었는지 또는 더 분리되어야 해야 하는지를 정규화 규칙에 대입하며 검증하는 것이다. 또한 단계별로 작업이 수행된 이후에 정규화 규칙에 의해 모든 엔티티 타입에 대해서 검증하는 작업이 필요하고 이상이 있는 경우에는 정규화 규칙을 적용하여 엔티티 타입을 정제해 나가도록 한다.

정규화의 의미

그러면, 데이터 모델링에서 정규화는 무엇을 의미하는가? 1970년 6월 E.F Code 박사는 ‘대규모 데이터 저장을 위한 관계형 데이터 모델(A Relational Model of Data for Large Shared Databanks)’이라는 연구에서 새로운 관계형 모델을 발표했다. 수학자인 Code 박사에 의해 제안된 정규화의 이론은 실세계에서 발생하는 데이터들을 수학적인 방법에 의해 구조화시켜 체계적으로 데이터를 관리할 수 있도록 하였다. 처음에는 1차 정규화, 2차 정규화, 3차 정규화가 제시되었으나 이후에 보이스-코드 정규화가 제시되었고, 이후 4차 정규화, 5차 정규화의 이론이 발표되었다.
정규화(normalization)란 다양한 유형의 데이터 값 검사를 통해 데이터 모델을 더 구조화시키고 개선시켜 나가는 절차에 관련된 이론이다. 정규화가 프로세스를 나타내는 의미라면 정규형(normalform)은 정규화가 완성된 이후의 엔티티 타입(테이블)을 지칭하는 용어이다. 정규화를 이해하기 위해서는 이론적인 기반이 되는 함수 종속성을 이해할 필요가 있다. 함수의 종속성(functional dependency)은 데이터들이 어떤 기준 값에 의해 종속되는 현상을 지칭하는 것이다. 이 때 기준 값을 결정자(determinant)라 하고 종속되는 값을 종속자/의존자(dependent)라고 한다.

<그림 1> 함수의 종속성

<그림 1>을 보면 사람이라는 엔티티 타입에는 주민등록번호, 이름, 출생지, 호주라는 속성이 존재한다. 여기에서 이름, 출생지, 호주라는 속성은 주민등록번호 속성에 종속된다. 만약 어떤 사람의 주민등록번호가 신고되면 그 사람의 이름, 출생지, 호주가 생성되어 단지 하나의 값만을 가지게 된다. 이를 기호로 표시하면 다음과 같다.

주민등록번호 -> (이름, 출생지, 호주)

즉 ‘주민등록번호가 이름, 출생지, 호주를 함수적으로 결정한다’라고 말할 수 있다. 실세계의 데이터들은 대부분 이러한 함수 종속성을 가지고 있다. 함수의 종속성은 데이터가 가지고 있는 근본적인 속성으로 인식되고 있다. 정규화의 궁극적인 목적은 반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게(프로세스에 의해 데이터의 정합성이 지켜질 수 있어야 함) 배치되도록 하는 것이므로 이 함수의 종속성을 이용하여 정규화 작업이나 각 오브젝트에 속성을 배치하는 작업을 한다.

• 정규화는 적절한 엔티티 타입에 각각의 속성들을 배치하고 엔티티 타입을 충분히 도출해가는 단계적인 분석 방법이다.• 정규화 기술은 엔티티 타입에 속성들이 상호 종속적인 관계를 갖는 것을 배경으로 종속 관계를 이용하여 엔티티 타입을 정제하는 방법이다.• 각각의 속성들이 데이터 모델에 포함될 수 있는 정규화의 원리를 이용하여 데이터를 분석하는 방법에서 활용될 수 있다.• 정규화는 현재 데이터를 검증할 수 있고 엔티티 타입을 데이터가 표현하는 관점에서 정의하는데 이용할 수 있다.• 정규화는 엔티티 타입을 분석하는 관점이 오브젝트별 분석하는 방법이 아닌 개별 데이터를 이용한 수학적인 접근방법을 통해 분석하는 방법이다.

정규화에 대한 실전 프로젝트 적용 사례

<표 1>은 1차 정규화, 2차 정규화, 3차 정규화와 보이스-코드정규화 그리고 4차와 5차 정규화에 대한 정리이다. 정규화의 정의를 이용하여 실전 프로젝트에서는 어떻게 적용할 수 있는지 살펴보자.

<표 1> 정규화에 대한 정리

정규화	정규화 내용
1차 정규화	복수의 속성 값을 갖는 속성을 분리
2차 정규화	주식별자에 종속적이지 않은 속성의 분리 부분 종속 속성을 분리
3차 정규화	속성에 종속적인 속성의 분리 이전 종속 속성의 분리
보이스-코드 정규화	다수의 주식별자 분리
4차 정규화	다가 종속 속성 분리
5차 정규화	결합 종속일 경우는 두 개 이상의 N개로 분리

1차 정규화(복수의 속성 값을 갖는 속성의 분리)

1차 정규화(first normalization)는 복수의 속성 값을 가진 속성을 분리한다. 즉 테이블 하나의 컬럼에는 여러 개의 데이터 값이 중복되어 나타나지 않아야 한다는 것이다. 이는 각 속성에 값이 반복 집단이 없는 원자 값(atomic value)으로만 구성되어 있어야 한다는 것을 의미한다.
이를 다시 정의하면, “모든 엔티티 타입의 속성에는 하나의 속성 값만을 가지고 있어야 하며 반복되는 속성 값의 집단은 별도의 엔티티 타입으로 분리한다”로 정의할 수 있다. 이 때 전제조건은 결정자에 의존하는 의존자의 반복성을 나타낸다. 실전 프로젝트에서 나타나는 데이터 모델의 표기법을 이용한 사례를 보도록 하자.

1차 정규화 사례 1
‘한 번의 주문에 여러 개의 제품을 주문한다’는 업무 규칙이 있는데 <그림 2>의 왼쪽 편과 같이 데이터 모델링을 했다고 가정해 보자. 왼쪽의 엔티티 타입은 하나의 주문에 여러 개의 제품이 존재하므로 주문번호, 주문일자, 배송요청일자의 동일한 속성 값이 주문한 제품의 수만큼 반복해서 저장될 것이다. 따라서 오른쪽과 같이 1차 정규화를 적용하여 중복속성 값을 제거한다.

<그림 2> 1차 정규화의 응용 1

이 사례의 특징은 주문의 PK(Primary Key)인 주문번호가 중복 속성 값을 가지기 때문에 PK를 가진 데이터베이스 테이블 생성이 불가능하다는 특징이 있다.

1차 정규화 사례 2
로우(Row) 단위로 1차 정규화가 안 된 모델은 PK의 유일성이 확보되지 않으므로 인해 실전 프로젝트에서는 거의 찾아보기가 힘들다. 반면 로우 단위로 중복된 내용을 컬럼 단위로 펼쳐 중복하는 경우가 아주 많이 발견된다. 1차 정규화의 응용이 된 형태로 볼 수 있다. 계층형 데이터베이스에서 이와 같은 형식의 모델링을 많이 했는데 관계형 데이터베이스에서도 이러한 형식으로 모델링을 진행하는 경우가 많이 발견된다.

<그림 3> 1차 정규화의 응용 2

<그림 3>의 모델을 보면 왼쪽 모델의 일재고 엔티티 타입에는 3개월 분에 대한 장기재고 수량, 주문수량, 금액, 주문금액이 차례대로 기술되어 있다. 이렇게 되면 장기재고 관리가 4개월 이상으로 늘어날 때 모델을 변경해야 하는 치명적이 결함이 있다. 따라서 오른쪽과 같이 1차 정규화를 통해 모델을 분리함으로써 업무 변형에 따른 데이터 모델의 확장성을 확보하도록 해야 한다.

2차 정규화(주식별자에 종속적이지 않은 속성의 분리)

1차 정규화를 진행했지만 속성 중에 주식별자에 종속적이지 않고 주식별자를 구성하는 속성의 일부에 종속적인 속성인, 부분종속 속성(PARTIAL DEPENDENCY ATTRIBUTE) 을 분리하는 것이 2차 정규화(SECOND NORMALIZATION)이다. 2차 정규화는 반드시 자신의 테이블을 주식별자를 구성하는 속성이 복합 식별자일 경우에만 대상이 되고 단일 식별자일 경우에는 2차 정규화 대상이 아니다.

2차 정규화 사례
여러 개의 속성이 주식별자로 구성되어 있을 때 일반속성 중에서 주식별자에 일부에만 종속적인 속성이 있을 경우 2차 정규화를 적용하여 엔티티 타입을 분리하도록 한다.

<그림 4> 2차 정규화 응용

<그림 4>의 모델은 고객번호에 종속적이지 않은 속성들을 분리하여 고객점포라는 새로운 엔티티 타입을 생성하였다. 실전 프로젝트에서는 코드 유형의 엔티티 타입들이 2차 정규화가 되지 않고 하나의 엔티티 타입으로 표현되는 경우가 많이 발견된다. 이 모델에서 함수종속 관계 표기법으로 표기하자면 고객번호 -> (고객명)으로 표시하여 별도의 엔티티 타입으로 분리할 수 있다.

3차 정규화(속성에 종속적인 속성 분리)

3차 정규화(third normalization)는 속성에 종속적인 속성을 분리하는 것이다. 즉 1차 정규화나 2차 정규화를 통해 분리된 테이블에서 속성 중 주식별자에 의해 종속적인 속성 중에서 다시 속성 간에 종속 관계가 발생되는 경우에 3차 정규화를 진행한다.
3차 정규화의 대상이 되는 속성들을 이전 종속(transitive dependence) 관계 속성이라고 한다. 이것은 곧 주식별자에 의해 종속적인 속성 중에서 다시 다른 속성을 결정하는 결정자가 존재하여 다른 속성이 이 결정자 속성에 종속적인 관계를 나타낸다.

3차 정규화 실전 적용
결정자 역할을 하는 일반 속성이 존재하고, 결정자 역할 속성에 의존하는 의존자가 존재하는 엔티티 타입은 3차 정규화의 대상이 된다.

<그림 5> 3차 정규화 응용

<그림 5>의 모델은 고객 엔티티 타입에 등록카드에 대한 정보가 포함되어 있는 모습이다. 등록카드번호가 결정자 역할을 하고 있고 등록카드사명과 등록카드유효일자가 의존자 역할을 하는 속성 간의 종속적인 속성이 발견되었으므로 3차 정규화의 대상이 되는 모델이다. 따라서 등록카드에 대한 내용에 대해 별도의 엔티티 타입을 도출한 오른쪽 모델로 만듦으로서 3차 정규화를 완성하였다. 실전 프로젝트에서는 1:1관계의 엔티티 타입이 하나로 통합이 되었거나 업무분석 과정에서 하나의 엔티티 타입에 많은 속성이 포함되어 있을 때 3차 정규화의 대상이 되는 경우가 많이 나타난다. 이 모델에서 함수종속 관계 표기법으로 표기하자면 등록카드번호 -> (등록카드사명, 등록카드유효일자)으로 표시하여 별도의 엔티티 타입으로 분리할 수 있다.

보이스-코드 정규화

1차 정규화, 2차 정규화, 3차 정규화는 모두 하나의 주식별자를 가졌을 때를 가정하여 진행하였다. 만약 하나의 테이블에 여러 개의 식별자가 존재하면 비록 1, 2, 3 정규형을 모두 만족하더라도 데이터를 조작하는 데 문제가 발생될 수 있다. 복잡한 식별자 관계에 의해 발생되는 문제를 해결하기 위해 3차 정규화를 보완한 보이스-코드 정규화(boyce-code normalization)를 진행한다.
보이스-코드 정규화란 테이블에 존재하는 식별자가 여러 개 존재할 경우 식별자가 중복되어 나타나는 현상을 제거하기 위해 정규화 작업을 진행한다.

BCNF 실전 적용
납품 엔티티 타입의 주식별자는 부품번호, 부품이름, 납품번호 세 개의 속성의 구성이 되어 있고 세 개의 속성을 구성한 주식별자는 납품수량, 납품단가에 대해 결정자 역할을 한다. 그런데 부품번호+납품번호 만으로도 납품수량, 납품단가에 대해 결정자 역할을 할 수도 있고 부품이름+납품번호 만으로도 납품수량, 납품단가에 대해 결정자 역할을 할 수도 있다. 또한 부품번호와 부품이름은 상호간에 결정자역할을 하는 특성을 가지고 있다. 이러한 성격을 이용하여 데이터 모델에서는 최소의 속성의 조합이 주식별자를 갖게 하도록 BCNF(Boyce Codd Normal Form)를 적용한다. 즉, 부품번호를 주식별자로 하여 하여 부품을 구성하거나 부품이름을 주식별자로 하여 부품 엔티티 타입을 분리하여 납품과 관계를 갖게 하는 형식으로 정규화를 진행하는 방식이 바로 보이스-코드 정규화 방법이 된다.

<그림 6> BCNF 정규화의 응용

개념적 설명은 무척 까다롭지만 실전 사례를 통해서는 쉽게 이해되는 부분이다. 다시 한 번 정리하면, 주식별자 속성 중에 주식별자의 유일성을 확보하는 최소한의 속성이 아닌 쓸데없이 추가된 속성을 분리하는 것이 보이스-코드 정규화라고 할 수 있다. 또한 주식별자 속성 중에 상호간의 함수종속 관계를 가지는 것을 분리한다. <그림 6>의 부품번호와 부품이름 사례처럼 단독으로 주식별자에 참여할 수 있으면서 상호간의 종속 관계가 있는 코드, 코드명을 생각하면 쉽게 이해될 수 있다. 주식별자 속성이 많아질수록 보이스-코드 정규화의 대상이 되는 경우가 나타나므로 개념을 잘 정리하여 실전에서 데이터 모델을 검증할 수 있도록 해야 한다.

4차 정규화(특정 속성 값에 따라 선택적인 속성의 분리)

보이스-코드 정규화까지 정규화 작업을 진행하면 함수의 종속성에 관한 작업은 모두 정리가 되었다. 이제 더 이상 속성 사이의 종속적인 관계로 인해 발생하는 정규화 작업은 필요하지 않게 되는 것이다. 그러나 하나의 테이블에 두 개 이상의 독립적인 다가속성(multi-valued attribute)이 존재하는 경우에 다가종속(multi-valued dependency)이 발생되어 문제가 생긴다. 다가종속이라는 단어를 해석하면, 하나의 속성 값에 두 개의 이상의 의미를 가지는 값을 가지는 것을 의미한다.
4차 정규화의 대상이 되는 경우는 실제 프로젝트에서는 독립적인 엔티티 타입을 설계할 때 발생하기 보다는 동시에 여러 개의 엔티티 타입과의 관계에서 발생되는 경우가 많이 있다.

4차 정규화의 실전 적용
<그림 7>과 같은 업무 규칙이 있다. ‘한 명의 사원은 여러 개의 프로젝트를 지원할 수 있다’ 그리고 ‘한 명의 사원은 여러 개의 기술을 보유할 수 있다’ 즉 사원과 프로젝트, 사원과 기술 간의 업무적인 관계의 규칙이 있는 경우이다. 이 업무 규칙은 보유하는 기술이 있다는 사실을 관리하고 보유한 기술은 지원한 프로젝트와는 아무런 상관이 없다는 것이 특징이다. 그럼에도 불구하고 <그림 7>의 왼쪽처럼 사원과 프로젝트와 기술 간의 관계를 모두 연결하면 4차 정규화의 규칙을 위배하여 어떤 사원이 새로운 기술을 습득하여 사원내역 엔티티 타입에 등록하려고 하면 마치 금방 습득한 기술을 가지고 어떤 프로젝트를 지원한 것처럼 값을 채워줘야만 하는 현상에 빠지게 된다. 따라서 필요하지 않은 조인 관계를 해소하기 위해 오른쪽 모델과 같이 업무 규칙에 적합하게 관계를 분리하는 방법이 4차 정규화이다.

<그림 7> 4차 정규화의 응용

4차 정규화가 실전 프로젝트에서 거의 나타나지 않는다고 하는 사람들이 많은데 필자가 파악하기로는 2차 정규화나 BCNF보다 더 많이 발생된다. 단, 4차 정규화를 하지 않고 개발을 하다가 새로운 값을 채울 경우에 값을 기본 값(default value)으로 지정해버리는 경우가 많이 있다. 참조무결성 제약조건(FK)를 데이터베이스 테이블에 걸지 않는 경우에 가능한데 구축단계 때 많은 프로젝트에서 이와 같은 편법으로 프로그램을 작성한다. 좋지 않은 경우이다. 이와 같은 경우 데이터모델에 나타난 관계가 실제 데이터에서 불가피하게 단절되어 나타나므로 무결성 체크가 불가능해진다. 설계단계 때 불필요한 관계에 의해 나타나는 4차 정규화의 대상 엔티티 타입을 검증하여 정규화를 적용하도록 해야 한다.

반정규화

논리적인 데이터 모델링 단계에서는 모든 엔티티 타입과 속성들을 정규화 규칙에 적절하게 분석하여 데이터 모델링을 수행한다. 이 단계는 실전 프로젝트에서는 분석단계 때 수행하는 경우가 많고 설계단계 때는 데이터베이스 성능을 고려하여 물리적인 데이터 모델링을 수행하는데 물리적인 데이터 모델링의 여러 개의 타스크 중에 반정규화를 수행하게 된다. 반정규화라고 하면, 일반적으로 다른 엔티티 타입에 있는 속성을 중복한 것만을 생각하는 경우가 많이 있다. 훨씬 많은 반정규화 유형이 있고 각각은 유용하게 활용될 수 있음을 알 수 있다.
반정규화란 정규화된 엔티티 타입, 속성, 관계에 대해 시스템의 성능향상과 개발(development)과 운영(maintenance)의 단순화를 위해 데이터모델을 조정하는 프로세스를 의미한다. 단순하게 정규화 규칙에 반대되는 개념으로만 생각한다면 속성의 중복 정도가 반정규화의 범위에 해당되지만 물리적인 성능을 고려한 반정규화의 개념으로 생각한다면 테이블 통합/분리, 속성 중복, 속성 추가, 관계 중복 등이 반정규화의 범위에 해당된다.
반정규화를 적용하기 전에 반드시 중요하게 고려해야 할 점은 데이터의 무결성을 유지시킬 수 있는 방안을 마련하고 반정규화를 적용해야 한다는 것이다. 시스템을 개발할 때는 성공적인 오픈을 위해 성능을 중요하게 여겨 여러 테이블에 속성들을 반정규화하는 경우가 많은데 반정규화를 많이 할수록 데이터의 무결성은 깨져 이상한 데이터가 많이 남아있거나 돈의 액수가 맞지 않거나 등록된 접수 건수가 맞지 않은 현상이 시스템을 운영하는 중에 점점 많이 발생하게 되어 나중에는 시스템을 사용하지 못하게 되는 경우가 발생된다. 데이터 무결성을 중요하게 생각하고 반정규화를 적용할 필요가 있다.

반정규화에 대한 실전 프로젝트 적용 사례

반정규화를 하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가하거나 분할할 수 있으며 제거할 수도 있다.

1차 정규화에 대한 반정규화

고객에 대한 엔티티 타입에 방문을 두 번까지 가능하다고 할 때 고객번호, 고객명이 중복 속성 값을 갖기 때문에 1차 정규화의 대상이 되어 중간에 있는 고객방문 엔티티 타입으로 1차 정규화가 되었다. 그러나 최대 2회까지 방문이 가능하다는 업무 규칙을 이용하여 성능과 단순성을 고려하여 오른쪽에 있는 1차 정규화에 대한 반정규화 엔티티 타입으로 설계된 예이다.

<그림 8> 1차 반정규화의 응용

최대 발생하는 값을 이용한 이와 같은 반정규화의 유형은 실전 프로젝트에서 빈번하게 사용되지만 최대 발생 값을 변할 수 있는 경우는 정규화된 모습으로 모델링해야 확장성(flexible)이 보장된다는 것을 기억해야 한다.

2차 정규화에 대한 반정규화

주식별자가 두 개 이상일 때 일부 주식별자 속성에 의존적인 속성을 분리하는 2차 정규화에서 조인에 의한 성능저하와 단순성 확보를 위해 반정규화를 적용할 수 있다.

<그림 9> 2차 반 정규화의 모델

<그림 9>의 모델은 일자별 매각 물건 엔티티 타입에서 매각 일자가 결정자가 되고 매각 장소와 매각 시간이 의존자가 된 함수 종속성이 존재하여 2차 정규화를 적용했다가 다시 조인에 의한 성능저하 예방과 단순성을 위해 다시 일자별매각물건이라는 엔티티 타입에 반정규화를 한 경우이다.

3차 정규화에 대한 반정규화

<그림 10>의 모델을 보면 수납이라고 하는 엔티티 타입은 속성간의 결정자(수납확인번호)와 의존자가 존재하는 3차 정규화의 대상이 되는 모습이다. 따라서 수납확인번호를 결정자로 하고 수납확인방법, 수납확인일자, 수납확인자사번을 속성으로 하는 3차 정규화를 적용하였다.

<그림 10> 3차 반정규화의 응용

반정규화를 하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다. 정규화에 위배되는 것은 아니지만 성능을 위해 적용하는 반정규화의 방법 테이블 통합/분리, 속성 중복, 관계 중복 등 여러 가지가 있을 수 있다.

이력의 최근 변경 속성 값 반정규화

<그림 11>의 모델은 공급자에 대한 전화번호, 메일주소, 위치 등에 대한 변경 정보를 각각 관리하는 현재 데이터와 이력 데이터에 대한 데이터 모델이다. 모든 속성 값이 중복이 없어 완벽히 정규화된 모습이지만 이력 모델이 정규화되어 있음으로 인해 최근 값을 처리하는 데 상당한 시간이 소요되고 SQL 구문도 복잡하게 된다. 따라서 데이터를 조회할 때는 프로세스의 대부분은 가장 최근 값을 참조한다는 성격을 이용하여 오른쪽과 같이 최근 값에 대한 속성 값만을 관리하기 위해 공급자 엔티티 타입에 전화번호, 메일주소, 위치에 대한 속성을 추가하였다.

<그림 11> 최근 변경 값 속성의 반정규화

<그림 11>에서 공급자번호 1001～1005에 해당하는 공급자번호, 공급자명, 전화번호, 메일주소, 위치에 대한 정보를 조회하면 다음과 같이 작성된다.

SELECT A.공급자명, B.전화번호, C.메일주소, D.위치 FROM 공급자 A, (SELECT X.공급자번호, X.전화번호 FROM 전화번호 X, (SELECT 공급자번호, MAX(순번) 순번 FROM 전화번호 WHERE 공급자번호 BETWEEN '1001' AND '1005' GROUP BY 공급자번호) Y WHERE X.공급자번호 = Y.공급자번호 … WHERE A.공급자번호 = B.공급자번호 AND A.공급자번호 = C.공급자번호 AND A.공급자번호 = D.공급자번호 AND A.공급자번호 BETWEEN '1001' AND '1005' SELECT 공급자명, 전화번호, 메일주소, 위치 FROM 공급자 WHERE 공급자번호 BETWEEN '1001' AND '1005' 정규화된 모델에서 SQL반정규화된 모델에서 SQL 반정규화

적절한 반정규화를 통해 성능도 훨씬 향상되었을 뿐만 아니라 SQL 구문도 비교가 안될 만큼 단순해졌음을 알 수 있다.

관계 반정규화

속성의 반정규화에서 데이터를 조회하는 경로를 단축하기 위해 일반속성(주식별자가 아닌)을 중복할 수도 있고 주식별자 속성을 중복할 수도 있다. 주식별자 속성의 중복 중 전체 주식별자를 이루는 전체 속성의 중복은 곧 관계의 중복을 의미한다. 관계의 반정규화는 인위적인 속성의 중복 없이 조회경로 단축을 통해 조인에 의한 데이터 처리 속도를 향상시키는 장점이 있다.
<그림 12>의 왼쪽은 고객, 주문, 주문목록, 배송 엔티티 타입이 정규화가 잘 되어 있고 관계도 업무 규칙에 따라 식별자 관계/비식별자 관계로 적절하게 설정되어 있다. 그런데 배송 엔티티 타입에 발생되는 프로세스가 데이터를 처리할 때 항상 고객에 있는 속성의 모든 정보를 참조해야 하는데 왼쪽 정규화된 모델에서는 항상 주문목록과 주문을 경유하여 고객정보를 처리함으로 조인에 의한 성능저하가 예상된다. 따라서 조회경로를 단축하기 위해 오른쪽과 같이 관계를 추가로 연결하여, 즉, 이미 고객->주문->주문목록->배송으로 관계는 연결되어 있지만 성능을 위해 고객->주문으로 직접 관계를 연결한 관계의 반정규화를 적용한 사례이다.

<그림 12> 관계의 반정규화

<그림 12>의 데이터 모델에 왼쪽에 있는 데이터 모델에 대해 배송일시와 고객번호, 고객명을 가져오는 SQL 문장을 작성하면 다음과 같이 작성될 수 있다.

  SELECT D.고객번호, D.고객명, A.일시  FROM 배송 A, 주문목록 B, 주문 C, 고객 D  WHERE A.배송번호 = ‘20031001001’  AND 배송.주문번호 = 주문목록.주문번호  AND 배송.제품번호 = 주문목록.제품번호  AND 주문.주문번호 = 배송.주문번호  AND 고객.고객번호 = 주문.고객번호

간단한 고객에 관련된 정보를 읽어오는데 2개의 테이블을 필요하지 않게 읽은 경우이다. 오른쪽과 같이 관계가 중복된 경우는 배송일시와 고객번호, 고객명을 가져오기 위해 다음과 같이 SQL 문장을 구성할 수 있다.

  SELECT B.고객번호, B.고객명, A.일시  FROM 배송 A, 고객 B  WHERE A.배송번호 = ‘20031001001’  AND 배송.고객번호 = 주문.고객번호

2개의 테이블에 대해서만 접근을 하므로 관계가 중복되지 않은 경우보다 훨씬 쉽게 SQL 문장도 구성되며 성능도 더 낫다. 테이블의 관계가 5단계 6단계까지 내려가면서 중간에 비식별자관계로 연결되어 있고 빈번하게 조인이 되는 경우라면 관계의 중복을 고려할 수 있다. 프로젝트 상황에 따라 관계의 반정규화는 성능과 단순성에 있어 매우 유용하다.

호두과자에는 호두가 있다!

중부지방을 경유하는 기차 여행을 하면 자주 호두과자를 먹게 된다. 붕어 없는 붕어빵과는 다르게 호두과자에는 호두 알갱이가 있어 제법 고소한 맛이 난다. 이처럼 관공서, 학교, 기업 등에 구축하는 데이터베이스가 견실하기 위해서는 잘 정리된 정규화 사상이 녹아져 있어 정규화 사상 맛이 나는 데이터 모델이어야 한다. 그리고 거기에 체계화된 방법과 타당성 있는 반정규화를 적용한 데이터 모델을 만들어 내야 한다. 이 일은 그렇게 해도 되는 선택적인 사항이 아니라 한 번 구축하면 변경이 불가능하고 잘못된 데이터베이스는 시간에 따라 엄청난 문제와 제정을 낭비하기 때문에 그렇게 해야 하는 당위성을 가지고 있는 중요한 작업이다.
그러기 위해서는 데이터 모델링을 수행하는 사람은 정규화/반정규화에 대해 거울로 자기 얼굴을 보듯 정확한 이해와 체계적인 사고를 바탕으로 데이터 모델링을 할 수 있는 능력을 가져야 한다. 이 글을 읽는 독자는 이론을 위한 이론, 학교시험에서 점수 획득을 위한 지식의 단계를 뛰어넘어 실전에서 무한한 가치를 창조해 내는 진정한 지식가치의 이론을 겸비하여 최고의 데이터 모델링을 수행하는 전문가가 되기를 희망한다.

'SQL' 카테고리의 다른 글

[ms-sql] 실행 계획 보기... 아주 쌩초보다.. (0)	2005.06.20
MySQL 1067 에러... (3)	2005.05.20
[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자 (0)	2005.03.02
mysql 4.0.x 이하 버전 .. 한글셋팅.. (0)	2005.02.16
[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용 (0)	2005.02.02

Posted by tornado

[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자

SQL 2005. 3. 2. 10:43

‘6개의 열쇠’로 데이터 모델링의 고수가 되자

이춘식

현업에 종사하는 질문자가 제기한 6개의 주요 질문에 답하는 형식으로 30일간의 여행을 정리하고자 한다. 실전에서 발생하는 데이터 모델의 1:1 관계에 대한 처리 방법과 이력 데이터 모델, 우편번호 데이터 모델에 대한 내용과 오라클 RAC과 배치 처리에 대한 질문과 답변 내용이 포함됐다.

신행정수도 이전 문제로 사회가 온통 떠들썩하다. 수도 이전에 대해 찬성과 반대 여부를 떠나 근본적으로 서울은 조선시대 도시 구조를 이어받아 발전한 곳이다. 도보와 마차가 중심인 조선시대의 도시 설계가 되어 있던 곳에 현대 문명을 담아내고자 이리 고치고 저리 고치면서 서울은 난잡한 도시의 형태를 가지게 됐다. 인구 집중과 함께 복잡성의 증가 등 많은 문제로 인해 새로운 행정수도를 모색하다보니 이전 비용이나 지역별 이해 관계 등의 여러 가지 골칫거리가 대두되고 있다.
기업의 데이터베이스를 분석하고 설계·구축하는 것은 단순히 건물 하나를 만드는 것이 아니라 도시의 근간을 만들어내는 것과 같다. 데이터베이스를 이용하는 방법, 사용하는 양 등을 고려하여 종합적이고 안정적으로 데이터 모델링부터 데이터베이스 관리까지 돼야 한다.
이번 질문과 답변은 데이터베이스 담당자가 오라클 기반의 업무를 수행할 때 발생하는 이슈 중 데이터 모델링을 중심으로 서술되어 있다. 시간과 분량의 제약으로 많은 내용은 담지 못했지만 서상현씨가 프로젝트에서 빈번하게 나타나는 문제에 대해 적절하게 질문함으로써 이 글을 읽는 독자에게도 유익한 간접 체험이 될 것이다.

1:1 관계와 과도한 반정규화의 해결 방안

질문 1) <그림 1>과 같이 현재 고객을 기준으로 모델이 구성되어 있다. 그리고 모든 관계는 1:1로 되어 있다. 문제는 이러한 모델이 논리적 모델의 확정 없이 프로세스 관점으로 데이터의 구분에 따라 분리되어 있는 상태라는 점이다. 이러한 테이블을 통합하거나 논리적 관점에서 재구성하려면 어떤 검토 과정을 거쳐야 하는지 궁금하다.

<그림1> 1:1 관계의 초기 데이터 모델

일단 구 데이터 모델을 완벽하게 리모델링하려면 ‘1단계 : 업무 구성과 흐름 분석’, ‘2단계 : 구 모델을 기준으로 논리 모델 작성’, ‘3단계 : 물리 모델 작성’의 형식으로 작업하면 된다. 이때 논리 모델은 최대한 업무 구성과 흐름에 적합한 데이터 모델을 만들어야 하고, 물리 모델은 데이터베이스에 적합한 모델, 즉 데이터베이스 중심의 모델을 만들어야 한다. 논리 모델은 업무 구성과 흐름에 따라 데이터 모델이 적절하게 표기된 모습, 즉 업무 거울이라고 할 수 있다. 물리 모델은 PK 순서, 반정규화 등이 고려돼야 한다. 1단계인 업무 흐름 분석이 어느 정도 됐다고 가정하고 2단계로 구 모델을 논리적인 데이터 모델로 일단 표현한 다음 3단계인 물리 모델로 만들어 보겠다.

2단계 논리적 데이터 모델은 트랜잭션이 슈퍼 타입이 되고 나머지 엔티티 타입들이 서브 타입이 되는 슈퍼 타입/서브 타입 데이터 모델로 표현할 수 있다. 다만 대출 내역 상세는 서브 타입이라기보다는 대출 내역의 내용으로, 대출 내역과 1:1 관계로 표시하면 <그림 2>와 같은 모델로 나타난다. 참고적으로 트랜잭션은 엔티티 타입의 업무 단위가 실행되면서 발생되는 모든 데이터의 정보를 담는다.
논리 모델인 슈퍼 타입/서브 타입의 특징은 슈퍼 타입에는 공통적인 속성이 존재하고 각각의 서브 타입에는 서로 다른 유형의 속성이 존재해야 되는데 대출 내역, 채무보증 내역, 금융 내역의 엔티티 타입은 서브 타입의 속성이 모두 거래 총 건수, 변경일자로 동일한 유형을 가지고 있어 서브 타입으로 적절하지 않다. 또한 대출 내역의 상세 엔티티 타입은 업무 흐름상 트랜잭션과 직접 1:1 관계가 있어도 되지만 대출 내역과 연결되는 것이 더 적절한 관계가 될 것이다. 트랜잭션에 추가한 트랜잭션 유형 코드는 하위에 있는 각각의 서브 타입을 구분할 수 있는 구분자 속성이다. 슈퍼 타입/서브 타입 모델링을 할 때 절대적으로 필요한 속성이니 반드시 기억하기 바란다. 이와 같은 내용을 반영하여 정제된 슈퍼 타입/서브 타입 모델을 만들면 <그림 3>과 같이 나타난다.

<그림3> 정제된 슈퍼 타입/서브 타입 모델로 표현

이때 슈퍼 타입인 트랜잭션 엔티티 타입에 거래 유형 코드의 값은 01:불량 내역, 02:대출 내역, 03:채무 보증 내역 04:금융 내역 서브 타입을 구분할 수 있는 형식으로 들어간다. 불량 내역은 워낙 다른 서브 타입과 속성의 차이가 많이 나므로 서브 타입으로 남아있고 대출 내역도 1:1 관계의 대출 내역 상세에 한 개의 속성만이 다른 서브 타입과 차이가 있어 서브 타입으로 남아있는 반면 채무 보증 내역과 금융 내역 서브 타입은 자신만의 속성이 없으므로 슈퍼 타입으로 통합됐다.

이전에 복잡했던 데이터 모델이 아주 간단해졌다. 그리고 슈퍼 타입과 서브 타입 모델을 통해 데이터 모델만 봐도 ‘아 이 업무는 트랜잭션이 유형에 따라서 불량 내역도 있고 대출, 채무보증 등이 있구나!’라고 알 수 있다. 업무 구성과 흐름이 명확해지는 논리 모델의 특징이다. 이제 마지막 3단계로 슈퍼 타입과 서브 타입 모델을 물리 모델로 전환해 보자. 슈퍼 타입/서브 타입 논리 모델은 개별 테이블로 변환, 슈퍼+서브 테이블로 변환, 하나의 테이블로 변환 등 세가지 물리 모델로 전환할 수 있다. <그림 4>는 세가지 형식으로 전환할 수 있는 방법을 나타낸다.

<그림4> 슈퍼 타입/서브 타입 모델 물리 모델 전환 방법 세가지

이와 같이 슈퍼 타입/서브 타입 모델을 세가지 형식의 물리 모델로 전환하는 기준은 트랜잭션의 성격과 양이다. 트랜잭션의 성격은 슈퍼 타입, 서브 타입에 대해 개별로 트랜잭션이 발생하는지 아니면 통합되어 발생하는지를 분석하는 것이다. 또 트랜잭션의 양을 고려한다는 것은 발생 건수가 적을 경우에는 분리하건 통합하건 크게 문제되지 않지만, 발생 건수가 많을수록 성능에 영향을 많이 미치는 점을 고려한다는 것이다. 그러므로 반드시 업무에서 트랜잭션이 발생될 때 슈퍼 타입과 서브 타입에 통합하여 발생되는지, 분리되어 발생되는지 고려해 적용해야 한다.

질문한 업무의 트랜잭션은 아주 빈번하게 발생되므로 트랜잭션의 유형이 고려돼야 하는 경우이고, 구 데이터 모델이 1:1 관계로 모델링된 것으로 보아 불량 내역, 대출 내역 등 개별적으로 트랜잭션이 많이 발생하는 것으로 보인다. 따라서 첫번째 방법인 슈퍼 타입/서브 타입 논리 모델을 개별 테이블로 변환하는 규칙을 적용하면 <그림 5>와 같은 물리적인 데이터 모델이 된다.

<그림5> 슈퍼 타입/서브 타입 모델을 1:1 관계의 물리 모델로 전환

그런데 이렇게 전환하고 보니 불량 내역에 비해 대출 내역의 속성이 너무 빈약하다. 즉 업무적인 트랜잭션이 발생될 때 대출 내역 테이블만을 읽어서 독립적으로 작업할 것 같지는 않다. 대출 내역 테이블은 트랜잭션에 테이블 반정규화를 하도록 한다. 다만 테이블 반정규화를 했기 때문에 대출 거래 총 금액이라는 속성이 반드시 데이터가 들어와야 하는 NOT NULL 속성이라고 해도 다른 서브 타입 유형에는 총 금액 속성이 없다. 그러므로 트랜잭션에 반정규화한 대출 거래 총 금액은 NOT NULL 형식의 컬럼 제약 사항을 걸 수 없다. 이와 같은 내용을 반영한 최종적인 데이터 모델은 <그림 6>과 같다.

<그림6> 반정규화를 적용한 물리 모델

아주 단순해졌지만 업무적인 흐름(논리 모델)이 모두 고려됐고 데이터베이스 성능에 대한 고려(물리 모델)도 되어 체계적이고 안정적인 데이터 모델이 됐다. 초기 모델과 변경된 모델을 비교하면 <그림 7>과 같이 나타난다.

<그림7> 이전 모델과 개선된 모델 비교

초기에 비해 많이 단순해졌다는 것을 알 수 있다. 많은 프로젝트의 데이터 모델이 앞의 경우처럼 불필요한 복잡성을 가지고 있는 경우가 있다. 참고로 데이터 모델이 단순해지면 데이터 모델에 대한 가독성(readablity)도 좋아지고 데이터베이스 성능도 향상된다.

질문 1-1) 책을 보면서 어렵게 여겼던 슈퍼-서브 타입간의 관계 해결 방안에 대한 내용이 이 예를 통해 많은 도움이 됐다. 정확한 업무의 흐름과 데이터를 이해하기 위해서는 논리 모델이 제대로 되어 있어야 업무가 추가·변경되더라도 쉽게 적응할 수 있을 것 같다. 그러나 현재 우리 회사 시스템의 경우 최종적인 물리 모델의 결과만 가지고 있어 이를 해소하기 위해 현행 물리 모델을 분석하여 논리 모델화하고 그 논리 모델을 이용해서 물리 모델을 재구성해야 할 것 같은데 이런 경우에는 어떤 진행 단계와 방법을 거치는지 알고 싶다.

좋은 질문이다. 논리 모델을 정확하게 만들기 위해서는 업무의 이해가 선행돼야 하고 업무에 따라 논리 모델을 만들어 내는 것이다. 이미 완성되어 있는 모델에서 논리 모델을 만드는 방법은 다음과 같이 적용하면 어렵지 않게 만들 수 있다. 엔티티 타입, 관계, 속성 등이 존재하기 때문에 존재하는 내용을 근간으로 다음 순서로 적용해 보자.

1. 업무의 기본이 되는 엔티티 타입의 관계, 속성을 나열한다. 기본이 되는 엔티티 타입이란 복잡한 업무의 흐름이 없이도 존재할 수 있는 엔티티 타입, 관계, 속성이다. 예를 들어 고객, 부서, 회원 등이 여기에 해당된다. PK와 FK도 기본적인 업무 구조를 바탕으로 적용한다.

2. 업무 흐름에 따라 발생되는 엔티티 타입을 나열하고 배치한다. ‘금융 거래를 한다’라는 업무 흐름이 있다면 거기에 따른 금융 거래 내역, 트랜잭션, 채무 보증 내역 등의 엔티티 타입과 관계, 속성이 있다. 그와 같은 엔티티 타입을 데이터 모델에 추가한다. 이때 PK와 관계 설정이 아주 중요하게 고려돼야 한다. 특히 가장 중심이 되는 핵심 엔티티 타입의 PK 선정이 제일 중요하다. 그리고 관계를 설정할 때도 무조건 주식별자 관계(부모의 PK가 자식의 PK로 오는 것)로 연결하는데 비식별자 관계(부모의 PK가 자식의 일반 속성으로 오는 것)를 적절하게 활용하면 아주 좋은 데이터 모델을 만들 수 있다.

3. 데이터 모델링의 기법에 따라 모델을 조정한다. M:N 관계를 해소하고 데이터 모델을 통합 또는 분리하는 등의 모델링을 전개한다.

4. 마지막으로 완성된 데이터 모델에 대해 엔티티 타입, 관계, 속성, 도메인을 중심으로 모델 검토를 하면 된다.

이때 코드성과 통계성을 제외한 모든 엔티티 타입은 관계가 존재하도록 모델링해야 한다. 이렇게 완성된 논리적인 데이터 모델은 다시 데이터베이스 무결성과 성능 관점에서 물리적인 데이터 모델로 전환하면 된다. 물리적인 데이터 모델의 최대 관심은 성능 향상이므로 데이터 무결성을 보장할 수 있는 방법을 보완하면서 적절하게 반정규화를 적용하고 PK 순서를 인덱스 구조에 맞게 조정하는 작업을 하면 된다.

효과적인 우편번호 관리 모델 설계하기

질문 2) 일반적으로 우편번호는 하나의 독립적인 엔티티 타입으로 만들어서 관리하고 있는 것 같다. 현재 우리 모델 역시 <그림 8>과 같이 독립적인 테이블로 만들어서 관리하고 있다. 실제 업무를 해보니 우편번호는 너무나 다양한 형태로 변하고 있어서 관리하기가 쉽지 않다. 그래서 현재는 수시로 전체 데이터를 받아서 지우고 입력하는 형태로 하고 있다.

<그림8> 우편번호 테이블

첫째, 우편번호의 변경에 대한 작업은 앞의 방법 외에는 없는 건지 아니면 테이블의 설계 변경을 통해 더욱 효율적인 관리 방법이 있는지 알고 싶다. 둘째, 우편번호에 대한 변경이 일어나면 우편번호를 이용한 회원 정보의 반정규화된 우편번호 속성 값에 대한 수정이 이루어져야 하는데 변경된 경우의 수가 많아서 쉽게 적용할 방법이 없는데 이를 효과적으로 할 수 있는 방법이 있는지 알고 싶다.

<그림9> 세분화해 관리하는 우편번호 주소 체계

첫번째 질문인 효율적인 우편번호 관리에 대한 답변이다. 업무 시스템에서 우편번호 관리에 대한 특징은 반정규화와 데이터 소유권의 두 가지가 있다. 우편번호에 관련된 정보는 반정규화가 기본이다. 우편번호에 대상 테이블을 공통으로 가지고 있지만 대부분 다른 업무 테이블에서는 우편번호와 주소 내용을 반정규화하여 사용하고 있는 것이 특징이다. 즉 원하는 우편번호와 주소를 복사해서 업무 테이블에 입력하는 형태이다. 이러한 특징은 공통으로 관리하는 우편번호 데이터가 변할 경우 데이터 무결성이 일치하지 않는 현상이 발생할 수 있는 구조이다.

두번째는 우편번호에 대한 소유권은 정보통신부 우정사업본부에 있다. 즉 우편번호 데이터에 대한 입력·수정·삭제에 대한 담당은 우리나라 정보통신부의 우정사업본부에서 관할하고 있다. 다시 말해 데이터에 대한 소유권이 우정사업본부에 있으므로 다른 시스템에서는 우정사업본부에서 담당하는 우편번호를 이용(읽기)만 할 수 있다.

우편번호 정보에 대해 데이터를 관리할 때 변경된 데이터만 수정하는 방법이 있고 모든 데이터를 수정하는 방법이 있다. 특정 우편번호에 대한 내용이 바뀌었을 때는 변경된 데이터에 대해서만 수정하는 것은 프로그램을 이용하여 쉽게 변경할 수 있어 효율적일 수도 있으나 우편번호가 추가되거나 변경 또는 삭제가 되었을 경우는 프로그램에 처리하는 것은 불가능하고 사람이 모두 데이터를 확인하여 처리해야 하는 단점이 있다. 반면 새롭게 전체 데이터를 수정하게 되면 데이터 모델이 다른 업무 테이블과 관계를 가질 수 없다는 단점이 있다. 그러나 우편번호 정보 관리의 특징상 데이터의 소유권이 특정 기관에 있고 또한 테이블과 관계를 가지지 않고 참조 형식의 데이터 모델이 되는 구조이므로 우편번호 정보 전체에 대해 데이터를 수정하여 적용하는 것이 가능하고 합리적인 방법이 될 수 있다.

<그림10> 우편번호 변경 내용(http://www.postman.pe.kr 참조)

또한 요즘 주소를 이용한 조회수가 증가하고 있고 향후 DW 유형으로 시스템을 만들어야 할 경우가 있으므로 우편번호 세부 정보를 이용하여 조회하는 경우가 많다. 우편번호를 관리할 때 우편번호와 시도(광역시), 시군구, 읍면동, 번지, 호의 내용을 개별 속성으로 가지고 있는 것이 정보 조회 처리에 아주 효율적인 방법이다.

두번째 질문인 우편번호 변경이 발생하면 우편번호를 이용하는 회원 정보에 대한 수정은 어떻게 하는지에 대한 답변이다. 2004년 5월 17일에 우편번호 변동 내역을 분석해 보면 139건의 변경사항이 있었는데 그 중에 12건만이 우편번호에 대한 변경사항이었고 나머지는 주소 부분에 대한 변경사항이었다. 즉 우편번호 전체 약 4만건 중 139건이 변경되어 0.3%가 변경됐고, 변경된 내용 중 91%는 주소 부분 변경이었고 9%가 우편번호에 대한 변경이었다.

우편번호 정보를 이용하는 업무 테이블에 내용을 쉽게 변경하기 위해서는 프로그램을 이용해 일괄적으로 하면 된다. 그러나 하나의 우편번호에 대해 두 개 이상의 주소가 포함되어 있으므로 사람이 확인하지 않고 프로그램에 의해 100% 변경하는 것은 정보가 잘못 변경될 가능성이 있어 아주 위험한 경우이다. 그렇다고 우편번호 정보 변경 내용을 하나의 레코드씩 업무 테이블에 반영하는 것은 너무 많은 시간이 소요된다. 그러므로 업무 테이블의 우편번호 정보 변경의 효율적인 방법은 우편번호만 변경된 경우와 주소 부분이 변경된 경우를 구분하여 일괄 변경하고 그 내용을 사용자 화면으로 확인하여 저장하는 방식이 될 수 있다.

질문 2-1) 현재의 우편번호 관리 체계가 단순히 우편번호/주소1/주소2로 되어 있다. 이 부분에 변경된 모델을 적용하는 이유는 하위 주소가 변경 혹은 삭제되었을 경우 수정의 용이성이나 우편번호의 분리와 통합에 따른 유연성이 확보될 것이기 때문이다. 물론 앞의 형태에서 주소 부분을 더 세분화할 수도 있을 것 같다.

문제는 업무 테이블의 우편번호에 대한 변경인데, 지금까지 변경 히스토리가 잘 관리되어 왔다면 모르겠지만 그렇지 않은 경우에는 작업하기가 쉽지 않을 것 같다. 우편번호 관리라는 것은 결국은 반정규화되어 여러 테이블에 분리·저장되어 있는 우편번호를 어떻게 효과적으로 관리하는 것인가의 문제인 것 같다.

맞다. 기존에 업무 테이블에 있는 우편번호 주소 정보를 세부적으로 하려면 많은 노력이 필요하다. 그래서 데이터 정련(data cleansing) 작업을 하는데 만약 할 수만 있다면 데이터 정련 작업과 함께 우편번호 주소 정보를 세분화하는 것을 권고한다.

데이터의 이력 관리를 위한 이력 모델 설계하기

질문 3) 데이터의 이력 관리를 위한 이력 모델 설계는 어떻게 해야 하는지 궁금하다.

하나의 업무 단위가 시간 흐름에 따라 발생하는 과거와 현재 데이터를 지속적으로 유지하는 관리 방법을 이력(history) 관리라고 한다. 이력 관리에 대한 내용을 ‘데이터베이스 설계와 구축’에 있는 내용을 기준으로 설명하겠다.

우리가 데이터 모델링을 진행할 때 이력이라고 하는 기준은 업무 단위의 시작과 끝을 가지고 판단해야 한다. 인사 관리에서 사원은 입사를 하면 반드시 퇴사가 존재한다.

주문 관리에서 상품을 주문하면 배송하든지 취소하든지 업무를 종결할 것이다. 시작과 끝이 존재하는 업무에서 하나의 업무 단위가 진행되는 중에 시간에 따라 변화하는 데이터가 존재하는 경우가 있다. 이때 데이터를 단순히 수정하여 관리한다면 그 데이터는 항상 그 시점의 현재 데이터만을 유지하는 형태가 될 것이며 만약 데이터를 수정하지 않고 계속해서 입력하여 관리한다면 그 시점의 과거 데이터도 유지되면서 현재 데이터도 발생될 것이다. 정보화 시스템을 구축하면서 이력의 대상이 되는 항목은 후자, 즉 하나의 업무 단위가 동일한 관리 항목에 대해 과거 데이터도 유지되면서 현재 데이터도 관리되는 형태를 지칭한다.

이력의 종류는 이력이 발생하는 유형에 따라 세 가지로 구분될 수 있다. 첫번째로 하나의 엔티티 타입에 대한 변경이 있을 때 변경 이전과 이후의 데이터를 모두 관리하는 변경 이력, 두번째로 년월일별로 특정 시점에 발생하는 발생 이력, 그리고 세번째는 어떤 데이터가 시간에 따라 연속성을 가지고 존재하는 형태의 진행 이력이 존재한다.

일단 해당 업무에서 이력 데이터를 관리하고자 했다면 데이터 모델에서 가장 중요하게 고려해야 할 부분이 엔티티 타입에 대한 식별자의 구성과 그 엔티티 타입이 가지는 관계이다. 먼저 엔티티 타입은 변경 이력, 발생 이력, 진행 이력에 따라 세 가지 형태로 구분하여 식별하도록 한다. 세 가지 모두 업무상 꼭 필요한지, 통계 정보나 기업 의사 결정에 필요한 정보인지를 먼저 검토하고 필요한 업무에 대해서만 데이터 모델에 반영하도록 해야 한다.

그리고 엔티티 타입의 구성을 결정한다. 이력이 발생한다는 것은 동일한 엔티티 타입에 대해 시간에 따라 데이터가 발생하는 것이므로 시간을 나타내는 속성이 필요할 것이다. 이 때 엔티티 타입을 한 개로 유지할 것인지 두 개로 분리할 것인지를 결정해야 한다. 시간에 따라 속성의 일부만 변하는지 아니면 모든 속성이 변하는지 파악하고, 일부 속성만 변할 경우 데이터의 중복(redundancy)을 피하기 위해 두 개의 엔티티 타입으로 분리하여 설계한다. 또한 동일 엔티티 타입의 모든 속성이 시간에 따라 같이 변한다고 할지라도 데이터를 조회하는 유형에 따라 엔티티 타입의 분리 여부를 결정해야 한다. 만약 일반적인 업무에서는 현재 유지되는 정보만을 조회하고 과거 데이터에 대해서는 가끔씩 조회한다면 대량으로 존재하는 이력 엔티티 타입을 별개로 구분하기 위해 엔티티 타입을 현재에 해당하는 엔티티 타입과 이력에 해당하는 엔티티 타입으로 분리하여 설계할 수 있다.

발생 이력과 변경 이력

데이터 처리의 성능을 위해 발생 이력과 변경 이력의 경우는 최종 생성된 데이터의 구분을 위한 기능성 컬럼이 필요하고, 진행 이력의 경우는 연속적인 특징이 있으므로 생성된 시점과 완료된 시점에 대한 기능성 컬럼이 필요한 특징을 가지고 있다. 발생 이력, 변경 이력의 경우 최신 값에 대한 기능성 컬럼이 존재하지 않아 성능이 저하된 경우이다.

일반적으로 업무적인 필요에 따라 모델링을 진행한 접수 통계 테이블은 변경 이력 테이블로서 사업소마다 접수받는 물량에 대한 정보를 가지고 있는 테이블이다. 사업소마다 접수 구분 코드가 ‘01’인 접수 물량을 합한 정보를 가져와야 한다면 <그림 11>과 같이 복잡한 SQL 구문이 작성된다.

<그림11> 발생 이력/변경 이력에서 최신 여부에 컬럼이 없을 경우

SQL 구문 작성시 그룹 함수를 사용하면 그룹의 대상이 많아짐에 따라 성능이 저하되는 것은 당연한 현상이다. <그림 11>의 접수 통계 테이블에서는 사업소 코드에 따른 최근에 변경된 내용에 대해 데이터를 가져오기 위해 ‘SELECT 사업소 코드, MAX(변경일자) ~ GROUP BY 사업소 코드’의 형식으로 SQL문을 작성할 수밖에 없다. 실행 계획을 보면 인라인 뷰를 사용했으므로 VIEW라고 하는 단어가 있고 SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간 단계에서 정렬 작업이 발생되었음을 알 수 있다. 그래서 접수 통계 테이블에 최신 여부를 나타내는 기능성 컬럼을 포함하면 <그림 12>와 같이 SQL 구문이 작성될 것이다.

<그림12> 발생 이력/변경 이력에서 최신 여부를 생성한 경우

최신 여부 컬럼이 접수통계 테이블에 있으므로 데이터를 조회할 때 별도의 인라인 뷰가 작성될 필요 없이 SQL WHERE 절에 최신여부 = ‘Y’만 있으면 쉽게 데이터를 처리할 수 있고 SQL문의 처리 성능은 향상된다. 단 새로운 데이터가 입력될 때 이전 변경일자에 대한 최신 여부 값을 ‘Y’에서 ‘N’으로 바꾸어야 하는 부가적인 작업은 발생된다. 즉 입력·수정·삭제시 기능성 컬럼에 대한 추가적인 고려가 필요하다.

필자가 경험한 E 프로젝트의 경우는 응용 프로그램의 60%가 이와 같은 발생 이력을 이용하여 업무 처리를 하는 프로세스를 가지고 있었는데 개발된 SQL 구문이 모두 인라인뷰와 그룹 함수까지 사용하여 최근에 발생한 값을 가지고 처리하는 방식으로 되어 있었다. 발생 이력이 업무적으로 중요한 테이블이었기 때문에 웬만한 SQL들은 모두 발생 이력 테이블을 이용할 수밖에 없었던 것이다. 개발 도중에 테스트 데이터가 많지 않았을 때는 별로 문제가 없었지만 개발이 끝나고 어느 정도 데이터를 생성한 이후에 테스트해보니 응용 프로그램에 심각한 성능 저하 현상이 발견됐다. 테이블에 인덱스를 추가하고 SQL 구문의 변경을 아무리 해도 근본적으로 성능이 개선되지 않았다. 그래서 발생 이력 테이블에 기능성 컬럼(최신 여부 컬럼) 하나를 추가하고 데이터 입력·수정·삭제시 이 컬럼을 고려하여 처리함으로써 성능 저하 현상을 막을 수 있었다. 비록 프로젝트 막바지에 개발된 프로그램의 60%를 수정해야 했지만 막대한 공수를 투입하여 데이터 모델과 응용 프로그램을 수정해 성능 저하를 예방한 것이다.

진행 이력

진행 이력의 경우는 발생 이력과 변경 이력과 다르게 발생된 시점 이외에도 데이터 조회가 빈번하게 이루어진다. <그림 13>에 있는 기관 정보 테이블은 어떤 시점에 따라 해당 기관이 가지고 있는 기관 거래 등급을 가지고 있으면서 다른 테이블에서 기관거래 등급 정보를 참조하여 업무를 처리하는 경우이다. 2004년 7월 1일자에 해당하는 기관 코드와 기관 거래 등급을 조회하는 SQL은 다음과 같이 복잡한 SQL 구문이 작성된다.

<그림13> 진행 이력에 상태 관련 종료 값이 없는 경우

<그림14> 진행 이력에 상태 관련 종료 값이 있는 경우

<그림 13>의 기관 정보 테이블에서는 지정된 날짜인 2004년 7월 1일에 기관 코드에 따른 기관 거래 등급 데이터를 가져오기 위해 ‘SELECT 기관 코드, MAX(적용일자) ~ GROUP BY 기관 코드’의 형식으로 먼저 지정된 날과 같거나 작은 적용일자를 가져와야 한다. 그리고 다시 메인 쿼리에서 적용일자를 비교해야 데이터를 가져올 수가 있다. 실행 계획을 보면 인라인 뷰를 사용했으므로 VIEW라고 하는 단어가 있고 SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간 단계에서 정렬 작업이 발생되었음을 알 수 있다. 적용일자에 인덱스가 있어도 범위가 넓어 성능 저하가 예상된다. 그래서 기관 정보 테이블에 기간을 알 수 있도록 적용 종료일자를 나타내는 기능성 컬럼을 포함하면 <그림 13>과 같이 간단한 SQL 구문이 작성될 것이다.

기관 정보 테이블에 적용일자라는 기능성 컬럼이 추가됨으로써 SQL 구문도 단순해지고 이전 모델에 비해 성능도 훨씬 빨라지게 된다. 단 새로운 데이터가 입력될 때 적용 종료일자 컬럼에 업무적으로는 입력되는 데이터가 있을 수 없지만 편의상 최대 값(예를 들어 9999년 1월 1일)을 입력하여 인덱스 이용에 문제가 없도록 해야 한다. 이와 같이 이력 데이터 모델은 발생 이력, 변경 이력, 진행 이력으로 구분하여 각각에 따른 적절한 기능성 컬럼을 부여함으로써 효율적인 데이터베이스 성능을 나타내게 할 수 있음을 기억해야 한다.

OPS를 RAC으로 마이그레이션하기

질문 4) 현재 사용 중인 오라클8의 OPS의 결함과 성능상의 문제로 오라클9i RAC으로의 업그레이드를 고려하고 있다. RAC으로 업그레이드시 OPS와 비교했을 때 얻을 수 있는 이점과 RAC 구성을 위한 시스템적 측면은 무엇인지 그리고 효과적인 업무 파티셔닝(partitioning)을 위한 방안은 무엇인지 궁금하다.

먼저 OPS와 RAC을 사용하는 두 가지 이유는 첫번째가 장애 복구(fail over)이고 두번째가 부하 분산(load balancing)을 하기 위한 것이다. 장애 복구를 용이하게 한다는 것은 시스템의 고가용성(high availability)을 확보한다고 할 수 있으며 두 대 이상의 데이터베이스 서버가 정보를 공유(노드간 일관성과 데이터 무결성을 보장)하고 있다가 한 대의 서버에 장애가 발생하여 다운되었을 때 즉각적으로 다른 서버가 동일한 서비스를 할 수 있는 환경 구성이다.

또한 부하 분산이 된다는 것은 하나의 데이터베이스 서버로 처리했을 때 CPU, 메모리 같은 자원이 부족하여 성능이 저하될 수 있으므로 두 대 이상의 서버에 데이터베이스를 구현하여 트랜잭션이 각각의 서버로 분산하여 처리됨으로서 성능이 향상되도록 하는 기능이다.

<그림15> OPS와 RAC의 구성 비교

OPS나 RAC은 모두 고가용성과 부하 분산이 가능하지만 고가용성에 더 큰 목적이 있다. OPS에서 RAC으로 전환했을 때 얻을 수 있는 장점은 캐시 퓨전(Cache Fusion)과 TAF이다. RAC에서 노드간 일관성을 유지하기 위해 사용하는 캐시 퓨전은 클러스터를 구성하는 인스턴스에 있는 데이터 블럭을 고속의 InterProcess Communication (IPC) 인터커넥트를 사용해서 캐시-투-캐시(Cache-to-Cache)로 전송할 수 있도록 하는 기술이다. OPS에서는 오라클 SGA의 버퍼캐시에 있는 Dirty Block을 강제로 디스크에 기록(디스크 I/O 발생으로 성능 저하)하고 이것을 다시 읽어오는 형태로 사용했기 때문에 성능이 저하되어 나타났다. 그러나 RAC에서는 디스크에 기록하는 것과 상관없이 RAC으로 구성된 각 노드의 메모리 영역(버퍼캐시)에서 정보를 읽어 통신하게 되므로 OPS보다 빠르게 처리할 수 있다.

OPS에서 RAC으로 전환했을 때 두번째의 장점은 TAF(Transpar ent Application Failover)의 사용이다. RAC에 TAF 옵션을 이용하는 애플리케이션은 연결한 인스턴스에 장애가 발생하면 다른 RAC 노드로 장애를 복구할 수 있다. 현재는 세션 장애 복구와 SELECT 장애 복구가 지원되고 있다(INSERT, UPDATE, DELETE 지원 안됨). 세션이 연결되어 있고 수행 중에 질의는 Failover 인스턴스에서 장애 전의 상태와 마찬가지로 장애 후에도 연결하여 수행이 가능하다.

다음은 효과적인 업무 파티셔닝 방안에 대해 설명하겠다. RAC에서 업무를 파티셔닝하는 이유는 각 노드간 데이터 일관성 유지를 위해 정보를 공유함으로 인해 잠금(lock) 현상이 발생하는데 동일한 테이블에 대해 데이터의 수정이 발생하면 많은 잠금 현상을 유발한다. 따라서 잠금 현상을 최소화하기 위해 데이터의 수정은 가능하면 한쪽 노드에서 발생시키고 다른 쪽에서는 그것을 읽기로 이용하도록 배치하는 방법을 이용한다.

효과적으로 업무를 파티셔닝할 수 있는 기준은 세 가지 방법이 있다. 첫번째는 대상 업무를 구분할 수 있다면 업무 기능 분해에 의해 각 노드에 업무별로 할당하는 것이다. 예를 들어 인사급여 관리 업무가 가동되고 있다면 인사 기본사항 업무는 노드1에 두고 급여에 관련된 업무는 노드2에 위치시키는 형태이다. 웬만한 시스템은 업무 기능에 의해 노드별 배치를 할 수 있다.

두번째는 대상 업무를 구분할 수 없다면, 즉 하나의 업무 영역이 각 노드에 할당될 때는 상관 매트릭스(CRUD MATRIX)를 그려서 업무간의 밀집도를 파악하여 배치하는 것이 좋은 방법이다. 테이블과 단위 프로세스에 대한 상관 매트릭스를 작성하면 업무적으로 밀집되어 있는 프로세스와 테이블을 한 눈에 파악할 수 있다. 이에 따라 업무를 쪼개어 배치할 수 있다. 업무 단위가 크고 트랜잭션이 많은 경우 이러한 노드별 분산 방법을 하면 좋은데, 업무 단위가 큰 금융권에서 많이 이용한다.

세번째는 시스템에 대한 업무 처리 방식에 따라 구분하는 방법이다. 노드1에는 즉각적으로 응답하는 온라인 업무 처리를 하는 테이블들을 위치시키고 노드2에는 통계성이나 배치 처리가 많이 발생하는 업무 영역을 배치하는 것이다. 공장의 생산 라인에서 초당 수만 건의 데이터를 처리하고 그 데이터의 결함율을 분석하여 모니터링하는 시스템인 생산관리시스템(MES)에서 이 방법을 많이 이용한다. 부하 분산의 중요성보다는 온라인 서비스를 하는 하나의 노드에 대한 장애 대응이 주 목적인 경우이다.

질문 4-1) 업무 파티셔닝과 관련하여 상관 매트릭스 작성에 대해 좀 더 실제적인 예를 들어 설명해주기 바란다.

상관 매트릭스는 작은 업무 그룹인 서브젝트 에어리어 단위로도 나눌 수 있고 아주 세부적으로는 속성 단위까지도 나누어 표현할 수 있다. 가장 많이 사용하는 매트릭스는 엔티티 타입과 단위 프로세스 상관 매트릭스이다. 참고로 단위 프로세스는 논리적인 작업 처리의 단위(logical unit of work)이다. 즉 트랜잭션을 보장하기 위한 단위로서 하나의 트랜잭션의 처리는 업무적으로 의미있어야 한다.

<그림16> 단위 프로세스와 엔티티 타입의 상관 매트릭스

<그림 16>처럼 상관 매트릭스를 표현하기 위해서는 X축과 Y축을 지정한 후 X축에는 단위 프로세스를 나열하고 Y축에는 엔티티 타입을 지정한다. 그리고 각각의 단위 프로세스가 엔티티 타입에 입력이면 C(Create), 조회면 R(Read), 수정이면 U(Update), 삭제이면 D(Delete)를 입력한다. 그림의 간단한 예제에서 보면 대체로 고객과 주문에 관련된 단위 프로세스는 고객 엔티티 타입과 주문 엔티티 타입류에 C, R, U, D가 발생되고 납품 회사에 관련된 단위 프로세스들은 납품 회사, 정산, 입고류에 C, R, U, D가 발생되어 두개의 노드로 분리될 수 있는 기준을 찾을 수 있다.

<그림 16>에서 나타나듯이 각각의 프로세스에 대해 엔티티 타입과 C, R, U, D를 체크하다 보면 엔티티 타입에 밀집도가 분석되고 이 밀집도에 따라 RAC 노드가 두 개이면 밀집도가 높은 엔티티 타입을 묶어 두 개로 나누어 각각의 노드에 배치하면 된다. 물론 <그림 16>처럼 다른 영역의 엔티티 타입과도 C, R, U, D가 발생할 수 있다. 그러나 단위 프로세스가 모든 엔티티 타입에 트랜잭션을 유발하는 것은 아니므로 업무 구분에 따라 대체적으로 밀집도 구분은 가능하게 된다. 밀집도 높은 것을 기준으로 분리하면 각 노드에 트랜잭션이 분리되어 발생되므로 잠금 현상을 경감시킬 수 있게 되는 것이다.

질문 4-2) OPS에서 오라클9i RAC으로의 전환할 때 이미 개발된 프로그램의 변경이 예상된다. 그럴 경우 가장 고려해야 할 사항이 무엇인지 알고 싶다.

OPS와 RAC은 클러스터링 아키텍쳐 구성이 변경됐고 성능이 향상됐지만 근본적으로 처리 방식이 달라지지 않았기 때문에 OPS에서 오라클9i RAC으로의 전환할 때 개발된 프로그램의 소스에서 추가적으로 고려해야 할 사항은 없다. 다만 환경이 OPS에서 RAC으로 변경됐기 때문에 개발된 프로그램이 정상적으로 성능을 발휘하고 있는지 성능 테스트를 하는게 가장 고려해야 할 사항이다.

배치 작업의 성능 이슈와 마스터 테이블 관리

질문 5) 일반적으로 배치 작업은 대량의 데이터를 일괄적으로 처리하기 위한 작업이다. 그래서 여러 배치 작업이 동시에 실행될 때는 자원의 경합을 해결하는 것이 가장 큰 문제라고 생각한다. 본인의 경우에도 현재 업무 중 가장 중요한 배치 업무의 성능 저하로 인해 어려움을 겪고 있는 상황이다.
첫째, 현재 가장 중요한 배치를 처리하는 업무는 주로 1:1 모델링 내용에서 자문했던 그 테이블을 위주로 데이터를 처리한다. 그 중에서도 항상 트랜잭션 테이블을 참조해서 데이터가 있으면 무시하고 없거나 다르면 입력이나 수정하는 작업이 있다 보니 같은 업무(불량 내역)를 처리할 때는 경합이 발생하지 않으나, 서로 다른 업무(불량 내역, 대출 내역)를 동시에 처리하면 경합이 발생하여 배치 작업 시간이 늘어나게 된다. 그래서 마스터 테이블인 트랜잭션 테이블에 대해 경합이 발생하지 않도록 모델을 재설계하는 방법이나 처리 방식 변경 방법에 대해 질문한다.

둘째, 현재 DB 서버에서 SAM 파일 형태의 데이터를 읽어서 Pro*C로 작업을 하다보니 3~5개의 분할된 작업을 돌리면 서버 CPU 사용률이 평소보다 20~30% 정도 증가한다.

DB 서버 부하를 주지 않도록 배치 작업을 다른 서버로 옮겨서 하는 것을 검토하고 있다. 이런 경우에 DB 서버에서 배치 처리가 좋은지 DB 서버와 배치 수행 서버를 분리하는 것이 적절한지에 대해서 알고 싶다.

셋째, 데이터 50~100만 건을 처리하는데 3~5개로 분할해서 처리하는 경우에 시간이 3~6시간 정도가 걸린다. 물론 시스템 리소스의 문제도 있겠지만 배치 처리 대상 테이블에 거의 트리거가 걸려 있다. 이럴 경우 트리거가 배치 처리의 시간이나 시스템 부하에 미치는 영향이 어느 정도인지 알고 싶고, 사후 처리하는 것 역시 하나의 방법이 될 것 같은데 각각의 경우에 대해 고려해야 할 사항을 알고 싶다.

첫번째 질문인 트랜잭션 테이블에 대해서 경합이 발생하지 않도록 모델을 재설계하는 방법이나 처리 방식 변경 방법에 대해 설명하겠다. 데이터 모델을 보면 트랜잭션 테이블이 배치 처리의 대상이 되는 마스터 테이블이다. 그러므로 트랜잭션 테이블을 이용하여 특정 배치 작업이 처리되고 다른 유형의 배치 작업이 수행되면 로우가 겹치지 않으므로 당연히 잠금이 발생하지 않지만 만약 동일한 사람에 대해 불량과 대출에 대한 배치 작업을 처리하다 보면 동일 구조의 거래 식별 테이블을 참조하여 처리해야 하므로 잠금 현상이 발생할 수밖에 없다. 이런 경우는 PK 구조를 바꾸어도 업무적으로 마스터 테이블의 PK를 이용할 수밖에 없으므로 잠금 현상이 발생한다.

이와 같은 경우에 잠금 현상이 발생하지 않고 동시에 처리하기 위해서는 커밋(commit)의 주기를 단축시키고 업무간 배치 처리의 로우가 겹치지 않도록 각각 배치 작업의 정렬(sort)이 배타적으로 되도록 하면 잠금 현상을 최소화할 수 있다. 단 커밋 주기를 단축할 경우는 배치 작업 중간에 에러가 발생하여 전체 롤백이 필요한데, 재처리 로직을 가진 배치 작업이 추가로 필요하다. 많은 프로젝트에서는 이와 같은 방식으로 배치 처리를 하고 있다.

두번째 질문인 DB 서버에서 배치 처리를 하는 것이 좋은지 DB 서버와 배치 수행 서버를 분리하는 것이 적절한지에 대해 설명하겠다. 동일 서버에서 처리하는 방법과 분리하여 처리하는 방법 중 어느 것이 좋다고 단정지어 말하기는 어렵다. 다만 배치 작업의 성능만을 고려한다면 아무래도 동일 서버에서 작업하는 것이 네트워크를 이용하지 않기 때문에 성능면에서 우수하다. 그러나 해당 서버의 자원이 충분하지 않고 배치 작업 중에도 온라인 서비스를 해야 한다면 배치 작업으로 인해 서버 성능이 저하되는 단점이 있다.

데이터가 있는 서버와 로직을 처리하는 서버가 분리되어 네트워크를 이용해 작업을 처리하는 경우에는 온라인 서비스를 하는 데이터베이스 서버에 배치 작업에 의한 부하가 동일 서버에 비해 상대적으로 적어지는 장점이 있다. 반면에 네트워크를 이용하여 배치 작업이 처리되기 때문에 네트워크 환경에 따라 배치 작업의 성능이 저하될 수 있고 네트워크에 문제가 발생할 경우 배치 작업이 영향을 받는 등 안정성이 상대적으로 떨어지는 단점이 있다.

일반적으로는 배치 작업 처리는 동일 서버에서 처리하는 경우가 많다. DW 환경에서도 다른 서버에 있는 배치 작업 대상 데이터 파일을 데이터베이스 서버에 FTP와 같은 형식으로 전송하고 동일 서버에서 로직이 수행되는 배치 프로세스가 가동되는 경우가 많이 있다. 그러므로 배치 작업의 성능과 안정성을 고려하여 가급적 서버의 사양(CPU, 메모리)을 업그레이드하고 배치 작업을 동일 서버에서 처리하도록 하되 업무적으로 사용자가 집중되는 시간(Peak Time)을 피하여 작업하는 것이 좋다.
세번째 질문인 배치 처리 대상 테이블에 트리거가 걸려있는 경우 영향도와 수행 방법에 대해 알아보자. 트리거 성능에 대한 결론부터 말하면 트리거가 수행되는 시간만큼 배치 처리 시간에 영향을 미치고 테이블 각각의 로우에 로직이 수행되므로 CPU 자원 활용률이 많아진다. 따라서 배치 작업 대상 테이블에는 일반적으로 트리거를 이용하지 않도록 한다.

트리거의 목적은 데이터의 동시성을 보장한다는 데 있다. 즉 하나의 테이블에 DML(입력, 수정, 삭제) 작업이 실행되면 동일한 트랜잭션에 의해 트리거 조건에 따라 다른 테이블도 수정하거나 입력해야 하는 작업이 수행되는 경우이다. 만약 A라는 테이블에 데이터를 올바로 입력하고 커밋해도 다른 테이블로 트리거 프로세스가 수행되다가 에러가 발생하면 A에 입력한 데이터도 롤백이 되어 버린다. 데이터의 동시성을 보장하기 위한 것이다. 그러므로 업무적으로 개별 트랜잭션에 따른 동시성을 반드시 보장해야 한다면 성능 문제가 있음에도 불구하고 트리거를 이용할 수밖에 없다. 그러나 배치 작업이 완료된 후에 데이터를 동기화시켜도 무방하다면, 즉 업무적으로 문제없다면 트리거를 이용하지 않는 것이 좋다.

대량으로 데이터를 처리하는 배치 작업의 경우는 보통 배치 작업이 완료된 이후에 다시 배치 처리로 데이터를 동기화하는 경우가 많다. 대신 테이블에 동기화 대상 여부와 같은 기능성 컬럼을 가지고, 배치 작업으로 인해 수정이 발생하면 ‘Y’로 하고 이후에 동기화가 수행되면 ‘N’으로 수정하는 방법을 고려할 수 있다.

질문 5-1) 첫번째 질문에서 잠금 현상을 회피하기 위한 방법으로서 커밋하는 것은 적용 가능한 부분인 것 같다. 그런데 정렬을 하기 위해서는 몇 가지 단계를 거쳐야 하고 소스 파일의 구조도 달라서 쉽지 않을 것 같다.

만약 인위적인 정렬이 어렵다면 반복문(LOOP문) 안에서 SAM 파일을 읽을 때 하나의 작업은 1..전체로우의 순으로 진행하고 다른 작업은 전체로우..1까지 처리하는 방법이 있다. 즉 100개의 로우를 가진 SAM 파일이 있다면 하나의 작업은 for i=1 to 100 { 작업1 } 또 하나의 작업은 for i=100 downto 1 { 작업2 } 형식으로 진행하면 처리되는 데이터가 겹칠 가능성이 적어지므로 잠금 현상을 최소화할 수 있다.

질문 5-2) 마스터 테이블에 대한 변경이 일어나지 않도록 업무별로 서로 다른 TEMP성 마스터 테이블을 가지고 마스터 테이블에 대한 변경 건이 발생하면 TEMP에 넣었다가 배치 작업이 완료된 후에 TEMP 데이터를 마스터에 일괄 반영하는 방법을 고려하고 있다. 이럴 경우 마스터 테이블은 오로지 SELECT만 일어나므로 서로 다른 업무를 동시에 진행할 수 있고 TEMP에 들어간 데이터는 적절한 업무 규칙을 적용하면 되지 않을까 한다.

TEMP에 배치 작업을 처리할 때는 잠금 현상 문제가 발생되지 않지만 TEMP에 들어간 데이터를 다시 마스터 테이블에 반영할 때 순차적으로 처리하지 않고 동시에 처리한다면 이 방법도 역시 동일한 형태의 잠금 현상이 발생할 가능성이 있다. 오히려 배치 작업이 두번 수행되는 형태가 되므로 시스템의 자원을 많이 사용하는 형태가 된다. 그러므로 커밋 주기를 단축시키고 배치 작업간 정렬 순서를 변경해 적용하여 잠금 현상을 피하는 것이 가장 좋은 방법이 될 수 있다.

모델러와 DA의 전망 및 비전

질문 6) 앞으로의 모델러 및 DA의 전망 및 비전은?

데이터를 다루는 데이터 아키텍트(Data Architect, DA)의 전망은 매우 밝다. 9년 전에 IT 기술 분야 중 가장 유망한 분야는 데이터베이스 분야와 네트워크 분야라는 이야기를 들은 적이 있다. 9년이 지난 지금 그 말은 사실이었고 데이터베이스 분야는 기업의 모든 IT 정책의 핵심을 이루고 있다. 지금도 필자가 속해있는 LG CNS에서는 데이터 분야의 기술을 가진 인재들이 여러 프로젝트와 시스템에서 중요한 역할을 수행하고 있다. 이제 유비쿼터스 환경이 되면 더욱 많은 데이터들이 온라인에서 움직이게 될 것이고 따라서 데이터의 중요성과 설계, 구축, 관리 전문가에 대한 요구가 더 늘어날 것이다.

데이터 아키텍트는 실무적으로 분석/설계/구축 이행 분야 및 기업의 업무 데이터를 견실하게 세울 수 있도록 하는 일을 수행한다. 실무 전문성을 확보한 이후에는 감리, 평가, 진단, ISP, ITA, SWAT(긴급 문제 해결) 분야에서 전문 데이터 아키텍트로서 활동할 수 있다. IT 정보 시스템 분야에 있거나 앞으로 이 일을 하기를 원하는 분들은 데이터 아키텍트로서 비전을 가지기 바란다.

데이터 모델의 핵심은 방향 설정

서울의 지하철 중 1호선은 구로역에서 인천 방향과 수원 방향으로 방향이 바뀐다. 인천에 살고 있는 필자가 1년에 한 번 정도 정신없이 수원행을 탄 적이 있다. 잘못 승차한 전철에서 바깥 풍경을 보고 있으면 ‘아차 잘못 탔구나!’ 느낀다. 그러면 가장 가까운 역에서 내려 다시 갈아타야 한다. 계속 머뭇거리고 결정을 내리지 못하면 목적지인 인천에서 더 먼 곳으로 자꾸 이동하게 된다. 데이터베이스의 방향은 데이터 모델이 결정한다. 데이터 모델이 잘못되었다는 것을 인지하면 신속하고 정확하게 방향을 잡아가는 것이 데이터 모델에서 제일 중요한 요소가 된다. 데이터 모델링을 할 때 논리 모델은 업무 중심으로 물리 모델은 데이터베이스 중심으로 전개하는 것을 꼭 잊지 말길 바란다.

출처 : 마이크로소프트웨어[2004년도 7월호]

'SQL' 카테고리의 다른 글

MySQL 1067 에러... (3)	2005.05.20
[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용 (0)	2005.05.13
mysql 4.0.x 이하 버전 .. 한글셋팅.. (0)	2005.02.16
[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용 (0)	2005.02.02
[펌] MySQL의 SQL (0)	2005.01.14

Posted by tornado

mysql 4.0.x 이하 버전 .. 한글셋팅..

SQL 2005. 2. 16. 21:56

자꾸 까먹어서 안되겠음.. 적어놔야지...

[mysqld]
basedir=D:/mysql
default-character-set=euc_kr

euc-kr 이 아니고.. euc_kr 이다!!!!

4.1.x 버전대는 euckr 이다!!!

JDBC 에서의 URL 뒤의 쿼리는

?useUnicode=true&characterEncoding=euc-kr

이거다!!!!

'SQL' 카테고리의 다른 글

[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용 (0)	2005.05.13
[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자 (0)	2005.03.02
[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용 (0)	2005.02.02
[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14

Posted by tornado

[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용

SQL 2005. 2. 2. 13:53

MySQL 4.1.8 RPM 설치시 한글설정.
작성자	아는남자	작성일	2005-01-19 21:06:12	조회수	411

MySQL 4.1.8 RPM 버전으로 인스톨시 기본 언어로 latin_1 이 잡히네요.

설치시에 옵션으로 언어설정이 가능한지, 설치후에 언어설정 변경이 가능한지 궁금합니다.

ALTER DATABASE langtopia DEFAULT CHARACTER SET euckr;

이렇게 변경해도 한글깨지는 현상이 계속됩니다.

아시는분 도움 부탁드립니다... ^^

이 글에 대한 댓글이 총 3건 있습니다.

잘은 몰라두 ..my.ini 에 추가를 함 돼는듯 싶은데요..mysql 설치본은..모든 언어설정이 가능하도록 셋팅돼어 컴파일했다고 알고 있읍니다.

그곳에 추가해보세요..그럼 해결될수도..

이준석(maroon)님이 2005-01-20 10:56:24에 작성한 댓글입니다.

해결했습니다. 메뉴얼 정독이 정답이네요.

mysql> SET character_set_client = euckr;
mysql> SET character_set_results = euckr;
mysql> SET character_set_connection = euckr;
mysql> ALTER DATABASE [DB명] DEFAULT CHARACTER SET euckr;
commit;

이렇게 하면 한글 잘됩니다.

환경은 Linux + MySQL 4.1.8입니다. ^^

아는남자님이 2005-01-20 11:00:42에 작성한 댓글입니다. Edit Ｘ

저도 이 문제 때문에 골치 아팠던 적이 있습니다.

단순하게 set names euckr 을 해주시면 됩니다.

이 명령은 Server, Connection 의 문자셋을 euckr 로 설정합니다.

mysqld 의 시작옵션이나 my.cnf 의 [mysqld] 섹션에 default-character-set = euckr 옵션을 지정하면 Db, Client 의 문자셋이 euckr로 설정됩니다. mysql 프로그램에서 \s 를 해보면 확인할 수 있습니다. set names 대신 [mysql] 섹션에 default-character-set 을 사용할 수 있는데 이렇게 하면 4.1.8에서는 mysqladmin 이 해당 옵션을 이해하지 못해 제대로 실행이 안됩니다. 4.1.9에서는 mysqladmin 프로그램도 default-character-set 옵션을 사용할 수 있도록 변경되었습니다.

문자셋과 관련하여 주의할 점은 일단 latin1 등의 다른 문자셋으로 설정된 필드에 데이터를 입력한 후 alter 명령을 통해 해당 필드의 문자셋을 euckr로 설정하면 무자비하게 깨진다는 것입니다.

create table test (merong varchar(20) collate latin1_general_ci);

이렇게 만들어진 테이블에 한글 데이터를 넣은 후 필드를 euckr 로 변경하려면 다음처럼 해야 합니다.

alter table test modify merong binary(100);

alter table test modify merong varchar(20) collate euckr_korean_ci;

binary 로 바꾸면 문자셋 특성이 사라지기 때문에 이런 변환과정을 거쳐야 합니다(메뉴얼에 의하면). 그냥 바꾸면 문자들이 손상됩니다.

바이너리 배포판에서 기본적으로 설정된 latin1 은 정렬을 위해 latin1_swedish_ci 을 사용하는데 이 경우 '이' 와 '인'이 같은 문자로 인식되는 식이어서 unique 키를 설정하는데 문제가 발생합니다. 이것은 latin1_bin 등의 collation 을 사용하여 해결할 수 있지만 이렇게 하면 영문자의 대소문자가 구분되어 버립니다.

천명재(audwox)님이 2005-01-21 11:36:07에 작성한 댓글입니다.
이 댓글은 2005-01-21 11:40:34에 마지막으로 수정되었습니다

'SQL' 카테고리의 다른 글

[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자 (0)	2005.03.02
mysql 4.0.x 이하 버전 .. 한글셋팅.. (0)	2005.02.16
[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27

Posted by tornado

[펌] MySQL의 SQL

SQL 2005. 1. 14. 12:54

3장 MySQL의 SQL

MySQL 데이터 베이스에서 데이터를 입/출력할 때에는 구조화된 질의 언어(SQL, Structured Query Language)를 사용한다. SQL을 사용하면 데이터를 검색하거나 입력, 수정, 삭제할 수 있다. MySQL과 데이터를 주고받을 때 가장 기본이 되는 도구는 SQL이다. 애플리케이션이나 그래픽 사용자 인터페이스를 통해 데이터베이스를 액세스할 때에도 애플리케이션에서는 SQL을 만들어낸다.

SQL은 일종의 "자연 언어"다. 바꿔 말하면 SQL 선언문은(적어도 겉으로 보기에는) 영어 문장의 구문과 비슷하게 생겼다. 이러한 방법을 사용하면 장점도 있고 단점도 있지만 결국 C, 자바, 펄과 같은 일반 언어와 비슷하다.

SQL 기초

SQL은 규격화된 규칙을 따른다는 점에서 "구조화"된 언어이다. 컴퓨터 프로그램을 이용하면 정형화된 SQL 질의를 쉽게 파싱할 수 있다. 오라일리에서 나온 존 레바인, 토니 메이슨, 더그 브라운의 lex dy yacc, end ed. (오라일리, 1992)에서는 언어를 해석하기 위한 프로그램을 만드는 예로 SQL 문법을 구현하고 있다. 질의(query)는 데이터베이스 서버로 전송되는 규격화된 명령어이며 이 명령어를 통해 원하는 동작을 처리할 수 있다. SQL 질의의 예로 다음과 같은 구문을 들 수 있다:

 SELECT name FROM people WHERE name LIKE 'Stac%'

위의 질의를 보면 SQL 구문은 영어 구문을 조금 이상하게 써 놓은 것과 매우 비슷하다는 사실을 알 수 있다(Select names from a list of people where the names are like Stac). SQL에서는 다른 컴퓨터 언어에서 많이 사용하는 특별한 형식이나 특수 문자를 거의 사용하지 않는다.

SQL 이야기

SQL은 코드(Codd) 박사가 관계형 데이터베이스의 개념을 개발하고 얼마 지나지 않은 1970년대에 IBM에서 처음 탄생했다. SQL은 처음 개발되었을 때부터 강력하면서도 쉽게 배울 수 있는 언어로 제작되었다. 영어처럼 자연 언어와 매우 비슷해서 컴퓨터 기술에 능숙하지 않은 사람들도 쉽게 배울 수 있다. 1970년대에도 그랬듯이 SQL의 이러한 특징은 지금도 큰 장점으로 작용하고 있다.

1970년대 초반에는 소위 말하는 해커가 없었다. BASIC을 배우거나 HTML로 웹 페이지를 만들면서 어린 시절을 보낸 사람이 없었기 때문이다. 컴퓨터 프로그래밍을 하는 사람은 컴퓨터를 속속들이 아는 사람들뿐이었다. SQL은 컴퓨터 기술을 잘 모르는 회계, 비즈니스 및 행정 담당자들도 손쉽게 관계형 데이터베이스를 활용할 수 있도록 하기 위해 개발되었다.

SQL이 컴퓨터 기술자가 아닌 일반인들에게 선풍적인 인기를 끌게 되자 1980년대에 오라클에서는 세계 최초로 대중적으로 사용할 수 있는 상용 SQL 시스템을 출시하기 시작했다. 오라클 SQL은 엄청난 반향을 불러 일으켰고, SQL을 기반으로 하는 산업 전반에 급속하게 퍼져 나갔다. 그 이후로 사이베이스, 인포믹스, 마이크로소프트를 비롯한 여러 회사에서 SQL 기반의 관계형 데이터베이스 관리 시스템(RDBMS)을 내놓게 되었다.

오라클 및 다른 경쟁 회사들이 막 증가하기 시작할 무렵에도 SQL은 비교적 최신 기술에 속했고, 아직 표준이 정해지지 않은 상태였다. 1989년이 되어서야 ANSI 표준 위원회에서 최초의 공식 SQL 표준을 제정하였다. 지금은 그 당시에 발표된 표준안을 SQL89라고 한다. 하지만 그 표준안에서는 SQL 언어의 기술적인 구조를 제대로 정의하지 못했다. 그로 인해 다양한 상용 SQL 언어가 어느 정도 비슷해지긴 했지만 여전히 문법이 조금씩 차이가 나서 서로 다른 시스템을 사용하기가 까다로웠다. 결국 1992년이 되어서야 ANSI SQL 표준이 제대로 정립되었다.

1992년에 발표된 표준안은 SQL92 또는 SQL2라고 한다. SQL2 표준에서는 상업용 소프트웨어에서 사용하는 다양한 확장 기능을 최대한 많이 포함시켰다. 대부분의 교차 DBMS 도구는 관계형 데이테베이스에서 사용하는 방법과 같이 SQL2를 바탕으로 표준화되었다. 하지만 SQL2 표준안에서 워낙 확장성을 중요시했기 때문에 표준을 완벽하게 따르는 관계형 데이터베이스는 너무 복잡하고 자원을 많이 소모한다.

SQL2가 SQL의 최종 표준안은 아니다. 객체 지향 데이터베이스 관리 시스템(OODBMS, Object-Oriented Database Management System)과 객체 관계형 데이터베이스 관리 시스템(ORDBMS, Object-Relational Database Management System)이 인기를 얻으면서 SQL 표준에도 객체 지향 데이터베이스 액세스를 지원해야 할 필요성이 제기되었다. 가장 최근에 나온 SQL3에서는 객체 지향 데이터베이스 액세스를 지원한다.

MySQL에서는 데이터베이스 서버 개발이라는 사업 분야에 새로운 방식으로 접근하였다. 몬티는 대기업 제품에 비해 성능이 떨어지는 새로운 초대형 RDBMS를 만들기보다 자주 쓰이는 SQL 기능만 제공하는 작고 빠른 데이터베이스를 만들기로 했다. 시간이 지나면서 MySQL은 더 많은 기능을 지원하게 되었고, 요즘은 데이터베이스 애플리케이션을 만들 때 필요한 거의 모든 기능을 제공한다.

SQL 설계

앞에서도 말했듯이 SQL은 간단하면서도 확실하게 정의된 명령문 형태의 구조를 가지기 때문에 컴퓨터 언어라기보다는 사람들이 사용하는 언어에 더 가깝다. "질의"라고 하는 SQL 명령은 영어 구문과 마찬가지로 언어 요소별로 나눌 수 있다. 다음과 같은 예를 생각해보자:

 CREATE  TABKE    people (name CHAR(10)) 동사    목적어     형용사구 INSERT INTO people   VALUE ('me') 동사   간접 목적어   직접목적어 SELECT   name         FROM people     WHERE name LIKE '%e' 동사     직접목적어   간접 목적어        형용사구

MySQL을 포함해서 대부분의 SQL에서는 대소문자를 구분하지 않는다. SQL 키워드의 대소문자는 전혀 구분하지 않는다. 위에 있는 CREATE 구문의 예는 다음과 같이 입력해도 된다:

 cREatE TAblE people (name cHAR(10))

하지만 SQL 키워드를 제외한 부분에서는 대소문자르르 구분한다. MySQL에서는 데이터베이스, 테이블 및 열의 이름에서도 대소문자를 구분한다. 하지만 모든 데이터베이스 엔진에서 이런 식으로 대소문자를 구분하지는 않는다. 따라서 데이터베이스에서 사용할 수 있는 애플리케이션을 만들고 싶다면 모든 이름에 사용하는 대소문자를 확실히 구분하는 것이 좋다.

SQL 질의는 반드시 동사로 시작한다. 이 동사는 데이터베이스 엔진에서 취할 동작을 나타낸다. 선언문의 나머지 부분은 동사에 따라 다르지만 항상 정해진 형식으로 따른다. 동작을 취할 대상의 이름을 적은 다음 그 동작에서 사용할 데이터를 기술한다. 예를 들어, CRETE TABLE people (name CHAR(10))이라는 문장에서는 CREATE라는 동사를 사용하고, 그 뒤에 TABLE이라는 목적어가 온다. 질의의 나머지 부분에는 새로 만들 테이블에 대한 설명이 들어간다.

SQL 질의는 클라이언트(사용자가 데이터베이스와 데이터를 주고받는 창구같은 역할을 하는 애플리케이션)에서 입력한다. 클라이언트에서는 사용자의 동작에 따라 질의를 구성한 다음 그 질의를 SQL 서버에 보낸다. 서버에서는 그 질의를 처리하고 주어진 동작을 수행한다. 서버에서 할 일을 끝마치면 어떤 값을 클라이언트에 보낸다.

SQL의 주된 목적은 데이터베이스 서버에 어떤 동작을 전달하는 것이기 때문에 범용 언어만큼 유연성이 뛰어나지는 않다. SQL의 거의 모든 기능은 데이터 추가, 변경, 읽기와 같은 데이터베이스의 입출력과 연관되어 있다. 다른 기능도 어느 정도 제공하지만 항상 데이터베이스에 있는 데이터를 조작하는 방법에 주안점을 둔다.

MySQL에 SQL을 보내는 방법

MySQL에 SQL을 보낼 때는 다양한 방법을 사용할 수 있다. 그 중 3부에서 다루는 프로그래밍 API를 경유하는 방법을 가장 많이 사용한다. 하지만 이 장에서는 SQL을 배우는 것이 목적이므로 대화식 명령행 도구인 mysql을 사용하기로 하자. 명령행에서 이 프로그램을 실행시키면 SQL을 받아들일 수 있는 프롬프트가 나타난다:

 [09:04pm] carthage$ mysql -u root -p Enther password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 to server version: 3.22.29 Type 'help' for help. mysql>

위에 나와 있는 'mysqwl -u root -p'는 루트 사용자로(-u 옵션) 비밀번호를 입력하도록 하여 (-p 옵션) MySQL 서버에 연결하라는 명령이다. 원격 호스트에 있는 MySQL 서버에 연결할 때에는 -h 옵션을 사용한다:

 [09:04pm] carthage$ mysql -u root -h db.imginary.com -p

운영체제의 계정과 MySQL의 계정은 서로 독립적이다. 즉 MySQL용 사용자 목록이 따로있고 MySQL이 있는 호스트와는 별개로 MySQL 관리자가 별도로 새로운 사용자를 추가해야 한다. 따라서 처음 MySQL을 설치했을 때에는 루트 외에는 다른 사용자가 전혀 없다. 여기에서 루트는 유닉스 루트 게정과는 다르다. 일반적으로 데이터베이스 관리 작업을 하는 경우를 제외하면 절대 MySQL에 루트로 접속하면 안 된다. 새로 설치한 MySQL이 있고, 그 MySQL을 독자가 임의대로 쓸 수 있다면 루트로 접속해서 이 장에 나와 있는 데이터베이스를 만들고 지우는 예제를 실행해도 된다. 그렇지 않다면 항상 자신에게 할당된 계정으로 MySQL에 접속해야 한다.

mysql을 실행하고 나면 SQL 명령을 한 줄에 모두 입력해도 되고, 여러 줄에 걸쳐서 입력해도 된다. MySQL에서는 세미콜론이 입력될 때까지 SQL을 실행하지 않는다:

 mysql> SELECT book_number    -> FROM book    -> ; +-------------+ | book_number | +-------------+ |           1 | |           2 | |           3 | +-------------+ 3 rows in set (0.00 sec)

클라이언트 도구를 컴파일한 방법에 따라 mysql 명령행에서 명령 히스토리를 사용할 수 있다. mysql 클라이언트를 만들 때 명령 히스토리 기능을 포함시켜서 컴파일했다면 키보드의 위, 아래 화살표로 앞에서 실행시킨 SQL 명령을 다시 사용할 수 있다.

데이터베이스 생성

MySQL을 사용하려면 데이터베이스를 만들어야 한다. 우선 SHOW DATABASES 명령으로 처음 설치할 때 만들어진 데이터베이스를 살펴보자. MySQL 3.23.40을 설치하고 나면 다음과 같은 테이블이 생성된다:

 mysql> SHOW DATABASES; +-------------+ |     Database| +-------------+ |        mysql| |         test| +-------------+ 2 rows in set (0.37 sec)

첫 번째 데이터베이스인 mysql은 MySQL의 시스템 데이터베이스이며, 자세한 내용은 5장에 나와 있다. 두 번째 데이터베이스인 test는 MySQL을 배우거나 테스트할 때 쓸 수 있는 테스트용 데이터베이스이다. MySQL이 원래 깔려 있었다면 다른 데이터베이스가 들어있을 수 있다. 우선 MySQL의 CREATE 선언문을 사용하는 법을 알아보기 위해 새로운 데이터베이스를 만들어 보자:

 CREATE DATABASE TEMPDB;

그리고 새로운 데이터베이스인 TEMPDB로 작업을 하자:

 USE TEMPDB;

DROP DATABASE 명령을 내리면 데이터베이스를 삭제할 수 있다:

 DROP DATABASE TEMPDB;

방금 해 본 것과 같이 CREATE 선언문을 이용하면 새로운 대상을 만들 수 있고 DROP 선언문을 이용하면 삭제할 수 있다.

테이블 관리

지금까지 MySQL 서버에 있는 데이터베이스에 연결하는 방법을 알아보았다. 이제 MySQL을 설치했을 때 기본으로 들어 있는 test 데이터베이스 또는 사용자가 만든 실습용 데이터베이스를 사용하여 실습해 보자. 데이터베이스의 목록을 출력할 때와 마찬가지로 SHOW 명령어로 현재 데이터베이스에 들어 있는 테이블의 목록을 출력할 수 있다. mysql 시스템 데이터베이스에 연결된 상태에서 SHOW TABLES 명령을 내리면 다음과 같은 화면이 출력된다:

 mysql> USE mysql; Database changed mysql> SHOW TABLES; +-------------------+ |    Tables_in_mysql| +-------------------+ |       columns_priv| |                 db| |               func| |               host| |        tables priv| |               user| +-------------------+ 6 rows in set (0.00 sec)

위에 있는 여섯 개의 테이블은 MySQL이 동작하기 위해 필요한 시스템 테이블이다. 이 테이블 중 특정 테이블의 구성을 알고 싶다면 DESCRIBE 명령을 사용하면 된다:

 mysql> DESCRIBE db; +----------------------------------------------------------------------+ |Field           |Type           |Null |Key |Default   |Extra   |      | +----------------------------------------------------------------------+ |Host            |char(60) binary|     |PRI |          |        |      | |Db              |char(64) binary|     |PRI |          |        |      | |User            |char(16) binary|     |PRI |          |        |      | |Select priv     |enum('N', 'Y') |     |    |N         |        |      | |Insert priv     |enum('N', 'Y') |     |    |N         |        |      | |Update priv     |enum('N', 'Y') |     |    |N         |        |      | |Delete priv     |enum('N', 'Y') |     |    |N         |        |      | |Create priv     |enum('N', 'Y') |     |    |N         |        |      | |Drop priv       |enum('N', 'Y') |     |    |N         |        |      | |Grant priv      |enum('N', 'Y') |     |    |N         |        |      | |References priv |enum('N', 'Y') |     |    |N         |        |      | |Index priv      |enum('N', 'Y') |     |    |N         |        |      | |Alter priv      |enum('N', 'Y') |     |    |N         |        |      | +----------------------------------------------------------------------+ 13 rows in set (0.36 sec)

13 rows in set (0.36sec) 위의 결과를 보면 테이블에 있는 각 열이 나오고, 그 열에 널 값이 들어 있는지 여부, 키의 종류, 기본값, 추가 정보 등이 나와 있다. 이게 무슨 소린지 잘 모르는 독자들도 있겠지만, 앞으로 모두 배우게 될 내용이니 걱정하지 않아도 된다.

이제 테이블을 직접 만들어 보자. 우선 MySQL을 처음 설치하면 자동으로 생성되는 test 데이터베이스에 접속하자:

 USE test;

mysql에는 새로운 테이블을 추가하면 안 되기 때문에 test 테이터베이스에 우선 접속해야 한다는 점에 주의하도록 하자. 테이블(table)은 데이터를 구조적으로 저장하기 위한 컨테이너이며 관계형 데이터베이스에 있어서 가장 기본적인 개념이다. 테이블에 데이터를 추가하기 전에 데이블의 구조를 정의해야 한다. 다음과 같은 레이아웃을 생각해 보자:

 +------------------------------+ |          people              | +------------------------------+ | name     | char(10) not null | +------------------------------+ | address  | text(100)         | +------------------------------+ | id       | int               | +------------------------------+

테이블에는 열 이름뿐 아니라 각 필드의 유형과 그 필드에 들어갈 수 있는 추가 정보까지 포함된다. 필드의 데이터 유형은 그 필드에 저장할 수 있는 데이터 필드의 종류를 나타낸다. SQL의 데이터 유형은 다른 프로그래밍 언어의 데이터 유형과 비슷하다. SQL 표준에서는 매우 광범위한 데이터 유형을 사용할 수 있도록 되어 있다. MySQL에서는 SQL 표준에 있는 거의 모든 유형 외에도 MySQL에만 있는 몇 가지 데이터 유형을 지원한다.

테이블을 만들 때 사용하는 일반적인 문법은 다음과 같다:

 CREATE TABLE table_name (  column_name1 type [modifiers]  [, column_name2 type [modifiers]] )

사용할 수 있는 식별자(identifier, 테이블이나 열의 이름)는 DBMS에 따라 조금씩 다르다. MySQL에서는 식별자로 64자까지 사용할 수 잇으며, $ 문자도 사용할 수 있고 숫자로 시작해도 된다. 또한 MySQL이 설치된 시스템에서 사용할 수 있는 문자 세트에 포함된 모든 문자를 식별자로 사용할 수 있다.

열(column)은 테이블에 있는 행을 위한 개별 데이터의 단위이다. 테이블에 들어갈 수 있는 열의 개수에는 제한이 없지만 열이 너무 많아지면 효율이 떨어진다. 이런 면에서 7장에서 다루는 좋은 데이터베이스 설계 방법이 중요하다. 정규화된 테이블을 잘 만들면 여러 개의 테이블에서 저장된 테이터를 연결하여 검색할 수 있다. 연결(join)에 대한 것은 이 장의 후반부에 다룬다.

다음과 같은 CREATE 선언문을 생각해 보자:

 CREATE TABLE USER (  USER_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,  USER_NAME CHAR(10) NOT NULL,  LAST_NAME VARCHAR(30),  FIRST_NAME VARCHAR(30),  OFFICE CHAR(2) NOT NULL DEFAULT 'NY');

이 선언문은 USER_ID, USER_NAME, LAST_NAME, FIRST_NAME, OFFICE라는 다섯 개의 열이 있는 USER라는 테이블을 만든다. 각 열 이름 뒤에는 그 열의 데이터 유형이 나오고 그 뒤에 다른 변경자(modifier)가 붙는다.

NOT NULL 변경자는 그 열에 널 값이 들어갈 수 없다는 것을 의미한다. 그 열에 널 값을 대입하려고 하면 SQL에서 에러가 난다. 하지만 여기에는 두 가지 예외가 있다. 첫 번째는 어떤 열을 AUTO_INCREMENT로 정의한 경우인데 이 때는 널값을 대입하면 새로운 값이 자동으로 생성된다(자동 증가는 잠시 후에는 알아보기로 하자). 두 번째는 위에 나온 OFFICE 열에서처럼 열에 기본값을 지정하는 경우인데 이 때는 널 값을 대입하면 기본값(위의 경우에는 NY)이 대입된다(데이터 유형과 PRIMARY KEY 변경자에 대한 내용은 잠시 후에 다루겠다).

언제나 그렇듯이 삭제하는 것은 만드는 것보다 쉽다. 데이터베이스에서 테이블을 삭제하는 명령은 다음과 같다:

 DROP TABLE table_name

위와 같은 명령을 사용하면 데이터베이스에서 table_name이라는 이름을 가진 테이블이 흔적도 없이 사라진다. 주어진 테이블에 있는 모든 데이터는 남김 없이 삭제된다. 일단 지우고 나면 테이블을 미리 백업해두지 않은 이상 절대로 데이터를 살릴 수 없다. 따라서 항상 데이터를 백업해 두고 테이블을 삭제할 때는 신중을 기해야 한다. 데이터를 백업해 두는 습관을 길러 두면 언젠가는 반드시 크게 도움이 될 것이다.

MySQL에서는 테이블 이름을 쉼표로 구분하여 입력하면 여러 개의 테이블을 한꺼번에 삭제할 수 있다. 예를 들어, DROP TABLE people, animals, plants라는 명령을 사용하면 people, animals, plants라는 세 개의 테이블이 지워진다. 또한 삭제하고자 하는 테이블이 원래 없는 경우를 대비하여 IF EXISTS 변경자를 사용할 수도 있다. 이 변경자는 데이터베이스와 그 안에 들어갈 모든 테이블을 한꺼번에 만드는 복잡한 스크립트를 만들 때 많이 쓰인다. 이러한 스크립트에서는 데이터베이스를 만들기 전에 DROP TABLE IF EXISTS table_name이라는 명령을 사용한다.

MySQL 데이터 유형

테이블의 각 열에는 유형이 있다. 앞에서 언급했다시피 SQL의 데이터 유형은 일반적인 프로그래밍 언어의 데이터 유형과 비슷하다. 보통 컴퓨터 언어에서는 완결성을 위한 최소한의 유형만 정의하지만 SQL에서는 일반 사용자들의 유용하게 쓸 수 있도록 DATE와 같은 유형들을 추가로 제공한다. DATE 유형을 그냥 숫자 유형으로 저장할 수도 있지만, 날짜 처리를 위한 유형을 따로 만들어 두면 SQL을 더 쉽게 사용할 수 있다(사용의 용이성은 SQL의 핵심 목표중 하나다).

MySQL에서 지원하는 SQL 유형에 대한 자세한 내용은 16장에서 나와 있다. [표 3-1]에서는 가장 흔하게 쓰이는 유형을 간략하게 모아놓았다.

[표 3-1] MySQL에서 자주 사용하는 데이터 유형 (전체 목록은 16장 참조)

데이터 유형	설명
INT	정수값. MySQL에서 INT는 부호가 있을 수도 있고 없을 수도 있다.
REAL	부동수수점 값. 이 유형은 INT 유형에 비해 범위도 더 넓고 유효숫자의 수도 많지만 INT처럼 정확하지는 않다.
CHAR(length)	길이가 고정된 문자 값. CHAR 필드에서는 length 값으로 정해진 길이 이상의 문자열을 저장할 수 없다. 필드의 길이가 짧으면 남는 부분은 공백으로 채워진다. SQL에서 가장 많이 쓰이는 유형.
VARCHAR(length)	길이가 바뀔 수 있는 문자 값
TEXT(length)	길이가 바뀔 수 있는 문자 값
DATE	표준 날짜 값. DATE에는 과거, 현재, 미래의 임의의 날짜를 저장할 수 있다.
TIME	표준 시간 값. 이 유형에는 날짜와 상관 없이 시각을 저장 할 수 있다. 날짜와 함께 사용하면 특정한 날짜의 특정한 시각을 저장할 수 있다. MySQL에서는 한 필드에 날짜와 시각을 동시에 저장할 수 있도록 DATETIME이라는 유형을 지원한다.

MySQL에서는 모든 숫자 유형에서 UNSIGNED 속성을 지원한다. 이 변경자를 사용하면 그열에 (부호는 없는) 양수만 저장할 수 있다. 부호가 없는 필드의 최대값은 부호가 있는 필드의 최대값의 두 배다. 예를 들어, 부호가 없는 TINYINT(MySQL의 1바이트 숫자 유형)의 범위는 0 에서 255까지이고, 부호가 있는 TINYINT의 범위는 -128에서 127까지이다. MySQL에서는 [표 3-1]에 정리한 것보다 더 많은 유형을 지원한다. 하지만 일상적인 프로그래밍에서는 위에 나와 있는 유형을 가장 많이 사용할 것이다. MySQL 테이블을 설계할 때에는 저장하고자 하는 데이터의 크기에 알맞은 유형을 선택하는 것이 좋다.

숫자 유형

테이블을 만들기 전에 먼저 테이블에 저장할 테이터의 종류를 알아야 한다. 데이터가 문자 기반인지 숫자 기반인지를 결정하고 나면 저장할 데이터의 대략적인 크기를 알아야 한다. 숫자 필드라면 최대, 최소값이 얼마인지, 나중에 바뀔수 있는지 등을 고려해야 한다. 최소값이 0 이상이라면 부호가 없는 유형을 사용하는 것이 좋다. 될 수 있으면 사용하게 될 최대값을 지원하는 유형 중 가장 작은 숫자 유형을 사용해야 한다. 예를 들어, 어떤 주의 인구를 나타내튼 필드가 있다면 부호가 없는 INT 필드를 사용하면 된다. 인구 수가 음수인 경우는 없고, 그 주에 사는 사람들의 수가 전 세계 인구수에 육박하지 않는 이상 한 주의 인구를 나타내기에는 부호가 없는 INT 필드로도 충분하기 때문이다.

문자 유형

문자 유형은 조금 더 복잡하다. 문자열의 최소 및 최대 길이뿐만 아니라 길이의 평균 및 편차도 감안해야 한다. 인덱스(index)는 검색하고자 하는 필드 또는 필드의 조합이다(WHERE 절에 들어갈 필드라고 생각하면 된다). 인덱싱은 사실 훨씬 복잡하기 때문에 나중에 자세히 다루기로 하겠다. 일단 여기에서 중요한 것은 필드의 길이가 고정되어 있을 때 문자 필드에 대한 인덱싱이 가장 잘 동작한다는 점이다. 문자 필드의 길이에 편차가 거의 없을 때(가능하다면 아예 없을 때)에는 CHAR 유형이 가장 좋다. CHAR 필드로 사용할 수 있는 좋은 예로 국가 코드를 들 수 있다. ISO에서는 국가 코드를 나타내는 두 글자로 표기되는 표준을 정해 놓았다(미국은 US, 프랑스는 FR 등). 이렇나 코드는 항상 두 글자이므로 ISO 표기법을 따르는 국가 코드를 저장할 때에는 CHAR(2)를 사용하는 것이 최선의 방법이다.

CHAR 필드에 저장되는 값의 길이가 항상 일정해야 하는 것은 아니다. 하지만 편차가 아주 작아야 한다. 예를 들어, 전화번호는 나라마다 조금씩 다르긴 하지만 CHAR(13)에 저장해도 문제가 되지 않는다. 편차가 충분히 작기 때문에 전화번호 필드를 굳이 길이가 바뀔 수 있는 필드로 만들지 않아도 된다. CHAR 필드를 사용할 때에는 저장되는 문자열의 길이가 아무리 크더라도 필드의 크기로 정해진 문자의 개수만큼의 공간을 차지한다는 점을 기억해 두자(절대로 그 이상, 그 이하의 공간을 차지하지 않는다). 저장되는 텍스트의 길이와 필드의 길이가 차이가 난다면 남는 부분은 공백으로 채워진다. 전화번호 데이터에서 조금 공간이 남아도는 게 큰 문제가 안 될지 몰라도 너무 많은 공간을 낭비하는 것은 좋지 않다.

가변 길이(variable-length) 텍스트 필드는 길이가 크게 달라지는 텍스트 필드에 적합하다. 가변 길이 데이터 유형이 필요한 필드의 대표적인 예로 웹 URL을 들 수 있다. 대부분의 웹 주소는 비교적 짧기 때문에(예를 들면, http://www.ora.com이나 http://www.imaginary.com, http://www.mysql.com 등) 별 문제가 없지만 다음과 같이 긴 웹 주소가 등장할 때가 있다:

 http://www.innekw.com/wkwekekkkkkkkkkkkkkkkkkkkkkkkkkkkkjre

이렇게 긴 URL도 저장할 수 있을 만큼 큰 CHAR 필드를 만들면 다른 짧은 URL을 저장 할 때에는 너무 많은 공간을 낭비하게 된다. 하지만 가변 길이 필드를 사용하면 짧은 값을 저장할 때에는 공간을 낭비하지 않고, 위와 같이 긴 텍스트 값도 저장할 수 있다.

MySQL의 길이를 바꿀 수 있는 텍스트 필드는 필드에 값을 저장하는데 필요한 공간만을 차지한다. 예를 들어, VARCHAR(225) 필드에 "hello world"라는 문자열을 저장하더라도 12바이트(한 글자가 1바이트씩 차지하고, 나머지 1바이트에 길이를 저장한다)만 차지한다.

MySQL에서는 ANSI 표준과 다르게 VARCHAR 필드에서 남는 자리에 공백 문자를 채우지 않고, 값을 저장하기 전에 남는 공백을 모두 삭제한다.

지정한 필드 길이보다 긴 문자열을 저장할 수는 없다. VARCHAR(4) 필드를 만드었다면 최대 네 글자 길이의 문자열만 저장할 수 있다. 만약 "happy birthday"라는 문자열을 그 필드에 저장하려고 하면 MySQL에서는 "happ"까지만 저장한다. 이렇게 원래 정해 놓은 필드 크기를 벗어나는 문자열을 저장할 수 없다는 단점이 있다. [표 3-2]는 각 테스트 데이터유형으로 앞에 나왔던 144 길이의 와인 감별사 사이트 URL과 일반적인 30자 길이의 URL을 저장할 때 필요한 저장 공간 그리고 각 유형의 최대 문자열 길이를 정리해 놓은 표다.

[표 3-2] 여러 가지 MySQL 문자 유형에서 필요한 저장 공간

데이터 유형	144자 문자열	30자 문자열	최대 문자열 길이
CHAR(150)	`150`	`150`	`255`
VARCHAR(150)	`145`	`31`	`255`
TINYTEXT(150)	`145`	`31`	`255`
TEXT(150)	`146`	`32`	`65535`
MEDIUMTEXT(150)	`147`	`33`	`16777215`
LONGTEXT(150)	`148`	`34`	`4294967295`

이 표를 보면 길이를 바꿀 수 있는 유형인 MEDIUMTEXT와 LONGTEXT에서는 데이터을 저장하는데 필요한 공간이 각각 1, 2 바이트씩 더 필요하다는 것을 알 수 있다. 이는 필드의 크기를 저장하는데 각각 1바이트씩 공간이 더 필요하기 때문이다. TEXT에서는 문자열 길이를 저장하는 데 1바이트로는 모자라기 때문에 VARCHAR보다 한 바이트가 더 필요하고 마찬가지 이유로 MEDIUMTEXT, LONGTEXT로 갈수록 2바이트, 3바이트 씩의 공간이 더 있어야 한다.

데이터베이스를 사용하다 보면 처음에는 VARCHAR(25) 만으로도 충분했던 필드 크기를 더 늘려야 할 때도 있는데, 이런 문제는 그리 어렵지 않게 해결할 수 있다. MySQL에서는 ALTER TABLE이라는 명령을 지원하기 때문에 이 명령을 이용하여 데이터 손실 없이 필드 유형을 재정할 수 있다:

 ALTER TABLE mytable MODIFY mycolumn LONGTEXT

이진 데이터 유형

MySQL에서는 문자 유형과 매우 유사한 이진 데이터 유형을 제공한다. MySQL의 이진 유형에는 CHAR, BINARY, VARCHAR, BINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB이 있다. 문자 유형과 그에 상응하는 이진 유형간의 실질적인 차이점은 인코딩이라는 개념에서 찾을 수 있다. 이진 데이터(binary data)은 기본적으로 MySQL에서 해석하지 않는 데이터 조각이다. 하지만 문자 데이터는 사람들이 사용하는 글자로 이루어진 텍스트 형식의 데이터를 나타낸다. 따라서 문자 데이터는 해당 문자 세트에 알맞은 규칙을 바탕으로 인코딩되고 정렬된다. ASCII 시스템에서 문자 데이터를 정렬할 때에는 대소문자를 구분하여 ASCII 순서대로 정렬한다.

열거 및 집합

그 밖에도 MySQL에서 제공하는 특수 유형이 있다. ENUM 유형(열거 유형)을 이용하면 테이블을 만들 때 그 필드에 들어갈 수 있는 값들의 목록을 지정할 수 있다. 예를 들어 apple, orange, kiwi, banana 중 하나만 들어갈 수 있는 fruit라는 열이 있는데, 이 열의 유형을 ENUM으로 한다고 가정해 보자:

 CREATE TABLE meal (meal_id INT NOT NULL PRIMARY KEY,                fruit ENUM('apple', 'orange', 'kiwi',                          'banana'))

이 열에 어떤 값을 넣는다면 그 값을 넣는다면 그 값을 위에 열거되어 있는 과일 이름 중 하나가 되어야 한다. MySQL에서는 미리 이 열에 들어갈 수 있는 값들을 알고 있기 때문에 문자열 형태가 아닌 숫자 형태로 저장한다. 즉 apple을 열에 대입할 때 문자열이 아니라 내부적으로 한 바이트자리 숫자로 바꿔서 저장한다. 하지만 MySQL에서 그 값을 읽어들이거나 질의에서 사용할 때에는 그냥 apple이라고 표기하면 된다. 또한 테이블에서 결과를 보거나 테이블을 호출할 때에도 apple을 사용하면 된다.

MySQL의 SET 유형(집합 유형)도 똑같은 방식으로 작동하지만 하나의 필드에 여러 값을 동시에 저장할 수 있고 바이트 대신 비트를 사용한다는 점이 다르다.

기타 데이터 유형

모든 데이터는 숫자 또는 문자 유형으로 저장할 수 있다. 사실 숫자도 문자 유영으로 저장할 수 있다. 하지만 그렇게 할 수 있다고 해서 꼭 그렇게 해야 하는 것은 아니다. 예를 들어, 데이터베이스에 날짜를 저장하는 것을 생각해 보자. 그 값을 유닉스 형식의 BIGINT로 저장할 수도 있고 날짜, 월, 연도를 저장하기 위한 열을 따로 만들 수도 있다. 하지만 그런 경우에 특정한 날짜보다 이틀 이상 이전의 날짜가 들어 있는 열을 찾으려면 어떻게 해야 할까? 그 날짜를 숫자로 표시해 놓은 값을 직접 계산하거나 날짜, 월, 연도 값을 모두 고려하는 아주 복잡한 연산을 처리해야 할 것이다.

실제 프로그래밍을 할 때 이렇게 하면 정말 골치 아프다. MySQL에서 이런 문제를 모두 해결해 줄수는 없을까? 다행히도 MySQL에는 해결책이 있다. MySQL에서는 일상적인 개념을 데이터로 표현할 수 있도록 여러 가지 복합 데이터 유형을 제공한다. 날짜의 개념을 DATE 데이터 유형을 통해 지원한다. 이와 비슷한 것으로 DATETIME과 TIMESTAMP가 있다.

인덱싱

MySQL은 다른 대형 데이터베이스 서버보다 속도가 훨씬 빠르지만, 데이터베이스를 설계할 때에는 몇가지 문제를 고려해야 한다. 예를 들어, 행의 개수가 수백만 개인 테이블을 사용한다면 하나의 행을 찾는 데에도 꽤 오랜 시간이 걸릴 수 있다. 대부분의 데이터베이스 엔진에서는 빠른 검색을 위해 인덱스를 사용한다.

인덱스는 데이터베이스에서 데이터를 저장할 때 더 빠르게 검색할 수 있도록 도와주는 역할을 한다. 이렇게 검색 속도를 향상시키려면 어쩔 수 없이 디스크 공간과 데이터 수정 속도 면에서 약간의 희생을 감수해야 한다. 인덱스의 효율을 높이고 싶다면 가장 많이 검색하는 열의 인덱스를 만드는 것이 좋다. 데이블의 인덱스를 만들 때에는 다음과 같은 구문을 사용한다:

 CREATE INDEX index_name ON tablename (column1, column2, ..., columnN)

다음과 같이 하면 테이블을 처음 만들 때 인덱스도 같이 만들 수도 있다:

 CREATE TABLE masterial (id INT NOT NULL,                        name CHAR(50) NOT NULL,                        resistance INT,                        melting_pt REAL,                        INDEX index1 (id, name),                        UNIQUE INDEX index2 (name))

이 예에서는 테이블에 두 개의 인덱스를 만든다. 첫 번째 인덱스(index1)는 id와 name 필드로 구성된다. 두 번째 인덱스에는 name 필드만 포함되며 name 필드의 값은 반드시 유일해야 한다고 되어 있다. 이미 데이터베이스의 다른 행의 name 열에 들어 있는 값과 같은 name 값을 가진 필드를 새로 삽입하면 에러가 난다. 보통 유일 인덱스(UNIQUE INDEX)에 포함된 필드는 NOT NULL로 선언해야 한다.

name 자체의 인덱스는 만들었지만 id의 인덱스는 별도로 만들지 않았다. id만으로 구성된 인덱스가 필요하다면 굳이 따로 만들 필요가 없다. 이미 만들어져 있기 때문이다. 인덱스에 두 개 이상의 열이 포함된다면(예를 들어, name, rank, serial_number 같은 식으로) MySQL에서는 그 열들을 왼쪽에서 오른쪽으로 가는 열의 부분집합이 메인 인덱스 안에 자동으로 생성된다. 예를 들어, name, rank, serial_number 인덱스를 만들면 name 자체와 name, rank로 구성되는 인덱스가 자동으로 만들어진다. 하지만 rank만으로 또는 name과 serial_number로 이루어지는 인덱스는 따로 만들어야 한다.

MySQL에서는 기본키(primary key)라는 특별한 인덱스에 대한 ANSI SQL 문법도 지원한다. MySQL에서 기본키는 PRIMARY라는 이름이 붙은 유일한 키이다. 어떤 열을 기본키로 선언하면 이 열을 테이블 연결을 지원하는 지원하는 유일 인덱스가 된다. 다음은 id를 기본키로 하여 cities라는 테이블을 만드는 예이다:

 CREATE TABLE cities (id INT NOT NULL PRIMARY KEY,                    name VARCHAR(100),                    pop MEDIUMINT,                    founded DATE)

테이블을 만들기 전에 우선 키를 지정할 계획이라면 어떤 필드를 키로 지정할지 결정해야 한다. 앞에서 말했듯이 연결을 지원할 필드를 기본키로 지정하는 것이 좋다. 테이블을 만들 때 적절한 기본키를 선정하는 방법은 7장에서 자세하게 알아보자.

ANSI SQL에서는 외부키(foreign key)라는 특별한 키를 지원한다. 외부키는 다른 테이블에 의존적인 관계를 가진 행의 삭제와 같은 작업을 데이터베이스에 관리할 수 있게 함으로써 데이터베이스의 무결성을 보호하는 도움을 준다.

MySQL에서도 외부키에 대한 ANSI 문법을 지원하긴 하지만 데이터베이스의 무결성을 검사할 때에 실제 그 기능을 사용하지는 않는다. 이 기능은 속도만 느리게 만들고 실절적인 장점이 거의 없기 때문이다. 일반적으로 외부키 무결성은 애플리케이션 자체에서 신경을 쓰도록 하는 편이 좋다.

데이터 관리

데이블을 새로 만들고 나서 가장 먼서 할 일은 데이터를 추가하는 것이다. 데이터를 집어넣고 나면 테이블을 관리(추사, 수정 및 삭제)해야 한다.

삽입

테이블에 행을 추가하는 방법은 비교적 간단하다. 이미 앞에서도 몇 번 해 본 작업이다. 우선 SQL 표준 INSERT 문법을 사용하는 방법이 있다:

 INSERT INTO table_name (column1, column2, ..., columnN) VALUES (value1, value2, ..., valueN)

이 문법에서는 열의 이름을 지정한 다음 그 열에 채울 값들을 순서대로 입력한다. 숫자 필드에 데이터를 삽입할 때에는 그대로 사용하면 되지만 다른 필드에 들어갈 값들은 작은 따옴표로 감싸야 한다. 예를 들어, 주소록 테이블에 새로운 행을 삽입할 때에는 다음과 같은 식으로 하면 된다:

 INSERT INTO addresses (name, address, city, state, phone, age) VALUES ('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',      '(800) 555-1234', 26)

작은 따옴표나 이스케이프 문자 자체를 표시할 때에는 이스케이프 문자(기본값은 )를 앞에 덧붙이면 된다:

 # c:PersonalStacie 에 있는 Stacie's Directory라는 # 디렉토리에 대한 정보를 넣는다. INSERT INTO files (description, location) VALUES ('Stacie 's Directory', 'C:\Personal\Stacie')

MySQL에서는 모든 값을 테이블을 만들 때 사용한 CREATE 명령에서 지정한 열 순서대로 값을 입력할 때에는 열 이름을 생략해도 된다. 하지만 어떤 열에 기본값이 있다면 기본값이 아닌 테이더를 입력하고자 하는 열의 이름은 확실히 지정해야 한다. 예를 들어, 앞에 나온 files 테이블에 size라는 열이 있었다면 그 열에는 Stacie's Directory가 아닌 기본값이 적용된다. 기본값은 테이블을 만드는 CREATE 명령에서 지정할 수 있다. NOT NULL 이면서 기본값을 지정하지 않은 열이 있다면 INSERT 선언문에 그 열을 반드시 포함시키고 NULL이 아닌 값을 넣어야 한다.

MySQL 최근 버전에서는 한꺼번에 여러 행을 삽입할 수 있는 비표준 INSERT 호출도 지원한다:

 INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),                        (NULL, 'Bananas', 122, 0, 4, 29),                        (NULL, 'Liver', 232, 3, 15, 10)

간단한 시스템 관리에는 MySQL에서 지원되는 비표준 문법을 사용하는 것도 좋지만 데이터베이스 애플리케이션을 만들 때에는 속도 문제가 아주 중요한 경우를 제외하면 비표준 문법을 사용하지 않는 편이 좋다. 될 수 있으면 MySQL에선 허용하는 범위 안에서는 최대한 ANSI SQL2 표준을 따르도록 하자. 이렇게 해야 나중에 데이터베이스를 바꿔도 애플리케이션을 그대로 사용할 수 있다. 중간 규모의 데이터베이스를 사용하는 사람들도 대체로 나중에는 대규모 데이터베이스를 사용하고자 하는 경향이 있기 때문에 애플리케이션을 만들 때에도 이런 경우를 감안하여 유연하게 만들어야 한다.

MySQL에서는 열의 이름과 값을 함께 지정하는 비표준 문법도 지원한다:

 INSERT INTO book SET title='The Vampire Leatat, author='Anne Rice';

마지막으로 다른 테이블(또는 여러 개의 다른 테이블)의 데이터를 이용하여 데이터를 삽입할 수도 있다. 예를 들면, 다음과 같다:

 INSERT INTO foods (name, fat) SELECT food_name, fat_grams FROM recipes

INSERT 호출에 들어 있는 열의 개수가 SELECT 호출에 들어 있는 열의 개수와 같어야 한다는 점에 유의해야 한다. 또한 INSERT 열의 데이터 유형도 그에 해당하는 SELECT 열의 데이터 유형과 맞아야 한다. 마지막으로 INSERT 선언문에 들어가는 SELECT 절에는 ORDER BY 변경자를 사용할 수 없으며, INSERT 작업을 하는 테이블과 같은 테이블에 대해 SELECT를 실행시킬 수 없다.

시퀀스 생성

기본키로 가장 좋은 것은 기본키 역할 외에 데이터베이스에서 아무 의미가 없는 열이다. 기본키는 관계형 데이터베이스에서 각 열을 유일하게 알아볼 수 있도록 해 주는 도구이다. 계정이나 이메일 주소 같은 정보를 기본키로 사용하면 그 계정이나 이메일 주소가 그 사용자를 나타낼 수 있는 고유의 성질이라고 간주하는 셈이 된다. 만약 그 사람이 계정이나 이메일 주소를 바꾸면 데이터베이스에 있는 데이터의 무결성을 새로 점검해야 하기 때문에 일이 번거로워진다. 따라서 좋은 데이터베이스를 설계하려면 다른 의미를 가지지 않는 숫자를 기본키로 사용하는 것이 좋다.

아무런 의미를 가지지 않는 숫자를 기본키로 사용하고 싶다면 매번 새로운 행을 추가할 때마다 1씩 증가하는 숫자를 기본키로 사용하면 된다. 예를 들어, 앞에서 나온 cities 테이블의 경우에는 첫 번째로 집어넣는 도시의 id가 1이 되고, 두 번째 도시의 id는 2, 세번째 도시의 id는 3, 이런 식으로 id를 정하면 된다. 이러한 기본키 시퀀스를 제대로 관리하려면 한 번에 하나의 클라이언트만 이 숫자를 읽고, 그 값에 1을 더할 수 있도록 해야 한다. 이렇게 하고 싶다면 기본키 필드를 AUTO_INCREMENT로 지정하면 된다.

MySQL에서 데이블을 만들 때에는 두 개 이상의 열을 AUTO_INCREMENT로 지정할 수 없다. 어떤 열을 AUTO_INCREMENT로 지정하면 행을 삽입할 때 그 열에 0 또는 NULL 값을 집어넣을 때 현재 그 열에 있는 값 중 가장 큰 값에 1을 더한 값이 자동으로 입력된다. AUTO_INCREMENT 열은 반드시 인덱싱해야 한다. 다음과 같은 명령으로 자동으로 증가하는 id 필드가 있는 cities 테이블을 만들 수 있다:

 CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,                    name VARCHAR(100),                    pop INT,                    founded DATE)

INSERT 선언문에서 id 필드의 값을 0이나 NULL로 대입하면 첫 번째 행의 id 필드의 값은 자동으로 1이 된다. 아래의 명령은 AUTO_INCREMENT 기능을 활용하는 방법을 보여준다:

 INSERT INTO cities (id, name, pop) VALUES (NULL, 'Houston', 3000000)

테이블에 다른 데이터가 없는 상태에서 위와 같은 명령을 내리면 MySQL에서는 이 필드에 NULL이 아닌 1을 자동으로 넣는다(이 필드에서 NULL 값이 들어갈 수 없다). 테이블에 이미 다른 데이터가 있다면 새로 삽입한 행의 id 값에는 현재 있는 가장 큰 id 값에 1을 더한 숫자가 들어간다.

LAST_INSERT_ID() 함수에서 리턴한 값에 직접 1을 더해서 수동으로 시퀀스를 만들 수도 있다:

 UPDATE table_name SET id = LAST_INSERT_ID(id + 1);

AUTO_INCREMENT 속성은 테이블에 있는 정수 유형의 열 중 최대 1 개의 열에만 적용할 수 있다. 이 속성이 적용되는 열은 정수 유형이어야만 하며, 기본키 혹은 그 열만으로 유일 인덱스를 구성할 수 있어야 한다. 그러한 정수 필드가 있는 테이블에 새로운 행을 삽입할 때 해당 필드에 아무 값도 대입하지 않거나 NULL 값을 대입하면 그 열에 있는 값 중 가장 큰 값에 1을 더한 값이 자동으로 입력된다.

LAST_INSERT_ID() 함수에 대한 상세한 내용은 17장에 있다.

갱신

데이터베이스에 새로운 행을 삽입하는 것은 데이터 관리의 시작에 불과하다. 데이터베이스가 읽기 전용이 아닌 이상 데이터는 주기적으로 바뀐다. 표준 SQL 변경 선언문은 다음과 같다:

 UPDATE table_name SET column1=value1, column2=value2, ..., columnN=valueN [WHERE 절]

갱신하고자 하는 테이블의 이름을 지정한 다음 SET 절에 대입할 값을 적는다. WHERE 절에서는 갱신할 행을 지정한다. WHERE 절을 생략하면 테이블에 있는 모든 행을 갱신한다.

열에 리터럴 값 대신 계산 결과를 대입할 수도 있다. 다른 열에 있는 값으로부터 대입할 값을 계산하는 것도 가능하다:

 UPDATE years SET end_year = begin_year + 5

위의 명령을 사용하면 테이블의 모든 행에 있는 end_year 열에 begin_year 열의 값에 5를 더한 숫자를 대입할 수 있다.

WHERE 절

앞에서 SQL에서 가장 중요한 것 중 하나인 WHERE 절을 소개하였다. SQL에서 WHERE 절은 어떤 조건을 지정해서 테이블의 특정 행을 선택할 수 있게 해 주는 역할을 한다. 예를 들면 다음과 같다:

 UPDATE bands SET lead_singer = 'Ian Anderson' WHERE band_name = 'Jethro Tull'

이 UPDATE 문에서는 band_name이 Jethro Tull인 행에 대해서만 lead_singer 열을 바꾸도록 한다. band_name 열이 유일 인덱스가 아니라면 WHERE 절의 조건에 맞는 행이 여러 개 나올 수도 있다. SQL을 사용하다 보면 WHERE 절로 조작할 행을 선택하는 경우가 많다. WHERE 절에 들어 있는 열을 검색하게 되므로 일반적으로 WHERE 절에 많이 들어가는 열에 대해 인덱스를 만드는 것이 좋다. WHERE 절에서 사용할 수 있는 비교문은 잠시후에 알아보도록 하자.

삭제

데이터를 삭제하는 작업은 간단하다. 테이블 이름을 입력하고 WHERE 절을 이용하여 삭제할 행을 지정하기만 하면 된다:

 DELETE FROM table_name [WHERE 절]

WHERE 절을 사용할 수 있는 다른 명령과 마찬가지로 WHERE 절을 반드시 사용해야 하는 것은 아니다. WHERE 절을 생략하면 테이블을 있는 모든 데이터가 지워진다. SQL에 데이터를 없애는 명령어가 많이 있지만, 그 중 DELETE는 가장 쉽게 데이터를 지울 수 잇는 명령어이므로 특히 조심해야 한다.

MySQL 4.0에서는 명령어 하나로 여러 개의 테이블에 있는 데이블에 있는 데이터를 지울 수 있는, 다소 위험한 DELETE 형식을 새로 지원한다:

 DELETE table1, table2, ..., tableN FROM table1, table2, ... tableN [WHERE 절]

이 구문의 FROM 절은 간단한 형태의 DELETE 구문에서 쓰이는 FROM 절과는 다른 의미로 쓰인다. 즉 행을 지울 테이블을 나타내는 것이 아니라 WHERE 절에서 참조하는 테이블의 목록을 나타낸다. SELECT 선언문을 떠올려 보면 SELECT 선언문의 FROM 절과 똑같은 식으로 작동하는 것을 알 수 있다. 데이터를 지울 테이블을 DELETE 선어문 바로 뒤에 입력해야 한다:

 DELETE Author, Address FROM Author, Book, Address WHERE Author.author_id = Address.address_id AND Author.author_id = Book.author_id AND Book.publish_date < 1980;

위의 선언문을 사용하면 1979년 이전에 책을 출판한 모든 저자에 대한 정보(Author 테이블)와 그 저자의 주소에 대한 정보(Address 테이블)를 삭제할 수 있다. 하지만 DELETE 키워드 뒤에 Book 테이블을 입력하지 않았기 때문에 Book 테이블에 있는 책에 관한 정보는 그대로 남는다. WHERE 절에 대한 복잡한 내용은 나중에 알아보기로 하자.

질의

마지막으로 배울 SQL 명령은 SELECT이다. SELECT를 이용하면 데이터베이스에 있는 데이터를 볼 수 있다. SELECT는 SQL에 있는 다른 어떤 명령보다도 많이 쓰인다. 신규 데이터를 추가하거나 데이터를 변경하는 일은 가끔 있을 뿐 사실 데이터베이스에서는 대부분의 시간을 데이터를 읽어들이는 데 쓴다. SELECT 선언문의 일반적인 형식은 다음과 같다:

 SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN [WHERE 절]

위와 같은 문법으로 거의 모든 SQL 데이터베이스에서 데이터를 읽어올 수 있다. SELECT 선언문을 이용하면 하나 혹은 여러 개의 테이블로부터 원하는 열을 선택할 수 있다. WHERE 절은 찾고자 하는 데이터가 들어 있는 행을 선택하는 부분이다.

물론 여러 가지 방법으로 복잡한 그리고 강력한 질의를 처리할 수 있다(SELECT와 관련된 자세한 문법은 15장에서 다룬다). 아래의 SELECT 선언문은 가장 간단한 SELECT 명령이다:

 SELECT 1;

이 선언문은 간단하긴 하지만 아무 쓸모가 없다. 단지 1이라는 값을 가진 하나의 열을 가진 하나의 행이 질의의 결과로 돌아올 뿐이다. 다음과 같은 질의는 어느 정도 써 먹을 수가 있다:

 mysql> SELECT DATABASE(); +---------------+ | DATABASE()    | +---------------+ | TEST          | +---------------+ 1 row in set (0.01 sec)

DATABASE()라는 표현식은 현재 데이터베이스의 이름을 리턴하는 MySQL 함수이다(함수에 대한 자세한 내용은 잠시 후에 알아보기로 하자). 위와 같은 예에서 알 수 있듯이 SQL를 이용하여 중요한 정보를 간단하게 찾아낼 수 있다.

하지만 대부분의 경우 데이터베이스에 있는 테이블에서 테이터를 추출하기 위해 조금 더 복잡한 질의를 사용한다. SELECT 선언문의 첫 번째 부분에서는 가져오고자 하는 열을 열거한다. 모든 열을 선택할 때에는 *을 사용하면 된다. FROM 절은 그 열을 어떤 테이블로부터 선택할지를 나타낸다. WHERE 절에서는 행을 선택하는 조건을 지정할 있으며 두 개의 테이블을 연결하는 방법을 지정할 수도 있다.

연결

연결(join)은 어떤 테이블에 있는 데이터를 다른 테이블에 있는 데이터와 연관시켜주는 기능으로 관계형 데이터베이스의 핵심이라고 할 수 있다. 기본적인 연결 형식은 내부 연결(inner join)이라고도 한다. 테이블을 연결할 때에는 서로 다른 테이블에 있는 열 사이에 등호를 넣으면 된다:

 SELECT book.title, author.name FROM author, book WHERE book.author = author.id

이렇게 하면 두 테이블에 있는 행끼리 어떤 관계가 있을 때 두개의 서로 다른 테이블로부터 열을 뽑아낼 수 있다. 위의 질의에서는 book 테이블의 author 열의 값이 author 테이블의 id 값과 같은 경우를 찾아낸다. book 테이블은 [표 3-3], author 테이블은 [표 3-4]와 같이 주어지는 테이터베이스를 생각해 보자:

 [표 3-3] book 테이블 -------------------------------------------------------------------------------  ID           | 제목                   | 저자               | 페이지 -------------------------------------------------------------------------------  1            | The Green Mile         | 4                  | 894  2            | Guards, Guards!        | 2                  | 302  3            | Imzadi                 | 3                  | 354  4            | Gold                   | 1                  | 405  5            | Howling Mad            | 3                  | 294 ------------------------------------------------------------------------------- [표 3-4] author 테이블 -------------------------------------------------------------------------------  ID           | 이름                       | 국적 -------------------------------------------------------------------------------  1            | Isaac Asimov               | US               | Terry Pratchett            | UK  2            | Peter David                | US  3            | Stephen King               | US  4            | Neil Gaiman                | UK -------------------------------------------------------------------------------

내부 연결을 하면 데이터베이스 서버에서는 두 테이블로부터 질의를 만족시키는 행을 찾아서 두 테이블의 필드를 합친 가상 테이블을 만든다. 위에 나온 예에서는 질의에서 book 테이블의 author 필드가 author 테이블의 id 필드와 같은 경우를 요구했다. 따라서 질의의 결과는 [표 3-5]와 같다:

 [표 3-5] 내부 연결에 의한 질의 결과 -------------------------------------------------------------------------------  책 제목                        | 저자 이름 -------------------------------------------------------------------------------  The Green Mile                 | Stephen King  Guards, Guards!                | Terry Pratcher  Imzadi                         | Peter David  Gold                           | Isasc Asimov  Howling Mad                    | Peter David -------------------------------------------------------------------------------

닐 게이먼(Neil Gaiman)은 위의 결과에 포함되지 않는다. 그 저자에 해당하는 author.id 값이 book 테이블의 author 열에 없기 때문이다. 즉 이 데이터베이스에 있는 책 중에는 닐 게이먼의 책이 하나도 없다는 것을 의미한다. 내부 연결의 결과에는 질의와 정확하게 일치하는 행만 포함된다. 데이터베이스에 저자가 들어 있지만 책이 없는 경우에 사용할 수 있는 외부 연결은 잠시 후에 알아보기로 하자.

별칭

테이블 및 열 이름을 만들다 보면 이름이 너무 길어지게 될 수 있다. 또한 SQL 함수를 사용하다 보면 하나의 선언문에서 같은 함수를 여러 번 호출하는 것이 귀찮게 느껴질 수도 있다. 별칭(alias)을 이용하면 이러한 문제를 해결할 수 있다. 보통 복잡한 이름 대신 더 간단하고 이해하기 쉬운 별칭을 사용한다. SQL 선언문에서 복잡한 이름 대신 간단한 별칭을 사용할 수 있다. 예를 들면, 다음과 같다:

 # 열 별칭 SELECT long_field_name_are_annoying AS myfiled FROM table_name WHERE myfiled = 'Joe' # 테이블 별칭 SELECT people.names, tests.score FROM tests, really_long_people_table_name AS people

정렬 및 분류

SELECT의 결과에는 따로 순서가 정해져 있지 않다. 하지만 이렇게 무작위적으로 열거된 결과를 원하는 순서대로 정리할 수 있도록 SQL에서는 정렬(ordering)과 분류(grouping) 기능을 제공한다.

기본정렬

데이터베이스에서 나온 결과는 특정 열을 기준으로 정렬할 수 있다. 예를 들어, 질의 결과를 last_name 순으로 정렬하도록 하면 last_name 값을 기준으로 알파벳 순서대로 정렬된 결과가 나타난다. 정렬은 ORDER BY 절로 처리할 수 있다:

 SELECT last_name, first_name, age FROM people ORDER BY last_name, first_name

위의 질의에서는 두 개의 열을 기준으로 정렬한다. 정렬을 할 때 기준의 되는 열의 개수에는 제한이 없다. 결과를 무작위순으로 출력하고 싶다면 ORDER BY RAND() 절을 사용하면 된다.

DESC (내림차순, descending) 키워드를 사용하면 순서를 거꾸로 하여(내림차순으로) 정렬 할 수 있다:

 ORDER BY last_name DESC

DESC 키워드는 그 키워드 바로 앞에 있는 필드에만 적용된다. 여러 개의 필드를 기준으로 정렬한다면 DESC 바로 앞에 있는 필드에 대해서만 내림차순으로 정렬하고 나머지에 대해서는 오름차순으로 정렬한다.

지역화 정렬

전 세계 여러 나라의 컴퓨터에서 실행시켜야 하는 애플리케이션에서는 사실 정렬이 꽤 골치 아픈 문제이다. 문자열을 정렬하는 방법이 사용하는 알파벳에 따라 다를 뿐만 아니라 같은 알파벳에 대해서도 정렬 순서가 다를 수 있기 때문이다. MySQL에서는 MySQL 엔진에서 사용하는 문자 세트에 따라 정렬을 다르게 하는 방법으로 이 문제를 해결한다. 처음 설치했을 때의 기본 문자 세트는 ISO-8859-1(Latin 1)이며 ISO-8859-1과 스웨덴어, 핀란드어의 정렬 방법을 기본으로 사용한다.

정렬 방벙을 바꾸고 싶다면 문자 세트를 바꾸면 된다. 우선 MySQL을 컴파일하고 설치할 때 원하는 문자 세트를 포함시켰는지 확인해야 한다. 서버를 컴파일할 때 문자 세트를 제대로 포함시켰다면 서버를 시작할 때 -default-characte-set=CHARSET 이라는 인자를 사용하여 기본 문자 세트를 바꿀 수 있다. 영문 알파벳은 워낙 간단하기 때문에 영어만 사용하는 경우에는 MySQL의 기본 문자세트인 ISO-8859-1을 그대로 사용하면 된다. 하지만 스웨덴어나 독일어는 둘 다 ISO-8859-1을 사용하긴 하지만 정렬할 때 문제를 일으킬 수 있다. 스웨덴어에서는 정렬을 할때 a가 z 다음에 오지만 독일어에서는 a가 a앞으로 간다. 따라서 독일어를 사용하는 경우에는 기본 정렬 방법을 그대로 사용할 수 가 없다.

MySQL에서는 이러한 문제를 해결하기 위해 사용자 정의 문자 세트를 만들 수 있도록 되어 있다. 원하는 문자 세트에 대한 설정 파일이 있다면 드라이버를 컴파일할 때 그 문자 세트 지원 기능을 포함시키면 된다. 이 파일에는 그 문자 세트를 구성하는 문자와 그 문자를 정렬하는 방법이 들어 있다. 이 파일은 직접 만들어서 쓸 수도 있고 MySQL에 포함되어 있는 것을 사용해도 된다.

여기에서 가장 문제가 되는 것

'SQL' 카테고리의 다른 글

mysql 4.0.x 이하 버전 .. 한글셋팅.. (0)	2005.02.16
[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용 (0)	2005.02.02
STOP WORD (2)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27
[펌] [오라클] 인덱스 (0)	2004.09.22

Posted by tornado

STOP WORD

SQL 2005. 1. 14. 12:48

http://www.lextek.com/manuals/onix/stopwords2.html

mysql 은 아니지만.... .작성할 곳이 없다...

스탑워드 리스트 나온 사이트..

한글은 없나??

'SQL' 카테고리의 다른 글

[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용 (0)	2005.02.02
[펌] MySQL의 SQL (0)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27
[펌] [오라클] 인덱스 (0)	2004.09.22
[mysql] ifnull (0)	2004.09.16

Posted by tornado

[MySQL] 다수의 테이블에서 데이터 동시 삭제하기

SQL 2004. 10. 27. 14:13

mysql> create table aa (
    ->   idx int unsigned not null auto_increment,
    ->   name varchar(20) not null,
    ->
    ->   Constraint pk_idx PRIMARY KEY(idx)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table bb(
    ->    idx int unsigned not null auto_increment,
    ->    parent int unsigned not null,
    ->    name varchar(20) not null,
    ->    primary key(idx)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into aa values(null, '111');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aa values(null, '222');
Query OK, 1 row affected (0.00 sec)

mysql> insert into aa values(null, '333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from aa;
+-----+------+
| idx | name |
+-----+------+
|   1 | 111 |
|   2 | 222 |
|   3 | 333 |
+-----+------+
3 rows in set (0.00 sec)

mysql> insert into bb values(null, 2, 'bb222');
Query OK, 1 row affected (0.00 sec)

mysql> select * from bb;
+-----+--------+-------+
| idx | parent | name |
+-----+--------+-------+
|   1 |      2 | bb222 |
|   2 |      2 | bb222 |
|   3 |      2 | bb222 |
+-----+--------+-------+
3 rows in set (0.00 sec)

mysql> delete aa, bb from aa, bb where aa.idx = bb.parent;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from aa;
+-----+------+
| idx | name |
+-----+------+
| 1 | 111 |
| 3 | 333 |
+-----+------+
2 rows in set (0.00 sec)

mysql> select * from bb;
Empty set (0.00 sec)

mysql>

'SQL' 카테고리의 다른 글

[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14
[펌] [오라클] 인덱스 (0)	2004.09.22
[mysql] ifnull (0)	2004.09.16
오라클 설치 잘 나온 사이트 (0)	2004.09.02

Posted by tornado

[펌] [오라클] 인덱스

SQL 2004. 9. 22. 18:59

인덱스는 "값 + rowid" 로 구성되어 있습니다.

동일한 값을 같은 경우 rowid순으로 소트 되어 있습니다.

때문에 같은 블럭에서 테이블 억세스가 이루어 집니다.

쿼리에서 인덱스를 사용하는 경우와 사용하지 않는 경우를 정리해 보면, 아래와 같습니다.

index 못쓰는 경우 : '%ABC%', '%ABC', <>, NULL, NOT NULL
index 쓰는 경우 : 'ABC%', <, >, between, =

-----------------------------------------------

인덱스에 관련 하여 쿼리 만들때 유의 사항 입니다.

-----------------------------------------------

[1. 좌변을 가공 하지 마라]
예 1)
select *
from dept
where substr(dname, 1, 3) = 'ABC'
테이블의 모든 내용을 읽어서 서브 스트링을 모두 돌린 다음 같은것 찾는다.

select *
from dep
where dname like 'ABC%'
만약 index잡혀 있다면 index에서 ABC로 시작 하는 것 찾아서 그것만 테이블 억세스 한다.

예2)
where sal*12 = 2000만 <-- sal * 12를 모두 다한후 비교 한다.
where sal = 2000만/12 <-- 한번만 연산후 그 값인것 찾는다.

select * from emp
where TO_CHAR(HIREDATE, 'YYMMDD') = '940101'
select * from emp
where HIREDATE = TO_DATE('940101', YYMMDD)

select *
from emp
where NVL(COMM, 0) < 100
좌변 가공 했으므로(함수사용) 인덱스 사용
COMM이 널이면 0으로 바꾼후 100보다 적은지 비교.
NULL은 답이 안 나오므로. 강제로 바꿔 준 것이다.

NULL때문에 디어 버린 사람:
A = (B+12) * C
C가 널이면 A가 0원이 되어 버린다.
이때부터 자신의 프로그램엔 NVL안쓰면 마음이 안 놓인다.
DB설계자가 조금만 더 생각 해서 not null이나 default를 설정해 줬더라면,
개발자가 저런짓을 했겠는가?

SELECT *
from emp
where empno between 100 and 200
and NVL(JOB, 'X') = 'CLERK'

select *
from emp
where empno between 100 and 200
and job = 'CLERK'

SELECT *
from emp
where DEPNO|JOB = '10SALESMAN'

SELECT *
from emp
where depno = '10'
and job = 'SALESMAN'

[2. null, not null 비교인 경우 인덱스 사용 안함]
select *
from emp
where ename is not NULL

(bitmap index인 경우 인덱스 사가능)

select * from emp
where ename is not null
=> select * from emp
where ename > ''

select * from emp
where comm is not null
=> select * from emp
where comm > 0

select * from emp
where comm is null
null에 대한 업무 디파인이 확실해야 한다.
어느 집합에도 속하면 안되는 경우 null이 맞다. 정말 null이 필요한지 파악해야 한다.
null이 필요한 경우는 정말 극히 드물다.

null이란? 모르는 값을 표현 하고자 할때 null을 사용한다.
모르는 값은 어떠한 연사을 해도 모르는 값이다.
=> 널공포증 발생.

[3. 옵티마이저가 취사 한다]
select *
from emp
where job like 'AB%'
and empno = '7890'

job와 empno가 모두 인덱스로 잡혀 있을 경우 옵티마이저가 like보다 =을 우선 하므로
둘다 사용 하는 것이 아니라, empno를 사용한다.

[4. 의도적으로 인덱스를 못쓰게 함]

select *
from AAA
where custno = 'DN02'
and status = 90

실행계획:
TABLE ACCESS BY ROWID AAA
ANDEQAL
INDEX RANGE SCAN AAA_STATUS
INdEX RANGE SCAN AAA_CUSTNO
:위에서 밑으로 읽어 나가되 안에서 밖으로 실행 된다.

0.5초

둘다 인덱스 이므로 인덱스 머지를 한다.

두개의 인덱스를 봐서 서로 같은 rowid를 나온 경우가 만족하는 경우 이다.
status쪽인 1첫째 부터 시작 해서 rowid의 크기를 비교 하여 서로의 인덱스를 머지 합니다.

ANDEQAL : toad에서 인덱스 머지를 뜻하는 문자.

고객이 dn02는 20명 된다(레인지가 좁다). status가 90인 것은 60%이다(레인지가 넓다)

select *
from AAA
where custno = 'DN02'
and RTRIM(status) = '90'

실행계획
TABLE ACCESS BY ROWID AAA
INdEX RANGE SCAN AAA_CUSTNO

0.03초

select *
from AAA
where cust like 'DN%'
and status like '9%'
값이 틀려 지면 더 적은 rowid가 있을 수 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다.
둘중 하나의 인덱스를 선정 합니다. 랭킹을 비교... 둘다 like이므로 만들어진 시점을
비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에 쓰여진 인덱스를 사용.
status인덱스를 사용 했다면.

200초 걸림

select *
from AAA
where cust like 'DN%'
and RTRIM(status) like '9%'
일부러 한쪽의 인덱스는 안쓰게 만들었다.

0.1초 걸림

실행 계획에 확신이 안간다면 실행 계획을 바로 잡아줘야 한다.
오라클은 사람의 지능을 절대로 따라 올 수 없다.

and X.SALEDEPT = '710'
and Y.SALEDATE LIKE '9411%'
SALEDEPT가 = 이므로 saledept를 사용한다.

and RTRIM(X.SALEDEPT) = '710'
and Y.SALEDATE LIKE '9411%'
saledate 인덱스를 사용하게 해 줌.

X.ORDNO = Y.ORDNO
and X.ORDDATE like'9411%'
and Y.ORDDEPT = '710'
order by X.ORDDATE
order by가 없으면 orddept인덱스를 탔으므로 주문날짜 순으로 소트 되지 않는다.
때문에 order by를 주었다. 엄청나게 오래 걸림~

RTRM(X.ORDNO) = Y.ORDNO
and X.ORDDATE like'9411%'
and Y.ORDDEPT = '710'
조인 문장이면 연결 고리를 먼저 본다. 연결 고리에 이상이 있으면,
그 이상이 있는 쪽이 먼저 드라이빙 됩니다.
Y쪽이 먼저 드라이빙 되면 X.ORDNO 인덱스를 못 타므로 효율이 떨어 진다.
때문에 다리가 불어진 X쪽이 먼저 드라이빙 되는 것이다.
그런데 보니까. X쪽에 ORDDATE에 인덱스가 있다. 와~ 잘됐다.
ORDDATE인덱스를 타서 읽어 온다.

[5. 데이터 형에 유의]

where xyz = :v1
xyz에 인덱스가 있는데 full table 스캔을 하는 경우가 있다.
이런 경우 데이터 형이 안 맞을 경우이다. char인데 int...

select * from samplet
where chr = 10

위의 문장은 아래와 같다.
=> select * from samplet
where to_number(chr) = 10

이러한 이유로 인덱스를 쓰지 않는다.

아래의 예는 인덱스를 쓴다.
select * from samplet
where dat = '01-jan-04'
=> select * from samplet
where dat = to_date('01-jan-04')

NLS_DATE_FORMAT = 'dd-mm-yy' 로 되어 있다.
때문에 to_date로 바꾸어 준다. 'yyyymmdd'로 하면 편하다.

[6. not 사용 피해라]

select 'not found' into :col1
from emp
where empno = '1234'

=>
select 'not found' into : col1
where dual
where not exist (select * from emp
where empno = '1234')
위의 sql은 index 사용 한다.

select *
from emp
where ename like '천%'
and job <> 'sales'

=>
select *
from emp a
where a.ename like '천%'
and not exist (select * from emp b
where a.empno = b.empno
and b.job='sales')
또는
=>
select * from emp
where ename life '천%'
MINUS (차집합)
select * from emp b
where b.JOB = 'SALES'

[7. NULL에 대하여]
1. 미확정 값을 표현 할 경우.
2. 특정 값의 분포가 몰리는 경우
예) 결제 정보의 경우
결제:1, 미결제:2 인 경우 미결제가 대부분이며, 미결제를 검색 하는 경우가 많다.
이럴때 결제를 NULL로 해주고, 미결제를 1로 하면 인덱스의 효율이 높아 진다.
3. 조인의 구성 요소인 경우 not null로
4. 입력 조건 값으로 자주 나오는 경우 not null로 한다.

'SQL' 카테고리의 다른 글

[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27
[mysql] ifnull (0)	2004.09.16
오라클 설치 잘 나온 사이트 (0)	2004.09.02

Posted by tornado

[mysql] ifnull

SQL 2004. 9. 16. 10:44

이걸 까먹다니 ㅡㅡ 이제 늙었나베 ㅠㅠ

select a.* , ifnull(b.name, "") as deptName
from emp a LEFT JOIN dept b on(a.deptcode=b.idx) ;

b.name 이 null 일때 "" 로 치환.....

'SQL' 카테고리의 다른 글

[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27
[펌] [오라클] 인덱스 (0)	2004.09.22
오라클 설치 잘 나온 사이트 (0)	2004.09.02

Posted by tornado

오라클 설치 잘 나온 사이트

SQL 2004. 9. 2. 12:36

http://www.puschitz.com/OracleOnLinux.shtml

오얼~!

'SQL' 카테고리의 다른 글

[펌] MySQL의 SQL (0)	2005.01.14
STOP WORD (2)	2005.01.14
[MySQL] 다수의 테이블에서 데이터 동시 삭제하기 (0)	2004.10.27
[펌] [오라클] 인덱스 (0)	2004.09.22
[mysql] ifnull (0)	2004.09.16

Posted by tornado

replication 사용하기.. version - 4.0.17

SQL/MySQL 2004. 7. 6. 15:01

mysql replication 사용....

데이터 복제는 두가지 설정으로 나누어 진다.

* master 설정
* slave 설정

mysql 은 이미 설치되어 있다는 전제하에 설명하며,

두대의 서버에 각각 설치되었다고 가정한다.

master 서버의 ip 는 111.111.111.111 로 가정하고
slave 서버의 ip 는 222.222.222.222 로 가정한다.

먼저 master 설정...

1. Master Server 에 새로운 사용자 등록...(권한은 all 로 했지만 Repl_slave_priv 만 준다고 함).
(전체 데이터베이스 복제를 위해 *.* 로 한다..... 디비는 딱 세개 있음 ㅡㅡ)
grant all on *.* to 'tornado'@'%' identified by '패스워드' with grant option;

2. 권한을 줬으면 mysql 데이터베이스에 user 테이블에 현재 생성한 유져가 잘 들어있는지 체크한다.

3. my.cnf 파일을 고쳐주어야 한다. 보통 mysql 설치후 /etc/ 디렉토리에 복사해 놓고 사용하는데
없다면 MYSQL_HOME/support-files 디렉토리에 보면

-rw-r--r--    1 root mysql    2686 6¿u 3 10:04 MySQL-shared-compat.spec
-rw-r--r--    1 root mysql 773 6¿u 3 10:04 magic
-rw-r--r--    1 root mysql    4874 6¿u 3 10:04 my-huge.cnf
-rw-r--r--    1 root mysql    4850 6¿u 3 10:04 my-large.cnf
-rw-r--r--    1 root mysql    4833 6¿u 3 10:04 my-medium.cnf
-rw-r--r--    1 root mysql    2418 6¿u 3 10:04 my-small.cnf
-rwxr-xr-x    1 root mysql 24917 6¿u 3 10:04 mysql-4.0.17.spec
-rwxr-xr-x    1 root mysql 676 6¿u 3 10:04 mysql-log-rotate
-rwxr-xr-x    1 root mysql    5432 6¿u 3 10:04 mysql.server
-rw-r--r--    1 root mysql 24917 6¿u 3 10:04 mysql.spec

이렇게 파일이 존재한다.

이중에... my-small.cnf 를 /etc/my.cnf 로 복사한다.

cp my-small.cnf /etc/my.cnf

4. vi 로 /etc/my.cnf 를 열어본다...

# vi /etc/my.cnf 로 보면 아래와 같은 부분이 있다.

log-bin
server-id = 1

이 부분이 주석처리 되면 안되며, server-id 는 master 서버와 slave 서버 사이에서 유일한 값이어야 한다.

6. Database 및 Table 생성

mysql 에는 test 디비가 있는데 그곳에 아래와 같이 테이블을 만든다.

create table a ( name varchar(50) );

7. Database 복사.

위에처럼 생성했다면 mysql/data/test 디렉토리에 a 테이블에 관련된 세개의 파일이 생성된다.

그럼 해당 Table 을 무식하게(?) 압축해서 slave 의 test 디렉토리로 이동한다.

[root@localhost data]# tar cvzf test.tar.gz ./test
./test/
./test/a.frm
./test/a.MYI
./test/a.MYD
You have new mail in /var/spool/mail/root
[root@localhost data]#

압축된 파일을 slave 서버의 mysql/data/ 디렉토리에 옮긴후 압축을 푼다.

# tar xvzf test.tar.gz

이제 Slave 서버 셋팅이다.

1. my.cnf 파일 수정하기..

master 서버의 my.cnf 를 복사한 것과 같은 방법으로 /etc/my.cnf 를 만들어 준다.

# vi /etc/my.cnf

파일을 보다가 server-id = 1 이라고 되어있는 부분이 있으면
server-id = 2 로 바꾸어 준다.

아래와 같이 master 서버의 주소와 사용자를 적어준다.

# Replication Configure
master-host=111.111.111.111
master-user=tornado
master-password=패스워드
master-port=3306

2. mysql 데몬 다시 시작하기.

master 서버 및 slave 서버의 데몬을 다시 시작한다.

./bin/safe_mysqld --user=mysql &

3. master 서버에서의 상태 보기.

show master status;

위 명령을 치면 master 서버의 상태를 보여준다.
그곳에 보이는 파일과 slave 에서 보이는 파일이 동일해야 한다.

4. slave 서버에서의 상태보기.

show slave status;

위 명령을 치면 master 와는 틀리게 컬럼이 좀 여러개가 보인다.

그중에 master 에 적혀 있는 파일이 정확하게 있는지 확인한다.

만약 없다면 slave 의 data/ 디렉토리에 있는 모든 파일(디렉토리는 빼고!!!) 을 싹 지운다.
보통 xxx-bin 과 같은 파일일 것임..

만약 컬럼 중간에 에러 비스무리한 것이 있다면... 잘 읽어보라.

5. slave 서버 시작/중지하기.

slave start ; <-- 슬레이브 시작하기
slave stop ; <-- 슬레이브 중지하기..

이렇게 중지해도 양쪽의 로그파일이 있기 때문에
중간에 중단되었다 해도 중단된 시점부터 다시 백업해준다.

6. 테스트

master 서버의 test 디비에서 아까 만들었던 a 테이블에 쿼리를 날려본다.

insert into a values('tornado');

마스터/슬레이브 서버에서 각각 select * from a; 를 해보고

데이터가 동일한지 확인해서 같으면 끝...

7. 문제점???

pk 컬럼의 중복 및 짜잘한 오류가 생겼을 경우 slave 가 죽는 현상이 벌어짐 ㅡㅡ
해결책 ... 모름.. 아시는 분 덧글 부탁 ^^

'SQL > MySQL' 카테고리의 다른 글

[MySQL] Got error 28 from storage engine (0)	2008.03.03
[MySQL] mysqld_multi 사용법, 데몬 두 개 운영하기 (0)	2006.11.03
mysql 관리 툴 EMS (0)	2006.09.30
[Mysql] GRANT로 사용자 추가하기 (0)	2006.08.28
[펌] 데이터베이스 관리 (0)	2004.06.30

Posted by tornado

[펌] 데이터베이스 관리

SQL/MySQL 2004. 6. 30. 14:32

4장 데이터베이스 관리

전체적으로 볼 때 MySQL은 그다지 관리할 것이 많지 않은 편이다. 일단 설치하고 나면 관리자가 할 일이 별로 없다. 그렇다고 해도 MySQL 관리자는 다음과 같은 임무를 맡고 있다.

설치
설정 및 튜닝
액세스 제어
로깅
백업 및 복구
테이블 관리

위에 열거한 주제 중 몇 가지는 다른 자에서 다룬다(설치는 2장에서, 퍼포먼스 튜닝은 5장에서, 액세스 제어는 6장에서).

MySQL 서버 관리를 할 필요가 없는 사람이라도 이 장에 있는 내용을 알아두면 도움이 된다. 데이터베이스 관리에 대한 내용을 알고 있으면 데이터베이스 관리자에게 문의하기 전에 어떤 문제가 있는지 확인해 볼 수 있기 때문이다.

여러 가지 관리 작업을 처리하려면 MySQL의 관리자 액세스가 필요하다(액세스 권한과 MySQL 데이터베이스 관리자 설정에 관한 내용은 6장에서 자세히 다룬다). 작업에 따라 운영 체제의 관리자 권한(유닉스에서는 root, 윈도우 NT/2000/XP에서는 Administrator)이 필요할 수도 있다.

설정

우선 MySQL 서버 프로세스인 mysqld 및 mysql 명령행 유틸리티를 비롯한 여러 클라이언트 프로세스에 대한 설정을 해야 한다. MySQL 설정은 유닉스에서 흔히 사용하는 방법으로 이루어진다. 즉 명령행 옵션, 설정 파일 그리고 환경 변수를 이용하여 설정할 수 있다. 이러한 세 가지 방법을 통해 모든 설정 가능한 내역을 관리할 수 있다.

옵션을 정의하는 방법이 다양하기 때문에 위의 세 가지 옵션들이 서로 충돌할 때에는 다음과 같은 우선순위를 기준으로 삼는다.

명령행 옵션
설정 파일 옵션
환경 변수 옵션

예를 들어, 패스워드 옵션이 명령행, 설정 파일, 환경 변수에서 제각기 다르게 정의되어 있다면 MySQL 클라이언트 도구에서는 명령행에서 지정한 옵션을 사용한다.

MySQL 옵션을 다루는 방법 중에서는 설정 파일을 이용하는 방법이 가장 쉽고 많이 쓰인다. 설정 파일을 이용하면 모든 옵션을 파일 하나에 넣을 수 있기 때문에 명령을 실행시키거나 컴퓨터에 로그인할 때마다 옵션을 지정하지 않아도 된다.

파일 위치

유닉스 시스템에서는 MySQL 설정 파일을 다음과 같은 위치에서 순서대로 검색한다.

/etc/my.cnf 파일. MySQL에서는 가장 먼저 전역 옵션 파일을 검색한다. 일반적으로 모든 사용자와 서버에서 사용하는 기본 옵션은 이 파일에 저장해 두는 편이 좋다.
DATADIR/my.cnf 파일. DATADIR은 MySQL 서버 인스턴스의 데이터파일을 저장하는 디렉토리이다. 이 설정 파일에는 주어진 서버 인스턴스에만 적용되는 설정 인자를 저장한다.
--default-extra-file=filename 명령행 옵션을 통해 지정한 위치. 이 명령행 옵션을 이용하면 MySQL 서버나 클라이언트 유틸리티에서 임의의 위치에 있는 설정 파일을 이용할 수 있다.
$HOME/.my.cnf 파일. $HOME은 현재 사용자의 홈 디렉토리를 나타내는 유닉스 환경 변수이다. 이 설정 파일은 사용자의 홈 디렉토리에 저장되며 여기에는 사용자들이 개인적으로 사용하는 옵션을 저장해둘 수 있다. 클라이언트 옵션은 대부분 여기에 저장된다.

윈도우에는 전역 설정 파일이 추가로 있으며, 사용자별 개인 설정 파일은 없다.

윈도우 시스템 폴더(보통 C:\WINNT\System32)에 들어가는 My.ini 파일
C:\my.cnf
C:\mysql/data/my.cnf
--default-extra-file=filename

만약 어떤 옵션이 여러 파일에서 서로 다르게 정의되었다면 가장 나중에 읽어들인 내용이 적용된다. 즉 C:\my.cnf 파일에 저장된 사용자 정의 옵션이 My.ini 파일에 있는 옵션보다 더 우선시된다. 이렇게 하면 데이터베이스 관리자는 클라이언트 도구의 기본값을 My.ini를 통해 설정하고 사용자는 필요에 따라 다른 옵션을 사용할 수 있다.

파일 내용

모든 설정 파일의 형식은 같다. 설정 파일의 예가 [예제 4-1]에 나와 있다. 설정 파일이 그다지 어렵진 않지만, 일단 이 파일을 꼼꼼하게 살펴보자:

 [예제 4-1] MySQL 설정 파일 샘플 # MySQL 설정파일 예제 # # 클라이언트 옵션 [client] password = my_password port     = 3306 socket   = /var/lib/mysql/mysql.sock # mysqld 서버 옵션 [mysqld] port   = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable   = max_allowed_packet=1M

#으로 시작하는 맨 처음 세 줄은 주석이다. MySQL 설정 파일 형식에서는 #와 ; 기호로 주석을 표시할 수 있다. 샵(#)이나 세미콜론(;)이 나타나면 주석 기호 뒤에 나오는 그 줄의 나머지 부분은 모두 무시된다.

다음 줄에는 한 섹션이 시작하는 것을 나타내는 다음과 같은 코드가 있다:

 [client]

삼바(samba)나 윈도우 INI 설정을 건드려본 경험이 있다면 위와 같은 형식이 그다지 낯설지 않을 것이다. 설정 파일은 몇 개의 섹션으로 나위고, 각 옵션은 그 섹션에만 적용된다. MySQL 설정 파일에는 client와 mysqld의 두 가지 섹션이 있다.

섹션을 표시하는 부분 다음 줄부터는 파일이 끝나기 전까지, 또는 다른 그룹이 시작되기 전까지 그 섹션에만 해당되는 옵션이 들어간다. client 섹션에는 클라이언트 도구에 필요한 세 가지 설정 내역이 들어있다. 첫 번째 옵션은 기본 패스워드를 지정하는 부분이다:

 password       = my_password

이 옵션은 --password=my_password 명령행 옵션과 같은 역할을 한다. 일반적으로 명령행 옵션에서 --option=value 형태로 지정하는 것은 MySQL 설정 파일에서 option=value 형태로 지정하는 것과 똑같다.

설정 파일에 패스워드를 넣어 두면 MySQL에 연결할 때마다 매번 패스워드를 입력할 필요가 없기 때문에 편리하다. 하지만 패스워드를 설정 파일이나 명령행에 직접 입력하는 것은 그다지 권장할 만한 방법이 아니다. 클라이언트 유틸리티에서 패스워드를 입력할 수 있는 프롬프트가 나오게 하는 것이 좋다. 설정 파일에 패스워드를 입력해 놓았을 때에는 다른 사람이 절대 그 파일을 열어볼 수 없도록 주의해야 한다.

패스워드 옵션 아래에는 있는 두 줄에서는 클라이언트 도구에서 서버에 연결할 때 사용할 포트 및 소켓 파일을 설정한다. 그 뒤에는 다음과 같은 행이 있어서 새로운 섹션이 시작된다:

 [mysqld]

이 섹션에서는 MySQL 서버 프로세스를 설정하는 옵션이 들어간다. 여기에서는 클라이언트 섹션에서 사용하는 것과 비슷한 형태 외에 두 가지 새로운 옵션 설정 방식이 등장한다. 다음과 같은 옵션에서는 값을 입력할 필요가 없다:

 skip-locking

위의 옵션은 MySQL 서버에서 시스템 잠금을 사용하지 않는다는 것을 의미하는 부울 옵션이다. 위의 옵션을 지정하지 않으면 시스템 잠금이 작동한다. 명령행에서는 --skip-locking 옵션을 사용하면 된다.

MySQL에서 옵션을 지정하는 또 다른 방법은 mysqld 변수를 직접 지정하는 방법이다:

 set-variable = max_allowed_packet=1M

명령행에서는 --set-variable max_allowed_packet=1M라고 적으면 된다. 변수는 서버와 클라이언트의 런타임 환경을 제어하는 설정값이다.

서버 시작 및 종료

MySQL 서버는 서버 프로세스 형태로 동작하므로 컴퓨터를 켤 때마다 자동으로 시작해야 한다. 또한 컴퓨터를 종료할 때 서버도 종료시켜야 한다. MySQL 서버를 자동으로 시작하고 종료시키는 방법은 운영 체제에 따라 다르다.

mysqld 명령으로 명령행에서 직접 서버를 시작하게 만들 수도 있다. 하지만 어떤 운영 체제에서든지 MySQL을 시작할 때에은 safe_mysqld를 사용하는 것이 좋다.

유닉스/리눅스

유닉스 및 리눅스와 같은 유닉스 계열 운영 체제(Mac OS X는 제외)에서 MySQL을 시작하고 끝내는 방법은 SVR4 시스템인지 아닌지에 따라 달라진다. SVR4 시스템에서는 mysql.server 스크립트를 사용하면 되고 다른 유닉스 시스템에서는 safe_mysqld를 사용하면 된다. 여기에서는 이러한 스크립트를 사용하는 대략적인 방법에 대해 설명하겠다(유닉스 시스템을 관리할 때 힘든 부분 중 하나가 바로 시스템별로 서비스를 시작하고 종료하는 방법이 조금씩 다르다는 점이다).

SVR4

SVR4 시스템에서 MySQL을 시작하고 종료할 때에는 mysql.server 스크립트를 사용한다. 이 스크립트는 보통 MySQL을 설치하고 나면 만들어지는 support.files라는 디렉토리(보통 /usr/local/mysql/support-files)에 저장된다. SVR4의 시작/종료 메커니즘은 시스템이 다른 런레벨(run level)에 들어갈 때 서비스를 시작하고, 종료하기 위한 스크립트(/etc 폴더 아래에 들어가는 몇 개의 폴더에 들어감)에 의해 운영된다.

리눅스 시스템에서 RPM 패키지로 설치했다면 mysql.server가 자동으로 설치된다. RPM 설치기에서 mysql.server 파일을 /etc/rc.d/init.d에 복사할 때 그 파일명을 mysql로 바꾼다. /etc/rc.d/init.d/mysql 파일이 있으면 MySQL이 자동으로 시작되고 종료될 것이다.

레드헷 리눅스 시스템에서 mysql.server를 설치하는 방법은 다음과 같다:

 $ cp mysql.server /etc/rc.d/init.d $ ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc3.d/S99mysql $ ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc0.d/S01mysql

첫 번째 줄에서는 mysql.server 스크립트를 초기화 스크립트가 들어가는 /etc/rc.d/init.d 디렉토리에 설치한다. 두 번째 명령에서는 시스템이 3번 런 레벨에 들어갈 때 리눅스에서 자동으로 스크립트를 실행시킬 수 있도록 링크를 만든다. 리눅스에서는 시스템이 3번 런 레벨로 들어가면 /etc/rc.d/rc3.d 디렉토리에 있는 스크립트를 실행시킨다. 3번 런 레벨은 보통 SVR4 시스템이 다중 사용자 모드로 들어간다는 것을 의미한다. 다중 사용자 모드의 런 레벨이 3번이 아니라면 그에 맞는 디렉토리에 링크를 설치해야 한다.

마지막 줄에서는 0번 런 레벨에 mysql.server 스크립트에 대한 링크를 만든다. 0번 런 레벨은 시스템을 종료시키는 런 레벨이다. 따라서 /etc/rc.d/rc0.d에 있는 스크립트는 시스템이 종료될 때 실행된다.

다른 유닉스 시스템

SVR4를 기반으로 하지 않는 유닉스 시스템에서는 safe_mysqld라는 스크립트로 MySQL을 시작한다. 이 스크립트는 MySQL 설치 디렉토리 밑에 있는 /bin 디렉토리(보통 /usr/local/mysql/bin)에서 찾을 수 있다.

safe_mysqld를 사용하고 싶다면 해당 유닉스 시스템이 시작할 때와 종료할 때 서비스를 시작하고 종료하는 방법을 알아야 한다. BSD 시스템 중에는 safe_mysqld를 호출할 수 있도록 /etc/rc.local이라는 파일을 수정해야 하는 것도 있다. 하지만 FreeBSD와 같이 최근에 나온 BSD 시스템에서는 rc.local 파일을 수정하면 안되는 경우도 있다. 예를 틀어, FreeBSD에서는 mysql.server와 같은 스크립트를 /usr/local/etc/rc.d 디렉토리에 저장하면 된다.

Mac OS X

Mac OS X에서는 유닉스 시스템에서 자동으로 서비스를 시작하는 새로운 방법을 도입하였다. 여기에서는 세 가지 서로 다른 시작 디렉토리를 사용한다.

/System/Library/StartupItems
/Library/StartupItems
$HOME/Library/StartupItems

./System/Library/StartupItems 디렉토리는 운영 체제 서비스를 위한 디레토리이고, $HOME/Library/StartupItems 디렉토리는 사용자 소유의 서비스를 위한 디렉토리이다. MySQL은 시스템이 부팅될 때 시작되어야 하는 일반 서비스 디렉토리인 /Library/StartupItems 디렉토리에서 시작해야 한다.

StartupItems 디렉토리에서는 각 서비스별로 디렉토리가 하나씩 있어야 하기 때문에 MySQL을 설치할 때에는 우선 /Library/StartupItems/MySQL 디렉토리를 만들어야 한다. 사실 디렉토리의 이름은 그다지 중요하지 않다. 어쨌든 그 디렉토리에는 다음과 같은 파일이 있어야 한다.

MySQL을 시작할 때 쓰는 유닉스 스크립트
StartupParameters.plist라는 이름의 시작 매개 변수 파일

유닉스 스크립트는 MySQL을 시작할 때 safe_mysqld 명령어를 호출하기 위한 간단한 스크립트이다. 이 파일명은 디렉토리의 이름과 같아야 한다(여기에는 MySQL). 스크립트는 다음과 같이 만들면 된다:

 #!/bin/sh ./etc/rc.common if ["${MYSQLSERVER:=-NO-}" = "-YES-"]; then        cd /usr/local/mysql        bin/mysqld_safe --user=mysql & fi

$MYSQLSERVER의 값을 확인하는 부분을 넣어 두면 MySQL을 사용하고 싶지 않을 때, StartupItems에서 MySQL 디렉토리를 지울 필요 없이 Mac OS X hostconfig 파일의 내용만 수정하면 된다. MySQL을 사용할 때에는 /etc/hostconfig 파일에 다음과 같은 내용을 추가하면 된다:

 MYSQLSERVER=-YES-

MySQL을 사용하지 않을 때에는 위에 있는 -YES-를 -NO-로 바꾸면 된다.

스크립트 설치를 끝내고 나면 StartupParameters.plist 파일을 만들어야 한다:

 {  Description = "MySQL Database Server";  Provides = ("MySQL");  Requires = ("Resolver");  OrderPreference = "None";  Message =  {   start = "Starting MySQL Server";   stop = "Stopping MySQL Server";  }; }

이 파일은 Mac OS X에 이 디렉토리에 있는 서비스에 대한 설명을 해 주고 다른 서비스에 대한 의존성을 알려주는 역할을 한다. 시스템을 재부팅하면 서비스 시작 메시지가 나올 때 "Starting MySQL Server"라는 메시지가 나오는 것을 볼 수 있다.

윈도우 NT/2000

윈도우 NT 시스템에서는 NT 서비스 형태로 설치된 모든 애플리케이션을 자동으로 시작하고 종료시킨다. 윈도우 시스템에서 MySQL을 서비스로 설치하는 방법은 2장에서 나와 있다.

로그

MySQL 서버에서는 다음과 같은 로그를 만들 수 있다.

에러 로그
질의 로그
이진 로그
느린 질의 로그

mysql.server 또는 safe_mysqld를 이용하여 MySQL을 시작하면 에러 로그가 만들어진다. 원한다면 위에 있는 모든 로그를 기록할 수도 있고, 로그를 전혀 기록하지 않도록 할 수도 있다. 따로 실정해주지 않으면 로그 파일은 데이터 디렉토리에 저장된다.

에러 로그

에러 로그에는 safe_mysqld 스크립트에서 디다이렉트된 출력이 기록된다. 유닉스에서는 hostname.err(hostname 자리에는 호스트명이 들어감)이라는 파일로 저장된다. 윈도우에서는 mysql.err이라는 파일로 저장된다. 이 파일에는 서버가 죽어서 재시작한 경우를 포함하여 매번 서버가 시작할 때와 종료할 때 내용이 기록된다. 테이블의 치명적인 에러 또는 경고 메시지도 이 로그에 기록된다(나중에 확인해야 하거나 고칠 때에는 이 로그를 참조하면 된다).

이진 로그

이진 로그에는 데이터를 갱신하는 모든 SQL 명령어가 기록된다. MySQL에서는 데이터베이스에 있는 데이터를 실제로 바꾼 선언문만을 로그에 남긴다. 예를 들어, 삭제 명령을 내렸는데 전혀 삭제된 행이 없다면 로그에 아무런 기록도 남지 않는다. 도한 어떤 열에 원래 들어있던 것과 같은 값을 다시 대입하면 그 내용도 로그에 기록되지 않는다. 업데이트 내역은 실행된 순서대로 기록된다.

마지막으로 백업한 이후에 일어난 모든 업데이트 내역을 보관할 때에는 이진 로그가 좋다. 예를 들어, 데이터베이스를 매일 한 번씩 백업하는데 데이터베이스가 깨져버렸다면 다음과 같은 방법으로 마지막으로 완료된 트랜잭션까지의 데이터베이스 내용을 그래도 살려낼 수 있다.

데이터베이스를 복구한다(복구 작업과 관련된 내용은 잠시 후에 나오는 "백업"과 "복구"절을 참고하기 바란다).
마지막으로 백업한 이후에 이진 로그에 저장된 트랜잭션을 적용한다.

이진 로그를 활성화시킬 때에는 --log-bin=file 옵션을 사용한다. 파일명을 지정해주지 않으면 hostname-bin이라는 파일에 로그가 저장된다. 상대 경로를 사용하면 데이터 디렉토리를 기준으로 한 상대 경로로 간주된다. 그리고 파일명에 숫사 인덱스를 붙이기 때문에 파일명은 filename.number와 같은 형태가 된다(예를 틀어, hostname-bin.2). 이 인덱스는 파일을 순환(rotate)시키기 위한 용도로 쓰이는데, 다음과 같은 경우에 파일이 순환된다.

서버를 다시 시작했을 때
서버를 갱신(refresh)했을 때
로그 크기가 최대 크기에 다다랐을 때
로그를 밀어낼 때(flush할 때)

또한 사용중인 모든 이진 로그 파일의 목록이 저장된 인덱스 파일도 생성된다. 따로 설정해주지 않으면 파일명은 hostname-bin.index가 된다. 이 인덱스의 이름과 위치는 --log-bin-index=file 옵션으로 바꿀 수 있다.

이진 로그의 내용을 볼 때에는 mysqlbinlog 유틸리티를 사용한다. 다음 예에서 이진 로그가 어떤 식으로 작동하는지 알아보자. 이 예에서는 odin이라는 호스트에서 MySQL을 시작했고 log-bin 옵션을 전체 설정 파일인 /etc/my.cnf 파일에서 설정했다고 가정하자. 이 경우에 데이터 디렉토리에 다음과 같은 파일이 만들어진다:

 $ cd /usr/local/mysql/data $ ls -l . . -rw-rw----   1  mysql    mysql     73 Aug   5  17:06 odin-bin.001 -rw-rw----   1  mysql    mysql     15 Aug   5  17:06 odin-bin.index . .

odin-bin.index 파일의 내용은 다음과 같다:

 $ cat odin-bin.index ./odin-bin.001 $

위와 같은 인덱스 내용으로부터 인덱스와 같은 디렉토리에 odin-bin.001이라는 이진 로그파일이 있다는 것을 알 수 있다. mysqlbinlog 유틸리티를 이용하면 로그를 읽을 수 있다:

 $ mysqlbinlog odin-bin.001 # at 4 #010805 17:06:00 server id 1    Start: binlog v 1, server 3.23.40-log created 010805 17:06:00 $

클라이언트에서 데이터베이스를 갱신할 때마다 이진 로그의 내용은 점점 불어난다. 예를 들어, mysql 명령 프롬프트에서 다음과 같은 명령을 내리는 경우를 생각해 보자:

 $ mysql mysql> USE test; mysql> INSERT INTO test (object_id, object_title) VALUES (1, 'test'); Query OK, 1 row affected (0.02 sec) mysql> QUIT; Bye $

위와 같이 하고 나면 이진 로그의 내용이 다음과 같이 바뀐다:

 $ mysqlbinlog odin-bin.001 # at 4 #010805 17:06:00 server id 1    Start: binlog v 1, server 3.23.40-log created 010805 17:06:00 # at 73 #010805 17:39:38 server id 1    Query   thread_id=2    exec_time=0 error_code=0 USE test; SET TIMESTAMP=997058378; INSERT INTO test (object_id, object_title) VALUES (1, 'test'); $

즉 SQL 구문으로 데이터베이스를 갱신하면 이진 로그도 갱신된다. 로그를 밀어내고(flush) 새로운 이진 로그를 시작할 때에는 다음과 같이 하면 된다:

 $ mysqladmin -u root -ppassword flush-logs

이렇게 하면 모든 새로운 갱신 내용이 odin-bin.002라는 파일에 저장된다. 또한 odin-bin.index 파일에도 odin-bin.002 파일이 추가되었음이 기록된다.

이진 로그에 있는 명령을 다시 실행시킬 때에는 mysqlbinlog의 결과를 파이프를 통해 mysql 클라이언트 도구로 보내면 된다:

 $ mysqlbinlog odin-bin.001 | mysql

MySQL에서는 이진 로그를 제어하기 위한 몇 가지 옵션을 제공한다. --binlog-do-db=dbname 옵션을 지정하면 특정 데이터베이스를 갱신했을 때에만 로그를 기록한다. 특정 데이터베이스에 대해서 이진 로그를 기록하지 않을 경우에는 --binlog-ignore-db=dbname 옵션을 사용하면 된다.

느린 질의 로그

느린 질의 로그에는 long_query_time이라는 시스템 변수에 주어진 시간보다 더 오랜 시간이 걸린 모든 명령이 기록된다. 이 로그는 문제가 있는 질의를 찾아서 데이터베이스나 애플리케이션에서 튜닝이 필요한 부분을 밝혀낼 때 많은 도움이 된다.

느린 질의 로그를 사용하고 싶다면 --log-slow-queries=filename 옵션을 사용하면 된다. 파일 이름을 지정하지 않으면 자동으로 hostname-slow.log라는 파일에 저장되고, 디렉토리 이름을 지정하지 않으면 데이터 디렉토리에 저장된다. long_query_time 변수를 설정하고 싶다면 --set-variable long_query_time=time 옵션을 사용하면 된다(이때 time 자리에 초 단위로 시간을 지정하면 된다).

로그 순환

로그를 사용할 때에는 로그 파일이 너무 커지지 않도록 적당히 관리해애 한다. 잘못하면 파일 시스템에서 처리할 수 없을 만큼 로그 파일이 커질 수도 있기 때문이다.

하지만 여기에서 다루는 내용은 에러 로그에는 적용되지 않는다. 에러 로그는 safe_mysqld 스크립트에서 만들기 때문에 MySQL 서버에선 제어할 수 없고, flush-logs 명령어로 밀어낼 수도 없기 때문이다. 게다가 safe_mysqld에서는 매번 새로 시작할 때 새로운 로그를 만들지 않고 같은 로그에 내용을 덧붙인다. 에러 로그를 제대로 제대로 관리하려면 MySQL 서버의 시작 및 종료 스크립트를 수정해야 한다.

레드헷 리눅스를 사용한다면 mysql-log-rotate를 이용하여 로그를 순환시킬 수 있다. 이 프로그램은 MySQL을 설치했을 때 생기는 support-files 디렉토리에 들어 있다. 이 프로그램은 logrotate 유틸리티를 이용하여 자동으로 에러 로그를 순환시켜준다. 레드헷 리눅스에서 mysql-log-rotate를 설치할 때에는 그 파일을 /etc/logrotate.d 디렉토리에 복사하기만 하면 된다. 이 스크립트를 편집하려면 다른 로그도 순환시킬 수 있다. 기본적으로 이 스크립트에서는 질의 로그만 순환시킨다. logrotate에 대한 자센한 내용은 man 페이지나 레드헷 문서에서 찾을 수 있다.

리눅스에서 MySQL을 RPM 패키지로 설치하면 mysql-log-rotata가 자동으로 설치된다. RPM 설치기에서는 mysql-log-rotata를 /etc/logrotate.d에 복사할 대 그 이름을 mysql로 바꾼다. /etc/logrotate.d/mysql 파일이 있다면 로그 순환용 프로그램이 설치되어 있다고 보면된다.

레드헷 기반이 아닌 시스템에서는 로그 순환용 스크립트를 직접 만들어야 되는데, 사용하는 로그의 종류와 로그 저장 위치에 따라 스크립트의 내용이 달라진다. 하지만 로그파일을 복사한 다음 mysqladmin flush-logs 명령으로 로그를 다시 초기화하는 작업을 처리한다는 점은 같다.

백업

관리자가 해야 할 일 중 가장 중요한 것이 바로 백업 계획을 잡는 것이다. 특히 시스템이 다운되었을 때 데이터를 최대한 원래대로 복구해내는 것이 중요하다. 그리고 실수로 테이블을 지우거나 데이터베이스를 손상시킨 경우에도 백업해 둔 데이터가 있다면 문제를 쉽게 해결할 수 있다.

하지만 각 사이트마다 차이점이 있기 때문에 백업 문제에 있어서 어떤 방법이 딱 맞다고 할 수는 없다. 자신이 어떤 식으로 설치했는지, 어떤 기능이 필요한지에 따라 방법이 달라진다. 이 절에서는 백업을 할 때 지켜야 하는 일반적인 규칙과 백업 방법 등에 대해 설명할 것이다. 이 절에 나와 있는 내용을 바탕으로 자신의 상황에 맞는 적당한 백업 방식을 만들면 한다.

일반적으로 백업을 할 때 다음과 같은 점을 고려해야 한다.

가능하다면 데이터베이스와 다른 기기(다른 디스크나 데이프 드라이브 등)에 백업하는 것이 좋다. 디스크가 망거졌더라도 다른 장소에 백업해 두었다면 데이터는 살릴 수 있다. 이진 로그를 사용한다면 이진 로그도 백업하는 기기에 저장하도록 하자.
백업을 하기 전에 디스크 공간이 충분히 남아 있는지 확인한다.
될 수 있으면 백업과 함께 이진 로그를 활용하자. 그러면 데이터 손실을 최소로 줄일 수 있다. 이진 로그를 사용하지 않으면 마지막으로 백업할 시점까지의 데이터만 살릴 수 있다. 백업을 해 두었더라도 이진 로그가 없으면 별 도움이 안되는 경우도 있다.
백업 아카이브의 개수를 적절하게 유지하는 것이 좋다.
위급 상황이 일어나기 전에 백업을 테스트해보는 것이 좋다.

이제 백업할 때 쓸 수 있는 두 가지 MySQL 유틸리티에 대해 알아보자.

mysqldump

mysqldump는 데이터베이스를 덤프해주는 MySQL 유틸리티이다. 이 프로그램에서는 기본적으로는 그 데이터베이스를 다시 만들 때 필요한 모든 명령어(CREATE TABLE, INSERT 등)가 들어 있는 SQL스크립트를 만든다. 이렇게 하면 mysqlhotcopy로 직접 복사하는 경우에 비해 다른 하드웨어나 운영 체제에서 같은 데이터베이스를 새로 만들기에 좋은(즉 이식성이 좋은) ASCII 형식으로 출력된다는 점이다. 그리고 출력 결과가 SQL 스크립트이므로 원하는 테이블만 골라서 복구할 수도 있다.

mysqldump로 데이터베이스를 백업할 때에는 -opt 옵션을 사용하는 것이 좋다. 이 옵션을 사용하면 -quick, --add-drop-table, --add-locks, --extended-insert, --lock-tables 옵션이 모두 선택되며 데이터베이스를 가장 빠르게 덤프할 수 있다. --lock-tables 옵션은 데이터베이스를 사용할 수 없다.

실제 명령은 다음과 같은 식으로 입력하면 된다:

 $ mysqldump --opt test > /usr/backups/testdb

이렇게 하면 test 데이터베이스가 /usr/backups/testdb라는 파일로 덤프된다. 이진 로그를 사용한다면 --flush-logs 옵션을 사용하여 백업할 때 이진 로그를 새로 시작하도록 하는 것이 좋다:

 $ mysqldump --flush-logs --opt test > /usr/backups/testdb

mysqldump에서는 백업할 때 사용할 수 있는 몇 가지 다른 옵션도 제공한다. mysqldump에서 사용할 수 있는 모든 옵션의 목록을 보고 싶다면 mysqldump --help라는 명령을 사용하면 된다.

mysqlhotcopy

mysqlhotcopy는 LOCK TABLES, FLUSH TABLES, 유닉스 cp 명령어 등을 조합하여 데이터베이스를 빠르게 백업하는 펄 스크립트이다. 이 스크립트에서는 데이터베이스 파일을 그대로 다른 곳으로 복사한다. 파일을 복사하기 때문에 mysqldump에 비해 훨씬 더 빠르지만 원래 이식 가능한 형태로 만들어지는 MyISAM 테이블을 제외하면 다른 하드웨어 또는 운영 체제에서 사용하기가 힘들다. 또한 mysqldump는 외부에서도 실행시킬 수 있지만, mysqlhotcopy는 데이터베이스와 같은 호스트에서만 실행시킬 수 있다.

mysqlhotcopy를 실행시킬 때에는 다음과 같은 명령을 사용한다:

 $ mysqlhotcopy test /usr/backups

이렇게 하면 /usr/backups 디렉토리에 test 데이터베이스에 있는 모든 데이터 파일이 복사된다.

이진 로그를 사용한다면 --flushlog 옵션을 사용하면 백업을 마치고 나서 이진 로그를 새로 시작하는 것이 좋다.

복구

디스크에 하드웨어적인 문제가 있는 경우, 데이터 파일이 망가진 경우, 실수로 테이블을 지워버린 경우와 같이 데이터를 복구해야 하는 상황은 정말 다양한다. 이 절에서는 복구 절차에 관한 전반적인 내용을 다뤄보기로 한다.

일반적인 데이터베이스를 복구할 때에는 백업 파일과 이진 로그, 이렇게 두 가지가 필요하다. 복구 절차는 크게 다음과 같이 나눌 수 있다.

마지막으로 백업한 내용으로부터 데이터베이스를 복구한다.
이진 로그를 적용해서 시스템을 완전하게 예전 상태로 되돌린다.

이진 로그 기능을 사용하지 않았다면 마지막으로 백업한 부분까지만 복구할 수 있다.

mysqldump 복구

여기에서는 앞에서 덤프한 test라는 데이터베이스를 북구한다고 가정하겠다.

다음과 같은 명령을 실행하면 데이터베이스를 다시 볼러올 수 있다:

 $ cat test.dump | mysql

이 명령을 실행시키면 mysqldump에서 만들어낸 SQL 명령을 모수 실행시키게 되므로 마지막으로 데이터베이스를 백업할 시점의 상태 그대로 돌아갈 수 있다.

시스템을 마지막으로 백업할 상태로 돌로놓고 나면 이진 로그를 이용해서 마지막 백업 이후에 처리된 트랜잭션을 다시 실행시키면 된다. 로그에 여러 개의 데이터베이스에 대한 내용이 들어 있는데 그 중 하나만 복구하고 싶다면 --one-database 옵션을 사용해서 다른 데이터베이스에 적용되는 SQL 명령을 걸러낼 수 있다. 또한 이진 로그 중에서 마지막 백업 이후에 기록된 부분만 다시 실행시켜야 한다. 각 이진 로그 파일에 대해 다음과 같은 명령을 입력하자:

 $ mysqlbinlog host-bin.xxx | mysql --one-database=testdb

경우에 따라 mysqlbinlog 프로그램에서 출력된 결과를 조금 수정해야 할 수도 있따. 예를 들어, DROP TABLE 명령을 잘못 내려서 지워진 테이블을 복구한다면 mysqlbinlog에서 출력된 내용 중 DROP TABLE 선언문 부분을 지워야 한다. 그렇게 하지 않으면 열심히 복구한 테이블이 다시 삭제된다. 따라서 이런 경우에는 mysqlbinlog에서 출력된 내용을 텍스트 파일에 저장해서 편집하고, 그 텍스트 파일을 이용하여 데이터베이스를 복구해야 한다.

mysqlhotcopy 복구

mysqlhotcopy 백업 데이터로부터 복구할 때에는 서버가 멈춰 있는 상태에서 데이터베이스 파일을 백업 디렉토리에서 mysql 데이터 디렉토리로 복사하면 된다. 데이터베이스를 /var/backup/test에 백업했다고 가정하고, MySQL 데이터 디렉토리가 /usr/local/mysql/data 라면 다음과 같은 명령을 실행시켜서 마지막에 백업한 시점으로 데이터베이스를 되돌릴 수 있다:

 $ cp -R /var/backup/test /usr/lcoal/mysql/data

이제 mysql 서버를 다시 시작시키고 앞에서 설명한 것처럼 이진 로그를 적용시키면 시스템을 완전히 복구할 수 있다.

테이블 관리 및 복구

데이터 파일에 대한 쓰기 작업이 제대로 처리되지 않았을 때 데이터베이스 파일이 망가질 수도 있다. 전원이 나가거나 MySQL 서버를 정상적으로 종료시키지 않으면 이런 일이 일어날 수 있다.

MySQL에서는 myisamchk/ismchk와 mysqlcheck라는 두 가지 방법으로 테이블 에러를 알아내고 수리할 수 있다. 웬만하면 정기적으로 테이블에 문제가 있는지 검사하는 것이 좋다. 데이터베이스에 문제가 있다는 것을 일찍 발견할수록 테이블을 성공적으로 복구할 수 있는 가능성이 높아지게 마련이다.

mysqlcheck는 MySQL 3.23.38부터 새로 등장한 프로그램이다. myisamchk/isamchk와 mysqlcheck의 가장 큰 차이점은 mysqlcheck에서는 서버가 돌아가고 있을 때에도 테이블을 검사하거나 수리할 수 있다는 점이다.

myisamchk와 isamchk는 서로 상당히 유사하다. 이 둘의 기능은 같지만 myisamchk는 MyISAM 테이블에서 isamchk는 ISAM 테이블서 사용한다. mysqlcheck는 MyISAM 테이블에서만 사용할 수 있다.

테이블 검사

테이블에 어떤 문제가 있는 것 같다면 우선 테이블 검사용 유틸리티를 이용하여 테이블을 검사해야 한다. 데이터 파일의 확장자를 보면 테이블의 종류를 알 수 있다. 파일 확장자가 .MYI라면 MyISAM 테이블이고 .ISM이면 ISAM 테이블이다. 따라서 .MYI 파일에 대해서는 myismchk와 mysqlcheck를, .ISM 파일에 대해서는 isamchk를 사용하면 된다.

table1(ISAM 테이블)과 table2(MyISAM 테이블)라는 두 개의 테이블이 있는 test라는 테이터베이스가 있다고 가정하자. 우선 적절한 유틸리티로 테이블을 검사해야 한다. myisamchk와 isamchk를 사용할 때에는 파일을 읽는 도중에 서버에서 파일에 쓰기 작업을 하는 것을 방지하기 위해 MySQL 서버를 종료시켜야 한다:

 $ myisamchk table2.MYI Data records:      0    Deleted blocks:      0 - check file-size - check key delete-chain - check record delete-chain - check index reference $ isamchek table1.ISM Checking ISAM file: table1.ISM Data record:       0   Deleted blocks:       0 - check file-size - check delete-chain - check index reference $ mysqlcheck test table2 test.table2                                  OK

위와 같은 결과가 나오면 두 테이블 모두 에러가 없음을 알 수 있다.

간단한 에러를 찾아낼 때에는 위와 같은 기본적인 검사만으로도 충분하다. 에러는 없지만 테이블이 손상된 것 같다면 myisamchek/isamchk의 --extend-check 옵션이나 mysqlcheck의 --extend 옵션으로 확장 검사를 할 수 있다. 확장 검사를 하면 시간은 조금 오래 걸리지만 매우 자세히 검사할 수 있다. 확장 검사에서도 아무로 에러가 없다면 테이블에 문제가 없음을 알 수 있다.

테이블 복구

검사 결과 테이블에 에러가 있다면 그 에러를 고쳐야 한다.

myisamchk나 isamchk를 사용하는 경우에는 테이블을 복구할 때 MySQL 서버를 종료해야 한다. 그리고 만약에 대비해서 테이블을 복구하기 전에 데이터 파일을 백업해 두는 것이 좋다.

우선 --recover 옵션을 주고, myisamchk/isamchk를 실행해 보자:

 $ isamchk --recover table1.ISM - recovering ISAM-table 'table1.ISM' Data records: 3 $ myisamchk --recover table2.MYI - recovering (with keycache) MyISAM-table 'table2.MYI' Data records: 2

위와 같이 해도 문제가 해결되지 않는다면 느리긴 하지만 --recover로는 수정할 수 없는 에러도 고칠 수 있는 --safe-recover 옵션을 사용해 보자:

 $ isamchk --safe-recover table1.ISM - recovering ISAM-table 'table1.ISM' Data records: 3 $ myisamchk --safe-recover table2.MYI - recovering (with keycache) MyISAM-table 'table2.MYI' Data records: 2

mysqlcheck에서느 복구 옵션이 --repair밖에 없다:

 $mysqlcheck -repair test table2 test.table2                                  OK

이렇게 해도 안 된다면 백업 자료와 이진 로그를 이용해서 테이블을 복구하는 수밖에 없다. 자세한 내용은 "백업" 절과 "복구" 절에 나와 있다.

테이블 정기 검사

테이터베이스 파일에 대해서 정기적으로 테이블 검사를 해 주는 것이 좋다. isamchk/myisamchk/mysqlcheck 명령을 처리하는 스크립트를 만들어서 cron과 같은 스케쥴링 소프트웨어에서 정기적으로 실행하도록 만들면 된다.

또한 시스템이 부팅될 때 테이블을 검사할 수 있도록 시스템 부팅 절차를 조금 고치는 것도 괜찮은 생각이다. 이렇게 하면 특히 시스템이 다운된 후에 재부팅할 때 여려 모로 도움이 된다.

한빛미디어 "MySQL 시스템 관리와 프로그래밍"

'SQL > MySQL' 카테고리의 다른 글

[MySQL] Got error 28 from storage engine (0)	2008.03.03
[MySQL] mysqld_multi 사용법, 데몬 두 개 운영하기 (0)	2006.11.03
mysql 관리 툴 EMS (0)	2006.09.30
[Mysql] GRANT로 사용자 추가하기 (0)	2006.08.28
replication 사용하기.. version - 4.0.17 (0)	2004.07.06

Posted by tornado

1 2 3

Tornado 의 블로그

카테고리

태그목록

공지사항

최근에 올라온 글

최근에 달린 댓글

링크

글 보관함

달력

42024 이전 다음

'SQL'에 해당되는 글 52건

[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용

'SQL' 카테고리의 다른 글

[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자

'SQL' 카테고리의 다른 글

mysql 4.0.x 이하 버전 .. 한글셋팅..

'SQL' 카테고리의 다른 글

[mysql] 디비사랑넷 펌 ... 4.1버젼 한글사용

'SQL' 카테고리의 다른 글

[펌] MySQL의 SQL

3장 MySQL의 SQL

'SQL' 카테고리의 다른 글

STOP WORD

'SQL' 카테고리의 다른 글

[MySQL] 다수의 테이블에서 데이터 동시 삭제하기

'SQL' 카테고리의 다른 글

[펌] [오라클] 인덱스

'SQL' 카테고리의 다른 글

[mysql] ifnull

'SQL' 카테고리의 다른 글

오라클 설치 잘 나온 사이트

'SQL' 카테고리의 다른 글

replication 사용하기.. version - 4.0.17

'SQL > MySQL' 카테고리의 다른 글

[펌] 데이터베이스 관리

4장 데이터베이스 관리

'SQL > MySQL' 카테고리의 다른 글

티스토리툴바