쿼리 최적화란 무엇인가요?

쿼리 최적화는 데이터베이스가 쿼리를 가장 효율적으로 실행할 수 있는 방법을 결정하는 과정입니다.

사용자가 일반적으로 정형 쿼리 언어(SQL) 문으로 작성된 쿼리를 제출하면 데이터베이스는 요청된 데이터를 검색하는 여러 방법을 평가합니다. 이 의사결정 과정은 가장 효율적인 실행 전략을 선택하는 쿼리 옵티마이저라는 구성 요소에 의해 처리됩니다.

현대의 데이터베이스 관리 시스템(DBMS)은 가장 효율적인 옵션을 선택하기 전에 다양한 실행 전략의 비용을 추정하는 비용 기반 옵티마이저를 사용합니다. 이러한 과정 때문에 동일한 결과를 생성하는 두 데이터베이스 쿼리라도 실행 시간은 크게 달라질 수 있으며, 이는 일반적으로 밀리초 단위로 측정되고 쿼리 성능 및 응답 시간에 영향을 미칩니다.

쿼리 최적화 또는 SQL 쿼리 최적화는 개별 쿼리 성능 이상의 훨씬 광범위한 영역에 영향을 미칩니다. 이는 시스템의 확장성과 리소스 활용 방식을 개선함으로써 전체 데이터 시스템, 머신 러닝 모델 및 인공지능(AI) 이니셔티브의 효율성을 결정합니다.

애플리케이션은 정보를 빠르고 일관되게 검색하기 위해 데이터베이스에 의존합니다. 쿼리가 비효율적이면 데이터베이스는 테이블 스캔 수행, 레코드 정렬 또는 대규모 데이터 세트 조인에 불필요한 시간을 소비할 수 있습니다. 이러한 지연은 애플리케이션 프로그래밍 인터페이스(API) 및 분석 워크로드를 느리게 만들어 전반적인 사용자 경험을 저하시키는 병목 현상을 유발할 수 있습니다.

조직이 더 많은 데이터를 수집함에 따라 데이터베이스는 방대한 데이터 양, 다양한 데이터 유형 및 더욱 복잡한 쿼리 패턴으로 인해 점점 더 복잡해지는 워크로드를 지원해야 합니다.

전 세계 데이터스피어가 2028년까지 393.9제타바이트에 이를 것으로 예상되는 가운데, 과거 수천 개의 행을 처리하던 쿼리는 결국 수백만 또는 수십억 개의 행을 처리하게 될 수 있습니다. 쿼리 최적화는 데이터 양과 워크로드 복잡성이 증가하더라도 효율적인 쿼리를 가능하게 함으로써 확장성을 향상시킵니다.

효율적인 실행 계획은 또한 쿼리 처리에 필요한 리소스를 줄여줍니다. 모든 데이터베이스 작업은 데이터를 처리하기 위해 중앙 처리 장치(CPU) 사이클 및 디스크 입력/출력(I/O)을 포함한 시스템 리소스를 필요로 합니다.

최적화가 제대로 이루어지지 않은 쿼리는 리소스를 많이 소모하며 동일한 결과를 생성하기 위해 필요한 것보다 훨씬 더 많은 처리를 요구합니다. 이러한 리소스 소비 증가는 리소스 사용량이 비용에 직접 영향을 미치는 클라우드 환경에서 높은 비용으로 이어질 수 있습니다.

머신 러닝, 실시간 분석, 검색 증강 생성(RAG) 및 AI를 지원하는 현대적인 데이터 플랫폼은 대규모 데이터에 대한 빠르고 안정적인 액세스에 의존합니다. 쿼리 최적화는 이러한 시스템이 예산을 초과하지 않으면서도 실시간 의사결정을 지원할 수 있을 만큼 신속하게 관련 정보를 검색할 수 있도록 지원합니다.

