쿼리 성능 최적화 (1)

쿼리가 느린 이유

 

빠른 쿼리의 핵심은 당연히도 실행 시간에 있다.

 

쿼리는 작업이지만, 하위 작업으로 구성되며 이러한 하위 작업은 모두 시간을 소모한다. 결국 쿼리를 최적화하기 위해서는  하위 작업을 (1) 제거하거나, (2) 발생 횟수를 줄이거나 (3) 더 빠르게 수행하여 최적화해야한다.

 

일반적으로 클라이언트로부터 서버까지의 쿼리의 ( 아주 대략적인 ) 라이프 사이클은 구문 분석, 계획 실행 후 다시 클라이언트로 돌아가는 것으로 이루어진다. 여기서 실행은 쿼리의 수명 중 가장 중요한 단계이며, 이 작업에서 스토리지 엔진에 대한 많은 호출과 그룹화 및 정렬과 같은 검색 후 작업이 이루어진다.

 

이 모든 작업을 수행하는 동안 쿼리는 네트워크,  CPU 및 통계, 계획, 잠금 등의 작업에 시간을 소비하며, 특히 행 검색을 위한 스토리지 엔진에 대한 호출에 시간을 보낸다. 이러한 호출은 메모리 작업, CPU 작업, 특히 데이터가 메모리에 없는 경우 I/O 작업에 시간을 소비한다. 

 

모든 경우에 작업이 불필요하게 수행되거나 너무 많이 수행되거나 너무 느림으로 인해 과도하게 시간이 소모될 수 있다. 최적화의 목표는 작업을 제거하거나 줄이거나 더 빠르게 함으로써 이러한 문제를 방지하는 것이다.

 

슬로우 쿼리의 기본: 데이터 엑세스 최적화

 

쿼리가 잘 수행되지 않는 가장 기본적인 이유는 너무 많은 데이터를 사용하기 때문이다. 대부분의 잘못된 쿼리는 더 적은 데이터에 엑세스하도록 변경할 수 있다. 아래와 같은 두 단계의 분석을 따르자.

 

1. 어플리케이션이 필요 이상으로 많은 데이터를 검색하는지 확인한다. 일반적으로 너무 많은 행에 엑세스하고 있음을 의미하지만, 종종 너무 많은 열에 엑세스하는 경우도 있다.
2. MySQL 서버가 필요 이상으로 많은 행을 분석하고 있는지 확인한다.

 

 

1. 데이터베이스에 불필요한 데이터를 요청하고 있는 경우

 

슬로우 쿼리의 특징 중 하나는 필요한 것보다 더 많은 데이터를 요청한 다음 일부를 버리는 것이다. 이는 MySQL 서버에 추가 작업을 요구하고, 네트워크 오버 헤드가 추가되며, 어플리케이션 서버의 메모리와 CPU 리소스를 소비한다.

 

다음은 몇 가지 전형적인 실수이다.

 

- 필요 이상의 행을 가져오기

 

MySQL이 전체 결과 셋을 계산하고 반환하는 대신 요청에 따른 결과만을 제공한다고 가정하는 실수이다. 예를 들어 100개의 기사 중 10개만을 1면에 실을 때, 처음 10개의 행을 가져오고 결과 셋을 닫아버리는 등의 행위이다. 최상의 해결책은 LIMIT을 사용하는 것이다.

 

-필요 이상의 열을 가져오기

 

조인 시, 필요한 데이터가 한정적일 경우 모든 데이터를 가져오는 "*" 사용에 유의하자

 

-- Bad Case
SELECT * FROM actor
JOIN film_actor ON film_actor.actor_id = actor.id
JOIN film ON film.id = film_actor.film_id
WHERE film.title = 'If only';

-- Good Case
SELECT action.* FROM actor ...;

 

`SELECT *` 이 표시되면 항상 의심해야 한다. 모든 열이 필요한 경우, 그리 많지 않다. 모든 열을 검색하면 인덱스 적용이 최적화되지 않을 수 있고, 서버에 대한 I/O, 메모리 및 CPU 오버 헤드가 가중될 수 있다. 일부 DBA는 이 때문에 SELECT * 구문을 보편적으로 권장하지 않는다.

 

- 동일한 데이터를 반복적으로 가져오기

 

주의하지 않으면 데이터 베이스에서 동일한 데이터를 반복적으로 검색하고 동일한 쿼리를 실행하는 어플리케이션 코드를 작성하기 쉽다. 처음 가져올 때, 캐시하고 나중에 다시 사용할 수 있도록 개선하자.

 

 

2. MySQL이 너무 많은 데이터를 검사하고 있는 경우 

 

쿼리가 필요한 데이터만 검색한다고 확신한다면, 결과를 생성하는 동안 너무 많은 데이터를 검사하는 쿼리를 찾는 것을 제안한다. MySQL 에서 가장 간단한 쿼리 비용 메트릭은 다음과 같다.

 

- 응답 시간
- 검사된 행 수
- 반환된 행 수

 

이 메트릭 중 어느 것도 쿼리 비용을 측정하는 완벽한 방법은 아니지만, 쿼리를 실행할 때 MySQL 이 내부적으로 접근해야하는 데이터 양을 대략 반영한다. 이는 모두 슬로우 쿼리 로그에 기록되기에, 많은 데이터를 검사하는 쿼리를 찾기 위해 이 로그들을 보는 것은 좋은 접근 방법이다.

 

- 응답 시간

 

응답 시간은 서비스 시간과 대기열 시간의 합이다.

 

서비스 시간: 서버가 실제로 쿼리를 처리하는 데 걸리는 시간.

