SQL 파싱 부하

 

 

🪡 SQL 처리 과정

 

과거 데이터베이스 프로그래밍을 할 때에는, 처리 절차를 프로그래머가 직접 작성을 해야 했다. 그러나 지금은 구조화된 질의언어(SQL) 을 통해 사용자가 원하는 결과집합을 정의하고, 그것을 얻는 데에 필요한 처리절차를 직접 기술을 하지는 않는다.

대신 프로그래밍을 해주는 엔진이 DBMS 내에 내장이 되어 있기 때문이며, SQL 옵티마이저가 그 역할을 수행한다.

옵티마이저에 의해서 생성된 처리절차를 실행계획이라고 부른다.

옵티마이저에서는 실행 계획을 생성하고, 프로시저(또는 코드) 형태로 변환하는 과정을 거친 뒤 SQL 엔진에 의해 수행이 된다.

 

🐣SQL 파싱(Parsing)

사용자가 SQL을 실행하게 되면, 제일 먼저 SQL Parser가 SQL 문장에 문법적 오류가 없는지 검사한다.

문법적으로 오류가 없으면 의미상 오류가 없는지를 검사하고, 이런 검사 절차를 마치면 사용자가 발행한 SQL과 그 실행 계획이 라이브러리 캐시에 캐싱되었는지를 확인한다. 만약 캐싱되어있다면, 무거운 최적화 과정 없이 곧바로 실행이 가능하다

- 소프트 파싱(Soft Parsing) : SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어감

- 하드 파싱(Hard Parsing) : SQL과 실행계획을 캐시에서 찾지 못하고 최적화 과정을 거친 뒤 실행 단계로 넘어감

라이브러리 캐시는 해시구조로 관리를 하기 때문에, SQL마다 해시 값에 따라 여러 해시 버킷으로 나누어 저장한다. SQL을 찾을 때에는 SQL 문장을 해시 함수에 입력, 변환된 해시 값을 이용해 해당 해시 버킷을 탐색하면 된다.

 

🐤 SQL 최적화(Optimization)

옵티마이저는 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최저비용의 처리경로를 선택해주는 DBMS의 핵심엔진이다.

옵티마이저의 최적화 과정을 거치는 것이 위의 하드 파싱인데, 그 최적화 과정은 굉장히 무거운 작업이기 때문에 옵티마이저가 실행한 최적화 과정을 통해 생성된 SQL 실행계획을 여러 사용자가 재사용할 수 있도록 공유 메모리에 캐싱해둔다.

 

✨SQL 실행 절차

1. 문법적 오류와 의미상 오류가 없는지 검사.

2. 해시 함수로부터 반환된 해시 값으로 라이브러리 캐시 내 해시버킷을 찾아감.

3. 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하며 같은 SQL 문장을 찾음.

4. SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행.

5. 찾아간 해시버킷에서 SQL 문장을 찾지 못하면 최적화 수행.

6. 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결.

7. 6에서 연결한 실행계획을 실행.

 

 

 

🧶 캐싱된 SQL 공유 

 

🐣 실행계획 공유 조건

SQL 수행 절차에서 하드 파싱을 반복하지 않고, 캐싱되어있는 버전을 찾아 재사용하기 위해서는 SQL을 먼저 찾아야 하는데 여기에서 찾는 것은 'SQL 문장' 이다. 결국, 문장을 해시의 키값으로 사용한다고 볼 수 있다.

즉, SQL 문장에서 작은 공백 문자 하나만 추가되더라도 서로 다른 문장으로 인식하기에 캐싱된 버전을 사용 불가능하게 된다.

 

🐤 실행계획을 공유하지 못하는 경우

위에 언급했듯 작은 공백 문자 하나만 추가되어도 다른 문장으로 인식하기 때문에, 옵티마이저는 공백, 대소문자 구분, 주석 등의 아주 작은 변화에도 다른 SQL로 인식해 별도의 실행계획을 수립하게 된다.

