데이터베이스 쿼리 동작 분석

Estimated Scan Times (Assuming No Index)

Number of Rows	Estimated Time (Disk-Based Query)
1,000 rows	~1 ms - 10 ms
10,000 rows (efficient enough)	~10 ms - 100 ms
100,000 rows (warning zone)	~100 ms - 500 ms
1,000,000 rows (critical bad performance)	~1 sec - 5 sec
10,000,000+ rows	5 sec - 20 sec+ (severe slowdown)

 

SQL 해석순서

• SQL logical query processing order
  • FROM – The tables are determined.
  • JOIN (if any) – The tables are joined together.
    • If common columns for join  are not indexed, it performs a full scan on one or both tables.
  • WHERE – The filtering of rows occurs.
    • If multiple conditions it includes only the rows that satisfy the condition in the result set.
    • order of  conditions in where clause, does not impact efficiency because of  query optimizer rearrangement  but best to set indexed column first
  • GROUP BY – The rows are grouped.
  • HAVING – Filters groups (after aggregation).
  • SELECT – The columns to return are determined.
  • ORDER BY – The result is sorted.
    • data is sorted before offset
  • LIMIT/OFFSET – The number of rows to return is limited

 

하지만 논리적 실행 순서(SQL의 처리 순서)는 이렇게 됩니다:

단계처리 내용

1	FROM + JOIN 으로 모든 행 조인
2	ON 조건 (INNER JOIN 일 때) 로 조인 결과 필터
3	WHERE 절로 조건 필터
4	GROUP BY
5	HAVING
6	SELECT 필드 산출
7	ORDER BY
8	LIMIT

 

즉 JOIN을 먼저 해서 데이터 집합을 만든 뒤,
그 집합에 WHERE 필터를 적용합니다.

 

실행계획(Execution Plan)이란?

•  
•  
• SQL 문법 순서와는 다르게, DBMS 옵티마이저가
• 테이블, 인덱스, 통계정보를 기반으로
• 가장 효율적인 순서(physical order)로 scan & join 하도록 plan(계획)을 만드는 것

 

실제 DBMS 물리적 실행 순서 (Physical Execution Plan)

그러나 DBMS 가 실제 디스크에서 읽고, join 하고, output 을 만드는 순서 는
위 순서를 그대로 따르지 않고, 옵티마이저가 가장 효율적인 방법을 계산해 실행합니다.

그래서 실제로는:

(1) 통계 + 인덱스 보고 어떤 테이블을 먼저 읽을지 결정
(2) WHERE 조건을 기반으로 candidate rows 를 먼저 읽음 (필터링)
(3) 그 rows 를 기반으로 JOIN 수행 (보통 Nested Loop or Hash Join)
(4) 필요시 GROUP BY, HAVING 수행
(5) SELECT 컬럼을 뽑고
(6) ORDER BY + LIMIT

 

 

즉 현실적으로는

• WHERE 조건이 적용된 테이블에서 먼저 rows 를 확보 → join → 그 다음 SELECT / ORDER BY
  하는 게 거의 대부분의 케이스.

 

 

 

 

 

슬로우 쿼리

• “DB에서 실행 시간이 오래 걸리는 쿼리”를 통칭하는 용어. 오래의 기준은 상대적이기 때문에, DBMS에서 정의된 기준이 필요.
• slow_query_log가 켜져 있고, long_query_time 초 이상 걸린 SELECT 쿼리가 자동으로 로그에 기록

SET GLOBAL slow_query_log = 1;

SET GLOBAL long_query_time = 2;

 

슬로우쿼리분석

• EXPLAIN으로 실행 계획 분석 : 쿼리 구조가 비효율적인지 확인 ; 인덱스 사용, 조인 방식, 정렬 등
• performance_schema : 전체 DB 쿼리 성능 추적 ; 최근 수백 개 쿼리에 대한 시간, CPU, IO, 정렬, 잠금 상태

 

Explain

• EXPLAIN은 SELECT 쿼리의 실행 계획을 미리 보여주는 명령어. 쿼리를 실행하지 않고, "어떤 방식으로 데이터를 읽고 처리할 것인지"를 보여줌.
• 어떤 테이블을 먼저 읽을지
• 어떤 인덱스를 쓸지
• join은 어떤 방식(Nested Loop, Hash Join)으로 할지
• 몇 row 정도 읽을지
  를 계산해 보여주는 보고서 입니다.

