#16. 함수 기반 인덱스
표현식으로 만든 인덱스의 함정
# 함수 기반 인덱스 — 표현식으로 만든 인덱스의 함정
> 📘 **학습자료 17편** | 연결 퀴즈: [**인덱스 고급2 풀러가기**](quiz.html?set=H)
> MySQL 8.0부터는 **함수 그 자체에 인덱스를 거는 방법** 이 생겼습니다. 강력하지만, 두 가지 까다로운 조건이 있습니다.
> 📚 **이전 편**: [16편 - 페이지 분할과 단편화]
> "WHERE에 함수를 쓰면 인덱스를 못 탄다"는 규칙은 6편(인덱스가 통하는 쿼리)에서 다뤘습니다.
> 이번 편은 그 한계를 우회하기 위해 도입된 기능입니다.
## 이 자료를 다 읽으면 알게 되는 것
- **함수 기반 인덱스(Functional Index)** 가 무엇이고, 왜 도입됐는지
- 어떤 문법으로 만들고, 어떻게 EXPLAIN에서 확인하는지
- 왜 **표현식이 정확히 일치할 때만** 인덱스가 적용되는지
- 왜 **표현식이 다른 함수의 인자로 들어가면** 인덱스가 적용되지 않는지
- 함수 기반 인덱스를 설계할 때 실수하기 쉬운 두 가지 함정
---
## 📑 목차
- [1. 출발점: 왜 함수 기반 인덱스가 필요한가](#1-출발점-왜-함수-기반-인덱스가-필요한가)
- [2. 함수 기반 인덱스 만들기](#2-함수-기반-인덱스-만들기)
- [⭐ 3. 함정 ① 정확히 같은 표현식이어야 한다](#3-함정--정확히-같은-표현식이어야-한다)
- [⭐ 4. 함정 ② 표현식이 독립 조건이어야 한다](#4-함정--표현식이-독립-조건이어야-한다)
- [5. EXPLAIN으로 직접 확인하기](#5-explain으로-직접-확인하기)
- [6. 정리: 두 함정 한눈에 보기](#6-정리-두-함정-한눈에-보기)
- [핵심 요약](#핵심-요약)
---
## 1. 출발점: 왜 함수 기반 인덱스가 필요한가
인덱스 활용의 규칙을 떠올려봅시다.
> WHERE 절에서 컬럼에 함수를 적용하면 인덱스를 못 탄다.
> `WHERE YEAR(birth_date) = 1990` → 인덱스 못 탐
> `WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31'` → 인덱스 탐
이유는 간단합니다. 인덱스는 `birth_date` 값을 정렬해두었지, `YEAR(birth_date)` 값을 정렬해둔 게 아닙니다. 옵티마이저는 모든 행에 함수를 적용해보지 않으면 결과를 알 수 없으니, 결국 풀 테이블 스캔으로 떨어집니다.
그런데 실무에서는 함수를 쓸 수밖에 없는 경우가 있습니다. 예를 들면:
- 이름의 대소문자 구분 없이 검색: `WHERE LOWER(name) = 'john'`
- 풀네임으로 검색: `WHERE CONCAT(first_name, last_name) = 'johnsmith'`
- 텍스트의 앞부분만 비교: `WHERE SUBSTRING(content, 1, 10) = '검색어'`
이런 경우를 위해 MySQL 8.0이 도입한 것이 **함수 기반 인덱스(Functional Based Index)** 입니다.
> 💡 **핵심 아이디어**
> "컬럼 값"이 아니라 **"표현식의 결과 값"** 을 인덱스에 저장하자. 그러면 옵티마이저가 그 표현식으로 검색할 때 인덱스를 쓸 수 있다.
---
## 2. 함수 기반 인덱스 만들기
문법은 일반 인덱스와 거의 같습니다. 다만 **컬럼 자리에 표현식이 들어가고, 표현식을 괄호로 한 번 더 감쌉니다.**
```sql
CREATE TABLE human (
id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
-- 함수 기반 인덱스: CONCAT 결과에 인덱스를 건다
INDEX ix_fullname ((CONCAT(first_name, '', last_name)))
);
```
이제 다음 쿼리는 인덱스를 탑니다:
```sql
SELECT * FROM human
WHERE CONCAT(first_name, '', last_name) = 'johnsmith';
```
내부적으로 InnoDB는 각 행에 대해 `CONCAT(first_name, '', last_name)` 값을 미리 계산해서 인덱스에 저장해둡니다. 그러면 검색 시점에는 그냥 그 값을 인덱스에서 찾기만 하면 되죠.
> 💡 **함수 기반 인덱스의 비용**
> 공짜는 아닙니다. 행을 INSERT/UPDATE할 때마다 표현식을 다시 계산해서 인덱스를 갱신해야 합니다. 1편에서 배운 "인덱스는 공짜가 아니다"는 원칙이 여기에도 그대로 적용됩니다.
---
## 3. 함정 ① 정확히 같은 표현식이어야 한다
> ⭐ **함수 기반 인덱스의 첫 번째 함정.**
함수 기반 인덱스는 **인덱스에 저장된 표현식과 WHERE 절의 표현식이 글자 그대로 일치해야** 사용됩니다. 한 글자라도 다르면 인덱스를 타지 못합니다.
### 예시로 이해하기
다음 인덱스가 있다고 합시다:
```sql
INDEX idx1 ((SUBSTRING(col1, 1, 10)))
```
이 인덱스는 `col1`의 **앞 10글자**를 잘라서 저장해둡니다. 그러면 어떤 쿼리가 이 인덱스를 탈까요?
| 쿼리 | 인덱스 사용? | 이유 |
|---|---|---|
| `WHERE SUBSTRING(col1, 1, 10) = '1234567890'` | ✅ 사용 | 표현식이 정확히 일치 |
| `WHERE SUBSTRING(col1, 1, 9) = '123456789'` | ❌ 미사용 | 9 vs 10, 인자가 다름 |
| `WHERE SUBSTRING(col1, 0, 10) = '1234567890'` | ❌ 미사용 | 0 vs 1, 인자가 다름 |
| `WHERE SUBSTR(col1, 1, 10) = '1234567890'` | ❌ 미사용 | SUBSTR vs SUBSTRING, 함수명이 다름 |
> ⚠️ **자주 닿지 않는 직관**
> "어차피 SUBSTRING(col1, 1, 9)는 SUBSTRING(col1, 1, 10)의 앞부분이니까 인덱스를 쓸 수 있지 않을까?" 라고 생각할 수 있습니다.
> 하지만 옵티마이저는 그렇게 추론하지 않습니다. **인덱스에 저장된 키와 WHERE의 키가 동일한 표현식인지** 만 봅니다. 의미상 비슷해도 표현식이 다르면 인덱스를 타지 않습니다.
---
## 4. 함정 ② 표현식이 독립 조건이어야 한다
> ⭐ **함수 기반 인덱스의 두 번째 함정.**
표현식이 정확히 일치한다고 해서 무조건 인덱스를 타는 건 아닙니다. **표현식이 그 자체로 하나의 조건** 이어야 합니다. 다른 함수의 인자로 들어가면 인덱스를 못 탑니다.
### 예시로 이해하기
다음 인덱스가 있다고 합시다:
```sql
INDEX ix_fullname ((CONCAT(first_name, '', last_name)))
```
이제 여러 쿼리를 비교해봅시다.
**✅ 인덱스를 타는 쿼리들** (CONCAT 결과가 그대로 비교 대상):
```sql
WHERE CONCAT(first_name, '', last_name) = 'johnsmith'
WHERE CONCAT(first_name, '', last_name) > 'john'
WHERE CONCAT(first_name, '', last_name) IN ('johnsmith', 'janedoe')
WHERE CONCAT(first_name, '', last_name) BETWEEN 'a' AND 'z'
```
각각 동등 비교, 부등 비교, IN, BETWEEN — 모두 다르지만 공통점은 **CONCAT 결과가 곧바로 비교의 한쪽 항** 이라는 점입니다.
**❌ 인덱스를 타지 못하는 쿼리들** (CONCAT 결과가 다른 함수에 감싸짐):
```sql
WHERE CONCAT(first_name, '', last_name) LIKE 'john%'
WHERE UPPER(CONCAT(first_name, '', last_name)) = 'JOHNSMITH'
WHERE LENGTH(CONCAT(first_name, '', last_name)) > 5
```
이 쿼리들은 CONCAT 결과를 다시 LIKE / UPPER / LENGTH 의 인자로 사용합니다. 옵티마이저 입장에서 이건 "CONCAT으로 검색"하는 게 아니라 **"LIKE/UPPER/LENGTH 결과로 검색"** 하는 것이니, ix_fullname 인덱스가 도움이 안 됩니다.
> 💡 **LIKE는 왜 안 될까?**
> 헷갈리기 쉬운 부분입니다. `WHERE col1 LIKE 'john%'` 같은 쿼리는 일반 인덱스에서는 잘 동작하죠. 그런데 `WHERE CONCAT(...) LIKE 'john%'` 는 다릅니다.
> 이 경우 LIKE는 CONCAT의 **결과 값** 을 다시 패턴 매칭의 인자로 사용합니다. ix_fullname 인덱스는 CONCAT 결과를 정렬해서 저장해둔 것이지만, **그 값에 LIKE 패턴 매칭을 적용한 결과는 또 다른 변환** 입니다. 옵티마이저는 이걸 직접 매핑하지 못합니다.
### 일반 컬럼의 경우와 비교
- 일반 컬럼 `col1`에 인덱스: `WHERE col1 LIKE 'john%'` → 인덱스 사용 (전방 일치 LIKE는 인덱스 사용 가능)
- 함수 기반 인덱스 `CONCAT(...)`: `WHERE CONCAT(...) LIKE 'john%'` → 인덱스 미사용
---
## 5. EXPLAIN으로 직접 확인하기
이론만으로는 와닿지 않으니 EXPLAIN 결과를 직접 봅시다. 인덱스는 위에서 만든 `ix_fullname`을 가정합니다.
### 케이스 1: 정확히 같은 표현식
```sql
EXPLAIN SELECT * FROM human
WHERE CONCAT(first_name, '', last_name) = 'johnsmith';
```
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | human | ref | **ix_fullname** | 1 | NULL |
`key: ix_fullname`, `type: ref`. 함수 기반 인덱스가 정상 적용되었습니다.
### 케이스 2: 표현식이 미묘하게 다름
```sql
EXPLAIN SELECT * FROM human
WHERE CONCAT(first_name, ' ', last_name) = 'john smith';
-- 차이: '' 대신 ' ' (공백) 사용
```
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | human | **ALL** | NULL | 10107 | Using where |
`type: ALL` — 풀 테이블 스캔. **인자 하나가 다르다는 이유로 인덱스가 무력화** 됩니다.
### 케이스 3: 표현식이 다른 함수에 감싸짐
```sql
EXPLAIN SELECT * FROM human
WHERE CONCAT(first_name, '', last_name) LIKE 'john%';
```
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | human | **ALL** | NULL | 10107 | Using where |
`type: ALL` — 역시 풀 테이블 스캔. 표현식은 일치하지만 LIKE의 인자로 들어갔기 때문에 인덱스를 타지 못합니다.
---
## 6. 정리: 두 함정 한눈에 보기
| 함정 | 의미 | 위반 예시 |
|---|---|---|
| ① 정확히 같은 표현식 | 인덱스에 저장된 표현식과 WHERE의 표현식이 글자 그대로 일치 | `SUBSTRING(c, 1, 10)` 인덱스에 `SUBSTRING(c, 1, 9)` 검색 |
| ② 독립 조건 | 표현식이 다른 함수의 인자가 아닌, 비교의 한쪽 항이어야 함 | `CONCAT(...)` 인덱스를 `CONCAT(...) LIKE 'john%'`로 검색 |
두 조건은 **둘 다** 만족해야 합니다. 하나라도 어기면 풀 테이블 스캔으로 떨어집니다.
### 실무 팁: 함수 기반 인덱스를 설계할 때
- **자주 쓰는 표현식인지 확인** : 표현식이 다양하게 변형된다면 함수 기반 인덱스의 효과가 떨어집니다. 한 가지 패턴으로 통일된 검색에만 효과적입니다.
- **쿼리 작성자에게 표현식을 명확히 공유** : "어떤 표현식으로 검색해야 인덱스를 타는지" 팀 내 합의가 필요합니다. 표현식이 미묘하게 어긋나는 순간 풀 스캔이 됩니다.
- **차라리 컬럼으로 만드는 것도 대안** : `CONCAT(first_name, '', last_name)` 을 매번 계산하느니, `full_name`이라는 GENERATED 컬럼으로 빼고 일반 인덱스를 거는 방법도 있습니다.
> 💡 **MySQL 8.0 이전에는 어땠나**
> 8.0 이전에는 함수 기반 인덱스 자체가 없었습니다. 위와 같은 검색을 빠르게 하려면 별도의 컬럼을 만들고 트리거나 애플리케이션 레벨에서 값을 채워넣는 우회 방법을 써야 했습니다. 8.0은 그 우회를 표준 기능으로 흡수한 셈입니다.
---
## 핵심 요약
이번 편에서 꼭 가져가야 할 것들:
> 🎯 **함수 기반 인덱스는 "컬럼 값"이 아니라 "표현식의 결과 값"을 인덱스에 저장한다**
> MySQL 8.0+에서 사용 가능. WHERE에 함수를 써도 인덱스를 탈 수 있게 해주는 기능.
> 🎯 **두 가지 함정을 모두 피해야 인덱스를 탄다**
> ① 표현식이 정확히 같아야 한다 (인자 하나만 달라도 안 됨)
> ② 표현식이 독립 조건이어야 한다 (다른 함수의 인자로 들어가면 안 됨)
체크리스트:
- [ ] 함수 기반 인덱스가 무엇이고 왜 만들어졌는지 설명할 수 있다
- [ ] 함수 기반 인덱스의 생성 문법을 안다
- [ ] 표현식이 미묘하게 다르면 인덱스를 타지 못하는 이유를 설명할 수 있다
- [ ] 표현식이 다른 함수의 인자로 들어가면 인덱스를 못 타는 이유를 설명할 수 있다
- [ ] EXPLAIN 결과에서 함수 기반 인덱스 사용 여부를 판단할 수 있다
- [ ] 같은 LIKE인데 일반 인덱스에서는 동작하고 함수 기반 인덱스에서는 동작하지 않는 이유를 설명할 수 있다
> 📝 체크리스트를 다 채울 자신이 있다면? [**인덱스 고급2 퀴즈 도전하기**](quiz.html?set=H)
---
## 이제 퀴즈에 도전하기
이번 편에서 익힌 개념들을 다시 정리하면:
- 함수 기반 인덱스는 표현식의 결과 값을 인덱스에 저장한다 (MySQL 8.0+)
- 옵티마이저는 표현식의 의미적 동치를 추론하지 않는다 — 글자 그대로 일치만 본다
- 함정 ①: 표현식이 정확히 같아야 한다
- 함정 ②: 표현식이 독립 조건이어야 한다 (다른 함수의 인자로 들어가면 안 됨)
- 두 조건이 모두 깨지면 풀 테이블 스캔으로 떨어진다
다음 편에서는 **인덱스 머지(Index Merge)** — 옵티마이저가 한 쿼리에 여러 인덱스를 동시에 사용하는 최적화 — 를 다룹니다. 8편에서 본 "여러 조건이 결합된 쿼리"에서 복합 인덱스가 없을 때 옵티마이저가 어떤 카드를 꺼내드는지 살펴봅시다.
**📘 [학습자료 18편: 인덱스 머지 — 옵티마이저는 인덱스를 합쳐 쓴다]**
---
## Reference
- MySQL 공식 문서: [CREATE INDEX - Functional Key Parts](https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-functional-key-parts)
- StackExchange: [MySQL 8 is not indexing on functional index](https://dba.stackexchange.com/questions/329260/mysql-8-is-not-indexing-on-functional-index)