쿼리 실행절차
쿼리 실행 절차는 보통 다음과 같다.
1. 사용자로부터 요청된 SQL문장을 잘게 쪼개서 MySQL서버가 이해할 수 있는 수준으로 분리한다.(파스 트리)
2. SQL의 파싱 정보를 확인하면서, 어떤 테이블로부터 읽고 어떤 인덱스를 이용해서 테이블을 읽을지 선택한다.
3. 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
여기서 두 번째 단계는 '최적화' 단계라고 할 수 있으며, MySQL서버의 옵티마이저에서 실행한다. 이 두번째 단계가 완료되면, 쿼리의 '실행 계획'이 만들어지는 것이다.
옵티마이저
- 옵티마이저는 쿼리를 최적으로 실행하기 위해, 각 테이블의 데이터가 어떤 분포로 저장돼있는지 통계정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행계획을 수립한다.
- 보통 비용 기반 최적화 방식으로 최적화를 수행함
- 비용 기반 최적화 : 쿼리를 처리하기 위한 여러가지 방법을 만들고, 각 단위 비용정보와 대상 테이블의 예측된 통계 정보를 이용하여 실행 계획별 비용을 산출하는 방식
풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔
- 풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 것
- MySQL 옵티마이저는 보통 다음과 같은 조건일 때, 풀 테이블 스캔을 사용한다.
1. 테이블의 레코드 건수가 너무 적어서, 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
2. WHERE절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
3. 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많을 때
- InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.
- 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서, 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
- 풀 테이블 스캔이 시작되면, 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만, 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
- MySQL 서버에서는 이 임계값을 innodb_read_ahead_threshold 시스템 변수를 활용하여, InnoDB스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 잇음
- 포그라운드 스레드에 의해 위 시스템 변수에 설정된 개수만큼이 연속된 페이지가 읽히면, 백그라운드에서 바로 그 다음페이지들을 읽어서 버퍼풀에 적재시키고...의 반복
풀 인덱스 스캔
- 풀 인덱스 스캔 :테이블을 처음부터 끝까지 스캔하는 것
병렬 처리
- 병렬 처리 : 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것.
- innodb_parallel_read_threads라는 시스템 변수로 스레드의 개수 설정 가능
- 서버에 장착된 CPU의 코어 개수를 넘어서는 경우로 설정하면, 오히려 성능이 떨어질 수 있음
ORDER BY 처리
- 정렬을 처리하는 방법은, 인덱스를 이용하는 방법과, 쿼리가 실행될 때 "FileSort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.
- filesort 사용시 장점 : 인덱스를 생성하지 않아도 되므로, 디스크 공간이 따로 필요 없고, insert, update, delete시 부가적인 인덱스 추가 / 삭제 작업이 필요 없음
- filesort 사용시 단점 : 정렬 작업이 쿼리 실행시 처리되므로, 레코드 대상 건수가 많아질수록 쿼리의 응답속도가 느리다
=> 보통 filesort는 이럴 경우 사용됨
1. 정렬 기준이 너무 많을 때
2. group by, distinct 같은 처리를 사용할 때
3. union결과와 같이 임시테이블의 결과를 정렬해야 할 때....
실행계획의 extra컬럼에서 using filesort 메세지를 통해 fileSort를 사용했는지 알 수 있음 .
소트 버퍼
- MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.
- 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야할 레코드의 크기에 따라 가변적으로 증가한다.
- 정렬해야할 레코드가 건수가 소트 버퍼보다 클 때, MySQL은 정렬해야할 레코드를 여러 조각으로 나눠서 처리하고, 임시 저장을 위해 디스크를 사용한다.
- 멀티 머지 : 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록하고, 그 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장 후 각 버퍼 크기 만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는 것.
- 멀티 머지는 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을 수록 이 반복 작업의 횟수가 많아진다 .
- 소트 버퍼는 세션(로컬) 메모리 영역에 해당하기 때문에, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐
정렬 알고리즘
- 싱글 패스 : 소트 버퍼에 정렬 기준 컬럼을 포함해, SELECT 대상이 되는 컬럼을 전부 담아서 정렬을 수행하는 방식.
=> 컬럼을 모두 담기 때문에, 투 패스 대비 더 많은 소트 버퍼 공간이 필요함. 정렬 대상 레코드의 크기나 건숙 작은 경우 빠른 성능 보임.
- 투 패스 : 정렬 대상 컬럼과, PK값만 소트 버퍼에 담아 정렬을 수행하고, 정렬된 순서대로 다시 PK로 테이블을 읽어서 select할 컬럼을 가져오는 방식 .
=> 테이블을 두 번 읽어야 함. 정렬 대상 레코드의 크기나 건수가 상당히 많을때 효율적임.
최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용함.
정렬 처리 방법
먼저 옵티마이저는 정렬 처리를 위해,
1. 인덱스를 사용할 수 있는지 검토한다.
2. 인덱스를 사용할 수 없다면 where 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 FileSort한다.
3-1. 조인의 드라이빙 테이블만 정렬한 다음 정렬을 수행하거나
3-2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행한다.
일반적으로 조인이 수행되면서, 레코드 건수와 크기는 거의 배수로 불어나기 때문에, 가능하다면 3-1번의 드라이빙 테이블만 정렬한다음 조인을 수행하는 방법이 효율적이다.
정렬 처리 방식
1. 스트리밍 방식
- 데이터가 얼마인지에 관계없이, 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식. 이 방식으로 쿼리를 처리할 경우, 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받음.
2. 버퍼링 방식
- order by나 group by같은 처리는, 조건에 일치하는 레코드를 모두 가져와야하기 때문에 차례대로 보내야하기 때문에 스트리밍되는 것을 불가능하게 함.
=> mysql 서버에서는 모든 레코드를 기다려야 하기 때문에 응답속도가 느려짐.
- group by는 루스 인덱스 스캔(인덱스의 레코드를 건너뛰면서, 필요한 부분만 읽어서 가져오는 것) 방식을 사용함.
내부 임시 테이블 활용
- Mysql 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나, 그루핑할 때는 내부적인 임시 테이블을 활용한다.
- 임시 테이블은 메모리에 생성되었다가, 테이블의 크기가 커지면 디스크로 옮겨진다.
메모리 임시테이블과 디스크 임시 테이블
- 메모리 테이블은 가변길이를 지원하는 Temptable 스토리지 엔진에 저장, 디스크 임시테이블은 InnoDB에 저장
- 임시테이블의 크기가 1기가가 넘어가는 경우, 디스크에 기록하게 되는데, 보통 MMAP 방식이나 InnoDB 테이블에 기록하게 된다.
-> 방식은 temptable_use_mmap 시스템 변수로 설정 가능.
-> 메모리의 TempTable을 MMAP 파일로 전환하게 되는 것은 InnoDB 테이블로 전환하는 것보다 오버헤드가 적음
- mysql서버는 임시테이블을 생성할 때, 파일 오픈 후 즉시 파일 삭제를 실행한다.
=> 이렇게 함으로써, MySQL 쿼리 종료시 임시테이블이 즉시 삭제되도록 보장하고, 서버 내부의 다른 스레드 또는 MySQL 서버 외부의 사용자가 임시 테이블을 위한 파일을 변경 및 삭제하거나 볼 수 없게끔 함.
임시 테이블이 필요한 쿼리
다음은 별도의 데이터 가공작업이 필요해서, 내부 임시테이블을 생성해야하는 케이스이다.
- order by와 group by에 명시된 컬럼이 다른 쿼리
- order by나 group by Id에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
- distinct와 order by가 동시에 쿼리에 존재하는 경우 / distinct가 인덱스로 처리되지 못하는 쿼리
- union이나 union distinct가 사용된 쿼리
- 쿼리의 실행계획에서 select_type이 derived인 쿼리
=> 실행계획에서 using temporary가 있는지 확인하면 됨.
=> 없으면 밑의 3가지 케이스에 쓰인 것
임시 테이블이 디스크에 생성되는 경우
- 임시 테이블은 기본적으로 메모리에서 생성되지만, 일부 예외적인 케이스는 디스크에 생성된다.
* UNION이나 UNION ALL에서 SELECT되는 컬럼중에서 길이가 512바이트 이상의 크기의 컬럼이 있는 경우
* GROUP BY나 DISTINCT 컬럼에서 512바이트 이상의 크기의 컬럼이 있는 경우
* 메모리 임시 테이블의 크기가 시스템 변수보다 큰 경우
'Database' 카테고리의 다른 글
| 인덱스 (0) | 2023.12.02 |
|---|---|
| Sybase Random함수 (0) | 2022.03.23 |
| [mybatis] <include refid=""> (0) | 2022.02.17 |
| DB / 데이터베이스 - 인덱스 (0) | 2021.09.26 |
| SQL Injection (0) | 2021.09.10 |
