쿼리를 재구성하는 방법
문제가 있는 쿼리를 최적화할 때는, 원하는 결과를 얻을 수 있는 다른 쿼리를 구성하는 것이 좋지만 반드시 원하는 결과와 동일한 데이터를 산출하는 쿼리를 고집할 필요는 없다. 효율성이 향상된다면 쿼리를 다시 작성하여 약간은 다른 결과를 검색하고, 궁극적으로 애플리케이션 코드를 통해 성능을 향상 시킬 수 있다. 이 섹션에서는 광범위한 쿼리를 재구성하는 데 도움이 되는 기법에 대해 설명하고 각 기법을 언제 사용하는지에 대해 설명하고자 한다.
복잡한 쿼리 vs 많은 쿼리
쿼리 설계에 대한 중요한 질문은 "복잡한 쿼리를 여러 개의 단순한 쿼리로 분할하는 것이 더 나은지 여부"이다. 데이터베이스 설계에 대한 전통적인 접근 방식은 가능한 한 적은 쿼리로 최대한 많은 작업을 수행하는 것을 강조했다. 이 방식은 네트워크 통신 비용과 쿼리 구문 분석 및 최적화 단계의 오버헤드 때문에 전통적으로 우수했다.
그러나 MySQL은 매우 효율적으로 연결 및 연결 해제를 처리하고, 작고 간단한 쿼리에 매우 빠르게 응답하도록 설계되었기 때문에 이 전통적 조언이 그다지 의미가 없다. 또한, 최신 네트워크는 이전보다 훨씬 더 빨라져 네트워크 지연시간이 단축된다. 서버 버전에 따라 MySQL은 상용 서버 하드웨어에서 초당 10만 개 이상의 간단한 쿼리를 실행할 수 있으며, 기가비트 네트워크에서는 단일 통신원으로부터 2,000개 이상의 QPS를 실행할 수 있다. 그렇기에 여러 쿼리를 실행하는 것이 반드시 나쁜 것은 아니다.
연결 응답은 MySQL이 내부적으로 통과할 수 있는 행 수에 비해 여전히 느리다. 그렇기에 다른 조건이 모두 동일하다면 가능한 한 적은 쿼리를 사용하는 것이 좋긴 하다. 다만, 때로는 쿼리를 분해하고 하나의 복잡한 쿼리 대신 단순한 쿼리를 여러 번 실행하는 것이 효율적일 수 있다. 따라서 이를 두려워하지말고 비용을 따져보고 작업량을 줄이는 전략을 취하자.
쿼리 자르기
쿼리를 분할하는 또 다른 방법은 쿼리를 동일하게 유지하되 매번 더 적은 수의 행에 영향을 주는 작은 "Chunk"로 실행하는 것이다.
좋은 예로 오래된 데이터를 제거하는 작업을 떠올려볼 수 있다. 주기적 삭제 작업은 상당한 양의 데이터를 제거해야 할 수 있으며, 하나의 대규모 쿼리에서 이 작업을 수행하면 오랜 시간 동안 많은 행을 잠그고, 트랜잭션 로그를 채우고, 리소스를 독점하며 중단되어서는 안되는 작은 쿼리를 차단할 수 있다. 따라서 이를 막기 위해, DELETE 문을 단일식(monolithic) 쿼리로 바꾸어 실행하는 것이 서버에 영향을 최소화할 수 있다.
-- Bad
DELETE FROM message WHRERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);
-- Good
rows_affected = 0;
do {
rows_affected=do_query(
"DELETE FROM messages WHERE create_time < DATE_SUM(NOW(), INTERVAL 3 MONTH) LIMIT 10000"
)
} while rows_affected > 0
조인 분해
많은 고성능 어플리케이션은 조인 분해를 사용한다. 다중 테이블 조인 대신 여러 단일 테이블 쿼리를 실행한 다음 애플리케이션단에서 조인을 수행하여 조인을 분해할 수 있다.
언뜻 보기에는 쿼리를 늘리면 비효율적으로 보일 수 있지만, 조인 분해를 통한 재구성은 상당한 이점을 갖는다.
1. 캐싱이 더 효율적일 수 있다. 많은 어플리케이션은 테이블에 직접 매핑되는 '객체'를 캐시한다. 이 과정에서 단순한 쿼리의 결과를 캐시에서 찾을 수 있다면 쿼리를 일부 생략할 수도 있다.
2. 개별적으로 쿼리를 실행하면 잠금 경합을 줄일 수 있다.
3. 어플리케이션에서 조인을 수행하면 테이블을 다른 서버에 배치할 수 있어서 데이터베이스 확장이 쉬워진다.
4. 불필요한 행 엑세스를 줄이는 등 쿼리 자체가 더 효율적일 수 있다.
쿼리 실행 기본 사항
MySQL 서버에서 고성능이 필요하다면, 시간을 투자해 MySQL이 쿼리를 어떻게 최적화하고 실행시키는지 공부하는 것이 필요하다. 이것을 안다면 쿼리 최적화는 공식보다는 추론 문제가 되고, 논리적인 프로세스를 거쳐 이루어질 수 있다.
다음은 MySQL의 쿼리 실행 프로세스이다.
1. 클라이언트는 쿼리를 서버로 보낸다.
2. 서버는 이를 구문 분석, 전처리 및 쿼리 실행 계획으로 최적화한다.
3. 쿼리 실행 엔진은 스토리지 엔진 API를 호출하여 계획을 실행한다.
4. 서버는 결과를 클라이언트로 보낸다.
아래에서는 각 단계에서 쿼리가 어떤 상태에 있는지 자세히 살펴본다.
1. MySQL 클라이언트/서버 프로토콜
MySQL의 클라이언트/서버 프로토콜은 "반이중"으로, MySQL 서버가 메세지를 보내거나 받을 수 있지만, 두 가지를 동시에 할 수는 없다. 즉, 흐름 제어가 없기에 한 쪽이 메시지를 전송하면 다른 쪽은 응답하기 전에 전체 메시지를 가져와야만 한다.
클라이언트는 서버에 쿼리를 단일 데이터 패킷으로 보낸다. 그래서 쿼리가 큰 경우 max_allowed_packet 구성 변수가 중요해진다. 반대로, 서버의 응답은 일반적으로 많은 데이터셋을 포함하는데 클라이언트는 이 데이터를 전부 받아야한다. 단순히 몇 개의 행을 가져온 다음 서버에 나머지를 보내지 말라고 요청할 수 없는 것이다. 그렇기에 LIMIT 절을 적절히 활용하는 것이 중요하다.
MySQL에 연결하는 대부분의 라이브러리를 사용하면 전체 결과 집합을 가져와 메모리에 버퍼링하거나 필요에 따라 각 행을 가져올 수 있다. 모든 행을 가져올 때까지 MySQL 서버가 쿼리에 필요한 잠금 및 기타 리소스를 해제하지 않기 때문에 이 작업이 중요하다. 클라이언트 라이브러리가 결과를 한 번에 모두 가져오면 서버가 수행해야 하는 작업량이 줄어든다.
2. 쿼리 최적화 프로세스
쿼리 수명주기의 다음 단계는 쿼리 최적화 프로세스이다. 이 단계는 구문 분석, 전처리 및 최적화와 같은 여러 하위 단계들로 이루어진다. 오류는 하위 프로세스의 어느 지점에서나 발생 가능하다.
- 파서와 전처리기
우선 MySQL의 파서( parser )는 쿼리를 토큰으로 나누고 토큰에서 '파스 트리'를 만든다. 파서는 MySQL의 SQL 문법을 사용하여 쿼리를 해석하고 검증한다. 가령, 쿼리의 토큰이 올바른 순서로 유효한지 확인하고 종료되지 않은 따옴표 문자열과 같은 오류를 확인한다.
그 후, 전처리기 ( preprocessor )에서는 파스 트리에서 파서가 확인할 수 없는 추가 의미 체계를 확인한다. 가령, 테이블과 열이 있는지 확인하고 열 참조가 모호하지 않은지 이름과 별칭을 확인한다. 그런 다음, 전처리기가 권한을 확인한다.
- 쿼리 옵티마이저
옵티마이저의 역할은 "최상의 옵션을 찾는 것"이다.
MySQL은 비용 기반 옵티마이저를 사용한다. 즉, 다양한 실행 계획의 비용을 예측하고 가장 저렴한 것을 선택하려고 한다. 쿼리를 실행한 다음 Last_query_cost 세션 변수를 검사하면 옵티마이저가 쿼리를 추정한 비용이 얼마인지 알 수 있다.
SELECT SQL_NO_CACHE COUNT(*) FROM film_actor;
SHOW STATUS LIKE 'LAST_query_cost';
> 실행 결과
| Variable_name | Value |
| Last_query_cost | 1040.59 |
위 결과는 옵티마이저가 쿼리를 실행하기 위해 약 1040개의 랜덤 데이터 페이지 읽기를 수행해야 한다고 추정했음을 의미한다. 이 추정의 기반은 테이블 또는 인덱스당 페이지 수, 인덱스의 카디널리티, 행 및 키의 길이, 키 분포 등 다양하다. 옵티마이저는 추정치에 캐싱 효과를 포함하지 않으며, 모든 읽기로 인해 디스크 I/O 작업이 발생한다고 가정한다.
물론 아래와 같은 다양한 이유로 옵티마이저가 항상 최상의 계획을 선택하지 못할 수 있다.
- 테이블에 대한 통계가 정확하지 않을 수 있다.
- 비용 메트릭은 쿼리 실행의 실제 비용과 정확히 동일하지 않다.
- MySQL 의 '최적'이 항상 가장 빠른 실행 시간을 의미하는 것은 아니다. 어디까지나 "비용을 최소화"하는 것이다.
- 옵티마이저는 동시에 실행되는 다른 쿼리를 고려하지 않는다.
- MySQL이 항상 비용 기반 최적화를 수행하는 것은 아니다. 가령, 전체 텍스트 MATCH() 절이 있으면 FULLTEXT 인덱스가 있을 경우 그것을 사용한다와 같은 규칙을 다룬다.
- 옵티마이저는 저장 함수나 사용자 정의 함수의 실행과 같이 제어할 수 없는 작업의 비용을 고려하지 않는다.
- 옵티마이저는 가능한 모든 실행 계획을 항상 추정할 수 있으므로 최적의 계획을 놓칠 수 있다.
MySQL의 쿼리 옵티마이저는 매우 복잡한 소프트웨어이며 쿼리를 실행 계획으로 변환하기 위해 많은 최적화를 사용한다. 기본적으로 정적 및 동적 최적화 유형이 있다.
정적 최적화는 구문 분석 트리를 검사하여 간단히 수행할 수 있다. 예를 들어 대수 규칙을 적용하여 WHERE 절을 변환하는 것은 정적 최적화에 해당한다. 정적 최적화는 쿼리가 수행될 때, 한 번 수행되며 쿼리가 다른 값으로 다시 실행되는 경우에도 유효하다. 이것을 "컴파일 시간 최적화"로 볼 수 있다.
반면 동적 최적화는 컨텍스트를 기반으로 하며 WHERE 절에 있는 값 또는 인덱스에 있는 행 수와 같은 많은 요인에 따라 달라질 수 있다. 동적 최적화는 쿼리가 실행될 때마다 다시 진행되며, 이를 "런타임 최적화"로 생각할 수 있다.
다음은 MySQL이 수행할 수 있는 몇 가지 최적화 유형이다.
- 조인 재정렬
옵티마이저는 데이터 탐색 등의 비용을 최소화하는 방향으로 조인 순서를 결정한다. 이때, 쿼리 실행 계획은 항상 왼쪽으로 치우친 트리( left-deep tree ) 형태를 취한다.
조인 실행 전략은 과거 단순히 모든 조인을 중첩 루프 조인으로 처리했으나, 8.0.20 버전부터 데이터셋 중 하나가 메모리 있을 수 있는 경우를 고려한 해시 조인을 사용하고 있다.
- OUTER JOIN을 INNER JOIN으로 변환
테이블 스키마나 쿼리 조건 등에 의해 OUTER JOIN이 INNER JOIN과 같아질 수 있다. MySQL은 이를 인식하고 조인을 재생성하여 순서를 변경할 수 있다.
- 대수 등가 규칙 적용
MySQL은 대수 변환 ( algebraic transformations )을 적용한다. 가령, (5=5 AND id > 5 ) 라는 항은 id > 5 로 줄인다.
- COUNT(), MIN(), MAX() 최적화
인덱스 및 열 null 허용 여부는 이를 최적화하는데 도움을 준다. 예를 들어, 인덱스가 있는 열의 MIN()을 실행하면 MySQL은 인덱스의 첫 번째 행을 요청하고 나머지 쿼리 값에 대해서는 상수로 처리할 수도 있다.
- 상수 표현식 평가 및 줄이기
MySQL은 표현식을 상수로 줄일 수 있음을 감지하면 최적화 중에 상수로 줄인다. 가령, 사용자 정의 변수가 쿼리 내에서 바뀌지 않는 경우 이를 상수로 변환한다.
뿐만 아니라, 쿼리로 간주할 수 있는 항목도 최적화 단계에서 상수로 축소된다. 예를 들어, 인덱스의 MIN()은 기본 키 또는 고유 인덱스에 대한 상수 조회로 확장될 수 있다.
- 커버링 인덱스
인덱스가 쿼리에 필요한 모든 열을 포함하고 있을 때, MySQL은 인덱스를 사용하여 행 데이터를 읽지 않고 결과를 반환할 수 있다.
- 서브쿼리 최적화
MySQL은 일부 유형의 서브쿼리를 보다 효율적인 대체 형식으로 변환하여 별도의 쿼리 대신 인덱스 조회로 줄일 수 있다.
- 조기 종료
mySQL은 쿼리나 단계가 완료되는 즉시 쿼리 처리를 중지할 수 있다. 가령, LIMIT 절이 존재하거나 WHERE 절에서 조건에 맞는 데이터가 존재하지 않는 경우 쿼리 실행을 조기에 종료시킨다.
- IN() 목록 비교
MySQL에서는 IN() 이 여러 OR 절의 동의
어가 아니다. MySQL은 IN() 목록의 값을 정렬하고 빠른 바이너리 검색을 사용하여 값이 목록에 있는지 확인한다. 이는 O(log n)을 소모하는 반면, 일련의 OR 절은 O(n)이다.
여기서 기억해야 할 한 가지는 미리 옵티마이저보다 앞서려고 하지 말라는 것이다. 결과적으로 실패하거나 쿼리를 더 복잡하게 만들고 유지 관리하기 더 어려워질 수 있다. 일반적으로는 옵티마이저가 작업을 수행하게 해야 한다.
3. 쿼리 실행 엔진
최적화 단계와 달리 실행 단계는 일반적으로 그렇게 복잡하지 않다. MySQL은 단순히 쿼리 실행 계획에 제공된 지침을 따른다. 계획에 포함된 많은 작업은 핸들러 API라고도 하는 스토리지 엔진 인터페이스에 의해 구현된 메서드를 호출한다.
4. 클라이언트에게 결과 반환
쿼리 실행의 마지막 단계는 클라이언트에 회신하는 것이다. 쿼리에서 결과 셋을 반환하지 않는 경우에도 영향을 받은 행 수와 같은 정보를 클라이언트에게 응답한다.
서버는 결과를 점진적으로 생성하고 발송한다. MySQL은 마지막 테이블을 처리하고 행 하나를 성공적으로 생성하자마자 해당 행을 클라이언트로 전송할 수 있고, 전송해야 한다. 이때, 이점은 서버가 메모리에 행을 보유하지 않아도 되고 클라이언트가 가능한 한 빠르게 결과를 받을 수 있다. 결과 셋의 각 행은 MySQL 클라이언트/서버 프로토콜의 개별 패킷으로 전송되고, 프로토콜 패킷은 버퍼링 되어 TCP 프로토콜 계층에서 함께 전송할 수 있다.
MySQL 쿼리 옵티마이저의 한계
MySQL의 쿼리 실행 방식이 모든 종류의 쿼리를 최적화하는 데에 이상적이지는 않다. 다행히 MySQL 옵티마이저가 제대로 작동하지 않는 경우는 제한적이며, 일반적으로 이러한 쿼리를 보다 효율적으로 다시 작성할 수 있다.
UNION 제한 사항
MySQL은 UNION을 일련의 단일 쿼리로 실행하며, 결과가 임시 테이블에 스풀링된 다음 다시 읽는다.
UNION 내부의 개별 쿼리가 LIMIT의 이점을 얻을 수 있다고 생각되거나 다른 쿼리와 결합하여 ORDER BY 절의 적용을 받게 될 것으로 생각된다면 행당 절을 UNION의 각 부분 안에 넣어야 한다.
(SELECT first_name, last_name
FROM actor
ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_name
FROM customer
ORDER BY last_name
LIMIT 20)
LIMIT 20;
병렬 실행
MySQL 은 여러 CPU에서 단일 쿼리를 병렬로 실행할 수 없다.
'Database > High Performance MySQL' 카테고리의 다른 글
| MySQL 복제 (2) (0) | 2023.12.12 |
|---|---|
| MySQL 복제 (1) (0) | 2023.12.11 |
| 쿼리 성능 최적화 (1) (0) | 2023.11.28 |
| 고성능을 위한 인덱싱 (2) (2) | 2023.11.09 |
| 고성능을 위한 인덱싱 (1) (0) | 2023.11.07 |