이 글은 Real MySQL 스터디에서 성우님이 발표한 발표자료입니다.
MySQL에서 사용 가능한 인덱스의 종류 및 특성에 대한 장으로 **쿼리의 성능**을 논할 때 빼놓을 수 없다.
각 인덱스의 특성과 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도, 중요한 요소가 될 것이다.
MySQL 8.0 버전까지 업그레이드 되어 오면서 다른 상용 RDBMS에서 제공하는 많은 기능을 지원하게 됐다.
기존의 MyISAM 스토리지 엔진에서만 제공하던 전문 검색이나 위치 기반 검색 기능도 모두 InnoDB 스토리지 엔진에서 사용할 수 있게 개선 됐다.
하지만 아무리 MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자의 역할은 매우 중요하다.
그래서 인덱스에 대한 기본 지식은 지금도 앞으로도 개발자나 관리자에게 매우 중요한 부분이며, 쿼리 튜닝의 기본이 될 것이다.
8.1 디스크 읽기 방식
인덱스에만 의존적인 용어는 아니다.
Random I/O, Sequential I/O 와 같은 디스크 읽기 방식을 먼저 간단히 알아보고 인덱스를 살펴보자
컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전했지만, 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했다.
비록 치근에는 디스크 원판에 의존하는 하드 디스크 보다, SSD 드라이브가 많이 활용되고 있지만, 여전히 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다.
데이터베이스나 쿼리 튜닝에 어느 정도 지식을 갖춘 사용자가 절감하고 있듯이 데이터베이스 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건일 때가 상당히 많다.
8.1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이드 드라이브(SSD)
컴퓨터에서 주요 장치들은 대부분 전자식 장치지만, 하드 디스크 드라이브는 기계식 장치다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다.
이러한 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시되고 있다.
SSD도 기존 하드 디스크 드라이브와 같은 인터페이스(SATA나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용할 수 있다.
SSD는 HDD에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착하고 있다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.
플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 컴퓨터의 D-RAM보다 느리지만 기계식 HDD보다는 훨씬 빠르다.
💡 주요 장치의 초당 처리 횟수
CPU : 1 B
DRAM : 100 M
SSD : 100 K
HDD : 0.2 K
예전에는 비쌌지만, 요즘에는 DBMS용으로 사용할 서버에는 대부분 SSD를 채택하고 있다.
Sequential I/O(순차 I/O)
- 디스크의 헤더를 움직이지 않고 한번에 많은 데이터를 순차적으로 읽음.
- SSD가 HDD보다 조금 빠르거나 거의 비슷한 성능을 보임
Random IO (랜덤 I/O)
- random에서 압도적으로 SSD가 빠르다.
DB 서버에서 순차 I/O 작업은 비중이 크지 않고, 랜덤 I/O 를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이다.
8.1.2 Random I/O, Sequential I/O
랜덤 I/O
- HDD의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 데스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미함
Sequential I/O도 작업 과정은 똑같다.
순차 I/O는 3개의 페이지(3 x 16KB) 를 디스크에 기록하기 위해 1번 시스템 콜을 요청했지만. 랜덤 I/O는 3번 시스템 콜을 요청했다.
즉 디스크에 기록해야 할 위치를 찾기 위해
순차 I/O 는 디스크 헤더를 1번, 랜덤 I/O는 디스크 헤더를 3번 움직였다.
디스크 헤더의 움직임이 디스크에 데이터를 쓰고 읽는데 걸리는 시간을 결정한다.
이 경우, 순차 I/O는 랜덤 I/O보다 3배 빠르다고 할 수 있다.
여러번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨 씬 더 크다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등의 기능이 내장되어 있다.
디스크 원판을 가지지 않는 SSD는 랜덤 I/O와 순차 I/O의 차이가 없을 것으로 예측하지만, 실제로는 SSD 에서도 랜덤 I/O가 순차 I/O보다 전체 스루풋이 떨어진다.
그래서 SSD 드라이브의 사양에도 항상 순차 I/O와 랜덤 I/O의 성능 비교를 구분해서 명시한다.
💡 랜덤 I/O, 순차 I/O 둘다 파일에 쓰기를 실행하면 반드시 동기화(fsync or flush)가 필요하다 근데 순차 I/O도 동기화가 계속 발생하면 똑같이 느려진다. 기업용으로 사용하는 DB 서버에는 캐시 메모리가 장착된 RAID 컨트롤러가 일반적으로 사용되는데, 빈번한 동기화 작업이 효율적으로 처리될 수 있게 변환하는 역할을 한다. SSD에서도 여전히 RAID 컨트롤러가 중요한 역할을 한다.
쿼리를 튜닝해서 랜덤I/O를 순차 I/O로 바꿔서 실행할 방법은 그다지 많지 않다.
쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이다. → 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것
💡 데이터 읽을 때!
- 인덱스 레인지 스캔
- 랜덤I/O
- 풀 테이블 스캔
- 순차I/O
큰 테이블의 레코드 대부분은 인덱스를 사용하지 않고 풀 테이블 스캔을 유도할 때가 많음
순차I/O가 랜덤 I/O보다 훨씬 많은 레코드를 읽어올 수 있음
OLTP(online transaction processing)성격의 웹서비스보다 데이터 하우스나 통계작업에서 자주 사용된다
8.2 인덱스란?
💡 색인!
찾아보기 : 인덱스
책의 내용은 : 데이터 파일
페이지 번호 : 레코드 주소
column의 값과, 해당 레코드가 저장된 주소를 key - value 페어로 삼아 인덱스를 만들어 두는 것 O(1)
책의 찾아보기 와 DBMS 인덱스의 공통점 가운데 중요한 것은 정렬
DBMS의 인덱스도 Column의 값을 주어진 순서로 미리 정렬해서 보관한다
SortedList : DBMS의 인덱스와 같은 자료구조
- 저장되는 값을 항상 정렬
- 데이터 업데이트가 느리고 복잡하다. (insert, update, delete)
- 데이터 Access는 빠르다 (select)
ArrayLisy : 데이터 파일과 같은 자료구조
- 저장되는 순서 그대로 유지
테이블의 인덱스를 하나더 추가할지 말지는, 데이터 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빨리 만들어야 하는지에 따라 결정해야한다.
Select 쿼리 문장의 Where조건절에 사용되는 Column이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.
인덱스의 역할로 구분
💡
PK(primary key)
- 레코드를 대표하는 column의 값으로 만들어진 인덱스
- 해당 레코드를 식별할 수 있는 기준 값
- 식별자
- NULL값 X, 중복 X
💡
Secondary Index
- PK를 제외한 나머지 모든 index
- unique index는 pk와 성격이 비슷하고 대체할 수도 있다 (대체 키)
데이터 저장 방식으로 구분
B-Tree와 Hash 로 구분
최근에는 Fractal-Tree 인덱스, 로그 기반의 Merge-tree 인덱스와 같은 알고리즘을 사용하는 DBMS도 개발되고 있다.
💡
B-TREE
- column의 값을 변형하지 않고 원래의 값을 이용해 indexing
- MySQL 서버에서는 위치 기반 검색을 지원하기 위해 R-Tree 인덱스 알고리즘도 있다
- R-Tree 인덱스는 B-TREE의 응용 알고리즘이다.
💡
Hash
- column의 값으로 해쉬값을 계산해서 인덱싱 하는 알고리즘
- 매우 빠른 검색을 지원한다
- 값을 변형해서 인덱싱
- 온전한 값을 탐색하는 경우가 아닐때 사용할 수 없다(값의 부분으로 탐색 X)
- 메모리 기반의 DB에서 사용한다
데이터의 중복 허용 여부로 구분
unique index or non-unique index
unique는 항상 1개의 값만 찾으면 더이상 할 필요 없음을 옵티마이저에게 알려 준다
인덱스의 기능별로 구분
전문 검색용 인덱스, 공간 검색용 인덱스
더 많지만 이정도만 ..
8.3 B(alanced)-TREE 인덱스
가장 일반적인 인덱싱 알고리즘 (가장 먼저 도입된 알고리즘)
가장 범용적인 목적으로 사용 된다 (Default)
일반적으로 DBMS에서 주로 사용하는 응용 알고리즘으로 B+-Tree , B*-Tree 가 있다.
column 의 원래 값을 변형시키지 않고, 인덱스 구조체 내에서는 항상 정렬된 상태로 유지된다.
전문 검색과 같은 특수한 요건이 아닌 경우 대부분 인덱스는 B-TREE를 사용할 정도로 일반적인 용도에 적합한 알고리즘이다.
8.3.1 구조 및 특성
DB에서는 index와 실제 데이터가 저장된 데이터는 따로 관리 된다.
Root Node
- 최상위 노드, 하위에 자식 노드가 붙어 있음
Leaf Node
- 가장 하위에 있는 노드
- 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 지니고 있다.
Branch Node
- 중간에 있는 노드
그림에 보듯이 인덱스의 키값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않고 임의 순서로 되어 있다. (Insert 순서는 아니다, 수정 삭제가 발생하기 때문에)
레코드가 삭제되어 빈 공간이 생기면 그다음 insert는 해당 빈 공간을 재활용되도록 DBMS가 설계되어 있음.
💡 대부분의 DBMS과는 달리, InnoDB 테이블에서 레코드는 클러스트 되어 디스크에 저장되므로 기본적으로 PK 순서로 레코드가 정렬되어 저장된다.
오라클의 IOT(Index organized table)나 MS-SQL의 클러스트 테이블과 같은 구조를 말한다.
다른 DBMS에서는 선택 사항이지만, InnoDB에서는 디폴트로 클러스트링 테이블이 생성된다
클러스트링 이란?
비슷한 값을 최대한 모아서 저장하는 방식
인덱스는 테이블의 key Column 만 갖고 있으므로, 나머지 Column을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 이를 위해 인덱스릐 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가진다.
MyISAM 테이블의 인덱스와 데이터 파일의 관계(그림 8.5)
Record 주소
- MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 Insert된 순서 or 데이터 파일 내의 위치(offset)
InnoDB 테이블의 인덱스와 데이터 파일의 관계
PK가 ROWID 의 역할을 한다.
💡 ROWID
- 테이블에서 행의 위치를 지정하는 논리적인 주소값
- DB 전체에서 중복되지 않는 유일값
두 테이블 인덱스의 차이점
secondary index를 통해 데이터 파일의 레코드를 찾는 방법에서 큰 차이가 있다.- MyISAM
- 물리적인 주소를 가진다
- InnoDB
- PK를 주소처럼 사용하기 떄문에, 논리적인 주소를 가진다.
- MyISAM
InnoDB 테이블에서 데이터를 읽을 때, 그림 MyISAM처럼 바로 찾아가지 못한다.
- index에서 pk 검색
- pk를 통해 leaf node 검색
- leaf node를 통해 record를 읽음
InnoDB는 data record를 읽기 위해 반드시 pk를 저장하고 있는 B-Tree를 다시 검색해야한다
단순하게 생각하면 성능이 더 떨어져 보이지만, 각각 장단점이 따로 있다 (8.8 참고)
8.3.2 B-TREE 인덱스 키 추가 및 삭제
8.3.2.1 인덱스 키 추가
저장될 키값 이용해 B-Tree 상의 적절한 위치 검색.
위치가 결정되면 Record의 키 값과 대상 레코드의 주소 정보를 B-TREE의 리프노드에 저장
리프노드가 꽉 찰 경우, 리프노드 split(저장 범위가 넘어갈 경우 상위 node에서 재분배 필요)
B-Tree는 상대적으로 Insert에 비용이 많이 든다
대략적으로 계산하는 방법
: 레코드 추가비용 : 1 = 인덱스 키 추가 비용 : 1.5
테이블에 인덱스가 3개가 있다 가정할때,
- 테이블에 인덱스가 하나도 없는 경우 비용 1
- 3개인 경우 5.5 (1.5*3 +1) 정도의 비용이 발생한다.
비용의 대부분이 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야해서 걸리는 시간이다.
(디스크 느려)
MyISAM 이나 Memory 스토리지 엔진을 사용하는 테이블에서는 Insert 문장이 실행되면 즉시 새로운 키 값을 B-TREE 인덱스에 변경한다.
InnoDB 의 경우 조금 더 지능적으로 처리한다. 필요하다면 Index Key 추가 작업을 지연시켜 나중에 처리할 수 있다. 하지만 PK나 unique index의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree 에 추가하거나 삭제한다. (Change Buffer 참고)
8.3.2.2 인덱스 키 삭제
심플하다. 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다.
그대로 방치하거나 재활용할 수 있다.
마킹 작업 또한 디스크 쓰기가 필요한 동작이므로 디스크 I/O가 필요한 작업이다.
MySQL 5.5 이상에서 InnoDB 스토리지 엔진은 이 또한 지연 처리 할 수 있다.
8.3.2.3 인덱스 키 변경
index의 키 값은 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키값이 변경 되는 경우에는 단순히 인덱스상의 키 값을 변경하는 것은 불가능하다.
- 키 값 제거 → 새로운 키값 추가
- 삭제와 추가는 8.3.2.1, 과 8.3.2.2에서 설명한대로 이루어짐.
8.3.2.4 인덱스 키 검색
- 빠른 검색 몰빵
- B-tree의 루트부터 시작해서, 최종 리프 노드 까지 이동하면서 비교 작업 수행 (트리 탐색)
- 인덱스 트리탐색은 update, delete를 위해 검색할 때도 사용
- B-tree 인덱스 검색은 100% 일치 또는 값의 앞부분(left-most part) 만 일치하는 경우에 사용
- 비교 조건에서도 활용 가능
- 값의 뒷부분 검색은 할 수 없음
- 인덱스의 키값에 변형이 가해진 후 비교되는 경우, 빠른 검색기능을 사용할 수 없다
- 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아니다.
- 함수나 연산을 수행한 결과로 정렬하거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없다.
InnoDB 스토리지 인덱스
레코드 잠금이나, 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후, 테이블의 레코드르 ㄹ잠그는 방식으로 구현되어 있다. update나 delete 문장이 실행될 때 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다 (테이블의 모든 레코드를 잠글 수도 있다)
InnoDB 스토리지엔진에서는 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.
8.3.3 B-Tree 인덱스 사용에 영향을 미치는 요소
- column의 크기
- record의 수
- unique index 개수
8.3.3.1 인덱스 키 값의 크기
Page or Block
: 데이터를 저장하는 기본 단위
: 디스크의 모든 읽기 쓰기 작업의 최소 작업 단위
: InnoDB → 데이터 버퍼링 기본 단위
: index도 page 단위로 관리 된다.
binary tree
- 자식 노드를 2개 가짐.
DBMS의 B-Tree는 이진 트리 아니다. 자식 노드의 개수가 가변적인 구조
B-Tree의 자식 노드의 최대 수
- 인덱스의 페이지 크기와 키 값의 크기에 따라 결정
innodb_page_size 를 통해 4kb~ 64kb를 설정할 수 있다. (기본값 : 16kb)
페이지의 종류 별로 6바이트~12바이트 다양한 크기의 값을 가질 수 있다.
여러 페이지를 읽어야할 경우, 디스크를 읽는 횟수가 늘어나므로, 느려진다!
인덱스 캐싱하는 영역은 크기가 제한적이다. 그래서 키 값의 길이가 커지면 메모리 효율이 떨어진다.
8.3.3.2 B-Tree 깊이
직접 제어할 방법 없음.
B-Tree의 깊이는 MySQL에서 값을 검색할 떄 몇번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다.
인덱스 키 값이 커진다
→ 인덱스 페이지의 키 값의 개수가 적어진다
→ B-Tree 깊이가 깊어진다
→ 디스크 읽기가 더 많이 필요하다
인덱스 키 값의 크기는 가능하게 작게 만들자
(실제로는 아무리 대용량 데이터베이스라도 깊이가 5단계 이상 깊어지는 경우는 흔치 않다)
8.3.3.3. 선택도(기수성)
selectivity == Cardinality
유니크한 값의 수를 의미한다
전체 100개중 유니크한 값이 10개라면 기수성은 10이다.
중복된 값이 많아질 수록 기수성은 낮아지고 선택도 떨어지낟.
선택도가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
- 전체 레코드 10,000건 , country 칼럼으로만 인덱스가 생성된 상태.
- case A : country 칼럼의 unique : 10
- case B : country 칼럼의 unique : 1000
mysql> Select * From tb_test where country="'korea' and city='seoul';mysql엣서 index의 통계 정보(number of unique key)가 관리되기 때문에 city 칼럼의 기수성은 작업 범위에 아무런 영향을 미치지 못한다.
A케이스의 경우 평균 1,000 건 B케이스의 경우 평균 10건이 조회될 수 있다는 것을 인덱스의 통계 정보로 예측할 수 있다.
A케이스와 B 케이스 모두 실제 모든 조건을 만족하는 레코드는 단 1건만 있었따면 A케이스의 인덱스는 적합하지 않은 것이라고 볼 수 있다. (A케이스는 1건의 레코드를 위해 쓸모 없는 999건의 읽음, but B케이스는 9번 더 읽음, A케이스가 비효율적임)
예시 ) tb_city, 10,000건을 갖고 있고 country column만 인덱스가 준비되어 있다. tb_city 테이블에는 국가와 도시가 중복해서 저장돼 있지 않다고 가정.
mysql > Create table tb_city (
country varchar(10),
city varchar(10),
index ix_country(conuntry)
);mysql> select * from tb_test, where country = 'korea' and city = 'seoul';내부적인 쿼리나 인덱스의 효율성을 살펴보자
- country 칼럼의 유니크 값이 10개 일때
- 10개 국가의 도시 정보가 저장되어 있다고 예상 가능 (도시 정보는 1000건)
- mysql 서버는 인덱스된 칼럼(country)에 대해서는 전체 레코드의 건수나 유니크한 값의 개수 등에 대한 통계 정보가 있다.
- 레코드 건수 / 유니크한 값 수 = 하나의 키 값으로 검색했을 때 일치하는 레코드의 수
- country = ‘korea’로 할 경우 1000건(10,000/10)이 일치 한다고 예상 가능
- index를 통해 검색된 1000건 가운데 city = ‘seoul’인 레코드는 1건이므로 999건은 불필요하게 일음
- country 칼럼의 유니크 값이 1000개 일대
- 국가 : 1000개 도시 : 10개 (예상 10,000 / 1000)
- 전체 레코드 수 / 국가 칼럼의 유니크 값의 개수 = 10건.
- tb_city 에서 country = korea로 검색 하면 10건이 일치 (10,000/1,000) 10건 중 city = seoul은 1건이므로 9건 불필요하게 읽음
- 국가 : 1000개 도시 : 10개 (예상 10,000 / 1000)
index에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.
8.3.3.4 읽어야 하는 레코드의 수
인덱스를 거치는게 더 높은 비용임. (안거치는 것보다)
100만건 중 50만을 읽어야 할때,
- 전체를 읽어서 50만건을 버릴지?
- 인덱스를 통해서 50만건만 읽어 올지?
효율적인 것을 잘 판단해야함.
일반적인 DBMS의 옵티마이저에서
- 인덱스를 통한 1건의 레코드 읽기 * 4~5 = 테이블에서 직접 읽기
- 읽어야 하는 수가 전체의 20~25%를 넘어서면 인덱스는 비효율적이다
- 넘어설 경우 전체를 읽어서 가려내는 방식으로 (필터링) 처리하는 것이 효율적
8.3.4 B-Tree 인덱스를 통한 데이터 읽기
8.3.4.1 인덱스 레인지 스캔
- 가장 대표적인 방법
- 빠름 (나머지보다)
- 하나를 읽는 것과 한건 초과를 읽는 경우 다르게 구분한다 (10장 실행계획 참고)
mysql > select * from employees where first_name between 'ebbe' and 'gad';인덱스의 범위가 결정 됐을 때 사용하는 방법
시작해야할 위치를 찾으면 그 때부터 리프노드의 레코드만 읽으면 된다.
차례대로 쭉 읽는 것을 스캔이라고 한다
최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.
루트와 브랜치 노드를 이용해 스캔 시작위치 검색. 이후 필요 방향으로 인덱스 읽어나감
인덱스를 구성하는 순서로 레코드를 가져옴. (인덱스 정렬 때문)
데이터 파일에서 레코드를 읽어오는 과정이 필요함
- 레코드 한건 한건 단위로 랜덤 I/O가 발생함
- 3건의 레코드가 검색 조건에 일치했다고 가정할 경우 위의 경우 최대 3번 발생
- 읽어야할 데이터 레코드가 20~25%가 넘으면 인덱스 ㄴㄴ
- 인덱스 조건 만족하는 값이 저장된 위치 찾기 (
index seek) - 필요한 만큼 인덱스 읽기 (
index scan) - 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽음
쿼리가 필요로 하는 데이터에 따라 3번은 없을 수 있음 → 커버링 인덱스
랜덤 읽기가 줄어 들고 성능은 그만큼 빨라지낟.
8.3.4.2 인덱스 풀 스캔
- 인덱스 레인지 스캔과 똑같지만 전체를 읽는다.
- 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫번째 칼럼이 아닌 경우 사용된다
- 인덱스는 A,B,C 순서로 만들어져 있지만, 쿼리의 조건절은 B, C로 검색하는 경우
- 테이블 전체를 읽는 것보다 효율적임.
인덱스의 리프노드를 연결하는 linked list를 따라서 처음부터 검색하는 것을 의미
8.3.4.3 루스 인덱스 스캔
Loose index scan. (인덱스 스킵스캔과 비슷)
타이트 인덱스 스캔
- 인덱스 레인지/풀 스캔
루스 인덱스 스캔
- 듬성듬성 읽는 것
인덱스 레인지 스캔과 비슷하지만, 불필요한 인덱스 키 값은 SKIP
Group by , MAX(), MIN() 에 대해 최적화 하는 경우 사용
8.3.3.4 인덱스 스킵 스캔
조건절을 이용한 검색을 위해 사용 가능하도록 도입.
조건절에 해당하느 인덱스들만 읽어 들임.
단점
-
Where 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함
-
쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야함(커버링 인덱스)
유니크한 값의 개수가 매우 많다면, 옵티마이저가 스캔해야할 시작 지점을 검색하는 작업이 많이 필요해진다. (느려짐)
유니크한 값의 개수가 적을 때만 적용 가능한 최적화
8.3.5 다중 칼럼 인덱스
- 실제 서비스에서는 2개이 상의 칼럼을 포함하는 인덱스가 더 많이 사용된다.
- Concatenated index 라고도 한다.
첫번째 칼럼(이전 칼럼)의 의존해서 정렬된다.
인덱스 내에서 칼럼의 위치가 상당히 중요하다
8.3.6 B-TREE 인덱스의 정렬 및 스캔 방향
- 항상 오름차순이거나, 내림차순으로 정렬된다.
- 정렬 순서와 다른 방향으로도 읽을 수 있다.
- 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.
8.3.6.1 인덱스의 정렬
8.0 부터는 정렬 순서를 혼합한 인덱스도 생성 가능하다.
8.3.6.1.1 인덱스 스캔 방향
- 옵티마이저가 인덱스의 읽기 방향을 전환해서 사용하도록 실행
8.3.6.1.2 내림차순 인덱스
내부적으로 인덱스 역순 스캔이 인덱스 정순 스캔보다 느릴수 밖에 없다. (InnoDB)
- 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
- 페이지 내에서 인덱스 레코드가 단방향 연결
8.3.7 B-Tree 인덱스의 가용성과 효율성
8.3.7.1 비교 조건의 종류와 효율성
다중 칼럼 인덱스에서 비교 연산에 따라 인덱스 칼럼의 활용 형태가 달라지고 효율도 달라진다.
칼럼의 순서만 달라도, 처리되는 과정이 달라진다. 무엇을 먼저 비교하냐에 따라 효율이 변경된다.
필터링 : 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 것
작업 범위 결정 조건 필터링 조건
작업 범위를 결정하는 조건은 많으면 많을 수록 쿼리의 성능을 높인다. 필터링은 ㄴㄴ
8.3.7.2 인덱스의 가용성
왼쪽값에 기준해서 오른쪽 값이 정렬되는게 특징임.
다중 칼럼 인덱스의 칼럼에 대해서도 함께 적용된다.
8.3.6.3 가용성과 효율성 판단
(예시들 잘 읽어보기)
사용할 수없는 조건에 대한 설명. (작업 범위 결정 조건 으로 사용 X)
경우에 다라 체크 조건으로 인덱스를 사용할 수 있다.
- Not - equal
- like ‘%??” (문자열 패턴, 앞부분X)
- 스토어드 함수, 다른 연산자로 인덱스 칼럼이 변형된 후 비교 (substring, dayofmonth 등 조건)
- not-deterministic 속성의 스토어드 함수가 비교(column = deterministic_function())
- 데이터 타입이 서로 다른 비교(칼럼 변환 필요할 경우)
- 문자열 데이터 타입의 콜레이션이 다른 경우
NULL값도 인덱스에 저장되기 떄문에, Where 조건도 작업범위 결정 조건으로 인덱스를 사용한다 작업 범위 결정으로
.. where column is NULL...다중 칼럼 작업 범위 결정 조건
- 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- 작업 범위 결정 조건으로 인덱스를 사용하는 경우
8.4 R-TREE 인덱스
공간 인덱스(Spatial Index) 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스
B-Tree는 1차원인 반면에 R-Tree는 2차원인(공간개념 값)
- 공간 데이터를 저장할 수 있는 데이터 타입
- 공간 데이터의 검색을 위한 공간 인덱스(R-tree 알고리즘)
- 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리
8.4.1 구조 및 특성
지원하는 데이터 타입
- Point, Line, Polygon, Geometry
Geometry 타입은 슈퍼 타입으로 다른 타입을 모두 저장할 수 있다.
MBR(Minimum Bounding Rectangle) → boundary box를 생각하자
MBR로 나타냈을 때,
- 최상위 레벨R1,R2 (root node)
- 차상위 레벨 R3, R4,R5, R6 (branch node)
- 최하위 R7~R14 (leaf node)
8.4.2 R-Tree 인덱스의 용도
Rectangle B-Tree → R-Tree
공간 인덱스(Spatial) 인덱스
일반 적으로 WGS84 기준의 위도 경도 좌표 저장에 주로 사용된다.
CAD/CAM 에서도 사용 된다.
ST_Distance() 와 ST_Distance_Sphere() 함수는 공간 인덱스를 효율적으로 사용하지 못하기 때문에
ST_Contains() 또는 ST_within()을 이용해서 거리 기반의 검색을 해야 한다.
ST_Contains()와 ST_within()연산은 사각형 박스와 같은 다각형으로만 연산할 수 있으므로 반경 5Km을 포함하는 최소 사각형 MBR()으로 포함 관계 비교를 통해서 해야한다.
ST_CONTAINS()를 또는 ST_WITHIN()을 이용해 사각 상자에 포함된 좌표 검색
mysql> select * from ab_location
where st_contains(사각상자, px);
mysql> select * from tb_location
where st_within(px, 사각 상자);두 함수의 파라미터는 반대로 사용해야함.
첫번째 파라미터는 함수의 기준.
p6를 제거해야한다면 st_distance sphere()를 이용해 필터링 해야한다
select * from tb_location
where st_contains(사각상자, px)
and st_distance_sphere(p,px) <= 5*1000 