✅ SQL 엔진 VS 스토리지(storage) 엔진
MySQL 서버 = SQL 엔진 + 스토리지 엔진
흔히들
MySQL라고 부르지만, 우리가 사용하는 MySQL 서버는 크게SQL 엔진과스토리지 엔진으로 구성되어 있다.
-
SQL 엔진: 클라이언트의 요청 처리- SQL 구문 분석
- 실행 계획 수립 (SQL Optimizer)
- Connection Handler
- SQL parser
- 캐시, 버퍼
-
스토리지 엔진: 실제 데이터를 조작하는(DML) 작업 수행- 트랜잭션 지원 여부 결정
- 데이터 저장 방식 결정
MySQL의 스토리지 엔진
- InnoDB
- MyISAM
- MEMORY
-
현재는 거의 모든 곳에서
InnoDB를 사용하며, DEFAULT 값이다. -
예전에 사용되던
MyISAM의 경우, 트랜잭션을 지원하지 않는다. -
MEMORY는 말 그대로 메모리 DB이다. (disk X) -
다른 스토리지 엔진을 사용하려면 테이블 생성 시 명시해줘야 한다.
스토리지 엔진 확인 명령어
show ENGINES;📒 Undo Log (언두 로그)
트랜잭션 시작 전에
disk의 정보를 undo log에 넣어놓고 시작한다.
그리고 트랜잭션 실패 시 undolog를 disk로 다시 복구시킨다.
✅ MySQL의 thread 구조
foreground thread + background thread-> MySQL thread
📒 foreground thread
read 스레드(데이터 버퍼나 캐시 또는 직접 디스크나 인덱스 파일로 접근하여 데이터를 가져오는 스레드)
-
클라이언트/사용자가 요청한 쿼리 문장을 처리하는 스레드
-
최소 MySQL 서버에 접속한 클라이언트 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리를 처리한다.
-
사용자가 작업을 마치고 세션이 종료되면, 해당 스레드는 Thread Pool(캐시)로 돌아간다.
이때, 이미 Thread Pool에 일정 개수 이상의 대기중인 스레드가 있으면 Thread Pool에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 캐시에 존재하게 한다.
📒 background thread
write 스레드(InnoDB Buffer Pool 데이터를 disk에 기록하는 스레드)
InnoDB의 Background Thread는 멀티스레드 방식으로 동작하며, 성능의 향상을 위해 스레드 별로 역할이 나뉘어져 있다.
InnoDB에서는 다음과 같은 많은 작업들이 background thread에서 처리된다.
-
Insert buffer를 병합하는 스레드
-
로그를 기록하는 스레드
-
데이터를 버퍼로 가져오는 스레드
-
Lock, 데드락을 모니터링하는 스레드
-
모든 background thread를 총괄하는 Main 스레드
✅ MySQL에서 지원하는 여러가지 Lock
🔓 1. Global Lock (글로벌 락)
범위가 가장 넓으며,
SELECT를 제외한 모든 쿼리들이 대기 상태로 남는다
서버 전체에 영향을 미치기 때문에, 작업 대상이나 테이블이 다르더라도 동일하게 영향을 받는다.
사용 예시
mysqldump: 데이터베이스를 전반적으로 업데이트 한다면 다른 작업들이 종료되어야 함
명령어
FLUSH TABLES WITH READ LOCK;🔓 2. Table Lock (테이블 락)
특정 테이블에 대한 Lock이며, Table Lock에는 두가지 종류가 있다.
1. READ LOCK
- READ Lock이 걸려있는 테이블에 Write(쓰기) 불가능
LOCK TABLES {테이블_이름} READ;2. WRITE LOCK
- WRITE Lock이 걸려있는 테이블에 READ(읽기) 불가능
LOCK TABLES {테이블_이름} WRITE;3. Lock 해제
UNLOCK TABLES-
테이블에
update 쿼리를 실행하면 자동으로 lock이 발생한다. (MyISAM, MEMORY 엔진 기준) -
따라서 특별한 상황이 아니라면 해당 명령어를 직접 사용할 일은 거의 없다. (다른 작업에 큰 영향을 미치기 때문)
-
InnoDB의 경우에는 DML 쿼리에서는 lock이 작동하지 않고, DDL의 경우에만 영향을 미침InnoDB는 레코드 기반의 락(Lock)을 제공하기 때문에 단순 update 쿼리로는 테이블 락이 설정되지 않는다.
🔓 3. Named Lock (네임드 락)
해당 락을 가지고 있을때만 insert가 가능하며,
GET_LOCK()이라는 명령어로 임의의 문자열에 대해 잠금을 설정한다.
명령어
- 특정 문자열에 대한 Lock 획득
SELECT GET_LOCK({문자열}, {유지 시간});- Lock 확인
SELECT IS_FREE_LOCK({문자열});- Lock 해제
SELECT RELEASE_LOCK({문자열});자주 사용되지 않지만, 여러 클라이언트가 상호 동기화를 처리해야할 때, 또는 많은 레코드에 대해 복잡한 요건으로 변경하는 트랜잭션(
배치 프로그램등)에 유용하게 사용된다.
🔓 4. MetaData Lock (메타데이터 락)
데이터베이스
컬럼의 이름이나 구조를 변경하는 경우에 획득한다.
테이블 락처럼 별도의 명령어를 사용할 수는 없고, 테이블을 변경하는 등의 작업을 할 때 자동으로 가져왔다가 release 한다.
✅ InnoDB 레벨에서 지원하는 Lock
🔓 5. Record Lock (레코드 락)
select 해올때 다른곳에서 접근하지 못하도록 자동으로 걸어준다.
특정 레코드(record)에 lock을 거는 것이며, 해당 레코드에 대해 read/write를 할 수 없게 한다.
-
수정할 가능성이 있는 row에 다 lock을 자동으로 걸어준다. 이는 실행 계획에 의해 결정된다.
-
레코드 락은
read 시 innoDB가 자동으로 걸며,트랜잭션에서 건드리는 데이터에 모두 락을 건다.예를 들어, 1000만번째 데이터를 read하면 최악의 경우 1000만개에 락을 거는 것이다.
-> 인덱스 전략과 설계가 중요하다.
✅ 트랜잭션 격리 수준
해당 내용은 이전에 정리해놓은 적이 있어서 링크로 대체한다.
