MySQL의 주요 처리 방식

1. 풀 테이블 스캔

 

풀테이블 스캔이란?

인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업

MyISAM 스토리지 엔진

• 풀 테이블 스캔시 디스크로부터 페이지를 하나씩 읽어 온다.

 

InnoDB

• 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업을 자동으로 시작한다.
• 이 경우 필요한 데이터를 미리 읽어 두었기 때문에 좀 더 빠른 성능을 보장할 수 있다.

 

+ Read ahead?

어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것

---

2. ORDER BY 처리, Using filesort

• 인덱스를 사용한 정렬
  • 이미 정렬된 기준이 인덱스를 이용해서 정렬을 시도하면 빠른 성능을 낼 수 있다.
  • 스트리밍 방식을 사용해 클라이언트는 데이터를 바로바로 얻을 수 있다.

 

• File sort를 이용한 정렬
  • 정렬할 레코드가 적다면 소트 버퍼(Sort buffer)를 이용한 정렬을 사용할 수 있어 충분히 빠른 성능을 낼 수 있지만 레코드 수가 많으면 디스크에 데이터를 임시로 저장하는 방식을 이용하기 때문에 상대적으로 느린 성능을 보인다.
  • 버퍼링 방식을 이용하기 때문에 모든 데이터가 정렬된 이후에 그 결과를 클라이언트에 반환하기 때문에 스트리밍 방식을 사용하는 인덱스를 이용한 정렬에 비해 성능상 취약하다.

• File sort를 이용한 정렬 : 드라이빙 테이블만 정렬 (조인 하지 않은 경우)

조인시 결과 레코드는 몇 배로 불어난다. 그래서 조인 전에 드라이빙 테이블만으로 정렬을 할 수 있다면 정렬한 대상 레코드 수가 상대적으로 적고 소트 버퍼를 이용한 정렬 방식을 사용할 확률이 높기 때문에 조인 이후 정렬을 하는 방식보다 좋은 성능을 보여준다.

 

• File sort를 이용한 정렬 : 조인 결과를 임시 테이블로 저장

조인 결과를 정렬하는 방식은 정렬 처리 방식 중에 최악의 방법이다.

레코드가 많을 가능성이 크기 때문에 성능도 위의 방식보다 느리다.

 

+ 어떤 정렬 방식으로 처리되는지가 정렬 처리에서 성능을 좌우하는 큰 요소이기 때문에 쿼리를 튜닝할 때 이러한 점을 고려하여 튜닝하는 것이 좋다.
+ JDBC 버퍼링 처리방식
JDBC는 기본 설정으로 버퍼링 방식을 체택해서 사용하기 때문에 스트리밍, 버퍼링 방식에 따른 차이가 없다. 

---

3. GROUP BY 처리

타이트 인덱스 스캔

• GROUP BY 칼럼으로 인덱스가 있는 경우, 그 인덱스를 순서대로 읽으면서 그룹핑 처리를 하는 방식이다. 
• 그룹 함수 등의 그룹 값을 처리해야하는 경우, 임시 테이블을 사용해야하는 경우도 있다.

 

루스 인덱스 스캔

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져와 처리하는 방법
• 임시 테이블이 필요하지 않다.

 

임시 테이블을 이용한 GROUP BY

• 인덱스를 전혀 사용하지 못하는 경우에는 임시 테이블을 이용한 방식을 사용한다.

---

4. 임시테이블 처리

임시 테이블이 필요한 쿼리

• 유니크 인덱스가 존재하는 임시테이블 생성하는 경우
  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  • ORDER BY 나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리
• 유니크 인덱스가 없이 임시테이블 생성하는 경우
  • UNION ALL이 사용된 쿼리
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 상당히 처리 성능이 느림

 

임시 테이블이 디스크에 생성되는 경우 = 뭔가 크다.

• 임시 테이블에 저장해야 하는 내용 중 BLOB(Binary Large Object)나 TEXT와 같은 대용량 칼럼이 있는 경우
• 임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512 바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 임시 테이블에 저장할 데이터의 전체 크기가 tmp_table_size 또는 max_heap_table_size 시스템 설정 값보다 큰 경우

메모리를 통한 처리가 힘들 정도로 임시테이블이 크면 디스크를 이용한다.

 

임시 테이블 관련 주의사항

• 가능하다면 인덱스 사용
  • 임시테이블을 사용하지 않아서 성능이 좋다.
• 내부 임시 테이블이 메모리에 저장될 수 있게 한다.
  • SELECT하는 칼럼을 최소화하여 레코드 사이즈 줄이기
  • 시스템 변수를 설정해서 메모리 가공 사이즈 늘리기 (너무 크면 DB서버가 가용할 여유 메모리를 모두 사용할 수 있으므로 중간점 찾기)

출처 : Real MySQL 이성욱 지음