MySQL(9)

오늘 한 것

9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

인덱스에 대한 내용은 8장에서 봤고, 여기선 풀 테이블 스캔 에 대한 내용을 보자.

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

옵티마이저는 다음과 같은 조건일때 풀 테이블 스캔을 선택한다.

  • 테이블의 레코드 건수가 너무 작아서 인덱스보다 풀테이블 스캔이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
  • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 BTree를 샘플링해서 조사한 통계 정보 기준)

일반적으로 테이블이 인덱스보다 훨씬 크므로 테이블 풀스캔은 상당한 disk IO를 요구한다. 그래서 대부분의 DBMS는 풀 테이블 스캔을 실행할때 한꺼번에 여러개의 블록이나 페이지를 읽어오는 기능을 내장한다.

하지만 MySQL에는 테이블 풀 스캔을 할때 한꺼번에 몇개씩 페이지를 읽어올지 설정하는 시스템 변수는 없어서 MySQL은 한번에 하나의 페이지만 읽어오는 것으로 생각한다.

  • 이것은 MyISAM에선 맞지만, InnoDB에선 틀린말이다. 특정 테이블의 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.

리드 어헤드 작업이란, 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.

즉, 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만, 특정 시점부터는 백그라운드 스레드로 넘긴다. 이때부턴 한번에 4~8개의 페이지를 읽어 계속 그 수를 증가시킨다.

이때 한번에 최대 64개까지 읽어 버퍼 풀에 저장한다.

포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 쓰면 되서 쿼리가 상당히 빨리 처리된다.

리드 어헤드는 풀 인덱스 스캔에서도 동일하게 사용된다.

SELECT COUNT(*) FROM employees;

이 쿼리는 아무런 조건없이 실행되므로 풀 테이블 스캔을 할것처럼 보이지만, 실제 실행계획은 풀 인덱스 스캔을 할 가능성이 높다. 이 예제와 같이 단순히 레코드의 건수만 필요로 하는 쿼리라면, 용량이 작은 인덱스를 선택하는 것이 dist IO를 줄일 수 있기 때문이다.

일반적으로 인덱스는 테이블 2~3개의 칼럼만으로 구성되므로 테이블 자체보다는 용량이 작아서 훨씬 빠른 처리가 가능하다.

하지만 다음과 같이 레코드에만 있는 칼럼이 필요한 쿼리는 풀 테이블 스캔을 한다.

SELECT * FROM employees;

9.2.2 병렬 처리

8.0부터 용도가 한정된 병렬 쿼리처리가 가능하다.

   여기서 말하는 병렬처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것.
   여러 스레드가 동시에 각각 쿼리를 처리하는것은 1.0부터 가능했음.

8.0에서는 아무런 WHERE조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다. \


SET SESSION innodb_parallel_read_threads=N(원하는 숫자)
SELECT COUNT(*) FROM salaries

병렬 처리용 스레드 개수를 아무리 늘리더라도, 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는, 오히려 성능이 떨어질 수 있다.

