CS 스터디 (DB) 20 - 인덱스를 타지 않거나 혹은 기대하는 성능을 못내는 경우는?

1. "같지 않음" 조건에서는 인덱스가 성능을 보장하지 않습니다. 

- 인덱스는 특정 값이나 값의 범위를 빠르게 조회하기 위해 설계되어 있습니다. 

- 다만, <>, NOT IN, NOT BETWEEN, IS NOT NULL 같은 부정 조건에서는 인덱스가 효과적이지 않을 수 있습니다.

- 특히 IS NOT NULL의 경우, 테이블의 90% 정도가 NULL 이 아닌 값이면 FULL TABLE SCAN과 비슷한 성능이 나올 수 있음

 

 

2. 와일드카드(%)로 시작하거나 상수가 아닌 LIKE 검색 조건은 인덱스를 사용할 수 없습니다.

- LIKE절을 사용할 때, 검색 패턴이 와일드카드 %로 시작하거나 비교 대상이 상수가 아닌 경우 인덱스를 사용할 수 없습니다.

- 예를 들어 LIKE '%abc' 또는 LIKE other_col과 같은 조건은 문제열의 전체를 확인해야 하므로, FULL TABLE SCAN을 선택할 가능성이 높습니다. 

- 반면 LIKE 'abc%'처럼 접두어가 명확한 경우 해당 문자열 범위를 통해 인덱스를 효과적으로 활용할 수 있습니다. 

올바른 쿼리(인덱스 사용 가능)

-- 접두어가 상수로 주어진 경우 (인덱스 사용 가능)
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

-- 정확한 문자열 비교 (인덱스 사용 가능)
SELECT * FROM tbl_name WHERE key_col = 'Patrick';

-- 접두어 검색으로 범위 조건이 명확할 경우 (인덱스 사용 가능)
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%';

 

옳지 않은 쿼리(인덱스 사용 불가능)

-- 와일드카드로 시작 (인덱스 사용 불가)
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

-- 상수가 아닌 다른 컬럼과 비교 (인덱스 사용 불가)
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

-- 와일드카드가 접두어에 위치 (인덱스 사용 불가)
SELECT * FROM tbl_name WHERE key_col LIKE '_atrick%';

 

3. 인덱스 컬럼에 가공 로직이 포함되면 인덱스를 사용할 수 없습니다. 

- 쿼리 내에서 인덱스가 설정된 컬럼에 함수나 연산을 적용하면, 해당 인덱스를 사용할 수 없습니다. 

- 아래의 SUBSTR(job,1,4)처럼 함수가 사용된 경우 RDBMS는 인덱스를 사용할 수 없고 각 행의 값을 직접 평가해야 합니다. 

- 반면, WHERE job LIKE 'SALE'와 같이 컬럼 값을 그대로 조건에 사용하는 방식은 인덱스를 사용할 수 있어 효과적입니다. 

-- 인덱스 컬럼에 변형이 일어났기 때문에 인덱스 사용이 불가
SELECT dept, ename, sal FROM emp WHERE SUBSTR(job, 1, 4) = 'SALE'

-- 인덱스 사용 가능
SELECT dept, ename, sal FROM emp WHERE job like 'SALE%'

 

 

4. 복합 인덱스에서는 첫 번째 컬럼이 조건에 포함되지 않으면 인덱스를 활용할 수 없습니다. 

- 복합 인덱스는 정의된 컬럼 순서대로 조건이 주어져야만 인덱스를 사용할 수 있습니다. 

- 또,  인덱스 순서를 따르지 않거나 첫 컬럼이 빠지면 인덱스가 무효화되고, 옵티마이저는 테이블 스캔을 선택할 수 있습니다.

CREATE INDEX idx_a_b ON my_table (a_column, b_column);

-- 첫 번째 컬럼 a_column이 조건절에 포함됨 → 인덱스 사용 가능
SELECT * FROM my_table WHERE a_column = 10 AND b_column = 'X';

-- 첫 번째 컬럼이 빠짐 → 인덱스 사용 불가
SELECT * FROM my_table WHERE b_column = 'X';

 

 

5. 인덱스에 포함된 컬럼의 크기가 크면 인덱스 스캔 자체의 성능이 저하될 수 있습니다. 

- 인덱스는 B-Tree 구조로 구성되며, 각 노드에는 컬럼 값과 포인터가 저장됩니다.

- 인덱스에 크기가 크거나 많은 컬럼을 인덱스에 포함하면, 노드당 엔트리 수가 줄고 트리 깊이가 증가해 디스크 I/O가 늘어납니다.

- 이로 인해 인덱스 탐색이 비효율적이 될 수 있으며, 경우에 따라 테이블 스캔보다 느릴 수 있습니다.

- 특히 TEXT, BLOB, 가변 길이 문자열 등의 대용량 컬럼은 인덱스에 포함하지 않는 것이 성능상 좋습니다.