인덱스 검색 조건

인덱스를 만들었는데도 쓰이지 않는 가장 흔한 이유는, 조건이 인덱스에 저장된 값과 같은 모양이 아니기 때문입니다. DATE(created_at)처럼 열을 함수로 감싸면 데이터베이스는 그 변환의 결과를 행마다 계산해야 하므로, 원래 시각의 순서에서 시작점을 바로 찾기 어렵습니다.

이 글에서는 하루 동안의 게시글, 제목의 접두어, 최신순 목록을 하나의 검색 규칙으로 묶습니다. 동등 비교는 한 지점, 범위는 시작과 끝, 접두 LIKE는 문자열의 구간, ORDER BY는 리프를 읽어 나가는 방향으로 옮깁니다. 앞 글의 post_search_demo와 복합 인덱스를 그대로 쓰며, 그 데이터는 1월의 것이므로 뒤의 7월 검색은 시험용 행을 넣기 전까지 0행입니다.

사용자의 하루를 UTC 구간으로 바꾼다

애플리케이션이 서울의 날짜 2026-07-14를 요청했다고 해 봅시다. 이 교재는 시각을 UTC로 저장하므로, 서울의 하루를 UTC의 범위로 바꾸어야 합니다. 서울은 UTC보다 9시간 빠르고 일광 절약 시간을 쓰지 않으므로, 서울의 2026-07-14 00:00은 UTC 2026-07-13 15:00이고 다음 날 서울의 00:00은 UTC 2026-07-14 15:00입니다.

그래서 SQL은 created_at >= '2026-07-13 15:00:00' AND created_at < '2026-07-14 15:00:00'을 받습니다. 날짜 구간의 끝은 사용자의 시간대에서 계산하되, SQL에는 이미 계산한 [시작, 끝)의 UTC 값을 넘깁니다. MySQL 안에서 Asia/Seoul 같은 이름 있는 시간대로 바꾸려면 서버에 시간대 테이블이 적재되어 있어야 하므로, 이 글은 계산한 두 UTC 값을 직접 씁니다.

열을 가공하면 범위를 잃는다

보기에는 간단한 DATE 함수와 앞뒤의 와일드카드가 검색의 범위를 숨깁니다. 아래 첫 쿼리의 DATE(created_at) = '2026-07-14'는 UTC로 저장된 값의 달력 날짜이고, 뒤에서 쓸 반열린 UTC 구간은 서울의 날짜입니다. %index%와 index%도 포함 검색과 접두 검색이라는 다른 뜻이므로, 두 쿼리를 같은 결과의 최적화 전후로 비교하면 안 됩니다.

EXPLAIN FORMAT=TREE
SELECT post_id, title, created_at
FROM post_search_demo
WHERE author_id = 42
  AND DATE(created_at) = '2026-07-14'
  AND title LIKE '%index%'
ORDER BY DATE(created_at) DESC;

-- 마이크로초가 있는 DATETIME(6)에서 잘못된 하루 끝
SELECT post_id
FROM post_search_demo
WHERE created_at BETWEEN
  '2026-07-14 00:00:00' AND '2026-07-14 23:59:59';

첫 쿼리의 계획은 작성자라는 접두어까지만 인덱스로 좁히고, DATE(created_at)와 title LIKE '%index%'는 읽은 행마다 따로 거르는 조건으로 남습니다. %index%는 시작하는 문자열을 알 수 없어 B+Tree에서 접두어로 찾을 수 없기 때문입니다. 실제 계획은 인덱스와 통계에 따라 다르며, WHERE에서 DATE의 값이 하나로 고정되었으므로 같은 식의 정렬은 필요 없어져 생략될 수도 있습니다.

둘째 쿼리의 오류는 성능이 아니라 정확성입니다. DATETIME(6)에 23:59:59.500000인 행이 있다면, 날짜는 같아도 23:59:59를 끝으로 둔 BETWEEN에서 빠집니다. 이 데이터에는 7월의 행이 없어 지금은 결과가 비어 있지만, 끝을 초 단위로 적은 닫힌 범위는 열의 정밀도와 어긋나 정답인 행 자체를 놓칩니다.

