MySQL의 구성 요소
MySQL은 쿼리 요청을 처리하는 서버 엔진과 실제로 데이터를 저장하는 스토리지 엔진으로 나뉜다.
서버엔진
- 클라이언트의 요청을 받아 SQL을 처리한다.
- DB가 SQL을 이해할 수 있도록 쿼리를 재구성하는 ‘쿼리 파싱’, 스토리지엔진에 데이터를 요청한다.
- 쿼리 캐시, 옵티마이징을 수행한다.
- Table Join, Group By, Order By 같은 일반적인 SQL 처리부터 Function/Procedure, Trigger, Constraint 같은 기능을 수행한다.
스토리지 엔진
- 서버엔진이 필요한 데이터를 물리 장치에서 가져오는 역할이다.
- 여러가지 스토리지 엔진이 존재하며, 플러그인 식으로 교체해가며 쓸 수 있다.
- InnoDB, MyISAM, CSV, MEMORY, ARCHIVE, FEDERATED, MRG_MYISAM, BLACKHOLE
주요 스토리지 엔진의 특징
| MyISAM | InnoDB | Archive | |
|---|---|---|---|
| 스토리지 제한 | 256TB | 64TB | None |
| 트랜잭션 | No | Yes | No |
| Locking 레벨 | Table | Row | Row |
| 인덱스 | B-Tree | B-Tree | No |
| Cache | Index | Data/Index | No |
| 파티셔닝 | Yes | Yes | Yes |
| Cluster Index | No | Yes | No |
| Foriegn Key | No | Yes | No |
스토리지 엔진
MyISAM
가장 오래된 스토리지 엔진이며 파일 기반 스토리지 엔진이다.
인덱스만 메모리에 올려서 처리한다. 데이터는 메모리에 적재하지 않고 바로 디스크로 접근해서 가져온다.
트랜잭션이 지원되지 않고, 테이블단위 잠금이 이루어진다. 트랜잭션이 지원되지 않아 보통 InnoDB를 많이 쓴다.
Full-text indexing, geo spatial indexing을 제공한다.
InnoDB
MySQL에서 유일하게 트랜잭션을 지원하는 스토리지 엔진이다. MVCC(다중 버전 동시성 제어 매커니즘; 여러 개의 복제본을 이용해서 하나의 데이터를 처리함으로써 서로의 READ/WRITE를 방해하지 않는 방식)를 제공한다.
인덱스와 데이터 둘 다 메모리에 올리기 때문에 InnoDB_Buffer_Pool_SIze가 DB 성능에 큰 영향을 미친다.
Primary Key는 Clustering Index(인덱스 순서로 데이터가 저장된 구조)로 구성된다.
Archive
로그 수집에 적합하다.
데이터가 메모리상에서 압축되고, 압축된 상태로 디스크에 저장된다.
한번 INSERT되면 UPDATE와 DELETE를 사용할 수 없고, 인덱스를 지원하지 않는다.
MySQL의 데이터 처리 방식
MySQL에서는 모든 SQL을 단일 코어에서 처리한다.
그리고 MySQL은 테이블 조인을 Nested Loop Join 알고리즘으로만 처리한다.
Nested Loop Join 알고리즘
조건 검색 결과값 하나하나를 테이블B와 비교하여 조인한다. ex. for문 안의 for문 형식이다.
DB 내부에서는 Block Nested Loop Join방식(조인 버퍼 개념)으로 처리하는데, 테이블 조인 시 필요한 데이터를 메모리(버퍼)에 일시적으로 저장해두고 한 번에 데이터에 접근한다.
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}SQL에서 WHERE 조건을 잘 이해하고 쓰자
묵시적 형변환 함정에 빠지지 말 것
조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 내부적으로 형변환되는 것을 말한다.
정수 타입이 문자열보다 우선순위가 높기 때문에 문자열과 정수값 비교 시 정수 타입으로 자동으로 형변환되어 처리된다. 만약 인덱스 필드에서 이 형변환이 일어난다면 모든 데이터를 묵시적으로 형변환하기 때문에 테이블 풀 스캔을 해야 한다. 옵티마이저는 문자열 인덱스밖에 모르기 때문에 심각한 성능 저하가 일어난다.
함수 사용
내장 함수, 또는 사용자 함수를 사용하는 것은 인덱스가 적용되지 않아 성능에 더 안좋을 수 있다.
LIKE 검색
대용량 테이블인 경우엔 위험할 수 있다.
LIKE 검색 시 ‘%’ 위치에 따라서는 칼럼에 해당 인덱스가 있을지라도 중간 또는 뒷부분부터 검색하면 의미가 없을 수 있다.
또 대부분의 DBMS에는 옵티마이저가 있는데, 옵티마이저는 데이터 분포도를 따져서 내부적으로 SQL을 최적화 한다. 만약 조회해야 할 데이터가 기준치 이상이라면, 옵티마이저가 인덱스를 먼저 읽고 다시 파일로 가는 것 보다 파일을 바로 읽는 것이 낫다고 판단해 DB Full Scan을 하기도 한다.
TODO
- LIKE 조건이 ‘검색어%’처럼 검색어가 앞 단에 있다면 데이터 분포도를 따져서 수행한다.
- LIKE 조건이 ‘%검색어’와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용해서 필요한 데이터 범위를 최대한 줄인다.
Transaction Isolation Level에 유의하자
MySQL에서 트랜잭션 Isolation 레벨은 기본적으로 ‘REPEATABLE-READ’이다.
이 상태에서 ‘INSERT’나 ‘CREATE’로 데이터를 처리하면 참조하는 테이블에 ‘테이블 단위 잠금’이 발생할 수 있다.
트랜잭션 격리 수준의 종류
- READ UNCOMMITTED 커밋 전 상태를 볼 수 있음
- READ COMMITED 커밋된 상태만 읽을 수 있음.
- REPEATABLE READ InnoDB의 기본 설정. SELECT 시 현재 데이터의 스냅샷을 만들어서 스냅샷을 조회. 동일 트랜잭션에서 데이터 일관성을 보장함. 데이터를 다시 읽으려면 트랜잭션을 다시 시작해야 한다.
- SERIALIZABLE 트랜잭션이 완료될 때 까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock(읽기만 가능)이 걸림.
인덱스는 적재적소에 배치하자
인덱스가 걸린 테이블에 아이템을 삽입한다면
InnoDB에서는 Primary Key 순서대로 데이터가 저장되기 때문에 InnoDB에서 Primary Key는 성능에 직접적인 영향을 준다. 데이터 삽입이 일어나면 전체 행의 순서들을 맞추기 위해 기존의 데이터들이 이동되므로 성능이 저하된다 (따라서 순차 삽입이 랜덤 삽입보다는 나음)
그렇다고 해서 Primary Key를 지정하지 않으면, InnoDB에서는 Primary Key를 선언하지 않아도 기본적으로 Primary Key를 다음과 같은 기준으로 생성하기 때문에 유의해야 한다.
- 인덱스 중 Unique 속성이 있는 Key를 Primary Key로 사용한다.
- Primary Key도 없고 Unique Key도 없으면 내부적으로 6Byte의 대체 Primary Key를 생성해서 사용한다.
불필요한 인덱스가 많으면?
CREATE TABLE `test_0_index` (
`i` int(10) unsigned NOT NULL AUTO_INCREMENT,
`j` int(10) unsigned NOT NULL,
`s` varchar(64) NOT NULL,
`d` datetime NOT NULL,
PRIMARY KEY (`i`),
INDEX idx_j(j),
INDEX idx_d(d),
INDEX cidx_jd(j,d),
INDEX cidx_dj(d,j)
);위와 같은 테이블에선 cidx_jd와 cidx_dj 인덱스에서 각 컬럼을 복합 인덱스 형식으로 구성했기 때문에 j 또는 d만으로 데이터를 조회하는 쿼리 요청이 와도 정상적으로 데이터를 빠르게 추출하므로 j와 d 컬럼의 인덱스는 불필요하다.
하지만 만약 d로 데이터를 검색한 후 결과를 i 컬럼 순서로 가져오는것이 목적이라면 cidx_dj가 오히려 필요없는 인덱스이다 → idx_d로 검색한 결과는 primary key 순서대로 나오지만, cidx_dj 인덱스에서 d로 검색한 결과는 j순서대로 나오기 때문이다.
인덱스 사이즈 줄이기
인덱스가 커지면 메모리 효율이 떨어지고 Disk I/O가 많아진다.
varchar 인덱스의 경우라면 crc32 같은 해시 함수를 이용해 크기를 줄이자.
테이블 파티셔닝
파티셔닝; 하나의 커다란 데이터를 여러 개의 데이터로 분할 저장함으로써 데이터 선택 효율을 높이는 기법이다.
제한 사항
- 파티셔닝 키는 Primary Key와 연관된 컬럼이어야 한다.
- 파티셔닝 사용 시 Unique Key 혹은 Foriegn Key와 같은 제약 조건을 추가할 수 없다.
- 파티셔닝 적용 시 풀텍스트 인덱싱(full text search에서 사용. 미리 매핑 정보를 저장.) 혹은 Spatial 인덱싱 기능은 사용할 수 없다.
- 데이터 조회 시 반드시 파티셔닝 키를 포함해야 하고, 그렇지 않으면 전체 데이터 파일을 스캔한다.
스토리지 엔진에서 고려해야 할 점
InnoDB 대신 다른 스토리지 엔진을 사용
InnoDB는 행 단위 잠금으로 처리(동시성 좋음) + 인덱스와 데이터를 모두 버퍼풀(메모리)에 저장하므로 디스크 I/O가 줄어든다.
하지만 반대로 인덱스와 데이터를 모두 메모리에 적재하기 때문에 메모리 효율이 좋지 않다. 게다가 Primary Key를 제외한 보조 인덱스는 데이터를 조회하기 위한 Primary Key를 함께 가지고 있기 때문에 인덱스 사이즈가 상당히 커질 수 있다.
따라서 단순 로그 수집을 위한 데이터라면 다른 스토리지 엔진을 사용하는 것이 좋다. → Archive or MyISAM.
서비스 특성에 따른 스토리지 엔진 선정
로그전용, 업데이트 X → Archive
읽기 전용 or 로그+업데이트 → MyISAM
동시 처리, 트랜잭션 → InnoDB
InnoDB 사용 시 Barracuda 파일 포맷 고려하기
데이터를 Compact, Dynamic, Compressed 총 세 가지 타입으로 저장할 수 있다.
Compact
텍스트 칼럼(BLOB, VARCHAR, TEXT)의 경우 처음 768바이트만 데이터 트리에 저장하고 나머지는 별도의 페이지에 저장. 나머지 데이터에 접근하기 위한 포인터 20바이트가 내부적으로 생성된다.
Dynamic
전체 칼럼을 데이터 트리에 넣도록 시도. 그렇지 않으면 별도의 페이지에 저장한다.
Compressed
Dynamic의 기능을 가지고 있으며, 정해진 블록 사이즈로 데이터를 압축한다. 모든 데이터를 있는 그대로 압축해서 저장한다. zlib 알고리즘을 통해 데이터와 인덱스를 모두 압축해서 제한된 메모리 사이즈를 극복하게 해준다
