[친절한 SQL 튜닝] 1장 SQL 처리 과정과 I/O 🔩 (1)

아래의 내용은 조시형의 <친절한 SQL 튜닝> 책을 공부하며 중요한 내용을 정리한 것입니다.

1.1 SQL 파싱과 최적화

SQL 처리 과정

SQL 처리과정을 크게 세단계로 세분화할 수 있다.

1. SQL 파싱 (by SQL Parser)
   1. 파싱 트리 생성
      • SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리를 생성한다.
   2. Syntax 체크
      • 문법 오류가 없는지 확인한다.
   3. Semantic 체크
      • 의미상 오류가 없는지 확인한다.
      • ex) 존재하지 않는 테이블 또는 칼럼 등을 사용했는지, 접근하려는 오브젝트에 권한이 있는지 등 확인
2. SQL 최적화 (by SQL Optimizer)
   1. 실행계획 후보군 생성
      • 쿼리를 수행하는데 사용할만한 실행계획 후보군을 찾는다.
   2. 실행계획의 비용 계산
      • 미리 수집해 둔 오브젝트 통계 정보, 시스템 통계 정보를 기반으로 각 실행계획의 예상 비용을 계산한다.
   3. 최적의 실행계획 선택
      • 실행계획 후보군 중에서 예상비용이 가장 적은 실행계획을 선택한다.
3. 로우 소스 생성 (by Row-Source Generator)
   • 실행계획을 실제 실행 가능한 코드 또는 프로시저 형태로 변환한다.

실행계획과 비용

앞에서 살펴봤듯이, 비용은 옵티마이저가 여러 개의 실행계획 후보군 중에서 가장 최적의 실행계획을 선택하는 기준이다.
여기서 비용이란, 정확히 말하면 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.
실제로 측정한 값이 아니라 옵티마이저가 여러가지 통계정보를 활용해서 계산한 예측값이기 때문에, 실제로 DB에서 쿼리가 처리될 때 발생하는 I/O 횟수와 소요시간과는 차이가 있을 수 있다.

옵티마이저 힌트

옵티마이저가 대개 최적의 실행계획을 생성해주지만, 옵티마이저가 생성한 실행계획이 항상 최적인 것은 아니다.
SQL이 복잡할수록 옵티마이저가 실수할 확률도 높아진다.
옵티마이저가 최적이 아닌 실행계획을 생성할 때 개발자는 옵티마이저 힌트를 사용해서 옵티마이저가 실행계획을 생성하는 과정에 개입할 수 있다.
(MySQL 옵티마이저 힌트 목록)

1.2 SQL 공유 및 재사용

소프트 파싱 vs 하드 파싱

SQL 파싱 -> SQL 최적화 -> 로우 소스 생성 과정을 거쳐서 생성된 프로시저는 재사용을 위해서 ‘라이브러리 캐시’ 라는 메모리 공간에 저장된다.
사용자가 SQL을 날리면 DBMS는 SQL을 파싱한 후에 해당 SQL이 라이브러리 캐시에 저장되어있는지 확인한다.
SQL이 라이브러리 캐시에 저장되어 있으면, SQL 최적화 -> 로우 소스 생성 과정을 생략하고 바로 실행된다.
이것을 소프트 파싱이라고 한다.
SQL이 라이브러리 캐시에 저장되어 있지 않으면, SQL 최적화 -> 로우 소스 생성 과정을 모두 거친 다음에 실행된다.
이것을 하드 파싱이라고 한다.
옵티마이저가 워낙 빠르게 처리하다보니 잘 느끼지 못하지만, SQL 최적화는 굉장히 비용이 큰 작업이다.
그렇기 때문에 가능하면 소프트 파싱으로 SQL이 처리되도록 해야 한다.

바인드 변수의 중요성

라이브러리 캐시에 SQL이 저장되어 있는지 확인할 때 사용하는 키값은 ‘SQL문 그 자체’이다.
그렇기 때문에 같은 데이터를 조회하는 SQL이더라도 띄어쓰기 한개라도 틀리면, 각자 별도의 SQL 최적화 과정을 거쳐서 별도의 라이브러리 캐시 공간에 저장된다.
아래는 모두 다른 SQL로 취급된다.

SELECT * FROM emp WHERE empno = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;
select * from emp where empno = 7900  ;
select * from com.emp where empno = 7900;
select /* comment */ * from emp where empno = 7900;
select /*+ first_rows */ * from emp where empno = 7900;

하드 파싱을 줄이기 위해서는 값을 SQL에 하드 코딩하지 않고 되도록이면 바인드 변수를 사용해서 나타내야 한다.

//  SQL에 하드코딩 🙅‍♂️
await queryRunner.query('SELECT * FROM emp WHERE empno =' + no + ';'); 

// 바인드 변수 사용 🙆‍♂️ 
await queryRunner.query('SELECT * FROM emp WHERE empno = ?;', [no]);