함수 기반 조건의 위험: DATE(), LOWER(), CAST()가 인덱스를 막는 이유

WHERE DATE(created_at) = '2026-07-17', LOWER(email) = 'user@example.com', CAST(order_code AS UNSIGNED) = 5000은 업무 의미가 분명하고 읽기도 쉽다. 그러나 인덱스가 있는 컬럼을 함수의 입력으로 바꾸는 순간 MySQL은 저장된 인덱스 키에서 검색 시작점과 종료점을 직접 계산하지 못할 수 있다. 그 결과 equality lookup이나 range scan 대신 전체 테이블 또는 전체 인덱스를 읽으면서 행마다 함수를 계산한다.

이 문제는 단순한 SQL 문법 취향이 아니다. 데이터가 증가하면 읽은 행 수, CPU 사용량, buffer pool 오염, 스토리지 I/O와 tail latency를 함께 키운다. 이 글은 MySQL 8.0 이상을 기준으로 DATE(), LOWER(), CAST()가 B-Tree 접근 경로를 막는 내부 이유를 설명하고, 의미를 보존하는 rewrite, collation과 타임존의 주의점, generated column과 functional index의 선택 기준을 다룬다. 실행계획 예제는 MySQL 8.0 검증 환경에서 실행한다.

1. 인덱스는 함수 결과가 아니라 저장된 키 순서를 정렬한다

일반 secondary B-Tree 인덱스 (created_at)은 원본 created_at 값을 정렬한다. 옵티마이저가 다음 조건을 보면 상수 경계를 인덱스 키 공간에 바로 대응시킬 수 있다.

created_at >= '2026-07-17 00:00:00'
AND created_at < '2026-07-18 00:00:00'

반면 DATE(created_at) = '2026-07-17'은 인덱스에 저장된 각 DATETIME 값에 DATE()를 적용한 뒤 비교하는 표현이다. 일반 (created_at) 인덱스에는 DATE(created_at) 결과가 정렬되어 있지 않다. 옵티마이저가 이 표현을 동치 범위로 자동 역변환하지 못하면 access condition이 아니라 읽은 뒤 평가하는 residual predicate가 된다.

flowchart LR
    Q[함수 기반 WHERE 조건] --> N{원본 인덱스 키에 대한<br/>경계를 만들 수 있는가?}
    N -- 예 --> R[ref 또는 range access]
    R --> S[필요한 key 구간만 탐색]
    N -- 아니오 --> F{동일 표현의 functional index나<br/>generated column index가 있는가?}
    F -- 예 --> E[표현식 결과 인덱스 탐색]
    F -- 아니오 --> A[table/index scan]
    A --> C[후보 행마다 함수 계산]
    C --> W[WHERE filter]

핵심은 “함수가 느리다”가 아니라 함수 적용 전 값과 적용 후 값의 순서 및 경계를 옵티마이저가 일반 인덱스에서 이용할 수 있는가다. 함수 계산 자체가 가벼워도 1억 행을 읽게 만들면 비용은 커진다. 반대로 동일한 표현을 저장한 functional index가 있고 쿼리 표현이 그 인덱스와 일치하면 함수 기반 검색도 인덱스를 사용할 수 있다.

2. 재현용 테이블과 데이터

다음 테이블은 시간, 이메일, 고정 폭 문자열 주문번호를 함께 가진다. 20,000행을 20일에 분산하고, 검색 대상 컬럼마다 일반 인덱스를 생성한다. 이 데이터는 절대 성능 수치를 비교하는 benchmark가 아니라 access type과 선택된 key를 확인하기 위한 축소 예제다.

DROP TABLE IF EXISTS function_predicate_orders;

CREATE TABLE function_predicate_orders (
  id BIGINT NOT NULL PRIMARY KEY,
  created_at DATETIME NOT NULL,
  email VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
  order_code VARCHAR(8) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY ix_created_at (created_at),
  KEY ix_email (email),
  KEY ix_order_code (order_code)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 20000;

INSERT INTO function_predicate_orders
  (id, created_at, email, order_code, amount)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 20000
)
SELECT n,
       TIMESTAMP('2026-07-01 00:00:00')
         + INTERVAL MOD(n - 1, 20) DAY
         + INTERVAL MOD(n * 7, 86400) SECOND,
       CONCAT('User', MOD(n, 1000), '@Example.com'),
       LPAD(n, 8, '0'),
       MOD(n * 37, 100000) / 100
