Keylog

인덱스란?

인덱스의 개념을 설명할 때 가장 많이 드는 예시가 있다. 바로 책의 색인(Index)이다. 데이터베이스 공부를 하다가 RDBMS 개념이 궁금해 책을 찾아보고 있다고 가정해보자. 책을 첫 장부터 넘기면서 해당 내용을 찾을 수도 있지만 이는 매우 비효율적이다. 아마 대부분의 똑똑한 독자들은 책의 맨 뒤에 있는 색인 페이지에서 해당 개념이 포함되어 있는 페이지를 찾아 볼 것이다. 이처럼 책의 색인은 우리가 원하는 단어를 쉽고 빠르게 찾을 수 있게 도와준다. 이와 마찬가지로 데이터베이스에서 인덱스를 설정하면 테이블 안에 내가 찾고자 하는 데이터를 쉽고 빠르게 찾을 수 있다.

여기까지만 보면 인덱스를 사용하는게 매우 효과적으로 보이지만 무조건 인덱스를 사용하는 것은 오히려 성능을 악화시킬 수 있다. 예를 들어 데이터베이스 책에서 "데이터베이스"라는 단어를 색인 페이지에 만들어 놓으면 데이터베이스라는 단어는 거의 모든 페이지마다 존재할 것이기 때문에 오히려 배보다 배꼽이 커지는 상황이 발생한다.

인덱스 장점

• 검색 속도가 향상될 수 있다. (단, 항상 그런것은 아님!!)
• 검색 속도의 향상으로 쿼리의 부하가 줄어 시스템 전체의 성능이 향상된다.

인덱스 단점

• 인덱스가 데이터베이스 공간을 차지하기 때문에 추가적인 공간이 필요하다. (대략 데이터베이스 크기의 10%)
• 처음 인덱스 생성하는데 시간이 많이 소요될 수 있다.
• 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어나는 경우에는 오히려 성능이 악화된다.

인덱스 생성 방법

인덱스는 테이블의 컬럼 단위에 생성되는데 인덱스 생성 방법으로는 크게 두 가지가 있다.

1. 제약 조건으로 자동 생성되는 인덱스
2. CREATE INDEX [...] 명령어를 사용해 직접 생성하는 인덱스

자동 생성되는 인덱스의 경우 제약 조건으로 특정 컬럼에 Primary Key나 Unique를 설정하면 NORMAL 인덱스가 자동 생성된다. 해당 인덱스는 DROP INDEX 명령어로는 삭제할 수 없으며 삭제하려면 제약 조건을 제거하는 수밖에 없다.

직접 생성하는 인덱스의 경우 아래 예시 명령어처럼 인덱스를 생성하고 삭제한다.

/* 특정 테이블의 특정 컬럼에 고유(UNIQUE) 인덱스를 생성하라 */
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명)

/* 특정 인덱스 삭제 */
DROP INDEX 인덱스명

인덱스 내부 작동 원리

여러 종류의 인덱스가 있지만 보통 B-Tree라는 자료 구조로 이루어져 있기 때문에 해당 글에서는 B-Tree 자료구조를 기반으로 정리했습니다. B-Tree 자료구조는 루트 노드, 브랜치 노드, 리프 노드로 이루어져 있으며 여기서 노드란 트리 구조에서 데이터가 존재하는 공간을 의미한다.

위와 같은 데이터가 데이터베이스에 저장되어 있다고 가정해보자. 인덱스가 없는 상황에서 MMM이라는 데이터를 조회하려면 AAA 부터 총 8건의 데이터를 검색해야 MMM을 조회할 수 있다.

하지만 아래와 같이 B-Tree 자료구조로 이루어진 인덱스가 생성되어 있다면 루트 노드에서 부터 검색을 시작하여 리프노드로 이동한 후 2개의 블록만 검색하면 되므로 5건의 데이터만 검색하면 MMM을 조회할 수 있다.

지금은 레벨이 2단계 뿐이라 크게 와닿지 않지만 만약 훨씬 많은 양의 데이터의 경우에는 그 차이가 기하급수적으로 난다.

B-Tree 자료구조가 조회 성능을 향상시키는데 매우 효율적임을 확인했다. 하지만 단점에서도 언급했듯이 데이터 변경 작업(Insert, Update, Delete)이 빈번하게 일어나는 곳에 인덱스를 적용하면 오히려 성능이 나빠지는데 이는 인덱스 분할 작업이 발생하기 때문이다.

