MySQL 전체 구조 및 개요

MySQL은 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)이다.
여러 프로그래밍 언어에 드라이버와 API를 제공하며, 내부적으로 MySQL 엔진과 스토리지 엔진으로 구성되어 있다.

1. MySQL 엔진과 스토리지 엔진

MySQL 엔진

MySQL 엔진은 DBMS의 두뇌 역할을 한다.
커넥션 핸들러, SQL 파서(Parser), 전처리기, 옵티마이저 등이 포함되어 있으며,
SQL을 해석하고 최적화하여 실행 계획을 만든다.

스토리지 엔진

스토리지 엔진은 데이터를 실제로 저장하고 읽어오는 역할을 한다.
MySQL 엔진이 쿼리를 처리하고 스토리지 엔진에게 데이터를 읽고 쓰도록 요청한다.

하나의 MySQL 서버에는 하나의 MySQL 엔진이 존재하지만, 스토리지 엔진은 여러 개 존재할 수 있다.
테이블 생성 시 ENGINE=InnoDB와 같이 지정하면 해당 테이블은 지정된 스토리지 엔진을 사용한다.

대표적인 스토리지 엔진은 InnoDB와 MyISAM이다.

• InnoDB: 버퍼 풀(Buffer Pool) 사용
• MyISAM: 키 캐시(Key Cache) 사용

MySQL 5.7 이후 버전에서는 MyISAM을 사용할 이유가 거의 없으며,
InnoDB가 표준 스토리지 엔진으로 사용된다.

핸들러 API

MySQL 엔진의 쿼리 실행기가 스토리지 엔진에 데이터를 요청할 때 사용하는 인터페이스를 말한다.
즉, MySQL 엔진이 데이터를 읽거나 쓸 때 스토리지 엔진과 통신하기 위해 사용하는 API이다.

SHOW GLOBAL STATUS LIKE 'Handler%'

2. MySQL 스레딩 구조

MySQL은 포그라운드 스레드와 백그라운드 스레드로 나뉜다.

포그라운드 스레드 (클라이언트 스레드)

MySQL 서버에 접속한 클라이언트 수만큼 존재한다.
각 스레드는 클라이언트의 SQL 요청을 처리한다.

스레드 캐시(Thread Cache)가 존재하며,
시스템 변수로 캐시 크기를 조정할 수 있다.

InnoDB의 경우 포그라운드 스레드는 버퍼나 캐시까지만 작업하며,
디스크에 데이터를 실제로 쓰는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB에서는 다음과 같은 작업이 백그라운드로 실행된다.

• 인서트 버퍼 병합
• 로그를 디스크에 기록
• 버퍼 풀의 데이터를 디스크에 기록
• 데이터를 버퍼로 읽어오기
• 잠금(lock) 및 데드락 감시

MySQL 5.5 이후부터는 읽기 스레드, 쓰기 스레드의 개수를 조정할 수 있다.
일반적으로 읽기 스레드는 많을 필요가 없고,
쓰기 스레드는 2~4개 정도로 설정하는 것이 적당하다.

3. 메모리 구조

MySQL의 메모리는 글로벌 메모리 영역과 로컬(세션) 메모리 영역으로 나뉜다.

글로벌 메모리 영역

MySQL 서버 시작 시 운영체제로부터 할당되며, 모든 스레드가 공유한다.
클라이언트 수와 관계없이 고정된 크기로 존재한다.

대표적인 구성 요소는 다음과 같다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬(세션) 메모리 영역

각 클라이언트 스레드가 독립적으로 사용하는 메모리 영역이다.
쿼리 실행 시마다 임시로 사용되며, 다음과 같은 용도로 활용된다.

• 정렬 버퍼
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

플러그인 스토리지 엔진 모델

MySQL 플러그인과 컴포넌트 구조

MySQL은 플러그인(Plugin) 모델을 기반으로 동작한다.
인증 방식, 검색어 파서(parser) 등 여러 기능이 플러그인 형태로 개발되어 제공된다.

