[테코톡] MySQL 아키텍처 2, 참고 자료, 쿼리 실행 과정, 클러스터링, MVCC, Undo log, Redo log&commit, index lock, 풀스캔PK인덱스, 복합인덱스, 더티페이지, adaptive hash index

---

https://youtu.be/vQFGBZemJLQ - 우기, 자막

https://youtu.be/8PRkLItDwXQ - 릭, 자막

둘다 정리 함

 

---

https://youtu.be/vQFGBZemJLQ - 우기, 자막

참고자료 :

댓글 :

Q.제가 알고 있기론 락을 걸게되면 동시성에 문제가 생겨서 그것에 대한 보완방식이 MVCC방식으로 알고 있고, 이를 이용하면 락이 필요 없는걸로 알고 있는데 혹시 틀렸을까요?

A.말씀해주신 내용이 맞습니다! MVCC 방식 덕분에 락을 걸지 않고 여러 클라이언트가 동시에 레코드를 조회할 수 있습니다. 하지만 레코드를 수정할 경우는 락을 걸어줘야 합니다. 영상에서 설명드리는 락은 UPDATE시 발생하는 락 입니다.

 

---

 

---

이게 전체 아키텍처다. 크게 네가지

클라이언트

MySQL 엔진

MySQL 스토리지 엔진 - 밑에 그림보면 얘도 메모리구나

운영체제 파일시스템과 하드웨어 부분 - 이게 HDD야

 

---

첫번째는 MySQL 접속을 위한 클라이언트

MySQL은 대부분의 프로그래밍 언어에 대해 접속 API를 제공한다.

쉘에서도 쉘 스크립트를 통해 MySQL에 접속해서 이용할 수 있다.

 

---

다음은 MySQL의 두뇌, MySQL 엔진

클라이언트 접속과 SQL 요청을 처리한다.

쿼리 파서, 전처리기, 옵티마이저, 실행 엔진 등으로 이뤄져 있는데

이중에서 옵티마이저는 요청된 SQL문을 최적화해서 실행시키기 위해 실행 계획을 짜는 중요한 역할을 한다

MySQL 엔진을 두뇌에 비유한 이유는 옵티마이저 때문

 

---

MySQL의 손발을 담당하는 MySQL 스토리지 엔진

데이터를 실제로 디스크에 저장하거나

디스크에 저장된 데이터를 읽어오는 역할을 담당

 

MySQL엔진은 옵티마이저가 작성한 실행 계획에 따라서 스토리지 엔진을 적절히 호출해서 쿼리를 실행한다

MySQL엔진이 스토리지를 호출할 때 사용하는 API를 핸들러 API라고 한다

핸들러 API를 직접 구현해서 나만의 스토리지 엔진을 추가할 수도 있습니다

 

---

마지막으로 실제 테이블 데이터와 로그데이터를 파일로 저장하는 운영체제 파일시스템과 하드웨어 부분으로 나눠볼 수 있습니다

 

---

쿼리 실행 과정

방금 그 그림을 쿼리 실행 과정이 더 잘 표현된 오른쪽 그림으로 대체

 

---

사용자가 SQL 요청을 MySQL로 보내면 가장 먼저 쿼리캐시를 만난다.

쿼리 캐시는 쿼리 요청 결과를 캐싱하는 모듈

쿼리 캐시를 통해서 동일한 SQL 요청에 대한 결과를 즉시 빠르게 받을 수 있습니다.

하지만 쿼리 캐시는 캐싱하고 있는 데이터의 테이블이 변경 된다면 더 이상 쓸모 없어지게 된 캐싱 데이터를 삭제해야한다

이렇게 캐싱 데이터가 삭제될 때마다 쿼리 캐시에 접근하는 쓰레드에 Lock이 걸리는데 이는 심각한 동시 처리 성능 저하를 유발합니다.

 

따라서 MySQL 8.0부터는 쿼리 캐시가 완전히 삭제 되었습니다.

 

---

다음은 쿼리 파서

쿼리 파서는 기본적인 SQL 문장 오류 체크

