데이터베이스 최적화
이 방법은 최적화 문제에서 가장 중요한 부분이다. 스크립트는 실행 시간의 대부분을 데이터베이스 작업에 사용하므로 몇 가지 방법을 통해 데이터베이스 작업의 성능을 개선하는 방법을 살펴보자.
여기서는 MySQL을 이용해 설명하지만 이 개념은 PostgreSQL, 오라클 등 다른 데이터베이스로도 확대될 수 있다.
쿼리 분석
일단 성능 저하의 문제가 쿼리에 있다는 것이 밝혀졌다면 각 쿼리에서 무엇이 문제인지를 알아내야 한다. 우선 불필요한 조인을 사용하지 않느지 확인하고 쿼리를 빠르게 만들 다른 방법이 있는지 확인해야 한다. 두 개의 큰 테이블을 조인하는 것보다 쿼리를 두 번하는 것이 빠르다.
쿼리 실행 방법
만일 제대로 만들어진 쿼리의 실행 시간이 매우 길다면 다음의 MySQL EXPLAIN 문을 이용해 MySQL이 쿼리를 어떻게 처리하는지 확인한다:
EXPLAIN SELECT ... FROM ... WHERE ...
간단히 SELECT 문 앞에 EXPLAIN만을 추가하면 된다. MySQL은 쿼리를 실행하고 다음과 같은 처리 정보를 가진 테이블을 리턴한다.
table : 출력된 행에서 참조하는 테이블
type : 사용되는 조인 형식. 조인 형식에 대한 자세한 정보는 뒤에 나오는 조인 형식을 참고한다.
possible_keys : 쿼리를 수행하기 위해 MySQL이 사용할 수 있는 컬럼. 만일 비어있다면 관련된 색인이 없다는 것이다. 이 경우 WHERE 절을 분석해 적절한 색인을 추가하면 성능이 개선된다.
key : 쿼리를 수행하기 위해 MySQL이 선택한 색인. 만일 NULL이면 색인이 사용되지 않은 것이다. 만일 MySQL이 잘못된 색인을 선택한다면 USE INDEX/IGNORE INDEX를 이용해 강제로 색인을 지정할 수 있다.
key_len : MySQL이 사용하기로 결정한 키의 길이
ref : 테이블에서 행을 선택하기 위해 사용된 컬럼 또는 상수
rows : 테이블에서 행을 선택하기 위해 MySQL이 반드시 검사해야 하는 행의 수
Extra : MySQL이 쿼리를 실행하는 방식에 대한 추가 정보. 다음과 같은 정보가 사용된다.
Distinct : 조건에 맞는 첫 번째 행을 발견하면 더 이상 검색을 진행하지 않는다.
Not exits : MySQL은 쿼리에 LEFT JOIN 최적화를 할 수 있는데, LEFT JOIN 조건에 맞는 행의 조합을 찾아내면 더 이상 테이블의 행을 검사하지 않는다.
Using filesort : MySQL은 검색된 행을 정렬해서 가져오기 위해 추가적인 작업이 필요하다. 이 작업은 조인 형식에 따라 모든 행을 검사하고 WHERE 절에 해당하는 모든 해으이 "정렬 키 + 포인터"를 저장함으로써 가능하다. 각 행을 순서대로 가져오기 전에 이 키가 먼저 정렬된다.
Using index : 실제 행을 검사하지 않고 테이블의 색인 트리에 있는 정보만을 사용하는 경우이다. 테이블에서 가져오는 모든 컬럼이 같은 색인의 일부분인 경우를 의미한다.
Using temporary : MySQL은 결과를 저장하기 위해 임시 테이블을 만들 필요가 있다. 이것은 하나의 테이블에서 서로 다른 컬럼에 사용된 GROUP BY의 결과 행을 ORDER BY로 정렬할 때 사용된다.
쿼리를 가능한 빠르게 만들려면 Using filesort와 Using temporary를 살펴본다. 만일 이러한 부분이 발견된다면 쿼리를 최적화할 필요가 있다.
이 방법은 최적화 문제에서 가장 중요한 부분이다. 스크립트는 실행 시간의 대부분을 데이터베이스 작업에 사용하므로 몇 가지 방법을 통해 데이터베이스 작업의 성능을 개선하는 방법을 살펴보자.
여기서는 MySQL을 이용해 설명하지만 이 개념은 PostgreSQL, 오라클 등 다른 데이터베이스로도 확대될 수 있다.
쿼리 분석
일단 성능 저하의 문제가 쿼리에 있다는 것이 밝혀졌다면 각 쿼리에서 무엇이 문제인지를 알아내야 한다. 우선 불필요한 조인을 사용하지 않느지 확인하고 쿼리를 빠르게 만들 다른 방법이 있는지 확인해야 한다. 두 개의 큰 테이블을 조인하는 것보다 쿼리를 두 번하는 것이 빠르다.
쿼리 실행 방법
만일 제대로 만들어진 쿼리의 실행 시간이 매우 길다면 다음의 MySQL EXPLAIN 문을 이용해 MySQL이 쿼리를 어떻게 처리하는지 확인한다:
EXPLAIN SELECT ... FROM ... WHERE ...
간단히 SELECT 문 앞에 EXPLAIN만을 추가하면 된다. MySQL은 쿼리를 실행하고 다음과 같은 처리 정보를 가진 테이블을 리턴한다.
table : 출력된 행에서 참조하는 테이블
type : 사용되는 조인 형식. 조인 형식에 대한 자세한 정보는 뒤에 나오는 조인 형식을 참고한다.
possible_keys : 쿼리를 수행하기 위해 MySQL이 사용할 수 있는 컬럼. 만일 비어있다면 관련된 색인이 없다는 것이다. 이 경우 WHERE 절을 분석해 적절한 색인을 추가하면 성능이 개선된다.
key : 쿼리를 수행하기 위해 MySQL이 선택한 색인. 만일 NULL이면 색인이 사용되지 않은 것이다. 만일 MySQL이 잘못된 색인을 선택한다면 USE INDEX/IGNORE INDEX를 이용해 강제로 색인을 지정할 수 있다.
key_len : MySQL이 사용하기로 결정한 키의 길이
ref : 테이블에서 행을 선택하기 위해 사용된 컬럼 또는 상수
rows : 테이블에서 행을 선택하기 위해 MySQL이 반드시 검사해야 하는 행의 수
Extra : MySQL이 쿼리를 실행하는 방식에 대한 추가 정보. 다음과 같은 정보가 사용된다.
Distinct : 조건에 맞는 첫 번째 행을 발견하면 더 이상 검색을 진행하지 않는다.
Not exits : MySQL은 쿼리에 LEFT JOIN 최적화를 할 수 있는데, LEFT JOIN 조건에 맞는 행의 조합을 찾아내면 더 이상 테이블의 행을 검사하지 않는다.
Using filesort : MySQL은 검색된 행을 정렬해서 가져오기 위해 추가적인 작업이 필요하다. 이 작업은 조인 형식에 따라 모든 행을 검사하고 WHERE 절에 해당하는 모든 해으이 "정렬 키 + 포인터"를 저장함으로써 가능하다. 각 행을 순서대로 가져오기 전에 이 키가 먼저 정렬된다.
Using index : 실제 행을 검사하지 않고 테이블의 색인 트리에 있는 정보만을 사용하는 경우이다. 테이블에서 가져오는 모든 컬럼이 같은 색인의 일부분인 경우를 의미한다.
Using temporary : MySQL은 결과를 저장하기 위해 임시 테이블을 만들 필요가 있다. 이것은 하나의 테이블에서 서로 다른 컬럼에 사용된 GROUP BY의 결과 행을 ORDER BY로 정렬할 때 사용된다.
쿼리를 가능한 빠르게 만들려면 Using filesort와 Using temporary를 살펴본다. 만일 이러한 부분이 발견된다면 쿼리를 최적화할 필요가 있다.
댓글을 달아 주세요
댓글 RSS 주소 : http://letmelove.net/blog/rss/comment/70