[MySQL] WHERE 절 / 칼럼의 값을 변경했을 때의 인덱스 적용 / 비교 대상의 데이터 타입

칼럼을 가공한 후 다른 상숫값 비교

 

인덱스를 사용하기 위한 기본 규칙

WHERE 절이나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 칼럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장하기 때문이다. WHERE 절이나 ORDER BY 또는 GROUP BY에서도 원본값을 검색하거나 정렬할 때만 B-Tree에 정렬된 인덱스를 이용한다.

 

인덱스를 적절하게 이용하지 못하는 경우

아래 쿼리는 WHERE 절에서 칼럼을 가공한 후 다른 상숫값과 비교했을 때, 인덱스를 적절하게 이용하지 못하는 경우다.

SELECT * FROM salaries WHERE salary*10 > 150000;

 

위 쿼리를 아래와 간단히 변경해서 인덱스를 사용하도록 유도할 수 있다.

SELECT * FROM salaries WHERE salary > 150000/10;

 

정리하자면, 인덱스의 칼럼을 변형해서 비교하는 경우(아무리 간단한 연산이라도)에는 인덱스를 이용할 수 없게 된다.

 

그럼에도 불구하고

옵티마이저의 실제 성능은 특정 상황에 따라 달라질 수 있다는 점에 유의하는 것이 중요하며 최적의 쿼리 성능을 보장하기 위해 옵티마이저에서 생성한 실행 계획을 철저히 테스트하고 분석하는 것이 항상 권장됩니다.

 

 

칼럼을 가공한 후 다른 상숫값 비교

 

두 비교 대상 값은 데이터 타입이 일치해야 한다

아래 간단한 쿼리를 살펴보자.

> CREATE TABLE tb_test (age VARCHAR(10), INDEX ix_age (age));
> INSERT INTO tb_test VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7');

 

아래 SELECT 쿼리의 실행 계획을 한번 확인해보자.

> SELECT * FROM tb_test WHERE age = 2;

 

실행계획을 살펴보자

age라는 칼럼에 인덱스가 준비돼 있어서 실행 계획의 type 칼럼에 "ref"나 "range"가 표시되어야 할 것으로 기대되지만, 사실은 "index" 라고 표시된다. 이는 인덱스 풀 스캔을 의미한다.

 

인덱스가 있는데 왜 인덱스 풀 스캔?

age 칼럼의 데이터 타입(VARCHAR)과 비교되는 값 2(INTEGER)의 데이터 타입이 다르기 때문이다. 비교되는 두 값의 타입이 서로 다를때 MySQL 옵티마이저가 내부적으로 문자열 타입을 숫자 타입으로 변환한 후 비교 작업을 처리한다. 결국 문자열 타입인 age 칼럼이 숫자 타입으로 변환된 후 비교돼야 하므로 인덱스 레인지 스캔이 불가능한 것이다.

따라서, 아래와 같이 변경하면 인덱스 레인지 스캔을 사용하도록 유도할 수 있다.

SELECT * FROM tb_test WHERE age = '2';