데이터베이스 옵티마이저는 잠재적인 실행 전략을 평가할 때 여러 접근 방식을 사용할 수 있습니다. 초기 데이터베이스 시스템은 쿼리 구조를 기반으로 실행 계획을 결정하기 위해 사전 정의된 규칙을 적용하는 규칙 기반 최적화를 자주 사용했습니다.

현대의 DBMS는 일반적으로 여러 가능한 실행 전략을 평가하고 각 전략에 필요한 리소스를 추정하는 비용 기반 최적화를 우선적으로 사용합니다. 일부 시스템은 쿼리 계획을 단순화하고 최적화 오버헤드를 줄이기 위해 실용적인 지침을 적용하는 휴리스틱 기반 기법도 활용합니다.

사용되는 최적화 접근 방식과 관계없이 옵티마이저가 잠재적인 실행 전략을 평가하는 방식에는 다음과 같은 여러 기술 개념이 영향을 미칩니다.

• 쿼리 옵티마이저
• 데이터베이스 통계
• 카디널리티 추정
• 인덱스 및 액세스 경로
• 조인 알고리즘

쿼리 옵티마이저는 효율적인 실행 계획을 선택하는 역할을 하는 데이터베이스 구성 요소이며, 일반적으로 비용 기반 최적화 기법을 사용합니다. 관계형 데이터베이스에서 이 과정은 데이터베이스 엔진이 SQL 쿼리를 가장 효율적으로 실행하는 방법을 결정하도록 지원합니다.

비용 기반 옵티마이저는 고정된 규칙에 의존하는 대신 데이터 특성과 쿼리 구조를 분석해 가장 효율적인 접근 방식을 결정합니다. 이러한 유연성 덕분에 데이터베이스는 데이터 세트와 워크로드가 변화함에 따라 실행 전략을 조정할 수 있습니다.

옵티마이저는 다양한 실행 계획의 비용을 추정하기 위해 데이터베이스 통계 정보에 크게 의존합니다. 통계 정보는 저장된 데이터의 주요 특성을 설명하며, 여기에는 다음이 포함됩니다.

• 각 테이블의 행 수
• 열 내 값의 분포
• 인덱싱된 열의 선택도
• 테이블 간 관계
• 각 열의 데이터 유형

이러한 통계 정보는 옵티마이저가 쿼리가 반환할 행 수와 각 실행 전략에 필요한 작업량을 추정할 수 있도록 합니다. 통계 정보가 오래되었거나 부정확해지면 옵티마이저는 비효율적인 실행 계획을 선택할 수 있습니다.

카디널리티 추정은 쿼리의 각 단계에서 생성될 행 수를 예측하는 것을 의미합니다. 예를 들어 쿼리가 다음과 같은 WHERE 절을 사용해 행을 필터링하는 경우:

WHERE region = ‘North America’

옵티마이저는 해당 필터와 일치하는 레코드 수를 추정해야 합니다.

이러한 추정값은 여러 핵심 결정에 영향을 미칩니다. 옵티마이저는 이를 사용해 테이블 조인 순서, 가장 효율적인 조인 순서, 사용할 조인 알고리즘 또는 전체 테이블 스캔 대신 인덱스 스캔 사용 여부를 결정할 수 있습니다.

인덱스는 데이터베이스가 전체 테이블을 스캔하는 것보다 특정 데이터를 더 효율적으로 찾을 수 있도록 합니다. 옵티마이저는 데이터 검색에 필요한 작업량을 줄이기 위해 인덱스를 사용합니다.

일반적인 액세스 경로에는 테이블의 모든 행을 읽는 전체 테이블 스캔, 인덱스 구조를 통해 행을 읽는 인덱스 스캔, 인덱스 조회를 사용해 특정 행을 검색하는 인덱스 탐색, 그리고 기본 테이블에 액세스하지 않고 인덱스에서 직접 데이터를 검색하는 인덱스 전용 스캔이 포함됩니다.

