MySQL Internal 살펴보기 - SELECT 수행과정

안녕하세요, 대담이 입니다!

저번 블로그에서 설명드린 MySQL 설치에 관한 내용을 이용해 뭐를 더 설명드릴까 하다가.. SELECT 수행 과정에 대해 설명드리는 것도 좋을 거 같아 이 부분에 대해 설명드리겠습니다.

 

저는 현재 Database Engineer로써 근무하고 있습니다.

데이터베이스의 특성 중 하나는 추상화(abstraction) 인데요, 이는 간단히 얘기해서 데이터베이스가 어떤 방식으로 데이터를 돌려주는지는 몰라도, 쉽게 이를 사용할 수 있다!입니다. 

그래도 운행하는 차에 문제가 생기면 자동차 엔지니어에게 수리를 요청하면 그 자동차 엔지니어는 차가 왜 운행할 수 없는 상태인지 진단할 수 있듯이, 데이터베이스 엔지니어라면 데이터베이스가 느린 원인을 진단을 통해 알아낼 수 있어야겠죠? 😃 

이를 위해 가장 많이 살펴보는 요소중 하나가 쿼리가 의도된 대로 수행되는지입니다.

 

이를 제대로 알아내기 위해서는 SELECT 쿼리의 수행 방식부터 제대로 파악하는 것이 중요합니다! 그럼 이전에 설치된 테이블을 이용하여 간단한 예제를 만들어 SELECT 쿼리의 수행과정에 대해 안내드려보도록 하겠습니다!

 

mysql> CREATE DATABASE test; 
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table test_tab(id int , val int, date_val datetime, char_val varchar(40), primary key (id)); # 테스트 테이블을 생성해줍니다.
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_tab(val, date_val, char_val) values(RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID());
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_tab(val, date_val, char_val) SELECT RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID() FROM test_tab;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
## ..반복합니다.
mysql> select COUNT(*) FROM test_tab; # 약 100만 건의 테스트 데이터를 입력 완료하였습니다.
+----------+
| COUNT(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.10 sec)

위의 구문을 이용해 랜덤한 데이터를 쉽게 만들어내실 수 있습니다. 이를 이용해 한 가지 쿼리를 해볼 텐데요, 

기본적인 쿼리의 모양을 한번 만들어 보겠습니다.

 

SELECT YEAR(date_val) as year, COUNT(*) as cnt
FROM test_tab
WHERE id BETWEEN 1 AND 10000
GROUP BY YEAR(date_val)
ORDER BY cnt DESC;
+------+------+
| year | cnt  |
+------+------+
| 2023 | 1866 |
| 2022 | 1854 |
| 2024 | 1783 |
| 2021 |  414 |
+------+------+
4 rows in set (0.00 sec)

위의 쿼리는 제가 테스트로 짜 본 쿼리입니다. 먼저 의미는 아신다는 가정하에 적고 있지만, id가 1부터 10000까지 가지는 테이블의 데이터 중, date_val의  row 카운트를 확인하는 쿼리입니다.

 

해당 쿼리를 나누어 어디부터 데이터베이스 엔진이 해석하는지 살펴볼까요?

 

1. FROM 절 : 먼저 어느 테이블을 가져와서 어느 테이블에 접근해야 할지 알아야 하기 때문에 FROM 절부터 확인합니다.

2. WHERE절 : 어느 테이블에 접근해야 하는지 알았으면, 어느 데이터를 가져와야 할지 알아야겠죠? 이를 위해 WHERE절에서 어떤 데이터를 가져오는지 체크하는 절차가 두 번째로 확인됩니다.

3. GROUP BY 절 : 데이터를 가져왔으면, 이번 쿼리에서는 집계가 포함되어있으니 집계해야겠죠? 이를 GROUP BY 절에 맞추어 진행합니다!

4. SELECT 절 : SELECT 절에서 집계한 데이터를 어떻게 표현할지(alias, AS로 표현한 별칭입니다.), 그리고 이 카운트는 얼마나 되는지 체크합니다.

5. ORDER BY 절 : 집계가 완료된 데이터를 제가 요청한 순서에 맞추어 정렬합니다.

 

이 순서를 알고 나면 뒤에 설명드릴 MySQL 엔진에서의 셀렉트 수행절차를 인터널로 살펴보시는데도 더 도움이 됩니다. 

 

MySQL Architecture (출처: MySQL Reference Manual)

위와 같은 MySQL 아키텍처를 통하여 SELECT 쿼리 처리 수행 절차에 대해 다시 설명드리겠습니다. 각 아키텍처의 역할은 차후 설명드리고, 또 운영방식에 대한 설명도 차후 다시 드릴 수 있도록 하겠습니다 😃

 

 1. 먼저, 쿼리가 들어오면, 이것이 Engine이 알아먹을 수 있는지 체크하는 Parser부터 들르게 됩니다! 여기서는 이 쿼리를 수행한 주체가 요청한 쿼리(여기선 SELECT)를 수행할 권한을 가졌는지, 그리고 문법에 맞게 작성된 쿼리인지 확인합니다.

2. 쿼리를 해석했다면, 아까 첫 번째로 수행되는 구문이 FROM이었죠? 일단 메모리 영역(Cache & buffers)에 접근하여, 해당 데이터가 존재하는지 확인합니다! 데이터가 있다면 바로 반환하지만(잠금에 따라 달라지지만, 이는 또 차후에 다루어보겠습니다!) 없을 때는 스토리지에 들러 해당 데이터를 캐시에 올리는 작업까지 진행합니다.

3. 캐시에 올라왔다면, 이를 쿼리를 요청한 클라이언트에 반환합니다. 반환 시에는 집계 등은 완성이 되지 않은 테이블에 저장된 그대로의 형태로 반환됩니다.

4. 클라이언트에서 해당 데이터를 받아, 집계 및 정렬을 수행 후, 반환합니다.

 

복잡하다면 복잡하고, 쉽다면 쉬운 SELECT 수행절차였습니다. SELECT 구문의 간단한 수행 뒤에는 이런 복잡한 일련의 과정이 Database 내부적으로 일어나고 있었다는 점, 알고 계셨나요? 파면 팔수록 재미있는 데이터베이스 엔진의 내부활동, 앞으로도 기대해주시면 감사하겠습니다!

 

그럼 오늘도 행복한 하루 보내시길 바라며, 지금까지 대담이었습니다!