Chapter 09.02 기본 데이터 처리

9.2 기본 데이터 처리

• 모든 RDBMS는 데이터 가공 결과물이 동일하나 처리 과정은 벤더별로 차이가 있다. 
• 기본적인 데이터 가공을 위해서 MySQL 서버가 어떤 알고리즘을 사용하는지 알아본다.

 

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

 

  Def) 풀 테이블 스캔

• 인덱스를 이용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업이다.
• 상당히 많은 디스크 읽기가 필요하다. 
• 대부분의 DBMS는 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장한다.
• 그러나 MySQL은 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다. 
  
  • 풀 테이블 스캔을 실행할 때
  • MyISAM 스토리지 엔진: 디스크로부터 페이지를 하나씩 읽어온다.
  • InnoDB 스토리지 엔진: 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해서 리드 어헤드(Read ahead, 어떤 영역의 데이터가 앞으로 필요해질 거라고 예측해서 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것)작업이 자동으로 시작된다. 
• 테이블 풀 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread, 포그라운드 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 
• 백그라운드 스레드가 넘겨받는 시점부터는 한 번에 4개, 8개씩 페이지를 읽으면서 계속 그 수를 증가시킨다. 
• 한 번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장한다. 

 

• 옵티마이저는 다음 조건을 만족할 때, 주로 풀 테이블 스캔을 이용한다.
  • 테이블의 레코드 건수가 너무 적어서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
  • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절 조건이 없는 경우
  •  인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우, (인덱스의 B-Tree 를 샘플링해서 조사한 통계 정보 기준)

 

9.2.2 병렬 처리

• MySQL 8.0 부터는 MySQL 서버에서도 쿼리의 병렬 처리가 가능해졌다.
  • 병렬 처리: 하나의 쿼리를 여러 스레드가 작업을 나눠서 동시에 처리하는 것을 의미한다.
• innodb_parallel_read_threads: 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할 지 변경할 수 있다. 
  • SET SESSION innodb_parallel_read_threads = 1;   (1, 2, 4, 8 가능)
  • 허용하는 스레드 개수가 많을수록 쿼리 처리 시간이 줄어든다.
  • 하지만 서버에 장착된 CPU의 코어 개수를 넘어서는 경우는 성능이 떨어질 수 있다.
• 아직 MySQL 서버에는  쿼리를 여러 개의 스레드를 이용해서 병렬로 처리하게 하는 힌트나 옵션이 없다. ???????
  • WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬 처리가 가능하다.

 

 

9.2.3 ORDER BY 처리 (Using filesort)

 

  정렬 처리 방법

1. 인덱스 이용
   • INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼있어서 순서대로 읽기만 하면 되므로 매우 빠르다. 
   • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느린다. 
   • 디스크 공간이 많이 필요
   • 인덱스 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요
2. Filesort 이용
   • 정렬할 레코드가 많지 않으면 메모리에서 Filesort 가 처리되므로 충분히 빠르다. 
   • 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느리다. 

 

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

 

 

 

9.2.3.1 소트 버퍼(Sort buffer)

 

  Def) 소트 버퍼(Sort buffer)

• MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아서 사용하는데 이 메모리 공간을 소트 버퍼라고 한다. 쿼리 실행이 끝나면 메모리는 즉시 반납된다.
• 소트 버퍼는 정렬이 필요한 경우만 할당된다. 
• 버퍼의 크기는 정렬해야할 레코드 크기에 따라 가변적으로 증가
• sort_buffer_size: 최대한 사용 가능한 소트 버퍼의 공간 
  • 크다면 메모리에서 모두 처리되니까 빠를까?
  • 그렇지 않다. 또한, 리눅스 계열의 OS에서는 너무 큰 sort_buffer_size를 사용하면 메모리 할당 때문에 성능이 더 떨어질 수 있다. 
• 정렬해야할 레코드 건수가 소트 버퍼로 할당된 공간보다 큰 경우는?
  • 이 경우, MySQL은 정렬할 레코드를 여러 조각으로 나눠서 처리하는데 이 과정에서 임시 저장을 위해 디스크를 사용한다. 
  • 메모리의 소트 버퍼에서 정렬을 수행하고 결과를 임시에 디스크에 저장한다. 
  • 다음 레코드를 가져와서 다시 정렬 해서 반복적으로 디스크에 임시 저장한다. 
  • 멀티 머지(Multi-merge): 위와 같이 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬하는 작업을 말한다. 
