Real MySQL

랜덤 I/O vs 순차 I/O

랜덤은 요청이 들어올 때 페이지마다 디스크 헤드를 움직인다. 순차 I/O는 디스크가 꽉차지 않는 한 한번의 요청에 대해 한번 디스크 헤드를 움직인다. disk에 데이터를 쓰고 읽는데 걸리는 시간은 디스크 헤더를 움직이는 단계에서 결정된다. 따라서 우리가 쿼리를 튜닝하는 것은 랜덤 I/O 발생 빈도를 줄여 처리시간을 줄이는 목적이다.

결론적으로 dbms에서 인덱스는 데이터의 저장(insert, update, delete)성능을 희생하여 읽기 성능을 높이는 기능이다. 인덱스를 하나 더 추가할지 말지는 저장속도를 어디까지 희생하고 읽기속도를 얼마나 더 빠르게 만들어야 하는지로 결정한다.

MySQL에는 위치 기반 검색을 지원하기 위한 R-Tree index 알고리즘도 있다. 유니크 인덱스와 논유니크 인덱스가 있다. 유니크 인덱스일 경우 dbms가 = 쿼리에 대해 하나의 record를 찾으면 더 이상 찾지 않아도 됨을 의미한다.

data file은 정렬돼있지 않다. insert 연산만 수행되었다면 삽입된 순서대로 정렬되어 있겠지만 중간에 delete연산이 일어날 경우 빈 공간을 재활용하여 insert연산을 수행하도록 dbms가 설계되기 때문이다. -> innoDB에서는 pk에 의해 정렬된다.

innoDB에서 data file은 pk를 논리적 주소로 사용하므로 pk를 안다고 해서 O(1)로 접근이 안된다. data file이 pk에 의해 다시 b-tree형태로 정렬되어 있으므로 이를 읽어야 record에 접근이 가능하다.

인덱스 키를 추가할 때 update나 insert 연산이 얼마나 지연되는지 대략적으로 계산하는 법은 disk table에 record를 저장하는 비용을 1이라 할 때 인덱스 테이블에 해당 키를 추가하는 비용을 1.5로 계산하는 것이다. index가 없는 table에 작업 비용이 1이면 index가 3개 있는 table이라면 작업 비용이 1 + 1.5*3 = 5.5 가 되는 것이다.

b-tree 에서 인덱스 키 값이 삭제되는 경우는 간단하다. 해당 키 값이 저장된 b-tree의 리프노드를 찾아서 삭제마크만 하면 끝난다. 해당 공간은 그대로 방치되거나 재활용할 수 있다. 키값이 update되는 경우는 삭제,삽입으로 처리된다.

index table에서 index 탐색이 이루어질 때 pk들은 인덱스에 의해 정렬되어 있다. 하지만 disk file은 pk에 의해 정렬되어 있으므로 index 탐색결과로 얻은 pk의 접근은 랜덤 i/o로 이루어진다. 따라서 index를 사용하여 얻은 data가 전체의 20%미만인 경우에만 index를 타는 것이 이득이다. 아니면 인덱스를 탈 수 있는 쿼리를 날려도 dbms 옵티마이저가 full scan을 실시한다.

index 풀 스캔: 인덱스의 첫번째 컬럼이 where절에 포함되지 않은 쿼리가 들어왔는데 인덱스 테이블에 존재하는 정보만을 필요로 하는 경우 disk file 풀 스캔보다 index table 풀 스캔이 효율적이므로(메모리이고 table 크기도 작으므로) index 풀 스캔이 이루어진다.

루스 인덱스 스캔: group by와 min 또는 max가 같이 쓰이는 경우. index table의 data는 어차피 index column 기준으로 정렬되어 있으므로 처음 또는 끝의 data만 읽으면 된다. 읽고 점프하는 것이다.

