디스크 읽기 방식
- 데이터베이스 성능 튜닝은 디스크 IO를 어떻게 줄이느냐가 관건
- 데이터베이스에서 병목지점은 항상 디스크 장치
- 하드디스크
- SSD → 하드디스크와 비교해서 용량이 적으며 비싸지만 더 사용된다 왜? 랜덤IO가 빨라서
순차IO와 랜덤IO
- 디스크 헤더가 안움직이고 한번에 읽는다 → 순차IO
- 헤더 이동없이 얼마나 많이 읽을 수 있는가 → 디스크의 성능
- 순차IO가 랜덤IO보다 항상 성능이 좋다 (약 3배정도)(SSD의 경우도 그렇다)
- so, 쿼리튜닝 == 랜덤IO를 줄인다
인덱스란?
- DBMS가 데이터베이스 테이블의 모든 데이터를 검색해 원하는 결과를 가져오려면 시간이 오래 걸린다.
- 그래서 컬럼들의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어두는 것
- DBMS의 인덱스는 컬럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
- 키밸류로 컬럼값과 레코드가 저장된 주소를 미리 저장해두는 자료구조
- INSERT, UPDATE, DELETE 성능을 포기하고 읽기 속도에 몰빵하는 기능
인덱스 알고리즘
- 비트리 알고리즘
- 해쉬 인덱스 알고리즘
B-Tree 인덱스
- 칼럼의 원래 값을 변형하지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다
- 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데,
- 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
B-Tree는 왜 빠른가?
- 어떠한 값에 대해서도 같은 시간에 결과를 얻을 수 있다
- 리프노드를 선형 탐색한다면 71이라는 값을 찾기위해 O(n)
- B-tree는 O(logN)
B+Tree
- 리프노드끼리 LinkedList형태로 연결됨
- 하나의 노드에 더 많은 데이터를 담기에 트리의 높이가 낮아짐
- 리프노드에 모든 데이터가 있기에 풀스캔시 리프노드의 LinkedList를 한번만 선형탐색하면 된다
B-Tree 인덱스 사용에 영향을 미치는 요소
인덱스 키 값의 크기
- 인덱스도 페이지 단위로 관리됨
- 인덱스를 구성하는 키값의 크기가 커지면 디스크로부터 페이지를 읽는 횟수가 늘어난다 → 느려진다
- 이노디비의 기본 페이지 크기는 16KB
- 추가
비트리의 깊이
- 깊이도 중요하지만 제어할 수 없다
- 인덱스 키 값이 커지면 하나의 인덱스 페이지에 더 적은 수의 키 값이 저장되고, 비트리의 깊이가 깊어짐 → 더 많은 디스크 읽기
인덱스의 선택도(기수성)
- 선택도(Selectivity) or 기수성(Cardinality)
- 모든 인덱스 값 중에 유니크한 값의 수
- 100개중에 유니크한게 10개라면 기수성은 10
- 선택도가 높다면 검색 대상이 줄어들기에 빠르다
SELECT * FROM tb_city
WHERE country='KOREA' AND city='SEOUL';
- 다음과 같은 조건
- tb_city 테이블에는 1만건의 데이터
- country 컬럼에만 인덱스
- country와 city 컬럼은 중복없다
- country 컬럼의 유니크 값이 10개일 때
- country=’KOREA’ 라는 조건으로 인덱스를 검색하면 1만 건의 데이터 중 1,000건(10,000 / 10)의 데이터가 일치할 것이라 예상할 수 있다.
- city=’SEOUL’인 레코드는 1건이므로 1,000건 중 999건이 불필요하게 읽히는 것으로 볼 수 있다.
- country 컬럼의 유니크 값이 1,000개일 때
- country=’KOREA’ 라는 조건으로 인덱스를 검색하면 1만 건의 데이터 중 10건(10,000 / 1,000)의 데이터가 일치할 것이라 예상할 수 있다.
- city=’SEOUL’인 레코드는 1건이므로 10건 중 9건이 불필요하게 읽히는 것으로 볼 수 있다.
읽어야하는 레코드의 수
- 인덱스로 읽어야하는 데이터가 전체 테이블 레코드의 20~25%를 넘어가면 보통 인덱스를 안쓰고 풀스캔
- 예시로, 100만건중에 50만건을 읽는 쿼리가 있다면 인덱스 사용안하고 풀스캔
MySQL의 인덱스 사용방법
- 인덱스 레인지 스캔
- 인덱스 풀 스캔
- 루스 인덱스 스캔 (Loose)
- 인덱스 스킵 스캔
인덱스 레인지 스캔
- 가장 대표적인 방식
- 검색해야할 인덱스 범위가 결정됐을때 사용
- 인덱스의 리프노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다
SELECT * FROM employees WHERE first.name BETWEEN 'Ebbe' AND 'Gad';
- 두꺼운 화살표가 실제 스캔하는 범위이다
- 루트노드 → 브랜치노드 → 리프노드를 거쳐 필요한 레코드의 시작점을 찾는다
- 리프노드에서 Ebbe와 Gad를 찾았다면 그 사이를 스캔한다
- 그 사이에 대해서는 실제 데이터 파일로 랜덤엑세스를 통해 가져오는 과정이 필요하다
- 만약 커버링 인덱스가 적용됐다면 이 과정 없음
- 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스라고한다
커버링 인덱스
- customer_id에만 인덱스가 있다고 했을때
select *
from temp_ad_offset
where customer_id = 7;
select customer_id
from temp_ad_offset
where customer_id = 7;
Extra 항목은 옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값
- Using filesort: Orderby처리가 인덱스를 사용하지 못할때
- Using index: 커버링 인덱스 사용할때
- Using temporary: 쿼리의 중간결과를 위해 임시테이블을 생성했을때
- Using index for skip scan: 인덱스 스킵 스캔 최적화를 한 경우
https://zzang9ha.tistory.com/436
인덱스 풀 스캔
- 인덱스의 처음부터 끝까지 모두 읽는 방식
- 인덱스 전체 크기는 테이블의 전체크기보단 작기에 테이블 풀스캔보단 나은 비용
- (A, B, C) 라는 인덱스가 있다
SELECT * FROM employees WHERE B = 'b' AND C = 'c';
- 조건절에 사용된 컬럼이 A를 포함하지 않기에 풀스캔 발생
루스 인덱스 스캔
- 듬성듬성 인덱스를 읽는 방식
- 인덱스 레인지 스캔과 비슷하지만, 중간에 필요하지 않은 인덱스 키 값은 무시한다
- 일반적으로 groupby나 max(), min() 함수에 대한 최적화로서 옵티마이저가 판단한다
인덱스 스킵 스캔
- MySQL8.0부터 추가된 기능
- 조건절에 첫번째 인덱스가 없어도 두번째 인덱스만으로 인덱스 사용하게해주는 기능
- 바로 위 인덱스 풀 스캔의 쿼리가 인덱스를 타게된다
- 단,
- where절에 포함되지않은 인덱스 컬럼의 유니크한 개수가 적어야함(A컬럼의 선택도가 낮아야함)
- 그렇지 않다면 인덱스에서 스캔해야 할 시작지점을 검색하는 작업이 많아지며 오히려 더 느려진다
- 커버링 인덱스를 사용할 수 있는 경우에만 스킵스캔이 된다.
멀티밸류 인덱스
- 다중컬럼 인덱스(복합인덱스)
- 각 컬럼은 자기 바로 앞 컬럼에 의존하여 정렬돼있음
- 두번째 컬럼은 첫번째 컬럼에 의존해 정렬돼있다
- 세번째 컬럼은 두번째 컬럼에 의존해 정렬돼있다
- …
- 선택도가 높은 컬럼을 앞쪽으로
- where절에서 자주 사용되는 조건을 앞쪽으로
- join조건에서 자주 사용되는 조건을 앞쪽으로
- orderby절에서 자주 사용되는 조건을 앞쪽으로
인덱스 스캔 방향
- 인덱스를 생성할 때 설정한 규칙에 따라서 오름차순/내림차순으로 정렬되어 저장된다
- 오름차순으로 저장되었다고 해서 오름차순으로만 이용할 수 있는건 아니다
- 오름차순 인덱스를 거꾸로 읽으면 내림차순으로 정렬된 인덱스로도 활용 가능
- 실행계획에서 결정됨
- ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우 인덱스를 읽는 순서만 변경해서 인덱스 생성 시 지정한 정렬 규칙에 대한 문제점을 해결할 수 있다.
내림차순 인덱스는 느리다
- 왜?
- 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이므로
- 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이므로
- 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 줄이는 데 도움됨
B-tree 인덱스의 가용성과 효율성
- where조건이나 groupby, orderby절이 어떤 경우에 인덱스를 사용하는지 알아야함
비교 조건의 종류와 효율성
SELECT * FROM dept_emp
WHERE dept_no= 'd002' AND emp_no >= 10114 ;
- 1번: 인덱스가 (dept_no, emp_no)인 경우
- 2번: 인덱스가 (emp_no, dept_no)인 경우
1번 케이스
- dept_no가 ‘d002’이고 emp_no가 10114보다 큰 레코드를 찾는다.
- 이후에는 dept_no가 ‘d002’가 아닐 때까지 인덱스를 쭉 읽기만 하면 된다.
2번 케이스
- emp_no가 10114 보다 큰 레코드이고 dept_no가 ‘d002’인 레코드를 찾는다.
- 이후 찾은 모든 레코드에 dept_no가 ‘d002’인지 비교하는 작업을 수행한다.
인덱스의 가용성
- B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다.
- 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다.
SELECT * FROM employees WHERE first_name LIKE '%mer1';
- 이 쿼리는 인덱스를 못탄다
- first_name 컬럼에 저장된 값의 왼쪽부터 비교해가며 일치하는 레코드를 찾아야 하는데,
- 조건 값의 왼쪽 부분(’%mer1’)이 정해져 있지 않기 때문이다
SELECT * FROM dept_emp WHERE emp_no>=10144;
- 인덱스가 (dep_no, emp_no)로 생성되어 있다면 아래의 쿼리는 인덱스를 효율적으로 사용하지 못한다
- 다중 컬럼 인덱스로 생성된 인덱스이므로 dept_no를 먼저 정렬한 후, 다시 emp_no 컬럼값으로 정렬돼 있기 때문 (랜덤IO)
B-tree에서 인덱스를 효율적으로 사용하지 못하는 케이스
- NOT-EQUAL로 비교된 경우 (NOT IN, NOT BETWEEN, IS NOT NULL)
- WHERE column <> 'N'
- WHERE column NOT IN (10,11,12)
- WHERE column IS NOT NULL
- LIKE %xxx 형태 문자열 패턴 비교인 경우
- WHERE column LIKE '%test‘
- WHERE column LIKE '%test%‘
- WHERE column LIKE '_test‘
- 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
- WHERE SUBSTRING(column,1, 1) = 'X'
- WHERE DAYOFMONTH(column) = 1
- 인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우
- WHERE char_column = 10 → char를 int와 비교
- 문자열 데이터 타입의 콜레이션이 다른 경우
- WHERE utf8_bin_char_column = euckr_bin_char_column
클러스터링 인덱스
- pk가 비슷한 레코드끼리 묶어서 인덱스로 저장한 것
- 클러스터링 인덱스의 리프노드에는 모든 레코드의 컬럼이 저장되어있다
- 클러스터링이란
- 여러개를 하나로 묶는다는 의미로 주로 사용됨
- MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것을 기준으로 묶어서 저장하는 형태
- 비슷한 것 = pk
- pk설정시 그 컬럼은 자동으로 클러스터링 인덱스가 된다
- 주로 비슷한 값을 동시에 조회하는 경우가 많다는 점에서 착안된 설계
- 테이블의 레코드가 pk 기준으로 정렬되어 저장되는 경우를 → 클러스터링 인덱스 or 클러스터링 테이블
- pk에 따라 레코드의 저장위치가 결정되므로 테이블의 레코드 저장 방식으로 볼 수 있다
- 인덱스 자체의 리프노드가 곧 데이터다. → 테이블 자체가 인덱스다
- 클러스터 인덱스는 페이지를 알기 때문에 바로 그 페이지를 펴는 것
- 넌 클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것.
- 테이블 스캔은 처음부터 한 장씩 넘기면서 내용을 찾는 것
- 인덱스 자체의 리프노드가 곧 데이터다. → 테이블 자체가 인덱스다
- pk가 없다면 다음 기준으로 이노디비 스토리지 엔진이 pk를 대체할 컬럼을 선정한다
- pk가 있다면 pk를 클러스터링 키로 선택
- not null인 유니크 인덱스중에 첫번째를 클러스터링 키로 선택
- 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 고른 후 클러스터링 키로 선택
- 장점 (주로 빠른 읽기)
- pk(클러스터링키)로 검색할때 성능이 매우 빠름(특히 pk를 범위검색에 사용하는 경우)
- 테이블의 모든 세컨더리 인덱스가 pk를 가지고있기 때문에 인덱스만으로 쿼리를 처리할 수 있을 확률이 높음(커버링 인덱스)
- 단점 (주로 느린 쓰기)
- 테이블의 모든 세컨더리 인덱스가 pk를 가지고있기 때문에 클러스터링 키값이 클수록 전체적으로 인덱스의 크기가 커짐
- 세컨더리 인덱스로 검색할때 pk로 한번 다시 검색해야하기에 처리성능이 느림
- INSERT를 할 때 pk에 의해 레코드 저장 위치가 결정되기에 처리 성능이 느림
- pk를 변경할 때 레코드를 DELETE하고 INSERT해야하기에 처리성능이 느림
- 클러스터링 인덱스는 인덱스에 실제 데이터를 저장하고있음
- 논 클러스터링(세컨더리) 인덱스는 인덱스 페이지를 별도로 관리함
- 논 클러스터링 인덱스의 장단점
- 장점
- 실제 데이터 페이지는 정렬되지 않기에 INSERT, UPDATE, DELETE 성능 좋음
- 단점
- 인덱스만 정렬되고 실제 데이터는 정렬되지 않기에 클러스터링 인덱스에 비해 검색 느림
- 장점
- 논 클러스터링 인덱스의 장단점
논 클러스터링 인덱스(세컨더리 인덱스)
- 클러스터링 인덱스(PK 인덱스)를 제외한 모든 인덱스
- 리프 노드에서 레코드의 물리적인 주소값이 아닌 PK를 가지기 때문에 레코드를 접근할 때 바로 접근할 수 없다
- 왜 논 클러스터링 인덱스를 통한 검색은 클러스터링 인덱스를 한번 거치게 했을까?
- 레코드 변경시 인덱스의 부하를 줄이기 위함
- pk가 변경되면 레코드의 물리적 주소가 바뀜
- 논 클러스터링 인덱스는 실제 레코드 주소값(pk)이 아닌 논리적인 pk만 참조하기에 레코드의 값이 변경되어도 논 클러스터링 인덱스는 바뀌지 않는다
클러스터링 인덱스 → pk(데이터)
논 클러스터링 인덱스 → 클러스터링 인덱스 → pk(데이터)
- 클러스터링 인덱스: 인덱스를 가지고 리프노드까지 탐색해서 원하는 데이터를 바로 얻음
- 논 클러스터링 인덱스: 인덱스를 가지고 리프노드에 탐색해서 실제 데이터 위치를 얻음 → 실제 데이터는 힙영역에서 얻는다
클러스터링 테이블 사용시 주의사항
- 이노디비 테이블(클러스터링 테이블)에서는 주의할 것이 있다
- 모든 논 클러스터링(세컨더리) 인덱스가 pk를 포함한다
- 그래서 pk의 크기가 커지면 세컨더리 인덱스도 커진다
- 그래서 다음과 같은 주의사항들이 있다
- pk는 auto_increment 보다는 업무적인 컬럼으로 생성
- 이노디비에서 pk는 클러스터링 키로 사용되며, 이 값에 따라서 레코드의 위치가 결정됨
- 즉, pk로 검색하는 경우 클러스터링 되지 않은 테이블에 비해서 매우 빠르다는 뜻
- 따라서 검색에서 빈번하게 사용되고 → 업무적으로 pk가 해당 레코드를 대표할 수 있다면 pk로 설정하는 것이 좋다
- pk는 반드시 명시할 것
- 명시하지 않으면 이노디비 스토리지 엔진이 내부적으로 일련번호 컬럼을 추가한다
- 하지만 이 컬럼은 보이지 않기에 사용자가 접근할 수 없다
- 따라서 명시하지 않아도 일련번호 컬럼이 추가되기에 명시하는거나 다름없다(오히려 접근이 안되기에 사용불가라는 단점만 있음)
- auto_increment 컬럼을 인조 식별자로 사용할 경우
- 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO-INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다.
- 이를 인조 식별자라고 함
- pk는 auto_increment 보다는 업무적인 컬럼으로 생성
유니크 인덱스
- 같은 값이 2개 이상 저장될 수 없음을 의미
- 인덱스라기보단 제약조건에 가깝다 → 하지만 인덱스 없이 유니크 제약만 설정할 방법이 없다
- 유니크 인덱스가 걸린 컬럼에 null도 저장될 수 있지만 null은 중복 허용임
- 유니크 인덱스가 다른 세컨더리 인덱스보다 조회가 빠른가?
- 그렇긴한데 유니크”인덱스”라서 빠른건 아니다
- 인덱스의 성격이 유니크한지 아닌지의 차이이지 인덱스 자체가 동작하는 방식은 동일하다
- 유니크하지 않은 세컨더리 인덱스는 읽어야 할 값이 많아서 느린것
- 유니크 인덱스는 1개뿐이라서 빠른것 (1개뿐임이 보장되기에 실행계획이 다름)
- 인덱스 자체에 대한 차이는 아님
- 주의
- 유니크 인덱스는 세컨더리 인덱스보다 변경에 느림
- 일반인덱스와 유니크인덱스는 인덱스 자체로는 동일하다
- pk를 유니크 인덱스로 쓰지말자 중복이다
참고:
'DB' 카테고리의 다른 글
[Typeorm] save() 알차게 사용하기 (1) | 2023.04.01 |
---|---|
[DB] 데이터베이스 정규화 (0) | 2021.07.14 |