MySQL 실무 기본 개념 정리

steve·2023년 8월 24일

mysql

DB

목록 보기

2/5

RDBMS 기본 특성

RDBMS의 특성에 따라 데이터 일관성이 좋다
데이터 정합성이 좋다 (NoSQL HA 구성 시 스케일 아웃하는 것과는 달리 일반적으로 RDBMS는 꼭 필요한 경우가 아니라면 분산 저장을 하지 않기 때문)

Isolation Level

트랜잭션의 4가지 속성(ACID; Atomicity, Consistency, Isolation, Durability) 중 Isolation에 대해 설명
Isolation level은 데이터베이스 트랜잭션 처리 중에 발생할 수 있는 동시성 문제를 제어하기 위한 설정이다. 아래에 설명하는 격리 수준을 고려하여 트랜잭션 간의 상호작용을 조절해야 한다

READ UNCOMMITTED

다른 트랜잭션에서 변경한 내용을 읽을 수 있음
최고의 동시성을 제공하지만, 데이터 불일치 가능성이 매우 높다
커밋되지 않은 데이터도 읽을 수 있기 때문에 Dirty Read가 발생
-> 무결성 취약함. 갱신되지 않는 과거 데이터 조회 시 또는 거대한 양의 데이터를 어림잡아 집계할 때 사용하는 것이 이상적

READ COMMITTED

커밋된 내용만 읽을 수 있음
트랜잭션이 커밋되기 전까지 다른 트랜잭션의 변경 사항은 읽지 못함
Dirty Read는 방지하지만 Non-Repeatable Read(한 트랜잭션 내에서 같은 행이 두 번 이상 조회될 때 그 값이 다른 경우)는 여전히 발생할 수 있다
-> Dirty Read를 방지하지만 lock이 발생하여 교착 상태가 벌어질 수 있다. 트랜잭션 정확도가 중요한 경우(예: 영화 예매, 계좌 조회 등) 부적합

REPEATABLE READ

같은 쿼리를 실행해도 항상 같은 결과를 보장
트랜잭션이 완료될 때까지 다른 트랜잭션의 변경 사항을 읽지 못함
Non-Repeatable Read는 방지하지만 Phantom Read(한 트랜잭션 내에서 같은 쿼리문이 실행되었음에도 조회 결과가 다른 경우)가 발생
쓰는 동안 읽을 수 없고, 읽는 동안 쓸 수 없다
-> lock 범위가 더 넓어져 성능이 느려질 수 있다

SERIALIZABLE

가장 높은 격리 수준을 제공
모든 읽기 및 쓰기 작업이 잠금을 사용하여 직렬화됨
동시성은 낮아지지만 데이터 일관성은 보장
Dirty Read, Non-Repeatable Read, Phantom Read 모두 방지

각 Isolation level은 동시성과 데이터 일관성 간의 상충 관계이며, 애플리케이션의 요구사항과 성능 등을 고려하여 결정해야 함

인덱스

MySQL 기본 인덱스 탐색 방법

Balanced Tree
이진트리처럼 leaf를 고정적으로 두 개를 갖지 않고 여러개를 갖는 대신 leaf가 한 쪽으로 치우치지 않게 균형있게 배치가 된 형태

인덱스 설정 관련 유의 사항

인덱스 사용 시 LIKE 검색을 하는 경우는 앞글자가 고정이 되어야 함

-- 인덱스 적용되어 검색됨
SELECT * FROM reservation WHERE phone LIKE '2b%';

-- 인덱스가 적용되지 않음
SELECT * FROM reservation WHERE phone LIKE '%2b';

BETWEEN vs IN

-- 값을 지정하지 않고 범위를 설정하여 검색하는 것이기 때문에 IN으로 지정해줘야 성능이 더 빠름
~~ WHERE type BETWEEN a AND b ;
~~ WHERE type IN (a, b) ;

인덱스가 여러개 일 때, 강제로 특정 인덱스를 사용해서 스캔하기

-- 기본적으로는 엔진이 자동 계산해서 빠른 걸로 적용됨
SELECT * FROM reservation USE INDEX (phone_index) WHERE phone LIKE '2b%';

인덱스 설정 기준

수정이 되지 않는 컬럼
조회 시 WHERE 절에 잘 들어가는 컬럼 선택
Cardinality 고려

1) 카디널리티(Cardinality)는 특정 데이터 집합의 유니크(Unique)한 값의 개수
예) 데이터베이스에 '성별' 컬럼의 경우 남자와 여자라는 값을 갖는다. 이 경우 카디널리티는 2다.
반면 주민등록 번호는 테이블에 존재하는 모든 레코드에 유니크 할 수 있다. 이 경우 주민등록 번호의 카디널리티는 테이블에 들어있는 레코드의 개수가 된다.

2) 카디널리티가 높을 수록 인덱스 설정 시 성능이 높아지지만 인덱스의 크기가 커질 수 있기 때문에 인덱스의 최적 카디널리티는 테이블의 크기, 인덱스 대상 컬럼의 고유한 값 수, 쿼리의 선택도 등 다양한 요소에 따라 달라질 수 있다