DB 정리 - 1 (데이터 베이스 / 인덱스)

데이터베이스

데이터베이스란?

데이터베이스(DB, database)는 통합하여 관리되는 데이터의 집합체를 의미한다.

이는 중복된 데이터를 없애고, 자료를 구조화하여, 효율적인 처리를 할 수 있도록 관리된다. 따라서, 여러 업무에 여러 사용자가 데이터 베이스를 사용할 수 있습니다.

이러한 데이터베이스는 응용 프로그램과는 다른 별도의 미들웨어에 의해 관리되며, 이를 데이터베이스 관리 시스템(DBMS: Database Management System)이라고 한다.

데이터베이스를 사용하는 이유

데이터베이스란 다수의 사용자가 사용하는 데이터들의 공유와 운영을 위해 저장해 놓는 공간을 말한다. 프로그램을 만들다보면 프로그램 사용자들에 의해 생성된 데이터, 프로그래머가 필요에 의해 프로그램에 넣어놓은 데이터등 필연적으로 많은 데이터들이 생성되어지게 되는데 데이터베이스를 사용하지 않으면 이 데이터들은 프로그램을 종료하는 순간 전부 날아가게 되기 때문에, 이런현상을 방지하기 위해 데이터들을 데이터베이스에 넣고 보관하는 방법을 사용한다.

데이터베이스가 존재하기 이전에는 파일 시스템을 이용하여 데이터를 관리하였다. (현재도 부분적으로 사용되고 있다.) 데이터를 각각의 파일 단위로 저장하며 이러한 일들을 처리하기 위한 독립적인 애플리케이션과 상호 연동이 되어야 한다. 이 때의 문제점은 데이터 종속성 문제와 중복성, 데이터 무결성이다.

File System vs Database System

1. File System

File System도 데이터를 저장하는데 문제가 없습니다. 그리고 여러개의 파일을 모아 관한다면 하나의 System을 형성합니다. 위 이미지에서는 인사, 영업, 생산이라는 파일을 갖고 File System을 구성해봤습니다. 만약 A라는 사원이 인사팀에서 영업팀으로 이동하게 된다면 인사파일에서 내용을 지우고 영업파일에 추가를 해줘야한다.

하지만 만약 누군가 동시에 파일을 접근하게 된다면 어떻게 될까? 누군가 한명은 작업을 못하거나, 파일의 내용의 싱크가 서로 안맞는 경우가 발생할 수 있다. 이것이 위에서 이야기한 데이터 종속성 문제와 중복성, 데이터 무결성 과 같은 문제이다.

이를 해결하기 위해 제시되는 방법이 Database System이다.

2. Database System

인사, 영업, 생산이라는 파일을 각각 다른 파일로 저장을 했던 File System과 달리 하나로 필요한 내용들을 하나의 Database로 관리한다면 어떻게 될까? 이는 DB의 특징과 연관되어 볼 수 있다.

데이터베이스의 특징

데이터의 독립성
- 물리적 독립성 : 데이터베이스 사이즈를 늘리거나 성능 향상을 위해 데이터 파일을 늘리거나 새롭게 추가하더라도 관련된 응용 프로그램을 수정할 필요가 없다.
- 논리적 독립성 : 데이터베이스는 논리적인 구조로 다양항 응용 프로그램의 논리적 요구를 만족시켜줄 수 있다.
데이터의 무결성
여러 경로를 통해 잘못된 데이터가 발생하는 경우의 수를 방지하는 기능으로 데이터의 유효성 검사를 통해 데이터의 무결성을 구현하게 된다.
데이터의 보안성
인가된 사용자들만 데이터베이스나 데이터베이스 내의 자원에 접근할 수 있도록 계정 관리 또는 접근 권한을 설정함으로써 모든 데이터에 보안을 구현할 수 있다.
데이터의 일관성
연관된 정보를 논리적인 구조로 관리함으로써 어떤 하나의 데이터만 변경했을 경우 발생할 수 있는 데이터의 불일치성을 배제할 수 있다. 또한 작업 중 일부 데이터만 변경되어 나머지 데이터와 일치하지 않는 경우의 수를 배제할 수 있다.
데이터 중복 최소화
데이터베이스는 데이터를 통합해서 관리함으로써 파일 시스템의 단점 중 하나인 자료의 중복과 데이터의 중복성 문제를 해결할 수 있다.

