MySQL 8.0 스터디
인덱스
인덱스 목적
랜덤 I/O 는 순차 I/O 에 비해 많이 느림. (DBMS 는 대부분 랜덤 I/O 가 많이 발생) 단순한 쿼리 튜닝으로는 랜덤 I/O 를 순차 I/O 로 변경하는것은 어렵지만, 랜덤 I/O 자체를 줄여줄 순 있음.
개념
인덱스는 SortedList, 데이터 파일은 ArrayList 와 비교할 수 있음.
- SortedList - 저장될 때마다 값을 정렬해야 하므로 저장하는 과정이 느리지만, 원하는 값을 아주 바르게 찾아올 수 있다.
- ArrayList - 값이 들어오는 순서대로 저장.
인덱스를 추가할지의 결정은 데이터의 저장(INSERT, UPDATE, DELETE) 속도를 어느정도 희생하고 읽기 속도를 얼마나 더 빠르게 만들어야 하는지에 따라 결정.
B-Tree 인덱스
구조
최상위에 루트 노드(Root node) 가 존재하고 그 하위에 자식 노드가 붙어 있는 형태.
트리구조의 가장 하위에 있는 노드를 리프 노드(Leaf node) 라고 하고, 루트 노드와 리프 노드 중간의 노드를 브랜치 노드(Branch node) 라고 함.
리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있음.
MyISAM 엔진에서는 리프 노드가 레코드의 물리적 주소를 가지고 있지만, InnoDB 엔진은 프라이머리 키를 갖고 있어 프라이머리 키 인덱스를 한번 더 검색한 후 레코드를 읽어옴.
인덱스 키 추가
MyISAM 이나 MEMORY 엔진에서는 INSERT 문이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경하지만 InnoDB 엔진은 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다.
(프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 추가/삭제한다)
인덱스 키 삭제
삭제하고자 하는 키 값이 저장된 B-Tree 의 리프 노드를 찾아 삭제 마킹을 함.
인덱스 키 변경
단순히 인덱스상의 키 값만 변경하는 것은 불가능하다.
키 값을 삭제 후 새로운 키 값을 추가하는 형태로 처리된다.
인덱스 키 검색
인덱스 검색 작업은 루트 노드부터 시작해 리프 노드까지 이동하면서 비교 작업을 수행한다.(트리 탐색)
B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다.
인덱스 키 값의 크기
InnoDB 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block) 이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
MySQL 5.7 버전부터 InnoDB 엔진의 페이지 크기를 innodb-Page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있다,(기본값은 16KB)
16KB 크기의 인덱스 페이지에서 자식 노드 주소 영역이 12바이트로 구성된다 할 때, 인덱스 키 크기가 16 바이트라면 16 * 1024 / (16 + 12) = 585 개의 키를 저장할 수 있다.
만약 인덱스 키가 32바이트로 늘어나게 되면 16 * 1024 / (32 + 12) = 372 개 저장할 수 있게 된다.
SELECT 쿼리가 레코드 500개를 읽어야 한다면 전자는 인덱스 페이지 한번으로 해결될 수 있지만 후자는 최소 2번 이상 읽어야 하므로 쿼리 속도가 느려진다.
B-Tree 깊이
B-Tree 인덱스의 깊이를 직접 제어할 방법은 없다.
B-Tree 인덱스의 깊이가 3인 경우 인덱스 키 값이 16바이트인 경우에는 최대 2억(585 * 585 * 585)개 정도의 키 값을 담을 수 있지만, 키 값이 32바이트로 늘어나면 5천만(373 * 372 * 372) 개로 줄어든다.
인덱스 키 값의 크기는 가능하면 작게 만드는게 효율적이다.
선택도(기수성, 카디널리티)
모든 인덱스 키 값 가운데 유니크한 값의 수.(전체 인덱스 키 값이 100개이고 유니크한 값의 수가 10개라면 기수성은 10. 중복된 값이 많아지면 기수성은 낮아짐)
인덱스는 기수성이 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
SELECT *
FROM tb_test
WHERE country = 'KOREA' AND city = 'SEOUL';country 와 city 가 중복된 값이 없는 10000 건의 데이터를 가진 tb_test 테이블에 위 쿼리를 실행할 때,
- 케이스 A: country 컬럼의 유니크한 값의 개수가 10개
- 케이스 B: country 컬럼의 유니크한 값의 개수가 1000개
케이스로 구분하면
A 케이스에선 country 인덱스에서 1000건을 찾아 city = 'SEOUL' 조건을 검색하기 때문에 999건의 불필요한 검색을 하고,
B 케이스에선 country 인덱스에서 10건을 찾아 city = 'SEOUL' 조건 검색으로 9건만 불필요한 검색을 하게 됨.
읽어야 하는 레코드의 건수
인덱스를 통해 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.
일반적인 DBMS 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4 ~ 5배정도 비용이 드는 작업인 것으로 예측.
즉 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블의 레코드의 20 ~ 25%를 넘어서면 인덱스를 이용하지 않고 테이블을 직접 읽어서 필요한 레코드만 필터링 하는 방식으로 처리하는 것이 효율적이다.
B-Tree 인덱스를 통해 데이터 읽기
인덱스 레인지 스캔
인덱스의 범위가 결정됐을 때 사용하는 방식.(ex: where id between 10 and 100)
시작 범위에 해당하는 인덱스를 찾아 마지막 범위 인덱스까지 차례대로 읽는 방식이다.
이 과정에서
- 인덱스 조건을 만족하는 값이 저장된 위치를 과정
- 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는 과정
이 존재하는데 1번을 인덱스 탐색, 2번을 인덱스 스캔이라고 한다.
MySQL 서버는 인덱스 탐색/스캔 작업이 얼마나 일어났는지 확인할 수 있는 상태 값을 제공한다.
SHOW STATUS LIKE 'Handler_%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Handler_read_first | 71 |
| Handler_read_last | 1 |
| Handler_read_key | 567 |
| Handler_read_nest | 3447233 |
| Handler_read_prev | 19 |
| ... | ... |
+------------------------+----------+이 상태값들은 각각
Handler_read_key: 상태 값은 인덱스 탐색이 실행된 횟수Handler_read_next: 인덱스를 정순으로 읽은 레코드 건수Handler_read_prev: 인덱스를 역순으로 읽은 레코드 건수Handler_read_first: 인덱스의 첫번째 레코드를 읽은 횟수Handler_read_last: 인덱스의 마지막 레코드를 읽은 횟수
을 나타낸다.
이렇게 나타내준 레코드 건수는 실제 인덱스만 읽었는지, 인덱스를 통해 테이블의 레코드를 읽었는지는 구분하지 않는다.
인덱스 풀스캔
인덱스의 처음부터 끝까지 모두 읽는 방식.
주로 인덱스의 두번째 컬럼이 조건절에 사용됐을 때 인덱스 풀 스캔을 사용한다.(ex: 인덱스가 INDEX ix_address (country, city) 로 만들어졌을 경우 where city = 'SEOUL' 식으로 사용할 경우)
일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다 인덱스만 읽는것이 효율적이다.반대로 레코드까지 모두 읽어야한다면 절대 이 방식으로 처리되지 않는다.
루스 인덱스 스캔
앞서 나온 두 인덱스 스캔은 타이트(Tight) 인덱스 스캔 으로 분류한다. 루스 인덱스 스캔은 듬성듬성 인덱스를 읽는것을 의미한다.
루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우 사용된다.
SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;이 쿼리에서 dept_no 를 인덱스 키로 찾은 다음 해당 값들의 최소값만 구하면 다음 인덱스 키값으로 넘어가서 쿼리를 수행한다.
인덱스 스킵 스캔
데이터베이스에서 인덱스는 값이 정력되어 있기 때문에 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.
ALTER TABLE employees
ADD INDEX ix_gender_birthdate (gender, birth_date);이렇게 ix_gender_birthdate인덱스를 만들고 사용하려면 gender컬럼에 대한 비교 조건이 필수이다.
-- 인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE birth_date >= '1995-02-01';
-- 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender = 'M' and birth_date >= '1995-02-01'; 첫 번째 쿼리를 인덱스를 이용하기 위해선 birth_date컬럼부터 시작하는 인덱스를 새로 생성해야만 했다.
MySQL 8.0 버전부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.
인덱스 스킵 스캔 기능의 유무를 비교하면
mysql> SET optimizer_switch = 'skip_scan=off';
mysql> EXPLAIN
SELECT gender, birth_date
FROM employees
WHERE BIRTH_DATE >= '1995-02-01';
+----+-------------+-------+----------------------------+----------------------------+
| id | table | type | key | Extra |
+----+-------------+-------+----------------------------+----------------------------+
| 1 | epmloyees | index | index ix_gender_birthdate | Using where; Using index |
+----+-------------+-------+----------------------------+----------------------------+위의 쿼리는 조건절에 gender 컬럼에 대한 조건 없이 birth_date 컬럼의 비교 조건만 가지고 있기 때문에 쉽게 ix_gender_birthdate 인덱스를 효율적으로 이용할 수 없다.
위 실행계획에서 type 컬럼이 index 라고 표시된 것은 인덱스를 처음부터 끝까지 모두 읽었다(풀 인덱스 스캔)는 의미이므로 인덱스를 비효율적으로 사용한 것이다.
mysql> SET optimizer_switch = 'skip_scan=on';
mysql> EXPLAIN
SELECT gender, birth_date
FROM employees
WHERE BIRTH_DATE >= '1995-02-01';
+----+------------+-------+----------------------------+------------------------------------------+
| id | table | type | key | Extra |
+----+------------+-------+----------------------------+------------------------------------------+
| 1 | epmloyees | range | index ix_gender_birthdate | Using where; Using index for skip scan |
+----+------------+-------+----------------------------+------------------------------------------+인덱스 스킵 스캔을 사용하면 실행 계획의 type 컬럼 값이 range 로 표시되는데, 이는 인덱스에서 필요한 부분만 읽었다는 것을 의미한다.
쿼리를 실행하면 우선 옵티마이저가 gneder 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gneder 컬럼 조건을 추가해 쿼리를 다시 실행하는 형태로 처리한다.
SELECT gender, birth_date FROM employees WHERE BIRTH_DATE gender = 'M' AND >= '1995-02-01';
SELECT gender, birth_date FROM employees WHERE BIRTH_DATE gender = 'F' AND >= '1995-02-01';인덱스 스킵 스캔의 단점
- WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
- 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함(커버링 인덱스)
첫 번째 조건은 유니크한 갑스이 개수가 많다면 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많아진다.(쿼리 성능이 떨어질 수 있음)
두 번째 조건은 SELECT 절에서 인덱스에 포함되지 않은 컬럼을 조회하면 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행한다.
다중 컬럼 인덱스
하나의 인덱스에 여러 컬럼이 존재하면 인덱스 키 순서대로 정렬하게 된다.
(ex: 인덱스에 컬럼이 두개인 경우 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬됨)
때문에 인덱스 내에서 각 컬럼의 순서가 상당히 중요하다.
궁금증
- MyISAM 의 인덱스 방법은 인덱스 키와 레코드 주소를 갖고 있기 때문에, 인덱스를 이용해 레코드 주소를 이용해 레코드를 찾을땐 랜덤 I/O 가 발생. 그러면 InnoDB 에선 인덱스 키와 프라이머리 키를 이용해 인덱스 스캔을 하면 레코드 값을 찾을 땐 순차 I/O 를 쓰는지?
