[MySQL] 인덱스 심화 2 — ORDER BY가 인덱스를 타는 조건, 암시적 형변환, 인덱스 없는 UPDATE의 락 확산

1. 문서 제목

• MySQL 인덱스 심화 2 — ORDER BY가 인덱스를 타는 조건, 암시적 형변환, 인덱스 없는 UPDATE의 락 확산

2. 기술 개요 요약

인덱스의 기본 구조(B+Tree, 클러스터드/세컨더리, 복합 인덱스 컬럼 순서)를 이해한 다음 단계는 경계 사례다. "인덱스가 있는데도 안 타는 쿼리"와 반대로 "안 탈 것 같은데 타는 쿼리"를 정확히 가르는 기준, 그리고 인덱스가 조회 성능을 넘어 UPDATE의 락 범위까지 좌우한다는 사실이 이 문서의 주제다. 판단 기준은 두 가지로 압축된다: ① 인덱스는 "저장된 정렬"이므로, 그 정렬을 그대로 활용할 수 있는 형태의 조건/정렬만 인덱스를 탄다 ② 가공되는 쪽이 컬럼이면 못 타고, 비교값이면 탄다.

다루는 질문:

#	질문	핵심 키워드
1	복합 인덱스 (a,b,c)에서 WHERE b,c + ORDER BY a는 인덱스를 탈까?	SQL 실행 순서, filesort, 선두 컬럼
2	WHERE 절의 컬럼을 함수로 가공하면 왜 안 타나? 비교값 쪽 함수는?	컬럼 가공 vs 비교값 가공
3	varchar 컬럼에 숫자 리터럴로 조회하면 왜 안 타나?	암시적 형변환의 방향
4	GROUP BY는 인덱스를 탈까?	loose/tight index scan
5	인덱스 없는 컬럼으로 UPDATE하면 락은 어디에 걸리나?	풀스캔 락 확산, 데드락

3. 핵심 개념 정리

3-1. ORDER BY가 인덱스를 타는 조건 — "등호로 고정된 프리픽스 + 이어지는 정렬"

함정 사례부터. 복합 인덱스 (a, b, c)가 있을 때:

SELECT * FROM t WHERE b = ? AND c = ? ORDER BY a;

"ORDER BY 컬럼 a가 인덱스 선두니까 탈 수 있다"고 생각하기 쉽지만, 정렬 회피용으로는 못 탄다. SQL 실행 순서로 유도하면: WHERE가 ORDER BY보다 먼저 실행되는데, 선두 컬럼 a에 대한 조건이 없으므로 b, c 조건으로는 인덱스를 seek할 수 없다(선두 컬럼 없이 뒤 컬럼만으로는 원칙적으로 탐색 불가 — 8.0.13+의 Skip Scan이 커버링 인덱스 + 선두 컬럼 저카디널리티라는 좁은 조건에서 예외를 만들지만, SELECT *인 이 예시에는 해당 없음). 결국 다른 방식으로 필터한 결과를 별도로 다시 정렬(filesort) 해야 한다.

일반 규칙: 인덱스로 ORDER BY를 대체하려면, ORDER BY 컬럼들이 "등호(=)로 고정된 선행 컬럼들 바로 뒤에 이어지는 인덱스 프리픽스"를 이뤄야 한다.

쿼리 (인덱스 a,b,c 기준)	정렬 회피	이유
WHERE a=1 ORDER BY b	가능	a 등호 고정 → b가 이어지는 프리픽스
WHERE a=1 AND b=2 ORDER BY c	가능	a,b 고정 → c가 이어짐
WHERE a=1 ORDER BY c	불가	b를 건너뜀
WHERE a>1 ORDER BY b	불가	범위 조건 뒤로는 정렬 순서가 보장 안 됨
WHERE b=2 AND c=3 ORDER BY a	불가	선두 a에 조건이 없어 seek 불가 → filesort

• 미세 뉘앙스: 옵티마이저가 마지막 사례에서 "a 순서로 인덱스 풀 스캔하며 b,c를 필터"하는 계획(type=index + Using where, 커버링이면 Using index도 함께)을 고를 수는 있다. 하지만 이는 인덱스 전체를 읽는 것이라 b,c 조건으로 좁혀 seek하는 것과는 전혀 다르고, 보통은 filesort가 선택된다. 판단은 항상 EXPLAIN의 Using filesort 유무로 확인한다.

면접 답변으로 압축하면: "WHERE가 ORDER BY보다 먼저 실행되는데, 선두 컬럼에 등호 조건이 없으면 인덱스를 seek할 수 없어 필터 결과를 다시 정렬해야 합니다. 인덱스로 정렬을 생략하려면 등호로 고정된 선행 컬럼 뒤에 ORDER BY 컬럼이 프리픽스로 이어져야 합니다."

3-2. 컬럼 가공 vs 비교값 가공 — 가공되는 쪽이 어디냐가 기준

인덱스 미사용 조건을 개별 암기하면 끝이 없다. 기준은 하나다: 인덱스는 "컬럼 원본 값" 기준으로 정렬돼 있으므로, 컬럼 쪽이 가공되면 정렬을 활용할 수 없다. 비교값 쪽 가공은 실행 전에 상수로 평가되므로 무관하다.

