참고) https://velog.io/@ddangle/Database-MySQL-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98
https://hoing.io/archives/2478
https://velog.io/@klm03025/Real-MySQL-%EC%A0%95%EB%A6%AC-4%EC%9E%A5-%EC%95%84%ED%82%A4%ED%85%8D%EC%B3%90
1. 아키텍처
- 두뇌역할을 하는 MySQL 엔진과 손발 역할을 하는 스토리지 엔진으로 나뉜다
- 스토리지 엔진은 핸들러 API를 만족하면 누구든지 서버에 추가해서 사용할 수 있다
- MySQL에서 기본제공하는 스토리지 엔진은 InnoDB와 MyISAM이 있다
1). MySQL엔진
- 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커텍션 핸들러와 SQL 파서 및 전처리기, 쿼리 최적화된 실행을 위한 옵티마이저가 중심을 이룬다
2). 스토리지 엔진
- 요청된 SQL문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분을 전담
- MySQL엔진은 하나지만 스토리지 엔진은 동시에 여러개를 가질 수 있다.
- 테이블이 사용할 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 해당 스토리지 엔진이 처리
- 디폴트는 InnoDB
- 각 스토리지 엔진은 성능 향상을 위해 버퍼 풀(Inno DB)나 키 캐시(MyISAM 스토리지 엔진)과 같은 기능이 내장하고 있다
CREATE TABLE test_table(fd1 INT, fd2 INT) ENGINE=INNODB;
3). MySQL 서버
- MySQL 엔진과 스토리지 엔진을 합친 것
- 프로세스 기반이 아닌 스레드 기반으로 동작하며
- 크게 포그라운드 스레드와 백그라운드 스레드로 구분된다
포그라운드 스레드 (클라이언트 스레드)
- MySQL서버에 접속된 클라이언트의 수만큼 존재한다
- 주로 각 클라이언트가 요청하는 쿼리 문장을 처리한다
- 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아간다
- 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시가 없는 경우는 직접 디스크의 데이터나 인덱스의 파일로부터 데이터를 읽어와서 작업을 처리한다
- InnoDB 테이블은 데이터 버퍼나 캐시까지만 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다
- MyISAM은 모두 포그라운드 스레드가 처리한다
백그라운드 스레드
- InnoDB에서는 읽기작업은 주로 포그라운드 스레드에서 처리되기 때문에, 읽기 스레드는 많이 설정할 필요 없지만, 쓰기 스레드는 많은 작업을 백그라운드로 처리하기 때문에 여유롭게 설정
- 데이터 쓰기 작업은 JPA의 쓰기 지연 저장소처럼 지연될 수 있지만 읽기 작업은 지연불가
- InnoDB에서도
Insert, Update, Delete같이 데이터를 변경하는 작업은 디스크에 완전히 저장될 때까지 기다리지 않아도 된다
4). 메모리 할당 및 사용구조
글로벌 메모리 영역
- 모든 메모리 공간은 MySQL서버가 시작되면서 운영체제로부터 할당된다
- 포그라운드 스레드와 무관하게 하나의 메모리 공간만 할당하고 생성된 글로벌 메모리 영역이 N개라고 하더라도, 모든 스레드에 의해 공유된다
- 대표적인 글로벌 메모리 영역
- 테이블 캐시
- InnoDB 버퍼풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
로컬 메모리 영역 (세션메모리 영역)
- 포그라운드 스레드가 쿼리를 처리하는데 사용되는 메모리 영역
- 대표적으로 커넥션 버퍼와 정렬버퍼 등이 있다
- 사용자가 MySQL서버에 접속하면, MySQL 서버에서는 클라이언트 커넥션으로부터 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데,
클라이언트 스레드(포그라운드 스레드)가 사용하는 메모리 공간이라고 해서클라이언트 메모리 영역이라고도 한다- 각 클라이언트 스레드별로 독립적으로 할당되며, 절대 공유되어 사용되지 않는다
- 각 쿼리의 용도별로 공간이 할당되고 필요하지 않는 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수 있다
- 커넥션이 열려있는 동안 계속 할당된 상태로 남아있는 공간
- 커넥션 버퍼
- 결과 버퍼
- 쿼리를 실행하는 순간에만 할당했다가 해제되는 공간
- 조인 버퍼
- 소트 버퍼
5). 쿼리 실행과정
- 대부분 MySQL엔진에서 처리되고, 데이터 읽기/쓰기만 스토리지 엔진에서 처리
- MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용
- MySQL엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저앟도록 명령하려면 무조건 핸들러를 통해야 한다
- 실질적인
Group by나Order By등 복잡한 처리는 스토리지 엔진 영역이 아니라쿼리 실행기에서 처리된다 - 하나의 쿼리 작업은 여러 하위작업으로 나뉘는데 MySQL엔진에서 처리되는지 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야한다
쿼리 실행 구조
- 쿼리 파서
- 쿼리 문장을 쿼리로 분리해 트리 형태의 구조로 만들어낸다
- 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 오류 메시지를 사용자에게 전달
- 전처리기
- 파서트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 파악
- 접근권한및 객체 존재여부를 확인해서 거른다
- 옵티마이저
- 가장 중요한 과정
- 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하는 것을 결정하는 담당
- DBMS의 두뇌
- 실행 엔진
- 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유된다
- 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청을 입력으로 연결하는 역할
- 핸들러 (스토리지 엔진)
- MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할
2. InnoDB VS MyISAM
- 용도에 따라 스토리지 엔진을 결정하는데, InnoDB와 MyISAM테이블을 같이 사용할 경우, 조인시 주의
- 참고) https://velog.io/@gillog/DBInnoDB-VS-MyISAM
https://velog.io/@mooh2jj/Mysql-InnoDB-vs-MyISAM
1). InnoDB
- 트랜잭션처리가 필요하고 대용량 데이터를 다루는 부분에서 효율적
- 즉 데이터 변화가 많은 서비스에서 좋다
- BDB 스토리지 엔진은 트랜잭션에 안전한 테이블을 제공하는 트랜잭션-스토리지 엔진이다
- 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었다
- 메인 메모리 안에에 데이터 캐싱과 인덱싱을 위한 버퍼풀을 관리한다
- Os 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있다
- 높은 퍼포먼스가 필요한 대용량 사이트에 적합
- row level locking을 지원한다
- 풀텍스트 인덱스를 지원하지 않는다
2). MyISAM
- 트랜잭션 처리가 필용없고, Read only 기능이 많은 서비스일수록 효율적
- Select가 많은 서비스에 적합
- 전체문장 검색능력 뿐만 아니라 고-성능 스토리지 및 복구 기능을 제공한다
- 블로그나 게시판처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에 최적의 성능을 발휘
- 항상 테이블에 Row Count를 가지고 있기 때문에
select count(*)명령시 빠르고, select 명령시에도 빠른 속도를 제공한다 - MyISAM은 풀텍스트 인덱스를 지원하는데 그렇기 때문에 Read Only 기능이 많은 서비스일수록 MyISAM이 효율적
- 풀텍스트 인덱스: 검색 엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장
- 단점: row level locking을 지원하지 못해, select, insert, update, delete시 해당 table 전체에 Locking이 걸린다
-> 그래서 row의 수가 커질수록 CRUD 속도는 엄청나게 느려진다
3). 차이점
- InnoDB는 테이블과 인덱스를 테이블 스페이스에 저장하고 테이블 스피에스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있다
- MyISAM은 각각 테이블과 인덱스를 각각 분리된 파일로 관리
