[DB] INDEX

: 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

 

DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX index_name;

• INSERT: 새로운 데이터에 대한 인덱스를 추가함
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

 

장점

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

 

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
• 인덱스를 관리하기 위해 추가 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

 

만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면
 
인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과
가 발생할 수 있다.

인덱스(index)를 사용하면 좋은 경우 

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼

인덱스(index)를 안타는 경우

1. 인덱스 컬럼을 변형하여 비교할 때

BAD
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
GOOD
WHERE HIREDATE = TO_DATE('19980518')

 

BAD
WHERE SALARY + 1000 > 100000;
GOOD
WHERE SALARY > 100000 - 1000;

2. 비교 대상의 형이 달라서 내부적으로 형변환을 하는 경우

BAD
WHERE EMP_ID = 200383;
GOOD
WHERE EMP_ID = ‘200383’;

3. NULL을 비교하는 경우

BAD
WHERE JOB IS NULL;

4. 부정형 조건인 경우 ( NOT IN 연산자 사용 )

BAD
WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');

5. 인덱스  컬럼의 변형

BAD
select * from table  where LOWER(name)  ='word';
select * from table  where idx - 1 = 5;

6. 와일드 카드 LIKE문장에서 범위를 전체를 지정시

BAD
select * from table  where name like '%word'; 
문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다.

select * from table  where name like 'word%'; 
당연한 얘기지만 쿼리가 이런 경우 인덱스를 탄다. 문자열 정렬 순서를 그대로 이용할 수 있기 때문이다.

 

7. 복합 컬럼 index에서 조건이 잘못되여 index 가 적용 되지 못하는경우

BAD
select * from table where name = 'word' or idx = 5

name과 idx가 둘다 인덱스가 걸려있는 경우라해도, DBMS가 최적의 OR 조건을 뽑기 힘들어, FULL SCAN 하는 경우가 많다.

 

1. 다중 컬럼 인덱스 추가 (name, idx)

2. UNION QUERY사용

SELECT * FROM table WHERE name = 'word' UNION SELECT * FROM table WHERE idx = 5;

 

 

 

 

---

https://mangkyu.tistory.com/96

https://lannstark.tistory.com/40