[Real MySQL] 인덱스

2024. 10. 31. 03:40

디스크 읽기 방식

데이터베이스 성능 튜닝은 디스크 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

MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)

💯 MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0) 실행 계획(EXPLAIN) 이란? 대부분의 DBMS는 많은 데이터를 안전하고, 빠르게 저장 및 관리하는 것이 주목적이다. 이러한 목적을 달성하기 위해 사

zzang9ha.tistory.com

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식
인덱스 전체 크기는 테이블의 전체크기보단 작기에 테이블 풀스캔보단 나은 비용
(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 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다.
  - 이를 인조 식별자라고 함

유니크 인덱스

같은 값이 2개 이상 저장될 수 없음을 의미
인덱스라기보단 제약조건에 가깝다 → 하지만 인덱스 없이 유니크 제약만 설정할 방법이 없다
유니크 인덱스가 걸린 컬럼에 null도 저장될 수 있지만 null은 중복 허용임
유니크 인덱스가 다른 세컨더리 인덱스보다 조회가 빠른가?
- 그렇긴한데 유니크”인덱스”라서 빠른건 아니다
- 인덱스의 성격이 유니크한지 아닌지의 차이이지 인덱스 자체가 동작하는 방식은 동일하다
  - 유니크하지 않은 세컨더리 인덱스는 읽어야 할 값이 많아서 느린것
  - 유니크 인덱스는 1개뿐이라서 빠른것 (1개뿐임이 보장되기에 실행계획이 다름)
  - 인덱스 자체에 대한 차이는 아님
주의
- 유니크 인덱스는 세컨더리 인덱스보다 변경에 느림
- 일반인덱스와 유니크인덱스는 인덱스 자체로는 동일하다
- pk를 유니크 인덱스로 쓰지말자 중복이다