MSSQL 쿼리 튜닝 4차 시도: 인덱스를 피했더니 읽기가 58배 줄었다

About this offer

지난번 3차 튜닝 끝에 "다음엔 검증 방법을 넓혀보겠다"고 적었다. 기회는 생각보다 빨리 왔다. 다만 이번엔 내가 찾은 문제가 아니었다. 유저마다 호출되는 프로시저의 조회 부분을 DBA분이 검토해주셨다. 돌아온 지시는 짧았다. 날짜 조건 컬럼에 CONCAT을 씌우라는 것. 인덱스를 일부러 못 타게 만들라는 뜻이었다. 1차 시도 때 인덱스를 타게 하려고 SARGable을 공부했다. 그런데 이번엔 인덱스를 타지 않게 하라고 한다. 지시대로 바꾸는 건 한 줄이면 끝난다. 하지만 이유를 모른 채 넘어가면 다음에 같은 상황에서 또 누군가에게 물어야 한다. 그래서 바꾸기 전과 후를 직접 측정해보기로 했다. 테이블·인덱스 이름은 실제와 다르게 바꿨다. 1. 바뀐 건 한 줄이었다 -- 변경 전 WHERE A.YMD LIKE @YM + '%' -- 변경 후 WHERE CONCAT(A.YMD, '') LIKE @YM + '%' 결과는 같다. 빈 문자열을 붙여도 값은 그대로니까. 달라지는 건 옵티마이저 눈에 보이는 모양이다. 컬럼이 함수 안에 들어가는 순간 인덱스 Seek 대상에서 빠진다. 평소엔 이게 실수다. 이번엔 의도였다. 2. 실행 계획에서 보인 것 변경 전 실행 계획에서 두 테이블이 같은 모양을 하고 있었다. 순서 연산자 하는 일 1 Index Seek (IX_CASH_KEY) 다른 조건으로 후보 행의 위치를 찾음 2 Constant Scan → Compute Scalar → Index Seek (IX_CASH_YMD) @YM + '%' 를 시작값·끝값 범위로 바꿔서 날짜 인덱스를 탐색 3 Hash Match (내부 조인) 1과 2의 결과를 행 위치 기준으로 교집합 4 RID Lookup (Heap) 교집합으로 남은 위치로 테이블에 가서 나머지 컬럼을 가져옴 인덱스 두 개를 따로 타고, 결과를 조인해서 교집합을 만든 다음, 테이블로 다시 간다. 이걸 인덱스 교집합(Index Intersection)이라고 부른다. 2번 앞의 Constant Scan과 Compute Scalar가 처음엔 낯설었다. LIKE 패턴이 리터럴이 아니라 변수나 파라미터일 때 생기는 모양이다. 실행 시점에 '202610%' 를 '202610' 이상 '202611' 미만 같은 범위로 계산해서 Seek에 넘겨준다. 3. 인덱스를 두 개 쓰는 게 왜 손해인가 이 테이블은 클러스터드 인덱스가 없는 힙(Heap)이다. 힙의 논클러스터드 인덱스에는 키 값과 함께 RID(Row Identifier) , 즉 행의 물리 주소(파일:페이지:슬롯)가 들어 있다. 인덱스 교집합의 비용은 이렇게 쌓인다. 인덱스 A Seek 인덱스 B Seek 두 결과를 RID로 해시 조인 (해시 테이블용 메모리 필요) 남은 RID마다 테이블 페이지로 랜덤 접근 조건 두 개를 한 번에 커버하는 복합 인덱스가 있었다면 Seek 한 번이면 됐을 일이다. 17편에서 정리했던 "단일 인덱스 여러 개는 복합 인덱스 하나가 아니다" 가 그대로 다시 나왔다. 그때는 DELETE의 락 문제로 만났고, 이번엔 조회 계획으로 만났다. 같은 구조적 결함이 작업 종류만 바꿔 다시 튀어나온 셈이다. CONCAT을 씌우면 날짜 인덱스가 후보에서 빠진다. 남은 인덱스 하나로 찾고, 날짜 조건은 읽은 행에서 걸러낸다. 2번과 3번 단계가 통째로 사라진다. 4. 옵티마이저는 왜 그 계획을 골랐을까 여기서 질문이 하나 남는다. 교집합이 손해라면 옵티마이저는 왜 그걸 골랐을까. 옵티마이저는 테이블을 직접 세보지 않는다. 통계(Statistics) 라는 요약 정보를 보고 "이 조건이면 몇 건 나오겠다"를 추정하고, 그 추정으로 비용을 계산한다. 구성 내용 헤더 마지막 갱신일, 그때의 전체 행 수, 샘플링 행 수 밀도(Density) 1 ÷ 고유값 개수. 값을 모를 때 평균치로 추정할 때 씀 히스토그램(Histogram) 값 분포를 최대 200개 구간으로 나눠 건수를 적어둔 표 변경을 지시하면서 DBA분이 남긴 설명은 이랬다. "월초라 10월 데이터가 적다고 판단해서 날짜 인덱스를 탄다." 처음엔 이 말이 이해가 안 됐다. 데이터가 적으면 빨라야 정상 아닌가. 핵심은 실제로 적은 게 아니라, 적다고 잘못 추정한다 는 데 있었다. 가능한 경로는 두 가지다. (1) 통계가 아직 10월을 모르는 경우 인덱스는 INSERT와 같은 트랜잭션 안에서 즉시 갱신된다. 통계는 다르다. 변경 건수가 임계치를 넘고, 그 뒤 쿼리가 컴파일될 때 갱신된다. 기준 자동 갱신 임계치 호환성 수준 130 미만 500 + 전체 행의 20% 호환성 수준 130 이상 위 값과 √(1000 × 전체 행) 중 작은 값 대용량 테이블은 이 임계치가 크다. 게다가 날짜 컬럼은 새 값이 전부 히스토그램 마지막 구간 바깥 에 쌓인다. 통계상 세상이 9월 30일에서 끝나 있으면, 10월 범위는 "거의 없음"으로 추정된다. 이걸 증가 키 문제(Ascending Key Problem)라고 부른다. (2) 값을 모른 채 계획을 만든 경우 LIKE 패턴이 변수나 파라미터라는 걸 2번에서 확인했다. 로컬 변수면 옵티마이저는 컴파일 시점에 값을 몰라 히스토그램 대신 고정 추정치를 쓴다. 프로시저 파라미터면 첫 실행 값으로 만든 계획을 재사용한다(파라미터 스니핑). 두 경로 모두 결론은 같다. 적은 건수를 전제로 고른 계획이, 실제 건수 앞에서 비싸졌다. 어느 쪽이 실제 원인인지는 아직 확정하지 못했다. 결과부터 확인하고 돌아오기로 했다. 5. 결과 변경 전후로 같은 조건을 두 번씩 실행하고 두 번째 결과를 기준으로 비교했다. SET STATISTICS IO, TIME ON 으로 측정했다. 지표 변경 전 변경 후 변화 논리적 읽기 5,834 페이지 100 페이지 98.3% 감소 (약 58배) 읽은 데이터량 (페이지 × 8KB) 약 45.6MB 약 0.8MB 98.3% 감소 CPU 시간 234ms 16ms 93.2% 감소 (약 14.6배) 경과 시간 232ms 18ms 92.2% 감소 (약 12.9배) 결과 행 수 1행 1행 동일 같은 1행을 얻기 위해 읽던 페이지가 5,834개에서 100개로 줄었다. 한 번 호출 기준으론 200ms 남짓 차이다. 체감하기 어렵다. 그런데 이 조회는 유저마다 호출되는 프로시저 안에 있다. 호출 횟수만큼 곱해지는 비용이라 건당 수치보다 누적으로 봐야 했다. 변경 전 실행에는 미리 읽기(read-ahead)가 178 페이지 있었다. 디스크에서 페이지를 가져온 시간이 경과 시간에 일부 섞였다는 뜻이다. 그래서 시간보다 캐시 상태와 무관한 논리적 읽기를 주 지표로 삼았다. 3차 때 CPU와 reads가 따로 논다는 걸 겪고 나서 생긴 습관인데, 이번엔 둘 다 같은 방향으로 줄었다. 접근 경로 자체가 바뀌었으니 당연한 결과였다. 6. CONCAT은 정답인가 빠르고 간단하다. DDL도 필요 없다. 오라클에서 YMD || '' 로 인덱스를 막던 오래된 방식과 같은 원리다. 다른 선택지와 나란히 놓아봤다. 방법 장점 한계 CONCAT으로 인덱스 회피 즉시 적용, 스키마 변경 없음 의도가 코드에 안 보임. 날짜 인덱스가 유리한 경우에도 영구히 막힘 통계 갱신 추정 자체를 바로잡음 날짜 컬럼은 갱신 직후부터 다시 새 값이 쌓임. 재발함 인덱스 힌트 의도가 명확함 데이터 분포가 바뀌어도 계획이 고정됨 복합(커버링) 인덱스 교집합과 RID Lookup을 근본적으로 없앰 쓰기 비용 증가. 14편에서 계산했던 그 트레이드오프 CONCAT의 가장 큰 위험은 다음 사람이 이걸 실수로 보고 지울 수 있다 는 점이다. 1차 시도 때의 나라면 "함수를 걷어내서 SARGable하게"라며 지웠을 것이다. 적어도 주석 한 줄은 있어야 한다. -- 날짜 인덱스 교집합 방지용. 월초 추정 오류로 Hash Match + RID Lookup 발생 WHERE CONCAT(A.YMD, '') LIKE @YM + '%' 7. 아직 확인하지 못한 것 숫자로 결과는 확인했지만, 원인은 아직 반만 닫혔다. 날짜 인덱스 Seek의 예상 행 수와 실제 행 수. 차이가 크면 추정 오류가 원인이라는 직접 증거가 된다. @YM이 로컬 변수인지 파라미터인지. 4번의 두 경로 중 어느 쪽인지가 여기서 갈린다. 통계의 마지막 갱신일과 그 이후 변경 건수. sys.dm_db_stats_properties 로 볼 수 있다. DBA분이 통계 갱신이나 복합 인덱스 대신 CONCAT을 고른 이유. 운영 관점의 판단 기준이라 가장 궁금한 부분이다. 1차 때는 인덱스를 타게 만들려고 애썼고, 2차 때는 탈 인덱스가 없다는 걸 알았고, 3차 때는 측정 지표를 잘못 골랐다는 걸 알았다. 이번엔 인덱스를 타는 게 손해인 경우 를 만났다. 네 번을 거치면서 기준이 한 방향으로만 가지 않는다는 걸 알게 됐다. SARGable하게 쓰는 건 원칙이지 목표가 아니었다. 목표는 읽는 양을 줄이는 거고, 인덱스는 그 수단 중 하나일 뿐이다. 수단이 둘로 쪼개져 교집합을 만들어야 하는 상황이면, 아예 안 쓰는 쪽이 싸다. 옵티마이저가 실제 건수가 아니라 추정 건수로 계획을 고른다는 것도 이번에 와닿았다. 인덱스는 즉시 갱신되는데 통계는 나중에 갱신되니, 날짜처럼 계속 증가하는 컬럼에서는 그 틈이 구조적으로 생긴다. 월초에 이 문제가 나온 게 우연이 아니었다. 그리고 하나 더. 지시로 받은 답이었지만 숫자로 다시 확인하고 나니 다음에 비슷한 계획을 봤을 때 혼자 판단할 수 있을 것 같다. 받은 답과 아는 답은 다르다. 다음 편에서는 7번의 빈칸을 채워본다. 예상 행 수와 실제 행 수를 나란히 놓고, 이번 계획을 만든 게 통계였는지 파라미터였는지 가려볼 생각이다. 빌링 도메인 백엔드 개발 공부 기록