DB를 보기 이전에, 컴퓨터 장치 요소 중 하나인 보조기억장치에 대해 얘기해보자.
보통 Database의 저장장치는 비싼 SSD 대신 HDD를 사용한다. 저장해야할 데이터양을 비교하면 당연 HDD보다 SSD가 훨씬 비싸다. (클라우드 서비스에서 SSD instance도 지원하지만.. 가격이 비싸다🤑)
HDD는 디스크 구조로 되어있으며, 물리적인 회전을 통해 데이터를 읽고 쓴다.
Disk-based 저장소에서 데이터를 불러올 때, 디스크에서 데이터를 읽어와 메모리에 넣고, 메모리에서 데이터를 처리한 후 다시 디스크에 쓰게 되는데, 이를 디스크 I/O라고 부른다. 디스크 I/O 에서 읽고 쓸때, 디스크 헤더가 물리적으로 움직이며 데이터를 읽고 쓸 위치로 옮기는 단계에서 총 시간이 결정된다.
일반적으로 디스크 I/O 는 Sequence Access(순차접근), Random Access(임의접근)가 있지만, 현실에선 Random Access 위주로 작업한다.
Database에서 정보를 읽고 쓸 때도 역시 디스크I/O 가 발생한다. DB 쿼리 튜닝의 목적은 이 과정에서 성능을 좀더 개선시켜주는 것이라 생각하자.
DB Index
인덱스는 단어정의 그대로 데이터에 색인을 추가하는 것이다. 책에 비유하자면 600페이지 가량의 책 내용을 모두 기억할 수 없으니 목차, 글소개 등, 페이지 번호에 맞는 인덱스 목록을 책 맨 앞 목차로 넣어두는 것이다.
DB에 저장된 데이터가 많아질수록, 데이터 검색에도 더 많은 시간이 소요된다. 인덱스를 적용하게 되면 해당 레코드가 저장된 주소를 key-value 쌍으로 만들고 이를 통해 보다 빠른 검색이 가능하다.
Index의 특징은 아래와 같다.
장점
- 테이블 조회 성능을 향상시킬 수 있다
- Database 시스템의 부하를 줄일 수 있다
단점
- Index 관리를 위해 Database의 약 10% 정도의 공간과 작업이 필요하다
- 부적절한 Index 사용 시 역효과로 성능저하가 발생할 수 있다
이에 대해선 아래 예제 설명 후 자세히 얘기해보자.
위 예시를 보면, Location과 Data가 저장된 원본테이블 TABLE과 index를 저장하는 테이블 INDEX가 별도로 존재한다. INDEX 테이블의 Location 은 원본 테이블 TABLE 의 Location 을 가리키고 있으며, index를 통한 검색 시 사용된다.
index를 활용해 검색 시, WHERE 쿼리문을 사용하면 자동으로 index 처리된 column의 경우 아래와 같은 순서로 데이터를 검색한다.
- INDEX 테이블에서 WHERE 문에 포함된 값(index 적용된
Data)검색 - 찾는 값의 TABLE ID(
INDEX의 Location) 조회 - 원본테이블(
TABLE)에서 ID에 해당하는 값 조회 후 반환
🤔 Index 테이블이 추가로 생성된 것인데 왜 성능이 더 좋아지는 것일까?
검색 중 유튜브를 참고했다. 30분 강의이며 끝까지 보는걸 추천한다.
YouTube [6:38 ~ 13:20]
10.2 B Trees and B+ Trees. How they are useful in Databases — YouTube
예시
Employee 데이터를 위 그림구조로 저장한다고 가정.
디스크의 블럭 사이즈는 512byte이다.100개의 Employee record
한 블럭(512byte)에 Employee(128byte) 는 4개의 record 저장가능
총 100개의 record를 저장하려면 25개의 block 필요100개 Employee의 Index
한 블럭(512byte)에 Index(16byte) 는 32개의 entity 저장가능
총 100개의 데이터의 Index를 저장하려면 약 3.2개의 block 필요100개의 Employee 에서 한명을 검색한다고 했을때,
일반 record로 검색시, 25개의 block 검색 (full scan)
Index 된 record로 검색시, 5개(4개 Index block+실제 데이터 1 block) 검색
∴ Index 된 record 검색(5개) < 일반 record 검색(25개) -> 검색할 block 개수가 적기 때문에 Index된 데이터 검색 속도가 더 빠르다.
DB Index가 어떤 원리로 빠른 조회가 가능한지 예제를 통해 간단히 알아봤다.
그럼, 단점에 해당하는 성능저하는 어떻게 발생하는것일까?
위 예제에서 원본 데이터와 Index 테이블이 별도로 관리되고 있다. 만약, 원본 데이터에 새로운 데이터가 추가/수정 시 Index 테이블에도 새로운 Index가 생기거나 기존 Index의 수정이 일어날 수 있다.
Index 작업에서 UPDATE, DELETE가 성능저하의 주된 요인이다.
- UPDATE: Index는 수정개념이 없어서 DELETE->INSERT 로 이루어짐.
- DELETE: 데이터가 지워지는게 아니라 사용하지 않도록 표시를 해준다.
보통 데이터가 삭제되면 다른 데이터가 그 공간을 사용할 수 있는데, Index는 이런 처리가 이루어지지 않는다.
결론적으로, Index가 적용된 필드의 INSERT, UPDATE, DELETE 가 일어날 때 성능저하가 발생하며, Index를 저장하는 공간도 커져 변경이 자주 일어나게 되면 Index를 재생성해야 할 수도 있다.
DB Index 는 용량과 속도를 교환하여 성능을 올리는 것이다!
📝 Index 선정시 유의사항
위의 장점과 단점을 통해, Index 를 적용할 column 선정시 몇 개의 기준을 정할 수 있다. (Cardinality와 Selectivity에 관한 설명은 블로그 참고)
- 데이터 갱신보다 조회가 많은 column
- Cardinality(Unique value 개수)가 높은 column: 중복도가 낮음
*Cardinality: 전체 data 중 한 값의 개수 - Selectivity(선택도)가 낮은 column
*Selectivity: Cardinality / Total number of records - 중복도가 낮은 column: 2,3번과 같은 문맥
- 활용도가 높은 column: WHERE 문에 자주 쓰이는 값일수록 좋음
DB Index의 자료구조는 Hash Table, B+Tree 등 구조를 사용한다. 이에 대한 설명은 추후 추가하도록 하겠다. To be continued..
Python 예제코드
이제 대략적인 내용은 알아봤으니 예제를 통해 얼마나 큰 성능차이를 보이는지 비교해보자.
위 코드는 간단한 User 모델과 Index를 적용한 UserIndex 모델의 데이터를 각 1,000,000개씩 생성 후, 특정 값을 검색했을 때 시간이 얼마나 걸리는지 비교하는 코드이다. 정말 단순하게 짠 코드라 결과만 보고 참고해도 된다..
...
2022-02-17 18:40:27,066 INFO sqlalchemy.engine.Engine COMMIT
-----user without index-----
0.000514000 sec
0.000049000 sec
0.000033000 sec
0.000028000 sec
0.000027000 sec
0.000027000 sec
0.000026000 sec
0.000025000 sec
0.000025000 sec
0.000024000 sec
avg time: 0.000076300
-----user with index-----
0.000041000 sec
0.000025000 sec
0.000025000 sec
0.000026000 sec
0.000026000 sec
0.000026000 sec
0.000025000 sec
0.000025000 sec
0.000025000 sec
0.000024000 sec
avg time: 0.000025800약 10번의 평균실행시간을 계산해보니, Index를 적용 전후 시간이 약 2~4배 정도 차이가 났다. 데이터가 많아질수록 성능차이가 더 크게 발생하니, 직접 예제를 실행해보는 것도 좋다.
DB Index는 서비스 개발에서 빠질 수 없는 요소라 생각한다. 데이터 양이 많아질수록 단순 Scale-up 처리만으로 처리할 수 없을 것이고, 개발자가 나름의 DB 튜닝과 이를 위한 공부 또한 필수이다.
내가 필요한 기능을 온전히 이해하고 쓰는 날까지..🧑💻
추가로 읽어보면 좋은 글
[Database] 인덱스(index)란? — MangKyu’s Diary (tistory.com)
Interview_Question_for_Beginner/Database at master · JaeYeopHan/Interview_Question_for_Beginner (github.com)
