MySQL 8.4 Invisible Index와 함수 기반 인덱스: 10만 건 실행계획으로 확인한 인덱스 리팩토링

운영 데이터베이스에서 인덱스를 정리할 때 어려운 부분은 삭제 문법이 아닙니다. 해당 인덱스가 사라졌을 때 어떤 SQL의 실행계획이 달라지는지 확인하는 일입니다.

평소 자주 실행하는 SQL에서는 사용하지 않더라도 특정 고객 조회나 정산 작업에 필요한 인덱스일 수 있습니다. 반대로 날짜 컬럼에 인덱스가 있어도 조건식에서 함수를 사용하면 원하는 방식으로 탐색하지 못할 수 있습니다.

이번 글에서는 로컬 MySQL 8.4의 주문 데이터 10만 건을 대상으로 두 가지를 확인합니다. 첫 번째는 기존 인덱스를 Invisible로 변경했을 때의 조회 영향입니다. 두 번째는 DATE(created_at) 조건에 함수 기반 인덱스를 적용했을 때의 변화입니다.

본문의 실행 시간과 실제 행 수는 직접 실행한 네 장의 EXPLAIN ANALYZE 캡처를 기준으로 작성했습니다. 각 시간은 단일 실행 측정값이며 반복 측정 평균이나 운영 환경의 성능 보장 수치는 아닙니다.

테스트 조건과 비교 대상입니다

테스트 테이블은 orders_refactor_lab이며, 주요 컬럼과 인덱스는 다음과 같습니다.

구분	구성
데이터베이스	로컬 MySQL 8.4
스토리지 엔진	InnoDB
전체 데이터	100,000행
주요 컬럼	id, customer_id, created_at, amount, payload
고객 조회 인덱스	idx_customer_created(customer_id, created_at)
일반 날짜 인덱스	idx_created_at(created_at)
함수 기반 인덱스	idx_created_day((DATE(created_at)))
고객 조회 조건	customer_id = 1001 — 실제 결과 10행
날짜 조회 조건	DATE(created_at) = '2025-03-01' — 실제 결과 1,440행

테스트 데이터는 2025년 1월 1일 0시부터 1분 간격으로 생성한 합성 데이터입니다. 운영 서비스의 실제 주문 이력을 사용한 테스트는 아닙니다.

조회 컬럼에는 amount를 포함했습니다. 이번 비교는 인덱스에 있는 컬럼만 반환하는 커버링 조회가 아니라, 주문 금액까지 가져오는 SQL을 대상으로 합니다.

기존 인덱스를 숨기면 같은 SQL의 접근 방식이 달라집니다

인덱스가 공개된 상태에서는 10행을 조회합니다

먼저 Invisible Index 사용 허용 설정을 OFF로 두고 고객 인덱스를 공개합니다.

SET SESSION optimizer_switch = 'use_invisible_indexes=off';

ALTER TABLE orders_refactor_lab
    ALTER INDEX idx_customer_created VISIBLE;

EXPLAIN ANALYZE FORMAT=TREE
SELECT id, created_at, amount
FROM orders_refactor_lab
WHERE customer_id = 1001;

캡처에는 Index lookup on orders_refactor_lab using idx_customer_created가 표시됩니다. 고객 번호를 조건으로 복합 인덱스의 선두 컬럼을 탐색한 것입니다.

실제 실행 정보는 actual time=0.0708..0.0729 rows=10 loops=1입니다. 이 노드는 한 번 실행됐으며 10행을 반환했습니다. 마지막 행을 반환할 때까지의 시간은 0.0729ms입니다.

인덱스를 숨기면 10만 행을 스캔합니다

이번에는 같은 인덱스를 Invisible로 변경하고 동일한 SQL을 실행합니다.

ALTER TABLE orders_refactor_lab
    ALTER INDEX idx_customer_created INVISIBLE;

EXPLAIN ANALYZE FORMAT=TREE
SELECT id, created_at, amount
FROM orders_refactor_lab
WHERE customer_id = 1001;

변경 후에는 Table scan 위에 고객 번호를 비교하는 Filter가 나타납니다.

