If you're seeing this message, it means we're having trouble loading external resources on our website.

웹 필터가 올바르게 작동하지 않으면 도메인 *. kastatic.org*.kasandbox.org이 차단되어 있는지 확인하세요.

주요 내용

쿼리 계획과 최적화를 이용한 더 효율적인 SQL

이제 여러분은 데이터를 선택하는 여러 가지 방법을 배웠고, SELECT를 통해 여러 개의 테이블에서 사용하는 방법을 배우려고 합니다. 지금이 여러분의 SQL 쿼리의 효율에 대해서 이야기할 시간인 것 같습니다. SQL 쿼리는 얼마나 빨리 실행되고 어떻게 더 빠르게 만들 수 있을까요?
SQL은 선언형 언어입니다. 각 쿼리는 SQL 엔진에게 우리가 무엇 을 원하는지 선언하지만 어떻게 하는지를 알려주지는 않기 때문입니다. 따라서, 어떻게 동작하는지 정의하는 "플랜(plan)" 은 쿼리의 효율성에 꽤나 중요한 영향을 미칩니다.

SQL 쿼리는 왜 플랜이 필요한가요?

예를 들어, 이런 간단한 쿼리를 생각해보세요.
SELECT * FROM books WHERE author = "J K Rowling";
해당 쿼리에 대해 SQL이 결과를 찾기 위해 다음 두 가지 방법이 있습니다.
  • "풀 테이블 스캔(full table scan)". 테이블의 모든 열을 보고 일치하는 열을 리턴합니다.
  • "인덱스(index)" 생성. 테이블을 복사하여 작가이름 기준으로 정렬하고, 바이너리 서치를 통해 작가가 "J K Rowling"인 행을 찾습니다. 그다음 일치하는 ID를 찾고, 원본 테이블에서 바이너리 서치를 통해 ID가 일치하는 열을 반환합니다.
어느 쪽이 더 빠를까요? 속도는 데이터의 양과 쿼리의 실행 횟수에 달려있습니다. 만약 테이블의 열이 10개뿐이라면, 풀 테이블 스캔을 하기 위해 딱 10개의 열만 찾아보면 되므로 이러한 경우에는 첫 번째 플랜이 적당할 것입니다.
만약 테이블의 열이 천만 개라면, 풀 테이블 스캔을 하기 위해서는 천만 개의 열을 찾아봐야 합니다. 이때 정렬된 테이블에서 바이너리 서치를 사용한다면 더 빠를 것입니다. 천만 개의 열에서 하나의 값을 찾기 위해 오직 23번만 확인하면 됩니다. 하지만 정렬된 테이블을 생성하는 것은 시간이 꽤 걸립니다 (엔진에 따라서 약 2억 3천만 번의 연산이 필요합니다). 만약 우리가 해당 쿼리를 자주 실행하거나 (23번 이상) 이미 생성된 테이블이 존재한다면 두 번째 플랜을 사용하는 것이 더 좋을 것입니다.
SQL 엔진은 어떤 플랜을 사용할지 어떻게 결정할까요? 이것은 중요한 단계이지만, 우리는 아직까지 쿼리의 구현보다는 쿼리의 구문에 초점을 두었기 때문에 아직 다루지 않았습니다. 우리가 더 큰 데이터베이스에서 고급 SQL을 접하게 된다면, 쿼리 플래닝의 중요성은 급상승합니다.

SQL 쿼리의 주기

우리가 하나의 쿼리를 실행할 때마다 SQL 엔진은 다음과 같은 단계를 밟아갑니다.
파스/구문 분석(parse), 최적화(optimize), 그 후 실행(execute)
  1. 쿼리 파서(query parser)는 쉼표의 위치 등 쿼리가 문법적으로 올바른지 검사하고, 테이블의 존재 여부 등의 의미론적으로 올바른지 검사한 뒤, 만약 올바르지 않다면 에러를 반환합니다. 만약 올바르다면, 쿼리는 수식으로 전환되어 다음 단계로 전달됩니다.
  2. 쿼리 플래너와 옵티마이저(query planner and optimizer)는 복잡한 일을 합니다. 이 단계는 확실한 최적화를 먼저 수행합니다 (5*10을 50으로 변환하는 것처럼 더 나은 성능을 위한 개선). 그 뒤 여러 가지 최적화 방법을 가지고 있는 "쿼리 플랜"들을 검색하여, 연관된 테이블의 열의 수에 의하여 발생되는 비용(CPU와 시간)을 계산한 뒤 가장 최적의 플랜을 찾고 다음 단계로 전달합니다.
  3. 쿼리 실행기(query executor)는 결정된 플랜으로 데이터베이스에서 작업을 수행하여 결과가 있으면 반환해줍니다.

