Hyerin Blog
Index


예전에 교내 수업에서 프로젝트를 하면서 인덱스를 적용해보면서
어떤 경우에 인덱스를 타지 않는지 주의 사항에 대해서는 알게 되었지만
어떻게 생성해야 최적으로 생성할 수 있는지는 헷갈리기 때문에
더 알아보고 제대로 설정해보고자 Real MySQL을 읽어보기로 했다.


인덱스란?

데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다.
그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의(key-value) 쌍으로 인덱스를 만들어두는 것이다.
최대한 빠르게 찾아갈 수 있게 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

SortedList는 DBMS의 인덱스와 같은 구조이고 ArrayList는 데이터 파일과 같은 자료구조를 사용한다.
인덱스는 저장되는 컬럼의 값을 이용해 항상 정렬된 상태로 유지한다.
때문에 데이터가 저장될 때마다 항상 값이 정렬되서 저장하는 과정이 복잡하고 느리지만,
이미 정렬돼 있어서 빨리 원하는 값을 찾아올 수 있다.


B-Tree 인덱스 키 검색

B-Tree 인덱스를 이용한 검색은 100%일치 또는 값이 앞부분만 일치하는 경우에 사용할 수 있다.
부등호 비교나 값의 뒷부분이 일치하는 경우에는 B-Tree 인덱스를 이용한 검색이 불가능하다.
또한 인덱스의 키값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
따라서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없다.

인덱스 조회 주의사항은 다음과 같다.


선택도 (기수성)

모든 인덱스 키값 가운데 유니크한 값의 수를 의미한다.
전체 인덱스 기값은 100개인데 그중에서 유니크한 값의 수는 10개라면 기수성은 10이다.
중복된 값이 많아지면 기수성은 낮아지고 선택도 또한 떨어진다.
인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.


읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.
테이블에 레코드가 100만 건이 저장돼 있는데, 그중에서 50만 건을 읽어야 하는 쿼리가 있다고 가정하자.
이 작업은 필요 없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통한 필요한 50만 건만 일어 오는 것이 효율적일지 판단해야 한다.

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해, 테이블에서
직접 레코드 1건을 읽는 것보다 4~5배 정도 더 비용이 많이 드는 작업으로 예측된다.
인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 코드의 20~25%를 넘어서면 인덱스를 이용하지 않고
직접 테이블을 모두 읽어서 필요한 레코드만 가져내는(필터링) 방식으로 처리하는 것이 효율적이다.


B-Tree 인덱스를 통한 데이터 읽기

인덱스 사용을 결정하려면 MySQL(각 스토리지 엔진)이 어떻게 인덱스를 이용해서 레코드를 읽어오는지 알아야 한다.

1) 인덱스 레인지 스캔
인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽는 경우 각각 다른 이름으로 구분하지만
모두 묶어서 “인덱스 레인지 스캔”이라고 표현할 수 있다.

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad'

인덱스 레인지 스캔은 검색해야 할 인덱스 범위가 결정되었을 때 사용하는 방식이다.
검색하려는 값의 수나 검색 결과와는 관계없이 레인지 스캔이라고 표현한다.
루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로
리프 노드까지 찾아 들어가면 실제로 원하는 시작 지점을 찾을 수 있다.
시작 위치를 찾으면 그때부터 리프 노드의 레코드만 순서대로 읽으면 된다.

중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다.
이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 실행된다.

2) 인덱스 풀 스캔
인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과 다르게 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.
쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.
쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.

먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후,
인덱스의 리프 노드를 연결하는 Linked list를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다.
이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다.
인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있으면 테이블의 레코드를 읽을 필요가 없기 때문에 적은 디스크 I/O로 쿼리를 처리할 수 있다.

3) 루스 인덱스 스캔
루스 인덱스 스캔은 말 그대로 느슨하게 혹은 듬성듬성하게 인덱스를 읽는 것을 의미한다.
루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만,
중간마다 필요치 않은 인덱스 키값은 무시하고 다음으로 넘어가는 형태로 처리한다.
일반적으로 GROUP BY 또는 집합 함수 중 MAX(), MIN() 함수에 대해 최적화하는 경우 사용된다.


B-Tree 인덱스의 가용성과 효율성

쿼리의 WHERE 조건이나 GROUP BY 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고
어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다.
그래야 쿼리의 조건을 최적화하거나 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.

1) 비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교(“=”)인지
아니면 크다(“>”) 또는 작다(“<”)와 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태가 달라지며,
효율 또한 달라진다. 예제로 알아보자.

SELECT * FROM dept_emp 
WHERE dept_no='d002' AND emp_no >= 10114;

위 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만 다른 2가지 케이스로 인덱스를 생성했다고 가정하자.

케이스 A는 dept_no='d002' AND emp_no >= 10114 인 레코드를 찾고
그 이후에는 dept_no='d002'가 아닐 때까지 인덱스를 읽기만 하면 된다.
이 경우 읽은 레코드가 모두 사용자가 원하는 결과임을 알 수 있다.
즉 5건의 레코드를 찾는 데 꼭 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 이용한 것이다.

하지만 케이스 B는 우선 emp_no >= 10144 AND dept_no='d022'인 레코드를 찾고,
그 이후 모든 레코드에 대해 dept_no='d022'가 맞는지 비교하는 과정을 거쳐야 한다.
이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택을 하는 작업을 필터링이라고도 한다.

작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서
(최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 처리 성능을 높이지는 못한다.
오히려 쿼리 실행을 더 느리게 만들 때가 많다.

2) 인덱스의 가용성
B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다.
여기서 왼쪽이라 함은 하나의 컬럼 내에서뿐만 아니라 다중 칼럼 인덱스의 칼럼에 대해서도 함께 적용된다.

SELECT * FROM employees WHERE first_name LIKE '%mer';

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다.
그 이유는 first_name 칼럼에 지정된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데.
조건절에 주어진 상수값에는 왼쪽 부분이 고정되지 않았기 때문이다.

다음은 케이스 B의 인덱스가 지정된 dept_emp 테이블에 대한 쿼리이다.

SELECT * FROM dept_emp WHERE emp_no >= 10144;

인덱스가 (dept_no, emp_no) 칼럼 순서대로 생성돼 있다면 인덱스의 선행 칼럼인 dept_no 값 없이
emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다.
케이스 B의 인덱스는 다중 칼럼으로 인덱스가 만들어졌기 때문에 dept_no에 대해 먼저 정렬한 후,
다시 emp_no 칼럼으로 정렬돼있기 때문이다.


project

CREATE INDEX IDX_USER_PET ON User (petId);   
CREATE INDEX IDX_USER_LOCATION ON User (locationId);  

CREATE INDEX IDX_PROFILE_PET ON PetProfile (petId);  
CREATE INDEX IDX_PROFILE_USER ON UserProfile (userId);  

CREATE INDEX IDX_BREED_PET ON PetBreed (petId);  
CREATE INDEX IDX_CHARACTER_PET ON PetCharacter (petId);   
CREATE INDEX IDX_INTEREST_PET ON PetInterest (petId);  

CREATE SPATIAL INDEX IDX_LOCATION_POINT ON Location (point);

우선 자주 조회하고 중복이 없는 key에 index를 생성했고
사용자의 주소 좌표를 저장하는 point는 가까이에 있는 사용자를 조회해야 하기 때문에
SPATIAL INDEX로 생성했다.
나중에 거리 계산 로직이 결정되면 EXPLAIN으로 key가 원하는대로 나오는지 확인이 필요하다.



© 2022 Hyerin. All rights reserved.