Index
Index (인덱스, 색인)
데이터베이스에서 Index란 책의 맨 처음 또는 마지막에 존재하는 색인과 같다. 데이터를 책의 내용, 데이터가 저장된 레코드의 주소를 페이지 번호와 같이 생각하면 된다.
데이터베이스에서 데이터를 검색할 때 모든 데이터를 검색해서 원하는 결과를 찾게 된다면 시간이 오래 걸린다. 이 때문에 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어두어 데이터 검색 속도를 향상시킨다.
DBMS의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 빠르게 검색할 수 있다. 하지만 새로운 값을 추가하거나 삭제, 수정하는 경우에는 실행 속도가 느려진다. 다시 말해 DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 읽기 속도를 높이는 기술이다. 따라서 SELECT 쿼리의 WHERE 조건절에 사용할 컬럼이라고 전부 색인화하면 데이터 저장 성능이 떨어져 오히려 역효과만 날 수 있으니 주의해야 한다.
Index 원리
Index를 해당 컬럼에 주게 되면 초기 Table 생성 시 FRM, MYD, MYI 3개의 파일이 만들어진다.
- FRM: 테이블 구조가 저장되어 있는 파일
- MYD: 실제 데이터가 있는 파일
- MYI: Index 정보가 들어있는 파일
Index를 사용하지 않는 경우에는 MYI 파일이 비어있다. 그러나 Index를 해당 컬럼에 만들게 되면 해당 컬럼을 따로 인덱싱하여 MYI파일에 입력한다.
이후에 사용자가 SELECT 쿼리로 Index를 사용하는 쿼리를 사용 시, 해당 테이블을 검색하는 것이 아닌 MYI 파일의 내용을 검색한다. 만약 Index를 사용하지 않은 SELECT 쿼리라면, 해당 테이블을 풀 스캔하여 데이터를 검색하게 된다.
Index 자료구조
B+ Tree 인덱스 알고리즘
가장 일반적으로 사용되는 알고리즘이다. B+ Tree 인덱스는 컬럼의 값을 변경하지 않고, 원래의 값을 이용해 색인화하는 알고리즘이다.
Hash 인덱스 알고리즘
컬럼의 값으로 해시 값을 계산하여 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형하여 인덱싱하므로, 특정 문자로 시작하는 값을 검색하는 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
Index를 생성하는데 B Tree를 사용하는 이유
데이터에 접근하는 시간복잡도가 O(1)인 해시 테이블이 더 효율적으로 보인다. 하지만 SELECT 질의 조건에는 부등호(<, >) 연산도 포함이 된다. 해시 테이블을 사용하여 접근하게 된다면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. Hash Table은 동등(=) 연산에 특화되었기에 데이터베이스의 자료구조로 적합하지 않다.
Index 장점
- 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
- 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
- 테이블의 기본 키는 자동으로 인덱싱된다.
- 필드 중에는 테이블 형식 때문에 인덱싱 할 수 없는 필드도 있다.
- 여러 필드로 이루어진 다중 필드 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.
cf. 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.
Index 단점
- 인덱스를 만들면 .mdb 파일 크기가 늘어난다.
- 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
- 인덱스된 필드에서 데이터를 업데이트 하거나 레코드를 추가 또는 삭제할 때 성능이 떨어진다.
- 인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요하다. DB의 10% 내외의 공간이 추가로 필요하다.
- 데이터 변경 작업이 자주 일어나는 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.
위와 같은 단점들 때문에 어느 필드를 인덱싱할지 미리 시험해본 후에 결정하는 것이 좋다. 인덱스를 추가하여 쿼리 속도가 1초 정도 빨라진다면 데이터 행을 추가하는 속도는 2초정도 느려지고, 이 경우 여러 사용자가 사용한다면 레코드 잠금 문제가 발생할 수 있다.
또, 다른 필드에 대한 인덱스를 만들게 되면 성능이 별로 향상되지 않을 수 있다. 예를 들어, 테이블에 회사 이름 필드와 성 필드가 이미 인덱스된 경우에 우편번호 필드를 추가로 인덱스에 포함해도 성능이 거의 향상되지 않는다. 만드는 쿼리의 종류와 관계없이 가장 고유한 값을 갖는 필드만 인덱스 해야 한다.
레코드 잠금
데이터에 대한 동시 액세스를 방지하는 기술로, 일관성 없는 결과를 방지한다.
같은 레코드를 수정하는 트랜잭션 2개가 동시에 시작되었을 때, 나중에 끝난 트랜잭션이 먼저 끝난 트랜잭션의 결과를 덮어씌워, 먼저 끝난 트랜잭션을 무효화시킨다. 이러한 결과를 방지하기 위해 하나의 트랜잭션이 끝날 때까지 레코드를 잠궈 다른 트랜잭션의 접근을 막는다.
상황 분석
Index를 사용하면 좋은 경우
- Where 절에서 자주 사용되는 컬럼
- 외래키가 사용되는 컬럼
- Join에 자주 사용되는 컬럼
Index 사용을 피해야 하는 경우
- Data 중복도가 높은 컬럼
- DML이 자주 일어나는 컬럼
DML에 취약
Insert
- 기존 Block에 여유가 없을 때, 새로운 Data가 입력된다.
- 새로운 Block을 할당받은 후, Key를 옮기는 작업을 수행한다.
- Index split 작업 동안 해당 Block의 Key값에 대해 DML이 블로킹한다. (대기 이벤트 발생)
Index split
인덱스의 Block들이 하나에서 두개로 나눠지는 현상
Delete
- Table에서 Data가 delete 되는 경우: Data가 지워지고 다른 Data가 그 공간을 사용할 수 있다.
- Index에서 Data가 delete 되는 경우: Data가 지워지지 않고
사용 안 됨표시만 해둔다.
즉, Table의 Data 수와 Index의 Data 수가 다를 수 있다. - Index를 사용해도 수행 소도를 기대하기 힘들다.
Update
- Index에는 Update 개념이 없다.
- Table에서 update가 발생하면, Index에서는 Delete가 먼저 발생 후 새로운 Insert가 발생되어 다른 DML보다 더 큰 부하를 주게 된다.
참고