대기열 시간: 서버가 실제로 쿼리를 실행하지 않는 응답 시간의 일부. 즉, I/O 작업이 완료되기를 기다리거나 행 잠금을 기다리는 등의 작업에서 촉진된 시간.

 

문제는 응답 시간을 이러한 구성 요소로 나누어 측정할 수 없다는 점이다. 일반적으로 가장 흔하고 중요한 대기는 I/O 대기와 잠금 대기이다. 물론 이 두 가지만 있는 것은 아니나, I/O 및 잠금 대기는 성능에 가장 큰 영향을 주기에 중요하다.

 

따라서 응답 시간은 다양한 부하 조건에서 일정하지 않다. 스토리지 엔진 잠금 ( 행 잠금 ), 높은 동시성 및 하드웨어와 같은 다른 요인들도 응답 시간에 상당한 영향을 미칠 수 있다. 그래서 응답 시간은 문제의 증상이자 원인 되기도 하지만 어떤 경우 항상 명확한 것은 아니다.

 

응답 시간을 살펴볼 때는, "쿼리에 대한 응답 시간이 적절한지" 자문해야 한다. 대략적으로 쿼리 실행 계획 및 관련 인덱스를 검토하고, 필요한 차례 및 랜덤 I/O 작업 수를 파악한 후, 하드 웨어가 이를 수행하는 데 걸리는 시간의 곱이 척도가 된다. 자세한 내용은 「Relational Database Index Design and the Optimizers」 (Wiley, 2005) 를 참조하자. 

 

- 검사한 행과 반환된 행

 

쿼리가 얼마나 효율적으로 데이터를 조회하고 있는지 확인하기 위해 검사되는 행 수를 고려하는 것은 유용하다. 그러나 이것이 '잘못된' 쿼리를 찾는 완벽한 메트릭은 아니다. 모든 행 엑세스가 동일하지는 않다. 행이 짧을수록 엑세스가 더 빠르게 메모리에서 행을 가져오는 것이 디스크에서 읽는 것보다 월등히 빠르다. 

 

검사된 행의 수가 반환된 수와 동일한 것이 이상적이긴 하지만, 실제로 이는 거의 불가능하다. 가령, 1:N 관계 테이블 사이의 JOIN 만 생각해보더라도 검사된 행의 수가 경우에 따라 매우 커질 수 있다.

 

- 검사한 행 및 엑세스 유형

 

MySQL은 여러 엑세스 방법을 사용하여 행을 찾고 반환할 수 있다. 엑세스 방법에 따라 많은 행을 검사하지 않고도 결과를 생성하기도 한다. 

 

엑세스 방법은 EXPLAIN 출력의 type 열에 나타난다. 엑세스 유형은 전체 테이블 스캔, 인덱스 스캔, 범위 스캔, 고유 인덱스 조회 및 상수 등 다양하다. 전체 테이블 스캔에서 고유 인덱스 조회 및 상수로 갈수록 더 적은 데이터를 읽으므로 쿼리가 더 빠르게 동작할 수 있다. 

 

올바른 엑세스 유형을 얻지 못했을 때, 문제를 해결하는 가장 좋은 방법은 "인덱스를 추가하는 것"이다. 고성능을 위한 인덱싱 에서도 봤듯, 쿼리 최적화에 인덱싱은 중요한 요소이다.

 

다음 예시는 EXPLAIN 쿼리와 그에 따른 결과이다.

 

EXPLAIN SELECT * FROM film_actor WHERE film_id=1;

********************* 1. row *********************

id: 1
select_type: SIMPLE
type: ref
key: idx_film_id
rows: 10
Extra: NULL
...

 

결과에서 볼 수 있 듯, 인덱싱이 되어있는 경우 10개의 행에만 엑세스해야 한다고 추정했음을 보여준다. 반면, 인덱스를 삭제하고 다시 실행하면 어떨까?

 

ALTER TABLE film_actor DROP KEY idx_film_id;
EXPLAIN SELECT * FROM film_actor WHERE film_id=1;

********************* 1. row *********************

id: 1
select_type: SIMPLE
type: ALL
key: idx_film_id
rows: 5462
Extra: Using where
...

 

예상대로 엑세스 유형은 전체 테이블 스캔 ( ALL ) 로 변경되었으며, 이제 MySQL은 쿼리를 충족하기 위해 5,462 개의 행을 검사해야 한다고 추정한다. Extra 열의 'Using where'는 스토리지 엔진이 행을 읽은 후 MySQL 서버가 WHERE 절을 사용하여 행을 삭제했음을 나타낸다. 

 

일반적으로 MySQL은 WHERE 절을 최선에서 최악까지 세 가지 방식으로 적용할 수 있다.

 

1. 일치하지 않는 행을 제거하기 위해 인덱스 조회 작업에 조건을 적용한다. ( 스토리지 엔진 계층에서 발생 )

2. 행 엑세스를 방지하고 인덱스에서 각 결과를 검색한 후 일치하지 않는 행을 필터링하기 위해 "커버링 인덱스  ( Extra열의 'Using index' )"를 사용한다.

3. 테이블에서 행을 검색한 다음 일치하지 않는 행을 필터링한다 ( Extra열의 'Using where' ).( 서버 계층에서 발생 )

 

안타깝게도 MySQL 은 결과 셋을 작성하는 데 사용한 행의 수는 알려주지 않고 엑세스한 총 행수만 알려준다. 이런 행의 대부분은 WHERE 절에 의해 제거되어 결과 셋에 기여하지 못할 수 있다.