쿼리의 실행 절차
DBMS에서 쿼리의 실행 계획을 수립하는 것은 옵티마이저
1단계 : SQL 파싱, MySQL서버의 SQL 파서 라는 모듈로 처리, SQL 문장이 문법적으로 잘못됐다면 여기서 걸러짐
SQL 파스 트리가 만들어짐
2단계 : 최적화 및 실행 계획 수립 단계
불필요한 조건 제거 및 복잡한 연산의 단순화
여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
각 테이블에 사용된 조건과 인덱스 통계정보를 이용해 사용할 인덱스를 결정
가져온 레코드들을 임시테이블에 넣고 다시 한번 가공해야 하는지 결정
3단계 : 수립된 실행 계획대로 스토리지 엔진에 에코드를 읽어오도록 요청하고, MySQL엔진에서는 스토리지 엔진으로 부터 받은 레코드를 조인하거나 정렬하는 작업 수행
옵티마이저 종류
옵티마이저는 DB 서버에서 두뇌 같은 역할을 담당
비용 기반 최적화 방법 : 워리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계정보를 이용해 실행 계획별 비용을 산출함(요즘 많이 사용됨, MySQL)
규칙 기반 최적화 방법 : 테이블의 에코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립하는 방식(초기방식)
기본 데이터 처리
풀 테이블 스캔, 풀 인덱스 스캔
풀 테이블 스캔
테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는것 보다 풀 테이블 스캔을 하는편이 더 빠를경우
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 조건이 없는 경우
인덱스 레인지 스캔을 사용할 수 있는 쿼리라도 조건 일치 레코드 건수가 너무 많은 경우
병렬 처리
MySQL 서버에서도 쿼리의 병렬 처리가 가능함
병렬처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미
innodb_parallel_readtheads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇개의 스레드를 이용해서 처리할지 변경 할 수 있음
하지만, 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장작된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있으니 주의
ORDER BY 처리
정렬을 처리하는 방법 : 인덱스를 이용하는 방법, 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법
인덱스를 사용하기 어려운 케이스
정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
GROUP BY의 결과 DISTINCT 같은 처리의 결과를 정렬해야하는 경우
임시 테이블의 결과를 다시 정렬해야 하는 경우
랜덤하게 결과 레코드를 가져와야 하는 경우
인덱스를 이용하지 않고 별도의 정렬처리를 수행했는지는 실행계획의 Extra칼럼에 Using filesort 메시지가 표시되는지 여부로 판단 가능
소트버퍼
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용
이 메모리 공간을 소트 버퍼라고 함
정렬이 필요한 경우에만 할당, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가, 메모리 공간은 쿼리 실행이 끝나면 반납
소트 버퍼의 크기가 256KB에서 8MB 사이에서 최적의 성능을 가짐
정렬 알고리즘
레코드를 정렬할 때 레코드 전체를 소트버퍼에 담을지 정렬기준 칼럼만 소트 버퍼에 담을지에 따라 싱글패스, 두패스 2가지 정렬 모드로 나뉨
첫번째 방식이 투 패스 정렬방식
두번째, 세번째 방식이 싱글패스 정렬방식
싱글패스 정렬방식
소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬방식
투패스 정렬방식
정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순거대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬방식
투패스 방식은 두번 읽어야 하기 때문에 불합리하지만, 싱글패스 방식은 이런 단점이 없음
다만, 싱글패스는 더 많은 소트 버퍼에 공간이 필요함
최신버전에서는 일반적으로 싱글패스 정렬방식을 주로 사용함
싱글패스는 정렬대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보여주고, 투패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적임
정렬이 필요한 SELECT는 불필요한 칼럼은 가져오지 않는것이 가장 효율적인 방법
정렬 처리 방법
ORDER BY가 실행 시 정렬 방법
인덱스 정렬
조인에서 드라이빙 테이블만 정렬
조인에서 조인 결과를 임시테이블로 저장 후 정렬
인덱스 정렬
ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야함
WHERE절에 첫번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 함
인덱스 정렬은 인덱스가 이미 정렬되어 있기 때문에 그대로 읽으면 댐
조인의 드라이빙 테이블만 정렬
조인이 수행되면 레코드는 몇배로 늘어나고, 크기도 커짐
때문에 조인을 실행하기 전에 첫번째 테이블에서 레코드를 정렬 후 조인을 실행하는게 정렬의 차선책
조인에서 첫번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY절을 작성해야함
임시 테이블 정렬
2개 이상의 테이블을 사용하면서 첫번째 테이블의 인덱스만 사용할 수 없을 경우 임시 테이블을 생성하여 정렬
3가지 방법중 정렬할 레코드가 가장 많기 때문에 가장 느린 정렬방법
정렬 처리 방법의 성능 비교
ORDER BY나 GROUP BY 같은 작업은 WHERE조건을 만족하는 레코드를 LIMIT건수 만큼만 가져와서 처리가 불가능함
조건을 만족하는 모든 레코드를 가져온 뒤 정렬을 수행하거나 그루핑 작업을 실행해야지만 LIMIT을 걸 수 있음
잘못된 ORDER BY, GROUP BY 때문에 쿼리가 느려지는 경우가 많음
쿼리가 처리되는 방법 : 스트리밍 처리, 버퍼링 처리
스트리밍 방식
서버쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
쿼리를 요청하고 원했던 첫 레코드를 전달받고, 마지막 레코드는 언제 받을지 모르지만 상관없음
쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답시간을 보장해줌
LIMIT까지 사용하면 마지막 데이터까지 받는데 시간이 단축됨
버퍼링 방식
ORDER BY, GROUP BY 처리는 쿼리의 결과가 스트리밍 되는 것을 불가능하게함
모든 레코드를 조회하고, 정렬하는동안 클라이언트는 무한 대기하기 때문에 응답속도가 느려짐
정렬처리 3가지 방법중 인덱스 정렬 방식은 스트리밍 형식의 처리방식
나머지는 버퍼링 방식
어느 테이블이 먼저 드아리빙 되어 조인되는지도 중요하지만, 어떤 정렬 방식으로 처리되는지는 더 큰 성능차이를 만듦
가능하다면 인덱스를 사용한 정렬로 유도하고, 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는것도 좋은 튜닝 방법