올바른 액세스 경로를 선택하면 특히 대규모 테이블 작업 시 쿼리 실행에 필요한 작업량을 크게 줄일 수 있습니다.

많은 쿼리는 여러 테이블에서 데이터를 검색합니다. 이 경우 옵티마이저는 이러한 테이블을 어떻게 결합할지 결정해야 합니다. 일반적인 조인 알고리즘에는 다음이 포함됩니다.

• 중첩 루프 조인: 한 데이터 세트의 행을 다른 데이터 세트의 행과 순차적으로 비교합니다. 이 접근 방식은 한 테이블이 상대적으로 작거나 인덱스를 통해 내부 조인의 빠른 조회가 가능한 경우 효과적으로 작동할 수 있습니다.
  
  
• 해시 조인: 한 데이터 세트로부터 해시 테이블을 생성하고 이를 사용해 다른 데이터 세트의 행을 효율적으로 일치시킵니다. 이 전략은 대규모 데이터 세트에서 효과적으로 작동하는 경우가 많습니다.
  
  
• 병합 조인: 정렬된 두 데이터 세트의 행을 동시에 스캔하여 결합합니다.

옵티마이저는 데이터 크기, 사용 가능한 인덱스 및 예상 행 수와 같은 요소를 기반으로 이러한 알고리즘 중 하나를 선택합니다.

쿼리 최적화가 어떻게 작동하는지 이해하려면 SQL을 선언형 언어로 생각하는 것이 도움이 됩니다. 즉, SQL은 데이터를 어떻게 검색할지가 아니라 어떤 데이터를 검색해야 하는지를 설명합니다.

옵티마이저는 요청을 어떻게 수행할지, 그리고 가장 효율적인 방식으로 수행할지를 결정하는 역할을 담당합니다. 이를 위해 대부분의 데이터베이스는 다음과 같은 여러 최적화 단계를 따릅니다.

• 구문 분석 및 검증
• 쿼리 재작성
• 실행 계획 생성
• 플랜 비용 추정
• 실행 계획 선택

쿼리가 제출되면 데이터베이스는 먼저 SQL 문을 구문 분석하고 구문을 검증합니다. 이 단계에서 시스템은 참조된 테이블, 열 및 인덱스가 존재하는지, 그리고 쿼리 구조가 유효한지 확인합니다.

또한 데이터베이스 스키마 내 관련 객체를 사용할 수 있는지도 확인합니다. 이 단계는 데이터베이스가 최적화나 실행을 시도하기 전에 요청 내용을 이해하도록 보장합니다.

구문 분석 후 데이터베이스는 쿼리를 더 효율적으로 실행할 수 있는 동등한 형태로 재작성할 수 있습니다. 이러한 변환은 쿼리 결과를 유지하면서 실행 구조를 개선합니다. 일반적인 쿼리 재작성 기법에는 다음이 포함됩니다.

• 조건자 푸시다운: 쿼리 실행 초기에 필터를 적용해 이후 처리해야 하는 행 수를 줄이는 방식입니다.
  
  
• 서브쿼리 평탄화: 중첩된 쿼리를 더 효율적으로 실행할 수 있는 조인 형태로 변환하는 방식입니다.
  
  
• 조인 재정렬: 중간 결과를 줄이기 위해 테이블 결합 순서를 변경하는 방식입니다.
  
  
• 중복 작업 제거: 불필요한 정렬이나 중복 제거와 같은 작업을 제거하는 방식입니다.

이러한 변환을 통해 옵티마이저는 최종 결과를 변경하지 않고도 더 효율적인 실행 전략을 탐색할 수 있습니다. 또한 불필요한 데이터 처리를 줄이는 데 도움이 될 수 있습니다.

쿼리가 재작성되면 옵티마이저는 여러 잠재적인 실행 계획을 생성합니다. 각 계획은 요청된 데이터를 검색하기 위한 서로 다른 전략을 나타냅니다.