• Sort_merge_passed: 수행된 멀티 머지 횟수에 누적하여 집계된다. 
•  정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역에 해당한다. 
  • 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아니다. 
  • 커넥션이 많을수록 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미한다. 
• 소트 버퍼의 크기를 10MB 이상으로 설정하면 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시 실행되고 OS는 메모리 부족을 겪을 수 있다. 
  • 여유 공간이 없으면 OS의 OOM-Killer가 여유 메모리를 확보하기 위해 프로세스를 강제 종료할 것이다. 
  • 일반적으로 MySQL 서버가 메모리를 가장 많이 잡아먹기 때문에 종료 1순위이다.
• 대량 데이터 정렬이 필요하다면?
  • 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄인다. 

 

 

9.2.3.2 정렬 알고리즘

 

• 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 정렬 모드를 나눈다.
  • 싱글 패스(single-pass): 기본적으로 정렬 키와 레코드 전체를 가져와서 정렬하는 방식이다. 소트 버퍼에 정렬 기준 칼럼을 포함해서 SELECT 대상이 되는 칼럼 전부를 담아서 정렬하는 방식이다. 
    • 1) 레코드의 칼럼들은 고정 사이즈로 메모리 저장하는 방식
    • 2) 레코드의 칼럼들은 가변 사이즈로 메모리 저장하는 방식, 정렬을 위한 메모리 공간의 효율적 사용을 위해 새롭게 도입된 방식이다. 
  • 투 패스(two-pass): 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식
    • 정렬 대상 칼럼과 PK 값만 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 PK로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 정렬 방식이다. 
    • 싱글 패스 정렬 방식이 도입되기 전부터 사용되던 방식이다. 
    • MySQL 8.0 에서는 여전히 특정 조건에서는 투 패스 정렬 방식을 이용한다. 

 

 

9.2.3.2.1 싱글 패스 정렬 방식

 

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

• 싱글 패스는 그림과 같이 employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name까지 모두 읽어서 소트 버퍼에 담고 정렬을 수행한다. 
• 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨준다. 

 

 

9.2.3.2.2 투 패스 정렬 방식

 

 

• 투 패스 정렬 방식
  • 옛날 방식
  • 테이블을 읽을 때는 정렬에 필요한 first_name 칼럼과 PK인 emp_no만 읽어서 정렬을 수행한다.
  • 정렬이 완료되면 그 결과 순서대로 employees 테이블을 한 번 더 읽어서 last_name 을 가져오고
  • 결과를 최종적으로 클라이언트에 넘긴다.
  • 테이블을 두 번 읽어서 합리적이지는 않다. <=> 싱글 패스와의 차이
  • 대신 싱글 패스는 더 많은 소트 버퍼가 필요하다. 
  • ex) 128KB의 정렬 버퍼를 이용할 때 투 패스 방식은 7000건의 레코드 정렬 가능하지만 싱글 패스는 반 정도 밖에 정렬할 수 없다. 물론 이것은 소트 버퍼의 크기와 레코드 크기에 의존한다. 
• MySQL 서버가 투 패스 정렬 방식을 이용하는 경우
  1. 레코드 크기가 max_length_for_sort_data 변수보다 클 때
  2. BLOB이나 TEXT 타입 칼럼이 SELECT 대상에 포함될 때

 

• 두 가지 방식을 주로 어떤 경우에 쓸까?
  
  • 싱글 패스 방식: 정렬 대상 레코드의 크기나 건수가 적은 경우에 효율적
  • 투 패스 방식: 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우에 효율적

 

 

9.2.3.3 정렬 처리 방법

 

• 쿼리에 ORDER BY가 사용되는 경우 아래 세 가지 방법 중 하나로 정렬이 처리된다.
• 아래 목록에서 1 ~ 3 으로 갈수록 속도는 떨어진다.
• 정렬 처리 방법 - 실행 계획의 Extra 칼럼의 내용 
  1. 인덱스를 이용한 정렬 - 별도 표기 없음
  2. 조인에서 드라이빙 테이블만 정렬 - "Using filesort" 메시지가 표시됨
  3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 - "Using temporary; Using filesort" 메시지가 표시됨

 

