Index(인덱스)란?
추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
즉, Table의 Column을 색인화함. (*B+ Tree구조)
B+ Tree 구조
데이터의 빠른 접근을 위한 인덱스 역할만 하는 비단말 노드가 추가로 있음. B Tree의 변형 구조로, index부분과 leaf노드로 구성된 순차 데이터 부분으로 이루어짐. 인덱스 부분의 key값은 leaf에 있는 key값을 직접 찾아가는데에 사용함.
Index의 원리
Index를 해당 컬럼에 주게 되면 초기 Table 생성 시, FRM, MYD, MYI 3개의 파일이 만들어진다.
- FRM : 테이블의 구조가 저장되어 있는 파일
- MYD : 실제 데이터가 있는 파일
- MYI : index 정보가 들어있는 파일
Index를 해당 컬럼에 만들게 되면, 해당 컬럼을 따로 인덱싱하여 MYI파일에 입력한다.
이후, 사용자가 SELECT쿼리로 Index를 사용하는 쿼리를 사용 시, 해당 테이블을 검생하는 것이 아닌 MYI파일의 내용을 검색한다. 만약 Index를 사용하지 않은 SELECT쿼리라면, 풀 스캔을 할 수 밖에 없다.
Index의 장단점
장점✨
- 키 값을 기초로 하기 때문에 검색, 정렬 속도를 향상시킴
- 테이블 행의 고유성을 강화시킬 수 있음.
- 테이블의 기본키는 자동으로 인덱싱 됨.
- 여러 필드로 이루어진 다중 필드 인덱스를 사용하면 필드 값이 같은 레코드도 구분이 가능함
단점🤔
- index생성시 .mdb 파일 크기가 증가한다.
- 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
- Index 된 필드에서 data를 업데이트하거나, record를 추가 또는 삭제 시 성능이 떨어진다.
- 데이터 변경 작업이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.
- Index를 생성하는 데에 시간이 많이 소요된다.
- Index가 데이터베이스 공간을 차지하기 때문에 추가적인 공간이 필요해진다. (*데이터베이스의 10%내외를 차지)
Index를 사용하면 좋은 상황
- where절에서 자주 사용되는 열
- 외래키가 사용되는 열
- Join에 자주 사용되는 열
Index를 사용하면 나쁜 상황
- Data 중복도가 높은 열 (-> 동시에 수정할 수 없기 때문에)
- DML이 자주 일어나는 열
📍 DML이란, 데이터베이스의 내부 데이터를 관리하기 위한 언어이다. 데이터를 조회 추가, 변경, 삭제 등의 작업을 수행하기 위해 사용된다. 예) SELECT, INSERT, UPDATE, DELETE
DML이 일어날 때 with Index
Insert
1. 새로운 Block을 할당 받은 후, Key를 옮기는 작업을 수행 (많은 양의 Redo가 기록되고, 유발)
2. Index Spilt 작업이 진행되는 동안, 해당 Block의 Key값에 대해서 DML이 블로킹 됨, 즉 대기 이벤트 발생
3. 기존 Block에 여유가 없을 때, 새로운 Data가 입력됨.
Delete
- Table에서 data가 delete 되는 경우 : Data가 지워지고, 다른 Data가 그 공간을 사용 가능
- Index에서 Data가 delete 되는 경우 : Data가 지워지지 않고, 사용 안 됨 표시만 해둠.
-> Table의 Data 수와 Index의 Data 수가 다를 수 있음.
Update
1. Table에서 update가 발생하는 경우 : Index는 Update 할 수 없음.
2. Index에서 update가 발생하는 경우 : **Delete가 먼저 발생**한 후에 새로운 작업의 **Insert작업이 수행**됨.