FROM seq;

ANALYZE TABLE function_predicate_orders;

SELECT COUNT(*) AS total_rows,
       MIN(created_at) AS min_created_at,
       MAX(created_at) AS max_created_at
FROM function_predicate_orders;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE function_predicate_orders (...);
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION cte_max_recursion_depth = 20000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO function_predicate_orders (...) WITH RECURSIVE seq AS (...) SELECT ...;
Query OK, 20000 rows affected (0.15 sec)
Records: 20000  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE function_predicate_orders;
+-------------------------------------------+---------+----------+----------+
| Table                                     | Op      | Msg_type | Msg_text |
+-------------------------------------------+---------+----------+----------+
| mysql_tech_note.function_predicate_orders | analyze | status   | OK       |
+-------------------------------------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) AS total_rows,
    ->        MIN(created_at) AS min_created_at,
    ->        MAX(created_at) AS max_created_at
    -> FROM function_predicate_orders;
+------------+---------------------+---------------------+
| total_rows | min_created_at      | max_created_at      |
+------------+---------------------+---------------------+
|      20000 | 2026-07-01 00:00:07 | 2026-07-20 23:59:40 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

email은 대소문자를 구분하는 utf8mb4_0900_as_cs collation을 사용한다. 따라서 이 예제에서 LOWER()를 제거하는 것은 단순 rewrite만으로 의미가 같지 않다. collation이 대소문자를 구분하지 않는 운영 스키마와 의도적으로 차이를 두었다.

3. DATE(): 컬럼을 날짜로 자르지 말고 상수를 범위로 바꾼다

3.1 함수 조건은 인덱스 전체를 읽게 만들 수 있다

다음 두 쿼리는 저장 시간대가 같다는 전제에서 2026년 7월 10일 하루의 행을 찾는다. 첫 번째는 인덱스 컬럼을 함수에 넣고, 두 번째는 원본 컬럼에 반개구간 [시작, 다음 날 시작)을 적용한다.

EXPLAIN
SELECT id, created_at, amount
FROM function_predicate_orders
WHERE DATE(created_at) = '2026-07-10';

EXPLAIN
SELECT id, created_at, amount
FROM function_predicate_orders
WHERE created_at >= '2026-07-10 00:00:00'
  AND created_at <  '2026-07-11 00:00:00';

실행 결과(MySQL 8.0.x):

mysql> EXPLAIN
    -> SELECT id, created_at, amount
    -> FROM function_predicate_orders
    -> WHERE DATE(created_at) = '2026-07-10';
+----+---------------------------+------+---------------+------+-------+-------------+
| id | table                     | type | possible_keys | key  | rows  | Extra       |
+----+---------------------------+------+---------------+------+-------+-------------+
|  1 | function_predicate_orders | ALL  | NULL          | NULL | 20101 | Using where |
+----+---------------------------+------+---------------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT id, created_at, amount
    -> FROM function_predicate_orders
    -> WHERE created_at >= '2026-07-10 00:00:00'
    ->   AND created_at <  '2026-07-11 00:00:00';
+----+---------------------------+-------+---------------+---------------+------+-----------------------+
| id | table                     | type  | possible_keys | key           | rows | Extra                 |
+----+---------------------------+-------+---------------+---------------+------+-----------------------+
|  1 | function_predicate_orders | range | ix_created_at | ix_created_at | 1000 | Using index condition |
+----+---------------------------+-------+---------------+---------------+------+-----------------------+
1 row in set (0.00 sec)

첫 계획에서 type=ALL, key=NULL이 나타나면 일반 (created_at) 인덱스를 검색 경계로 쓰지 못한 것이다. 두 번째 계획의 type=range, key=ix_created_at은 정확한 날짜 구간만 탐색할 수 있음을 뜻한다. 옵티마이저 통계 표본에 따라 rows 값은 달라질 수 있으므로 숫자 자체보다 access type과 key의 방향을 본다.