이렇게 오류는 두 종류입니다. 함수와 앞쪽의 와일드카드는 인덱스가 가진 정렬된 범위를 쓰지 못하게 해 검사량을 키우고, 끝을 닫힌 값으로 적으면 데이터 타입의 정밀도와 맞지 않아 행을 놓칩니다. 성능의 최적화가 정확성의 오류를 가려서는 안 되므로, 먼저 서울의 하루를 [2026-07-14 00:00, 2026-07-15 00:00)로 정한 뒤 UTC로 바꾸어 인덱스의 범위에 씁니다.

조건을 점검할 때는 네 곳을 의심합니다. WHERE의 왼쪽에서 인덱스의 열이 함수나 산술, 형 변환 없이 그대로 나오는지, 범위의 끝을 마지막 순간으로 짐작하지 않고 다음 구간의 시작으로 두었는지 봅니다. LIKE에 고정된 접두어가 있는지, 아니면 정말 포함 검색이 필요한지 나누고, ORDER BY도 저장된 인덱스의 열과 방향을 그대로 쓰는지 확인합니다.

인덱스로 범위를 계산할 수 있는 조건

인덱스에 저장된 키와 상수나 파라미터를 직접 비교해 탐색의 범위를 계산할 수 있는 조건을 SARGable(Search ARGument able) 조건이라 부릅니다. created_at >= ? AND created_at < ?는 분명한 두 끝을 주고, title LIKE 'index%'는 고정된 접두어로 문자열의 범위를 줍니다.

복합 인덱스는 앞쪽의 동등 조건들 뒤에서 처음 범위 조건이 나오는 열까지를 연속된 범위로 읽습니다. 범위 뒤의 열은 인덱스 조건 푸시다운(Index Condition Pushdown, ICP)으로 리프에서 걸러질 수는 있어도, 탐색의 범위를 더 잘게 나누지는 못합니다. 전통적인 EXPLAIN의 Extra에 Using index condition이 보이면 이 방식으로 거른 것입니다. 그래서 SQL 문장에서 조건의 순서를 바꾸는 것보다, 인덱스 열의 순서와 연산자의 종류가 중요합니다.

반열린 범위와 접두 LIKE

애플리케이션이 날짜 구간의 끝을 계산해 파라미터로 넘기고, SQL은 created_at을 직접 비교합니다. 접두 검색만 제공한다면 %를 뒤에만 두고, 임의의 포함 검색은 FULLTEXT나 별도의 검색 엔진이라는 요구로 나눕니다.

CREATE INDEX ix_post_search_author_created_title
  ON post_search_demo (author_id, created_at, title);

EXPLAIN ANALYZE
SELECT post_id, title, created_at
FROM post_search_demo
WHERE author_id = 42
  AND created_at >= '2026-07-13 15:00:00.000000'
  AND created_at <  '2026-07-14 15:00:00.000000'
  AND title LIKE 'index%'
ORDER BY created_at DESC, post_id DESC
LIMIT 20;

이 쿼리는 작성자가 같은 구간에서 시각 범위의 시작 리프를 찾아, 다음 구간의 시작 전까지만 읽고 그 안에서 제목의 조건을 평가합니다. 끝 시각 바로 앞인 2026-07-14 14:59:59.999999 UTC의 행은 포함되고, 2026-07-14 15:00:00.000000 UTC의 행은 빠집니다. 지금의 데이터에는 7월의 행이 없어 실제로 읽는 행은 0개이며, 끝 근처의 행은 다음 절에서 넣어 확인합니다.

이 인덱스에서는 created_at이 범위 조건이므로, 그 뒤의 title은 범위를 더 나누는 탐색이 아니라 범위 안의 필터로 동작할 수 있습니다. 제목의 접두어가 더 선택적이고 핵심이라면 (author_id, title, created_at)라는 후보와 실제 작업량을 비교합니다. 또 이 인덱스는 시각 뒤에 title이 오므로 같은 시각에서 post_id DESC의 순서까지 맞춰 주지 못합니다. 날짜와 제목, 정렬을 모두 최적화할 수 없다면 무엇을 먼저 할지 정하고, 옵티마이저가 여러 인덱스를 합쳐 쓰는 인덱스 병합(index merge)을 고르더라도 복합 인덱스보다 언제나 낫다고 가정하지 않습니다.

양 끝 근처의 행으로 확인한다