1. MySQL 엔진과 스토리지 엔진의 역할

MySQL에서 쿼리가 실행되는 과정은 대부분 MySQL 엔진에서 처리된다.
실제 데이터의 읽기와 쓰기 작업만 스토리지 엔진이 담당한다.

MySQL 엔진 내부의 주요 처리 순서는 다음과 같다.

• SQL 파서(Parser): 쿼리를 구문 분석
• SQL 옵티마이저(Optimizer): 최적의 실행 계획 수립
• SQL 실행기(Executor): 실행 계획을 수행

이후, 실행기의 요청에 따라 스토리지 엔진이 데이터를 읽거나 쓴다.
즉, SQL 파서 → 옵티마이저 → 실행기는 MySQL 엔진의 영역이고,
데이터 읽기/쓰기는 스토리지 엔진의 영역이다.

대부분의 데이터 접근은 1건의 레코드 단위로 처리된다.
스토리지 엔진을 변경하더라도 결과는 동일하며, 데이터 처리 방식만 달라질 뿐이다.

GROUP BY, ORDER BY 같은 복잡한 연산은 스토리지 엔진이 아닌
MySQL 엔진의 쿼리 실행기에서 처리된다.

현재 서버가 지원하는 스토리지 엔진은 다음 명령으로 확인할 수 있다.

결과의 주요 상태값은 다음과 같다.

• YES: 사용 가능
• DEFAULT: 기본 스토리지 엔진
• NO: 서버에 포함되어 있지 않음
• DISABLED: 서버에는 포함되어 있으나 파라미터 설정으로 비활성화됨

서버에 포함되지 않은 스토리지 엔진을 사용하려면 재컴파일이 필요하지만,
서버가 플러그인 구조로 준비되어 있다면 플러그인 형태의 스토리지 엔진을 추가로 설치할 수 있다.

2. 플러그인 구조

플러그인은 MySQL의 기능을 확장하기 위해 제공되는 구조이다.
스토리지 엔진뿐 아니라 인증, 복제, 보안 등 다양한 기능이 플러그인으로 제공된다.

하지만 기존 플러그인 구조에는 다음과 같은 한계가 있다.

1. 플러그인은 MySQL 서버와만 통신 가능하며, 플러그인 간 직접 통신이 불가능하다.
2. 플러그인은 MySQL 서버 내부 변수나 함수를 직접 호출하므로 캡슐화가 이루어지지 않아 안전하지 않다.
3. 플러그인 간 의존 관계를 설정할 수 없어 초기화 순서를 제어하기 어렵다.

3. 컴포넌트 구조

MySQL 8.0부터는 이러한 플러그인 구조의 한계를 개선하기 위해 컴포넌트(Component) 개념이 도입되었다.
컴포넌트는 기존 플러그인보다 더 안전하고 모듈화된 구조를 가진다.

컴포넌트는 다음과 같은 특징을 가진다.

• 컴포넌트 간 통신 가능
• 캡슐화된 인터페이스 제공
• 의존성 관리 가능

예를 들어, 비밀번호 검증 기능(validate_password) 은
기존 플러그인에서 컴포넌트 형태로 변경되어 제공된다.

이처럼 MySQL 8.0부터는 핵심 보안, 인증, 관리 기능들이
점차 컴포넌트 기반으로 전환되고 있다.

4. 쿼리 실행 구조

MySQL의 쿼리 실행 순서는 다음과 같다.

1. 쿼리 파서(Parser)
   SQL 문을 토큰 단위로 분리하고 트리 형태로 구조화한다.
   문법 오류는 이 단계에서 감지된다.
2. 전처리기(Preprocessor)
   파서 트리를 기반으로 구조적 문제를 검사한다.
   테이블, 칼럼, 권한 등의 존재 여부를 확인한다.
3. 옵티마이저(Optimizer)
   쿼리를 가장 효율적으로 실행할 방법(실행 계획)을 결정한다.