3.2 열린 상한은 시간 정밀도 변화에도 안전하다

하루 검색을 다음처럼 작성하면 마지막 시각의 정밀도에 의존한다.

created_at BETWEEN '2026-07-10 00:00:00' AND '2026-07-10 23:59:59'

컬럼이 DATETIME(6)이면 23:59:59.000001부터 23:59:59.999999까지 빠진다. >= 시작 AND < 다음 시작은 DATETIME, DATETIME(6), TIMESTAMP의 fractional seconds와 무관하게 경계를 표현한다. 월별 검색도 같은 원칙으로 월초 이상, 다음 달 월초 미만을 사용한다.

3.3 타임존 변환은 상수 쪽에서 끝내야 한다

UTC로 저장한 TIMESTAMP나 DATETIME을 한국 날짜로 검색한다고 해서 다음처럼 컬럼마다 변환하면 안 된다.

DATE(CONVERT_TZ(created_at, 'UTC', 'Asia/Seoul')) = '2026-07-10'

애플리케이션 또는 쿼리의 상수 계산 단계에서 한국 날짜 경계를 UTC instant로 변환하고, 최종 조건은 원본 컬럼 범위로 만든다. 다만 DST가 있는 지역은 하루가 항상 24시간이 아니므로 INTERVAL 1 DAY로 UTC 경계를 기계적으로 더하지 말고 지역 시간대 규칙으로 시작과 다음 시작을 각각 계산해야 한다.

4. LOWER(): collation, 정규화 규칙, 검색 키를 분리한다

4.1 대소문자 무시 collation이면 함수가 불필요할 수 있다

LOWER(email) = LOWER(?)는 대소문자 무시 검색의 흔한 구현이다. 그러나 컬럼 collation이 이미 case-insensitive라면 원본 컬럼을 파라미터와 직접 비교해도 의미가 맞을 수 있다. 먼저 다음을 확인해야 한다.

컬럼의 character set과 collation은 무엇인가?
업무상 대소문자뿐 아니라 accent도 구분해야 하는가?
공백, Unicode normalization, locale별 case folding 규칙은 무엇인가?
equality와 정렬에 같은 규칙을 써도 되는가?

이 글의 email은 case-sensitive이므로 LOWER() 조건과 원본 equality 조건은 의미가 다르다. 먼저 일반 인덱스만 있는 상태의 계획을 확인한다.

EXPLAIN
SELECT id, email
FROM function_predicate_orders
WHERE LOWER(email) = 'user42@example.com';

EXPLAIN
SELECT id, email
FROM function_predicate_orders
WHERE email = 'User42@Example.com';

실행 결과(MySQL 8.0.x):

mysql> EXPLAIN
    -> SELECT id, email
    -> FROM function_predicate_orders
    -> WHERE LOWER(email) = 'user42@example.com';
+----+---------------------------+-------+---------------+----------+-------+--------------------------+
| id | table                     | type  | possible_keys | key      | rows  | Extra                    |
+----+---------------------------+-------+---------------+----------+-------+--------------------------+
|  1 | function_predicate_orders | index | NULL          | ix_email | 20101 | Using where; Using index |
+----+---------------------------+-------+---------------+----------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT id, email
    -> FROM function_predicate_orders
    -> WHERE email = 'User42@Example.com';
+----+---------------------------+------+---------------+----------+------+-------------+
| id | table                     | type | possible_keys | key      | rows | Extra       |
+----+---------------------------+------+---------------+----------+------+-------------+
|  1 | function_predicate_orders | ref  | ix_email      | ix_email |   20 | Using index |
+----+---------------------------+------+---------------+----------+------+-------------+
1 row in set (0.00 sec)

첫 번째 계획은 각 email을 소문자로 계산한 뒤 비교하므로 ix_email을 equality lookup에 사용하기 어렵다. 두 번째 계획은 저장된 값과 같은 collation으로 직접 비교하므로 ref 접근이 가능하다. 그러나 두 번째는 대소문자가 다른 입력을 허용하지 않으므로 업무 의미를 확인하지 않고 기계적으로 바꾸면 안 된다.

4.2 정규화 컬럼은 규칙과 인덱스를 명시적으로 만든다