인덱스 스킵 스캔: MySQL 8.0 부터 지원하는 기능. 쿼리의 요구 data가 index table에 다 포함되어 있는 경우에 원래는 index의 첫번째 컬럼이 where에 포함되지 않으면 효율적인 스캔이 불가능하여 새로운 index를 만들어줘야 했다. 하지만 인덱스 스킵 스캔이 이것을 해결해주었다. 예를 들어 첫번째 컬럼이 남/녀인 경우 그 이후의 컬럼에 대해 '남'에서 스캔을 실시하고 '녀'로 건너뛰어 다시 스캔을 실시한다. 쿼리의 요구 data가 index table에 모두 있어야 하고 위의 예시처럼 남/녀 같이 where절에 없는 컬럼의 유니크한 값의 개수가 적어야 한다.

ㅊ

인덱스 정렬은 MySQL 8.0 부터 오름차순, 내림차순을 선택할 수 있게 되었다. 또한 스캔을 할 때에도 옵티마이저가 빠른 방향을 선택해서 오름차순, 내림차순을 선택해서 읽을 수 있다. 그렇다면 옵티마이저가 방향도 설정할 수 있는데 왜 인덱스 정렬방향을 선택해야 하는가? b-tree 구조상 인덱스를 정방향으로 읽는 것이 역방향으로 읽는 것보다 빠르기 때문이다. 그 이유는 data가 저장되어 있는 페이지 내부의 data는 heap 형태로 되어있기 때문인데, 페이지 잠금도 정방향이 오버헤드가 적게 일어난다고 한다. (왜그런지 까지는 모르겠다...)

따라서 조회가 자주일어나는 쿼리인지를 따져서 인덱스를 추가하는 것이 나은지 아닌지를 비교해봐야 한다.

다중컬럼 인덱스에서 오름차순, 내림차순이 섞여있는 경우 내림차순으로만 읽을 수 있다는데 뭔말인지 모르겠다.

필터링 조건 vs 작업 범위 결정 조건

공식 명칭은 아님.

select * from table where a = 1 and b >= 3000

다음과 같은 쿼리가 있다고 하자.

• 인덱스 A: (a,b)

• 인덱스 B: (b,a)

라고 하자.

인덱스 A를 타면 쿼리의 조건을 만족하는 data가 5개 있다고 하면 딱 5번의 비교만 실행할 수 있다. a,b에 대해 조건을 만족하는 row를 찾은뒤에 a!=1 인 row가 나올 때 까지만 스캔하면 된다. 하지만 인덱스 B의 경우를 봐보자. b를 만족하는 row를 찾은 뒤에 끝까지 스캔하고 나서 a=1인 애들을 골라내는 작업을 해야한다.

이런 상황에서 인덱스 A에서 a,b조건은 모두 작업 범위 결정 조건으로 작용한 거싱고 인덱스 B에서 b는 작업 범위 결정 조건으로 작용했지만 a는 필터링으로 작동했다고 한다.

작업 범위 결정 조건은 많으면 많을 수록 쿼리의 처리 성능을 높이지만 필터링 조건은 최종 레코드의 크기는 줄일지 몰라도 쿼리의 성능을 높이지는 못한다. 트레이드 오프가 있는 것.

R-Tree 인덱스

공간인덱스를 만드는데 사용되는 알고리즘. MBR의 개념이 중요한데 MBR이란 Minimum Bounding Rectangle의 약자로 도형을 감싸는 최소크기의 사각형을 말한다. MySQL에서는 point, line, polygon, geometry 의 네가지 데이터 타입을 제공한다. gemetry는 나머지 3개 타입의 슈퍼타입으로 3가지 객체를 모두 저장할 수 있다. 이제 이 MBR들을 또 더 큰 MBR이 감싸는 식으로 하여서 계층 관계가 생기고 이를 바탕으로 R-Tree를 생성한다.

그리고 ST_Contains() 또는 ST_Within() 의 함수를 사용하여 어떤 MBR에 포함되는지 여부를 where절의 조건으로 걸 수 있다. 내가만든 반경 3km이내의 가게 반환 api같은 경우 위의 함수를 사용하여 3km정사각형으로 걸러낸 뒤, ST_Distance_Sphere()함수를 사용하여 필터링하는 방식으로 구할 수 있겠다. 필터링이야 서버에서 해도 되지만 db i/o를 통해 전달되는 데이터양을 줄이는 것이 이득이기 때문에 하는 것이 좋다.