그리고 SQL문장을 의미 있는 단위의 토큰으로 쪼갠 다음에 트리로 만든다

이 트리를 파스 트리라고 한다 MySQL은 내부적으로 이 파스트리를 사용해서 쿼리를 실행한다

 

---

전처리기

전처리기는 쿼리 파서가 만든 파스 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 검사

파스트리의 토큰을 하나씩 검사하면서 토큰에 해당하는 테이블 이름이낭 컬럼 등이 실제로 존재하는 값인지 체크, 접근 권한에 대해서도 체크

 

---

옵티마이저

옵티마이저는 SQL을 최적화해서 실행 시키는 쿼리 실행 계획을 만듦

옵티마이저가 SQL을 최적화 하는 방법이 2가지가 있음

규기최 : 저설명과같다

비기최 : SQL을 처리하는 다양한 방법을 마련해두고, 각 방법의 비용과 테이블 통계 정보를 통해서 실행 계획을 수립하는 방법

 

---

쿼리 실행 엔진

옵티마이저가 만들어준 쿼리 실행 계획에 따라서 스토리지 엔진을 적절히 호출해서 쿼리를 수행하는 역할을 담당

 

---

스토리지 엔진

쿼리 실행 엔진이 요청한대로 데이터를 디스크에 저장하거나, 디스크로부터 데이터를 읽어온다

대표적으로 InnoDB, MyISAM 스토리지 엔진이 있다.

스토리지 엔진은 플러그인 형태로 제공되기 때문에 사용자는 원하는 스토리지 엔진을 선택해서 사용할 수 있다

 

MySQL은 스토리지 뿐만 아니라 검색어 파서, 사용자 인증 모듈 등도 플러그인 형태로 제공합니다

 

플러그인에는 몇 가지 한계가 있는데, 플러그인끼리는 통신할 수 없고,

플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 캡슐화를 위반한다는 단점도 있다.

 

그래서 MySQL 8.0부터는 이러한 플러그인 아키텍처를 보완한 컴포넌트 아키텍처를 제공하게 됐다

 

---

이제 대표적인 스토리지 엔진인 InnoDB와 MyISAM에 대해서 더 자세히 알아보기

먼저 InnoDB 스토리진 엔진

쟤네들이 핵심 특징

 

---

PK에 의한 클러스터링은 PK를 기준으로 데이터를 묶어서 저장하는 것

다시 말해, PK 순서대로 레코드를 정렬해서 디스크에 저장하는 것

참고로 InnoDB에서는 PK를 통해서 데이터 파일에 접근한다

PK에 대한 인덱스를 자동으로 생성해주기도 한다

클러스터링을 하면 PK 기반 범위 검색 속도가 매우 빨라진다

PK 기준으로 데이터가 정렬돼서 묶여서 한 군데 저장 되어있기 때문

하지만 쓰기 성능은 저하, PK 값이 바뀌면 그에 따라 레코드의 물리적 순서도 하나씩 바꿔줘야 하기 때문

 

일반적인 웹 서비스는 쓰기보다 읽기 요청이 훨씬 많기 때문에 쓰기 성능 희생하고 읽기 성능을 얻는 클러스터링을 하는 것이 합리적

InnoDB에서 PK를 지정하지 않으면 내부적으로 PK를 자동 생성해서 클러스터링을 하는데, 이렇게 내부적으로 생성된 PK는 사용자가 직접 사용할 수 없다.

따라서 InnoDB에서 테이블을 설계할 때는 PK를 직접 설정해주는게 좋다

 

---

다음은 InnoDB가 지원하는 트랜잭션 관련 내용

InnoDB는 기본적으로 Commit, Rollback 기능을 제공

MVCC라는 기능도 제공

 

저 그림이 데이터베이스의 상태인데,

InnoDB 버퍼풀은 변경된 데이터를 디스크에 반영 하기전까지 잠시 버퍼링하는 공간

언두로그는 변경되기 이전 데이터를 백업 해두는 공간이라고 보면 된다

 

USER 테이블에 1번, 유재석, 독서인 레코드를 삽입하는 쿼리 실행하고 커밋 찍어보면