• 옵티마이저는 인덱스를 이용할 수 있는지 여부를 확인한다. 
  • 가능한 경우: 별도의 "Filesort"과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다.
  • 불가능한 경우: WHERE 조건에 일치하는 레코드를 검색해서 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)할 것이다. 
  • 이 때, 옵티마이저가 정렬 대상 레코드를 최소화하기 이해 두 가지 중 하나를 선택한다.
    1. 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행한다. - 2번 보다 효율적
    2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후, 정렬을 수행한다. 
  • 조인이 수행되면 보통 레코드 건수와 레코드 크기가 거의 배로 늘어난다. => 1번이 더 효율적이다.  
    • 따라서, 드라이빙 테이블만 정렬한 다음 조인을 수행하는 것이 효과적이다.

 

 

9.2.3.3.1 인덱스를 이용한 정렬

 

• 인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고 ORDER BY의 순서대로 생성된 인덱스가 있어야한다. 
• ??????????? WHERE 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야한다. 
• 인덱스를 이용해서 정렬이 처리되는 경우는 실제 인덱스 값이 정렬돼있기 때문에 인덱스 순서대로 읽기만 하면 된다. 
• ORDER BY가 있든 없든 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력된다.
  • ORDER BY절이 없어도 정렬 되는 이유는?
  • PK를 읽은 다음에 salaries 테이블을 조인했기 때문이다. 

 

 

 

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

 

  Ex) 

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;

• 조인을 수행하면 결과 레코드 건수가 늘어나고 레코드 하나하나의 크기도 늘어난다.
• 따라서, 첫 번째 테이블의 레코드를 정렬한 다음에 조인을 실행한다. 
  • 이 방법으로 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼 만으로 ORDER BY절을 작성해야한다. 
  • 드라이빙 테이블: 옵티마이저가 조인의 기준으로 정하는 테이블
• WHERE 절이 다음 2가지 조건을 갖추고 있으므로 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다. 
  1. WHERE 절의 검색 조건("emp_no BETWEEN 100001 AND 100010")은 employees 테이블의 PK를 이용해서 검색하면 작업량을 줄일 수 있다. 
  2. 드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 있다. 

 

1. 인덱스를 이용해서 "emp_no BETWEEN 100001 AND 100010" 은 employees 테이블의 PK 를 이용해서 검색하면 작업량을 줄일 수 있다. 
2. 검색 결과를 last_name 칼럼으로 정렬을 수행한다 (Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인해서 86개의 최종 결과를 가져온다. 
   • salaries 테이블의 오른쪽 번호는 레코드가 조인되어 출력되는 순서를 의미한다. 

 

 

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

• 2개 이상의 테이블을 조인해서 그 결과를 정렬하는 경우 임시 테이블이 필요할 수도 있다.

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 where; Using temporary; Using filesort"
  • "Using where; Using temporary; Using filesort": 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬 처리했다는 뜻이다.

 

 

 

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

• 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 그 다음에야 LIMIT로 건수를 제한할 수 있다.
• WHERE 조건이 아무리 인덱스를 잘 활용해서 튜닝해도 잘못된 ORDER BY 나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다. 
• 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수밖에 없을까?

 

 

9.2.3.3.4.1 스트리밍(Streaming)방식

• Def) 스트리밍 방식: 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 말한다. 
  
  • 이 방식으로 처리하면 클라이언트는 쿼리를 요청하고 원했던 첫 번째 레코드를 바로 전달 받는다. 
  • 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터 가공 작업을 시작할 수 있다. 
  • 웹 서비스 같은 OLTP(Online transaction processing, 온라인 트랜잭션 처리) 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달 받는데까지의 응답 시간이 중요하다.
  • 스트리밍 방식은 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장한다. 

 

 

 

9.2.3.3.4.2 버퍼링(Buffering) 방식

• Def) 버퍼링 방식
  • ORDER BY나 GROUP BY같은 쿼리는 쿼리 결과가 스트리밍 되는 것을 불가능하게 한다. 
  •  조건에 일치하는 모든 레코드를 가져온 후, 정렬 or 그루핑해서 순서대로 보내야하기 때문이다.
  • MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업하는 동안에 클라이언트는 그냥 기다려야 하므로 응답 속도가 느려진다.  => "버퍼링" 이라고 표현
  • 버퍼링 방식 처리되는 쿼리는 결과를 모아서 MySQL 서버에서 일괄 가공해야한다.  따라서 모든 결과를 스토리지 엔진으로 가져올 때까지 기다려야한다. 
    • 따라서 LIMIT로 결과가 줄어들어도 성능 향상에 도움이 안 된다. 
    • 네트워크로 전송되는  레코드 건수가 줄더라도 MySQL 서버가 해야하는 작업량에는 그다지 변화가 없기 때문이다. 

 

 

 

  Ex) 조인과 함께 ORDER BY절과 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건이며 두 테이블의 조인 결과는 전체 1000건이라고 가정한다. 