검색 규칙이 명확하고 여러 쿼리에서 반복된다면 generated column에 정규화 결과를 저장하고 인덱스를 둘 수 있다. 다음 DDL은 LOWER(email) 결과를 email_lc에 저장한다.

ALTER TABLE function_predicate_orders
  ADD COLUMN email_lc VARCHAR(120)
    GENERATED ALWAYS AS (LOWER(email)) STORED,
  ADD KEY ix_email_lc (email_lc);

EXPLAIN
SELECT id, email
FROM function_predicate_orders
WHERE email_lc = 'user42@example.com';

SELECT id, email, email_lc
FROM function_predicate_orders
WHERE email_lc = 'user42@example.com'
ORDER BY id
LIMIT 3;

실행 결과(MySQL 8.0.x):

mysql> ALTER TABLE function_predicate_orders
    ->   ADD COLUMN email_lc VARCHAR(120)
    ->     GENERATED ALWAYS AS (LOWER(email)) STORED,
    ->   ADD KEY ix_email_lc (email_lc);

Query OK, 20000 rows affected (0.18 sec)
Records: 20000  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
    -> SELECT id, email
    -> FROM function_predicate_orders
    -> WHERE email_lc = 'user42@example.com';

+----+-------------+---------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table                     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | function_predicate_orders | NULL       | ref  | ix_email_lc   | ix_email_lc | 483     | const |   20 |   100.00 | NULL  |
+----+-------------+---------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT id, email, email_lc
    -> FROM function_predicate_orders
    -> WHERE email_lc = 'user42@example.com'
    -> ORDER BY id
    -> LIMIT 3;

+------+--------------------+--------------------+
| id   | email              | email_lc           |
+------+--------------------+--------------------+
|   42 | User42@Example.com | user42@example.com |
| 1042 | User42@Example.com | user42@example.com |
| 2042 | User42@Example.com | user42@example.com |
+------+--------------------+--------------------+
3 rows in set (0.00 sec)

generated column은 검색 키의 존재와 타입을 스키마에 드러내며, 애플리케이션마다 다른 정규화 로직을 구현하는 문제를 줄인다. 반면 base column 변경 시 generated value와 secondary index도 갱신하므로 쓰기 비용과 저장 공간이 늘어난다. VIRTUAL generated column에도 인덱스를 만들 수 있지만 DDL, redo/undo, online operation 특성은 대상 버전과 실제 테이블에서 검증해야 한다.

MySQL 8.0.13 이상에서는 functional key part도 선택지다. 예를 들어 CREATE INDEX ix_email_lower ON t ((LOWER(email)))처럼 표현식 결과를 직접 인덱싱할 수 있다. 다만 다음 이유로 “모든 함수 조건에 expression index를 추가한다”는 접근은 피해야 한다.

쿼리 표현과 인덱스 표현의 타입, collation, 길이와 함수가 맞아야 한다.
함수가 deterministic하고 functional index 제한을 만족해야 한다.
숨은 generated column과 secondary index 유지 비용이 추가된다.
정규화 정책이 스키마에서 덜 명시적으로 보일 수 있다.
기존 대형 테이블의 DDL 시간과 replica 영향이 발생한다.

5. CAST(): 타입 변환 방향이 검색 경로와 결과 의미를 바꾼다

5.1 인덱스 컬럼을 CAST하면 정렬 기준이 달라진다

order_code는 '00005000' 같은 8자리 문자열로 저장되어 lexical order와 equality를 제공한다. 다음 첫 쿼리는 모든 문자열을 숫자로 변환한 결과를 비교한다. 두 번째는 파라미터를 컬럼 타입과 표현에 맞춘다.

EXPLAIN
SELECT id, order_code, amount
FROM function_predicate_orders
WHERE CAST(order_code AS UNSIGNED) = 5000;

EXPLAIN
SELECT id, order_code, amount
FROM function_predicate_orders
WHERE order_code = '00005000';

실행 결과(MySQL 8.0.x):

mysql> EXPLAIN
    -> SELECT id, order_code, amount
    -> FROM function_predicate_orders
    -> WHERE CAST(order_code AS UNSIGNED) = 5000;