위 데이터베이스에 III 데이터를 추가한다고 가정해보자. 다행히 해당 리프노드에 빈자리가 있어서 JJJ 데이터를 한칸 이동시킨 후 III가 추가되었다.

문제는 이 다음 GGG 데이터를 추가할 때 발생한다. GGG 데이터를 추가하면 FFF 블록 다음에 GGG를 추가해야 하는데 빈 공간이 없어 인덱스 분할 작업이 발생한다. 이어서 데이터를 추가하다보면 결국 루트노드에도 빈 공간이 없기 때문에 루트노드에서도 인덱스 분할이 일어나게 되고 결국 데이터를 추가한 것 만으로 연쇄적으로 많은 작업들이 발생하게 된다. 위와 같은 인덱스 분할 작업은 비용이 많이 들기 때문에 성능에 큰 영향을 주게 된다.

인덱스 특징

• 인덱스 생성 시에는 데이터 블록은 그냥 둔 상태에서 별도의 블록에 인덱스를 구성한다.
• 엔덱스의 리프 블록은 데이터가 아니라, 데이터가 위치하는 주소값(ROWID)이다.
• 데이터의 입력/수정/삭제 시에는 인덱스가 없을 때보다 느리다.
• 인덱스는 여러 개 생성할 수가 있다. 하지만, 함부로 남용할 경우 오히려 시스템 성능을 악화시킬 수 있다.
• 인덱스를 검색하기 위해서는 반드시 WHERE 절에 해당 인덱스를 생성한 열의 이름이 나와야한다.

인덱스 사용 주의사항

• WHERE 절에서 자주 사용되는 열에 인덱스를 만들어야 하며 생성, 변경, 삭제보다 조회가 자주 사용되는 곳에 인덱스를 생성해야 한다.
• 데이터의 중복도가 높은 열에는 인덱스를 만들어도 효과가 없다. (카디널리티가 높은 순서를 기반으로 인덱스를 생성한다.)
• 사용하지 않는 인덱스는 제거하자

정규화란?

정규화는 릴레이션 간의 잘못된 종속 관계로 인해 데이터베이스 이상 현상이 일어나서 이를 해결하거나, 저장 공간을 효율적으로 사용하기 위해 릴레이션을 여러 개로 분리하는 과정입니다. 정규화는 무결성을 충족시키기 위해 같은 의미를 담는 릴레이션을 더 좋은 구조로 만들면서 자료의 중복은 최소화하고 독립적인 관계는 별개의 릴레이션으로 표현해야 하며, 각각의 릴레이션은 독립적인 표현이 가능해야 합니다.

이상 현상

• 삽입 이상(insertion anomalies) : 원하지 않는 데이터가 삽입되거나 필요한 데이터가 삽입되지 않아 발생하는 문제
• 삭제 이상(deletion anomalies) : 하나의 데이터를 삭제할 때 다른 필요한 데이터까지 같이 삭제되는 문제
• 수정(갱신) 이상(modification anomalies) : 일부의 튜플만 갱신되어 정보가 모호해지거나 일관성이 없어지는 문제

정규화 과정

1. 제 1정규형 - 도메인이 원자값
2. 제 2정규형 - 부분 함수 종속 제거
3. 제 3정규형 - 이행적 함수 종속 제거
4. 보이스/코드 정규형(BCNF 정규형) - 결정자이면서 후보키가 아닌 함수 종속 제거

제 1정규형

제 1정규형은 릴레이션의 모든 도메인이 더 이상 분해될 수 없는 원자 값만으로만 구성되어야 합니다.

예를 들어 아래 수정 전 테이블과 같이 하나의 속성(수강명 or 성취도)에 두 개 이상의 값을 가지는 반복 집합이 있으면 안됩니다. 이런 경우에는 수정 후 릴레이션 처럼 반복 집합을 제거해서 도메인이 원자 값만 갖도록 수정해주어야 합니다.

수정 전

수정 후

제 2정규형

