무럭무럭 STUDY - Index를 타지 않고 Table Full-Scan 처리? (MySQL)

MySQL에서는 인덱스가 있음에도 불구하고 인덱스를 사용하지 못하고 Table Full-Scan으로 처리되는 경우가 있다. 어떤 경우가 있는지 살펴보자.
 

쿼리에서 컬럼이 가공되는 경우

쿼리에서 컬럼이 가공되는 경우에는 해당 컬럼에 인덱스가 존재해도 인덱스를 사용하지 못하게 된다. 이는 인덱스가 컬럼의 원본 값을 기반으로 구성되어 있는데 수행된 연산 결과가 인덱스 데이터로 인덱싱되어 있지 않기 때문이다.

-- 컬럼의 산술 연산
select * 
  from exam_tab 
 where id + 10 < 100
 
-- 함수의 인자로 직접 사용
select *
  from exam_tab
 where MOD(id, 2) = 0

-- 자동 형변환
select * 
  from exam_tab
 where exam_string_col = 100

Tip)
컬럼이 가공되는 경우는 컬럼이 어떤 연산의 대상으로 포함되는 것을 말한다. 예를 들어 컬럼의 산술 연산, 함수의 인자로 직접 사용되는 경우, 컬럼의 자료형이 형변환. 즉 타입 컨버전이 발생하는 경우가 있다. 이때 형변환의 경우에는 숫자 타입의 ID 컬럼에 조건 값으로 문자열 타입의 숫자 값을 주면 인덱스를 정상적으로 탄다. mysql 내부적으로 형변환 우선순위가 있기 때문에 숫자를 문자로 변경하는 것보다 문자를 숫자로 변경하는 것이 더 우선순위가 높기 때문에 ID 컬럼 값을 형변환하지 않고 조건으로 주어진 문자열 값을 숫자로 형변환하기 때문이다.

 
실제로 table full-scan 일어나는 경우에 대해 실행계획을 보면 하기와 같다.

-- index를 정상적으로 타는 경우
explain
 select
   from exam_tab
  where id = 1

-- table full scan이 일어나는 경우
explain
 select
   from exam_tab
  where id * 1 = 1;

id	table	type	key	key_len	rows
1	exam_tab	const	PRIMARY	1	1

id	table	type	key	key_len	rows
1	exam_tab	ALL	NULL	NULL	100

 

인덱싱 되지 않은 컬럼을 조건절에 OR 연산과 사용하는 경우

인덱싱된 컬럼과 인덱싱되지 않은 컬럼을 or 연산에 각각 사용하는 경우 table full-scan을 하게 된다. or 연산의 경우 인덱싱되지 않은 컬럼이 조건에 해당되는지 확인하기 위해 테이블을 스캔할 수밖에 없기 때문이다. 즉 or 연산에서는 어차피 인덱싱되지 않은 컬럼을 위해 전체 데이터를 읽어야 하므로 쿼리 자체는 table full-scan으로 동작하게 되는 것이다. 물론 and 연산이라면 인덱싱되지 않은 컬럼이 함께 사용되어도 인덱싱된 컬럼에서 1차로 필터링을 수행하기 때문에 문제는 없다. 

-- index를 타지 못하는 경우
select *
  from exam_tab
 where idx_col = 1 or no_idx_col = 2

 

복합 인덱스의 컬럼들 중 선행 컬럼을 조건에서 누락한 경우

복합 인덱스로 구성되어 있는 컬럼들 중 선행 컬럼을 제외하고 나머지 컬럼들로만 쿼리의 조건으로 주어지는 경우 쿼리는 인덱스를 사용하지 못하게 된다. 인덱스는 인덱스를 구성하는 컬럼 순서대로 정렬된 인덱스 데이터가 만들어지기 때문이다. 즉 인덱스의 선행 컬럼이 조건으로 주어지지 않으면 쿼리에선 해당 인덱스를 사용할 수 없게 된다. 결론적으로 인덱스를 구성하는 컬럼들의 순서가 쿼리에서 인덱스 사용 여부를 결정하는 중요한 부분인 것을 알아야 한다. 물론 인덱스의 선행 컬럼 없이 후행 컬럼으로만 쿼리에서 조건을 주지 않도록 주의하자.

CREATE TABLE exam (
    id INT,
    name VARCHAR(100),
    KEY idx_id_name (id, name)
);

-- index를 타지 못하는 경우
select *
  from exam_tab
 where name = 'hongdosan'

 

LIKE 연산에서 시작 문자열로 와일드 카드를 사용하는 경우

like 연산에서 시작 문자열로 와일드 카드를 사용하면 조회 대상이 되는 값의 범위를 지정할 수 없고 전체 데이터를 스캔하면서 문자열 포함 여부를 확인해야 되기 때문에 인덱스를 사용하지 못하고 table full-scan을 하게 된다. 반면 와일드 카드가 아닌 prefix 문자열 값을 명시해주는 경우에는 인덱스를 활용해 쿼리를 처리할 수 있다.

-- index를 타지 못하는 경우
select *
  from exam_tab
 where name like '%dosan%'

 

REGEXP 연산을 사용하는 경우

regexp 연산은 표현식을 만족하는지 하나하나 확인을 해야하기 때문에 일반적으로 인덱스를 사용하지 못한다 주의하자.

-- index를 타지 못하는 경우들
select *
  from exam_tab
 where REGEXP '^dosan';
 
select *
  from exam_tab
 where REGEXP '^[ho]...';

 

테이블 풀스캔이 인덱스보다 효율적인 경우

쿼리가 실행될 때 MySQL 내부적으로 쿼리 최적화를 진행하는 단계에서 옵티마이저가 테이블 풀스캔이 인덱스보다 효율적인지 판단하게 된다. 예를 들어, 특정 테이블에 데이터가 총 100개가 있다고 가정하고 그 100개 중 97개의 데이터가 가지는 인덱스가 걸린 특정 컬럼의 값이 'hongdosan'이라고 했을 때, "인덱스가 걸린 특정 컬럼 = 'hongdosan'" 이란 조건을 걸면 옵티마이저가 테이블 풀스캔이 더 효율적이라고 생각하여 테이블 풀 스캔을 할 것이다. 이는 카디널리티를 생각하면 이해하기 쉬울 것이다.

Tip)
NOT Equal 조건과 IS NOT NULL 조건도 항상 인덱스를 사용하지 못하는 것이 아니다. 즉 옵티마이저가 인덱스가 더 효율적이라고 판단하면 인덱스를 사용하게 된다.

---

Reference

Real MySQL - 이성욱님, 백은빈님