데이터베이스 이론: 트랜잭션(Transaction)과 인덱스(Index)

ACID 트랜잭션

트랜잭션이란 무엇인가?

데이터베이스와 데이터 스토리지 시스템이라는 맥락에서 트랜잭션이란 한 단위의 작업으로 취급되는 모든 작업을 말합니다. 트랜잭션은 완전히 완료되기도 하고 전혀 완료되지 않을 수도 있으며, 스토리지 시스템을 한결같은 상태로 둡니다. 트랜잭션의 전형적인 예는 은행 계좌에서 현금을 인출할 때 일어나는 일입니다. 현금이 계좌에서 인출되거나, 인출되지 않거나 둘 중의 하나일 뿐 그 중간 어딘가의 상태란 없습니다. 

A.C.I.D. 속성: 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 영속성(Durability)

• 원자성(Atomicity) - 트랜잭션에 속한 각각의 문(SELECT, INSERT, DELETE, UPDATE)을 하나의 단위로 취급합니다. 과정 전체가 실행되거나(Commit All) 그 문의 어떤 부분도 실행되지 않거나(Roll-back All) 둘 중 하나입니다. 이 속성이 있으면 예컨대 스트리밍 데이터 소스가 스트리밍 중에 갑자기 오류를 일으키더라도 데이터 손실과 손상이 방지됩니다.
• 일관성(Consistency) - 작업은 항상 일관성을 띄어야 합니다. 트랜잭션이 테이블에 변경 사항을 적용할 때 미리 정의된, 예측할 수 있는 방식만 취합니다. 트랜잭션 일관성이 확보되면 데이터 손상이나 오류 때문에 테이블 무결성에 의도치 않은 결과가 생기지 않습니다.
• 격리성(Isolation) - 여러 사용자가 같은 테이블에서 모두 동시에 읽고 쓰기 작업을 할 때, 각각의 트랜잭션을 격리하면 동시 트랜잭션이 서로 방해하거나 영향을 미치지 않습니다. 각각의 요청이 실제로는 모두 동시에 발생하더라도, 마치 하나씩 발생하는 것처럼 발생할 수 있습니다.
• 영속성(Durability) - 트랜잭션 실행으로 인해 데이터에 적용된 변경 사항이 저장되도록 보장합니다. 시스템 오류가 발생해도 마찬가지입니다.

ACID 트랜잭션이 있으면 좋은 이유

ACID 트랜잭션은 가능한 최대한의 데이터 안정성과 무결성을 보장해줍니다. 작업 하나가 일부분만 완료되는 바람에 데이터가 일관적이지 않은 상태가 되는 불상사가 절대로 일어나지 않게 해준다는 뜻입니다. 예를 들어 ACID 트랜잭션이 없다고 가정하면, 데이터베이스 테이블에 몇몇 데이터를 쓰던 중에 예기치 못하게 정전이 되면 데이터 중 일부는 저장이 되고 일부는 저장되지 않는 사태가 발생할 수 있습니다. 그러면 데이터베이스는 일관성 없는 상태가 되어 복구하기 무척 어렵고 시간도 오래 걸립니다.

인덱스(Index)란?

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조입니다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됩니다. 이렇게 인덱스가 생성하였다면 앞으로 쿼리문에 "인덱스 생성 컬럼을 Where 조건으로 거는 등"의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있습니다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져올 수 있습니다.

즉 인덱스는 책에 있는 목차라고 생각하시면 편합니다. 우리가 책에서 정보를 찾을때도 먼저 원하는 카테고리를 목차에서 찾고 목차에 있는 페이지 번호를 보고 찾아가듯 인덱스도 인덱스에서 내가 원하는 데이터를 먼저 찾고 저장되어 있는 물리적 주소로 찾아갑니다. 이 Index에 관한 내용은 자세히 알아두는 것이 좋습니다. 실제 DB 관련 작업을 할 때 대부분의 속도 저하는 바로 Select문 특히 조건 검색 Where절에서 발생하는데 가장 먼저 생각해 볼 수 있는 대안으로 Index를 생각할 수 있기도 하고, SQL 튜닝에서도 Index와 관련된 문제사항과 해결책이 많기 때문입니다.

인덱스(Index)를 사용하는 이유

테이블에 데이터들이 인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점입니다. 이 특징으로 인해 조건 검색이라는 영역에서 굉장한 장점이 됩니다.

• 조건 검색 Where 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽으로 저장됩니다. 이렇게 되면 Where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 합니다. 이것을 풀 테이블 스캔 (Full Table Scan)이라고 합니다. 하지만 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있겠죠. 이것이 인덱스(Index)를 사용하는 가장 큰 이유입니다. 

• 정렬 Order by 절의 효율성

인덱스(Index)를 사용하면 Order by에 의한 Sort과정을 피할수가 있습니다. Order by는 굉장히 부하가 많이 걸리는 작업입니다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됩니다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 됩니다. 이미 정렬이 되어 있기 때문에 가져오기만 하면 되니까요.

• MIN, MAX의 효율적인 처리가 가능하다.

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점입니다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있습니다.

인덱스(Index)의 단점

인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있습니다. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지 시켜줘야 한다는 점입니다. 그렇기에 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미칩니다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야겠죠. 그리고 INDEX 테이블, 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야 한다는 단점도 있습니다.

그리고 검색시에도 인덱스가 무조건 좋은 것이 아닙니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫습니다. 그리고 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 무턱대고 INDEX를 만들어서는 결코 안 될 말입니다.

인덱스(Index)의 관리

앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생합니다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신합니다.