4. 실행 엔진(Execution Engine)
   옵티마이저의 계획에 따라 스토리지 엔진에 데이터를 요청한다.
5. 핸들러(스토리지 엔진)
   실제 데이터를 디스크에 저장하거나 읽는다.

쿼리 캐시

MySQL 5.7 버전까지는 SQL 실행 결과를 메모리에 캐싱하는 기능이 있었다.
하지만, 동시성 문제와 캐시 관리 오버헤드로 인해 8.0부터 제거되었다.

5. 스레드 풀

MySQL 커뮤니티 에디션은 스레드 풀을 지원하지 않는다.
엔터프라이즈 에디션 또는 Percona Server 플러그인을 통해 사용할 수 있다.

Percona Server의 스레드 풀은 CPU 코어 수만큼 스레드 그룹을 생성하며,
다음 변수를 통해 제어한다.

• thread_pool_size: 스레드 그룹 수
• thread_pool_oversubscribe: 초과 허용 작업 수
• thread_pool_stall_limit: 지연 허용 시간(ms)
• thread_pool_max_threads: 전체 스레드 최대 수

6. 트랜잭션 메타데이터 관리

MySQL 5.7까지는 메타데이터를 파일 기반으로 관리했다.
이 방식은 트랜잭션이 지원되지 않아 서버 비정상 종료 시 데이터 불일치가 발생했다.

8.0부터는 InnoDB 테이블 기반 메타데이터 관리로 개선되어,
모든 시스템 테이블이 InnoDB 엔진을 사용한다.
이로 인해 스키마 변경 작업이 트랜잭션 단위로 완전 성공 또는 실패로 처리된다.

7. InnoDB 스토리지 엔진 아키텍처

(InnoDB 아키텍처 구조도)

주요 특징

1. 프라이머리 키 클러스터링
   데이터가 PK 순서대로 저장되며, 세컨더리 인덱스는 PK를 주소로 사용한다.
2. 외래 키 지원
   InnoDB만 외래 키를 지원한다.
   부모-자식 관계 확인 시 잠금이 여러 테이블에 걸리므로 데드락에 주의해야 한다.
   foreign_key_checks 옵션을 일시적으로 비활성화하면 데이터 적재 속도를 높일 수 있다.
3. MVCC (Multi Version Concurrency Control)
   언두 로그(Undo Log)를 이용해 잠금 없는 일관된 읽기를 제공한다.
4. 자동 데드락 감지
   InnoDB는 데드락 감지 스레드를 통해 주기적으로 잠금 그래프를 검사한다.
   언두 로그가 적은 트랜잭션이 롤백 대상이 된다.
5. 자동 복구 기능
   MySQL 시작 시 InnoDB는 자동으로 복구를 수행한다.
   문제가 발생하면 innodb_force_recovery 변수를 설정해 복구할 수 있다.

8. InnoDB 버퍼 풀

버퍼 풀은 디스크의 데이터와 인덱스를 메모리에 캐싱하는 공간이다.
쓰기 작업을 지연시켜 일괄 처리하는 버퍼 역할도 한다.

구조

• LRU 리스트: 자주 접근된 페이지를 오래 유지
• 플러시 리스트: 변경된(Dirty) 페이지 관리
• 프리 리스트: 비어 있는 페이지 관리

버퍼 풀 크기는 innodb_buffer_pool_size로 설정하며,
5.7 이상부터는 동적 조절이 가능하다.

9. 리두 로그(Redo Log)

리두 로그는 비정상 종료 시 데이터 유실을 방지하는 복구용 로그이다.
데이터 변경 전 로그에 기록해두고, 서버 재시작 시 이를 기반으로 복구한다.

8.0부터는 리두 로그를 비활성화할 수 있어 대량 데이터 적재 시 속도를 높일 수 있다.

10. 어댑티브 해시 인덱스