계획은 사용되는 인덱스, 테이블 조인 순서 또는 중간 결과 처리 방식에 따라 달라질 수 있습니다. 비교적 단순한 쿼리라도 여러 가능한 실행 전략을 생성할 수 있습니다.

예를 들어 지난주 주문을 조회하는 단일 쿼리에도 여러 선택지가 있습니다. 주문 테이블을 스캔한 뒤 이후에 행을 필터링할 수도 있고, 주문 날짜 인덱스를 사용해 최근 레코드를 빠르게 찾을 수도 있으며, 관련 고객 또는 제품 테이블을 조인하기 전에 먼저 데이터 세트 범위를 좁힐 수도 있습니다.

그런 다음 옵티마이저는 비용 모델을 사용해 각 후보 계획을 평가합니다. 비용 모델은 특정 계획을 실행하기 위해 데이터베이스가 수행해야 하는 작업량을 추정합니다. 이러한 추정에는 일반적으로 다음과 같은 요소가 고려됩니다.

• CPU 처리 요구 사항
• 데이터 검색에 필요한 디스크 I/O 작업
• 정렬 또는 해싱과 같은 작업에 필요한 메모리 사용량
• 분산 환경에서의 네트워크 전송

데이터베이스는 정확한 비용을 사전에 알 수 없기 때문에 데이터에 대해 저장된 통계 정보에 의존합니다. 이 정보는 옵티마이저가 예상 처리 시간을 추정하고 어떤 알고리즘 및 지원 데이터 구조가 가장 적합한지 결정하는 데 도움이 됩니다.

후보 계획을 평가한 후 옵티마이저는 예상 비용이 가장 낮은 계획을 선택합니다. 선택된 전략은 쿼리 실행 계획이 되며, 이는 데이터베이스가 쿼리를 실행할 때 수행하는 작업 순서를 설명합니다.

효율적인 실행 계획에는 일반적으로 테이블 스캔, 조인, 정렬 및 집계 작업(예: GROUP BY 또는 LEFT JOIN 사용)이 포함됩니다. 사용자는 EXPLAIN 계획을 검토해 옵티마이저가 요청된 데이터를 검색하기 위해 수행하는 단계를 확인할 수 있습니다.

현대 데이터베이스 옵티마이저가 매우 정교함에도 불구하고 쿼리 최적화를 어렵게 만드는 여러 요소가 존재합니다.

• 부정확한 통계 정보: 통계 정보가 오래되었거나 불완전하면 옵티마이저가 데이터 분포에 대해 잘못된 가정을 할 수 있습니다. 이는 필요 이상으로 많은 작업을 수행하는 비효율적인 실행 계획으로 이어질 수 있습니다.

• 데이터 왜곡: 불균형한 데이터 분포로 인해 옵티마이저가 쿼리가 반환할 행 수를 정확히 추정하기 어려울 수 있습니다. 특정 값이 다른 값보다 훨씬 자주 나타나는 경우 표준 추정 기법이 부정확한 예측을 생성할 수 있습니다.

• 복잡한 쿼리: 많은 조인, 중첩 작업 또는 서브쿼리를 포함하는 쿼리는 매우 많은 잠재적 실행 계획을 생성할 수 있습니다. 모든 가능한 계획을 평가하는 것은 현실적이지 않을 수 있으므로 옵티마이저는 휴리스틱과 근사치에 의존해야 할 수 있습니다. 이것은 시스템 규모가 커질수록 SQL 쿼리 최적화가 더욱 어려워지는 이유 중 하나입니다.

• 동적 데이터 환경: 데이터가 자주 변경되면 쿼리 동작 역시 시간이 지남에 따라 달라질 수 있습니다. 한때 효율적으로 작동했던 실행 계획도 데이터 분포가 변화함에 따라 효율성이 떨어질 수 있습니다.