INSERT: 새로운 데이터에 대한 인덱스를 추가합니다.
DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행합니다.
UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가합니다.

인덱스 생성 전략

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.

1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼
4. ORDER BY 절에서 자주 사용되는 컬럼
5. 조인 조건으로 자주 사용되는 컬럼

B * Tree 인덱스

인덱스에는 여러가지 유형이 있지만 그 중에서도 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조입니다. 그리고 B TREE 인덱스 중에서도 가장 많이 사용하는것은 B*TREE 와 B+TREE 구조를 가장 많이 사용되는 인덱스의 구조입니다.

B * Tree 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스입니다. 구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됩니다. 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 정렬한 순서가 중간 쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROW ID를 저장합니다.

결합 인덱스란?

결합 인덱스란 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말합니다. 주로 단일 컬럼으로는 나쁜 분포도를 가지지만 여러 개의 컬럼을 합친다면 좋은 분포도를 가지고, Where절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성합니다.

결합 인덱스 컬럼 선택

1. where절에서 and 조건으로 자주 결합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들 
2. 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들
3. order by에서 자주 사용되는 컬럼들
4. 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때

결합 인덱스의 컬럼 순서 결정

결합 인덱스를 만들 때 결합 인덱스를 구성하는 컬럼들의 배열 순서는 아주 중요하기에 신중하게 결정하여야 합니다. 컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아질 수 있기 때문입니다. 만약 select 문의 where절에 결합 인덱스의 첫 번째 컬럼을 조건에 사용하였다면 그 질의문은 결합 인덱스를 사용할 수 있습니다. 하지만 개발자가 결합 인덱스의 두번째 컬럼만을 where 절에 조건으로 사용하고 결합 인덱스를 사용하고자 했다면 실행계획은 인덱스를 사용하지 못합니다. 따라서 쿼리문 작성 시 결합 인덱스를 사용하고자 한다면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용하여야 합니다. 조건은 컬럼 전체를 순서대로 사용할 수도 있고, 아니면 선행하는 일부 컬럼을 순서대로 사용할 수 있습니다. 

결합 인덱스 컬럼의 설정 시 고려해야 할 우선순위

1. where절 조건에 많이 사용되는 컬럼이 우선시
2. Equal('=')로 사용되는 컬럼 우선
3. 분포도가 좋은 컬럼을 우선
4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정

결합 인덱스 사용 예시

1. 결합 인덱스 생성

create index emp_pay_idx on emp_pay(급여년월, 급여코드, 사원번호);
emp_pay 테이블에서 급여년월, 급여코드, 사원번호 컬럼으로 emp_pay_idx라는 결합 인덱스를 생성하였습니다.

  2. 결합 인덱스 사용

select * from emp_pay where 급여년월 = '202107';
select * from emp_pay where 급여년월 = '202107' and 급여코드 ='정기급여';
select * from emp_pay where 급여년월 = '202107' and 급여코드 = '정기급여' and 사원번호 = '20210401';
select 문장의 where 절에서는 다음과 같은 조건 조합에서 인덱스가 사용되게 됩니다.

결합 인덱스가 사용되지 않을 때

select * from emp_pay where 급여코드 = '정기급여';
select * from emp_pay where 사원번호 = '20210401';
select * from emp_pay where 사원번호 = '20210401' and 급여코드 = '정기급여';
위와 같이 where 조건문을 나열할 때 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건식이 없으면 B*Tree 구조인 결합 인덱스를 검색할 수 없기 때문에 결합 인덱스가 무용지물이 되니 주의하여야 합니다.

결합 인덱스의 효율성이 떨어지는 경우

결합 인덱스도 일반적인 인덱스와 마찬가지로 데이터들이 정렬되어 보관되기 때문에 소수의 데이터를 빠르게 찾는 것에는 유리하지만 아래와 같이 스캔이 많이 생기게 된다면 효율성이 떨어지게 됩니다. 아래의 예시들은 emp_pay_idx 인덱스를 사용하기는 하지만 스캔이 많이 생기는 경우로 인덱스의 효율성이 떨어지는 경우들의 예시입니다.

select * from emp_pay where 급여년월 LIKE '2021%' and 급여코드 = '정기급여';

위 조건절의 경우 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건이 있더라도 Equal(=)이 아닌 범위 연산자인 LIKE '2021%' 조건을 사용했으므로, 세개의 칼럼이 모두 필요한 emp_pay_idx 인덱스를 찾을 때 두번째 칼럼인 급여코드에 대한 조건을 B*Tree에서 쉽게 찾을수가 없게 됩니다. 이는 결합 인덱스가 각 칼럼별로 정렬이 되어 있는 것이 아니라 첫번째, 두번째, 세번째 칼럼이 결합이 되어 정렬이 되어있기 때문입니다. 이때 급여코드에 대한 조건은 인덱스를 찾아가는 검색조건이 아니라 인덱스 값이 조건에 맞는지 여부를 검증하는 체크 조건이 됩니다.

select * from emp_pay where 급여년월 = '202107' and 사원번호 = '20210401';

위 조건절의 경우는 결합 인덱스의 첫번째 칼럼인 급여년월의 조건이 equal(=)이더라도 두번째 컬럼인 급여코드에 대한 조건이 없으므로 세번째 칼럼인 사원번호 조건을 검색 조건이 아닌 체크 조건으로 밖에 사용할 수 없게 됩니다. 즉 결합 인덱스에서 급여년월인 모든 데이터를 찾아서 사원번호 조건에 맞는지 일일이 확인하는 풀 테이블 스캔이 일어나고 있는 셈입니다.