EXPLAIN 결과 해석법 요약표

컬럼	의미	주의 깊게 봐야 할 값
id	실행 단계 (JOIN 단계 수)	1 이상이면 중첩 쿼리
select_type	쿼리 유형 (PRIMARY, DERIVED, etc.)	SUBQUERY, DERIVED이 성능 영향 높음
table	액세스되는 테이블 이름	-
type	조인 방식 (접근 방식)	ALL이면 Full Table Scan으로 성능 위험
possible_keys	사용할 수 있는 인덱스 후보	비어 있으면 인덱스 미사용
key	실제 사용된 인덱스	NULL이면 인덱스 미사용
key_len	사용된 인덱스 길이 (Byte)	-
ref	조인 시 비교 기준	ex) const, func, table.column
rows	예측 읽기 건수	많을수록 느려질 가능성 있음
Extra	추가 정보	Using filesort, Using temporary 있으면 성능 경고

 

• index를 타고 있는지?
• join 순서가 잘못되어서 큰 테이블을 먼저 돌고 있는 건 아닌지?
• rows 예상 개수가 너무 큰 건 아닌지?

 

• index를 제대로 타는지 (key 칸)
• 풀스캔이 아닌지 (type이 ALL 이면 위험)
• join 순서가 의도대로인지
• 예상 row (rows × filtered) 가 너무 큰지
• join 조건에서 Using where or Using join buffer 같은 부작용은 없는지
•  

이걸 해석하면

• table: 어떤 테이블을 지금 단계에서 읽고 있는지
• type: 어떻게 읽는지 (ALL=풀스캔, index, range=범위스캔, ref=키 기반 join)
• key: 어떤 인덱스를 쓰고 있는지
• rows: 몇 row 정도 읽을 것 같다고 예측하는지
• filtered: WHERE 조건에서 몇 % 정도가 살아남을지
• Extra: join 방식, temp table 여부 등

 

 

대표적인 JOIN 알고리즘

 

이름	특징
Nested Loop Join	작은 테이블을 기준으로 루프 돌면서, 필요한 값이 큰 테이블에서 인덱스를 이용해 바로 찾아옴 (Index Nested Loop Join).
Hash Join	작은 테이블을 해시 테이블로 만든 뒤, 큰 테이블에서 스캔하면서 키가 일치하면 join.
Merge Join	두 테이블 모두 정렬된 상태에서 병합 스캔(merge scan) 하면서 join.

 

 

디스크 I/O는 ‘읽어야할 row 개수’보다 ‘접근 패턴’이 중요

 

디스크는 블록 단위로 읽는다

 

• 디스크에서 데이터를 읽을 때는 row 단위가 아니라 보통 4KB, 8KB 블록 단위로 읽습니다.
• 하나의 블록에는 수십~수백 개의 row가 들어있습니다.
• 즉, 100만 row를 Full Scan한다 해도, 디스크는 수천 개의 블록만 읽으면 충분합니다.

 

 

 

Full Scan

 

• 순차 I/O (Sequential I/O): 디스크가 물리적으로 인접한 블록을 한 번에 쭉 읽는 방식
  • 하드디스크의 경우, 디스크 헤드가 거의 움직이지 않음 (빠름)
  • SSD도 내부 채널 병렬성 활용이 가능 (빠름)
  • OS나 DB가 미리 예측해서 prefetch → 성능 극대화
• Full Scan은 하드디스크나 SSD가 물리적으로 정렬된 데이터를 블록 단위로 연속해서 읽기 때문에 OS나 DB가 prefetch도 할 수 있고, 버스·디스크 캐시 활용률도 높습니다.

 

랜덤 I/O

 

• 랜덤 I/O (Random I/O): 비연속적인 블록을 이리저리 점프하며 읽는 방식으로  ‘이동 비용’이 큼
  • 하드디스크(HDD)에서는 디스크 헤드가 물리적으로 이동해야 해서 매우 느림 (ms 단위 I/O)
  • SSD도 내부 주소 매핑 & 제어기 비용이 있어 HDD보다 빠르지만, 순차 I/O보다 여전히 느림
  • OS도 미리 예측하기 어려워 prefetch 불가 → 효율 ↓