패턴	인덱스	이유
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-07'	못 탐	컬럼을 함수로 가공 — 모든 행에서 함수를 실행해봐야 비교 가능
WHERE created_at + INTERVAL 1 DAY > NOW()	못 탐	컬럼에 연산 — 위와 동일
WHERE created_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND CURDATE()	탐	함수가 비교값 쪽에만 있음 — 옵티마이저가 실행 전에 상수로 평가한 뒤 일반 범위 검색과 동일하게 처리
WHERE user_id != 3 / NOT IN (...)	대체로 못 탐	조건을 만족하는 범위가 "정렬된 한 구간"이 아니라 나머지 전부 — 스캔 범위가 넓어 옵티마이저가 풀스캔 선택. 단 절대 규칙이 아니라 비용 판단
WHERE a = 1 OR b = 2	조건부	각 컬럼에 개별 인덱스가 있으면 index_merge(union)가 가능하지만, 한쪽이라도 없으면 풀스캔. "OR는 인덱스에 불리하다"가 실무 감각으로는 맞되, 절대 명제는 아님
LIKE 'abc%'	탐	프리픽스 고정 → 정렬 구간으로 좁힐 수 있음
LIKE '%abc', LIKE '%abc%'	못 탐	시작이 불특정 → 정렬 활용 불가

세 번째 사례가 특히 헷갈리는 지점이다. "함수가 들어가면 무조건 안 탄다"로 외우면 틀린다 — 컬럼이 가공되면 못 타고, 비교값이 가공되면 탄다. CURDATE(), DATE_SUB(...) 같은 함수는 쿼리 실행 시점에 한 번 평가되어 상수가 되기 때문이다.

대안: 컬럼 가공이 꼭 필요한 조건이라면 MySQL 5.7+의 generated column이나 8.0.13+의 함수 기반 인덱스(functional index)로 "가공된 값 자체"에 인덱스를 걸 수 있다.

3-3. 암시적 형변환 — 변환당하는 쪽이 컬럼이면 인덱스가 죽는다

-- user_no 컬럼이 VARCHAR인데 숫자로 저장돼 있는 경우
SELECT * FROM users WHERE user_no = 123;      -- 인덱스 못 탐
SELECT * FROM users WHERE user_no = '123';    -- 인덱스 탐

이것도 3-2와 같은 원리의 특수 케이스다. MySQL의 비교 규칙상 문자열과 숫자를 비교하면 문자열 쪽이 숫자로 변환된다. 즉 varchar 컬럼 = 숫자 리터럴은 사실상 CAST(user_no AS DOUBLE) = 123이 되어 컬럼 가공과 동일해진다 — 모든 행의 컬럼 값을 변환해봐야 하므로 인덱스를 못 탄다.

• 반대 방향은 안전하다: INT 컬럼 = '123'(문자열 리터럴)이면 변환당하는 쪽이 리터럴이므로 인덱스를 탄다.
• 실무 발생 경로: DataGrip 등에서 값이 숫자처럼 보여서 따옴표 없이 조회하거나, 애플리케이션에서 타입이 어긋난 파라미터를 바인딩하는 경우. 겉보기엔 멀쩡한 쿼리라 EXPLAIN을 떠보기 전까지 눈치채기 어렵다.
• "자료가 많아서 안 탄다"가 아니라 "컬럼이 변환당해서 못 탄다"가 정확한 이유 — 틀리기 쉬운 설명 포인트.

3-4. GROUP BY는 인덱스를 탄다

GROUP BY를 인덱스 미사용 사례로 착각하기 쉽지만, GROUP BY도 인덱스의 정렬 순서를 활용할 수 있는 연산이라 ORDER BY와 같은 조건에서 인덱스를 탄다. (참고로 8.0부터 GROUP BY의 암시적 정렬은 사라졌다 — "그룹핑 = 정렬"이 아니라 "그룹핑에 인덱스 순서를 쓸 수 있다"가 정확한 프레임.) 그룹핑 컬럼이 인덱스 프리픽스와 일치하면:

• Tight index scan: 인덱스를 순서대로 읽으며 그룹 경계에서 집계 — 정렬/임시테이블 생략.
• Loose index scan (Using index for group-by): 그룹마다 첫/마지막 항목만 점프하며 읽음 — MIN()/MAX() 집계에서 특히 효율적.

인덱스를 못 태우면 EXPLAIN에 Using temporary(+ 구버전에서는 Using filesort)가 나타난다. "GROUP BY라서 안 타는" 게 아니라, ORDER BY와 마찬가지로 인덱스 프리픽스와 맞지 않아서 안 타는 것이다.

3-5. 인덱스는 UPDATE의 락 범위도 결정한다

인덱스를 조회 최적화로만 이해하면 절반이다. InnoDB의 행 락은 테이블의 행이 아니라 "인덱스 레코드"에 걸린다. 그래서 UPDATE의 WHERE 조건이 인덱스를 타느냐가 락의 폭을 결정한다:

상황	락 범위
WHERE 조건 컬럼에 인덱스 있음	해당 인덱스 레코드만 잠금 — 유니크 인덱스 등호 검색이면 레코드 락만, 비유니크/범위 검색이면 REPEATABLE READ 기준 넥스트키 락으로 인접 갭까지
WHERE 조건 컬럼에 인덱스 없음	풀스캔하며 읽은 모든 레코드에 락 — 조건에 맞지 않는 행까지 포함, 사실상 테이블 전체가 잠기는 효과

인덱스 없는 컬럼으로 UPDATE ... WHERE name = '이준호'를 실행하면, '이준호'를 찾기 위해 전체를 스캔해야 하고 그 과정에서 스캔한 레코드 전부에 배타 락이 걸린다. 이 상태에서 다른 트랜잭션의 UPDATE와 겹치면 블로킹과 데드락이 급증한다. "느린 쿼리" 문제가 아니라 "동시성 붕괴" 문제가 되는 것이다.

• 완화 뉘앙스: 격리수준이 READ COMMITTED이면 확산이 덜하다 — 조건에 안 맞는 행의 락을 조기 해제하고, 별개로 semi-consistent read(최신 커밋 버전을 읽어 락 대기 자체를 회피)도 동작한다. 하지만 InnoDB 기본값인 REPEATABLE READ에서는 위 설명대로 동작하므로, "UPDATE/DELETE의 WHERE 컬럼에는 인덱스가 필수"가 실무 원칙.

면접 답변으로 압축하면: "InnoDB 락은 인덱스 레코드에 걸리기 때문에, WHERE 컬럼에 인덱스가 없으면 풀스캔 과정에서 조건에 맞지 않는 행까지 전부 잠급니다. 조회가 느려지는 수준이 아니라 다른 UPDATE와의 블로킹·데드락으로 번지므로, 갱신 쿼리의 WHERE 컬럼 인덱스는 성능이 아니라 동시성 문제입니다."

4. 사용 예시 및 코드 스니펫

ORDER BY 인덱스 활용 여부를 EXPLAIN으로 확인:

CREATE INDEX idx_abc ON t (a, b, c);

EXPLAIN SELECT * FROM t WHERE a = 1 ORDER BY b;
-- Extra에 filesort 없음 → 인덱스 정렬 활용

EXPLAIN SELECT * FROM t WHERE b = 2 AND c = 3 ORDER BY a;
-- Extra: Using filesort → 정렬을 인덱스로 대체하지 못함

컬럼 가공 대신 비교값 가공으로 바꾸는 리팩토링:

-- 나쁜 예: 컬럼 가공 → 인덱스 못 탐
SELECT * FROM orders
WHERE DATE(created_at) = '2026-07-12';

-- 좋은 예: 비교값 쪽으로 옮김 → created_at 인덱스 탐
SELECT * FROM orders
WHERE created_at >= '2026-07-12 00:00:00'
  AND created_at <  '2026-07-13 00:00:00';

암시적 형변환 확인 (MySQL 5.7+는 실행계획 경고로 알려준다):

EXPLAIN SELECT * FROM users WHERE user_no = 123;  -- user_no VARCHAR
SHOW WARNINGS;
-- "Cannot use range access ... due to type or collation conversion" 류 경고 확인

5. 실제 사용 시 주의점 / Best Practice

• 인덱스 미사용 조건을 개별 암기하지 말고 두 기준으로 환원할 것: ① 인덱스는 저장된 정렬이다 — 정렬을 한 구간으로 활용할 수 없는 조건(부정, 중간 LIKE, 선두 컬럼 없는 정렬)은 못 탄다 ② 가공당하는 쪽이 컬럼이면 못 타고, 비교값이면 탄다(함수·형변환 공통).
• "함수가 들어가면 안 탄다", "OR면 안 탄다"처럼 무조건 단정으로 답하지 말 것 — 비교값 함수는 타고, OR는 index_merge가 가능하다. 단정 대신 원리를 말하면 꼬리질문에 무너지지 않는다.
• UPDATE/DELETE의 WHERE 컬럼 인덱스는 조회 성능이 아니라 락 동시성 관점에서 점검할 것. 배치 갱신 작업 전에 EXPLAIN으로 풀스캔 여부를 반드시 확인.
• 타입이 어긋난 바인딩은 조용히 풀스캔을 만든다 — 스키마의 컬럼 타입과 애플리케이션 파라미터 타입 일치를 코드리뷰 체크포인트로.
• 판단이 애매하면 언제나 EXPLAIN + SHOW WARNINGS가 정답지다.

6. 참고자료 / 공식 문서 출처

• MySQL 8.0 Reference — ORDER BY Optimization: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
• MySQL 8.0 Reference — GROUP BY Optimization (loose/tight index scan): https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
• MySQL 8.0 Reference — Type Conversion in Expression Evaluation (암시적 형변환 규칙): https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
• MySQL 8.0 Reference — Index Merge Optimization: https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
• MySQL 8.0 Reference — InnoDB Locking (락은 인덱스 레코드에 걸린다): https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• MySQL 8.0 Reference — Locks Set by Different SQL Statements (인덱스 없는 UPDATE의 락 범위): https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html