제 2정규형은 제 1정규형 조건을 만족하면서 부분 함수의 종속성을 제거한 형태를 말합니다. 종속성이란 X와 Y라는 컬럼이 있을때 X의 값이 Y의 값을 유일하게 식별하면 Y는 X에 종속되어 있다고 표현합니다. 부분 함수의 종속성 제거란 기본키 중에 특정 컬럼에만 종속된 컬럼이 없어야 한다는 것입니다. 예를 들어 아래 수정 전 테이블은 기본키로 유저ID와 수강명을 가지고 있는데 유저 번호의 경우 유저ID 기본키에는 종속적이지만 수강명 기본키에는 종속적이지 않습니다. 그래서 수정 후 테이블 처럼 유저 번호가 유저ID 기본키에만 종속적이도록 테이블을 나눠주어야 합니다.

이때 주의할 점은 릴레이션을 분해할 때 동등한 릴레이션으로 분해해야 하고 정보 손실이 발생하지 않도록 무손실 분해로 분해되어야 합니다.

수정 전

수정 후

유저번호 릴레이션

성취도 릴레이션

제 3정규형

제 3정규형은 제 1, 제 2 정규형을 만족하면서 기본키가 아닌 모든 속성이 이행적 함수 종속을 만족하지 않는 형태를 말합니다. 이행적 종속 함수란 A -> B와 B -> C가 존재하면 논리적으로 A -> C가 성립하는데 이때 집합 C가 집합 A에 이행적으로 종속 되었다고 합니다. 아래 수정 전 테이블을 예로 들면 유저ID 컬럼은 등급을 결정하고 다시 등급 컬럼은 할인율을 결정함으로 이행적 종속 관계입니다. 이런 경우 수정 후 테이블처럼 등급 컬럼과 할인율 컬럼을 분리 함으로써 이행적 함수 종속을 제거할 수 있습니다.

수정 전

수정 후

등급 릴레이션

할인율 릴레이션

보이스/코드 정규형 (BCNF 정규형)

BCNF 정규형이란 제1, 제2, 제3 정규형을 만족하고 결정자이면서 후보키가 아닌 함수 종속 관계를 제거하여 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 상태를 말합니다.

• 후보키 : 기본키가 될 수 있는 후보들이며 유일성과 최소성을 동시에 만족하는 키
• 결정자 : 함수 종속 관계에서 특정 종속자를 결정짓는 요소, X -> Y 일 때 X는 결정자, Y는 종속자입니다.
• 유일성 : 중복되는 값이 없음
• 최소성 : 필드를 조합하지 않고 최소 필드만 써서 키를 형성할 수 있는 것

아래 조건을 만족하는 유저ID-수강명-강사 테이블이 존재한다고 가정하겠습니다.

• 각 수강명에 대해 한 학생은 오직 한 강사의 강의만 수강한다.
• 각 강사는 한 수강명만 담당한다.
• 한 수강명은 여러 강사가 담당할 수 있다.

위 테이블에서 강사 속성은 수강명을 결정짓는 결정자이지만 후보키가 아닙니다. 그렇기 때문에 모든 결정자가 후보키인 상태가 되도록 강사 속성을 테이블로부터 분리시켜 주어야 합니다.

정규화 장점

1. 데이터베이스 변경 시 이상 현상 제거
2. 데이터베이스 구조 확장 시 재 디자인 최소화 - 정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 됩니다. 이는 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되며 응용프로그램의 생명을 연장시킵니다.
3. 사용자에게 데이터 모델을 더욱 의미있게 제공 - 정규화된 테이블들과 정규화된 테이블들간의 관계들은 현실 세계에서의 개념들과 그들간의 관계들을 반영합니다.

정규화 단점

1. 테이블을 분리하여 정규화를 진행하므로 테이블 간 JOIN 연산이 증가하여 데이터 처리 속도가 느려질 수 있습니다. 따라서 서비스에 따라 정규화 또는 반정규화 과정을 진행해야 합니다.

트랜잭션이란?

트랜잭션이란 데이터베이스의 상태를 변화시키는 하나의 논리적인 작업 단위라고 할 수 있으며, 트랜잭션에는 여러개의 연산이 수행될 수 있다. 그리고 트랜잭션은 아래 네 가지 원칙(ACID)가 반드시 지켜져야 한다.

원자성 (Atomicity)

