SQL을 튜닝하기 전에, 옵티마이저가 SQL을 어떻게 처리하는지, 서버 프로세스는 데이터를 어떻게 읽고 저장하는지 살펴보자.
| 구조적, 집합적, 선언적 질의 언어
SQL은 Structured Query Language의 줄임말로, 구조적 질의 언어이다.
- SQL은 기본적으로 구조적이고, 집합적이고, 선언적인 질의언어이다.
- 원하는 결과집합을 구조적, 집합적으로 선언하지만, 결과집합을 만드는 과정은 절차적이다.
- 데이터베이스 내부에서는 이 선언적인 쿼리를 기반으로 데이터를 가져오는 과정을 절차적으로 처리한다.
- 사용자가 입력한 쿼리를 분석(Parsing)하고,
- 실행 계획을 최적화(Optimization)하며,
- 데이터를 필터링(Filtering)하고 정렬(Sorting)하여 결과를 반환한다.
데이터베이스 내부에서 데이터를 절차적으로 가져오는 과정을 하기 위해서는 *프로시저가 필요하다.
- 프로시저를 만들어주는 것이 DBMS 내부 엔진의 SQL 옵티마이저이다. (옵티마이저가 프로그래밍을 대신해주는 것)
- [사용자] - SQL - [옵티마이저] - 실행계획 - [프로시저]
DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 SQL 최적화라고 한다.
*프로시저(Procedure) : 데이터베이스에 저장된 프로그램으로, 특정 작업을 수행하는 일련의 SQL 문과 제어 구조를 포함하는 코드 블록
| SQL 최적화
최적화 과정을 세분화 해보자.
- SQL 파싱 : 사용자로부터 SQL을 전달받으면, 가장 먼저 SQL 파서가 파싱을 진행한다.
- 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- Syntax 체크 : 문법적 오류가 없는지 확인, 예를 들어, 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인
- Semantic 체크 : 의미상 오류가 없는지 확인, 예를 들어, 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인
- SQL 최적화 : 옵티마이저의 역할이다. SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트를 통계정보를 바탕으로 다양한 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다. 데이터베이스의 성능을 결정하는 가장 핵심적인 엔진이다.
- Row-Source 생성 : SQL 옵티마이저가 선택한 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다. (로우 소스 생성기의 역할)
| SQL 옵티마이저
SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.
- SQL 옵티마이저는 서버 백그라운드 프로세스가 아니라, 서버 프로세스가 가진 하나의 기능이다. (SQL 파서와 로우 소스 생성기도 동일)
최적화 단계는 다음과 같다.
- 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
- 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행계획의 예상 비용을 산정한다.
- 최저 비용을 나타내는 실행 계획을 선택한다.
| 실행계획과 비용
DBMS에 SQL 실행 경로를 미리 볼 수 있다. 실행계획을 통해 보면된다.
- SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있도록 트리 구조로 표현한 계획이다.
- 자신이 작성한 SQL이 테이블을 스캔하는지, 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지 확인할 수 있다.
- 예상과 다른 방식으로 동작하지는 않는지 확인한다.
옵티마이저가 특정 실행 계획을 선택하는 근거는 무엇일까?
비용(Cost)이다. (쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값)
데이터베이스 옵티마이저에게 특정 실행 계획(Execution Plan)을 고려하거나 강제하도록 지시하는 옵티마이저 힌트를 줄 수도 있다.
| 소프트 파싱 vs 하드 파싱
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간을 라이브러리 캐시라고 한다.
- 라이브러리 캐시는 *SGA 구성요소이다.
사용자가 SQL 문을 전달하면
- DBMS는 SQL 파싱하고, 해당 SQL이 라이브러리 캐시에 존재하는지 확인
- 캐시에서 찾으면, 곧바로 실행단계로 (소프트 파싱: Soft Parsing)
- 찾지 못하면 최적화 단계를 거친다. (하드 파싱: Hard Parsing)
라이브러리 캐시가 필요한 이유는 SQL 최적화 과정이 왜 하드한가에 대해 생각해보면 나온다.
- SQL 최적화 과정은 SQL 옵티마이저가 순식간에 엄청나게 많은 연산을 수행한다.
- 따라서, 하드 파싱은 CPU를 많이 소비하는 작업이다.
- 이렇게 생성한 내부 프로시저를 한 번만 사용하고 버리기에는 아깝다..!
*SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.
| 바인드 변수의 중요성
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 가지며, 컴파일한 상태로 딕셔너리에 저장된다.
하지만, SQL은 이름이 따로 없다. 전체 SQL 텍스트가 이름 역할을 한다.
- 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다.
- SQL 자체가 이름이기 때문에 텍스트 중 작은 부분만 수정되어도 새로운 객체가 탄생한다.
SQL은 왜 이름이 없을까? 이름을 가지고 영구 저장할 수는 없을까?
대부분의 DBMS는 이름이 없다고 한다.
개발을 할 때를 떠올려보면, SQL 문이 완성된 SQL 문이 아닌 변경될 수 있는 SQL이다. (작은 부분만 수정되어도 새로운 객체가 탄생하는 특성 때문에 SQL을 영구적으로 저장하면 비효율이 커진다.)
만약, 이름을 가지고 있다면 미완성 SQL 문부터 일회성, 무효화된 SQL문까지 전부 저장해야 한다.
그러면, 많은 공간이 필요하고 SQL을 찾는 속도도 느려진다.
이것이 SQL을 영구 저장하지 않는 이유이다.
좀 더 나아가보자.
예시로 각 고객의 정보를 가져오는 SQL문을 하드 코딩했다고 생각해보자.
String SQLStmt = "SELECT * FROM customers WHERE customer_id = '" + customer_id + "'";
위와 같이 코드가 작성되면 고객의 정보를 조회할 때마다 내부 프로시저를 하나씩 만들어서 라이브러리 캐시에 적재한다.
이는 고객이 한 번에 몰릴 때 문제가 발생한다. 위의 코드가 로그인 로직에 있다면, CPU 사용률이 급격하게 올라가고 로그인 처리가 제대로 이루어지지 않을 것이다.
따라서, 위와 같은 상황을 방지하기 위해 바인드 변수가 필요하다.
- 바인드 변수 : SQL 쿼리에서 동적으로 변경될 수 있는 값을 변수로 사용하는 기법
- SQL 문을 실행할 때 변수 자리에 값을 바인딩하여 실행한다.
바인드 변수를 사용하면, SQL 텍스트가 동일하게 유지되기 때문에 하드 파싱을 줄이고 소프트 파싱으로 처리할 수 있다.
- 소프트 파싱은 기존에 생성된 실행 계획을 재사용하므로, CPU와 메모리 비용이 크게 줄어든다.
따라서, 바인드 변수를 사용한 SQL 쿼리는 SQL 텍스트가 동일하며 실행 계획이 라이브러리 캐시에 저장되고 재사용 가능하기 때문에 공유 가능한 SQL이다.
I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다. 따라서 SQL 튜닝 원리를 제대로 이해하려면 I/O에 대한 이해가 중요하다.
데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘을 살펴보자.
| SQL이 느린 이유
SQL이 느린 이유는 대부분 디스크 I/O 때문이다.
I/O가 뭘까?
책에서는 I/O를 잠(Sleep)이라고 표현한다.
OS 또는 I/O 서브 시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다.
프로세스는 실행 중인 프로그램이며, 생명 주기를 갖는다.
생성 이후부터 종료 전까지 준비 - 실행 - 대기 상태를 반복한다.
- 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태로 전환했다가 다시 실행 상태로 전환한다.
- 특정 순간에 하나의 프로세스만 CPU에서 사용할 수 있기 때문에 이런 매커니즘이 필요하다.