MySQL 인덱스 점검｜중복·선택도·쓰기 비용 30분 검증

느린 쿼리를 발견했다고 WHERE 절의 모든 컬럼에 인덱스를 추가하면 조회 한 건은 빨라져도 INSERT·UPDATE가 느려지고 저장 공간이 늘 수 있습니다. MySQL 인덱스 최적화는 후보 쿼리 선정, 기존 인덱스 중복 확인, 실행 계획 비교, 쓰기 영향과 원복 검증까지 한 묶음으로 진행해야 합니다. 아래 순서는 운영 변경 전에 30분 동안 근거를 만드는 방법입니다.

1. 5분: 느린 쿼리 한 개만 고르기

SELECT status, COUNT(*)
FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-08-01'
GROUP BY status;

감으로 후보를 만들지 말고 슬로우 쿼리 로그나 Performance Schema에서 반복 횟수와 누적 시간이 큰 SELECT를 하나 선택합니다. 실제 값 대신 같은 분포를 가진 테스트 데이터를 준비하고, SQL·실행 시간·rows examined·호출 횟수를 변경 전 기록으로 남기세요. 로그 수집부터 필요하다면 MySQL 슬로우 쿼리 30분 진단부터 확인하세요.

2. 5분: 기존 인덱스와 중복 후보 확인

SHOW INDEX FROM orders;

SELECT table_schema, table_name,
       redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE()
  AND table_name = 'orders';

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE()
  AND object_name = 'orders';

schema_redundant_indexes는 다른 인덱스와 중복되거나 포함 관계에 있는 후보를 보여줍니다. schema_unused_indexes는 관찰 기간 동안 사용 이벤트가 없었던 인덱스입니다. 서버가 재시작됐거나 통계 수집 기간이 짧으면 사용하지 않는다는 결론을 내릴 수 없으므로, 이 결과만으로 삭제하지 마세요. 월말 배치나 관리자 기능처럼 드물게 실행되는 쿼리도 확인해야 합니다.

3. 7분: 복합 인덱스 순서 설계

MySQL은 복합 인덱스의 왼쪽부터 이어지는 컬럼 조합을 사용할 수 있습니다. 예를 들어 (status, created_at)은 status 조건과 status+created_at 조건에 후보가 되지만 created_at만 검색하는 모든 쿼리를 자동으로 해결하지는 않습니다. 등호 조건, 범위 조건, 정렬과 실제 데이터 분포를 함께 보고 순서를 정하세요.

EXPLAIN FORMAT=TREE
SELECT status, COUNT(*)
FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-08-01'
GROUP BY status;

-- 테스트 환경에서만 후보 생성
CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

EXPLAIN의 key, rows와 접근 방식을 전후로 비교합니다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로 운영 데이터 변경문에 무심코 사용하지 말고, 먼저 일반 EXPLAIN과 테스트 환경에서 검증하세요. 실행 계획 해석이 필요하면 MySQL EXPLAIN 읽는 법을 함께 참고하세요.

4. 8분: 조회와 쓰기를 함께 비교

한 번의 빠른 결과가 아니라 같은 데이터와 캐시 조건에서 여러 번 측정합니다. 새 인덱스가 SELECT를 줄여도 쓰기 지연이나 버퍼 사용량이 크게 늘면 다른 후보를 검토해야 합니다. 대형 InnoDB 테이블의 인덱스 생성은 온라인 DDL이라도 마지막 단계의 메타데이터 잠금이나 장기 트랜잭션 때문에 지연될 수 있으므로 유지보수 시간과 중단 기준을 정해 두세요.

5. 5분: 원복과 관찰 기간 정하기

• 테이블 정의와 기존 인덱스 목록을 변경 전에 저장합니다.
• 신규 인덱스 이름과 제거용 원복 SQL을 검토받아 준비합니다.
• 배포 후 슬로우 로그, CPU, 잠금 대기와 쓰기 지연을 함께 봅니다.
• 개선 기준을 충족하지 못하면 계획한 시간 안에 원복합니다.
• 기존 인덱스 제거는 충분한 업무 주기를 관찰한 뒤 별도 변경으로 진행합니다.

MySQL 8에서는 기존 보조 인덱스를 invisible로 바꿔 옵티마이저가 사용하지 않는 상태를 시험할 수 있습니다. 인덱스 유지 작업과 UNIQUE 제약은 계속 적용되며, 필요한 인덱스를 숨기면 쿼리가 느려질 수 있으므로 테스트 환경이나 제한된 관찰 절차에서만 사용하세요. 문제가 생겼을 때 다시 visible로 돌리는 명령까지 미리 준비해야 합니다.

TIP

인덱스 최적화 보고서에는 ‘추가했다’가 아니라 대상 SQL, 호출 빈도, 변경 전후 실행 계획, SELECT 중앙값, 쓰기 지연, 원복 시간을 기록하세요. 인덱스 변경 중 서버 CPU가 오르면 리눅스 CPU 20분 진단으로 MySQL PID와 I/O 대기를 분리해 보세요.

마무리

좋은 인덱스는 컬럼 수가 많은 인덱스가 아니라 실제로 반복되는 쿼리의 검사량을 줄이면서 쓰기 비용과 운영 위험을 감당할 수 있는 인덱스입니다. 느린 쿼리 한 개를 고르고, 중복을 확인하고, 테스트 환경에서 실행 계획과 전후 지표를 비교한 뒤 원복 가능한 변경만 적용하세요.

공식 참고 자료

• MySQL Optimization and Indexes
• MySQL 인덱스 사용 방식
• MySQL 복합 인덱스
• schema_redundant_indexes
• schema_unused_indexes
• MySQL Invisible Indexes
• InnoDB Online DDL 제한

검증 기준 2026년 9월 2일 MySQL 8.4 공식 문서를 확인했습니다. 아래 표는 독자가 직접 기록하는 양식이며 SJSBiz 운영 서버의 측정 결과가 아닙니다. 운영 테이블의 DDL은 백업·복구 경로와 잠금 영향을 확인한 뒤 진행하세요.

중복·미사용 인덱스 결과는 삭제 목록이 아니다

중복 가능성이나 미사용 통계에 표시됐다는 이유만으로 인덱스를 바로 삭제하지 않습니다. 고유 제약, 외래 키, 컬럼 순서, 접두 길이와 실제 쿼리 사용 여부를 SHOW CREATE TABLE, SHOW INDEX와 실행 계획으로 다시 확인해야 합니다. 서버 재시작이나 통계 초기화 직후에는 관찰 기간이 짧고, 월말 작업처럼 가끔 실행되는 쿼리가 빠질 수 있습니다.

MySQL 공식 인덱스 문서도 불필요한 인덱스가 공간을 사용하고 INSERT·UPDATE·DELETE 비용을 늘린다고 설명합니다. 조회 속도 한 항목만 보지 않고 쓰기 지연과 저장 공간도 함께 비교합니다.

삭제 전 invisible index로 영향 확인

버전과 환경이 지원한다면 invisible index로 optimizer 선택에서 제외해 삭제 영향을 시험할 수 있습니다. 이 상태에서도 인덱스는 변경 시 유지되고 고유 제약도 계속 적용됩니다. 계획이나 응답 시간이 나빠지면 즉시 visible 상태로 돌릴 명령과 담당자를 먼저 정합니다.

문서 확인·수정 이력: 2026-09-02 — 미사용 통계의 한계, invisible index 시험과 조회·쓰기 비교표를 추가했습니다.