MySQL 엔진

MySQL 서버

MySQL 서버는 MySQL 엔진과 스토리지 엔진으로 구성되어 있음

MySQL엔진은 커넥션 및 쿼리 요청을 처리하고, SQL 인터페이스 제공, SQL 파서, SQL 옵티마이저를 담당함

스토리지 엔진은 핸들러 API를 통해 스토리지 엔진에 읽기/쓰기 요청이 가능함

MySQL 스토리지 엔진 종류

• InnoDB 엔진
• MyISAM 엔진
• Memory 엔진
• Archive 엔진
• CSV 엔진
• Federated 엔진
  등등이 있고 위의 3개의 엔진이 가장 보편적으로 알려져있다.

InnoDB 엔진

트랜잭션 처리를 위해 고안됐는데 대부분의 경우 롤백되지 않고 완료되는(정상 종료되는) 짧은 트랜잭션이 많은 경우를 처리하기 좋게 되어있음, 유일하게 레코드 기반의 락(Lock)을 제공 -> 높은 동시성 처리와 안정적인 특징을가지고 있음
가장 많이 사용되는 스토리지 엔진으로 ACID 트랜잭션을 지원하는 대표적인 MySQL의 뛰어난 성능과 장애 복구 기능을 가진 엔진
결제 정보와 같이 정보의 무결성을 가져야하고 손실되면 안되는 중요한 데이터를 필요로 할 때 사용

 

Lock : 데이터의 일관성을 보장하기 위한 방법, Lock은 동시성을 제어/트랜잭션은 데이터의 정합성을 보장

여러 커넥션에서 동시에 동일한 자원을 요청할 경우, 순서대로 한 지점에는 하나의 커넥션만 변경할 수 있게 해줌

 

ACID : Atomic(원자성) 모두 실행, 모두 실행 x, Consistent(일관성) 성공적으로 트랜잭션이 완료되면 일관적인 DB상태 유지, Isolated(격리성) 트랜잭션 수행할 때 다른 트랜잭션이 끼어들지 못하게 보장, Durable(지속성) 성공적으로 트랜잭션이 수행되면 데이터베이스에 영구적으로 반영

 

InnoDB 장점

• 프라이머리 키값 순서대로 디스크에 저장되고 프라이머리 키에 의한 Range 스캔이 빠르게 처리됨
• 외래키가 지원된다. -> MyISAM이나 Memory 테이블에서는 사용안됨 -> 근데 외래키 자체가 여러가지 제약사항 탓으로 인해 실무에서는 잘 사용되지 않는다고 함
• 손실이나 장애로부터 데이터를 보호하기 위해 여러 메커니즘이 탑재되어 있음, MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 트랜잭션(Partial write) 등에 대한 일련의 복구 작업이 자동으로 진행됨
• 자동으로 데드락을 감지하여 레코드가 가장 작은 트랜잭션을 롤백해서 데드락을 풀어줌

데드락 : 두 개 이상의 작업이 서로 상대방의 작업이 끝나기 만을 기다리고 있기 때문에 결과적으로 아무것도 완료되지 못하는 현상

ex) 트랜잭션 A는 사원 테이블의 1번 사원에 대한 Update를 하고 트랜잭션 B는 2번 사원에 대한 Update 진행

1번 사원의 row는 트랜잭션 A가, 2번 사원의 row는 트랜잭션 B가 락을 하고 있는 상태인데 두 트랜잭션이 모두 끝나지 않은 상태에서 트랜잭션 A가 2번 사원 Update를 시도하고 트랜잭션 B가 1번 사원 Update를 시도하면 두 개의 트랜잭션이 모두 waiting에 들어가고 데드락에 빠지게 됨

MYISAM 엔진

과거 MySQL 스토리지 엔진
Transaction을 지원하지 않아 심플하고 빠르지만 동시성 제어가 어려움
Read 쿼리가 많은 환경에서 많이 사용됨

장점

• Key Cache는 InnoDB의 버퍼풀과 비슷한 역할을 함, 인덱스 대상으로만 작동
• 행 단위가 아닌 테이블 전체를 잠금, select쿼리 실행 중에도 테이블에 새 행을 삽입할 수 있음(동시 삽입)

MEMORY 엔진

메모리에 데이터를 저장하는 엔진
Transaction을 지원하지 않음, table-level locking을 사용
메모리를 사용하기 때문에 기본적으로 속도가 아주 빠르지만 데이터를 잃어버릴 위험이 있음
중요하진 않지만 빠른 처리가 필요한 임시 테이블로 많이 사용되는 편
ex) 조회용 또는 매핑용 테이블(우편번호를 주소에 매핑하는 테이블)
주기적으로 집계되는 데이터의 결과 캐시용
데이터 분석 시 중간결과저장용

MySQL 엔진 선택시 고려사항

응용프로그램에 트랜잭션이 필요하다면 InnoDB엔진이 가장 안정적
MyISAM은 트랜잭션이 필요없는 사이트, SELECT가 메인인 업무, 로깅과 같은 환경

쿼리는 어떻게 실행이 될까?

SQL 파서가 들어온 쿼리 문장에 대해서 문법을 확인하고 쿼리 문장을 적절한 단위로 분할하여 파서 트리를 만듦

-> 전처리기가 앞에서 만들어진 파서 트리를 보고 구조적으로 문제가 있는지 확인(없는 테이블, 컬럼인지, 권한이 있는지 등)

-> 옵티마이저가 요청받은 쿼리를 어떻게하면 가장 적은 비용으로 빠르게 처리할지 결정, 개발자는 옵티마이저가 더 나은 결정을 할 수 있도록 쿼리를 작성해야함 -> 옵티마이저에 의해 결정된 실행 계획대로 핸들러에게 지시 -> 스토리지 엔진이 실행 엔진이 내려준 지시대로 작업을 수행

 

옵티마이저가 하는 일 : Query Transformer 불필요한 조건을 제거하거나 복잡한 연산을 단순화 시키는 등의 작업 수행 가능, Estimator SQL을 실행할 때 소요되는 총 비용을 계산, Plan Generator Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획 도출

 

참고: https://nomadlee.com/mysql-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EC%A2%85%EB%A5%98-%EB%B0%8F-%ED%8A%B9%EC%A7%95/#Archive_%EC%97%94%EC%A7%84