• 정렬 처리 방법별로 읽어야하는 레코드 건수와  정렬을 수행해야하는 레코드 건수를 비교한다. 

 

 

 

• 어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지 여부가 더 큰 성능 차이를 만들 수 있다. 
• 먼저 인덱스 사용한 정렬로 유도하고 그렇지 못하면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로  유도하는 것도 좋은 튜닝 방법이다. 

 

 

 

9.2.3.4 정렬 관련 상태 변수

• MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다. 
• 정렬과 관련해서 몇 건의 레코드나 정렬 처리를 수행했는지 소트 버퍼간의 병합 작업(멀티 머지)은 몇 번이나 발생했는지 확인할 수 있다. 

FLUSH STATUS;
SHOW STATUS LIKE 'Sort%';

 

• Sort_merge_passes: 멀티 머지 처리 횟수
• Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수 
• Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다. Sort_scan과  Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값이다.
• Sort_rows: 지금까지 정렬한 전체 레코드 건수를 의미한다. 

 

• 결과
  • 풀 테이블 스캔의 결과를 1번(Sort_scan 상태 변수 값) 정렬
  • 단위 정렬 작업의 결과를 13번Sort_merge_passes 병합 처리
  • 전체 정렬된 레코드의 건수는 300,024(Sort_rows 상태 변수 값)

 

 

 

9.2.4 GROUP BY 처리

• GROUP BY는 ORDER BY처럼 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나이다. 
  • GROUP BY절에는 HAVING절 사용해서 필터링 가능하다. 
  • GROUP BY에 사용된 조건을 인덱스를 사용해서  처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성할 필요 없다. 
• GROUP BY
  • 인덱스 사용하는 경우
    1. 인덱스 스캔 방법: 인덱스를 차례대로 읽는다.
    2. 루스 인덱스 스캔: 인덱스를 건너뛰면서 읽는다.
  • 인덱스 사용하지 않는 경우
    • GROUP BY 작업은 임시 테이블을 사용한다. 

 

 

 

9.2.4.1 인덱스 스캔을 이용하는 GROUP BY (타이트 인덱스 스캔)

• ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해서그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. 
• 인덱스를 이용해서 GROUP BY를 처리하더라도 그룹 함수의 그룹값을 처리해야하는 경우는 임시 테이블이 필요하다. 

 

 

 

9.2.4.2 루스 인덱스 스캔을 이용하는  GROUP BY 

• 루스 인덱스 스캔: 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 걸 의미한다. 
• 루스 인덱스 스캔을 사용하면 extra 칼럼에  "Using index for group-by" 라고 표시된다. 

 

 

  Ex) 

EXPLAIN
    SELECT emp_no
    FROM salaries
    WHERE from_date='1985-05-31'
    GROUP BY emp_no;

• 테이블의 인덱스는 (emp_no, from_date)로 생성되어 있으니까 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리이다.
• 실행 계획은 인덱스 레인지 스캔을 이용한다. 
• GROUP BY처리 까지 인덱스를 이용한다. 
• 쿼리 실행 순서
  1. (emp_no, from_date) 인덱스를  차례대로 스캔하면서 emp_no의 첫번째 유일한 값(그룹 키) "10001"을 찾아낸다.
  2. (emp_no, from_date) 인덱스에서 emp_no가  '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다. 
     • 이 방법은 emp_no = 10001 AND from_date = "1985-03-01" 조건으로 (emp_no, from_date) 인덱스를 검색하는 것과 흡사하다. 
  3. (emp_no, from_date) 인덱스에서  emp_no의 다음 유니크한(그룹 키) 값을 가져온다. 
  4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복을 수행한다. 
• 프리픽스 인덱스(prefix index, 칼럼 값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없다. 
• 루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 복잡하다. 

 

 

  Ex) MIN()과  MAX() 이외의 집합 함수가 사용됐기 때문에 루스 인덱스 스캔을 사용 불가능한 경우

SELECT col1, SUM(ccol2) FROM tb_test GROUP BY col1;

 

 

  Ex) GROUP BY 에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않기 때문에  루스 인덱스 스캔을 사용 불가능 

