SQL : INDEX 정리 및 인덱스를 타지 않는 경우

---

INDEX ? 
테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용

보통 INDEX를 테이블의 특정 컬럼에 한개 이상을 주면 Index Table이 따로 만들어지는데 이 Index Table에는 인덱스 컬럼의 로우값과 rowid 값이 저장되게 되며 로우의 값은 정렬된 B-TREE구조로 저장시켜 두어 더 빠르게 해당 데이터를 찾는데 도움을 줌
하지만 UPDATE, INSERT, DELETE시에 속도가 느려진다는 단점이 있는데, 이 세가지는 원본테이블은 물론 INDEX TABLE에도 데이터를 갱신시켜 줘야하기 때문


사용방법  

CREATE   INDEX   인덱스이름  ON  테이블이름 (필드이름1, 필드이름2, ...)
ex) create index idx_account on account(account_id);
-> account Table에 idx_account 라는 index를 만들어줌으로써 account_id 조회 시 성능이 좋아짐

---

인덱스를 타지 않는 경우

 

1. 인덱스  컬럼의 변형
select * from table  where LOWER(name)  ='word';
select * from table  where idx - 1 = 5;
이 처럼 인덱스에 변형을 가하게 되면, DBMS가 인덱스를 이용하지 않는다.

2. NOT 또는 IN 연산자 사용
NOT일 경우 무조건 인덱스를 안타는 것이 아니다.
NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다.
마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.

3. 와일드 카드 LIKE문장에서 범위를 전체를 지정시
select * from table  where name like '%word'; 
문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다.

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

4. 복합 컬럼 index에서 조건이 잘못되여 index 가 적용 되지 못하는경우
select * from table where name = 'word' or idx = 5

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

5. Optimizer 의 선택
select * from table  where name ='word' and  id ='elky'; 

인덱스가 name 과   id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될수도 있고, 둘다 선택 될 수도있다.
어떤 방식으로 선택하는냐가 속도에 중요할수도있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 관리가 필요하다

출처: http://elky.tistory.com/210 [엘키의 주절 주절]