그렇기 때문에 비효율을 줄이고 공유 가능한 형태로 SQL을 작성하기 위해서는 개발 초기에 SQL 작성 표준을 정의하여 이를 준수하도록 해야한다. 보통 임의질의를 통해 나오는 것은 수행빈도가 낮기 때문에 걱정할 것이 없지만, 사용자가 직접 입력한 값을 계속해서 다른 SQL로 실행하는 경우라면 말이 달라진다. 

 

 

 

🏹 바인드 변수 사용하기

 

사용자 로그인을 한다고 생각해보자. 

select *
from customer
where login_id = 'user';

select *
from customer
where login_id = 'user2';

 

이와 같은 식으로 작성한다면,  옵티마이저는 로그인 시마다 이와 같은 프로시저를 계속해서 생성해낼 것이다. 그러나, 근본적으로 이것의 내부 처리 루틴은 같고, where에 들어가는 문자열만 달라지는 것이다. 그렇기때문에 같은 프로시저를 계속 생성하도록 하는 것보다, login_id를 피라미터로 받아 하나의 프로시저로 처리하게 하는 것이 옳다.

이것을 위해 SQL을 작성하는 방법이 곧 바인드 변수(Bind Variable)이다. 바인드 변수를 사용하게 되면, 하나의 프로시저를 공유하며 반복 재사용할 수 있게 된다.

 

select * from customer where login_id = :login_id;

 

위와 같이 바인드 변수를 사용하게 되면, 이를 처음 수행할 때 하드파싱을 통해 실행계획을 생성한다. 이후 라이브러리에 캐싱해두면서 반복작업을 할 때 계속해서 재사용할 수 있도록 해준다. 

바인드 변수를 사용하게 되면 재사용을 하게 되므로 파싱 소요시간과 메모리 사용량을 줄여준다. 결국, 시스템 전반의 CPU와 메모리 사용률을 낮춰 데이터베이스 확장성을 높이게 된다. 

 

그러나, 바인드 변수가 존재함에도 불구하고 리터럴 SQL 위주로 개발을 하게 된다면 캐시 경합으로 인해 시스템의 정상 가동이 어려운 상황에 놓이게 될 수 있는데, 이에 대비하여 DBMS는 조건절 비교 값이 리터럴 상수일 때 이를 자동으로 변수화시키는 기능을 제공하는데, 문제는 이 옵션을 적용하는 순간 실행계획이 변경이 되기 때문에 원래 이용하던 SQL이 느려지는 문제가 발생할 수 있다. 또한, 의도적으로 사용한 상수에도 문제가 생길 수 있다. 그렇기 때문에 가급적 바인드 변수를 사용하는 것이 좋다.

 

🐣  바인드 변수 사용 시 주의사항

바인드 변수를 사용하면 SQL이 최초 수행될 때이기 때문에 옵티마이저는 균일한 값이 들어옴을 가정한다. 그렇기 때문에 칼럼 분포가 균일하지 않을 때에 실행 시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다. 이럴 때에는 바인드 변수를 사용하는 것보다 상수 값을 사용하는 것이 나을 수 있다.

 

 

 

🛒 static SQL과 Dynamic SQL

 

🐣 Static SQL

StaticSQL은 String형 변수에 담지 않고 코드 사이에 직접 SQL문을 기술한 것이다.  코드에 SQL문을 섞어서 기술을 하게 되면 컴파일을 거치면서 SQL을 실행하게 되는데 Static SQL의 경우에는 런타임 시에 질의문이 변경이 되지 않기 때문에 구문 분석, 유효 오브젝트 여부, 오브젝트 엑세스 권한 등을 체크할 수 있다.

 

🐤 Dynamic SQL

Dynamic SQL의 경우에는 String 형 변수에 담아서 SQL을 작성한다. 변수를 사용하기 때문에 SQL문을 동적으로 변경할 수 있고, 런타임 시에 사용자에게 SQL의 일부 또는 전체를 입력받을 수도 있다. 그렇기 때문에 PreCompiler에서는 내용을 확인하지 않고 DBMS에 바로 전송하고, 따라서 위에서 가능한 구문 분석, 유효 오브젝트 여부, 오브젝트 엑세스 권한 등을 체크할 수 없다.