[DATABASE] DB 인덱스(INDEX)

인덱스에 대해 그 동안 잘못 이해했던것은 책갈피 같은 개념으로 생각했다.

따라서 내가 필요한 것들만 책갈피를 해두면 쉽고 빠르게 찾을 수 있는 개념으로 생각했으나,

이 부분은 확연히 잘못됐다.

 

차라리 책 뒷부분에 있는 색인에있는 키워드와 책페이지번호가 인덱스의 개념에 가깝다.

 

그렇다면 인덱스에 대해 다시 파헤쳐 보자.

 

인덱스의 장점

 

- 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킵니다.
- 질의나 보고서에서 그룹화 작업의 속도를 향상시킵니다.
- 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있습니다.
- 테이블의 기본 키는 자동으로 인덱스 됩니다.
- 필드 중에는 데이터 형식 때문에 인덱스 될 수 없는 필드도 있습니다.
- 여러 필드로 이루어진(다중 필드) 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있습니다.
  참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있습니다.

 

인덱스의 단점

 

- 인덱스를 만들면 .mdb 파일 크기가 늘어난다.
- 여러 사용자 응용 프로그램에서의 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
- 인덱스 된 필드에서 데이터를 업데이트하거나, 레코드를 추가 또는 삭제할 때 성능이 떨어집니다.

- 인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요해진다. (DB의 10퍼센트 내외의 공간이 추가로 필요)
- 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
- 데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.

 

INDEX의 목적

RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.
SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.
※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.

 

인덱스를 생성해야 하는 경우와 그렇지 않은 경우

- 인덱스는 열 단위로 생성된다.
- WHERE절에서 사용되는 컬럼을 인덱스로 만든다.
- 데이터의 중복도가 높은 열은 인덱스로 만들어도 효용이 없다. (예 : 성별, 타입이 별로 없는 경우, 적은경우)
- 외래키가 사용되는 열에는 인덱스를 되도록 생성해주는 것이 좋다.
- JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
- INSERT / UPDATE / DELETE가 얼마나 자주 일어나는지를 고려한다.
- 사용하지 않는 인덱스는 제거하자

Index 사용 전 명시 사항

• where절에서 자주 사용하는 컬럼에 사용한다.
• like '%~'는 조심해야 한다. %는 뒤에만 사용하도록 해야한다.
  (table scan이여서 성능 감소)
• between A and B (Clustered Index가 유리)
  범위 쿼리문에서는 클러스터 인덱스가 유리하지만 클러스터 인덱스는 테이블 당 1개만 가질 수 있다는 단점 존재
• order by에 항상 또는 자주 사용되는 컬럼에 사용한다.
• join으로 자주 사용되는 컬럼에 사용한다.
• Foreign key (1:1 매핑)이 많을 때 -> Clustered, NonClustered Index 둘 다 상관 없다.
  상황에 따라 Clustered Index사용
• Foreign key (1:N 매핑)이 많을 때 -> Clustered Index 사용한다.
• 100만건의 데이터 중 10건의 데이터 조회 -> 찾는 건이 적은 컬럼에 Index를 사용한다.
  상책중복이 많은 컬럼 (EX:성별)에는 Index를 거는 것이 아니다.
  조회되는 건 수가 많으면 인덱스를 걸지 않고 Table Scan이 더 나은편이다.
• not 연산자는 긍정문으로 변경
• Insert, Delete 등 데이터의 변경(DML)이 많은 컬럼은 인덱스를 걸지 않은 편이 좋다.
• 인덱스를 만드는데 시간과 저장공간이 소비되고 만들고 난 후에도 추가적인 공간이 필요하다..
• 데이터를 변경(Insert, Update, Delete)를 하면 인덱스를 다시 조정해야하기 때문에 자원이 많이 소모된다.
  특히나 Insert 연산

Index 사용 예제

CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......);
EX> CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 
// UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻
EX> CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

ALTER TABLE  테이블명 ADD INDEX(필드명(크기));

CREATE TABLE 테이블 명 ( 필드명 데이터타입(데이터크기), INDEX(필드명(크기)) ENGINE MyISAM; 

필드 중에는 데이터 형식 때문에 인덱스가 될 수 없는 필드도 있다.

 

여러 필드로 이루어진(다중 필드) 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.

 

참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.

 

• 인덱스 삭제

DROP INDEX [인덱스 명]

 

• 전체 확인

SHOW INDEX FROM 테이블이름

 

Index Rebuild

인덱스를 리빌드하는 이유

 

인덱스 파일은 생성 후 Insert, Update, Delete등을 반복하다보면 성능이 저하된다.
생성된 인덱스는 트리구조를 가지는데, 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어지기 때문이다.
이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋다.

 

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

 

• Index 트리의 깊이가 4이상인 Index를 조회하는 쿼리

 

해당 쿼리문을 실행하여 검색되는 Index는 리빌딩을 하는것이 좋다.

 

• 인덱스 리빌드

ALTER INDEX [인덱스명] REBUILD;

 

• 전체 인덱스 리빌드 쿼리문 만들기

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

 

 

참조사이트 : lalwr.blogspot.com/2016/02/db-index.html

참조사이트 : velog.io/@gillog/SQL-Index%EC%9D%B8%EB%8D%B1%EC%8A%A4