DB의 성능 튜닝은 대부분 디스크 I/O를 줄이는 방향으로 이루어진다.
SSD는 DRAM보다는 느리지만, HHD보다 1000배 가량 빠르다.
순차 I/O에선 SSD가 HDD보다 조금 빠르거나 비슷하지만, 랜덤 I/O에선 SSD가 HDD보다 압도적으로 빠르다.
→ DBMS에선 랜덤 I/O가 대부분이므로 SSD가 좋다.
랜덤 I/O, 순차 I/O 다 읽을 데이터의 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽어야 한다.
위 그림 기준으로 순차 I/O는 읽을 데이터가 붙어있기 때문에 헤더를 1번만 움직여도 되지만 랜덤 I/O는 읽을 데이터가 흩어져있기 때문에 헤더를 3번 움직여야한다.
→ 순차 I/O의 성능이 랜덤 I/O보다 좋다.
→ 쿼리 튜닝 시 랜덤 I/O를 순차 I/O로 바꾸는 것이 중요하다.
DBMS에서 인덱스는 데이터의 쓰기(INSERT
, UPDATE
, DELETE
)성능을 희생하고 읽기 성능을 높이는 기능이다.
B-Tree는 DB에서 가장 일반적으로 사용되는 인덱스 알고리즘이다.
B-Tree는 트리 구조의 최상위에 하나의 루트 노드, 가장 하위에 있는 리프 노드, 루트 노드도 리프 노드도 아닌 중간 노드인 브랜치 노드로 구성된다.
인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않다.
위 그림은 InnoDB에서 인덱스를 통해 데이터를 읽는 과정을 나타낸다.
InnoDB에선 PK만이 물리적인 주소를 가지므로 세컨더리 인덱스를 이용하여 데이터를 찾기 위해선 두번의 B-Tree 탐색이 필요하다.
다른 스토리지 엔진에서는 INSERT
, UPDATE
, DELETE
문장이 실행되면 즉시 쓰기 연산을 실행한다.
But, InnoDB에선 필요한 경우 키 추가 작업을 지연시켜 나중에 처리할 수 있다.(By 체인지 버퍼)
But, PK나 유니크 인덱스의 경우는 중복 체크가 필요하므로 지연 처리가 불가능하다.
변경의 경우는, 인덱스는 키 값에 따라 저장될 위치가 결정되므로 삭제한 뒤 새로운 값을 추가하는 형태로 처리한다.
여러 단점을 감당하며 인덱스를 쓰는 이유는 “빠른 검색” 때문이다.
100% 일치, 앞 부분만 일치, 부등호 비교 조건에서는 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
ex1) 이름이 “John”인 사람 검색 → 인덱스 O
ex2) 이름이 “Jo”로 시작하는 사람 검색 → 인덱스 O
ex3) 이름이 “J” 이후 인 사람 검색 → 인덱스 O
ex4) 이름이 “hn”로 끝나는 사람 검색 → 인덱스 X
ex5) 이름이 “John”이 아닌 사람 검색 → 인덱스 X (사용하지만 비효율적)
ex6) 이름이 “Jo”로 시작하지 않는 사람 검색 → 인덱스 X (경우에 따라 사용됨, 비효율적)
인덱스를 이용한 검색 시 인덱스의 키 값에 변형이 가해진 후 비교되는 경우는 사용할 수 없다.
→ 연산을 수행한 결과로 검색하는 작업은 B-Tree의 장점을 이용할 수 없다.
InnoDB에선 데이터를 읽어오며 해당 레코드의 잠금을 획득하는데, 인덱스가 설정되어 있지 않아서 테이블 풀 스캔을 하게 되면 많은 레코드와 갭이 잠기게되어 성능 문제나 데드락이 발생할 수 있다.
→ 인덱스 설계가 매우 중요하다.
인덱스 키 값의 크기
디스크에 데이터를 저장하는 가장 기본 단위를 “페이지” 라 하고, ****인덱스도 페이지 단위로 관리된다.
B-Tree는 자식 노드의 개수가 가변적인데, MySQL에서는 인덱스의 페이지 크기와 키 값의 크기에 따라 자식 노드 수가 결정된다.
innodb_page_size
시스템 변수를 이용해 4~64kb 선택할 수 있다. (기본 값은 16kb)만약 페이지 크기 = 16kb, 키 값의 크기 = 16b, 자식 노드 주소 = 12b 라고 하면 16 * 1024 / (16 + 12)
= 585 개의 자식 노드를 가질 수 있다.
그러므로 당연히 키 값이 커지면 가질 수 있는 자식 노드의 수가 줄어들고, 그렇게 되면 디스크 I/O 횟수가 늘어나고, 속도가 느려지게 된다.
또한, 인덱스 키의 크기가 커지면 전체적인 인덱스의 크기도 커진다.
→ 인덱스를 캐시해 두는 InnoDB의 버퍼 풀 영역의 크기는 제한적이므로 인덱스 키의 크기가 커지면 메모리에 캐시해 둘 수 있는 레코드 수는 줄어들어 성능이 저하될 수 있다.
B-Tree 깊이
인덱스 키 값의 크기가 커지면 한 페이지에 담을 수 있는 키의 수가 적어지고, 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져서 더 많은 디스크 읽기가 필요하다.
ex) B-Tree의 깊이가 3이고 키의 크기가 16b면 최대 2억개 정도의 키를 담을 수 있지만, 키의 크기가 32b면 5천만개 정도의 키를 담을 수 있다.
카디널리티
카디널리티는 데이터의 중복도를 말하며, 높을 수록 중복된 값이 적다.
인덱스는 카디널리티가 높을수록 검색 대상이 줄어들기 때문에 더 빠르게 처리된다.
SELECT *
FROM tb_test
WHERE country = 'KOREA' AND city = 'SEOUL';
tb_test
테이블의 레코드 건수는 1만 건, country
컬럼에만 인덱스를 생성했다 했을 때 아래 두가지 케이스를 살펴보자
country 컬럼의 유니크한 값의 개수가 10개인 경우
country = ‘KOREA’
이 조건에서 일치하는 값은 1000개 일 것이고, 그 중 city = ‘SEOUL’
인 값이 1건이면 999건은 불필요하게 읽은 것이다.
country 컬럼의 유니크한 값의 개수가 1000개인 경우
country = ‘KOREA’
이 조건에서 일치하는 값은 10개 일 것이고, 그 중 city = ‘SEOUL’
인 값이 1건이면 9건은 불필요하게 읽은 것이다.
→ 카디널리티가 높을 수록 읽어오는 데이터가 줄어들며 인덱스의 효율이 상승한다.
읽어야 하는 레코드의 건수
인덱스를 통해 레코드를 읽는 것은 바로 테이블의 레코드를 읽는 것 보다 비용이 높다. (약 4~5배)
즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블 풀 스캔 후 필요한 레코드만 걸러내는 방식으로 처리한다.
→ 읽어야 하는 레코드의 건수가 많을 수록 인덱스 사용이 비효율적이다.
인덱스 레인지 스캔
인덱스의 접근 방법 중 가장 대표적이고, 뒤에 나오는 다른 방식보다 빠르다.
[ 과정 ]
인덱스에서 조건이 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색)
탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽는다. (인덱스 스캔)
→ 해당 인덱스를 구성하는 컬럼의 정순 or 역순으로 정렬된 상태로 레코드를 가져온다.
읽어온 인덱스 키와 레코드 주소를 이용해 레코드를 읽는다.
→ 이때 레코드 한 건 마다 랜덤 I/O가 일어난다.
→ 인덱스를 통해 레코드를 읽는 작업은 그냥 읽는 것 보다 비용이 많이든다.
근데, 만약 필요한 데이터가 전부 인덱스에 있다면 이 과정을 생략한다. (커버링 인덱스)
인덱스 풀 스캔
인덱스를 사용하지만 인덱스의 처음부터 끝까지 모두 읽는 방식이다.
인덱스 크기가 테이블의 크기보다 작으므로 인덱스만으로 조건을 처리할 수 있을 때 테이블 전체를 읽는 것 보다 인덱스를 읽는 것이 더 효율적이고, 그럴 때 인덱스 풀 스캔 방식이 사용된다.
루스 인덱스 스캔
인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 넘어가는 형태로 처리한다.
일반적으로 GROUP BY
or 집합 함수 중 MIN()
, MAX()
함수에 대해 최적화를 하는 경우에 사용한다.
인덱스 스킵 스캔
인덱스 스킵 스캔이 없다면 new_idx(A,B)
와 같은 인덱스가 있을 때 아래 쿼리는 인덱스를 효율적으로 사용할 수 없다.
SELECT * FROM employees WHERE B >= '2000-11-05';
하지만, 인덱스 스킵 스캔은 아래와 같은 방식으로 처리하여 인덱스를 활용할 수 있다.
// A 컬럼이 'type1', 'type2' 두 종류의 값만 가지고 있음
SELECT * FROM employees WHERE A = 'type1' AND B >= '2000-11-05';
SELECT * FROM employees WHERE A = 'type2' AND B >= '2000-11-05';
위와 같이 처리하므로 아래와 같은 단점들이 있다.
WHERE
조건절에 조건이 없는 인덱스의 선행 컬럼(위에선 A 컬럼)의 유니크한 값의 개수가 적어야한다.여러 컬럼을 포함하는 인덱스를 말한다.
중요한 점은, 복합 인덱스 생성 시 순서에 따라 정렬 값이 결정된다.
예를 들어 index_1(A,B) 이러한 인덱스라면, A에 따라 정렬된 뒤 A 값이 같은 경우 B 컬럼으로 정렬된다.
→ 복합 인덱스 생성 시 컬럼의 순서가 매우 중요하다.
인덱스 역순 스캔은 아래의 이유들 때문에 정순 스캔에 비해 28.9% 정도 시간이 더 걸린다.
→ 자주 사용되는 정렬 순서로 인덱스를 생성하는 것이 효율적이다.
≠
, LIKE ‘%??’
, 인덱스 컬럼이 변형된 후 비교, 데이터 타입이 다른 경우 등 몇 몇 경우에는 인덱스를 효율적으로 활용할 수 없다.R-Tree 인덱스
B-Tree와 내부 메커니즘은 비슷하지만, R-Tree는 인덱스를 구성하는 컬럼의 값이 2차원 공간 값이다.
MySQL은 공간 데이터 타입, 공간 인덱스, 공간 연산 함수 를 제공한다.
각 도형을 감싸는 최소 크기의 사각형을 기준으로 인덱스가 생성된다.
위 그림 처럼 루트 노드가 여러 도형들을 감싸는 가장 큰 범위의 사각형, 리프 노드가 하나의 도형을 감싸는 최소 크기의 사각형 인 형태로 되어있다.
ST_Contains()
, ST_Within()
함수로 공간 인덱스를 활용한 효율적인 공간 검색이 가능하다.
전문 검색 인덱스
문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 말한다.
불용어 처리, 어근 분석 의 과정을 거쳐 색인 작업이 수행된다.
함수 기반 인덱스
칼럼의 값을 변형해서 만들어진 값에 대한 인덱스이다.
구현 방법은 아래와 같이 2가지가 있다.
예를 들어 first_name
, last_name
이렇게 2개 컬럼이 있고 이를 이용하여 full_name
이라는 가상 컬럼을 만들면 가상 컬럼을 이용한 인덱스를 만들 수 있다.
멀티 밸류 인덱스
하나의 레코드에 여러가지 값의 인덱스를 가질 수 있는 인덱스이다.
주로 JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스를 구현하기 위해 사용된다.
MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK 기준)들 끼리 묶어서 저장하는 것을 말한다.
그러므로 PK 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야하고, PK 기반 검색이 매우 빠르지만, 레코드 저장 및 PK의 변경이 느리다.
만약 PK가 없다면 아래와 같은 로직으로 대체 컬럼을 선택한다.
NOT NULL
옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택한다.INSERT
할 때 PK에 의해 저장 위치가 결정되므로 처리 성능이 느리다.DELETE
하고 INSERT
하는 작업이 필요하기 때문에 처리 성능이 느리다.유니크 인덱스는 같은 값이 2개 이상 저장될 수 없는 인덱스를 말한다.
하지만 NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.
PK는 기본적으로 NOT NULL + 유니크 인덱스이다.
→ 유일성이 꼭 보장되어야 하는 컬럼에 대해선 유니크 인덱스를 생성하되, 그렇지 않다면 일반 인덱스 생성을 고려하자.
왜래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스가 생성된다.
// 커넥션 1 시작
// 커넥션 1 부모 테이블 컬럼 업데이트
UPDATE users SET name = 'change' WHERE id = 1;
// 커넥션 2 시작
// 커넥션 2 자식 테이블 컬럼 업데이트
UPDATE posts SET user_id = 1 WHERE id = 100;
// 커넥션 1 롤백
// 커넥션 2 완료
InnoDB의 왜래키 관리에는 두 가지 특징이 있음
UPDATE posts SET user_id = 1 WHERE id = 100;
)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때 까지 기다린다. (테이블이 변경되며 잠금 대기가 발생했다.)UPDATE posts SET user_id = 1 WHERE id = 100;
)은 왜래키로 인한 잠금 확장이 발생하지 않는다. (왜래키가 연관되지 않아서 잠금 대기가 발생하지 않았다.)