+----+-------------+---------------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table                     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | function_predicate_orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19465 |   100.00 | Using where |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT id, order_code, amount
    -> FROM function_predicate_orders
    -> WHERE order_code = '00005000';

+----+-------------+---------------------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table                     | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | function_predicate_orders | NULL       | ref  | ix_order_code | ix_order_code | 34      | const |    1 |   100.00 | NULL  |
+----+-------------+---------------------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

CAST(order_code AS UNSIGNED)는 일반 문자열 인덱스에 저장된 키 자체가 아니다. 또한 숫자 변환은 '00005000', '5000', '5000x' 같은 값의 비교 의미와 warning 가능성을 바꿀 수 있다. 식별자, 전화번호, 우편번호처럼 숫자처럼 보이지만 문자열 의미를 가진 컬럼은 애플리케이션 바인딩 타입도 문자열로 맞추는 것이 안전하다.

5.2 CAST를 상수 쪽으로 옮겨도 의미 검증이 먼저다

원칙은 컬럼의 저장 타입과 비교 타입을 맞추되, 가능하면 상수나 바인딩 값 쪽을 변환하는 것이다.

-- 애플리케이션에서 8자리 문자열로 검증·정규화한 값을 바인딩
WHERE order_code = ?

-- 입력이 숫자 업무 키이고 DB 컬럼도 숫자여야 한다면 스키마 타입 재검토
WHERE numeric_order_id = ?

단순히 CAST(? AS CHAR)로 옮기는 것만으로 충분하지 않을 수 있다. 길이, character set, collation, leading zero 정책이 결과를 좌우한다. 입력 '5000'을 CHAR로 바꾸어도 저장 값 '00005000'과 같아지지는 않는다. query rewrite 전에 데이터 모델이 숫자 의미인지 문자열 의미인지 결정해야 한다.

5.3 암묵적 변환도 CAST와 같은 위험을 숨긴다

명시적 CAST()가 없어도 문자열 컬럼을 숫자 literal 또는 숫자 바인딩과 비교하면 MySQL의 coercion rule에 따라 컬럼 값이 숫자 비교에 참여할 수 있다. 실행계획에서 인덱스를 쓰지 못할 뿐 아니라 잘못된 문자열이 0으로 변환되는 등 결과가 예상과 달라질 수 있다.

운영 점검에서는 SQL text뿐 아니라 prepared statement의 실제 bind type을 확인한다. ORM 로그에 값만 보이고 JDBC/MySQL protocol type이 보이지 않으면 애플리케이션 코드와 드라이버 설정까지 확인해야 한다.

6. 세 함수의 rewrite 원칙을 한눈에 보기

위험한 조건	우선 검토할 형태	의미 보존 시 확인할 사항
`DATE(ts) = '2026-07-10'`	`ts >= 시작 AND ts < 다음 시작`	저장 시간대, session time zone, DST, fractional seconds
`LOWER(email) = 'user@example.com'`	적절한 collation의 원본 equality 또는 정규화 검색 키	case/accent 민감도, Unicode 정규화, unique 정책
`CAST(code AS UNSIGNED) = 5000`	컬럼 타입과 같은 문자열 파라미터	leading zero, 비숫자 값, collation, 업무 식별자 의미
반복되는 복잡한 함수식	generated column 또는 functional index	deterministic 여부, 표현식 일치, 쓰기·DDL 비용

rewrite의 목표는 SQL을 짧게 만드는 것이 아니라 원본 인덱스 키 또는 명시적으로 저장한 검색 키에 대한 equality/range 조건을 만드는 것이다. 결과 집합이 달라지면 빠른 쿼리도 올바른 쿼리가 아니다.

7. 실행계획과 런타임에서 확인할 신호

함수 기반 predicate를 점검할 때는 다음 순서가 실용적이다.

SHOW CREATE TABLE로 컬럼 타입, collation, generated column, 인덱스를 확인한다.
실제 bind 값을 대입한 EXPLAIN에서 type, possible_keys, key, rows, Extra를 비교한다.
EXPLAIN ANALYZE를 안전한 비운영 환경 또는 읽기 부하를 통제한 환경에서 실행해 estimated rows와 actual rows를 비교한다.
Performance Schema digest에서 실행 횟수, examined rows, latency를 확인한다.
rewrite 전후 결과 집합을 비교하고 경계값 테스트를 수행한다.

