시작
나는 최근 DB에 대해 관심이 많다. 그래서 재밌게 공부하면서 나의 무기처럼 쓰기 위해 조금 깊게 팔 생각이다. 여러 책을 고민하면서 고른 책 4권 중에 MySQL 퍼포먼스 최적화에 공부를 시작해볼 것이다.
책을 보기 전 나는 지금 어떻게 생각하고 있는가?
나는 최근에 쿼리 최적화를 하면서 계속 의문이 들었다. 최적화는 최적화인데 대체 어떻게 쿼리가 DB서버까지 가서 실행이 되고 쿼리가 실행이 되면 어떻게 이게 왜 최적화인지 궁금했다. 솔직히 아직까지는 잘모르겠다.
그림에는 없지만 Web Application Server 왼쪽 부분에는 WebServer가 있으며 이 Web Server와 그림에 있는 Web Application Server, Db Server까지가 Server Side쪽이고 그 Web Server와 연결된 Http 로 통신하는 부분. 즉, 사용자쪽이 또 따로 있다. Client Side라 생각하면 된다.
최근 생각날 때 마다 고민하면서 그린건데 솔직히 맞는지는 잘모르겠다. 서버엔진에서 그 받은 쿼리를 파싱하고 나서 스토리지 엔진은 인터페이스와 같은 역할로 DISK에 통신을 하는 것으로 알고 있다.
즉, 나는 왜 이런 고민을 하냐면 인덱스에 대한 최적화를 공부하면서 최대한 DB에 적게 접근을 해야한다고 배웠고 그게 과연 Web Application Server에서 DB Server의 네트워크를 말하는건지 아니면 DB Server에서의 서버엔진에서 DISK로 가는 그러한 접근을 말하는 건지 이해를 할 수가 없었다.
또 좀더 얘기를 해보자면 어떻게 보면 우리가 보는 쿼리는 고급언어이다. 이 고급언어를 TCP/IP로 통신을 하고 packet으로 오버헤드를 붙인 다음 프록시에 들렸다가 다시 DB Server로 쏘고 이제 여기서 MySQL 시스템이 있으며 여기에 서버엔진, 스토리지엔진이 있다고 생각하며 여기에 또한 데이터가 있는 DISK가 있다고 생각한다.
그럼 이제 좀 더 나아가면 클러스터 인덱스 같은 경우에는 leaf 부분에 데이터가 이미 있기 때문에 이 DISK에 가지 않고 그렇기에 이 성능이 좋아진다는 것으로 이해하고 있다.
즉, 지금 나의 머리가 복잡한 이유가 이 과정이다. 아니 이게 과연 인덱스가 필요로 한다고? 겨우 이걸로? 라는 생각으로 부터 지금 내가 생각하는 이 데이터 흐름이 과연 맞는지. 나는 이러한 생각들로 이 원론적인 부분에 들어가려한다. 물론 아직 책을 읽기 전이지만 답이 나올 것 같지는 않다. 답을 내려고 하는 것은 아니다. 물론 답이 나온다면 좋다. 이 포스팅은 나의 머리에 있는 지금 여러 흐름들을 정리하고자 쓰는 글이라고 생각하면 좋다.
MySQL은 전체적으로 어떻게 생겼나?
서버엔진, 스토리지 엔진 이 두가지를 알아야한다.
서버엔진 : 클라이언트의 요청을 받아 SQL을 처리하는 DB 자체의 기능적인 역할
스토리지 엔진 : 서버 엔진이 필요한 데이터를 물리 장치에서 가져오는 역할을 한다.
자. 책에서 읽어보고 이해를 했을 때, 스토리지 엔진에서 DISK(물리 장치)에서 실제 데이터를 갖고 오는 것이 맞다.
우선 여기까지는 내가 생각했던 것과 같다. 이 서버엔진은 DB가 SQL을 이해할 수 있도록 쿼리를 재구성하는 쿼리 파싱, 디스크나 메모리 같은 물리적인 저장장치와 통신하는 스토리지 엔진에 데이터를 요청하는 업무를 담당한다. 즉, 이 말을 조금 더 풀어서 생각하자면 스토리지 엔진이 데이터를 통신하기 위한 API 역할을 한다고 생각하면 쉬울 것 같다. 이것 또한 내가 생각했던 것과 같다.
이제 여기서 가장 중요한 말을 하고 싶다.
스토리지 엔진에서 받아온 데이터를 사용자 요청에 맞게 처리하고 접근 제어, 쿼리 캐시, 옵티마이저 등의 역할을 수행한다. 사용자와 MySQL 사이에서 발생하는 데이터 처리 프로세스를 전부 담당한다고 생각하면 된다.
이해가 되는가? 조금 더 이해하기 쉽도록 예를 보여주자면 Jojn, Group By, Order By, Function/Procedure, Trigger 와 같은 것 또한 서버엔진에서 모두 맡아서 한다고 생각하면 된다.
이제 서버엔진을 봤으니 스토리지 엔진을 볼 차례다. 이 스토리지 엔진은 물리적인 저장장치에서 테이터를 읽어오는 역할을 한다. 이게 되게 웃긴 것은 다른 DBMS와는 다르게 플러그인 처럼 작동하기 때문에 여러 종류의 스토리지 엔진을 간단하게 설치하여 바로 사용할 수 있다. 예를 들면 InnoDB, MyISAM과 같은 엔진이 있다.
MySQL에서 스토리지 엔진이란 무엇인가?
MySQL은 이러한 다양한 엔진으로 부터 다른 DBMS보다도 강력하다고 할 수 있다. 확장성과 유연함에서 말이다. 우리는 다른 엔진보다도 InnoDB를 엔진을 많이쓴다. 이유는 무엇일까?
InnoDB는 MySQL에서 유일하게 트랜잭션을 지원하는 스토리지 엔진이다. 그렇기에 많이 쓴다. 또한 그 경쟁자 MyISAM 같은 경우는 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 올린다는 것이 가장 성능에 차이가 있다는 것. 즉, InnoDB_Buffer_Pool_Size가 DB성능에 큰 영향을 주기도 한다.
그럼 너무나도 당연한게 InnoDB에서는 클러스터 인덱스를 사용할 수 있지만 MyISAM같은 경우는 클러스터 인덱스를 지원하지 않는다.
Archive 스토리지 엔진?? 나는 이 책을 읽으면서 처음으로 들어본 엔진이다. 책을 읽으면서 너무 쓸데없는 부분이 있거나 쓰지 않는 엔진같은 경우 정리를 하지 않지만 이 엔진 같은 경우에는 나중에라도 한번은 쓸 수 있을 것같아서 정리를 한다. 이 엔진 같은 경우에는 로그 수집에 적합한 스토리지 엔진이다. 데이터가 메모리상에서 압축되고 !! 이 상태로 디스크에 저장되기에 행 단위 잠금이 가능하고 UPDATE, DELETE를 사용할 수 없기 때문에 굉장히 로그와 같은 통계 프로젝트에 적합하다. 즉, 압축을 하므로 디스크에 대한 용량 또한 많이 줄일 수 있어서 좋다.
MYSQL은 데이터를 어떻게 처리 할까?
MySQL은 SQL을 병렬 처리하지 않는다... 음.. 우선 병렬처리는 하지 않는다. 모든 SQL을 단일 코어에서 처리한다.
우선 팩트만 말하면 MySQL 입장에서는 CPU 코어 개수를 늘리는 Scale-Out 보다는 단위 처리량이 좋은 CPU로 Scale-Up 하는 것이 훨씬 좋다. 이게 팩트다.
MySQL의 Join은 항상 Nested Loop Join 알고리즘으로 처리한다. 이러한 Nested Loop Join 같은 경우에는 처리할 데이터가 적어지면 수행 속도가 빠르지만 테이블 A나 테이블 B 중 하나라도 연산해야 할 데이터가 많아지면 쿼리 효율이 기하급수적으로 떨어진다.
이제 여기서 내가 알고 있던 지식은 드라이빙 테이블에서 드라이븐 테이블로 접근할 때, 드라이븐 테이블에서 인덱스가 없으면 풀스캔이 난다는 지식만 알고 있었고 만약 그렇지 않으면 풀스캔을 한다는 것만 알고 있었다. 이 책을 읽어보고 여기서 조금 더 추가를 하자면 3개의 for 반복문이 있고 하나의 테이블이 더 생성되며, 만약 드라이븐 테이블에서 마땅한 index가 없으면 3번째 테이블을 이용한 풀스캔을 한다는 것을 알았다.
그리고 조금 더 나아가자면 Block Nested Loop Join을 알았다. 즉, DB 내부에서 조금 더 성능을 높이기 위해서 테이블 조인 시 필요한 데이터를 일시적으로 메모리에 저장하여 효율적으로 데이터에 접근하는 것!
우선 이 부분은 읽어봐도 이해가 되지는 않지만, 드라이븐 테이블에 접근하는 빈도수를 더 적게 해주면서 성능이 좋아지는 것같다.
마치며
오늘 쉬는 날이기도 해서 가볍게 DB에 대한 책을 읽어보기 시작했다. 이 책은 작다. 그리고 페이지수도 적다. 출, 퇴근 시간에 버스에서 읽으려고 했는데 멀미가 나서 그 동안 읽지 못했다. 무튼.. 결론은 굉장히 작은 책이지만 재밌었다.
참고 : 실무 예제로 다가가는 MySQL 쿼리작성 MySQL 퍼포먼스 최적화 - 성동찬, 한빛미디어