테이블 스캔 노드는 actual time=0.419..11.7 rows=100000 loops=1입니다. 상위 필터는 actual time=0.506..14.6 rows=10 loops=1입니다.

최종 결과는 여전히 10행입니다. 그러나 그 결과를 얻기 위해 하위 단계에서 100,000행을 읽어 필터에 전달했습니다. 최상위 노드의 완료 시간도 0.0729ms에서 14.6ms로 증가했습니다.

이 결과는 idx_customer_created가 해당 SQL에 필요한 인덱스라는 근거입니다. 미사용으로 판단하고 삭제했다면 이 접근 경로를 잃게 됩니다.

여기서 rows=100000은 테이블 스캔 노드가 반환한 실제 행 수입니다. 디스크에서 10만 번 읽었다거나 물리 I/O가 정확히 그만큼 발생했다는 뜻은 아닙니다.

복구는 인덱스를 다시 공개하는 방식입니다

ALTER TABLE orders_refactor_lab
    ALTER INDEX idx_customer_created VISIBLE;

Invisible 상태에서는 인덱스 구조가 남아 있으므로 다시 생성하지 않고 공개 상태로 바꿀 수 있습니다. 운영에서는 복구 후 실제 플랜과 응답 지연이 회복됐는지도 확인해야 합니다.

이 명령은 DDL입니다. 일반 트랜잭션의 ROLLBACK으로 취소하는 방식이 아니라 반대 방향의 DDL을 실행하는 방식입니다.

날짜 컬럼에 인덱스가 있어도 함수 조건을 확인해야 합니다

두 번째 비교 대상은 다음 날짜 조건입니다.

WHERE DATE(created_at) = '2025-03-01'

일반 날짜 인덱스는 created_at의 원본 값을 기준으로 정렬됩니다. 반면 위 조건은 DATE(created_at)의 계산 결과를 비교합니다. 인덱스가 존재한다는 사실만으로 해당 조건에 효율적인 탐색이 적용됐다고 판단할 수 없습니다.

이번 실습에서는 함수 기반 인덱스를 다음과 같이 구성합니다. 생성 SQL은 해당 인덱스가 없을 때 한 번 실행하는 구문입니다.

CREATE INDEX idx_created_day
    ON orders_refactor_lab ((DATE(created_at))) INVISIBLE;

바깥 괄호는 인덱스 키 목록이며, 안쪽 괄호는 함수 표현식을 나타냅니다. Functional Key Parts는 이처럼 표현식의 결과를 인덱스 키로 사용할 수 있게 합니다. MySQL CREATE INDEX 공식 문서

이미 인덱스가 있는 상태라면 다시 생성하지 않고 다음과 같이 숨깁니다. 아래 비교는 함수 인덱스가 존재하는 상태에서 사용 허용 여부를 변경한 실험입니다.

ALTER TABLE orders_refactor_lab
    ALTER INDEX idx_created_day INVISIBLE;

SET SESSION optimizer_switch = 'use_invisible_indexes=off';

EXPLAIN ANALYZE FORMAT=TREE
SELECT id, created_at, amount
FROM orders_refactor_lab
WHERE DATE(created_at) = '2025-03-01';

캡처의 테이블 스캔은 actual time=0.778..13.3 rows=100000 loops=1입니다. 상위 필터는 actual time=17.5..19.8 rows=1440 loops=1입니다.

전체 100,000행을 스캔하고 날짜 조건을 통과한 1,440행을 반환했습니다. 최상위 노드에서 마지막 행을 반환할 때까지의 시간은 19.8ms입니다.

TREE 출력에서는 입력한 DATE(created_at)이 cast(created_at as date) 형태로 표시됩니다. 이 캡처에서는 날짜 변환 조건이 테이블 스캔 뒤의 필터에서 처리되는 것을 확인할 수 있습니다.

함수 인덱스 사용을 허용하면 날짜 조건으로 탐색합니다

현재 연결에서 Invisible Index도 실행계획 후보로 검토하도록 설정합니다.

SET SESSION optimizer_switch = 'use_invisible_indexes=on';

