인덱스 사용하기: WHERE, GROUP BY, ORDER BY

☁️ 인덱스 사용의 기본적인 유의사항

기본적으로 인덱스를 사용하려면 인덱스된 컬럼의 값(인덱스 키값) 자체를 변환하지 않고 사용해야 한다. 해당 조건은 타입이 동일해야 한다는 조건을 포함한다. 자동 타입 변경으로 인해 인덱스를 사용하지 못하는 경우도 유의해야 한다. 예를 들어 아래와 같은 쿼리는 인덱스를 사용하지 못한다.

-- employees.salary가 단일 컬럼 인덱스로 선언되었다고 가정
SELECT * FROM employees WHERE salary*10=12000;

-- 아래 처럼 변경하면 인덱스를 사용할 수 있다.
SELECT * FROM employees WHERE salary=12000/10;

만약 특정 컬럼을 변환한 값에 의한 검색이 빈번하게 발생하는 경우 가상 컬럼을 통해 인덱스를 생성하고 사용하는 것이 효율적이다.

 

☁️ WHERE

WHERE절에서 인덱스를 사용하는 방법은 크게 범위 결정 조건, 체크 조건으로 나뉜다.

• 범위 결정 조건: 탐색하는 레코드의 범위를 좁힐 수 있는 조건이다. 성능과 직결된다.
• 체크 조건: 범위가 결정된 이후 쿼리와 일치하는 레코드를 체크하기 위한 조건이다.

두 조건은 인덱스에 존재하는 키의 선언 순서에 따라 결정된다.

아래의 사진과 같이 순서대로 a, b, c 컬럼이 인덱스의 키로 선언되어 있는 상황에 쿼리의 where 조건이 b, c, a 순서대로 작성되었다고 가정해보자. (where절의 조건은 모두 AND)

 

우선 where 절의 조건은 옵티마이저에 의해 사용하고자 하는 인덱스 컬럼들의 오름차순인 a = ?, b < ?, c = ? 순서로 변경된다. (MySQL 8.0 부터)

• a=?: 인덱스 키 a를 사용하여 검색하고자 하는 레코드의 범위를 좁힐 수 있다.
• b<?: 인덱스 키 b를 사용하여 검색하고자 하는 레코드의 범위를 좁힐 수 있다.
• c=?: b를 사용한 범위 검색에 대해 c=? 인지 체크하는 체크 조건이 된다.

 

이중 하나의 조건이 OR로 변경된다면 어떨까?

 

a = ? AND b < ? OR C = ?

a, b 컬럼은 인덱스를 통한 탐색이 가능하지만 c 컬럼은 인덱스를 사용할 수 없기 때문에 테이블 풀 스캔을 수행하여야 한다. 즉 a, b 컬럼의 레인지 스캔+ c 컬럼의 풀 스캔 작업을 수행해야 한다는 것이다. 이는 레인지 스캔 이후 풀 스캔을 할 필요가 없기에 옵티마이저에 의해 처음부터 풀 스캔을 수행하게 된다.

 

 

☁️ GROUP BY

GROUP BY 절에서 인덱스를 사용하기 위해서는 인덱스 키의 순서와 GROUB BY 절에 선언된 컬럼 순서가 동일해야 한다. 조금만 생각해보면 당연한 말이다. 다중 컬럼 인덱스는 먼저 선언되어 있는 키 부터 나중에 선언되어있는 컬럼 순서대로 정렬의 기준이 된다. 따라서 그룹핑을 위해서 인덱스를 활용하려면 이를 따라야 한다.

 

예외적인 상황이 있다. where 절에서 인덱스 키 컬럼이 범위 결정 조건으로 사용된 경우 GROUP BY절에서 빠져있어도 인덱스를 사용할 수 있다. 예를 들어 위의 사진과 같은 인덱스를 그대로 사용한다고 가정해보자.

1. SELECT * FROM test_tb GROUP BY a, b, c;
2. SELECT * FROM test_tb GROUP BY b, c; 
3. SELECT * FROM test_tb WHERE a=? GROUP BY b, c; 

1, 3 번 쿼리는 인덱스를 사용할 수 있다. 하지만 2번 쿼리는 인덱스를 사용할 수 없다.

 

 

☁️ ORDER BY

ORDER BY 절에서 인덱스를 사용하는 방식은 GROUP BY절과 거의 동일하다. 다만 정렬 방향을 인덱스의 정렬 방향과 동일하게 맞춰야 한다.

-- a ASC, B DESC, c ASC
1. SELECT * FROM test_tb ORDER BY a, b, c;
2. SELECT * FROM test_tb ORDER BY a, b DESC, c;

2번 쿼리는 인덱스를 사용할 수 있지만, 1번 쿼리는 사용할 수 없다.

WHERE절과 혼합하여 사용하는 예시를 보자. 이것도 조금만 생각해보면 당연하다.

1. SELECT * FROM tb_test WHERE a = 10 ORDER BY b, c;
2. SELECT * FROM tb_test WHERE a > 10 ORDER BY a, b, c; 
3. SELECT * FROM tb_test WHERE a > 10 ORDER BY b, c;

1, 2번 쿼리는 인덱스의 사용이 가능하고 3번 쿼리는 불가능하다. 3번 쿼리는 a에 의해 범위가 결정되고 a > 10인 a순으로 정렬된 레코드는 b와 c순으로 정렬되어 있지 않다. 만약 a = 10처럼 a의 범위를 동등비교로 고정시킨다면 가능하다.