쿼리 최적화는 자동으로 수행되지만 개발자, 관리자 및 데이터 엔지니어는 여러 최적화 기법을 통해 성능을 향상시킬 수 있습니다.

많은 데이터베이스 플랫폼은 또한 개발자와 관리자가 쿼리 성능을 분석하고 비효율적인 실행 계획을 식별할 수 있도록 지원하는 내장 툴을 제공합니다.

예를 들어 SQL Server Management Studio(SSMS)는 쿼리 성능 모니터링 및 병목 현상 식별을 지원하고, MySQL Workbench는 쿼리 계획 분석 및 실행 최적화 툴을 제공하며, Oracle SQL Tuning Advisor는 SQL 쿼리 개선을 위한 자동화된 권장 사항을 생성할 수 있습니다.

쿼리 최적화와 쿼리 튜닝은 밀접하게 관련되어 있지만 서로 다른 프로세스를 의미합니다.

쿼리 최적화는 데이터베이스가 효율적인 실행 전략을 결정하기 위해 사용하는 자동화된 프로세스를 의미합니다.

반면 쿼리 튜닝은 쿼리 성능 향상을 위한 수동 작업을 의미합니다. 이러한 작업에는 비효율적인 쿼리 재작성, 새로운 인덱스 생성, 통계 정보 업데이트 또는 데이터베이스 구성 설정 조정이 포함될 수 있습니다.

실제 환경에서는 쿼리 최적화와 쿼리 튜닝이 함께 작동하여 데이터베이스 성능을 향상시키는 경우가 많습니다. 이 둘은 함께 프로덕션 시스템에서 SQL 성능을 향상시키기 위한 실질적인 최적화 전략 세트를 구성합니다.

쿼리 최적화는 기존의 비용 기반 계획 방식을 넘어 발전하고 있습니다. 현대의 데이터베이스 시스템은 이제 자동화, 적응형 실행 및 인공지능을 도입해 쿼리 분석 및 실행 방식을 개선하고 있습니다.

새롭게 떠오르는 방향 중 하나는 시스템이 성능을 지속적으로 모니터링하고 문제에 자동으로 대응하는 자율형 데이터베이스 기능 개발입니다. 이러한 시스템은 사후 대응식 문제 해결에만 의존하는 대신 워크로드 동작, 쿼리 성능 및 시스템 신호를 분석하여 잠재적인 성능 문제를 조기에 식별하고 시정 조치를 권장합니다.

많은 자율형 데이터베이스 아키텍처는 이러한 기능을 세 가지 운영 영역으로 구성하며, 이는 주로 AI 에이전트에 의해 구동됩니다.

• 에이전틱 유지 관리는 패치 적용, 상태 점검 및 성능 최적화와 같은 일상적인 운영 작업을 자동화합니다.
  
  
• 에이전틱 복구는 사용자에게 영향을 미치기 전에 쿼리 성능 저하, 잠금 문제 또는 워크로드 병목 현상과 같은 이상 징후를 탐지하기 위해 시스템 동작을 지속적으로 분석합니다.
  
  
• 에이전틱 대응은 시스템에서 변경된 사항을 분석하고 문제 해결을 안내하는 컨텍스트 기반 분석 정보를 제공함으로써 팀이 사고를 더 빠르게 해결할 수 있도록 지원합니다.

이러한 에이전틱 기능은 human-in-the-loop 모델 내에서 작동하도록 설계되었으며, 여기서 자동화는 명확하게 정의된 운영 작업을 처리하고 데이터베이스 팀은 핵심 시스템에 대한 감독 권한을 유지합니다.

조직이 데이터 플랫폼을 계속 확장하고 AI 기반 애플리케이션을 도입함에 따라 스스로 모니터링, 최적화 및 유지 관리할 수 있는 시스템은 안정적인 데이터베이스 성능을 보장하는 데 점점 더 중요한 역할을 하게 될 것입니다.