InnoDB가 자주 접근하는 데이터를 자동으로 인덱싱하는 기능이다.
상황에 따라 성능에 도움이 되지 않을 수 있으며,
innodb_adaptive_hash_index 변수로 활성화 또는 비활성화할 수 있다.

• 활성화 권장: 데이터가 메모리에 대부분 존재하고 동등 비교가 많은 경우
• 비활성화 권장: 조인, LIKE 검색, 랜덤 I/O가 많은 경우

11. MyISAM 스토리지 엔진

MyISAM은 과거 MySQL 기본 엔진이었으나 현재는 거의 사용되지 않는다.
다음과 같은 제약이 있다.

• 레코드 단위 락 없음 (테이블 단위 락만 존재)
• 트랜잭션 미지원
• 외래 키 미지원

읽기 전용 용도 외에는 InnoDB가 항상 우수하다.

12. MySQL 로그 파일

에러 로그

서버 구동 중 발생하는 오류, 경고, 종료 메시지를 기록한다.

제너럴 쿼리 로그

모든 쿼리 실행 내역을 기록한다.
쿼리 실행 중 오류가 발생해도 로그에는 기록된다.

슬로우 쿼리 로그

long_query_time 변수에 설정된 시간보다 오래 걸린 쿼리를 기록한다.
쿼리 튜닝에 활용된다.

이상이 MySQL의 주요 구조와 구성 요소이다.
MySQL은 버전이 올라가면서 내부 구조가 단순 파일 기반에서 트랜잭션 기반으로 발전했고,
특히 InnoDB를 중심으로 안정성과 성능이 크게 향상되었다.

다른 DBMS와 다른점 : MySQL은 단순히 사용자의 아이디뿐만 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인

MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할도 도입되어 각 사용자의 권한으로 미리 준비된 권한 세트를 부여하는 것도 가능

-->

GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user1'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user2'@'localhost';

전에는 이렇게 일일히 권한 부여했음

지금은?

CREATE ROLE developer; // 롤 생성

GRANT SELECT, INSERT, UPDATE ON mydb.* TO developer; // 역할 권한 부여

GRANT developer TO 'user1'@'localhost';
GRANT developer TO 'user2'@'localhost'; // 사용자에게 역할부여

SET DEFAULT ROLE developer TO 'user1'@'localhost'; // 역할 활성화

이렇게 롤 생성해서 유저에게 그 권한을 자동으로 바로바로 부여 가능

MySQL 계정은 '아이디 + 호스트' 조합으로 식별된다

'사용자명'@'호스트명'

--> 

'svc_id'@'127.0.0.1'과 'svc_id'@'%'는 서로 다른 계정

• 클라이언트의 IP 주소(192.168.0.10) 를 확인
• 접속 가능한 계정 중 “가장 구체적인 것(=범위가 좁은 것)” 을 선택
• 그래서 svc_id'@'% 비밀번호 사용하면 실패  svc_id'@'127.0.0.1 비밀번호 사용해야함

MySQL 8.0버전부터 SYSTEM_USER 권한 유무에 따라 시스템 계정과 일반계정으로 구분된다.

시스템 계정

