Real mysql 8.0 ch9 10
9 옵티마이저와 힌트
대부분의 DBMS에서는 옵티마이저가 MySQL 서버로 요청된 쿼리를 어떤 방법이 최적이고 최소의 비용이 소모될지 결정한다.
옵티마이저 종류
- 규칙 기반 최적화
- 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식.
- 비용 기반 최적화
- 쿼리를 처리하기 위한 여러 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출.
풀 데이터 스캔과 풀 인덱스 스캔
- 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리
- 테이블의 레코드 수가 너무 작아서 인덱스보다 풀 테이블 스캔이 더 빠른 경우 (페이지 1개로 구성된 경우)
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
- 상당히 많은 디스크 읽기가 필요하여, 대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장.
- InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다
- 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.
- 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
- 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리된다.
innodb_read_ahead_threshold시스템 변수를 이용해 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다.- 웨어하우스용으로 MySQL을 사용한다면 이 옵션을 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋은 방법이다.
ORDER BY 처리 (Using filesort)
- 인덱스 이용
- INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 읽기만 하면 되므로 매우 빠름
- INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느림.
- 인덱스 때문에 디스크 공간이 더 많이 필요
- Filesort 이용
- 인덱스를 생성하지 않아도 되므로 디스크 공간이나, 추가작업에 대해 필요없음
- 정렬할 레코드가 많지 않으면 메모리에서 Filesort 처리되므로 충분히 빠름
- 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느림
소트 버퍼
정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 함. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.
정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다. 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는데, 이 작업들이 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아진다. 소트 버퍼 사이즈 시스템 변수의 설정값이 무조건 크면 메모리에서 모두 처리되니 빨라질 것으로 예상하지만 실제 결과는 그렇지 않다.
싱글 패스 정렬 방식
소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식. 많은 소트 버퍼 공간이 필요. 최신 번전에서는 일반적으로 싱글 패스 정렬 방식을 사용한다. 정렬 대상이 적을수록 빠르다.
투 패스 정렬 방식
정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 컬럼을 가져오는 정렬. 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.
조회 쿼리에서 꼭 필요한 컬럼만 조회하는 것이 효율적이다
정렬 처리 방법
- 인덱스를 사용한 정렬
- 조인에서 드라이빙 테이블만 정렬
- 조인에서 조인 결과를 임시 테이블로 저장 후 정렬
인덱스를 사용한 정렬
반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다.
인덱스를 사용하면서 자동으로 정렬이 된다고 일부러 ORDER BY를 제거하는 것은 좋지 않은 선택이다.
정렬을 인덱스로 처리할 수 있는 경우 부가적으로 불필요한 정렬 작업을 수행하지 않는다. 인덱스로 정렬이 처리될 때는 ORDER BY가 쿼리에 명시된다고 해서 작업량이 더 늘지는 않는다. 어떤 이유로 쿼리의 실행 계획이 조금 변경된다면 ORDER BY가 명시되지 않은 쿼리는 결과를 기대했던 순서로 가져오지 못해서 애플리케이션의 버그로 연결될 수도 있다. 하지만 ORDER BY절을 명시해두면 성능상의 손해도 없디만 예외 상황에 대비도 된다.
조인의 드라이빙 테이블만 정렬
일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어난다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다.
임시 테이블을 이용한 정렬
2개 이상의 테이블이 조인되면서 정렬이 실행될 때 임시 테이블이 필요할 수도 잇다. 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다. 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.
쿼리가 처리되는 방법: 스트리밍 방식
서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해줌
쿼리가 처리되는 방법: 버퍼링 방식
ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다. 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 크루핑해서 차례대로 보내야 하기 때문이다. 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느리다.
MySQL 서버는 스트리밍 방식으로 처리해서 반환하지만 클라이언트의 JDBC 라이브러리가 버버필을 한다. JDBC 라이브러리가 자체적으로 레코드를 버퍼링하는 이유는 이 방식이 전체 처리 시간이 짧고 MySQL 서버와의 통신 횟수가 적어 자원 소모가 줄어들기 때문이다.
인덱스를 사용한 정렬 방식은 LIMIT으로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있다. 하지만 인덱스를 사용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT으로 제한된 건수만큼 잘라서 클라이언트로 전송해줄 수 있음을 의미한다.
어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있다.