버퍼풀에 새로 삽입한 레코드가 생겼다

 

---

이 상태에서 유재석의 취미를 코딩으로 바꾸는 update쿼리를 날리면

버퍼풀에 있는 유재석 레코드의 취미는 코딩으로 바로 바뀌는데, 이전 취미였던 독서 칼럼 값은 id와 함께 언두로그에 복사됐다

 

---

이 상태에서 다른 트랜잭션이 유재석 레코드를 조회하면

데이터베이스에 설정된 트랜잭션 격리 수준에 따라 다르다.

READ_UNCOMMITTED라면 취미가 코딩인 유재석 레코드가 버퍼풀에서 조회  ▽커밋 안 된것 포함해서 최신 것 보여줘?

만약 READ_COMMITTED 또는 REPEATABLE_READ 또는 SERIALIZABLE이라면 언두 로그에 있는 변경되기 이전 데이터인 취미가 독서인 유재석 레코드를 조회한다  ▽아직 안될 수도 있으니까?

이렇게 트랜잭션 격리 레벨에 따라 조회되는 데이터가 달라지게 하는 기술을 MVCC라고 한다  ▽이게 MVCC구나 즉 REPEATABLE_READ이런게 MVCC인 거구나?

 

멀티 버전 컨커린시 컨트롤(Multi-Version Concurrency Control). 다양한 버전이 동시에 관리된다는 뜻

 

여기선 유재석 레코드 2개가 취미가 다른데, 버퍼풀과 언두 로그에서 동시에 관리되고 있음을 확인할 수 있다

 

MVCC를 통해 레코드에 잠금을 걸지 않고도 이렇게 트랜잭션 격리 레벨에 따라 일관된 읽기를 할 수 있다는 것이 중요

 

---

다음은 트랜잭션과 관련된 두 가지 로그에 대해

Undo Log : 변경되기 이전 데이터를 백업해서 트랜잭션 보장

예를들어 롤백을 한다면 Undo Log에 백업된 데이터를 그대로 복원해주면 된다

Undo Log는 트랜잭션 격리 수준도 보장. 방금 살펴봤듯이 트랜잭션 격리 수준에 따라 Undo Log에 있는 데이터를 반환해준다

 

Redo Log는 트랜잭션의 영속성을 보장해주는데, Redo Log에는 변경됐고, commit된 데이터를 백업한다.(▽둘다 백업이구나) 하드웨어 or 소프트웨어 문제로 MySQL이 비정상적으로 종료되면 Redo Log를 통해 데이터를 복원한다.  ▽Redo log는 하드디스크야?? 메모리가 아닌가보네?? 메모리는 꺼지면 복구 못 하잖아

 

---

InnoDB의 레코드 단위 잠금

데이터베이스에서 데이터를 변경할 때는 동시성 문제를 고려해서 레코드에 대한 접근을 막는다 이를 Lock이라고 함

InnoDB는 레코드 단위로 잠금을 걸어서 동시 처리 성능이 좋다

하지만 실제로는 레코드 그 자체를 잠그는 것이 아니라 인덱스 레코드를 잠그는 것

 

예를 들어 DB 상황 가정

레코드가 5000개 들어 있는 유저 테이블

테이블에는 성씨 칼럼인 '박'인 레코드가 300개 존재

박 병욱은 1개

성씨 칼럼에는 인덱스가 걸려 있다.

 

박 병욱 레코드의 취미 컬럼을 코딩->축구 로 바꾸는 UPDATE쿼리를 날릴거야

 

업데이트를 하려면 데이터베이스는 먼저 박병욱 레코드를 검색해야돼. 이때 성씨 인덱스 컬럼을 사용하게 됨.

이 상황에 어떤 레코드가 잠기게 될까요?

박병욱만?

그렇지 않다. InnoDB는 레코드 그 자체를 잠그지 않고 인덱스 레코드를 잠그는 방식으로 처리가 된다

