mysql 쿼리 튜닝 기본

#디스크 접근 방식

Sequential Access

mySQL은 디스크에 저장된 데이터에 접근할 때 페이지 단위로 접근한다. 페이지는 데이터를 검색하는 최소 단위이다. 물리적으로 인접한 페이지를 순차적으로 읽는 방식으로 보통 테이블 풀 스캔에 사용된다. 데이터를 찾고자 이동하는 디스크 헤더의 움직임을 최소화하여 작업 시간과 리소스 점유 비용을 줄일 수 있다.

Randon Access

물리적으로 떨어진 페이지들에 임의로 접근하는 방식으로 페이지가 위치한 물리적인 위치를 고려하지 않고 접근한다. 디스크 헤더가 정해진 순서 없이 이동하므로 디스크의 물리적인 움직임이 많아 오래 걸린다.

 

# 오브젝트 스캔 유형

테이블 풀 스캔

인덱스를 거치지 않고 테이블을 처음부터 끝까지 훑어보는 스캔 방식이다. 테이블 풀 스캔은 인덱스가 없이 바로 디스크에 위치한 테이블 데이터에 접근하는 유일한 스캔 방식이다.

 

인덱스 풀 스캔

인덱스를 처음부터 끝까지 수행하는 방식으로 테이블에 접근하지 않고, 인덱스로 구성된 열 정보만 요구하는 SQL 문에서 인덱스 풀 스캔이 수행된다.

테이블 풀 스캔에 비해 스캔하는 양이 적으나 인덱스를 모두 스캔하므로 검색 범위를 최대한 줄이는 방향으로 튜닝이 필요하다.

 

인덱스 Range 스캔

SQL 문에서 BETWEEN ~ AND 구문이나 >, <, lIKE 구문 들의 비교 연산 구문이 있을때 사용된다.

Index seek + index scan + 데이터 읽기 과정으로 이루어진다.

1. index seek: 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾음.(레코드의 시작 지점 찾기)

2. index scan: 탐색된 위치부터 필요한 만큼 leaf node의 인덱스를 차례대로 쭉 읽음.

3. 인덱스의 leaf node에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어옴. Leaf node에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 디스크 랜덤 I/O가 발생함. =>비용이 많이 드는 작업.

 

인덱스 unique scan

primary 키나 고유 인덱스로 테이블에 접근하는 방식으로, WHERE 절에 =조건으로 작성한다. WHERE절의 조건이 primary key나 unique index의 선두 열로 설정되었을 때 사용된다.

 

인덱스 loose scan

듬성듬성하게 인덱스를 읽는 것으로 인덱스 range 스캔과 비슷하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. 

 

인덱스 merge scan

테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식이다. WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 사용한다. merge하는 방식에는 union과 intersection 방식이 있다. 개별 인덱스를 각각 수행해야 하므로 인덱스에 접근하는 시간이 더 오래 걸린다.

 

참고 서적)

http://www.yes24.com/Product/Goods/102382080

업무에 바로 쓰는 SQL 튜닝 - YES24