9.2.3 ORDER BY 처리(Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면, 대부분의 SELECT쿼리에서 정렬은 필수적으로 사용된다. 데이터 웨어하우스 처럼 대량의 데이터를 조회해서 일괄 처리하는 기능이 아니라면, 아마도 레코드 정렬 요건은 대부분의 조회 쿼리에 포함되 있을 것이다. 정렬을 처리하는 방법은 2가지로 나뉠 수 있다.

  1. 인덱스 이용

    장점 : INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어서 순서대로 읽기만 하면 되서 빠르다.

    단점 : 해당 작업시 부가적인 인덱스 추가, 삭제 작업이 필요하므로 느리다. 그리고 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 innodb의 버퍼풀을 위한 메모리가 많이 필요하다.

  2. FileSort 이용

    장점 : 인덱스를 생성하지 않아도 되므로 인덱스 단점이 장점으로 바뀐다., 정렬 레코드가 적다면, 메모리에서 Filesort가 처리되므로 충분히 빠르다.

    단점 : 정렬 작업이 쿼리 실행 시 처리되므로, 레코드 대상 건수가 많아질수록 쿼리 응답속도가 느리다.

물론 filesort를 항상 정렬 작업시 사용하는건 아니다. 8.3.6 Btree 인덱스의 정렬 및 스캔방향에서 인덱스 정렬은 봤다.

하지만, 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능하다.

  • 정렬 기준이 너무 많아서 요건 별로 모두 인덱스를 생성하는 것이 불가능한 경우
  • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우

이제 MySQL의 정렬이 어떻게 처리되는지 살펴보자. 정렬 특성을 이해하면 쿼리 튜닝시 어떻게 하면 조금이라도 더 빠른 쿼리가 될 지 판단할 수 있다.

9.2.3.1 소트 버퍼

정렬을 수행하기 위한 별도의 메모리 공간을 sort buffer 라고 한다.

정렬이 필요한 경우에만 할당되며, 레코드 크기에 따라 가변적이나, 시스템 변수로 설정할 수 있다.

이 메모리 공간은 쿼리의 실행이 완료되면 반환된다.

여기까진 이상적인 내용인데, 이게 정렬이 문제가 되는 이유를 보자. 레코드가 소량이라면 빠르게 정렬이 처리된다.(filesort 메모리 처리 충분히 빠르다 어쩌구)

레코드의 건수가 작으면 소트버퍼 내에서 메모리 공간에서 빠르게 정렬처리한다. 레코드가 소트버퍼 보다 크면 어떻게 처리하지?

여러 조각으로 나눠 처리하고, 임시 저장을 위해 디스크를 이용한다. 소트 버퍼에서 정렬을 수행하고, 결과를 임시로 디스크에 기록. 다음 레코드 정렬 디스크 임시저장. 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행.이 병합 작업을 멀티 머지(multi merge) 라고 표한하며, 수행된 멀티 머지 횟수는 Sort_merge_passes라는 상태 변수에 누적해서 집계된다.

이 작업들이 모두 dist io를 유발한다. 레코드가 많을수록 dist io도 증가한다.

9.2.3.2 정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트버퍼에 담을지 또는 정렬 기준 칼럼 만 소트 버퍼에 담을지에 따라 (공식 용어는 아니지만) 싱글패스 와 투 패스 2가지 정렬 모드로 나눌 수 있다.

여기서는 옵티마이저 트레이스 결과로 <sort_key, rowID> , 즉 정렬 키와 레코드의 rowid만 가져와서 정렬하는걸 투 패스. <sort_key, additional_fields>, <sort_key, packed_additioal_fields> : 정렬 키와 레코드 전체를 가져오사ㅓ 정렬하는 방식, 레코드의 칼럼들은 가변 사이즈로 메모리 저장

이 두가지를 싱글 패스 라 명명하겠다.

9.2.3.2.1 싱글 패스 정렬 방식

소트버퍼에 정렬 기준 칼럼 을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;

위 쿼리를 도식화하면 아래와 같다.

ㄴㅇㄹ

그림에서도 보이듯, 필요없는 last_name도 disk에서 읽어서 소트 버퍼에 담고 정렬을 수행한다. 그리고 정렬이 완료되면 그 내용을 그대로 클라이언트에 전달한다.

9.2.3.2.2 투 패스 정렬 방식 (예전방식)
  1. 정렬 대상 칼럼과
  2. PK 값만

소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 PK로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬방식, 싱글패스 방식이 도입되기 이전부터 사용하던 방식.

하지만 8.0에서도 여전히 특정 조건에서는 이러한 투패스 를 사용한다.

ㄴㅇㄹ

이 예전방식은 테이블을 두 번 읽어야 하기 때문에 불합리하다. 싱글패스는 불합리 하지 않다. 하지만, 싱글패스는 소트버퍼를 더 많이 먹는다.

최신 버전에서는 일반적으로 싱글패스 정렬을 주로 사용한다.

  싱글패스 (요즘 메인)
  - 장점
    - 테이블 한 번만 조회
    - 정렬대상 레코드 크기나 건수가 작을때 빠르다.
  - 단점
    - 소트버퍼 많이 잡아먹음
  투패스(옛날 방식)
  - 장점
    - 소트버퍼가 적어도 됨.
    - 정렬대상 레코드 크기나 건수가 클때 효율적이다.
  - 단점
    - 테이블을 2번 조회함.


  언제 투패스로 돌아오냐
  - 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  - BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될때

얼핏 투패스 정렬이 더 빠를것같지만 막상 그렇지만은 않다.

중요

  SELECT쿼리에서 꼭 필요한 칼럼만 조회하지 않고 * 로 가져오도록 하는 경우, 정렬 버퍼를 몇배 몇십배 비효율적으로 사용할 가능성이 크다. 특히 정렬 버퍼에만 영향을 미치는 것이 아니라 임시 테이블이 필요한 쿼리에서도 영향을 미친다.

9.2.3.3 정렬 처리 방법

ORDER BY가 쿼리에 사용되면 다음 3가지 방법 중 하나로 정렬이 처리된다. 아래로 갈 수록 처리 속도가 느려진다.

정렬 처리 방법 실행 계획의 Extra 칼럼내용
9.2.3.3.1 인덱스 사용 정렬 별도 표기 없음
9.2.3.3.2 조인의 드라이빙 테이블만 정렬 “Using filesort” 표시됨
9.2.3.3.3 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 “Using temporary; Using filesort” 표시됨

먼저 옵티마이저는 인덱스 사용을 검토한다. 가능하면, 별도의 FileSort 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다.

안되면, WHERE 조건에 일치하는 레코드를 검색해 정렬버퍼에 저장하면서 정렬을 처리(Filesort)할 것이다. 이때 옵티마이저는 정렬 대상 레코드를 최소화 하기 위해 다음 2가지 방법 중 하나를 선택한다.

  1. 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
  2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

일반적으로 조인이 수행되면서 레코드 건수, 레코드 크기는 거의 배수로 불어나므로, 가능하다면 드라이빙 테이블만 정렬한다음 조인을 수행하는 것이 효율적이다.

9.2.3.3.1 인덱스를 이용한 정렬

  • 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.

  • WHERE절에 첫번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.

  • BTree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스 정렬을 못한다.

  • 예외적으로 Rtree도 Btree 일종이지만, 특성상 인덱스 정렬이 안된다.

  • 여러 테이블이 조인되는 경우에는 네스티드 루프 방식의 조인에서만 이게 가능하다.

인덱스를 이용해 정렬이 처리되는 경우, 실제 인덱스 값이 정렬되어 있으므로 인덱스읭 순서대로 읽기만 하면 된다. 실제로MySQL엔진에서 별도의 정렬을 위한 추가 작업을 수행하지는 않는다.

SELECT *
FROM employees e, salary s
WHERE s.emp_no = e.emp_no
  AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.emp_no;



SELECT *
FROM employees e, salary s
WHERE s.emp_no = e.emp_no
  AND e.emp_no BETWEEN 100002 AND 100020

이 두가지가 모두 같은 순서로 출력된다.

ㅊ튜

ORDER BY절이 없어도 정렬되는 이유는 그림처럼 테이블의 PK를 읽고 그 다음으로 테이블을 조인했기 때문이다.

그래도 정렬이 필요한데 자동으로 해준다고 ORDER BY명시 안하는건 안된다. 어떤 이유로 실행계획이 변경될 경우 버그가 생기고, 가독성도 떨어진다.

인덱스를 사용한 정렬이 가능한 이유는 Btree인덱스가 키 값으로 정렬되어있기 때문이다. 또한 조인이 nested-loop 방식으로 실행되기 때문에 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다. 하지만 조인이 사용된 쿼리의 실행 계획에 조인 버퍼(join buffer)가 사용되면 순서가 흐트러질 수 있다.

9.2.3.3.2 조인의 드라이빙 테이블만 정렬

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어난다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인으르 실행하는 것이 정렬의 차선책이 될 것이다.

이 방법으로 정렬이 처리되려면 조인에서 첫번째로 읽히는 테이블(=드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다.

SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no 
  AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY e.last_name;

우선 WHERE 절이 다음 2가지 조건을 갖추고 있기 때문에 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다.

  • WHERE 절의 검색 조건(“emp_no BETWEEB 100002 AND 100010”)은 employees 테이블의 PK를 이용해 검색하면 작업량을 줄일 수 있다.

  • 드리븐 테이블(salaries)의 조인 칼럼인 emp_no칼럼에 인덱스가 있다.

검색은 인덱스 레인지 스캔으로 처리할 수 있지만, ORDER BY 절에 명시된 칼럼은 employees 테이블의 PK와 전혀 관련이 없으므로 인덱스를 이용한 정렬은 불가능하다. 그런데 ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블(employees)에 포함된 칼럼임을 알 수 있다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한 것이다.

아래 그림 참고

  1. 인덱스를 이용해 “emp_no BETWEEN 100002 AND 100010” 조건을 만족하는 9건을 검색

  2. 검색 결과를 last_name 칼럼으로 정렬을 수행(FileSort)
  3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴 (오른쪽에 있는 번호는 레코드가 조인되어 출력되는 순서를 의미)

ㅁㄴㅇㄹ

9.2.3.3.3 임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 select해서 정렬하는 경우라면 임시 테이블 이 필요하지 않다.

하지만 2개이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다.

앞서 9.2.3.3.2는 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시테이블을 사용하지 않는다. 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.

이 방법은 정렬의 3가지 방법 가운데 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.

다음 쿼리는 “드라이빙 테이블만 정렬” 에서 살펴본 예제와 ORDER BY 절의 칼럼만 제외하고 같은 쿼리다. 이 쿼리도 9.2.3.3.2와 같은 이유로 employees 테이블이 드라이빙 테이블이 되고, salaries가 드리븐 테이블이 될것이다.

SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
  AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary; //이게 차이

하지만, 이번 쿼리에서는 ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 칼럼이다. 즉 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수 밖에 없다.

실행계획을 보면 “Using temporary; Using filesort” 라는 코멘트가 표시된다.

이는 조인 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미한다. 아래 그림은 이 쿼리 결과를 보여준다.

ㅁㄴㅇㄹ

9.2.3.3.4 정렬 처리 방법의 성능 비교

주로 웹서비스용 쿼리에서는 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경향이 있다. 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에, MySQL서버가 처리해야할 작업량을 줄이는 역할을 한다.

그런데 ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼만 가져와서는 처리할 수 없다.

우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 비로소 LIMIT으로 건수를 제한할 수 있다.

따라서 WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.

쿼리에서 인덱스를 사용하지 못하는 정렬이나, 그루핑 작업이 왜 느리게 작동할 수 밖에 없는지 한번 살펴보자.

이를 위해 쿼리가 처리되는 방법을 2가지 방식으로 구분해보자.

9.2.3.3.4.1 스트리밍 방식

ㄴㅁㅇㄹ

그림과 같이 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때 마다 바로바로 클라이언트로 전송해주는 방식을 의미한다. 이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.

물론 가장 마지막 레코드는 언제 받을지 알 수 없지만, 이는 그다지 중요하지 않다.

그림과 같이 쿼리가 스트리밍 방식으로 처리될 수 있다면, 클라이언트는 MySQL서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다. 웹 서비스 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기 까지의 응답 시간이 중요하다. 스트리밍 방식으로 처리되는 쿼리는 빠른 응답시간을 보장한다.

또한 스트리밍 방식으로 처리되는 쿼리에서, LIMIT같이 건수 제한 조건은 쿼리 전체 실행시간을 상당히 줄여줄 수 있다. 매우 큰 테이블을 아무런 조건없이 SELECT 해보면 첫 번째 레코드는 아주 빨리 가져온다는 사실을 알 수 있다. 물론 서버에서는 쿼리가 아직 실행되고 있음에도.

이것은 풀 테이블 스캔의 결과가 아무런 버퍼링, 필터링 과정 없이 바로 클라이언트로 스트리밍 되기 때문이다. 이 쿼리에 LIMIT 조건을 추가하면 전체적으로 가져오는 레코드 건수가 줄어드므로, 마지막 레코드 가져오기까지의 시간이 크게 감소한다.

9.2.3.3.4.2 버퍼링 방식

ORDER BY나 GROUP BY 같은 처리는, 쿼리가 스트리밍 되는 것을 불가능하게 한다.

우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다.

MySQL 서버에서는 모든 레코드를 검색하고 정렬작업을 하는동안, 클라이언트는 아무것도 하지 않고 기다려야 한다(동기방식 느낌) 응답속도가 느려진다.

그래서 스트리밍의 반대 표현으로 버퍼링 이라고도 한다.

ㄴㅁㅇㄹ

먼저 결과를 모아서 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다.

그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다. 네트워크로 전송되는 레코드 건수를 줄일 수는 있으나, MySQL 서버 작업량엔 그다지 변화가 없다.

  참고

  스트리밍 처리는 어떤 클라이언트 도구나 API를 사용하냐에 따라 방식 차이가 있다.
  대표적으로 `JDBC 라이브러리`를 이용해 "SELECT * FROM bigtable" 같은 쿼리를 실행하면 MySQL 서버는 레코드를 읽자마자 클라이언트로 그 결과를 전달한다. 

  하지만 JDBC 는 서버로부터 받는 레코드를 일단 내부버퍼에 담아둔다. 마지막 레코드가 전달되면 비로소 클라이언트 애플리케이션에 반환한다.
  즉, MySQL 서버는 스트리밍 방식으로 처리해서 반환하지만, 클라이언트의 JDBC 라이브러리가 버퍼링 한다.

  하지만 JDBC를 사용하지 않는 SQL 클라이언트 도구는 버퍼링을 안해서 첫번째 레코드는 매우 빨리 가져온다.

그럼 왜 jDBC는 버퍼링을 할까

이 방식이 전체처리(Throughput) 시간이 짧고 서버와의 통신 횟수가 적어 자원 소모가 줄어들기 때문이다. 이 방식은 JDBC라이브러리와 MySQL 서버가 대화형으로 주고받는게 아니라. 서버는 데이터 크기에 관계없이 무조건 보내고, JDBC는 데이터를 저장만 하므로 불필요한 네트워크 요청이 최소화되기 때문에 전체 처리량이 뛰어나다.

그러나 기본이 그렇다는 거고 아주 대량의 데이터를 가져올때는 스트리밍 방식으로 변경할 수 있다.

9.2.3.3에서 소개한 3가지 방법, 인덱스로 정렬하기, 조인의 드라이빙 테이블 정렬하기, 임시테이블 정렬

이 중에서 인덱스 정렬만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링 된 후 정렬된다.

즉, 인덱스 정렬방식은 LIMIT으로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해 줄 수 있다. 하지만 인덱스를 못 쓰는 처리는 모든 레코드를 읽어서 정렬한 후에야 LIMIT으로 잘라서 전송해 줄 수 있다.

SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1=t2.col1
ORDER BY t1.col2
LIMIT 10;

tb_test1 테이블의 레코드가 100건, tb_test2 레코드가 1000 건(tb1 레코드 1건당 tb2 레코드 10건이 있다고 치자.) 두 테이블의 조인 결과는 1000건이라고 가정.

정렬 처리 방법별로 읽어야 하는 레코드 건수와 정렬을 수행햐야 하는 레코드 건수를 비교해보자.

tb_test1이 드라이빙되는 경우

정렬 방법 읽어야 할 건수 조인 횟수 정렬해야 할 대상 건수
인덱스 사용 tb_test1: 1건
tb_test2: 10건		 1번 0건
조인의 드라이빙 테이블만 정렬 tb_test1: 100건
tb_test2: 10건		 1번 100건
(tb_test1 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후 정렬 tb_test1: 100건
tb_test2: 1000건		 100번
(tb_test1 테이블의 레코드 건수만큼 조인 발생)		 1000건
(조인된 결과 레코드 건수를 전부 정렬해야 함)


tb_test2가 드라이빙되는 경우

정렬 방법 읽어야 할 건수 조인 횟수 정렬해야 할 대상 건수
인덱스 사용 tb_test2: 10건
tb_test1: 10건		 10번 0건
조인의 드라이빙 테이블만 정렬 tb_test2: 100건
tb_test1: 10건		 10번 100건
(tb_test1 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후 정렬 tb_test2: 1000건
tb_test1: 100건		 1000번 1000건
(조인된 결과 레코드 건수를 전부 정렬해야 함)

어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능하다면 인덱스 정렬로 유도하고, 최소한 드라이빙 테이블로 유도하는것도 좋은 튜닝 방법이다.

  참고

  인덱스를 사용하지 못하고 별도로 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT조건이 아무런 도움이 되지 못하는 것은 아니다. 정렬할 대상 레코드가 1000건인 쿼리에 LIMIT 10 이라는 조건이 있다면 MySQL 서버는 1000건 레코드를 모두 정렬하는 것이 아니라 필요한 순서(ASC, DESC)대로 정렬해서 상위 10건만 채워지면 결과를 반환한다.

  하지만 MySQL 서버는 퀵소트, 힙소트를 사용한다. 이는 LIMIT 10을 만족하는 상위 10건을 정렬하기 위해 더 많은 작업이 필요할 수 도 있음을 의미한다.

  결론적으로 ,인덱스를 사용하지 못하는 쿼리를 페이징 처리에 사용하는 경우 LIMIT 으로 5~10건만 조회한다고 하더라도 쿼리가 기대만큼 빨라지진 않는다.

9.2.3.4 정렬 관련 상태 변수

정렬관련 상태변수를 따로 저장한다.

results matching ""

    No results matching ""