Real MySQL 8.0 4장(1) - MySQL 구조

1. MySQL 전체 구조

 

MySQL 엔진

• 사용자 접속, 쿼리 요청을 처리, SQL 파싱 및 옵티마이저 담당하는 부분

스토리지 엔진

• InnoDB, MyISAM, Memory 등이 있다.
• 데이터를 실제로 어떻게 저장하고 읽어올지 담당하는 부분
• 각 테이블 마다 다른 종류의 스토리지 엔진을 사용하는것이 가능하다.

현재 테이블의 스토리지 엔진 확인하기

mysql> SHOW TABLE STATUS FROM test;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Persons | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2023-02-11 12:26:52 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.03 sec)

 

MyISAM 스토리지 엔진

• MySQL 5.5 버전 이전에 테이블 생성시 디폴트로 지정되던 스토리지 엔진
• 키 캐시 기능을 지원한다.

InnoDB 스토리지 엔진

• MySQL 5.5 버전 이후에 테이블 생성시 디폴트로 지정된 스토리지 엔진
• 캐 캐시 대신 버퍼풀을 지원한다.
• 그리고 무엇보다도 트랜잭션이 지원 되기 때문에 현재는 MySQL 사용한다고 하면 InnoDB 스토리지 엔진을 사용한다고 보아도 무방할 정도로 많은 곳에서 사용하고있다.

MySQL 을 사용하는 이유 = 트랜잭션 처리가 필요해서 = InnoDB 스토리지 엔진 사용

핸들러 API(핸들러 요청)

MySQL 엔진에서 Inno DB와 같은 스토리지 엔진에 데이터 읽기, 쓰기 요청을 헨들러 요청이라고 한다.

즉, MySQL 엔진은 이 헨들러 요청들을 통해서만 디스크로부터 데이터를 읽고, 쓸수있다.

이 핸들러 API를 통해 얼마나 데이터를 읽고 쓰기 위해 디스크에 왔다 갔다 했는지 분석 할 수 있다.

얼마나 많은 핸들러 요청이 있었는지 확인하기 - SHOW GLOBAL STATUS

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 647   |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 6869  |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 16    |
| Handler_read_first         | 61    |
| Handler_read_key           | 1922  |
| Handler_read_last          | 0     |
| Handler_read_next          | 4358  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 4046  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 350   |
| Handler_write              | 1302  |
+----------------------------+-------+
18 rows in set (0.11 sec)

 

2. MySQL의 스레딩 구조

MySQL 서버는 멀티 프로세스 기반이 아니라 멀티 스레드 기반으로 작동한다. 그리고 크게 포그라운드 스레드와 백그라운드 스레드로 나뉠수 있다.

MySQL의 포그라운드 스레드(=클라이언트 스레드, 사용자 스레드)

사용자의 요청을 처리하는 스레드

데이터를 읽는 작업을 처리한다. 디스크에 직접 데이터를 쓰는 작업은 백그라운드 스레드에서 일어남.

데이터 캐시나 버퍼에서 데이터를 읽어오며, 만약 캐시나 버퍼에 데이터가 없으면 디스크나 인덱스파일에 데이터를 직접 읽어와 처리하기도 한다.

현재 MySQL에 접속된 클라이언트의 수 만큼 포그라운드 스레드가 존재한다.

MySQL의 백그라운드 스레드(=클라이언트 스레드)

디스크에 데이터를 직접 기록하는 작업은 백그라운드 스레드에서 일어난다.

현재 로그나, 버퍼풀에 있는 데이터를 디스크에 기록

기록이외에 데이터를 버퍼로 읽어오거나, 락, 데드락 모니터하는 백그라운드 쓰레드도 존재한다.

MySQL(InnoDB)이 스레드를 포그라운드, 백그라운드로 나뉜 이유?

읽기 요청의 처리와 쓰기 요청의 처리를 분리하기 위함.

읽기 요청의 경우 바로 처리되어야 하지만, 쓰기 작업 같은 경우는 여러 요청을 모아서 한번에 처리해도 사용상의 문제가 되지 않기 때문이다.

 

3. MySQL이 사용하는 메모리 영역

MySQL 에서 사용하는 메모리 영역은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 나뉜다.

글로벌 메모리 영역