EXPLAIN ANALYZE FORMAT=TREE
SELECT id, created_at, amount
FROM orders_refactor_lab
WHERE DATE(created_at) = '2025-03-01';

변경 후에는 Index lookup on orders_refactor_lab using idx_created_day가 표시됩니다. 함수 결과에 대한 동등 조건이 인덱스 탐색에 사용된 것입니다.

실제 실행 정보는 actual time=0.31..2.87 rows=1440 loops=1입니다. 반환 행 수는 이전과 같은 1,440행이며, 최상위 노드의 완료 시간은 19.8ms에서 2.87ms로 감소했습니다.

두 시간을 단순 비교하면 약 6.9배의 차이입니다. 다만 단일 실행 캡처이므로 이를 운영 환경에서도 보장되는 개선 배수로 해석하지 않습니다. 이번 결과에서 더 중요한 변화는 전체 스캔 후 필터링하던 방식이 날짜 표현식에 대한 인덱스 탐색으로 바뀌었다는 점입니다.

이 설정은 인덱스 선택을 강제하지 않습니다. 또한 현재 연결에서 다른 Invisible Index도 후보가 될 수 있습니다. 실제 선택 여부는 실행계획의 인덱스명으로 확인해야 합니다. MySQL Invisible Index 공식 문서

네 개의 실행계획을 비교한 결과입니다

조회 조건	인덱스 사용 상태	접근 방식	접근 노드의 실제 출력 행 수	최종 결과 행 수	최상위 노드 완료 시간
고객 1001	고객 인덱스 사용	Index lookup	10	10	0.0729ms
고객 1001	고객 인덱스 미사용	Table scan → Filter	100,000	10	14.6ms
2025-03-01	함수 인덱스 미사용	Table scan → Filter	100,000	1,440	19.8ms
2025-03-01	함수 인덱스 사용	Index lookup	1,440	1,440	2.87ms

표의 시간은 최상위 노드의 actual time=a..b에서 b를 읽은 값입니다. 모든 캡처의 loops는 1입니다. 부모 노드 시간에는 자식 노드의 작업이 포함되므로 테이블 스캔 시간과 필터 시간을 더하지 않습니다. MySQL EXPLAIN ANALYZE 공식 문서

또한 cost는 밀리초 단위의 실행 시간이 아닙니다. 추정 rows와 실제 rows도 구분해야 합니다. 예를 들어 테이블 스캔의 추정 행 수는 99,216이지만 실제 출력 행 수는 100,000입니다.

날짜 필터의 추정 행 수 99,216과 실제 결과 1,440의 차이는 조건 선택도 추정에도 오차가 있었음을 보여줍니다. 함수 인덱스를 사용한 캡처에서는 추정 행 수와 실제 행 수가 모두 1,440으로 표시됩니다. 이러한 추정 정확도가 다른 데이터 분포에서도 유지되는지는 별도로 확인해야 합니다.

SQL을 수정할 수 있다면 범위 조건도 검토합니다

함수 기반 인덱스를 추가하기 전에 SQL 변경으로 기존 인덱스를 활용할 수 있는지 확인합니다.

SELECT id, created_at, amount
FROM orders_refactor_lab
WHERE created_at >= '2025-03-01 00:00:00'
  AND created_at <  '2025-03-02 00:00:00';

이번 DATETIME 컬럼에서는 같은 날짜에 속하는 행을 찾는 조건입니다. 원본 컬럼에 범위 조건을 적용하므로 idx_created_at을 이용한 범위 탐색을 검토할 수 있습니다.

이 대안은 이번 네 장의 캡처로 성능을 측정한 항목은 아닙니다. 애플리케이션 SQL을 수정할 수 있다면 먼저 비교할 수 있는 방법으로 제시합니다.

반대로 리포팅 도구나 공통 모듈이 생성하는 SQL을 변경하기 어렵다면 함수 기반 인덱스가 대안이 될 수 있습니다. 두 방법 중 어느 쪽이 적절한지는 조회 빈도와 쓰기 비용, 변경 가능한 범위를 함께 보고 판단합니다.

