"오늘도 DBA에게 연락이 왔어요. '이 쿼리, 인덱스가 시급합니다!'"
개발자라면 한 번쯤 경험해 봤을 법한 상황이죠. 대체 DBA는 무엇을 보고 이런 요청을 하는 걸까요? 그 비밀은 바로 SQL 쿼리 실행 계획(Execution Plan)에 숨어 있습니다. 오늘은 이 실행 계획을 통해 쿼리 성능의 민낯을 파헤치고, DBA의 간절한 외침에 담긴 속사정을 알아보는 시간을 갖겠습니다.
실행 계획(EXPLAIN)이란? 쿼리의 미래를 엿보는 마법!
EXPLAIN은 여러분이 작성한 SQL 쿼리가 데이터베이스 내부에서 어떤 방식으로, 어떤 순서로 실행될지 그 계획을 미리 보여주는 아주 유용한 명령어입니다. 마치 쿼리의 미래를 엿보는 수정 구슬 같다고 할까요? 이 실행 계획을 잘 분석하면 쿼리 성능을 진단하고 개선하는 데 엄청난 도움을 받을 수 있습니다.
기본 사용법은 정말 간단해요!
분석하고 싶은 SQL 쿼리문 맨 앞에 EXPLAIN 키워드만 붙여주면 끝입니다.
EXPLAIN SELECT 컬럼명1, 컬럼명2 FROM 테이블명 WHERE 조건;더 자세한 분석을 원한다면? ANALYZE 옵션을 활용해보세요! (MySQL 8.0 이상, PostgreSQL 등 지원)
EXPLAIN ANALYZE를 사용하면 쿼리가 실제로 실행되면서 각 단계별로 어떤 일이 일어났는지, 예상치가 아닌 실제 실행 시간과 행 수 등 훨씬 더 상세하고 정확한 정보를 트리 형태로 보여줍니다. (주의: ANALYZE는 쿼리를 실제로 실행하므로, UPDATE, DELETE 등의 DML 쿼리에 사용할 때는 신중해야 합니다.)
EXPLAIN ANALYZE SELECT 컬럼명1, 컬럼명2 FROM 테이블명 WHERE 조건;EXPLAIN 결과 파헤치기: DBA의 외침, 그 핵심 단서들!
자, EXPLAIN 사용법은 알겠는데, 결과표에 나오는 알쏭달쏭한 항목들은 어떻게 해석해야 할까요? DBA가 "인덱스 필요해요!"라고 외치는 결정적인 단서가 숨어있는 핵심 컬럼, 바로 rows, key, Extra (그리고 type) 요 네 친구를 집중적으로 살펴보겠습니다.
1. rows (예상 작업량 확인!)
- 의미: 쿼리를 처리하기 위해 데이터베이스가 읽어야 할 것으로 예상되는 행(row)의 수를 나타냅니다.
- DBA의 속마음: "이 쿼리, 실제 결과는 몇 줄 안 되는데
rows값이 왜 이렇게 크죠? 너무 많은 데이터를 뒤적거리는 것 같아요! 이러니 느릴 수밖에..." - 체크포인트: 실제 반환되는 행 수에 비해 이
rows값이 터무니없이 크다면, 쿼리가 비효율적으로 작동하고 있다는 강력한 증거입니다. 불필요한 데이터를 너무 많이 읽고 있다는 신호이니 주의 깊게 살펴보세요.
2. key (똑똑한 인덱스 사용 여부 체크!)
- 의미: 쿼리 실행 시 실제로 사용된 인덱스의 이름을 보여줍니다.
- DBA의 속마음: "이 쿼리, 분명
idx_user_id인덱스를 타도록 설계했는데key컬럼이NULL이네요? 아니면 엉뚱한 인덱스를 타고 있거나... 인덱스 추가하거나 쿼리 수정이 필요해 보입니다!" - 체크포인트:
key값이NULL이라면? 인덱스를 전혀 사용하지 못하고 있다는 의미입니다. (풀 테이블 스캔 가능성 UP!)- 기대했던 인덱스가 아니라면? 옵티마이저가 다른 인덱스를 사용하는 것이 더 효율적이라고 판단했거나, 쿼리 조건이 우리가 의도한 인덱스를 활용하기 어렵게 작성되었을 수 있습니다.
- 이 컬럼을 통해 인덱스가 제 역할을 하고 있는지, 아니면 새로운 인덱스가 필요한지 판단할 수 있습니다.
3. Extra (성능 저하의 숨은 주범을 찾아라!)
- 의미: 쿼리 실행에 대한 추가적인 중요 정보를 제공합니다. 특히 성능에 부정적인 영향을 미칠 수 있는 단서들이 숨어 있습니다.
- DBA의 속마음: "
Using temporary?Using filesort? 이런 메시지가 보이면 성능 저하가 불 보듯 뻔한데... DBA 미간에 주름 하나 추가요!" - 주요 경고 메시지:
Using temporary: 쿼리 결과를 처리하기 위해 내부 임시 테이블을 사용했다는 의미입니다. 복잡한GROUP BY나ORDER BY,UNION등에서 나타날 수 있으며, 디스크 I/O를 유발하여 성능을 저하시킬 수 있습니다.Using filesort: 정렬 작업을 위해 인덱스를 사용하지 못하고, 별도의 정렬 작업(메모리 또는 디스크 사용)을 수행했다는 의미입니다. 대량 데이터 정렬 시 심각한 성능 저하를 유발합니다.Using where:WHERE절 조건으로 행을 필터링했다는 의미인데, 인덱스를 사용하여 효율적으로 필터링한 경우(Index Condition Pushdown 등)와 그렇지 않은 경우를 구분해야 합니다.type컬럼과 함께 봐야 합니다.
- 체크포인트: 이 컬럼에 나타나는 메시지들은 쿼리 최적화의 중요한 실마리가 됩니다.
4. type (풀스캔 경고! 최악의 시나리오를 피하라!)
- 의미: 데이터베이스가 테이블에 어떻게 접근했는지, 즉 조인 유형 또는 접근 방식을 보여주는 매우 중요한 컬럼입니다.
- DBA의 속마음: "
type이ALL이라고요? 이건 재앙인데... 당장 튜닝 대상 1순위입니다!" - 주요
type값 (성능 좋은 순서대로 나열, 아래로 갈수록 성능 저하 가능성):system: 테이블에 단 하나의 행만 존재 (매우 드묾).const:PRIMARY KEY나UNIQUE인덱스 조건으로 단 하나의 행만 정확히 찾는 경우.eq_ref: 조인 시PRIMARY KEY나UNIQUE인덱스를 사용하는 경우.ref:UNIQUE인덱스가 아닌 일반 인덱스를=조건으로 사용하는 경우.range: 인덱스를 사용하여 특정 범위의 행을 검색하는 경우 (예:BETWEEN,>,<).index: 인덱스 전체를 스캔하는 경우.ALL보다는 낫지만, 인덱스 크기가 크다면 여전히 많은 데이터를 읽을 수 있어 주의가 필요합니다. (예: 커버링 인덱스 상황에서 테이블 접근 없이 인덱스만 읽는 경우)ALL: 풀 테이블 스캔! 테이블의 모든 행을 처음부터 끝까지 읽는 최악의 접근 방식입니다. 인덱스를 전혀 사용하지 못하고 있다는 강력한 증거이며, 가능한 한 피해야 합니다.
- 체크포인트:
type컬럼의 값이ALL이나index(특히 인덱스가 매우 클 경우)라면, 인덱스 전략을 재검토하거나 쿼리를 수정하여 접근 방식을 개선해야 합니다.
느린 쿼리, 어떻게 찾아낼까? 데이터베이스별 확인 방법 총정리!
실행 계획을 볼 줄 알고 인덱스를 만들 줄 알아도, "어떤 쿼리가 문제인지"를 모르면 소용이 없겠죠? 지금부터 각 데이터베이스별로 성능 저하의 주범인 느린 쿼리(Slow Query)를 찾아내는 방법을 알아보겠습니다.
1. MySQL & MariaDB
- 핵심 기능: 느린 쿼리 로그 (Slow Query Log)
- 설정 방법:
- MySQL 설정 파일 (
my.cnf또는my.ini)에서slow_query_log = ON으로 활성화합니다. long_query_time = X(X는 초 단위, 예: 1 또는 0.5) 설정을 통해 X초보다 오래 실행된 쿼리를 로그 파일에 기록하도록 지정합니다. (기본값 10초는 너무 길 수 있음)log_slow_admin_statements = ON설정을 통해ALTER TABLE같은 관리자 구문도 느린 쿼리 로그에 포함시킬 수 있습니다.log_queries_not_using_indexes = ON(선택 사항): 인덱스를 사용하지 않는 쿼리도 기록할 수 있습니다.
- MySQL 설정 파일 (
- 확인 방법: 설정된 로그 파일(
slow_query_log_file경로 확인)을 직접 열어보거나,pt-query-digest같은 분석 도구를 활용합니다.
2. PostgreSQL
- 핵심 기능:
log_min_duration_statement설정 및pg_stat_statements확장 - 설정 방법 (
postgresql.conf):log_min_duration_statement = X(X는 밀리초 단위, 예: 5000으로 설정 시 5초 이상 걸리는 쿼리 로깅)0으로 설정하면 모든 쿼리를 로깅하고,-1(기본값)로 설정하면 로깅하지 않습니다.- 특정 데이터베이스나 사용자에 대해서만 이 설정을 적용할 수도 있습니다. (
ALTER DATABASE ... SET log_min_duration_statement = ...)
pg_stat_statements확장:CREATE EXTENSION pg_stat_statements;명령으로 활성화합니다. (설정 파일에서shared_preload_libraries에도 추가 필요)- 쿼리 실행 통계(총 실행 시간, 평균 실행 시간, 호출 횟수, I/O 정보 등)를
pg_stat_statements뷰를 통해 매우 상세하게 확인할 수 있어, 시스템 부하의 주범인 쿼리를 파악하는 데 매우 유용합니다.
3. MongoDB
- 핵심 기능: 내장 프로파일러(Profiler)
- 설정 방법:
db.setProfilingLevel(level, options)명령을 사용합니다.level 0: 프로파일링 비활성화 (기본값)level 1:slowms옵션보다 느린 작업 기록 (예:db.setProfilingLevel(1, { slowms: 100 })- 100ms 이상 걸린 작업 기록)level 2: 모든 작업 기록
operationProfiling.mode를slowOp로,operationProfiling.slowOpThresholdMs를 원하는 시간(밀리초)으로 설정 파일에서 지정할 수도 있습니다.
- 확인 방법:
system.profile컬렉션을 조회하거나,db.getProfilingStatus()로 현재 상태 확인,db.currentOp()로 현재 실행 중인 느린 작업 확인. Studio 3T, Compass 같은 GUI 도구를 사용하면 분석이 더 편리합니다.
4. SQLite
- 핵심 기능: 직접적인 느린 쿼리 로그 기능보다는 쿼리 실행 계획 분석 중심.
- 확인 방법:
- 쿼리 앞에
EXPLAIN QUERY PLAN키워드를 붙여 실행하면, SQLite가 해당 쿼리를 어떻게 처리하는지 그 계획을 보여줍니다. - 실행 계획 결과에서
SCAN TABLE <table_name>같은 부분이 보인다면, 해당 테이블에 대한 인덱스가 없어 전체 테이블을 스캔하고 있다는 신호입니다. 이런 부분을 찾아 인덱스를 추가하거나 쿼리를 최적화합니다.
- 쿼리 앞에
5. Redis
- 핵심 기능: 내장된 Slow Log 기능.
- 설정 방법 (redis.conf 또는
CONFIG SET명령어):slowlog-log-slower-than X: X 마이크로초(µs)보다 오래 실행된 명령어를 기록합니다. (기본값 10,000µs = 10ms).0으로 설정하면 모든 명령어를 기록합니다.slowlog-max-len Y: 저장할 느린 로그의 최대 개수를 지정합니다. (기본값 128개). 오래된 로그부터 삭제됩니다.
- 확인 방법:
SLOWLOG GET [count]명령어로 기록된 느린 명령어 목록을 확인할 수 있습니다. 각 로그에는 실행 시간, 명령어, 인자 등의 정보가 포함됩니다.
6. MSSQL (Microsoft SQL Server)
- 핵심 기능: SQL Profiler, 동적 관리 뷰(DMV), 실행 계획 분석, 확장 이벤트 등 다양.
- 확인 방법:
- SQL Server Profiler (GUI 도구): 실시간으로 발생하는 이벤트를 추적하고 특정 조건(예: 실행 시간 초과)에 맞는 쿼리를 필터링하여 확인할 수 있습니다. (최근에는 확장 이벤트(Extended Events) 사용 권장)
- 동적 관리 뷰 (DMVs):
sys.dm_exec_query_stats: 캐시된 쿼리 계획에 대한 누적 실행 통계(총 실행 시간, CPU 시간, 논리적 읽기/쓰기 등)를 제공하여 리소스 소모가 큰 쿼리를 식별할 수 있습니다.sys.dm_exec_requests: 현재 실행 중인 요청들의 상태, 대기 유형, 실행 시간 등을 확인할 수 있습니다.sys.dm_exec_procedure_stats,sys.dm_exec_trigger_stats등
- 실행 계획 분석 (SQL Server Management Studio - SSMS): SSMS에서 쿼리 실행 시 "실제 실행 계획 포함" 옵션을 선택하거나, 쿼리 창에서
Ctrl+M을 눌러 실행 계획을 시각적으로 분석할 수 있습니다. 어디가 비효율적인지, 어떤 인덱스가 필요한지 등을 파악하는 데 매우 유용합니다. - 느린 쿼리 로그 (Error Log 또는 확장 이벤트): 설정에 따라 특정 시간(기본 1초)을 초과하는 SQL 문을 SQL Server 오류 로그나 확장 이벤트 세션에 기록하도록 구성할 수 있습니다.
💡 Slow Query 관리, 언제 어떻게 할까?
느린 쿼리 관리는 "문제가 터진 후에" 하는 것보다 "틈틈이, 주기적으로" 하는 것이 훨씬 효과적입니다.
- 정기적인 모니터링: 주요 시스템의 느린 쿼리 로그나 실행 통계를 주기적으로 검토하여 잠재적인 성능 병목 지점을 미리 파악합니다.
- 알림 설정: 특정 임계치를 초과하는 느린 쿼리 발생 시 알림을 받도록 설정하여 신속하게 대응합니다.
- 코드 리뷰 시 실행 계획 확인: 새로운 기능 배포 전, 주요 쿼리에 대한 실행 계획을 확인하는 습관을 들이는 것이 좋습니다.
- 부하 테스트: 실제 운영 환경과 유사한 부하 테스트를 통해 예상치 못한 느린 쿼리를 발견하고 최적화합니다.
결론: 실행 계획은 성능 튜닝의 나침반! 🧭
DBA의 "인덱스 추가 요청"은 단순한 잔소리가 아니라, 시스템 성능과 안정성을 위한 중요한 신호입니다.
EXPLAIN을 통해 쿼리 실행 계획을 분석하고, 각 데이터베이스가 제공하는 느린 쿼리 확인 기능을 적극적으로 활용한다면, 우리는 더 이상 느린 쿼리 때문에 밤샘하는 악몽에서 벗어날 수 있을 것입니다.오늘 배운 내용들을 바탕으로 여러분의 애플리케이션 쿼리들을 한번 점검해 보세요. 생각지도 못한 곳에서 성능 개선의 실마리를 찾을 수 있을지도 모릅니다!
