[DB Optimization#12] 인덱스가 안 먹히나요?

MySQL에서 인덱스를 걸어도 예상과 달리 EXPLAIN 결과에 Full Table Scan이 찍히는 경우가 있습니다.
“인덱스를 걸었는데 왜 작동하지 않지?”라는 의문이 드는 순간, 우리는 쿼리 작성 방식을 돌아봐야 합니다.

 

이번 글에서는 실제 인덱스를 무력화되는 대표적인 상황을 실습을 통해 알아보고, 어떻게 쿼리를 고쳐야 인덱스를 제대로 활용할 수 있는지 소개합니다.

 

 

Case 1

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),
    FLOOR(1 + RAND() * 1000)
FROM cte;

CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT * 
FROM users 
ORDER BY name DESC;

→ type = ALL, 즉 풀 테이블 스캔이 발생함

 

🔍 왜 인덱스를 사용하지 않았을까?

MySQL 옵티마이저는 정렬 기준인 name에 인덱스가 있더라도, 전체 100만 건을 모두 정렬해야 하는 상황에서는 굳이 인덱스를 통해 테이블을 다시 조회하는 것보다 그냥 전체 테이블을 스캔해서 정렬하는 게 더 빠르다고 본 것입니다.

 

즉, 인덱스를 쓰는 게 항상 빠른 건 아니며, 경우에 따라서는 풀 테이블 스캔이 더 효율적일 수 있습니다.

 

 

 

Case 2

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 100만 건의 데이터 삽입
INSERT INTO users (name, salary, created_at)
WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),
    FLOOR(1 + RAND() * 1000000),
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND)
FROM cte;

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_salary ON users(salary);

EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

EXPLAIN SELECT * FROM users
WHERE salary * 2 < 1000;

 

🔍 왜 인덱스를 사용하지 않았을까?

MySQL은 인덱스가 걸린 컬럼에 함수나 연산이 적용되면, 해당 컬럼을 더 이상 “인덱스로 활용할 수 있는 상태”로 보지 않습니다.

• SUBSTRING(name, ...) → 문자열 가공
• salary * 2 → 산술 연산

→ 이러한 가공은 인덱스를 무력화합니다.

 

해결 방법

아래와 같이 인덱스 컬럼은 가공하지 않고 조건을 거는 방식으로 작성해야 합니다.

-- name 컬럼 앞 부분을 그대로 비교 (LIKE 사용)
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%';

-- salary 연산을 조건 값으로 이전
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2;

→ 두 쿼리 모두 type이 range로 변경됨을 확인할 수 있습니다.