[도서 회고록] 친절한 SQL 튜닝을 읽고

최근에 회사에서 DB 구조와 성능에 관련해서 많은 고민을 했다.

 

일을 하면서 맘에 안드는 부분이 있었는데, 이에 대해서 제대로 반박을 못했다.

 

특히 성능 부분도 부분이지만 화면상에서의 쓰임과 데이터의 흐름을 잘 집어내야 합리적인데... 

 

하지만 그 당시 키메라는 이를 정확히 찝어낼 수 가 없었다.!  (이런 된장고추장! 팍팍무쳐 먹어! ㅠㅜ...)

 

아아악!

 

그래서! 내가 내 스스로의 무능함(?)에 짜증이나서 SQL관련 책을 읽기 시작했다.

 

가장 최근에는 SQL Antipattern 을 읽었는데, 다 읽고 나니 좀 오래된 책이다 보니 이해는 되는데

 

글이 그렇게 잘 눈에 들어오지는 않았고, 대부분의 문제는 사실 정규화를 잘 따라서 하면 되는 문제였다.

 

 

그리고 이전에 사두고 읽기 않고 박혀있는 '친절한 SQL 튜닝' 이라는 책을 읽고 정말! 초 압축 실전해버린(?) 간단한 정리와 후기를 적는다.

 

---

0. DB 저장 구조

테이블 스페이스

세그먼트를 담는 콘테이너

 

세그먼트(테이블, 인덱스, 파티션, LOB)

데이터 저장공간이 필요한 오브젝트.

여기서 오브젝트란 데이터베이스 내에서 데이터를 저장, 관리, 참조하기 위해 생성되는 모든 구조물을 통칭하는 용어

 

익스텐트

공간을 확장하는 단위. 세그먼트는 여러 익스텐트로 이루어져 있다.

 

블록

사용자가 입력한 레코드를 실제로 저장하는 공간. 한 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.

 

1. 논리적 I/O와의 싸움, 쿼리 튜닝

논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL튜닝이다.

 

BCHR(Buffer Cache Hit Ratio) 가 있다.

 

BCHR = (캐시에서 곧바로 찾을 수 있는 블록 수 / 총 읽은 블록 수 ) X 100

           = ( (논리적 I/O - 물리적 I/O) / 논리적 I/O) X 100

           = (1 - (물리적 I/O) / (논리적 I/O) ) X 100

 

=> 물리적 I/O = 논리적 I/O X (100% - BCHR)

 

이렇게 된다.

 

물리적 I/O는 시스템 상황에 따라 결정되는 통제 불가능한 회생 변수이다. 

 

우리가 직접적으로 할 수 있는건 쿼리 튜닝을 통한 논리적 I/O를 줄여서 물리적 I/O의 성능을 올리는 것이다. 

2. Index가 많다고 늘 좋은건 아니다 + 설계법

인덱스는 일정 조회 데이터의 임계점을 초과하면 성능이 나빠진다고 한다. 

 

인덱스란 소량의 데이터를 빠르게 찾기 적합하지 대량의 데이터를 조회하기에는 부적합하다. 

 

대량의 데이터를 조회해서 사용해야 하는 경우에는 full scan을 사용하는게 나을 수 있다.

 

 

설계법은 간단하다. 점 조건을 앞에 위치하고 선분조건을 뒤에 배치한 인덱스를 생성하면 된다.

 

인덱스 액세스 조건과 인덱스 필터 조건이 있는데,

 

인덱스 액세스 조건을 타게 되면 인덱스 트리에서 스캔의 시작점을 정확히 찾아가고,

어디까지 읽을지 종료점을 결정한다.

 

즉, 스캔해야 할 범위를 대폭 줄여준다!

 

인덱스 필터 조건을 타게 되면 액세스 조건에 의해 결정된 범위를 쭉 스캔하고 데이터가 있는지 없는지를 판단한다. 

 

 

 

스캔 범위 자체를 줄여주지 못하며, 읽어 들인 블록(I/O)이 낭비되는 원인이 되기에 되도록이면 최대한 액세스 조건을 사용하고

 

그 후에 필터 조건을 사용해야 한다.

 

다시 외치자! 점 조건을 앞에 위치하고 선분조건을 뒤에 배치한 인덱스를 생성하면 된다.

 

3. in절 조건조회 vs between조건 검색

in절로 사용하는 경우 union all이 붙어서 쿼리를 조회하는것과 같다.

 