성능의 숫자만으로는 날짜의 버그를 찾기 어렵습니다. 범위의 양 끝 근처 시각 네 개를 트랜잭션 안에서 넣어 결과를 확인한 뒤 되돌려, 실습을 반복할 수 있게 합니다.

START TRANSACTION;

INSERT INTO post_search_demo
  (author_id, status, created_at, view_count, title)
VALUES
  (42, 'PUBLISHED', '2026-07-13 14:59:59.999999', 10, 'index-before'),
  (42, 'PUBLISHED', '2026-07-13 15:00:00.000000', 10, 'index-start'),
  (42, 'PUBLISHED', '2026-07-14 14:59:59.999999', 10, 'index-end-minus'),
  (42, 'PUBLISHED', '2026-07-14 15:00:00.000000', 10, 'index-end');

SELECT title
FROM post_search_demo
WHERE author_id = 42
  AND created_at >= '2026-07-13 15:00:00.000000'
  AND created_at <  '2026-07-14 15:00:00.000000'
ORDER BY created_at;

ROLLBACK;

title
index-start
index-end-minus

시작 직전의 행은 빠지고, 시작과 같은 행은 들어가며, 끝 직전의 행은 들어가고, 끝과 같은 행은 빠졌습니다. ROLLBACK으로 시험용 행은 남지 않지만, 이 행들이 받았던 AUTO_INCREMENT의 번호는 비게 됩니다. 문자열의 조건도 같은 방식으로 시험해, 조건이 index%라면 index, indexing, reindexing을 넣어 접두 검색이 무엇을 포함하는지 확인합니다.

실험의 범위를 넓혀 볼 수도 있습니다. YEAR나 LOWER, 산술을 쓴 조건을 원래 열의 범위 비교로 바꾸어 보고, 열을 DATETIME(0)에서 DATETIME(6)으로 바꾸어도 반열린 범위의 결과가 같은지 확인합니다. 대소문자를 구별하는 정렬 규칙의 임시 열에서 LIKE의 결과와 계획을 비교하고, 정렬을 ASC와 DESC로 바꾸어 인덱스를 거꾸로 읽는지 따로 정렬하는지도 기록합니다.

정렬 규칙과 시간대의 함정

문자열의 범위는 정렬 규칙의 영향을 받습니다. 대소문자나 악센트를 구별하는지가 업무의 요구와 다르면, 인덱스를 써도 결과의 뜻이 틀릴 수 있습니다. 바이트 그대로 비교해야 한다고 쿼리마다 CAST하기보다, 열의 정렬 규칙과 검색 기능을 설계합니다.

MySQL 8.4는 ((DATE(created_at)))처럼 표현식을 인덱스로 만드는 함수 기반 인덱스(functional index)와 생성 열을 지원합니다. 그러나 사용자마다 시간대가 다르면 날짜를 하나의 고정된 표현식으로 저장하기 어려우므로, 사용자의 시간대에서 범위를 계산해 파라미터로 넘기는 방식이 더 두루 쓰입니다.

일광 절약 시간을 쓰는 지역에서는 바뀌는 날의 하루가 23시간이나 25시간일 수 있으므로, UTC의 시작에 단순히 24시간을 더하지 않습니다. LIKE의 _와 %를 사용자 입력으로 받는다면 3장 2편의 이스케이프 규칙을 적용하고, 문자열과 숫자의 암묵적인 변환은 결과와 인덱스의 사용을 바꿀 수 있으므로 바인딩의 타입을 열과 맞춥니다. OR로 서로 다른 범위를 합치면 인덱스 병합이나 여러 범위의 조회가 될 수 있으니, UNION ALL로 나누는 편이 더 분명한지 측정합니다.

API가 날짜 문자열만 받는다면 사용자의 시간대와 일광 절약 시간의 규칙을 서비스 계층에서 확정합니다. 데이터베이스 서버의 시간대에 기대면 환경마다 결과가 달라질 수 있습니다. 검색 화면의 포함 검색을 접두 검색으로 몰래 줄이지 말고, 기능의 차이를 사용자에게 드러내 검색의 품질과 데이터베이스의 비용을 제품의 결정으로 남깁니다. 범위가 시작된 뒤 다음 열을 어떻게 쓰는지는 7장 4편의 복합 인덱스 순서로 이어집니다.

