Ch08-6 함수 기반 인덱스

한 줄 정의

함수 기반 인덱스는 칼럼 그 자체가 아니라 함수를 적용한 결과값 을 인덱싱하여, WHERE UPPER(name)='ABC' 같은 쿼리도 인덱스를 탈 수 있게 해주는 MySQL 8.0의 기능입니다.

쉽게 말하면

일반 인덱스는 “책의 원문 그대로”를 색인합니다. 함수 기반 인덱스는 “원문을 특정 규칙으로 변환한 결과 “를 색인합니다.

예를 들어 “이름의 앞 3글자”만 가지고 검색하는 쿼리가 자주 나온다면, 이름 전체가 아니라 앞 3글자만 별도로 색인 해두는 것입니다.

왜 필요한가?

WHERE 조건의 칼럼에 함수가 적용 되면 일반 인덱스는 무용지물 이 됩니다.

-- 인덱스: (name)
SELECT * FROM users WHERE UPPER(name) = 'KIM';
-- → 인덱스 사용 불가 (풀 스캔)

왜냐면 인덱스는 name 원본으로 정렬되어 있는데, 조건은 UPPER(name) 기준이라 정렬 순서가 일치하지 않기 때문입니다.

이 문제를 해결하는 방법은 두 가지입니다.

핵심 내용

방법 1: 가상 칼럼 + 인덱스

MySQL 5.7부터 지원되는 방식입니다.

ALTER TABLE users
ADD COLUMN name_upper VARCHAR(50) AS (UPPER(name)) VIRTUAL,
ADD INDEX ix_name_upper (name_upper);
 
-- 이제 이렇게 쿼리
SELECT * FROM users WHERE name_upper = 'KIM';

가상 칼럼(VIRTUAL) 은 저장 공간을 차지하지 않고, 필요할 때 계산됨
인덱스를 걸면 값이 인덱스에는 저장됨 (옵티마이저가 활용 가능)
단점: 쿼리를 다시 써야 함 (WHERE name_upper = 'KIM' 형태로)

방법 2: 함수 기반 인덱스 — MySQL 8.0+

쿼리를 수정하지 않고도 함수가 적용된 WHERE를 인덱스로 처리할 수 있습니다.

CREATE INDEX ix_name_upper ON users ((UPPER(name)));
 
-- 기존 쿼리 그대로 인덱스 사용 가능
SELECT * FROM users WHERE UPPER(name) = 'KIM';

문법 주의점

함수 표현식을 괄호로 한 번 더 감싸야 합니다.

-- X : 일반 인덱스로 해석됨
CREATE INDEX ix ON tab (UPPER(name));
 
-- O : 함수 기반 인덱스
CREATE INDEX ix ON tab ((UPPER(name)));

두 방법 비교

기준	가상 칼럼 + 인덱스	함수 기반 인덱스
MySQL 버전	5.7+	8.0+
칼럼 추가 필요	O (가상 칼럼)	X
쿼리 수정 필요	O (칼럼명 사용)	X
표현식 일치 조건	칼럼 정의와 동일	WHERE의 표현식과 정확히 일치해야 함
SELECT에서 활용	가능 (칼럼으로 조회)	불가 (내부적으로만 사용)

표현식 일치의 함정

함수 기반 인덱스는 WHERE의 표현식과 인덱스의 표현식이 정확히 일치 해야 사용됩니다.

CREATE INDEX ix ON users ((UPPER(name)));
 
-- O : 정확히 일치
WHERE UPPER(name) = 'KIM'
 
-- X : UCASE는 UPPER의 별칭이지만 옵티마이저는 다르게 취급할 수 있음
WHERE UCASE(name) = 'KIM'
 
-- X : 함수의 인자 타입이 다름
WHERE UPPER(CAST(name AS CHAR)) = 'KIM'

이 때문에 함수 기반 인덱스는 실제 사용할 쿼리 패턴 을 먼저 확정하고 만들어야 합니다.

유스케이스

대소문자 구분 없는 검색

CREATE INDEX ix_email_ci ON users ((LOWER(email)));
-- WHERE LOWER(email) = 'user@example.com'

날짜의 연/월 기준 조회

CREATE INDEX ix_year_month ON orders ((DATE_FORMAT(created_at, '%Y-%m')));
-- WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-04'

JSON 필드 추출값 인덱싱

CREATE INDEX ix_json_name ON docs ((CAST(data->>'$.name' AS CHAR(100))));
-- WHERE CAST(data->>'$.name' AS CHAR(100)) = 'foo'

내 생각

함수 기반 인덱스는 편리하지만, “쿼리를 함수 없이 쓰는 것”이 더 좋은 해결책 일 때가 많습니다. 예를 들어 대소문자 구분 없는 검색은 칼럼의 collation을 utf8mb4_general_ci 로 지정하면 함수 없이도 대소문자 무관하게 검색됩니다.
함수 기반 인덱스의 진가는 스키마를 바꾸기 어려운 기존 테이블 에서 발휘됩니다. 운영 중인 테이블에 가상 칼럼을 추가하려면 DDL이 필요하지만, 함수 기반 인덱스는 인덱스만 추가하면 끝 입니다.
“이 쿼리가 왜 풀 스캔이지?” 하고 EXPLAIN을 봤을 때, WHERE에 함수가 쓰이고 있다면 함수 기반 인덱스 or 쿼리 리팩토링 중 하나를 선택해야 합니다. 일반적으로는 쿼리 리팩토링이 더 안전 합니다.

출처

Real MySQL 8.0 (1권), 8.6 함수 기반 인덱스

Dev Wiki

탐색기