2025년 01월 중견기업 ERP 개발자로 취업했다.
DB를 다룰 일이 상당히 많다.
SQLD > SQLP 순으로 준비하며, DBA쪽으로 나아갈까도 생각중이다. 아니면 SAP?
곧 해외 지사 ERP와 본사(한국) ERP를 통합하는 글로벌 ERP 프로젝트가 시작될 예정이며, 이에 따라 데이터베이스 역시 통합될 예정이다.
INDEX
인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
우리가 책에서 원하는 내용을 찾으려고 책의 모든 페이지를 찾아 보는 것은 오랜 시간이 걸린다.
그렇기 때문에 책의 저자는 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, DB의 인덱스는 책의 색인과 같다.
DB에서 하나의 데이터를 찾기 위해 테이블을 Full-Scan 하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고있다.
인덱스를 사용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. UPDATE, DELETE도 해당 작업을 수행하기 전에 데이터를 조회하는 선행 작업을 수행하기 때문이다.
만약 Index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 된다. Full Scan은 이름 그대로 테이블 전체를 탐색하기 때문에 처리 속도가 느리다.
DBMS는 Index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.
INSERT - 새로운 데이터에 대한 인덱스를 추가한다.
DELETE - 실제로 삭제하는 데이터의 인덱스를 삭제하지 않고, 사용하지 않음 처리한다.
UPDATE - 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대한 인덱스를 추가한다.
이러한 이유로 인덱스를 설정할 때에는, 변경이 잦지 않은 속성에 설정을 하는 것이 적절하다.
만약 INSERT, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져, 성능이 저하되는 역효과가 발생한다. 주요 원인은 DELETE, UPDATE이다.
앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 사용하지 않음 처리를 하기 때문이다.
만약, 어떤 테이블에 UPDATE, DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.
인덱스의 장/단점
인덱스를 사용한다면 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있으며, 전반적인 시스템의 부하를 줄일 수 있다.
반면 인덱스를 관리하기 위해 DB의 약 10%의 저장공간이 필요하다. 또한, 인덱스를 관리하기 위해 추가 작업이 필요하며, 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
인덱스 사용이 적절한 CASE
- 규모가 작지 않은 테이블
규모가 작은 테이블(기준정보 테이블)에 조회를 하는 경우, 인덱스가 설정된 것과 Full Scan을 하는 것에는 큰 차이가 없다. 결과적으로는 불필요하게 DB의 저장공간만을 차지하게 된다. - INSERT, DELETE가 자주 발생하지 않는 속성
앞서 얘기한 내용이다. - JOIN, WHERE 또는 ORDER BY에 자주 사용되는 컬럼
컬럼 입장에서는 테이블의 레코드는 순서가 없이 저장된다. 이때 Where절의 특정 조건에 맞는 데이터를 찾기 위해서는 Full Scan을 하면서 조건에 부합하는지 비교해야 된다. 하지만, Index는 데이터가 정렬되어 있기 때문에 Where절의 조건에 맞는 데이터를 빠르게 찾아낼 수 있다.
또한, 인덱스를 사용하지 않을 경우 전체 테이블을 대상으로 Order By에 의한 정렬을 해야 된다. 하지만 인덱스를 사용할 경우 이미 정렬되어 있기 때문에 정렬에 필요한 자원을 소모할 필요가 없다.
인덱스(Index)의 자료구조
인덱스를 구현하기 위해서는 다양한 자료구조를 사용할 수 있는데, 가장 대표적으로 해시 테이블과 B+Tree가 있다.
해시 테이블(Hash Table)
해시 테이블(Key, Value)로 데이터를 저장하는 자료구조 중 하나로 빠른 데이터 검색이 필요할 때 유용하다. 해시테이블은 Key값을 이용해 고유한 Index를 생성하여 그 Index에 저장된 값을 꺼내오는 구조이다.
해시 테이블 기반의 DB 인덱스는 데이터 -> 컬럼의 값, 데이터의 위치를 Key, Value로 사용하여 컬럼의 값으로 생성된 해시를 통해 인덱스를 구현하였다. 키로 인덱스의 위치를 찾을 수 있는 해시 테이블의 시간복잡도는 O(1)이다.
하지만, DB 인덱스에서 해시 테이블이 사용되는 경우는 매우 제한적이다. 그러한 이유는 해시가 등호(=)에만 특화되었기 때문이다. 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산이 자주 사용되는 DB 검색에는 해시 테이블이 적합하지 않다.
이러한 이유로 DB의 Index에는 B+Tree가 일반적으로 사용된다.
B+Tree
B+Tree는 DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다. B+Tree는 모든 노드에 데이터를 저장했던 B-Tree와 다른 특성을 가지고있다.
B+Tree의 특성
- Leaf Node(데이터 노드)만 인덱스와 함께 데이터를 가지고 있고, other Nodes(인덱스 노드)들은 데이터를 위한 인덱스만을 갖는다.
- Leaf Node들은 LinkedList로 연결되어 있다.
- 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.
앞서 말 했듯이 DB의 인덱스 컬럼은 부등호를 이용한 순사 검색 연산이 자주 발생된다. 이러한 이유로 B+Tree의 리프노드는 서로 LinkedList로 연결되어, 형제 노드끼리도 옮겨가며 조회할 수 있다. 연결된 리프노드의 리스트를 따라가면서 범위 쿼리를 할 수 있어서 범위 검색 성능이 있다.
물론, B-Tree의 BEST CASE에 대해 리프노드까지 가지 않아도 탐색할 수 있는 것에 비해 B+Tree는 무조건 리프노드까지 가야되는 단점이 있다.
이러한 이유로 B+Tree는 O(𝑙𝑜𝑔2𝑛)의 시간복잡도를 갖지만 해시테이블보다 인덱싱에 적합한 자료구조이다.
Internal노드에는 데이터의 포인터가 없으며, 오로지 키만 저장된다.
- 데이터를 찾기 위한 포인터도 리프노드에만 있다.
- Internal 노드의 크기를 줄여 메모리 사용이 효율적이다.
B+Tree의 모든 노드의 키는 항상 정렬된 상태를 유지한다. - Internal 노드의 키와 Leaf Node의 키는 모두 오름차순 정렬되어 있다.
새로운 데이터의 삽입 및 삭제가 비교적 간단하다. - INSERT 시에는 Leaf Node에 새로운 데이터를 추가한다.
- DELETE 시에는 데이터를 제거하면서 B+Tree의 균현을 유지하고, 저장공간이 절약된다.
Clustered Index, Non-Clustered Index
Clustered Index
Clustered Index는 테이블의 레코드를 지정된 컬럼에 대해 물리적으로 재배열한다. Clustered Index는 테이블 당 한 개만 존재할 수 있고, PK 제약조건을 지정하는 컬럼에 대해 자동으로 생성된다. 그렇기 때문에 우리가 일반적으로 테이블을 생성할 때 특정 컬럼에 PK 제약조건을 지정한다면, 데이터가 자동으로 정렬되는 것이다.
Clustered Index를 생성한 컬럼을 기준으로 테이블의 데이터가 정렬되어 있기 때문에 속도면에서 우수한 성능을 보인다. 하지만, 데이터의 추가/수정/삭제 시 매번 레코드를 정렬해야 하기 때문에 추가/수정/삭제의 성능이 저하된다.
Clustered Index의 문제점
만약 ID값이 1,3,4인 데이터를 가지고 있는 상태에서 ID값이 2인 데이터를 추가 저장한다고 생각해보자.
Clustered Index는 지정된 컬럼을 기준으로 데이터를 정렬하기 때문에 ID값이 2인 데이터를 추가할 경우 ID가 2보다 큰 데이터는 한 칸씩 아래로 이동하고, 2번째 위치에 데이터가 추가된다.
이 예시에서는 데이터 2개만 뒤로 밀려나지만, 데이터가 100만 건이 있다고 생각해보면 INSERT에 소모되는 비용이 굉장히 크다.
그렇기 때문에 PK를 어떤 컬럼으로 선택하는가에 따라 DB의 성능이 좌우된다.
이러한 이유로 ID라는 별도의 필드를 PK로 설정하고 Auto_Increment 옵션을 주어 Clustered Index에서 발생할 수 있는 문제점을 해결한다.
Clustered Index 구조
Clustered Index를 구성하기 위해 레코드를 해당 컬럼으로 정렬한 후에, 루트 페이지를 만들게 된다. Clustered Index는 Root Page와 Leaf Page로 구성되며, Leaf Page는 데이터 그 자체이다. 즉, Index 자체에 데이터가 포함된다.
Index Page를 키 값과 데이터 페이지 번호로 구성하고, 검색하고자 하는 데이터의 키 값으로 페이지 번호를 검색하여 데이터를 찾는다.
Non-Clustered Index
Non-Clustered Index는 물리적으로 레코드를 정렬하지 않은 상태로 Data Page가 구성된다. 즉, 테이블의 레코드는 그대로두고 지정된 컬럼에 대해 정렬된 인덱스를 만든다.
물리적으로 레코드를 정렬하지 않기 때문에 Clustered Index보다 속도면에서 성능이 떨어지지만, 추가/수정/삭제의 성능이 더 뛰어나다.
Non-Clustered Index는 Unique 제약조건을 설정한 컬럼에 대해 자동으로 Non-Clustered Index를 생성한다. 따라서 테이블 당 여러개 존재 가능하다. 하지만 함부로 남용하면 오히려 시스템 성능이 저하될 수 있다.
Non-Clustered Index 구조
Non-Clustered Index는 데이터 페이지를 건들지 않고, 별도의 장소에 인덱스 페이지를 생성한다. Non-Clustered Index의 인덱스 페이지는 키값과 위치 포인터(ROWID)로 구성된다.
ROWID는 '파일그룹번호-데이터페이지번호-데이터페이지오프셋' 으로 구성되는 포인팅 정보이다.
우선 인덱스 페이지의 리프 페이지에 인덱스로 구성된 컬럼을 정렬하고 ROWID를 생성한다. ROWID는 Clustered Index와 달리 '페이지번호+#오프셋' 이 기록되어 바로 데이터 위치를 가리킨다.
Clustered Index & Non-Clustered Index
Multi-Column Index
다중 컬럼 인덱스는 두 개 이상의 컬럼을 조합하여 생성한 인덱스이다.
다중 컬럼 인덱스에서 가장 중요한 것은 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다는 것이다.
즉, 두 번째 컬럼은 첫 번째 컬럼의 값이 같은 레코드에서만 정렬되어 있다. 따라서 다중 컬럼 인덱스에서는 컬럼의 순서가 상당히 중요하다.
'=' 조건과 같이 개수가 적은 데이터를 조회하는 컬럼을 앞에 설정하고, 범위 검색과 같이 개수가 많은 데이터를 조회하는 컬럼을 뒤쪽에 설정해야 효율적이다.
또한, 다중 컬럼 인덱스는 단일 컬럼 인덱스보다 추가/수정/삭제 시 더 비효율적이기 때문에 가급적으로 추가/수정/삭제를 하지 않는 컬럼을 선정하는 것이 더 좋다.
Multi-Column Index 사용시기
데이터 조회 시 단일 컬럼 인덱스를 여러 개를 사용해야 하는 경우가 많다면 다중컬럼 인덱스를 고려해볼 수 있다.
예를 들어 A, B 컬럼을 조건절에 포함한 검색을 자주한다고 가정해보자.
A, B 컬럼 각각 인덱스를 설정할 경우 Optimizer는 A 컬럼과 B 컬럼 중 어떤 컬럼이 더 빠르게 검색되는지 판단하고 더 빠른 컬럼의 인덱스를 통해 레코드를 탐색하고 이 레코드에서 B 컬럼을 탐색한다.
만약, 각각의 인덱스를 복합 인덱스로 설정할 경우 인덱스에 A와 B 컬럼의 정보가 있기 때문에 바로 탐색이 가능하므로 위의 방식보다 빠르다.
하지만 Where절에 B 컬럼만 사용할 경우, 이 복합 인덱스는 B가 A에 의존적으로 정렬되기 때문에 해당 인덱스를 탐색하지 않는다.
EX) 테이블 T에 컬럼 A,B가 PK일 때, WHERE 조건에 B만 사용하면 인데스를 타지 않는다.
