🔖 Index
- 인덱스(Index) : 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
DB Index의 목적 : RDBMS에서 검색 속도를 높이기 위한 기술
500 페이지의 책에서 우리가 원하는 내용을 찾는다고 가정하자. 책의 모든 페이지를 찾아보는 것은 오랜 시간이 걸리기 때문에, 책의 저자들은 책의 맨 앞이나 뒤에 색인을 추가한다. 이 색인은 독자들이 자신이 원하는 내용을 빠르게 찾는 것을 도와준다.
데이터베이스의 Index도 책의 색인과 같은 역할을 수행한다. 데이터베이스에서 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에, 데이터와 데이터의 위치를 포함한 자료구조를 생성하여(= 인덱스를 활용하여) 빠르게 조회할 수 있도록 돕는다.
Table의 Column을 따로 파일을 만들어 색인화한다.
➡️ 해당 Table의 Record를 Full scan 하지 않는다. 색인화된 (B+ Tree 구조로) Index 파일 검색으로 검색 속도를 향상시킨다.
Index 과정
-
Table을 생성하면 MYD, MYI, FRM 3개의 파일이 생성된다.
FRM (Format file): 테이블 구조가 저장되어 있는 파일MYD (MySQL data file): 실제 데이터가 저장되어 있는 파일MYI (MySQL index file): 인덱스 정보가 저장되어 있는 파일
인덱스를 사용하지 않는 경우,
MYI는 비어있다.
이후 사용자가 SELECT 쿼리로 인덱스를 사용하는 컬럼을 조회하는 경우 MYI 파일의 내용을 검색한다.
Index의 장단점
장점
- 테이블을 조회하는 속도, 성능을 향상시킨다.
- 전반적인 시스템의 부하를 줄일 수 있다.
단점
- 인덱스를 관리하기 위한 저장공간이 필요하다(DB의 약10%).
- 인덱스 생성 시
.mdb파일 크기가 증가한다.
- 인덱스 생성 시
- 인덱스를 관리하기 위한 추가 작업이 필요하다.
- 데이터 변경 작업이 일어나는 경우, 인덱스를 다시 작성해야 한다.
- 인덱스를 잘못 사용할 경우, 오히려 성능이 저하되는 역효과가 발생할 수 있다.
Index를 사용하면 좋은 경우
위의 장단점에 의해, 인덱스는
- 규모가 작지 않은 테이블
INSERT,UPDATE,DELETE가 자주 발생하지 않는 컬럼JOIN이나WHERE,ORDER BY에 자주 사용되는 컬럼- 외래키가 사용되는 컬럼
- 데이터의 중복도가 낮은 컬럼
등의 특징을 갖는 곳에서 사용하는 것이 좋다.
INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼에 사용하면 좋은 이유
- DML(Data Manipulation Language) : 데이터 조작어로, 데이터베이스의 테이블에 들어있는 데이터를 조회하거나, 데이터에 변형을 가하는 명령어들이다.
INSERT,UPDATE,DELETE명령어는 DML 중 데이터에 변형을 가하는 명령어들이다.
DML이 일어났을 때의 상황은 아래와 같다.
- INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
- DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다고 처리한다.
- UPDATE : 기존의 인덱스를 사용하지 않는다고 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.
DELETE 와 UPDATE 의 경우, 기존의 인덱스를 삭제하는 것이 아니라 사용하지 않음 처리를 한다.
UPDATE와 DELETE가 빈번하게 발생한다면, 실제 데이터는 10만건이지만 인덱스는 100만건이 넘어가게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어질 수도 있다.
Reference