• 다른 계정 관리 : 다른 사용자 생성, 삭제, 권한 부여/회수 가능
• 다른 세션 강제 종료 : (다른 사용자가 실행 중인 쿼리나 세션을 종료 가능 (KILL QUERY, KILL CONNECTION)
• 스토어드 프로그램에 DEEFINER 지정 가능 : 트리거나 프로시저를 만들 때 DEFINER로 자신이 아닌 다른 사용자 계정을 지정 가능
• 서버 관리 작업 수행 가능 : 예: 서버 변수 변경, 복제(Replication) 제어 등

내장 시스템 계정들 : 내부 기능을 위해서만 사용

• mysql.infoschema : INFORMATION_SCHEMA 관련 내부 작업 수행
• mysql.session : 백그라운드 스레드, 복제, 쿼리 로그 등 내부 세션에 사용
• mysql.sys : sys 스키마(성능 보기, 관리용 뷰) 실행용

-> MySQL 내부에서만 사용되며 직접 로그인 불가능
보안상으로도 안전하게 account_locked = 'Y' 로 설정되어 있어서 접속 시도 시 에러

SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
+------------------+-----------+----------------+
3 rows in set (0.01 sec)

MySQL 5.7 버전 까지는 GRANT 명령 만으로 권한 부여와 동시에 계정 생성까지 가능했다.

GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

MySQL 8.0부터는 더 안전하게 관리하기 위해 계정 생성과 권한 부여를 완전히 분리

• CREATE USER : 계정 생성
• GRANT : 권한 생성

CREATE USER 'user'@'%'
IDENTIFIED WITH 'mysql_native_password' BY 'password'
REQUIRE NONE
PASSWORD EXPIRE INTERVAL 30 DAY
ACCOUNT UNLOCK;

• IDENTIFIED WITH
  어떤 인증 방식을 쓸지, 그리고 비밀번호를 뭘로 할지를 지정.
  MySQL 8.0부터는 기본 인증 방식이 Caching SHA-2 Authentication으로 변경.
  (이전의 mysql_native_password보다 보안이 강함)
• REQUIRE
  SSL/TLS 암호화 채널을 반드시 쓸지 결정
  “REQUIRE NONE”은 암호화 없이 연결하겠다는 뜻.
  다만, Caching SHA-2 방식을 쓰면 자동으로 암호화된 채널로 연결.
• PASSWORD EXPIRE INTERVAL 30 DAY
  비밀번호를 30일마다 바꾸게 강제.
• PASSWORD HISTORY / REUSE INTERVAL
  예전에 썼던 비밀번호를 일정 기간 동안 재사용하지 못하게 하는 기능.
  예를 들어 PASSWORD HISTORY 5는 “최근 5번 쓴 비밀번호는 다시 못 씀”을 의미
• ACCOUNT LOCK / UNLOCK
  계정을 잠그거나 품.
  보안상 문제가 생긴 계정은 잠가두면 로그인할 수 없음.

MySQL은 기본적으로 “길이”나 “문자 조합”만 검사했지만,
8.0부터는 비밀번호 정책을 세분화할 수 있는 컴포넌트가 들어왔음

-> validate_password 컴포넌트.

이걸 설치하려면:

이제 비밀번호의 복잡도를 검사가능.

• LOW → 길이만 확인
• MEDIUM → 숫자, 대소문자, 특수문자 조합 확인
• STRONG → 위 조건 + “금칙어(금지된 단어)” 포함 여부도 검사

금칙어 목록을 직접 등록가능.

이렇게 하면 금칙어 파일을 참조해서
비밀번호에 특정 단어(예: “password”, “admin” 등)가 들어가면 거부.

이중 비밀번호 (Dual Password)

MySQL 8.0부터 한 계정에 비밀번호를 두 개까지 설정할 수 있다.

• 최근 비밀번호: Primary
• 이전 비밀번호: Secondary

둘 중 하나만 맞아도 로그인할 수 있다.
비밀번호 변경 시 서비스 중단 없이 교체할 수 있다.

권한 (Privilege)

사용자가 수행할 수 있는 작업을 정의한다.

• 글로벌 권한: 서버 전체에 영향을 미침 (예: SUPER, CREATE USER, SHUTDOWN) → ON *.*으로 지정
• 객체 권한: 특정 데이터베이스나 테이블에만 적용 (예: SELECT, INSERT, UPDATE, DELETE) → 대상 명시 필요
• 동적 권한: 실행 중 동적으로 생성되는 권한 (복제, 플러그인 관리 등)

권한 부여 예시:

역할 (Role)

MySQL 8.0에서 새로 도입된 기능으로, 여러 권한을 묶어 하나의 세트로 관리한다.

역할 생성 및 권한 부여 예시:

이렇게 하면 user1, user2는 developer 역할을 통해 해당 권한을 자동으로 가진다.