사람이 할 일은 무엇인가요?

쿼리 플래닝과 최적화는 모든 쿼리에서 발생하는데, 몇몇은 평생 이를 모르고 SQL 쿼리를 만들 수가 있습니다. 하지만 더 큰 데이터들을 다루기 시작하면, 여러분은 쿼리의 실행 속도에 신경을 더 쓰게 될 것입니다. 쿼리들의 속도를 더 향상시킬 방법이 없는지 고민하고 있는 여러분을 발견할 것입니다.
대부분의 경우 쿼리가 복잡할수록, 쿼리를 최적화 시킬 수 있는 방법이 존재합니다. 이것은 "쿼리 튜닝(query tuning)" 이라고 합니다.
첫 번째 단계는 SQL 프로파일러 같은 것을 통해 데이터베이스 호출 중 가장 오래 걸리는 것을 찾거나 가장 많은 자원을 사용하는 것을 찾아서 튜닝하고 싶은 쿼리를 구분하는 것입니다. 때때로, 시간이 너무 오래 걸려서 데이터베이스를 통째로 다운시키기도 하는 매우 성능이 좋지 않은 쿼리가 발견되기도 합니다. 이러한 것들을 미리 알아내면 좋겠죠.
다음 단계는 특정 SQL 엔진이 쿼리를 실행하는 방법을 이해하는 것입니다. 모든 SQL 시스템은 엔진에게 질문하는 방법을 제공합니다. SQLite에서는, EXPLAIN QUERY PLAN을 SQL 앞에 붙여 해당 SQL이 뒤에서 어떻게 작동하는지 볼 수 있습니다. 만약 이것을 사용하려면 EXPLAIN QUERY PLAN 참조를 깊게 공부하셔야 합니다. "설명"이 상당히 구체적이기 때문입니다. 만약 다른 SQL 엔진을 사용한다면, "how do I get an execution plan in X"을 검색해보세요.
이제 수동으로 실행 플랜을 최적화하는, 어려운 부분이 시작됩니다. 이 부분은 종종 SQL 엔진의 특성과 여러분의 데이터의 특성에 따라 달라집니다.
예를 들면, 본문 맨 처음에 이야기했던 쿼리를 기억하시나요? 만약 우리가 사전에 작가이름 열에서 WHERE 절을 이용해서 수 백 번의 쿼리를 실행할 것을 알았다면, CREATE INDEX를 사용하여 명시적으로 인덱스를 생성할 수 있습니다. 그러면 SQL 엔진은 그 인덱스를 사용하여 효율적으로 쿼리와 일치하는 행을 찾을 수 있을 것입니다. 인덱스에 대한 이해를 돕기 위해 SQLite query planning 가이드를 참고하시면 도움이 될 것입니다.
인덱스를 생성하는 것은 많은 경우 반복되는 쿼리를 더 효율적으로 동작할 수 있게 만들어줍니다. 하지만 다른 접근 방법도 존재합니다. SQLite의 query planner overview의 "매뉴얼" 섹션을 참고하시면 더 많은 아이디어를 얻을 수 있습니다.
여기서 쿼리 최적화와 쿼리 튜닝의 모든 복잡한 부분을 다룰 순 없기 때문에 필요할 때 더 깊이 공부하는 것을 추천합니다.
(여기 더 깊이 공부하는데 도움이 될 SQL 쿼리 플래너들이 있습니다. SQL Server Query OptimizerOracle SQL TuningMSSQL Execution Plan Basics)