처음에 언급했듯이 트랜잭션에는 여러개의 연산이 수행될 수 있는데 이 연산들이 모두 성공하거나 모두 실패해야 한다. 예를 들어 은행의 계좌 이체 서비스를 개발한다고 가정하면 하나의 계좌에서는 출금이 이루어져야 하고, 이체의 대상이 되는 계좌에는 입금이 동시에 일어나야 한다. 만약 출금에는 성공했는데 입금에는 실패한다면 결국 출금 계좌의 주인은 돈만 잃은 셈이 된다. 즉 한 트랜잭션 안에 있는 모든 연산이 성공했을때만 커밋(COMMIT)되어 데이터베이스에 영구히 저장되고 하나라도 실패한다면 한 트랜잭션 내에 포함된 모든 연산들이 전부 롤백(ROLLBACK)되어야 한다.

일관성 (Consistency)

모든 트랜잭션은 일관성 있는 데이터베이스 상태를 유지해야 한다. 즉 허용된 방식으로만 데이터를 변경해야 하는 것을 의미하며 항상 데이터베이스에서 정한 무결성 제약 조건을 만족해야 한다. 예를 들어 통장에 0원이 있는데 다른 사람에게 500만원을 이체하는 것은 불가능하다. 만약 이체가 가능해버린다면 일관성을 해치는 것.

격리성 (Isolation)

동시에 실행되는 트랜잭션들이 서로에게 영향을 미치지 않도록 격리한다. 즉 트랜잭션으로 처리되는 중간에 외부에서의 간섭이 없어야 한다.

지속성 (Durability)

트랜잭션이 성공적으로 처리되면 그 결과는 영속적으로 보관되어야 한다. 중간에 시스템에 문제가 발생해도 데이터베이스 로그 등을 사용해서 성공한 트랜잭션 내용을 복구해야 한다.

무결성이란?

무결성이란 데이터의 정확성, 일관성, 유효성을 유지하는 것을 말한다.

격리 수준

트랜잭션은 원자성, 일관성, 지속성을 기본적으로 보장한다. 하지만 격리성은 동시성과 관련된 성능 이슈로 인해 격리 수준을 선택할 수 있다. 격리 수준은 아래 네 가지가 있으며 READ UNCOMMITED가 격리 수준이 가장 낮고 아래로 갈수록 격리 수준이 높다. 그리고 격리 수준이 낮을수록 동시성은 증가하지만 격리 수준에 따라 다양한 문제가 발생한다.

READ UNCOMMITED

커밋되지 않은 데이터에 대한 읽기를 허용한다.

DIRTY READ, NON-REPEATABLE READ, PHANTOM READ 문제가 발생할 수 있다.

READ COMMITED

커밋된 데이터에 대해서만 읽기를 허용한다. 그렇기 때문에 DIRTY READ 문제는 발생하지 않지만 어떤 트랜잭션이 접근한 행을 다른 트랜잭션이 수정할수 있어 NON-REPEATABLE READ 문제와 PHANTOM READ 문제가 발생할 수 있다.

MySQL8.0, PostgreSQL, SQL Server, 오라클 등 대부분의 데이터베이스들이 사용하는 격리 수준이다.

REPEATABLE READ

트랜잭션에 진입하기 이전에 커밋된 데이터에 대해서만 읽기를 허용한다. REPEATABLE_READ는 하나의 트랜잭션이 수정한 행을 다른 트랜잭션이 수정할 수 없다. 하지만 새로운 행을 추가하는 것은 가능하다. 그렇기 때문에 DIRTY READ와 NON-REPEATABLE READ 문제는 발생하지 않지만 PHANTOM READ 문제가 발생할 수 있다.

SERIALIZABLE

SERIALIZABLE은 말 그대로 트랜잭션을 순차적으로 진행시키는 것을 말하며 여러 트랜잭션이 동시에 같은 행에 접근할 수 없다.

가장 엄격한 트랜잭션 격리 수준으로 DIRTY READ, NON-REPEATABLE READ, PHANTOM READ 문제는 발생하지 않지만 교착 상태가 일어날 확률도 많고 가장 성능이 떨어지는 격리 수준이다.

격리 수준에 따른 문제점

1. DIRTY READ : 아직 커밋하지 않은 데이터를 조회하는 문제
2. NON-REPEATABLE READ : 한 트랜잭션 안에서 동일한 엔티티를 반복 조회 했을때 결과 값이 다르게 나오는 문제
3. PHANTOM READ : 한 트랜잭션 안에서 일정 범위의 데이터를 조회했을 때 결과 집합이 다르게 나오는 문제