데이터베이스의 성능?

데이터베이스의 성능 이슈는 디스크 I/O 를 어떻게 줄이느냐에서 시작된다.
디스크 I/O 란 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다. 이 때 데이터를 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 즉 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 따라 결정된다고 볼 수 있다.

그렇기 때문에 순차 I/O 가 랜덤 I/O 보다 빠를 수 밖에 없다. 하지만 현실에서는 대부분의 I/O 작업이 랜덤 I/O 이다. 랜덤 I/O 를 순차 I/O 로 바꿔서 실행할 수는 없을까? 이러한 생각에서부터 시작되는 데이터베이스 쿼리 튜닝은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다.

이러한 성능 개선을 위한 방법으로는 인덱스, 옵티마이저, 분산기법 (클러스터링, 레플리케이션, 샤딩)등이 있다.

데이터베이스 튜닝의 3단계 (자료 출처)

1단계 DB 설계 튜닝 (모델링 관점)	데이터베이스 설계 단계에서 성능 고려하여 설계 데이터 모델링, 인덱스 설계 데이터파일, 테이블 스페이스 설계 데이터베이스 용량 산정	반정규화 분산파일배치
2단계 DBMS 튜닝 (환경 관점)	성능을 고려하여 메모리나 블록 크기 지정 CPU, 메모리 I/O에 관한 관점	Buffer 크기 Cache 크기
3단계 SQL 튜닝 (APP 관점)	SQL 작성 시 성능 고려 Join, Indexing, SQL Execution Plan	Hash / Join

데이터베이스 튜닝 영역별 세부적인 기법 (자료 출처)

DB 설계 튜닝 영역	테이블 분할 및 통합	파티션 기능, 테이블 수평/수직 분할
	식별자 지정/Key 설정	본질/인조 식별자 정의, 클러스터링
	효율적 인덱스 설정	인덱스 분포도 고려 10~15%(손익 분기점)
	정규화/반정규화	테이블, 컬럼, 관계 정규화/반정규화
	적절한 데이터 타입 선정	조인 시 연결되는 데이터 타입 일치
	데이터 모델링	슈퍼/서브 타입, PK, 파티셔닝, 데이터 통합
DBMS 튜닝 영역	I/O 최소화	실제 필요한 데이터만 Read, Query off-loading
	Buffer Pool 튜닝	지역성 관점 데이터 관리, Keep Buffer Cache
	Commit/Check Point	Check Point 수행주기 조절, Commit 주기 조정
	Thread/Reuse	Middleware 기능과 연동
SQL 튜닝 영역	Undo Segment 설정	Undo 영역 크기 조정
	옵티마이저	RBO/CBO 이해, 통계정보 최신화
	힌트 사용	지원되는 힌트 기반 실행계획 유도
	부분범위 처리	일부만 Access, 옵티마이저 정보 제공
	인덱스 활용	인덱스 기반 조회 속도 향상, Sort 연산 대체
	조인 방식 / 순서	실행계획(Plan) 확인 후 조정
	동적 SQL 지양	파싱(Parsing) 부하 감소위한 Static SQL 사용
	다중 처리	한 번의 DBMS 호출로 여러 건 동시 처리
	병렬 처리	하나의 SQL을 여러 개의 CPU가 분할 처리
	SORT 튜닝 수행	인덱스 기반 MIN, MAX 구하기, TOP-N 쿼리

튜닝의 순서인 DB 설계 → DBMS → SQL 튜닝 순으로 효율성이 줄어듦에 따라 우선적으로 처리하는 것이 좋다.
SW 처리가 불가능 하거나 효율적이지 않는 경우 HW 방식의 시스템 데이터베이스 튜닝 필요

Index

인덱스(Index)란?

인덱스는 말 그대로 책의 맨 처음 또는 맨 마지막에 있는 색인이라고 할 수 있다.

이 비유를 그대로 가져와서 인덱스를 살펴본다면 데이터는 책의 내용이고 데이터가 저장된 레코드의 주소는 인덱스 목록에 있는 페이지 번호가 될 것이다. DBMS 도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져 오려면 시간이 오래 걸린다. 그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.

DBMS 의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만 새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있다.

장점

테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
전반적인 시스템의 부하를 줄일 수 있다.

단점