•  예시: 인덱스를 따라 각 row의 주소로 접근 → 이 row는 여기, 다음 row는 다른 블록 → 디스크가 매번 점프

 

 

랜덤 I/O는 ‘블록 재사용이 안 되는’ 단점이 있음

• 인덱스를 타면 DB는 특정 조건의 row를 가진 주소(pointer) 를 찾습니다.
• 이 주소를 따라가면 row가 있는 블록을 읽어야 하는데,
• 이 블록이 이전에 안 읽었던 것이면 디스크에서 새로 읽어야 하며,
• 위치가 다 제각각이면, 계속 디스크를 건너뛰며 접근하게 되어 → 랜덤 I/O 발생

➡️ 랜덤 I/O는 블록을 매번 새로 디스크에서 찾아야 해서 비싸다

구분	특징	예시	속도
순차 I/O	연속된 블록을 한 번에 읽음	책 한 권을 쭉 읽기	빠름
랜덤 I/O	서로 다른 위치를 뛰어다님	책 여러 권의 특정 페이지마다 찾기	느림

 

그러므로 인덱스를 써도 항상 빠르진 않다

왜냐하면?

• 인덱스 자체는 “주소 목록”일 뿐입니다.
• 인덱스를 타면 → 인덱스 → 실제 row 주소로 가서 → row를 메모리에 로딩해야 합니다.
• 이때 row들이 서로 멀리 떨어져 있으면 랜덤 I/O가 발생합니다.

상황	인덱스 성능	이유
인덱스 조건이 아주 희귀할 때	👍 빠름	소수의 row만 가져오니 랜덤 I/O 부담도 적음
조건이 널널해서 row를 수천~수만 건 가져올 때	👎 느릴 수 있음	랜덤 I/O가 폭발적으로 늘어남
조건 없이 전체 스캔할 때	🔁 Full Scan이 오히려 더 빠름	순차 I/O + prefetch + 블록 단위 버퍼링 활용 가능

 

 

예시 시나리오

• 총 6억 row (예: 로그 테이블)

SELECT * FROM logs
WHERE camera_no = 123;

 

 

조건	필터 결과	인덱스 vs Full Scan 예상
camera_no = 1	2만 건 (0.003%)	✅ 인덱스 탐색이 훨씬 빠름
camera_no = 123	200만 건 (0.33%)	✅ 아직 인덱스가 빠름
camera_no = 123 AND yyyymmdd = '20250616'	60,000건 (0.01%)	✅ 인덱스 + 조건 필터 유리
camera_no = 1 OR status = 'ALERT'	3천만 건 (5%)	⚠️ 경계, 옵티마이저 판단 따라 다름
camera_no IN (1,2,3,...,100)	2억 건 (33%)	❌ Full Scan이 훨씬 유리함

 

실제 DB 엔진은 어떻게 하냐?

DB는 **통계 정보(통계 힌트, 히스토그램)**를 기반으로 다음을 판단합니다:  "이 조건이면 인덱스 타는 게 빠를까? Full Scan이 빠를까?” 이걸 **쿼리 옵티마이저(Query Optimizer)**가 결정합니다. 그래서 때로는 인덱스를 무시하고 Full Scan을 택하기도 합니다.

 

실무에서의 판단 방법

1. 실행계획 (EXPLAIN or EXPLAIN ANALYZE)
   → 실제 인덱스를 쓰는지, Full Scan인지, 예상 row 수는 얼마인지 확인
   → MySQL/MariaDB: EXPLAIN SELECT ...
   → PostgreSQL: EXPLAIN ANALYZE SELECT ...
2. 데이터 분포를 파악하자 (ANALYZE, 통계 정보)
   • 특정 칼럼의 distinct 개수 (선택도)
   • 특정 조건에서 평균적으로 몇 건이 걸리는지
3. 테스트 환경에서 쿼리 벤치마크
   → 1천 건, 10만 건, 100만 건 등으로 증가시켜 성능 추이 관찰
4. 옵티마이저 힌트 (힌트나 FORCE INDEX)로 테스트 가능
   → 인덱스를 강제로 사용하거나 무시하고 성능 비교