버전별 차이
4.0
InnoDB의 BLOB와 TEXT 타입에 대한 인덱스 지원
JOIN DELETE (Multiple Delete) 도입
JOIN UPDATE (Multiple Update) 도입
Memory 스토리지 엔진에서 NULLABLE 컬럼의 인덱스 지원
UNION 집합 연산 도입
SQL_CALC_FOUND_ROWS 힌트와 FOUND_ROWS() 함수 구현
Query Cache 도입
4.1
SubQuery 도입
CREATE TABLE ... LIKE ...
GROUP_CONCAT() 구현
유니코드(UTF8, UCS2) 지원
GIS 관련 기능(Spatial extension) 지원
ALTER DATABASE 명령 지원
DUAL 테이블 내부 지원(타 DBMS와의 호환성 유지)
"SELECT 1" 명령과 "SELECT 1 FROM DUAL" 명령은 동일
Memory 스토리지 엔진에서 B-Tree 허용
EXPLAIN EXTENDED 구현
CVS 스토리지 엔진
Blackhole 스토리지 엔진
5.0
StoredRoutine (Procedure,Function) 도입
CURSOR 도입
Archive 스토리지 엔진
INFORMATION_SCHEMA 딕셔너리 데이터베이스 도입 (ANSI 표준)
VIEW 도입
TRIGGER 도입
FEDERATED 스토리지 엔진
5.1
Plugin API 도입
EXPLAIN PARTITIONS(파티션 테이블의 실행 계획) 지원
RBR(Row Based Replication) 복제 모드
Partition pruning 기능
Mixed 복제 모드
general_log 파라미터
SHOW PROFILE
InnoDB Plugin
5.6
Security improvements.
Partitioning 향상 (최대 8192개)
복제, 로깅 향상 (트랜잭션 복제 지원)
옵티마이저 향상
5.7
보안 개선(계정 잠금 및 해제 지원)
RENAME INDEX 지원
다중 트리거 지원
JSON Support
Multi-source Replication 등 복제기능 향상
Optimizer Cost Model
SYS 스키마 지원
쿼리 캐시 지원 중단
8.0
데이터사전 : 데이터베이스 개체에 대한 정보를 저장하는 트랜잭션 데이터 사전을 통합.이전 MySQL 릴리스에서는 사전 데이터가 메타 데이터 파일과 비 트랜잭션 테이블에 저장
보안 및 계정관리 향상
인덱스 내림차순 지원
Invisible 인덱스 지원
JSON 향상 (JSON TABLE FUNC, JSON Aggregation FUNC, JSON Merge Func..)
5.7 대비 최대 약 2배의 성능
MySQL 전체 구조
간략한 설명
MySQL 엔진 : 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러. 성능 향상을 위한 캐시나 버퍼풀과 같은 보조 저장소도 포함.
스토리지 엔진 : 실제 데이터를 디스크에 저장하거나 디스크에서 데이터를 읽어오는 작업의 처리.
핸들러 API : MySQL 엔진이 스토리지 엔진에 READ/WRITE를 요청할 때 사용. SHOW GLOBAL STATUS LIKE 'Handler%';를 통하여 작업 크기 확인 가능
스레딩 구조
MySQL 서버는 프로세스가 아닌 스레드를 기반으로 작동. 크게 포그라운드와 백그라운드로 나누어짐.
포그라운드 스레드(클라이언트) : 각 사용자가 요청한 쿼리 문장을 수행하는 역할
사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 스레드 캐시(Thread Pool)로 돌아가는데, 이 때 스레드 캐시에 일정 개수 이상의 대기 스레드가 있으면 해당 스레드는 종료시킴.
SHOW VARIABLES LIKE 'thread_cache_size'; 로 스레드 캐시의 스레드 수를 설정 가능.
데이터를 데이터 버퍼나 캐시로부터 가져오며, 해당 부분에 데이터가 없는 경우에는 디스크나 인덱스로부터 데이터를 읽어와서 작업을 처리.
백그라운드 스레드 : InnoDB의 경우에는 여러 작업이 백그라운드로 처리.
인서트 버퍼를 병합하는 스레드, 로그를 디스크로 기록하는 스레드, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 잠금이나 데드락을 모니터링하는 스레드, 이 모든 것을 총괄하는 메인 스레드 등이 있음.
가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 하는 쓰기 스레드.
쿼리 처리 실행 과정
PARSER : 사용자 요청으로 들어온 쿼리를 토큰으로 분리해 트리 형태의 구조로 만드는 작업 수행. 기본 문법 오류 검출.
OPTIMIZER : 실행 계획 결정.
실행엔진 : 계획을 바탕으로 각 핸들러에게 요청하는 역할.
STORAGE ENGINE : 실행 엔진의 요청에 따라 데이터를 디스크로 저장 및 읽는 역할.
스토리지 엔진
InnoDB 아키텍처
특징 :
레코드 기반 lock을 사용하여 높은 동시성 및 안정적 성능.
기본키에 의한 클러스터링 → pk에 의한 레인지 스캔의 처리 속도가 빠름
잠금이 필요없는 일관된 읽기 → MVCC 기술을 이용해 락없이 READ 수행.
※MVCC : UNDO LOG를 통해 하나의 레코드에 대해서 2개의 버전이 유지되고 보여지는 구조
외래키 지원
자동데드락 감지 : 감지된 데드락은 ROLLBACK이 가장 용이한 트랜잭션을 자동적으로 강제 종료함.
자동화된 장애 복구
오라클 아키텍처와 유사
UNDO LOG : UPDATE 또는 DELETE와 같은 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳.
INSERT BUFFER : INSERT 또는 UPDATE 될 때 인덱스 업데이트 작업이 필요한 경우 사용하는 임시 메모리 공간.
REDO LOG : 커밋된 데이터 변경을 순차적으로 디스크에 기록하기 위한 로그 파일.
LOG BUFFER : REDO LOG의 버퍼링에 사용되는 공간
Memory(HEAP STORAGE ENGINE)
데이터의 크기가 작고 아주 빠른 처리가 필요한 경우에 적합한 엔진.
고정길이 칼럼만 지원. LOB타입 지원 X. 해시 인덱스 사용. 임시 테이블 용도로 자주 사용됨.
NDB 클러스터(Network Database)
가용성에 집중한 구조
InnoDB 잠금 및 트랜잭션 모델
적용 요소에 따른 LOCK 분류
Share, Exclusive, Intent 락 제공.
LOCK 사이 충돌 여부
| X | IX | S | IS | |
| X | Y | Y | Y | Y |
| IX | Y | X | Y | X |
| S | Y | Y | X | X |
| IS | Y | X | X | X |
상황에 따른 LOCK 분류
Record Lock : primary key 또는 unique index (multi-column unique index 포함)로 조회해서 하나의 인덱스 레코드(=row)에만 lock을 거는 것
Gap Lock : 실제 존재하는 인덱스 레코드에 락을 거는것이 아니고 범위를 지정하기 위해 인덱스 레코드 사이의 범위(gap)에 락을 거는 것
Next-Key Lock : RECORD LOCK과 GAP LOCK이 복합적으로 사용되는 것
Insert Intention Lock : 여러개의 트랜잭션들이 gap 안의 다른 위치에 INSERT를 동시 수행할 때 기다릴 필요가 없도록 하는것이 목적
Auto-INC lock : 여러 트랜잭션이 동시에 실행될때 AUTO_INCREMENT 컬럼의 값을 일관성 있게 증가시키기 위해 필요한 lock
격리수준은 다음과 같이 4가지 존재
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ(default), SERIALIZABLE
데드락
상태확인 방법 : SHOW ENGINE INNODB STATUS;
InnoDB VS MyISAM
| InnoDB | MyISAM | |
| 구조의 복잡성 | Complication | Simple |
| 트랜잭션 지원 | O | X |
| Locking 단위 | 행 단위 | 테이블 단위 |
| 기능 지원 | Index B-tree, Clustered | B-tree, R-tree, Full-text |
| 캐쉬 | Data/Index | Index |
| 외래 키 | O | X |
MyISAM
5.5 이전 버전까지 기본 엔진.
데이터 모델 디자인이 단순하여 속도는 InnoDB보다 빠름.
데이터 저장에 실제적인 제한이 없으므로 효율적으로 저장
단순한 Select 작업 속도가 빠름(Order by등 정렬이 추가되면 느려짐)
Full-text 인덱싱이 가능하여 복잡한 내용의 검색이 가능.
Insert, update, delete 의 속도가 table 단위 락으로 인하여 느림
무결성에 대한 보장X
로그파일
슬로우 쿼리 로그 파일
정상적으로 완료된 쿼리만 기록.
long_query_time 에 설정된 이상의 시간이 소요된 쿼리가 기록됨.
show variables like 'long_query_time';
출력 내용
Time : 쿼리가 종료된 시점.
User@Host : 쿼리를 실행한 사용자 계정.
Query_time : 쿼리가 실행되는데 걸린 전체 시간.
Rows_examined : 쿼리가 처리되기 위해 접근한 레코드의 건수
Rows_sent : 클라이언트에 보낸 레코드 수
에러 로그 파일
MySQL 구동과 모니터링, 쿼리 에러에 관련된 메시지가 포함.
log_error 파라미터를 정의하여 설정 가능.
일반 로그 파일
DB 서버에서 실행되는 모든 쿼리가 기록됨. 도중에 발생한 에러들도 기록.
바이너리, 릴레이 로그
DB의 쿼리중 DML만 기록. 마스터 서버에는 바이너리 로그, 슬레이브 서버에는 릴레이 로그가 기록.
복제
MMM
Manager 장비를 사용하여 가용성을 보장함.
Master(Active)와 Master(Passive) 양방향 복제.
장애 발생 시 Active Master의 접속을 차단한 후 Passive Master로 서비스의 접속을 넘김.
MHA
장애 발생시 자동으로 Master를 지정하는데 이때 가장 최신의 Slave DB를 Master로 승격 시킴.