운영 인덱스 정리는 관측 기간과 복구 기준을 함께 정합니다

사용 기록이 없다는 사실만으로 삭제하지 않습니다

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'index_refactor_lab'
  AND object_name = 'orders_refactor_lab';

이 조회는 미사용 후보를 찾는 출발점입니다. 서버 재시작이나 통계 초기화, 수집 설정과 관측 기간을 확인하고 주간 배치와 월말 정산 등 대표적인 업무가 포함됐는지 판단해야 합니다. schema_unused_indexes 공식 문서

이번 고객 인덱스 실험은 미사용 인덱스를 발견한 사례가 아닙니다. 필요한 인덱스를 숨겼을 때 조회가 악화되는 사례입니다. 해당 SQL이 운영에 있다면 이 결과는 삭제보다 유지 판단을 뒷받침합니다.

숨기는 작업과 검증 연결 설정의 영향 범위는 다릅니다

ALTER INDEX ... INVISIBLE은 인덱스의 공통 메타데이터를 변경합니다. 기본 설정을 사용하는 다른 연결들의 실행계획에도 영향을 줄 수 있습니다.

반면 SET SESSION optimizer_switch는 현재 연결의 설정입니다. 신규 인덱스를 Invisible로 만든 뒤 검증 연결에서 사용을 허용하는 방식과 기존 인덱스를 숨기는 방식은 영향 범위가 다릅니다.

숨긴 인덱스도 데이터 변경에 따라 계속 유지됩니다. 저장 공간과 유지 비용이 사라지는 것은 아니며, UNIQUE 인덱스의 유일성 검사도 유지됩니다. 따라서 Invisible 상태의 테스트로 삭제 후 쓰기 성능까지 검증했다고 판단하면 안 됩니다.

변경 전에 되돌릴 명령과 업무 지표를 준비합니다

운영에서는 후보 인덱스를 하나씩 검증하고, 영향받는 SQL의 지연 시간과 오류, 처리량을 확인합니다. 문제가 나타나면 VISIBLE로 복구한 뒤 실제 플랜을 다시 확인합니다.

PRIMARY KEY는 Invisible로 만들 수 없습니다. 외래 키를 지원하는 인덱스와 인덱스명을 지정한 힌트가 있는 SQL도 별도로 확인해야 합니다.

Visibility 변경과 신규 인덱스 생성은 작업 비용이 다릅니다. 새 인덱스 생성에는 데이터 읽기와 인덱스 구성 작업이 필요하며, 온라인 DDL도 메타데이터 잠금 때문에 대기할 수 있습니다. 무영향 작업으로 가정하지 않고 장기 트랜잭션과 변경 시간대의 자원 여유를 확인해야 합니다. InnoDB Online DDL 제약

함수 인덱스는 기존 날짜 인덱스의 완전한 대체재가 아닙니다

DATE(created_at) 인덱스는 날짜 표현식에 맞춘 인덱스입니다. 시각까지 포함한 범위 조회를 지원하는 created_at 인덱스를 곧바로 삭제할 근거가 되지는 않습니다.

또한 함수 인덱스는 표현식과 결과 타입에 맞춰 설계해야 합니다. 문자열 표현식에는 collation도 고려합니다. DATE_FORMAT() 등 다른 표현식까지 같은 인덱스를 사용할 것이라고 가정하지 않습니다.

검증을 마친 함수 인덱스를 공개하는 명령은 다음과 같습니다.

ALTER TABLE orders_refactor_lab
    ALTER INDEX idx_created_day VISIBLE;

공개 후에는 일반 연결 설정에서도 해당 인덱스가 선택되는지 확인합니다. 이 공개 이후의 결과는 이번 네 장의 비교 측정에는 포함하지 않았습니다.

이번 테스트에서는 같은 결과를 반환하는 SQL이라도 인덱스의 사용 가능 여부에 따라 전체 스캔과 인덱스 조회로 접근 방식이 달라졌습니다. 인덱스를 줄일 때는 의존하는 SQL을 확인하고, 인덱스를 추가할 때는 실제 조건식과 유지 비용을 함께 검토하는 것이 필요합니다.