현재 예제의 검색용 인덱스와 generated column 정의는 다음처럼 확인할 수 있다.

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, EXPRESSION
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'function_predicate_orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

SELECT COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME, EXTRA, GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'function_predicate_orders'
  AND COLUMN_NAME IN ('created_at', 'email', 'email_lc', 'order_code')
ORDER BY ORDINAL_POSITION;

실행 결과(MySQL 8.0.x):

mysql> SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, EXPRESSION
    -> FROM information_schema.STATISTICS
    -> WHERE TABLE_SCHEMA = DATABASE()
    ->   AND TABLE_NAME = 'function_predicate_orders'
    -> ORDER BY INDEX_NAME, SEQ_IN_INDEX;
+---------------+--------------+-------------+------------+
| INDEX_NAME    | SEQ_IN_INDEX | COLUMN_NAME | EXPRESSION |
+---------------+--------------+-------------+------------+
| ix_created_at |            1 | created_at  | NULL       |
| ix_email      |            1 | email       | NULL       |
| ix_email_lc   |            1 | email_lc    | NULL       |
| ix_order_code |            1 | order_code  | NULL       |
| PRIMARY       |            1 | id          | NULL       |
+---------------+--------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME, EXTRA, GENERATION_EXPRESSION
    -> FROM information_schema.COLUMNS
    -> WHERE TABLE_SCHEMA = DATABASE()
    ->   AND TABLE_NAME = 'function_predicate_orders'
    ->   AND COLUMN_NAME IN ('created_at', 'email', 'email_lc', 'order_code')
    -> ORDER BY ORDINAL_POSITION;
+-------------+--------------+--------------------+------------------+-----------------------+
| COLUMN_NAME | COLUMN_TYPE  | COLLATION_NAME     | EXTRA            | GENERATION_EXPRESSION |
+-------------+--------------+--------------------+------------------+-----------------------+
| created_at  | datetime     | NULL               |                  |                       |
| email       | varchar(120) | utf8mb4_0900_as_cs |                  |                       |
| order_code  | varchar(8)   | utf8mb4_0900_ai_ci |                  |                       |
| email_lc    | varchar(120) | utf8mb4_0900_ai_ci | STORED GENERATED | lower(`email`)        |
+-------------+--------------+--------------------+------------------+-----------------------+
4 rows in set (0.00 sec)

type=ALL은 table scan, type=index는 전체 index scan일 수 있다. 둘 다 key가 보인다는 이유만으로 선택적 탐색이라고 판단하면 안 된다. 원하는 신호는 일반적으로 equality의 ref 또는 범위의 range, 의도한 key, 크게 줄어든 후보 행이다. Using where는 그 자체로 실패 신호가 아니며, access condition으로 범위를 줄인 뒤 residual predicate를 추가 평가할 때도 나타난다.

8. 운영 장애로 이어지는 대표 실패 모드

8.1 데이터 증가 후 갑자기 드러나는 일별 배치

초기에는 전체 테이블이 buffer pool에 들어가므로 DATE(created_at) scan도 빨라 보인다. 데이터 보존 기간이 늘면 매일 더 많은 page를 읽고, 업무 트래픽이 사용할 hot page를 밀어낸다. 배치 시간만 길어지는 것이 아니라 API latency가 함께 상승할 수 있다.

8.2 대소문자 정책 없이 추가한 LOWER()

개발자가 입력 편의를 위해 모든 문자열 검색에 LOWER()를 추가하면 기존 인덱스가 무력화된다. 반대로 함수만 제거하면 case-sensitive schema에서 결과가 달라진다. collation 변경은 equality, sorting, unique key 충돌 가능성까지 바꾸므로 쿼리 하나의 hotfix로 처리해서는 안 된다.

8.3 숫자처럼 보이는 식별자의 잘못된 CAST

주문번호나 계정번호의 leading zero는 데이터 의미의 일부일 수 있다. 이를 숫자로 변환하면 서로 다른 표현이 같은 값이 되고, 잘못된 문자열의 변환 warning도 놓칠 수 있다. 성능 문제와 데이터 정확성 문제가 동시에 발생한다.