다시 말해 업데이트할 레코드를 검색할 때 사용된 인덱스 레코드가 잠긴다. 그림처럼 검색에 사용된 박씨 인덱스 레코드가 모두 잠기고, 실제 박씨 레코드 300개도 모두 잠기는 것을 볼 수 있다

 

만약에 성씨 인덱스가 아예 없었더라면

박병욱 레코드를 찾기 위해서 기본으로 생성된 PK 인덱스를 사용하여 테이블 풀 스캔을 한다.

이 경우에도 검색에 사용된 모든 PK 인덱스가 잠긴다 = 5000개의 레코드가 전부 잠긴다

▽풀스캔을 할 때도 기본으로 생성된 PK 인덱스를 사용해서 하는거야??

 

만약 성씨와 이름에 대한 복합 인덱스를 생성했다면  ▽복합 인덱스 이렇게 생겼구나

 

이렇게 박병욱 레코드 한 개만 잠그게 됐을 것

 

이렇게 인덱스를 어떻게 설정 하는지에 따라 레코드 잠금 범위가 달라질 수 있으니 InnoDB를 사용할 때는 인덱스는 신중하게 설정하는 것이 좋다

 

---

InnoDB 버퍼풀에 대해 설명

버퍼풀은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱해두는 공간

쓰기 작업을 지연시켜서 일괄적으로 작업을 처리해주기도 한다

버퍼풀은 SQL요청 결과를 일정한 크기의 페이지 단위로 캐싱하는데

운영체제가 가상 메모리를 효율적으로 사용하기 위해 페이징을 하는 것처럼 데이터베이스도 테이블 데이터에 대해 페이징을 한다

InnoDB는 페이지 교체 알고리즘으로 LRU 알고리즘을 사용하고 있다

▽전혀 이해가 안되는 부분이네요~ 나중엔 되겠죠

 

버퍼풀은 쓰기 지연 버퍼로도 사용되는데, Insert, update, delete 명령으로 변경된 페이지를 더티페이지라고 부른다

InnoDB는 이 더티 페이지들을 모았다가 주기적으로 이벤트를 발생시켜서 한 번에 디스크에 반영한다

이렇게 변경된 데이터를 한 번에 모았다가 처리하는 이유는 랜덤 I/O를 줄이기 위해서

 

---

adaptive hash index

어댑티브 해시 인덱스는 인덱스키와 페이지의 주소값 쌍으로 구성된 인덱스

페이지에 빠르게 접근하기 위해 사용

어댑티브 해시 인덱스는 사용자가 자주 요청하는 데이터에 대해서 InnoDB가 자동으로 만들어줍니다

어댑티브 해시 인덱스를 통해 원하는 페이지에 빠르게 접근할 수 있으므로 쿼리를 더 빠르게 처리할 수 있다.

 

여기까지  InnoDB 스토리지 엔진의 특징

 

---

다음은 MyISAM 스토리지 엔진 아키텍처에 대해 간단히

클러스터링, 트랜잭션, 외래키를 지원하지 않는다

테이블 단위로 잠금을 걸기 때문에 동시 처리에 불리

MyISAM에도 InnoDB 버퍼풀과 같은 역할을 하는 키 캐시가 존재

버퍼풀과 달리 키 캐시는 인덱스만을 대상으로 동작하는 것이 특징

 

또한 MyISAM은 전문검색, 공간 좌표 검색 기능 등을 지원

 

---

옛날에는 MyISAM이 기본 스토리지 엔진으로 사용되는 경우가 많았는데

MySQL 5.5 버전부터는 InnoDB 스토리지 엔진이 기본 엔진으로 채택

하지만 MySQL의 시스템 테이블은 여전히 MyISAM을 사용했었다.

MySQL 8.0부터는 모든 테이블이 InnoDB 스토리지 엔진을 사용하게 되었고 MyISAM에서만 지원하던 전문 검색 기능이나 공간 좌표 검색 기능을 InnoDB에서도 제공하게 되었다.

 

InnoDB스토리지 엔진의 기능은 계속 개선, MyISAM은 그렇지 않고 있어서 이후 버전에서 MyISAM은 사용되지 않을 것으로 예상