인덱스 검색 조건

인덱스를 만들었는데도 사용하지 않는 가장 흔한 이유는 조건이 인덱스에 저장된 값과 같은 모양이 아니기 때문입니다.

DATE(created_at)처럼 컬럼을 함수로 감싸면 데이터베이스는 변환 결과를 각 행에서 계산해야 하므로 원래 시각 순서의 시작점을 바로 찾기 어렵습니다.

이번에는 7월 하루의 게시글, 제목 접두어, 최신순 목록을 하나의 검색 규칙으로 묶습니다.

동등 비교는 한 지점, 범위는 시작과 끝, 접두 LIKE는 문자열 구간, ORDER BY는 리프 진행 방향이라는 식으로 번역합니다.

앞 문서의 post_search_demo와 복합 인덱스를 유지합니다.

애플리케이션이 서울 날짜 2026-07-14를 요청했다고 가정하고 UTC 저장 시각으로 변환합니다.

날짜 구간의 끝은 사용자 시간대에서 계산하되 SQL에는 이미 계산된 [start, end) UTC 값을 전달합니다.

이 예제에서 서울의 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'을 받습니다.

이름 있는 시간대를 MySQL 안에서 변환하려면 서버 시간대 테이블이 적재되어 있어야 하므로, 이 장에서는 계산된 두 UTC 경계를 직접 사용합니다.

열 가공과 인덱스 손실

보기에는 간단한 DATE 함수와 앞뒤 와일드카드가 검색 범위를 숨깁니다.

BETWEEN ... 23:59:59는 마이크로초 정밀도 행을 빠뜨릴 수 있고 %index%는 시작 문자열을 알 수 없어 B+Tree 접두 탐색을 할 수 없습니다.

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';

계획은 작성자 접두어까지만 쓰거나 전체 필터를 수행하고, 23:59:59.500000 행은 날짜가 같아도 BETWEEN 결과에서 빠집니다.

plan:
  usable prefix: author_id only
  DATE(created_at): residual filter
  title LIKE '%index%': residual filter
  ORDER BY DATE(created_at): sort

row at 2026-07-14 23:59:59.500000
  expected by user: included
  BETWEEN result: excluded

오류 결과는 두 종류입니다.

첫째, 함수와 선행 와일드카드는 인덱스가 가진 정렬 경계를 직접 사용하지 못해 검사량을 키웁니다.

둘째, 닫힌 끝값을 초 단위로 적으면 데이터 타입 정밀도와 불일치하여 정답 행 자체를 누락합니다.

성능 최적화가 정합성 오류를 가려서는 안 됩니다.

먼저 하루라는 업무 범위를 정확히 [2026-07-14 00:00, 2026-07-15 00:00)로 표현한 뒤, 그 모양이 인덱스 범위에도 적합함을 확인합니다.

함수·패턴·끝값을 따로 의심하기

1. 컬럼 원형 — WHERE 왼쪽에서 인덱스 컬럼이 함수·산술·형 변환 없이 나타나는지 봅니다.
2. 범위 끝 — 마지막 순간을 추측하지 말고 다음 구간의 시작을 배타적으로 둡니다.
3. LIKE 시작 — 고정 접두어가 있는지, 임의 포함 검색이 정말 필요한지 구분합니다.
4. 정렬 식 — ORDER BY도 저장된 인덱스 컬럼 자체와 방향을 맞춥니다.

SARGable 조건

SARGable 조건은 인덱스에 저장된 키와 상수·파라미터를 직접 비교해 탐색 범위를 계산할 수 있는 형태입니다.

created_at >= ? AND created_at < ?는 명확한 두 경계를 제공하고, title LIKE 'index%'는 고정 접두어 범위를 제공합니다.

복합 인덱스에서는 앞선 동등 조건 뒤에 첫 범위가 나타난 지점부터 연속 범위를 읽습니다.

범위 뒤 컬럼은 ICP(Index Condition Pushdown, 인덱스 조건 푸시다운)로 리프에서 필터될 수 있어도 다음 탐색 경계를 자유롭게 만들지는 못합니다.

조건 순서를 SQL 텍스트에서 바꾸는 것보다 인덱스 컬럼 순서와 연산자 종류가 중요합니다.

업무 날짜를 검색 범위로 번역하기

1. 업무 시간대 — 사용자의 로컬 날짜 시작과 다음 날짜 시작을 시간대 규칙으로 계산합니다.
2. 저장 시간대 — 두 경계를 UTC로 변환해 DATETIME 또는 TIMESTAMP 저장 규칙과 맞춥니다.
3. 문자 검색 의도 — 접두 자동완성인지 임의 포함 검색인지 제품 요구를 나눕니다.
4. 정렬 안정성 — created_at 동률을 위해 post_id를 두 번째 정렬 키로 둡니다.

파라미터 기반 범위 계산

애플리케이션이 날짜 구간의 끝을 계산해 파라미터로 전달하고, 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;

시각 범위의 시작 리프를 찾아 다음 구간의 시작 전까지만 읽고, 경계 안에서 제목 조건을 평가합니다.

끝 시각 직전의 마이크로초 행도 포함됩니다.

time contract: [2026-07-13 15:00:00.000000,
                2026-07-14 15:00:00.000000)
boundary row 14:59:59.999999 UTC: included
boundary row 15:00:00.000000 UTC: excluded
access: author_id equality + created_at range

현재 인덱스에서 created_at이 범위이므로 그 뒤 제목은 범위를 더 잘게 나누는 다음 탐색이 아니라 범위 내부 필터로 동작할 수 있습니다.