이를 IN-List Iterator 라고 한다.

 

in 절에 있는 데이터를 정렬해서, 점조건으로 일일히 조회를 한다.

 

다만 in절 사용시 index skip scan의 여지를 옵티마이저에게 제공한다. 

 

between 혹은 범위조건을 사용하는 경우에는 수직탐색 후 끝점을 넘을 때 까지 수평 탐색을 한다. 

 

물론 값이 적은 경우에는 in절을 사용해도 문제가 없지만, 연속된 어느정도 큰 값들 조회시에는 between을 사용해서 조회하는게 현명하다.

 

예를 들자면, 필자의 경우 PMS 개발을 진행중인데 요금 정보는 매우 자주! 조회한다.

 

그리고 특정 날짜에는 요금이 없어서 판매를 안하는 경우가 없기에, 연속적으로 데이터가 있을 수 밖에 없다. 

 

고로, 일별로 생성되는 요금 정보 조회시에는 betwen 혹은 범위조건을 사용하는게 맞다.

 

4. NL 조인, 소트머지조인, 해시조인

조인에도 여러 방법을 사용할 수 있는데,

NL 조인, 머지조인, 해시조인이 있다. 

 

각각을 간단하게 집고 넘어가자. 

 

4.1) NL(Nested Loop) 조인

해석하면 중첩된 루프 조인이다. 

같이 조인하는 테이블이 A, B가 있고 driving table이 A라고 한다면 

A를 기준으로 outer loop, B를 기준으로 inner loop가 발생해서 이중 loop 구조이지 않은가? 그래서 중첩 루프 조인이라고 한다. 

이는 인덱스를 이용한 조인 방식이며 순차적으로 진행이 되는 점을 보면 대량 데이터 조인에는 불리하다. 

 

해당 조인을 튜닝하기 위해서는 과도한 랜덤엑세스가 발생하는 지점을 알아내고 이를 인덱스가 추가하던지, 기존의 인덱스를 수정하던지 결정해야 한다.

 

4.2) 소트머지조인(Sort Merge Join)

우선 이를 이해하기 위해서는 SGA(System Global Area) 와 PGA(Private Global Area)에 대해 이해해야한다. 

 

SGA는 공유 메모리 영역으로 동시 액세스가 불가능하다. 

 

PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간으로 이것이 작으면 Temp 테이블스페이스를 이용한다. 

 

소트머지조인은 소트와 머지를 순서대로 거쳐서 조인을 한다는 말로, 

 

양쪽 집합을 조인 컬럼 기준으로 정렬해서 이를 서로 머지한다. 

 

소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 한번에 읽어서 PGA혹은 Temp 테이블스페이스에 저장하는데, 

데이터를 읽을 때 래치 획득 과정이 없기 때문에 빠르다.

 

4.3) 해시조인(Hash Join)

소트 머지 조인과 비슷하데 Build 단계(1) 후 Probe 단계(2)를 거친다.

작인 쪽 테이블을 읽어서 해시 테이블을 생성한 후, 큰쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인한다. 

 

결론은 다음과 같다.

 

소량 데이터 조인시 : NL조인

대량 데이터 조인시 : 해시 조인

대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 조인 조건식이 등치(=)조건이 아닐 때 : 소트머지조인

 

5. 그 외 내용 (5~7장)

책을 반으로 나눠서 전반은 챕터 4까지 내용을 정리한더라면,

5부터는 소트 튜닝, DML 튜닝, 옵티마이저에 관한 내용이 있다.

 

소트는 PGA에서 할당한 Sort Area에서 이루어지는데 메모리 소트와 디스크 소트로 나뉜다. 

소트 튜닝은 소트 연산이 메모리 집약적이며 CPU 집약적이며 처리할 데이터가 많으면 디스크 I/O까지 사용한다고 하니... 

이 소트에 대해서 이해를 해야 한다. 

 

가장 좋은건 물론 소트가 발생하기 않도록 SQL을 작성하는 것이다. 

 

어떻게? 인덱스를 활용하고, union과 union all 을 구분해서 활용하고 또 exists를 잘 활용하면 된다. distinct 를 쓰면 뭔가 잘못된걸 알아차려야 한다.

 

이 부분에서 인상깊었던 부분은 Top N Sortkey 알고리즘이었다.

 

6장의 DML 튜닝의 핵심은 파티션 분리와 로컬 인덱스 생성,

7장의 SQL 옵티마이저에 대해서는 굳이 내가 이거까지 알아야 하나... 하는 생각이 들었다. (?)

 