SELECT col1, col2 FROM tb_test GROUP BY col2, col3;

 

 

  Ex) SELECT 절의 칼럼이 GROUP BY 와 일치하지 않기 때문에 루스 인덱스 스캔을 사용 불가능

SELECT col1, col3 FROM tb_test GROUP BY col1, col2;

 

 

 

9.2.4.3 임시 테이블을 사용하는 GROUP BY

• GROUP BY 의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. 

 

 

  Ex 1) ORDER BY 절이 없는 경우

EXPLAIN
    SELECT e.last_name, AVG(s.salary)
    FROM employees e, salaries s
    WHERE s.emp_no=e.emp_no
    GROUP BY e.last_name;

• 실행 결과: 쿼리의 실행 계획을 확인하면 extra 칼럼에 "Using temporary" 메시지 표시
  • 임시 테이블 사용한 이유는?
  • employees 테이블을 풀 스캔하지 않고 인덱스를 전혀 사용할 수 없는 GROUP BY 이기 때문이다. 
• 그런데 Using filesort 는 표시되지 않는다. 
  • MySQL 8.0 이전 버전 까지는 GROUP BY가 사용된 쿼리는 그루핑되는 칼럼 기준으로 묵시적 정렬까지 수행했다.
  • 그래서 GROUP BY는 있지만 ORDER BY절이 없는 쿼리에 대해서 기본적으로 그루핑 칼럼인 last_name 칼럼으로 정렬된 결과를 반환했다. 
  • MySQL 8.0 버전부터는 묵시적 정렬이 더 이상 실행되지 않는다. 
• MySQL 8.0 에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 칼럼으로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다. 

CREATE TEMPORARY TABLE ... (
    last_name VARCHAR(16),
    salary INT, 
    UNIQUE INDEX ux_lastname (last_name)
);

• 서버는 쿼리를 수행하기 위해 위와 같은 임시테이블을 만들어서 중복 제거, 집합 함수 연산을 수행한다. 
• 그리고 조인의 결과를 한 건씩 가져와서 임시 테이블에서 중복 체크하면서 INSERT, UPDATE를 실행한다. 
  • 별도의 정렬 작업 없이 GROUP BY 처리된다. 

 

 

  Ex 2) ORDER BY 절이 있는 경우 

EXPLAIN
    SELECT e.last_name, AVG(s.salary)
    FROM employees e, salaries s
    WHERE s.emp_no=e.emp_no
    GROUP BY e.last_name
    ORDER BY e.last_name;

• 실행 결과: 쿼리의 실행 계획을 확인하면 extra 칼럼에 "Using temporary;  Using filesort" 메시지 표시
• MySQL 8.0은 GROUP BY, ORDER BY 절이 같이 사용되면 명시적으로 정렬 작업을 실행한다 .
• 예제 1번과 다르게 Using filesort 가 추가된다.

 

 

9.2.5 DISTINCT 처리

• Def) DISTINCT: 특정 칼럼의 유니크한 값만 조회하기 위해 SELECT 쿼리에 사용한다.
  • 집합 함수를 사용하는 경우와 사용하지 않는 경우를 나눠서 살펴볼 예정이다. 
  • DISTINCT가 영향을 미치는 범위가 달라지기 때문이다. 
  • SELECT하는 레코드를 유니크하게 SELECT 하는 것이지 특정 칼럼만 유니크하게 조회하는 것이 아니다. 

 

 

9.2.5.1 SELECT DISTINCT ...

• SELECT 되는 레코드 중에서 유니크한 레코드만 가져올 때 쓴다. 
• GROUP BY와 동일한 방식으로 처리된다.

 

 

  Ex) 

SELECT DISTINCT emp_no FROM salaries;

SELECT emp_no FROM salaries GROUP BY emp_no;

• 두 쿼리는 내부적으로 같은 작업을 수행한다. 

 

 

 

Issue 1

• 9.2.2
• 1) MySQL 서버에서도 쿼리의 병렬 처리가 가능해졌다.
• 2) 아직 MySQL 서버에는 쿼리를 여러 개의 스레드를 사용해서 병렬로 처리하게 하는 힌트나 옵션이 없다. 
  • WHERE 조건없이 단순히 전체 건수를 가져오는 쿼리만 병렬 처리가 가능하다. 
• 1), 2) 내용 모순

 

 

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」