[MySQL] WHERE 절에서의 인덱스 사용 / AND 연산자로 연결된 경우와 OR 연산자로 연결된 경우

인덱스 칼럼 순서와 where 조건절 칼럼 순서가 중요한 이유

다음 구조를 가진 테이블을 살펴보자.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  city VARCHAR(50)
);

 

두가지의 쿼리를 살펴보자.

쿼리 1

SELECT * FROM employees WHERE age > 30 AND city = 'New York';

쿼리 2

SELECT * FROM employees WHERE city = 'New York' AND age > 30;

 

쿼리1과 쿼리2는 모두 동일한 조건을 가지지만, WHERE절에서 명시한 컬럼의 순서가 다르다.

 

그렇다면, 두 개의 인덱스를 만들어서 인덱스 칼럼 순서가 쿼리 성능에 어떤 영향을 미치는지 확인해보자.

index 1 > CREATE INDEX idx_age_city ON employees (age, city);

index 2 > CREATE INDEX idx_city_age ON employees (city, age);

 

index 1 사용(idx_age_city)

• 쿼리 1(age > 30 AND city = 'New York')은 age 및 city 칼럼이 모두 쿼리 조건과 동일한 순서로 인덱스에 포함되어 있으므로 인덱스를 완전히 활용할 수 있다. 이로 인해 쿼리 성능이 개선될 수 있다.
• 쿼리 2(city = '뉴욕' AND age > 30)는 city 조건에 대해서 인덱스를 부분적으로 사용할 수 있지만, age에 대해서 추가 필터링 작업을 할 수 있으므로, 쿼리 1에 비해 성능이 저하될 수 있다.

 

index 2 사용(idx_city_age)

• 쿼리 1(age > 30 AND city = 'New York')은 인덱스에서 age 조건이 city 조건 앞에 나오고 인덱스가 이 조건 순서에 대해 최적화되지 않았기 때문에 인덱스를 활용할 수 없다.
• 쿼리 2(city = '뉴욕' AND age > 30)는 city 조건이 인덱스에서 첫 번째로 오고 쿼리의 조건 순서와 일치하는 age 조건이 뒤따르므로 인덱스를 활용할 수 있다.

 

 

WHERE 조건절에서 OR 연산자가 있다면 주의하자

아래 쿼리를 살펴보자.

SELECT * 
	FROM employees
    WHERE first_name = 'Kebin' OR last_name="Poly';

 

위의 쿼리에서 first_name = 'Kebin' 조건은 인덱스를 이용할 수 있지만, last_name = 'Poly'는 인덱스를 사용할 수 없다. 이 두 조건이 AND 연산자로 연결됐다면 first_name의 인덱스를 이용하겠지만 OR 연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수밖에 없다. 

따라서, (풀 테이블 스캔 + 인덱스 레인지 스캔)의 작업량 보다는 (풀 테이블 스캔) 한 번이 더 빠르기 때문이다. 이 경우 first_name과 last_name 컬럼에 각각 인덱스가 있다면 index_merge 접근 방법으로 실행할 수 있으나, 여전히 풀 테이블 스캔보다는 빠르지만 제대로 된 인덱스 하나를 레인지 스캔하는 것보다는 느리다.

따라서, WHERE 절에서 각 조건이 AND로 연결되면 읽어와야 할 레코드의 건수를 주이는 역할을 하지만, 각 조건이 OR로 연결되면 읽어서 비교해야 할 레코드가 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의하자.