[데이터베이스] 인덱스(Index)의 정의와 사용하면 안되는 경우에 대하여

[데이터베이스] 인덱스(Index)의 정의와 사용하면 안되는 경우에 대하여

웹어플리케이션을 개발하는 경우에 개발자가 SQL쿼리를 작성할때, 인덱스를 생각안하고 짜는 경우가 많이있다. 테이블에 인덱스를 지정해놓고도, 인덱스가적용이안되거나 더 효율이 떨어지는 경우가있는데 그런경우를 체크해보자!

**인덱스란!?

전체 내용중에서 특정한 부분을 바로찾을수 있도록 목차(색인)을 만드는 개념.

데이터가 정렬되어있는 주소값을 저장하는 오브젝트입니다.

**인덱스의 특징

 - 일반적인 테이블은 정렬이 되어있지 않지만, 인덱스는 정렬이 되어있다.

 - 전체 데이터에서 적은양의 데이터를 검색할때 index를 사용한다

   (전체테이블의 15%이내여야 인덱스를 사용할때 유리하다. 많은양의 데이터를 검색하게된다면 index를 경유하고 row정보를 이용해 실제 데이터와 매칭이 되어야하기 때문에 더  많은 부하와 시간이 걸릴수있다. 차라리 전체 데이터를 스캔하는방법이 더나은방법)
 - 성능향상 조건 : I/O와 지연시간 낮추기.

 - B트리로 구성되어있다.

이제까지는 인덱스의 기초지식이였고..

본격적으로 인텍스를 사용하지 못하는 경우는

첫번째, LIKE 검색시 좌번에 %를 붙일 경우

WHERE 조건에서 LIKE '%하하' : 인덱스 사용 불가

WHERE 조건에서 LIKE '하하%' : 인덱스 사용 가능

좌변에 LIKE %를 붙일경우 인덱스를 참조 하지 못하게 됩니다. 왜냐하면 인덱스는 컬럼의 값과 주소를 정렬하여 저장하는 색인인데, 맨처음값이 무엇인지 모른다면 인덱스를 참조하는 것은 불가능하다. 가령, 사전에서 처음글자를 알지못하고 사전을 찾는 것과 같다. 이럴경우 처음부터 Full scan해야한다.  

​

두번째, IS NULL or NOT NULL을 사용 할 경우

IS NULL은 NULL은 Value에 들어가지 않는다.

IS NOT NULL의 경우,, 인덱스는 NULL이 없으므로 Full scan을 수행한다. 

세번째, 인덱스로 지정된 칼럼이 가공되거나 변형된 경우

SUBSTR('하하하',2,3)

COL * 2

위와같이 SUBSTR을 사용할 경우 문자열의 중간부분을 떼어내기때문에 LIKE문과 동일한 경우이다.

두번째 경우에는 인덱스로 지정된 칼럼의 값이 변형되었기 때문에 인덱스를 사용하지 못한다.

​ 

네번째, 부정연산자를 사용하는경우(!=,<>)

인덱스로 자료를 찾는 경우에 결과의 값이 적을 때 빠르게 찾을 수 있다. 하지만 부정연산자를사용하면 넓은 범위의 많은 결과값이 나오기 때문에 Full scan하게 된다.

다섯번째, 암시적 형변환

암시적 형변환이 일어날 경우에는 인덱스 컬럼이 가공이되는지, 아니면 비교되는 데이터가 변경이되는지 에 따라서 인덱스를 사용할 수도 안할수도있다.