[ MySQL 최적화 ] 인덱스를 걸었는데도 인덱스가 작동하지 않는 경우

분명 인덱스를 걸었음에도 풀 테이블 스캔으로 데이터를 조회하는 경우가 있다.

경우 1 : 넓은 범위의 데이터를 조회하는 경우

 

옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 왜냐면, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는게 효율적이라고 판단하기 때문이다.

 

(예시)

SELECT * FROM users 
ORDER BY name DESC;

  ▶ 이런 경우 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단

넓은 범위의 데이터를 조회하는 경우, 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔을 이용하는 것이 효과적.

 

* 최적화를 위하여 넓은 범위의 데이터를 조회하는지 잘 파악이 안된다면, 인덱스를 적용한 후에 실행 계획 조회해 보자.

그림에 보이듯이 인덱스를 했음에도 불구하고 trype이 ALL 인 것을 볼 수 있다. 

  → 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적

CREATE INDEX idx_name ON users (name);

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

 

 

경우 2 : 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자열 조작 등 )을 한 경우

 

id, name, salary, created_at 콜롬들을 가진 users 테이블에 100만 건의 랜덤 데이터 삽입

 

인덱스 생성

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_salary ON users (salary);

 

실행 계획 조회해보기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

 

첨부된 결과 사진에 있는 타입에 보이듯이 ALL 이다. 즉, 풀테이블 스캔을 한 것을 볼 수 있다.

 

인덱스 컬럼을 가공해서 사용하지 않게 SQL문 수정하기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%'; # % 와일드 카드: 0개 이상의 어떤 문자든지 매칭될 수 있음

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

위 두 결과 그림에서 type 을 보면 인덱스가 제대로 활용 된 것을 볼 수 있다.

 

인덱스를 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.