AI 요약
측정 데이터 목록 조회에서 최신 20건만 필요함에도 조인 순서 때문에 수집 로그 테이블에서 불필요한 읽기가 많이 발생하고 있었다는 문제를 STATISTICS IO/TIME으로 확인했습니다. 인덱스를 넣어 측정 테이블 읽기를 줄이고, 결국 먼저 20건을 뽑아서 그 결과만 로그와 대상자 테이블에 조인하는 구조로 바꿔 전체 logical reads를 약 98% 줄였습니다.
프로젝트에 시간이 지날수록 데이터가 계속 누적되는 테이블들이 있었다.
현재 데이터 규모에서는 크게 문제가 되지는 않았지만, 운영 기간이 길어질수록 DB 읽기 비용이 증가할 수 있겠다고 생각했다.
그래서 SQL Server의 STATISTICS IO/TIME을 활용해서 테이블을 점검하고, 인덱스와 쿼리를 개선해보기로 했다.
개선 대상으로 잡은 기능은 측정 데이터 목록 조회였다.
이 화면은 최신 측정 데이터 20건만 조회한다. 겉으로 보면 단순한 목록 조회처럼 보이지만, 내부적으로는 측정 데이터 테이블, 수집 로그 테이블, 대상자 테이블을 조인하고 있었다.
기존 흐름은 대략 다음과 같았다.
최종 결과는 20건이지만 DB는 20건을 고르기 전에 여러 테이블을 먼저 조인하고 정렬해야 했다.
먼저 추측으로 개선하지 않고 현재 쿼리의 실행 비용부터 확인했다.
SQL Server에서 아래 옵션을 켜고 기존 쿼리를 실행했다.
01SET STATISTICS IO ON;02SET STATISTICS TIME ON;개선 전 측정 결과는 다음과 같았다.
| 항목 | Logical Reads |
|---|---|
| 수집 로그 테이블 | 2737 |
| 측정 데이터 테이블 | 172 |
| 전체 | 2911 |
실행 시간은 다음과 같았다.
| 항목 | 값 |
|---|---|
| CPU 시간 | 16ms |
| 경과 시간 | 4ms |
현재 데이터가 많지 않아 실행 시간 자체는 느리지 않았다. 하지만 logical reads를 보면 수집 로그 테이블에서 읽기 비용이 크게 발생하고 있었다.
처음에는 측정 데이터 테이블이 병목일 것이라고 예상했다. 측정 데이터는 시간이 지날수록 계속 쌓이는 테이블이기 때문이다.
그런데 실제 STATISTICS IO 결과는 예상과 달랐다.
01측정 데이터 테이블 logical reads: 17202수집 로그 테이블 logical reads: 2737측정 테이블보다 조인 대상인 수집 로그 테이블의 읽기 비용이 훨씬 컸다.
즉, 문제는 단순히 “측정 테이블이 크다”가 아니었다.
기존 쿼리 구조에서는 최신 20건만 필요함에도, 20건을 선별하기 전에 로그 테이블과 먼저 조인하면서 불필요한 읽기가 발생하고 있었다.
다음과 같은 인덱스를 추가했다.
01create index idx_measure_type02on measure_data (03 measure_type,04 id desc05 )06include (07 log_id,08 subject_id,09 measure_value,10 measure_date,11 measure_time,12 regist_date13 );여기서 핵심은 두 가지다.
measure_type으로 필요한 측정 유형만 빠르게 찾는다.
id desc로 최신순 정렬 비용을 줄인다.
또한 조회 결과와 조인에 필요한 컬럼을 include에 넣어서 인덱스만으로 필요한 데이터를 최대한 가져올 수 있도록 했다.
1차 개선 후 결과는 다음과 같았다.
약 91% 감소했다.
하지만 여전히 수집 로그 테이블에서 대부분의 읽기 비용이 발생하고 있었다.
새로 추가한 인덱스가 측정 데이터 테이블에만 적용되었고 기존 쿼리 구조상 20건을 가져오기 전에 로그 테이블과 먼저 조인하고 있었기 때문이다.
따라서 전체 비용을 줄이기 위해서는 로그 테이블에 인덱스를 추가하는 것보다, 먼저 조인 범위를 줄이는 것이 필요하다고 판단했다.
개선 방향은 다음과 같다.
쿼리는 대략 다음과 같은 형태로 바꿨다.
01WITH measured_page AS (02 SELECT03 ROW_NUMBER() OVER (ORDER BY m.id DESC) AS page_row,04 m.id,05 m.log_id,06 m.subject_id,07 m.measured_value,08 m.measured_date,09 m.measured_time,10 m.created_at11 FROM measure_data m12 WHERE m.measure_type = ?13)14SELECT15 p.id,16 p.subject_id,17 p.measured_value,18 p.measured_date,19 p.measured_time,20 p.created_at,21 l.device_number22FROM measured_page p23INNER JOIN collection_log l24 ON l.log_id = p.log_id25INNER JOIN subject_master s26 ON s.subject_id = p.subject_id27WHERE p.page_row BETWEEN 1 AND 2028ORDER BY p.page_row ASC;핵심은 “필요한 20건을 먼저 고른다”는 점이다.
그 후에는 20건에 대해서만 로그 테이블과 대상자 테이블을 조인한다.
2차 개선 후 결과는 다음과 같았다.
| 대상 | 개선 전 | 인덱스 적용 후 | 쿼리 구조 개선 후 |
|---|---|---|---|
| 수집 로그 테이블 logical reads | 2737 | 2737 | 40 |
| 측정 데이터 테이블 logical reads | 172 | 15 | 15 |
약 98% 감소했다.
최종 결과는 다음과 같다.
| 항목 | 개선 전 | 개선 후 |
|---|---|---|
| 전체 logical reads | 2911 | 57 |
| 수집 로그 테이블 | 2737 | 40 |
| 측정 데이터 테이블 | 172 | 15 |
| CPU 시간 | 16ms | 0ms |
| 경과 시간 | 4ms | 2ms |
현재 데이터 규모가 크지 않아 경과 시간 차이는 크지 않았다.
하지만 logical reads가 크게 줄었다는 점이 중요하다.
logical reads는 DB가 메모리에서 읽은 페이지 수를 의미한다. 데이터가 커질수록 이 수치 차이는 실제 응답 시간과 DB 부하 차이로 이어질 가능성이 높다.
이번 작업은 단순히 현재 쿼리 시간을 줄이는 것이 아니라, 데이터가 누적될수록 커질 수 있는 조회 비용을 사전에 줄이는 작업이었다.
처음에는 측정 데이터 테이블이 병목일 것이라고 예상했다. 하지만 실제 STATISTICS IO를 확인해보니 가장 큰 비용은 조인 대상인 수집 로그 테이블에서 발생하고 있었다.
인덱스 추가도 중요하지만, 인덱스만으로 모든 문제가 해결되지는 않았다. 실제로 복합 인덱스를 추가했을 때 측정 테이블의 읽기 비용은 크게 줄었지만, 전체 비용은 여전히 로그 테이블 조인에서 발생했다.
결국 효과가 컸던 개선은 쿼리 구조를 바꾸는 것이었다.
필요한 데이터를 먼저 줄이고, 그 결과만 조인한다.
이 단순한 원칙만으로 전체 logical reads를 약 98% 줄일 수 있었다.
앞으로도 먼저 IO 지표를 확인하고, 병목이 실제로 어디에서 발생하는지 확인한 뒤 개선 방향을 정해야겠다고 느꼈다.