인덱스를 쓸 수 있는 조건을 만드는 것은 문법의 요령이 아니라, 업무의 범위를 저장된 키의 구간으로 정확히 옮기는 일입니다. 더 빠르지만 다른 행을 돌려주는 쿼리는 개선이 아닙니다.

더 깊이: 함수 기반 인덱스가 쓰이는 조건

MySQL의 함수 기반 인덱스(functional index)는 숨은 가상 생성 열(virtual generated column)로 구현됩니다. 가상 열 자체는 저장 공간을 쓰지 않고 인덱스만 일반 인덱스처럼 공간을 차지하지만, 함수 기반 키 부분 하나하나는 테이블의 전체 열 수 한도에 들어갑니다. 인덱스에 저장되는 것은 식을 계산한 결과이므로, 옵티마이저가 이 인덱스를 쓰려면 쿼리에도 같은 식이 나와야 합니다.

MySQL 8.4 매뉴얼의 예에서 SUBSTRING(col1, 1, 10)으로 만든 인덱스는 SUBSTRING(col1, 1, 9) = ...에는 쓰이지 않고, 인자까지 같은 SUBSTRING(col1, 1, 10) = ...에만 쓰입니다. 그래서 ((DATE(created_at))) 인덱스를 만들었다면 쿼리도 DATE(created_at) = ?로 써야 합니다. 그리고 이 식은 저장된 UTC 값의 달력 날짜이므로, 이 글에서 계산한 서울의 하루와 같은 뜻이 아니라는 점은 인덱스를 만들어도 그대로입니다.

쓸 수 있는 범위에도 제한이 있습니다. 함수 기반 키 부분은 UNIQUE 인덱스에는 넣을 수 있지만 기본 키에는 넣을 수 없고, 접두 길이를 지정할 수 없으며, 외래 키에도 쓸 수 없습니다. 식에는 서브쿼리와 파라미터, 변수, 저장 함수를 넣을 수 없고, 생성 열에 허용되는 함수만 쓸 수 있습니다.

정리

• 인덱스의 열을 가공하면 저장된 정렬의 범위를 쓸 수 없게 됩니다.
• 시간의 구간은 시작을 포함하고 다음 구간의 시작을 빼는 반열린 구간으로 나타냅니다.
• 사용자의 하루는 사용자의 시간대에서 계산한 뒤 UTC의 두 값으로 넘깁니다.
• 접두 LIKE와 임의의 포함 검색은 서로 다른 기능이고 비용도 다릅니다.
• 범위의 양 끝 근처 행으로 정확성을 시험하고, 실행 계획으로 접근 방식을 함께 확인합니다.

연습 문제

1. 서울의 7월 한 달

서울 시간으로 2026년 7월 한 달 동안의 공개 게시글 가운데 제목이 mysql로 시작하는 행을 최신순으로 조회해 보세요. 월의 UTC 시작과 다음 달의 시작을 파라미터로 가정합니다. 그리고 다음 네 행만 있는 종이 위의 사례에서 어떤 행이 돌아올지 먼저 골라 봅니다. 시각은 모두 UTC입니다.

SELECT post_id, title, created_at
FROM post_search_demo
WHERE status = 'PUBLISHED'
  AND created_at >= '2026-06-30 15:00:00.000000'
  AND created_at <  '2026-07-31 15:00:00.000000'
  AND title LIKE 'mysql%'
ORDER BY created_at DESC, post_id DESC;

2. DATE 함수를 범위로 바꾸기

다음 두 쿼리는 같은 행을 셉니다. 실습 데이터에서 결과는 얼마이고, 두 쿼리가 인덱스를 쓰는 방식은 어떻게 다른가요?

SELECT COUNT(*) AS rows_by_function
FROM post_search_demo
WHERE author_id = 42
  AND DATE(created_at) = '2026-01-02';

SELECT COUNT(*) AS rows_by_range
FROM post_search_demo
WHERE author_id = 42
  AND created_at >= '2026-01-02 00:00:00'
  AND created_at <  '2026-01-03 00:00:00';

rows_by_function
1440

rows_by_range
1440

다음 글 실행 계획과 커버링 인덱스에서는 인덱스를 고른 뒤에도 원래 행을 다시 읽는 비용과, 그 비용을 없애는 커버링 인덱스를 실행 계획으로 분석합니다.