DB 인덱스를 타지 못하는 경우

[인덱스(Index) 란]

인덱스란 지정한 컬럼들 기준으로 일종의 목차를 생성하는 것이라고 볼 수 있다.

또 인덱스란 데이터 검색 속도 향상을 위해 사용되는 기술이다.

 

lee 를 찾는다고 가정했을 때 , 아래와 같은 상황에서는 모든 자리를 찾아봐야한다.

또 사람이 많아지면 많아질수록 시간이 오래걸린다.

4-kim 5-park 6-lee 1-lim 2-koo 3-ju

하지만 아래와 같이 목록을 만들어 둔다면 원하는 데이터만 찾을수있기 때문에 빠르다.

1-lim 

2-koo 

3-ju

4-kim 

5-park 

6-lee 

 

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

1.테이블 행의 갯수가 많은 경우

2.인덱스를 적용한 컬럼이 where절에서 많이 사용되는 경우

3.검색 결과가 원본 테이블 데이터에 비해 적을 경우(2-4%)

4.해당 컬럼이 null을 포함하는 경우

 

인덱스 사용하면 좋지 않은 경우

1.테이블의 행의 갯수가 적음

2.검색결과가 원본테이블 데이터의 많은 비중

3.원본 테이블의 insert, delete update가 빈번(성능이 떨어진다. 인덱스를 재성생해야하기 때문)

*쓸데없는 인덱스는 성능 개선을 해친다.

---

[인덱스 SQL 문법]

 

[인덱스 생성 문법]

1) 단일 인덱스

CREATE INDEX 인덱스 명 ON table_name(컬럼1); 

2) 복합 인덱스

CREATE INDEX 인덱스 명 ON table_name( 컬럼1,컬럼2,컬럼3); 

 

[인덱스 조회 문법]

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUST';

 

[인덱스 삭제 문법]

DROP INDEX 인덱스명;

---

[인덱스 사용 시 주의점]

인덱스가 적용된 컬럼이 조건식에서 인덱스를 탈 수 있게 하기위해서 주의해야할 점이 있다.

해당 컬럼을 가공하지 않거나 연산을 하지 않은 상태에서 비교해야 인덱스를 탄다.

 

그 이유는 인덱스 컬럼에 변형이 일어나면 where 절에서 비교되기 전에 가공이 먼저 되기 떄문이다.

인덱스는 가공되기 전의 값으로 생성 되있기 때문에 인덱스를 사용할 수 없게 되는것이다.

---

CREATEDATE가 인덱스라고 가정해보자

 

SELECT * FROM CUST WHERE CREATEDATE+1=3;   인덱스 안탄다. (비교 전 인덱스에 가공이 일어났기 때문)

SELECT * FROM CUST WHERE CREATEDATE=3;         인덱스 탄다.

 

---

따라서 내가 사용하려고 했던 SELECT * FROM CUST WHERE CONCAT(DATE,TIME) 블라블라 쿼리는 CONCAT이라는 함수를 사용하여 인덱스를 가공했기에 인덱스를 타지 않는다

---

인덱스를 타지 않는 또 다른 예들을 더 보자면

 

- LIKE ‘%문자열%’로 검색 (문자열%(가능))

- 부정 연산자(!=,<>,NOT IN, NOT EXISTS)를 사용

- OR 구문을 사용

 

[인덱스를 타는지 안타는지 확인하는 방법은?]

1. DB 상용툴(ex: orange , sql developer dbeaver)에서 확인 하는 법 

sql 작성 후 실행 계획(Explain plan) 탭에서 확인가능하다

 

2.sql 을 통해 확인 하는 법

EXPLAIN plan FOR select * from where 조건~~;
SELECT * FROM TABLE(dbms_xplan.display);

 

---