SQL 처리 과정과 I/O

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

---

SQL 처리 과정

 

SQL파싱과 최적화

 

1️⃣ SQL 파싱

사용자로부터 SQL을 전달받으면 SQL 파서가 파싱을 진행한다.

 

💡 SQL 문을 이루는 개별 구성 요소를 분석하여 파싱 트리를 생성한다.

 

💡 그리고 전달 받은 SQL 문에서 문법적 오류 (사용할 수 없는 키워드 사용 등) 나 의미상 오류(존재하지 않는 테이블 혹은 컬럼 사용 등)가 존재하는지 확인한다.

 

2️⃣ SQL 최적화

💡 옵티마이저는 다양한 실행 경로를 생성해서 비교한 후, 가장 효율적인 하나를 선택한다.

 

그렇게 선택한 경로를 실행 가능한 코드 또는 프로시저 형태로 포맷팅한다.

 

---

실행 계획과 비용

 

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 그 결과를 만들어 내는 방법은 매우 다양하다.

 

다양한 방법 중, 가장 최적이고 최소의 비용이 소모되는 방법을 선택하는 작업을 옵티마이저가 담당한다.

 

MySQL 옵티마이저는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.

 

대표적인 옵티마이저는 산출된 비용을 바탕으로, 비용이 최소로 소요되는 처리 방식을 선택하는 💡 비용 기반 옵티마이저 가 있다.

 

실행 계획을 보고, 자신이 작성한 SQL문이 테이블을 스캔하는지 인덱스를 스캔하는지 등을 파악할 수 있고 예상과 다르게 실행된다면 실행경로를 변경할 수 있다.

 

옵티마이저가 만들어 내는 실행 계획을 이해할 수 있어야 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 유도할 수 있다.

 

💡 옵티마이저가 항상 100% 실수없이 동작하는 것이 아니기 때문에, 옵티마이저 힌트를 사용하면 개발자가 직접 더 효율적인 엑세스 경로를 찾아낼 수도 있다.

 

예를 들어,

 

SELECT /*+ INDEX(A (주문일자)) */
	A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
AND A.고객ID = B.고객ID

위와 같이 주석 기호에 '+' 를 붙이면 힌트를 사용할 수 있고, 위 경우에는 주문일자 컬럼이 선두인 인덱스를 사용하도록 힌트를 지정한 것이다. 

 

그 외, 조인 방식이나 조인 순서 등도 힌트를 통해 지정할 수 있다.

 

---

SQL 공유 및 재사용

 

소프트 파싱 VS 하드 파싱

 

사용자가 SQL문을 전달하면 DBMS 는 SQL을 파싱한 후 해당 💡 SQL이 라이브러리 캐시에 존재하는지 확인한다.

 

💡 찾았다면 곧바로 실행단계로 넘어가고 (소프트 파싱),

 

🚨 그렇지 못했다면 처음에 설명한대로 문법적, 의미상 오류가 없는지 판단하고 실행 경로를 비교하는 최적화 단계를 수행한 뒤 포맷팅하여 라이브러리 캐시에 저장한다. (하드 파싱)

 

즉, 사용자에게 받은 SQL 문을 파싱하고 최적화하는 과정은 엄청난 연산이 소모되기 때문에, 한번 사용하고 버리기엔 너무나 비효율적이다.

 

따라서 처음부터 이 모든 과정을 진행하는 것을 하드 파싱이라고 부를 수 있고

 

이미 계산해둔 데이터를 통해 빠르게 파싱되어 실행되는 경우를 소프트 파싱이라고 부른다.

 

❓ 첫 번째 SQL 문을 파싱하고 두 번째 SQL문을 실행하면 소프트 파싱이 될까 하드 파싱이 될까?

1. SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'PAUL';

2. SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'JOHN';​

일반적으로는 두 구문이 데이터를 가져오는 과정은 비슷하지만 다른 구문이기 때문에 두번의 하드 파싱이 진행된다.

따라서, 효율적으로 SQL문을 파싱하기 위해서

SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?;​

 

LOGIN_ID 에 들어가는 부분만 바인드 변수로 받도록 하는 프로시저를 공유하면서 재사용하면 여러개의 프로시저를 생성하지 않아도 된다.

기존 방식대로, 쿼리가 100개 요청이 되었다면 라이브러리 캐시에는 100개의 프로시저가 생성되어있을 것이지만

개선한 방식을 사용하면 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1​

위와 같은 쿼리만 캐싱되어 여러 SQL이 공유하면서 사용할 수 있게 된다.

 

 

데이터베이스의 I/O

DB의 주된 역할은 당연히 데이터를 저장하는 것이다.

 

그리고, 한 테이블의 데이터는 하나의 블록에 함께 저장되며 다른 테이블의 데이터와 섞여있지 않는다.

 

다만, 하나의 블록을 점유해서 사용하다가 추가적으로 공간이 필요해지면 추가적으로 💡 익스텐트 단위로 공간을 확장한다.

 

다만, 한 블록 안에서의 데이터는 연속된 공간이지만, 🚨 익스텐트 끼리는 붙어있지 않으면 연속된 공간이 아니고 공간을 확장하는 과정에서 비연속적으로 확장되는 경우가 더 흔하게 발생한다.

 

따라서, 연관된 익스텐트 내의 블록을 찾는 과정에서 비용이 발생하게 되는 것이다.

 

 

 

데이터베이스의 I/O 단위는 일반적으로 💡 블록이 된다.

 

따라서, 블록 내의 레코드 1개를 읽고 싶어도 블록을 통째로 읽어야한다.

 

 

이러한 블록을 읽는 방법은 시퀀셜 엑세스 방식과 랜덤 엑세스 방식이 존재한다.

 

시퀀셜 엑세스 방식은 논리적 또는 물리적으로 연결된 순서에 따라 블록을 읽는 방식이고 

 

랜덤 엑세스 방식은 논리적 또는 물리적 순서를 따르지 않고 한 블록씩 비연속적으로 접근하는 방식이다.

 

 

논리적 I/O 와 물리적 I/O

디스크에서 매번 데이터를 읽어오는 과정을 반복하는 것은 매우 큰 비용이기 때문에

 

한번 읽어 온 데이터는 캐시에 저장해두게된다.

 

💡 논리적 I/O는 버퍼와 디스크를 구분짓지 않고 블록을 읽은 횟수로 볼 수 있고

 

💡 물리적 I/O는 찾으려는 블록이 버퍼에 존재하지 않아서 디스크로 가서 읽어온 횟수(캐시 히트에 실패한 횟수와 유사)로 볼 수 있다.

 

❓ 물리적 I/O의 경우 시스템 상황에 따라 달라지는 변수이므로 논리적 I/O를 개선하는 것이 성능 향상에 중요하다. 그렇다면 논리적 I/O는 어떻게 줄일 수 있을까?

 

애초에 DB 에 SQL을 요청할 때, 블록 개수를 조금 읽게금 요청하는것이 가장 확실하다.

 

논리적 I/O를 줄임으로서 물리적 I/O를 줄이는 것이 SQL 튜닝이라고 할 수 있다.