제목 접두어가 더 선택적이고 핵심이라면 (author_id, title, created_at) 후보와 실제 워크로드를 비교합니다.

하나의 인덱스로 날짜·제목·정렬을 모두 최적화할 수 없을 때 우선순위를 정합니다.

옵티마이저가 인덱스 병합을 선택할 수 있지만 복합 인덱스보다 항상 낫다고 가정하지 않습니다.

네 경계 행으로 누락을 막기

1. 경계 테스트 — 시작 직전·정확한 시작·끝 직전·정확한 끝 네 행을 넣습니다.
2. 문자열 테스트 — 인덱스, 인덱싱, 재인덱싱을 넣어 접두 검색에 무엇이 포함되는지 확인합니다.
3. 계획 비교 — DATE 조건과 범위 조건의 실제 행 수와 정렬 노드를 나란히 둡니다.
4. 제품 합의 — 임의 포함 검색을 포기한 것이 아니라 별도 검색 기능으로 분리했음을 문서화합니다.

경계값과 실행 계획 검증

성능 데이터만으로 날짜 버그를 찾기 어렵습니다.

정확한 경계 시각 네 건을 트랜잭션 안에서 넣고 결과를 확인한 뒤 롤백하여 실습을 반복 가능하게 만듭니다.

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;

결과는 index-start, index-end-minus 두 행입니다.

직전과 정확한 끝 행이 빠지므로 인접한 날짜 조회와 중복도 누락도 없습니다.

정밀도와 시간대를 흔드는 실험

1. 함수 제거 — YEAR, LOWER, 산술 연산 조건을 원래 컬럼의 경계 비교로 각각 바꿉니다.
2. 정밀도 확장 — DATETIME(0)에서 DATETIME(6)으로 바꿔도 반열린 범위 결과가 같은지 확인합니다.
3. 정렬 규칙 비교 — 대소문자 구분 정렬 규칙 임시 컬럼에서 LIKE 결과와 계획을 비교합니다.
4. 정렬 역방향 — ASC·DESC를 바꾸고 이전 버전 인덱스 스캔과 파일 정렬 유무를 기록합니다.

정렬 규칙과 표현식 인덱스의 숨은 조건

문자열 범위는 정렬 규칙 영향을 받습니다.

대소문자·악센트 구분 여부가 업무 요구와 다르면 인덱스를 사용해도 결과 의미가 틀릴 수 있습니다.

이진 비교가 필요하다고 매 쿼리에서 CAST하기보다 컬럼 정렬 규칙과 검색 기능을 설계합니다.

표현식 인덱스나 생성 열로 DATE(created_at)를 인덱싱할 수도 있지만 시간대별 날짜를 하나의 고정 표현식으로 저장하기 어렵습니다.

사용자별 시간대 경계를 파라미터로 계산하는 방식이 범용적입니다.

DST·이스케이프·암묵 변환의 함정

• DST 전환일은 로컬 하루가 23시간 또는 25시간일 수 있으므로 단순히 UTC 시작에 24시간을 더하지 않습니다.
• LIKE의 _와 %를 사용자 입력으로 받을 때 이스케이프 규칙을 적용하지 않으면 의도치 않은 패턴이 됩니다.
• 암묵적 문자열-숫자 변환은 결과와 인덱스 사용을 바꿀 수 있으므로 바인딩 타입을 컬럼과 맞춥니다.
• OR로 서로 다른 범위를 합치면 인덱스 병합 또는 여러 범위가 될 수 있으니 UNION ALL 분리가 더 명확한지 측정합니다.

시간·문자 검색 방식 비교

검색 API가 책임질 경계 계산

API가 날짜 문자열만 받는다면 사용자 시간대와 DST 정책을 서비스 계층에서 확정합니다.

DB 서버 시간대에 기대면 환경별 결과가 달라질 수 있습니다.

검색 UI의 포함 검색을 접두 검색으로 몰래 축소하지 않습니다.

기능 차이를 사용자에게 드러내고, 검색 품질과 DB 비용을 제품 결정으로 남깁니다.

범위가 시작된 뒤 다음 컬럼을 쓰는 방식은 ch7-4의 복합 인덱스 순서로 이어집니다.

시간 구간의 끝 규칙은 트랜잭션 통계와 물리 모델의 시간 타입 선택에서도 다시 사용합니다.

인덱스 검색식 작성 기준

SARGability는 문법 요령이 아니라 업무 범위를 저장된 키의 구간으로 정확히 번역하는 능력입니다.

더 빠르지만 다른 행을 반환하는 쿼리는 개선이 아닙니다.

연습 문제

서울 시간 2026년 7월 전체 공개 게시글 중 제목이 mysql로 시작하는 행을 최신순으로 조회하세요.

월의 UTC 시작·다음 달 시작을 파라미터로 가정하고 SQL과 경계 테스트를 작성합니다.

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;

EXPLAIN FORMAT=TREE
SELECT post_id
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';

핵심 정리

• 인덱스 컬럼 가공은 저장된 정렬 경계를 숨길 수 있습니다.
• 시간 구간은 시작 포함·다음 구간 시작 제외로 표현합니다.
• 접두 LIKE와 임의 포함 검색은 다른 기능과 비용을 가집니다.
• 정합성 경계 테스트와 실행 계획 검증을 함께 수행합니다.

다음 문서에서는 인덱스를 선택한 뒤에도 원본 행을 다시 읽는 비용과 커버링 인덱스를 분석합니다.