MySQL 시작시 운영체제로 부터 할당받는 클라이언트 수와 무관하게 할당받는 모든 스레드가 공유하는 메모리 영역

버퍼, 캐시를 위해 사용된다.

대표적으로 InnoDB의 버퍼풀이 있다.

로컬 메모리 영역 (=세션 메모리 영역, 클라이언트 메모리 영역)

포워드 스레드(=클라이언트 스레드)가 사용자의 쿼리를 처리하기 위해 사용하는 독자적인 메모리 공간

스레드간 공유되는 글로벌 메모리영역과 달리, 로컬 메모리 영역은 스레드간 공유되지 않는다.

대표적으로 커넥션 버퍼나 정렬이나, 조인할때 사용하는 버퍼 등이 있다.

커넥션 버퍼는 커넥션이 열려있는 동안 계속 메모리 공간에 남아있으며, 조인이나 정렬할때 사용되는 버퍼는 쿼리를 실행하는 순간에만 메모리공간에 할당 되었다가 해제된다.

 

4. MySQL의 플러그인과 컴포넌트

플러그인

MySQL에서 기본적으로 제공되는 기능 이외에 부가적인 기능을 더 제공하기 위한 방법으로 플러그인 모델을 사용한다.

InnoDB 또한 플러그인 형태로 제공되는 하나의 스토리지 엔진에 해당한다.

스토리지엔진 뿐만아니라 3장에서 살펴보았던, 사용자 인증을위한 Native Authentication, Caching SHA-2 Authentication과 같은 인증 모듈들도 전부 플러그인이다.

플러그인 확인 하기 - SHOW PLUGINS

mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
...

컴포넌트(MySQL 8.0부터)

MySQL 8.0부터 플러그인의 단점을 보완하기 위해 등장한 개념

플러그인은 어떤 단점이 있는데?

⇒ 플러그인들 끼리 통신이 안되고, 플러그인끼리의 상호 의존관계 설정이 안됨

⇒ 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에(캡슐화가 안되어있음) 안전하지 않음

MySQL 5.7에서 플러그인 형태로 제공 되던 서드파티 비밀번호 검증기능들도 8.0 버전 부터는 이제는 컴포넌트형태로 제공된다.

 

5. MySQL 쿼리 실행 구조

1. 사용자 요청

2. 쿼리 파서에서 트리형태로 쿼리 재구성 ⇒ 문법오류 걸러짐

3. 전처리기 ⇒ 쿼리 구조적 오류, 테이블, 컬럼이름 검증, 권한 검증

4. 옵티마이저 ⇒ 쿼리를 어떻게 빠르게 처리할지 결정

5. 쿼리실행기 ⇒ 옵티마이저에서 만든 계획대로 핸들러에게 실제 요청을 주고받음

6. 스토리지엔진 ⇒ 실제 데이터를 디스크로부터 읽고 쓰는 작업을 수행

 

6. 복제(Replication)

⇒ 16장에서 따로 다룸

 

7. 쿼리 캐시(8.0 버전부터 사라짐)

빠른 응답을 위해 SQL 실행 결과를 캐싱해두는 기능

캐시 저장결과가 실제 데이터와 달라지면 Invalidate 해야하는 문제가 발생하고 이 과정에서 성능저하 유발

⇒ 8.0 부터 완전히 사라지게 됨

 

8. 스레드풀(엔터프라이즈 에디션에서만 지원)

사용자의 요청을 처리하는 포워드 스레드의 갯수를 CPU가 최대한 잘 처리할 수 있는 수준으로 줄여서 동시에 처리되는 요청이 많더라도 한정된 스레드에서 처리 될 수 있도록 하는것이 목적

 

9. 트랜잭션 지원 메타데이터(8.0 버전 부터)

8.0 버전 부터는 테이블의 구조정보와 같은 메타데이터들이 별도 파일이 아닌, 트랜잭션을 지원하는 InnoDB 스토이지 엔진에 테이블 형태로 저장되도록 개선 됨

⇒ 스키마 정보가 파일로 저장되었을 경우에는 중간에 파일 쓰다가 에러나면 문제가 될수 있는데, 스키마 변경 작업중에 MySQL 서버가 비정상적으로 종료되더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리 된다.