Index 생성시, .mdb 파일 크기가 증가한다.
한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
인덱스 된 Field에서 Data를 업데이트하거나, Record를 추가 또는 삭제시 성능이 떨어진다.
데이터 변경 작업이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.

사용하면 좋은 경우

Where 절에서 자주 사용되는 Column
외래키가 사용되는 Column
Join에 자주 사용되는 Column
ORDER BY 절에서 자주 사용되는 Column
항상 = 으로 비교되는 Column
중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) Column

Index 사용을 피해야 하는 경우

Data 중복도가 높은 Column
DML이 자주 일어나는 Colum

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그렇기 때문에 사용하지 않는 인덱스는 바로 제거를 해주어야 한다.

Index 자료구조

그렇다면 DBMS 는 인덱스를 어떻게 관리하고 있을까?

1. B+-Tree 인덱스 알고리즘

일반적으로 사용되는 인덱스 알고리즘은 B+-Tree 알고리즘이다. B+-Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다.), 원래의 값을 이용해 인덱싱하는 알고리즘이다.

리프노드(데이터노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지 노드(인덱스노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.
리프노드들은 LinkedList로 연결되어 있다.
데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

2. Hash 인덱스 알고리즘

칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

왜 index 를 생성하는데 b-tree 를 사용하는가?

이렇게 시간만을 확인하면, 데이터에 접근하는 시간복잡도가 O(1)인 hash table 이 더 효율적일 것 같다는 생각이 들 수 있다. 하지만, SELECT 질의의 조건에는 부등호(<>) 연산도 포함이 된다. hash table 을 사용하게 된다면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. 동등 연산(=)에 특화된 hashtable은 데이터베이스의 자료구조로 적합하지 않다.

그러한 이유는 해시가 등호(=) 연산에만 특화되었기 때문이다. 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산(>, <)이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다. 즉, 예를 들면 "나는"으로 시작하는 모든 데이터를 검색하기 위한 쿼리문은 인덱스의 혜택을 전혀 받지 못하게 된다. 이러한 이유로 데이터베이스의 인덱스에서는 B+Tree가 일반적으로 사용된다.

Primary Index vs Secondary Index

클러스터(Cluster)란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, 클러스터드 인덱스도 크게 다르지 않다.

인덱스에서 클러스터드는 비슷한 것들을 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안된 것이다. 여기서 비슷한 값들은 물리적으로 인접한 장소에 저장되어 있는 데이터들을 말한다.

클러스터드 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 쉽계 예를 들어보자면 페이지를 알고 있어서 바로 해당 페이지를 펼치는 것과 같다.

즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터드 인덱스라고 표현한다. 클러스터드 인덱스에서는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되며 프라이머리 키 값이 변경되면 그 레코드의 물리적인 저장 위치 또한 변경되어야 한다. 그렇기 때문에 프라이머리 키를 신중하게 결정하고 클러스터드 인덱스를 사용해야 한다.

클러스터드 인덱스는 테이블 당 한 개만 생성할 수 있다. 프라이머리 키에 대해서만 적용되기 때문이다, 이에 반해 non 클러스터드 인덱스는 테이블 당 여러 개를 생성할 수 있다.

clustered index의 예시. page id를 기준으로 묶여있다.

non 클러스터드 인덱스는 물리적으로 데이터를 배열하지 않은 상태로 데이터 페이지가 구성된다. 즉, 테이블의 데이터는 그대로두고 지정된 컬럼에 대해 정렬시킨 인덱스를 만들 뿐이다. 책으로 비유하자면 책 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그리고 나서 해당 페이지로 이동하는것과 같다.

Non-Clustered Index는 Clustered Index보다 검색 속도는 느리지만, 데이터의 입력/수정/삭제는 더 빠르다. 하지만, 함부로 남용하면 오히려 시스템 성능을 떨어뜨린다.

non- clustered index의 예시. 배열하지 않은 채로 데이터 페이지가 구성된다.

Composite Index

Composite Index(결합 인덱스)란 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말한다.

주로 SQL 문장에서 WHERE절의 조건 컬럼이 2개 이상 AND로 연결되어 함께 사용되는 경우에 많이 사용하게 되며, 인덱스로 설정하는 필드의 속성이 중요하다. title, author 이 순서로 인덱스를 설정한다면 title 을 search 하는 경우, index 를 생성한 효과를 볼 수 있지만, author 만으로 search 하는 경우, index 를 생성한 것이 소용이 없어진다. 따라서 SELECT 질의를 어떻게 할 것인가가 인덱스를 어떻게 생성할 것인가에 대해 많은 영향을 끼치게 된다.

Index 의 성능과 고려해야할 사항

SELECT 쿼리의 성능을 월등히 향상시키는 INDEX 항상 좋은 것일까? 쿼리문의 성능을 향상시킨다는데, 모든 컬럼에 INDEX 를 생성해두면 빨라지지 않을까?

결론부터 말하자면 그렇지 않다.

우선, 첫번째 이유는 INDEX 를 생성하게 되면 INSERT, DELETE, UPDATE 쿼리문을 실행할 때 별도의 과정이 추가적으로 발생한다. INSERT 의 경우 INDEX 에 대한 데이터도 추가해야 하므로 그만큼 성능에 손실이 따른다. DELETE 의 경우 INDEX 에 존재하는 값은 삭제하지 않고 사용 안한다는 표시로 남게 된다. 즉 row 의 수는 그대로인 것이다. 이 작업이 반복되면 어떻게 될까?

실제 데이터는 10 만건인데 데이터가 100 만건 있는 결과를 낳을 수도 있는 것이다. 이렇게 되면 인덱스는 더 이상 제 역할을 못하게 되는 것이다. UPDATE 의 경우는 INSERT 의 경우, DELETE 의 경우의 문제점을 동시에 수반한다. 이전 데이터가 삭제되고 그 자리에 새 데이터가 들어오는 개념이기 때문이다. 즉 변경 전 데이터는 삭제되지 않고 insert 로 인한 split 도 발생하게 된다.

하지만 더 중요한 것은 컬럼을 이루고 있는 데이터의 형식에 따라서 인덱스의 성능이 악영향을 미칠 수 있다는 것이다. 즉, 데이터의 형식에 따라 인덱스를 만들면 효율적이고 만들면 비효율적은 데이터의 형식이 존재한다는 것이다. 어떤 경우에 그럴까?

이름, 나이, 성별 세 가지의 필드를 갖고 있는 테이블을 생각해보자. 이름은 온갖 경우의 수가 존재할 것이며 나이는 INT 타입을 갖을 것이고, 성별은 남, 녀 두 가지 경우에 대해서만 데이터가 존재할 것임을 쉽게 예측할 수 있다. 이 경우 어떤 컬럼에 대해서 인덱스를 생성하는 것이 효율적일까? 결론부터 말하자면 이름에 대해서만 인덱스를 생성하면 효율적이다.

왜 성별이나 나이는 인덱스를 생성하면 비효율적일까? 10000 레코드에 해당하는 테이블에 대해서 2000 단위로 성별에 인덱스를 생성했다고 가정하자. 값의 range 가 적은 성별은 인덱스를 읽고 다시 한 번 디스크 I/O 가 발생하기 때문에 그 만큼 비효율적인 것이다.

정리하자면,

새로 추가되는 인덱스가 기존 엑세스 경로에 영향을 미칠 수 있음을 유의한다.
지나치게 많은 인덱스는 오버헤드로 작용한다.
인덱스는 추가적인 저장공간이 필요하다.
넓은 범위를 인덱스 처리할 시, 오히려 전체 처리보다 많은 오버헤드를 발생시킬 수 있따.
인덱스와 테이블 데이터의 저장공간을 적절히 분리할 수 있도록 설계해야 한다.

참고 자료
공통
https://github.com/JaeYeopHan/Interview_Question_for_Beginner/tree/master/Database

데이터베이스란?
https://coding-factory.tistory.com/77
https://myjamong.tistory.com/165
http://blog.skby.net/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%ED%8A%9C%EB%8B%9D-db-tuning/

Index 란?
https://mangkyu.tistory.com/96
https://github.com/gyoogle/tech-interview-for-developer/blob/master/Computer%20Science/Database/%5BDB%5D%20Index.md
https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

'CS 공부 > 데이터베이스' 카테고리의 다른 글

DB 정리 4 - Redis, Statement (0)	2021.09.22
DB 정리 - 2 (정규화, 트렌젝션, 트렌젝션 격리수준) (0)	2021.09.14