6. 느낀점과 결론

해당 책의 구성은 개인적으로 4장까지는 매우 흥미진진하게 읽었다.

 

그리고 5장 부터는 아니 내가 이것도 알아아 해? 참나~ 하는 생각이 들었다.

 

뭐... 알면 좋지만 너무 깊게 들어가면 끝이 없으니 문제라는 것이다.

 

현재 상황에서는 사용하지 않는 정보들에 너무 목메고 이렇게까지 깊게 내가 파고 들어도 쓰지 않는 정보는 금방 잊혀지기에

 

5장까지만 잘 이해하고 전체적으로 키워드만 기억을 해도 후일에 AI에게 물어봐서 금방 해결 할 수 있을거라는 판단이 들었다.

 

무엇보다도, 지식을 탐구하자 하면 끝이 없다. 그것이 요즘 빠르게 도입되고 있는 AI agent와의 협업에 좋다고는 생각을 안했다.

 

그래서 최소한의 지식으로 키워드만을 선별해 최대한의 효율을 내는것이 현 시점에서는 타당하다고 판단했다.

 

 

그럼 아예 책을 읽지 않는게 낫지 않은가? 스스로에게 반문도 해보았는데,

 

결국에 깊이 생각하고 이것을 이끌어 가는 것은 사람이고, 필자 키메라의 경우에는 그것을 끊임없이 키워나가야 한다는 생각에 

 

책을 읽고, 공식문서를 읽고 원리를 어느정도 파악을 하는게 엔지니어의 소양이라고 생각한다.

---

 

책을 열심히 읽었지만 책에 대한 지식도 지식이지만 이상하게 옛날 생각이 많이 났다.

 

3년전이다. 

 

이전 직장인데, DBA일을 하시는 대리님이 있었다.

 

주니어인 내 쿼리를 보더니 다음과 같이 말했다.

 

대리 : 키메라님. 쿼리에서 조인 순서에 따라서도 성능이 달라질 수 있는거 아나요?
키메라 : 아뇨? 그냥 데이터 잘 나오면 되는거 아닌가요?
대리 : 그게 성능이 달라질 수 있어요. (할 일 하러 간다)
키메라 : ...? (알려준다는건가...?)

 

그렇게 해서 대리님은 나에게 그 말만 남기고 떠났다. 읽어보니 진짜 별거 없는데 참;; 

 

당시에는 편협적으로 spring framework 사용방법과 clean code를 작성하는 방법등에 관심이 많아서 DB성능을 왜 내가 고려해야하지... Java 개발자가 뭔 다해야해;;; 이랬었다.

 

물론 이러한 지식들이 없으면 대화가 안되는 엔지니어의 세계를 다시 실감했고, 현재는 단순히 강의를 보는게 아닌 책을 읽고 원리를 파해치기 위해 노력했다.

 

 

여태 내가 부족했던 점은 실전을 위한 공부가 아닌 이론을 위한 공부만을 했다는 점이었다.

 

이 상황에서는 어떻게 할까? 다른 상황에서는? 하는 trade-off에 대한 고민의 부재가 성장을 더디게 만들었다. 

 

결국 알면 정말 별거 아닌데... 이제는 좀 부끄러운 내 과거와 좀 마주하고, 앞으로 더 나아가는 느낌을 받았다.

 

 

사실 몇 일 동안 읽으면서 책의 모르는 부분은 AI에게 직접 바로바로 물어보면서 학습하니 학습의 속도가 배가 되었다.

 

이것처럼 내가 무언가 튜닝을 할 일이 있다면 어디에 어떤 정보가 있었지~ 하고 다시 펴서 읽고 기억을 상기시키고

 

이를 AI에게 물어봐서 내가 찾은 정보에 대해 검증받고, 현 상황에 대해서 질문한 후 해결하면 될 듯 하다.

 

 

결국 어느정도의 이해는 엔지니어로서 중요하지만 예전처럼 막 엄청나게 하나하나 상세하게 다 찾아가지고 아... 진짜 나 전문가다! 하는 순간이 계속 하다보면 오겠지만, 현재 키메라의 경우에는 개발자이기 때문에

 

건방지지만 이 정도 수준만 알아도 정~말 많이 아는거라고 생각한다. 

 

다만 연차가 좀 더 쌓이고 규모가 큰 시스템을 설계하고 유지보수 하게 되면 좀 더 찾아보도록 하겠다.