8.4 functional index를 응급 처방으로 남발

functional index는 유용하지만 인덱스마다 쓰기 증폭, buffer pool 점유, 통계 관리, DDL 비용이 생긴다. 함수 조건을 rewrite할 수 있고 원본 인덱스가 더 범용적이라면 rewrite가 우선이다. 표현식 검색이 핵심 업무 요구이며 반복적으로 사용될 때만 인덱스화하는 편이 좋다.

8.5 통계와 작은 테스트 데이터에 속는 경우

작은 테이블에서는 sargable 조건도 optimizer가 table scan을 선택할 수 있다. 반대로 covering index 전체 scan은 table scan보다 작아서 type=index가 보일 수 있다. 테스트에서는 충분한 데이터 분포를 만들고, ANALYZE TABLE 후 key와 access type뿐 아니라 actual rows와 latency를 함께 본다.

9. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 optimizer와 B-Tree access path를 사용하므로 함수 기반 조건의 기본 원리는 같다. 분산 스토리지와 read replica가 non-sargable predicate를 sargable하게 바꾸지는 않는다.

reader를 늘리면 읽기 동시성은 분산할 수 있지만 쿼리 한 번이 읽는 행 수는 줄지 않는다.
buffer cache hit가 낮아지고 스토리지 읽기가 증가하면 replica 전체의 지연 분포가 나빠질 수 있다.
Performance Insights에서 CPU 또는 SQL latency 상위 digest를 찾은 뒤 실제 predicate와 실행계획을 확인한다.
generated column이나 functional index 추가는 writer 부하와 replica 적용 영향, DDL 수행 방식, parameter group 차이를 사전에 검증한다.
엔진 버전 업그레이드 전후에는 expression matching, collation, optimizer plan이 같은지 staging에서 회귀 테스트한다.

Aurora의 관리형 기능은 관측과 복구를 돕지만 잘못된 검색 경계를 대신 만들어 주지 않는다. 가장 효과적인 개선은 여전히 읽을 key 범위를 SQL과 스키마에 명확히 표현하는 것이다.

10. 변경 및 검증 절차

함수 기반 조건을 발견했을 때 다음 절차로 변경한다.

원본 SQL, 실제 bind value와 bind type, 실행 횟수를 확보한다.
컬럼 타입, collation, time zone, index 정의를 확인한다.
함수식이 표현하는 업무 의미와 경계값을 문서화한다.
원본 컬럼 비교로 rewrite할 수 있는지 먼저 검토한다.
rewrite 전후 결과를 정상값, 경계값, NULL, 잘못된 입력으로 비교한다.
EXPLAIN과 필요 시 EXPLAIN ANALYZE로 access path와 actual rows를 비교한다.
rewrite로 해결할 수 없고 반복 검색이 중요할 때 generated column 또는 functional index를 검토한다.
인덱스 추가 전 쓰기 비용, 디스크, buffer pool, DDL 시간과 replica 영향을 산정한다.
배포 후 digest별 examined rows, latency, CPU와 오류율을 관찰한다.
애플리케이션의 회귀 테스트에 날짜 경계, 대소문자, leading zero 사례를 고정한다.

11. 체크리스트

12. 결론

DATE(), LOWER(), CAST()가 항상 나쁜 것은 아니다. 위험은 일반 B-Tree 인덱스가 원본 키를 정렬하는데, 쿼리가 변환된 결과를 검색하면서도 그 결과에 맞는 검색 경로나 명시적 인덱스를 제공하지 않는 데 있다. 이 경우 MySQL은 필요한 범위를 찾아가는 대신 넓게 읽고 행마다 함수를 평가한다.

가장 먼저 검토할 해법은 컬럼을 그대로 두고 상수 경계와 bind type을 컬럼의 저장 의미에 맞추는 rewrite다. 업무상 함수 결과 검색이 본질적이라면 collation, 정규화 컬럼, generated column, functional index를 명시적으로 설계한다. 다음 글에서는 이런 predicate 형태가 복합 인덱스의 key part 순서와 결합될 때 equality prefix, range boundary, covering 여부가 비용에 어떤 차이를 만드는지 이어서 살펴볼 수 있다.