인덱스 미활용 안티 쿼리 패턴

데이터베이스에서 인덱스를 생성하면 WHERE 조건 절에 인덱스가 생성된 컬럼을 사용하여 인덱스 탐색이 가능하다. 하지만 인덱스가 있어도 쿼리를 잘못 작성하면 인덱스를 전혀 사용하지 못하고, 결국 모든 데이터를 처음부터 끝까지 스캔하는 전체 테이블 스캔(Full Table Scan)이 발생해 성능이 저하된다. 이러한 경우를 인덱스 안티패턴이라고 하며 어떤 경우에 인덱스를 활용할 수 없는지 살펴 보자.

1. 함수로 감싼 컬럼

인덱스는 컬럼 원본 값을 기반으로 생성되는데, 함수로 감싸게 되면 함수를 적용한 결과값을 모두 계산한 후에 비교해야 하기 때문에 인덱스 탐색이 불가능하게 된다.

SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM orders WHERE DATE(created_at) = '2023-01-01';
SELECT * FROM products WHERE LENGTH(product_name) > 10;

 

2. 연산이 수행된 컬럼

연산이 수행된 결과값은 인덱스에 없는 값이기 때문에 인덱스 탐색이 불가능하다. 인덱스는 컬럼 원본 값을 기반으로 생성된다.

SELECT * FROM products WHERE price * 1.1 > 1000;
SELECT * FROM users WHERE age + 5 > 30;
SELECT * FROM orders WHERE total_amount - discount > 500;

 

3. LIKE 연산 와일드카드(%) 접두어 사용

LIKE 연산 수행 시 와일드카드(%)를 접두어에 사용하게 되면 모든 행들이 대상이 되기 때문에 인덱스 탐색이 불가능하게 된다. 

SELECT * FROM users WHERE name LIKE '%john%';
SELECT * FROM products WHERE product_code LIKE '%ABC';

 

4. 복합 인덱스 시 후행 컬럼만 사용

복합 인덱스는 인덱스 생성시 선언했던 컬럼 순서대로 인덱스가 생성된다. 따라서 모든 컬럼을 조건에 넣지 않아도 되지만, 선행했던 컬럼 순서대로 조건에 넣어야 인덱스 활용이 가능하다.

CREATE INDEX idx_user_info ON users (age, gender, city);

-- 인덱스 탐색 불가능 (후행 컬럼만 사용)
SELECT * FROM users WHERE gender = 'M';
SELECT * FROM users WHERE city = 'Seoul';
SELECT * FROM users WHERE gender = 'M' AND city = 'Seoul';

 

5. 데이터 타입 불일치

데이터베이스는 비교하려는 컬럼의 데이터 타입이 불일치한 경우 내부적으로 형변환을 진행한다. 이 과정에서 인덱스 컬럼에 함수가 적용된 것과 동일한 효과가 발생하여 인덱스 탐색이 불가능해진다.

-- user_id가 INT 타입인데 문자열로 비교
SELECT * FROM users WHERE user_id = '123';

-- phone이 VARCHAR 타입인데 숫자로 비교
SELECT * FROM users WHERE phone = 01012345678;

-- created_at이 DATETIME 타입인데 문자열로 비교
SELECT * FROM orders WHERE created_at = '2023-01-01';