mysql - 퍼포먼스 튜닝

MySQL의 빠른 속도는 이미 정평이 나 있다. 하지만 어쩔 수 없는 요인에 의해 애플리케이션과 데이터베이스의 성능에 문제가 생길수도 있다. 이러한 문제 중 일부는 애플리케이션 제작자가 신경써야 하고, 나머지는 데이터베이스 관리자가 처리해야 한다. 이장에서는 퍼포먼스 듀닝을 할때 알아야 할 몇가지 원칙과 손쉽게 사용할수 있는 도구에 대해 알아보기로 하자.

데이터베이스의 성능을 향상시키려면 무엇보다도 애플리케이션과 데이터베이스를 제대로 설계하는 것이 중요하다. 설계가 제대로 되어 있지 않으면 퍼포먼스 튜닝만으로는 성능을 향상시키는 데 한계가 있다. 퍼포먼스 튜닝은 애플리케이션 개발과정에서 거의 마지막 단계이기 때문이다. 좋은 데이터베이스 설계법은 7장에서 다룬다. 우선 이장에서는 애플리케이션 제작이 끝난 상태에서 시스템 속도를 최대한 끌어 올리는 방법에 대해 알아보자.

퍼포먼스 튜닝에 대한 접근

MySQL 애플리케이션을 퍼포먼스 튜닝할 때에는 아래에 나온 다섯 가지 사항을 고려해야 한다.

애플리케이션 튜닝
SQL 질의 튜닝
데이터베이스 서버 튜닝
운영 체제
하드웨어
이 다섯 가지 사항은 가격 대비 성능 순서대로 열거하였다. 예를 들어, 메모리를 추가하거나 프로세스를 업그레이드하면 애플리케이션의 성능이 향상되지만 애플리케이션 코드나 데이터베이스 서버를 튜닝하면 훨씬 적은 비용으로도 성능을 대폭 향상시킬 수 있다. MySQL 서버를 퍼포먼스 튜닝하면 그 서버를 사용하는 모든 애플리케이션의 성능에 영향을 미치는데, 어떤 애플리케이션의 성능은 향상되지만 다른 애플케이션의 성능에는 좋지 않은 영향을 끼칠 수도 있다. 일반적으로 튜닝을 할 때 위에 열거한 순서대로 처리하는 것이 좋다.

애플리케이션 튜닝

애플리케이션 퍼포먼스 튜닝은 크게 두 부분으로 나눌 수 있다.

호스트 애플리케이션 튜닝(C/C++, 자바, 펄 등)
SQL 질의 튜닝
호스트 애플리케이션 튜닝

성능이 뛰어난 애플리케이션을 만들려면 애플리케이션 설계도 중요하고, 프로그래밍 실력도 좋아야 한다. 아무리 질의 튜닝을 열실히 해도 코드 자체가 비효적이라면 좋은 성능을 기대할 수 없다. 데이터베이스 애플리케이션 설계 방법에 관한 것은 8장에서 자세히 다루겠지만, 애플리케이션의 성능을 최적화하고 싶다면 다음과 같은 일반적인 지침을 따르는 것이 좋다.

데이터베이스를 정규화(normalize)하자

데이터베이스에서 중복되는 부분을 없애면 성능이 크게 향상된다.

상황에 따라 적절히 데이터베이스를 비정규적으로 만들 필요도 있다.

경우에 따라 데이터베이스를 비정규화(denormalize)해서 성능을 향상시킬 수도 있다. 대표적인 예로 기본적인 정보를 요약하는 일간 보고서를 들 수 있다. 이러한 보고서를 만들 때에는 다량의 데이터중에서 필요한 내용만 추려서 요약해야 한다. 이런 경우에는 가장 최근에 나온 요약 정보를 가지고 정기적으로 내용을 갱신하는 "중복되는 내용을 가진" 테이블을 만드는 것도 그리 나쁘지 않다.

MySQL 서버에서 잘 하는 것을 시키자

당연한 얘기인 것 같지만 제대로 지켜지지 않은 경우가 빈번하게 나타나다. 예를 들어, 테이블에서 여러 개의 행을 가져올 때 호스트 애플리케이션에서 다음과 같은 순환문을 이용한 코드를 만드는 경우를 생각해 보자:

for(int i = 0; i++; < i < keymax) {
SELSECT * FROM foobar WHERE key=i;
그 행을 처리한다.
}

하지만 이렇게 하면 루프를 돌 때마다 같은 질의를 파싱하고 최적화하고 실행시키면서 오버헤드가 생기기 때문에 문제가 있다. 모든 행을 한꺼번에 가져오면 이러한 부담을 덜 수 있다. 예를 들면, 다음과 같다:

SELECT * FROM foobar WHERE key < keymax;
각행에 대해 {
그행을 처리한다.
}

될 수 있다면 데이터를 캐시하자.

애플리케이션에서 데이터를 캐시하면 성능을 향상시킬 수 있다. 예를 들어, 고객의 주문을 처리하는 애플리케이션에 주(state)의 약어를 입력받아서 데이터베이스를 검색한 다음 그 주의 전체 이름을 알려주는 부분이 있다고 가정하자:

사용자로부터 입력받은 내용을 $state에 저장한다
state 테이블에서 state_abbr = $state_abbr을 만족하는 state_name을
선택하여 $state_name에 저장한다.
화면에 출력할 필드에 $state_name을 저장한다.

하지만 주에 관한 정보는 자주 바뀌지 않으므로 state 테이블을 캐시해 두면 애플리케이션의 성능을 향상시킬 수 있다. 예를 들면 다음과 같다:

# 애플리케이션 시작부분
SELECT state_name, state_abbr FROM state;
각 행에 대해 {
주의 이름을 state_name으로 불러온다.
state_abbr을 인덱스로 하여 테이블을 해싱한다
}
사용자로부터 입력을 받어서 $state_abbr에 저장한다.
화면에 출력할 필드에 state_name[$state_abbr]을 저장한다.

이렇게 하면 고객이 주문을 할 때마다 매번 질의를 할 필요가 없다.

데이터를 캐시하는 방법은 정적인 정보에 적합하다. 데이터의 내용이 자주 바뀌면 캐시를 사용해서 얻는 이득에 비해 갱신하는 데 드는 비용이 더 크기 때문에 캐시를 사용하지 않는 편이 낫다.

가능하면 지속 연결을 사용하거나 연결 풀을 사용하라

데이터베이스에 연결하거나 연결을 해제할 때에도 오버헤드가 발생한다. 연결 및 연결해제 개념은 8장에서 주로 다루고, 각 언어(펄, 파이썬, C, 자바/JDBC)별 연결 및 연결 해제방법은 해당 언어에 대한 장에서 별도로 다룬다. 일단 기본적인 아이디어는 연결과 연결 끊기 회수를 줄여보자는 것이다. 특히 페이지에 대한 요청이 들어올 때마다 CGI나 PHP 스크립트에서 그 페이지와 관련된 정보를 불러오기 위해 데이터베이스에 연결해야 하는 웹 애플리케이션에서는 이러한 문제가 매우 중요하다. 지속 연결(persistent connection)이나 연결 풀(connection pool)을 사용하면 연결 및 연결해제 과정에서 생기는 오버헤드를 줄여서 애플리케이션의 성능을 향상시킬 수 있다. 하지만 연결 풀이나 지속 연결을 너무 많이 사용하면 MySQL 서버에서 시스템 자원을 너무 많이 잡아 먹기 때문에 다른 문제를 일으킬 수도 있다.

연결 풀과 지속연결은 각 언어에 따라 다른 방식으로 처리한다. 예를 들어 PHP에서는 mysql_pconnect() 함수로 지속 연결을 할 수 있다. 자바에서는 JDBC 드라이버에서 연결 풀 기능을 제공한다. 자신이 사용하는 언어에서 연결 풀 및 지속 연결을 구현하는 방법은 그 언어에 대한 부분에 나와 있다.

SQL 질의 튜닝

데이터베이스에 있는 데이터는 디스크에 저정된다. 데이터를 읽고 갱신하는 작업은 결국 디스크 입/출력 작업이다. SQL 질의 튜닝의 목적은 입/출력 회수를 최소화하는 것이다. 질의를 튜닝할 때 쓰이는 주무기는 바로 인덱스이다.

데이터베이스 테이블에 인덱스가 없으면 매번 데이터를 읽을 때마다 관련된 테이브의 모든 데이터를 하나씩 확인해야 한다. 다음과 같은 예제를 통해 구체적으로 어떤 문제가 있는지 확인해 보자:

SELECT name FROM Employee WHERE ssnum = 999999999

위의 예에서는 사원 정보 테이블(Emloyee 테이블)에서 사회 보장번호(ssnum)가 999-99-9999인 사원의 이름을 선택한다. 사회 보장 번호는 유일해야 한다(사회 보장 번호가 같은 사람은 없다). 즉 테이블에 있는 모든 레코드에 대해 ssnum의 값은 유일하다. 따라서 사회 보장 번호가 999-99-9999인 행은 하나뿐이기 때문에 위에 있는 질의 결과는 한 줄이다.

위에서 사용한 Employee 테이블에서는 인덱스를 사용하지 않았기 때문에 MySQL에서 질의 결과로 나온 데이터가 하나뿐이라는 사실을 알 수 없다. 따라서 질의를 실행할 때 주어진 WHERE 절에 맞는 레코드를 찾기 위해 테이블 전체를 검색해야 한다(결국 사회 보장 번호가 999-99-9999인 사람 한명을 찾기 위해 테이블 전체를 샅샅이 뒤져야 한다). Employee 테이블에 1,000개의 행이 있다면 그 1,000개의 행을 모두 돌면서 각 행의 ssnum 값과 999999999라는 값을 비교해야 한다. 이 작업을 하는데 소요되는 시간은 테이블에 있는 행의 개수가 늘어나면 그에 비례해서 증가한다.

인덱스는 MySQL에 테이블에 대한 결정적인 정보를 알려주는 도구이다. 예를 들어, Employee 테이블에 ssnum 열에 대한 인덱스를 추가하면 MySQL에서는 우선 인덱스를 참고로 하여 주어진 조건에 맞는 ssnum값을 찾아낼수 있다. 인덱스를 만들면 MySQL에서는 레코드를 빨리 찾을 수 있도록 ssnum을 기준으로 행을 정렬하고 트리 구조로 만든다. 조건에 맞는 레코드를 찾으면 그 레코드의 name 데이터만 읽으면 된다. 이 작업은 테이블에 있는 행의 개수가 늘어날 때, 행의 개수의 로그 값에 비례하기 때문에 인덱스를 사용하지 않은 테이블에 비해 훨씬 빠르다.

이 예에서 볼 수 있듯이 MySQL 질의 튜닝은 대부분 테이블에서 인덱스를 제대로 만들었는지, MySQL에서 그 인덱스를 제대로 사용하고 있는지 확인하는 문제로 귀결된다.

인덱스 사용 지침

테이블을 적절하게 인덱싱하는 것이 애플리케이션의 성능에 결정적인 영향을 끼친다는 점을 확인했다. 그렇다면 인덱스를 만들면 성능이 향상되니까 데이터베이스에 있는 각 테이블의 모든 열에 대해 인덱스를 만들면 되지 않을까?

하지만 인덱스를 만들려면 그만큼 대가를 치러야 한다. 인덱스가 있는 테이블에 쓰기작업(INSERT, UPDATE, DELETE)을 하면 그 때마다 각 인덱스를 갱신해야 한다. 따라서 인덱스를 추가할 때마다 그만큼 테이블에 오버헤드가 생긴다. 또한 인덱스를 추가하면 데이터베이스의 크기가 커진다. 그리고 WHERE 절에 포함되는 열에 대해서만 인덱스를 만든 효과가 나타난다. 만약 어떤 인덱스를 만들기만 하고 한 번도 쓰지 않는다면 괜히 그 인덱스를 유지하기 위한 비용만 낭비할 뿐이다.

가끔씩 쓰이는 인덱스도 굳이 만들 필요가 없다. 예를 들어, 어떤 질의는 한 달에 한번 씩만 샐행시키는데, 인덱스 없이 그 질의를 시행시키는데 2분이 걸린다고 가정해 보자. 이렇게 드물게 실행되는 질의는 인덱스 없이 실행시켜도 된다. 하지만 한 달에 한번밖에 실행시키지 않는 질의라 하더라도 한 번만 실행키는데 몇 시간씩 걸린다면 인덱스를 만드는 것이 낫다. 애플리케이션에서 필요로 하는 용도에 따라 적절한 균형을 유지 하면서 인덱스를 만들지 말지 현명한 결정을 내려야 한다.

인덱스를 만들 때에는 지금까지 설명한 장단점을 염두에 두고 다음과 같은 가이드라인을 따르도록 한다.

WHERE 절에 등장하는 모든 열에 대해 인덱스를 만들자

WHERE 절에서 쓰이는 모든 열에 대해 인덱스를 만드는 것이 좋다. 물론 예외도 있다. 어떤 열에 <, <=, >=, > 및 BETWEEN 연산자로 비교하면 인덱스가 쓰인다. 하지만 WHERE 절에 나오는 열에 대해 함수를 사용하면 그 열에 대한 인덱스가 쓰이지 않는다. 따라서 다음과 같은 경우에는 name 열에 대한 인덱스를 만들어도 성능을 향상시키는데에는 전혀 도움이 되지 않는다:

SELECT * FROM Employee WHERE LEFT(name, 6) = 'FOOBAR'

반면에 LIKE 연산자에서는 패턴에 리터럴 전치사가 있으면 인덱스가 쓰인다. 예를 들어, 다음과 같은 구문에서는 인덱스가 활용된다:

SELECT * FROM Employee WHERE name like 'FOOBAR%'

하지만 다음과 같은 경우에는 인덱스를 사용하지 않는다:

SELECT * FROM Employee WHERE name like '%FOOBAR'

또한 앞에서 설명했듯이 WHERE 절에 들어가는 열이라고 무조건 인덱스를 만드는 것도 좋지 않다. 항상 인덱스를 유지하는 데 들어가는 비용과 인덱스를 사용함으로써 얻는 성능 향상을 고려하여 적당한 균형을 유지하는 것이 중요하다.

될 수 있으면 유일 인덱스를 사용하자

인덱스에 있는 데이터가 유일하다면(예를 들어, 기본키나 대체키라면) 유일 인덱스(unique index)를 사용하자. 유일 인덱스를 보통 인덱스에 비해 훨씬 더 빠르다. 그 값이 유일하는 것을 미리 알수 있기 때문에 성능을 크게 향상시킬 수 있다.

다중 열 인덱스를 활용하자

다중 열 인덱스(multicolumn index)를 잘 이용하면 인덱스의 개수를 줄일 수 있다. MySQL에서는 다중 열 인덱스의 왼쪽부터 순서대로 인덱스를 사용한다. 예를 들어, first_name과 last_name이라는 열이 있는 Employee라는 테이블이 있다고 가정해 보자. 절의 first_name 열은 가끔씩만 사용하고 last_name을 자주 사용한다면 첫 번째 열이 last_name, 두 번재 열이 fist_name인 다중 열 인덱스를 만들면 된다. 이러한 인덱스를 만들어 놓으면 WHERE 절에서 last_name만을 사용하는 질의와 last_name과 first_name을 함께 사용하는 경우에 모두 이 다중 열 인덱스를 사용할 수 있다.

하지만 다중 열 인덱스를 잘못 만들면 그 인덱스가 전혀 쓰이지 않을 수도 있고 쓰이더라도 가끔씩만 쓰일 수 있다. 위에 나온 예의 경우, WHERE 절에 first_name만 들어가는 질의에서는 last_name이 첫 번째 열이고 first_name이 두 번째 열인 인덱스를 사용할 수 없다.

다중 열 인덱스를 제대로 만들려면 애플리케이션과 그 애플리케니션에서 나올 수 있는 질의 유형을 확실히 이해해야 한다. EXPLAIN SELECT 도구(잠시 후에 나옴)를 이용하여 결과를 다시 확인해보는 습관을 갖도록 하자.

인덱스를 만들지 않는 것이 나을 수도 있다

인덱스와 데이터 테이블을 읽는 것보다 테이블 전체를 검색하는 것이 더 빠른 경우도 있다. 특히 인덱스 열에 몇 개가 되지 않는 테이터가 고루 분포된 경우가 이런 경우에 속한다. 대표적인 예로 두 개의 값(남성과 여성)이 고루 분포된 남/여 구분 열을 들수 있다. 성을 기준으로 선택할 때에는 어차피 전체 열 가운데 절반 정도를 읽어들여야 한다. 따라서 이런 경우에는 테이블 전체를 스캔하는 것이 더 빠르다. 물론 사전에 어느 쪽이 더 나을지 테스트해 보고 인덱스 사용 여부를 최종적으로 결정하는 것이 좋다.

EXPLAIN SELECT

MySQL에서 제공하는 EXPLAIN SELECT라는 명령은 퍼포먼스 튜닝에 있어서 매우 중요한 도구이다. 어떤 애플리케이션에 질의를 적용하기 전에 반드시 이 유틸리티를 이용하여 질의가 예상대로 실행되는지 확인해보는 것이 좋다. 이 도구를 이용하면 다음과 같은 것을 알 수 있다.

질의에서 인덱스를 사용하는 방법(또는 사용하지 못하게 되는 원인)
테이블을 연결하는 순서
이 명령을 이용하면 MySQL에서 질의를 실행하는 방법을 정확하게 알 수 있기 때문에 질의 성능을 향상시키는데 있어서 많은 도움을 얻을 수 있다.

EXPLAIN SELECT에 대해 자세하게 알아보기 전에 MySQL에서 SQL 질의를 어떻게 컴파일하고 실행시키는지 알아보자. 질의 처리 과정은 [그림 5-1]에 나온 것과 같이 몇 가지 단계로 나눌수 있다:

+--------------------------------------------------------+
|                       클라이언트                       |
+--------------------------------------------------------+
   |1                    MySQL                  ^5
   V                                            |
+-----------+       +-----------+       +-------------+
| 파싱 단계 | --> |최적화 단계| --> | 실행 단계 |
|     2     |       |     3     |       |      4      |
+-----------+       +-----------+       +-------------+

*[그림 5-1] 질의 처리 단계*

[그림 5-1]에 나온 모델은 MySQL에서 질의를 처리하는 과정을 단순화시켜놓은 것이다. 하지만 이 정도만 알아도 질의를 최적화하는 과정을 알아보는 데에는 충분하다. 이제 각 단계에 대해 알아보자.

질의를 서버로 보낸다. 모든 질의 처리 과정은 서버에 질의를 보내는 클라이언트에 의해 시작된다.
파싱 단계. 이 단계에서는 MySQL이 SQL 질의를 문법에 맞게 파싱한다. 또한 질의에 들어 있는 모든 테이블과 열이 유효한지 확인한다.
최적화 단계. 이 단계에서는 MySQL이 파싱 단계에서 수집한 정보를 받아서 질의를 있는 정보를 모두 고려하고 내부 최적화 규칙을 적용하여 더 빠르게 처리할 수 있는 실행 계획을 수립한다. 이 단계까지는 테이블이나 인덱스를 전혀 액세스하지 않는다. 2, 3번 단계를 보통 컴파일 단계(compilation)라고 한다.
실행 단계. 실행 단계에서는 최적화 단계에서 만들어진 질의 계획을 받아서 실행시킨다. 여기에서 중요한 것은 실행단계에서는 질의 계획을 바꾸지 않는다는 점이다. 따라서 최적화 단계에서 계획을 잘못 세워도 그 계획을 그대로 실행된다.
결과를 클라이언트로 보낸다. 질의가 종료되면 그 결과를 클라이언트에 보낸다.
모든 질의는 최적화 단계에서 만들어진 계획에 따라 실행된다. 따라서 질의의 성능을 향상시키는 데 있어서 가장 중요한 것은 MySQL에서 질의를 실행시키기 위해 사용하는 질의 계획을 이해하는 것이다. 질의 최적화는 아주 복잡한 소프트웨어에 의해 처리된다. 다른 소프트웨어와 마찬가지로 이 소프트웨어에서도 몇가지 내부 규칙과 가정을 바탕으로 작업을 처리한다. 대체로 만족스러운 결과가 나오지만 가끔씩 최적화가 조금 덜된 계획이 나오는 경우도 있다.

EXPLAIN SELECT는 질의 계획을 보여줌으로써 그 질의 계획을 향상시킬 수 있게 해주는 명령어이다. 질의 계획을 알면 그 계획의 어떤 부분에 문제가 있는지 찾아낼 수 있다.

예를 들어, State 테이블(주에 대한 정보가 들어 있는 테이블)이 있고, 주를 나타내는 약어인 state_cd를 바탕으로 이름인 state_name을 구하는 질의가 있다고 생각해 보자:

mysql> SELECT state_name FROM State WHERE state_cd = 'CA';

+------------+
| California |
+------------+
| state_name |
+------------+

1 row in set (0.00 sec)

EXPLAIN SELECT 명령어를 사용할 때에는 SELECT 질의의 맨 앞에 EXPLAIN 키워드만 추가하면 된다. 이렇게 하면 MySQL에서는 질의를 실행시키지 않고 질의를 실행시키기 위한 계획을 설명하는 내용을 출력한다. 예를 들면, 다음과 같다:

mysql> EXPLAIN SELECT state_name FROM State WHERE state_cd = 'CA';

1 row in set (0.00 sec)

위의 결과로부터 실행 단계에서 처리할 단계들을 순서대로 알 수 있다. 여기에서 사용한 예에는 단계가 하나밖에 없다. 조금 더 복잡한 질의 계획은 잠시 후에 다루기로 하고 우선 위의 EXPLAIN SELECT 문에서 리턴한 열을 살펴보고 그 의미를 알아보기 하자.

table

출령된 행에서 다루고 있는 테이블. 여러 개의 테이블을 사용하는 질의에서는 EXPLAIN SELECT에서 각 테이블마다 행을 하나씩 리턴한다.

type

연결 유형. 연결 유형의 종류는 다음과 같다(빠른 것부터 느린 것 순서).

system

한 행짜리 시스템 테이블. const 연결 유형이라는 특별한 경우에만 적용된다.

const

조견에 맞는 행이 최대 한 개 있고, 한 번만 읽을 수 있으며, 질의 최적화의 나머지 과정에서는 상수로 간주된다. 테이블을 한 번 밖에 읽지 않기 때문에 빠르게 처리된다.

eq_ref

이전 테이블에 있는 행의 각 조합에 대해 한 행까지만 읽는다. 이 유형은 인덱스에 있는 모든 열을 질의에서 사용하고, 그 인덱스가 UNIQUE 또는 PRIMARY KEY인 경우에 쓰인다.

ref

이전 테이블에 있는 행의 각 조합에 대해 조건에 맞는 모든 행을 읽는다. 이 유형은 인덱스가 UNIQUE도 PRIMARY KEY도 아닌 경우 또는 질의에서 인덱스 열의 왼쪽 부분집합을 사용한 경우에 쓰인다.

range

주어진 영역에 있는 행만 가져간다. 이때 인덱스를 사용하여 행을 선택한다.

index

이전 테이블에 있는 행의 각 조합에 대해 모든 인덱스를 검색한다. 인덱스만 검색한다는 점을 제외하면 ALL 연결 유형과 같다.

ALL

이전 테이블에 있는 행의 각 조합에 대해 테이블 전체를 검색한다. ALL 연결이 되지 않도록 인덱스를 이용해야 한다.

possible_keys

possible_keys에는 이 테이블에 있는 행을 찾기 위해 MySQL에서 사용할 수 있는 인덱스의 목록이 들어간다. 관련된 인덱스가 없으면 possible_keys가 NULL이 된다. 이런 경우에 인덱스를 추가해 질의의 성능을 향상시킬 수 있음을 알 수 있다.

key

key에는 MySQL에서 실제 선택한 인덱스의 목록이 나온다. 인덱스를 사용하지 않으면 이 값이 NULL이 된다.

key_len

key_len에는 MySQL에서 선택한 인덱스의 길이가 바이트 단위로 출력된다. 이 목록으로부터 다중 열 인덱스에서 어떤 부분이 쓰이는지 알 수 있다.

ref

ref에는 테이블에서 행을 선택할 때 사용한 열 또는 상수의 목록이 나온다.

rows

rows는 MySQL에서 질의를 실행하기 위해 검사할 행의 개수를 나타낸다.

Extra

Extra에는 질의를 분해하는 방법에 대한 추가 정보가 들어 있다. 여기에 들어갈 수 있는 값은 다음과 같다.

distinct

MySQL에서 조건에 맞는 행을 찾아내면 그 테이블에 대한 검색을 중단한다.

not exists

MySQL에서 질의에 대해 왼쪽 연결 최적화를 할 수 있는 경우

range checked for each record (index map: #)

MySQL에서 적당한 인덱스를 찾지 못한 경우. 이전 테이블에 있는 행의 각 조합에 대해 사용할 인덱스를 찾아본다. 이상적인 방법은 아니지만 인덱스를 전혀 사용하지 않는 것보다는 빠르다.

using fiesort

데이터를 가져오기 전에 행을 정렬해야 하는 경우

using index

필요한 정보가 모두 인덱스에 있기 때문에 테이블에서 전혀 테이터를 읽을 필요가 없는 경우

using temporary

질의를 분해하기 위해 임시 테이블을 만들어야 하는 경우. 서로 다른 열에 대해 ORDER BY와 GROUP BY를 사용하는 경우에 해당한다.

where used

이 테이블에서 리턴하는 행에 제한을 두기 위해 WHERE 절을 사용한다는 것을 알려준다.

앞에서 사용한 것보다 조금 더 복잡한 예를 통해 EXPLAIN SELECT로 질의를 최적화하는 방법을 살펴보자. 이 절에서는 SELECT 질의만을 다루고 있지만 UPDATE와 DELETE 선언문에 대해도 같은 방법을 적용시킬 수 있다. INSERT 선언문은 INSERT...SELECT 선언문이 아닌 이상 최적화할 필요가 없다. INSERT...SELECT 선언문에서도 최적화하는 부분은 SELECT 선언문 뿐이다.

이 예제에서는 미국에 있는 50개의 주에 대한 데이터가 저정되어 있는 State 테이블을 사용하기로 하자:

mysql> DESCRIBE State;

3 rows in set (0.00 sec)

캘리포니아주(주를 나타내는 약어가 CA)의 이름을 구할 때에는 다음과 같이 하면 된다.

SELECT state_name FROM State WHERE state_cd = 'CA';

EXPLAIN SELECT를 실행시킨면 질의가 어떻게 실행되는지 알 수 있다:

mysql> EXPLAIN SELECT state_name FROM State where state_cd = 'CA';

ALL이라는 연결 유형으로부터 MySQL에서 질의를 만족시키기 위해 State 테이블에 있는 모든 행을 검색할 것이라는 것을 알 수 있다. MySQL에서는 테이블에 있는 모든 행을 읽어서 WHERE 절에 있는 조건(state_cd = CA)과 비교한다. rows 열에서는 MySQL에서 질의를 만족시키기 위해 50개 있으므로 쉽게 50개일 것이라고 예상할 수 있다)의 행을 검사할 것으로 예측하고 있음을 알 수 있다.

물론 이 질의 속도를 향상시킬 수 있다. WHERE 절에서 state_cd를 사용하므로 그 열에 대해 인덱스를 만들고 EXPLAIN_SELECT를 다시 실행시켜보자:

mysql> CREATE INDEX st_idx ON State (state_cd);
.
.
mysql> EXPLAIN SELECT state_name FROM State WHERE state_cd = 'CA';

+-----------------------------------------------------------------------------+
| table | type | possible_keys | Key | key_len | ref | rows | Extra |
+-----------------------------------------------------------------------------+
| state | ALL | st_idx | st_idx | 2 | const | 1 | where used |
+-----------------------------------------------------------------------------+

1 row in set (0.00 sec)

key 열을 보면 MySQL에서 새로 만든 인덱스를 사용하고 있다는 것을 알 수 있다. 결과적으로 처리해야 할 행의 개수가 50개에서 한 개로 줄어들었다.

state_cd 열에 대한 인덱스는 MySQL에 최적화 과정에서 사용할 수 있는 추가 정보를 제공한다. MySQL에서는 st_idx 인덱스를 이용하여 WHERE 절의 조건을 만족시키는 행을 찾는다. 인덱스는 정렬되어 있기 때문에 조건에 맞는 행을 빠르게 찾을 수 있다. 인덱스에 있는 각 행은 테이블에 있는 실제 행을 가리키는 포인터를 제공한다. MySQL에서 인텍스에 있는 행을 찾아내고 나면 질의를 만족시키기 위해 테이블의 어떤 행을 읽어야 할지 정확하게 알아낼 수 있다.

첫 번째 경우, 즉 인덱스를 사용하지 않은 경우에는 MySQL에서 조건에 맞는 행을 찾기 위해 테이블에 있는 모든 행을 읽어서 주어진 조건과 비교해야 했다. 하지만 두 번째 경우, 즉 인덱스를 사용하는 경우에는 정렬된 인덱스로부터 조건에 맞는 레코드를 찾아내고 그 인덱스에 해당하는 행을 테이블에서 읽어오면 된다. 따라서 휠씬 빠르게 결과를 얻을 수 있다. 이제 조금 더 복잡한 예를 생각해 보자. 다음과 같은 City라는 테이블이 있다고 가정하자:

mysql> DESCRIBE City;

이 예제에서는 테이터베이스에 각 주마다 50개씩의 도시가 있어서 도시의 개수가 총 2,500개라고 가정하자. 또한 State 테이블에 인덱스가 없다고 가정하자. 다음은 샌프란시스코가 속한 주를 검색하기 위한 질의이다:

mysql> SELECT state_name FROM State, City
-> WHERE city_name = "San Francisco"
-> AND State.state_cd = City.state_cd;

EXPLAIN SELECT 명령을 이용하면 위의 질의에 대한 정보를 얻을 수 있다:

mysql> EXPLAIN SELECT state_name FROM State,
-> City WHERE city_name = "San Francisco"
-> AND State.state_cd = City.state_cd;

위의 질의 계획은 두 단계로 이루어진다. 첫 번째 단계에서는 (질의 유형이 ALL이므로) MySQL에서 State 테이블에 있는 모든 행을 읽어들인다는 것을 알 수 있다. 또한 MySQL에서 50개의 행을 읽을 것이라고 예측한다는 사실도 알 수 있다. 두 번째 단계에서는 그 이전 테이블에 있는 50개의 행에 대해 City 테이블에 있는 2,500개의 행을 모두 읽어서 "San Francisco"라는 이름을 가진 도시를 찾아낸다. 즉 질의를 만족시키기 위해 모두 125,000(50*2,500)의 행을 읽어들이고 각각의 행이 조건에 맞는지 비교해야 한다. 이렇게 되면 분명히 이상적인 상황과는 거리가 멀어진다. WHERE 절에 인덱싱되지 않은 열이 있기 때문에 인덱스를 사용하면 성능을 분명히 향상시킬 수 있을 것이다. 우선 앞에서 만들었던 주 코드에 대한 인덱스를 만들어 보자:

mysql> CREATE UNIQUE INDEX st_cd ON State (state_cd);

이제 훨씬 향상된 질의 계획을 확인할 수 있다:

mysql> EXPLAIN SELECT state_name FROM State, City WHERE city_name =
-> "San Francisco" AND State.state_cd = City.state_cd;

여전히 두 단계를 거쳐야 하지만 이번에는 City 테이블에 있는 각 행을 읽어서 그 값을 WHERE 절의 조건과 비교한다. 조건에 맞는 행을 찾으면 두 번째 단계로 넘어가서 그 행을 주 코드를 바탕으로 State 테이블에 있는 행과 연결한다.

이렇게 인덱스를 한 개만 추가해도 상황은 크게 호전된다. 이제 각 도시마다 한 주만 읽어오면 된다. 이제 city_name 열에 대한 인덱스를 추가하면 City 테이블의 연결 유형이 더 이상 ALL이 아니라 ref로 바뀐다:

mysql> CREATE INDEX city_idx ON City (city_name);
.
.
mysql> EXPLAIN SELECT state_name FROM State, City WHERE city_name =
-> "San Francisco" AND State.state_cd = City.state_cd;

+-----------------------------------------------------------------------------------------+
| table | type | possible_keys | Key        | key_len | ref           | rows | Extra      |
+-----------------------------------------------------------------------------------------+
| City | ref | city_idx      | city_idx   | 20      | const         | 2500 | where used |
| State | ref | st_idx        | st_idx     | 3       | city.state_cd | 1    | where used |
+-----------------------------------------------------------------------------------------+

두 개의 인덱스를 추가하고 나니 읽어야 할 행의 개수가 125,000개에서 두개로 줄어들었다. 이것만 보아도 인덱스를 추가하면 얼마나 속도가 빨라질지 알 수 있을 것이다.

캘리포니아주에 있는 모든 도시를 찾아내는 질의는 전보다 훨씬 복잡하다:

mysql> EXPLAIN SELECT city_name FROM City, State WHERE City.state_cd
-> = State.state_cd and State.state_cd = 'CA';

이번에는 MySQL에서 2,500개의 도시를 모두 검색해보자. City 테이블에 인덱스가 없으면 state_cd를 가지고 연결을 할 수 없으므로, 그 인덱스를 추가해 보자:

mysql> CREATE INDEX city_st_idx ON City (state_cd);
.
.
mysql> EXPLAIN SELECT city_name from City, State where City.state_cd
-> = State.state_cd and State.state_cd = 'CA';

+-----------------------------------------------------------------------------------------------+
| table | type | possible_keys | Key         | key_len | ref   | rows | Extra                   |
+-----------------------------------------------------------------------------------------------+
| State | ref | st_idx        | st_idx      | 2       | const | 1    | where used; Using index |
| City | ref | city_st_idx   | city_st_idx | 2       | const | 49   | where used              |
+-----------------------------------------------------------------------------------------------+

이렇게 인덱스를 추가하고 나면 MySQL에서는 약 50개 정도의 행만 읽어도 질의를 끝낼수 있다. 여기에 나와 있는 숫자는 사실 예상치일 뿐이라는 점을 기억해 두자. 질의 계획을 분석하다 보면 이렇게 나온 예상치와 자신이 알고 있는 테이터베이스에 대한 정보를 바탕으로 한 값을 비교하는 일이 자주 생간다. 이 경우에는 이 데이터베이스에 기록된 캘리포니아주에 있는 도시가 50개이므로 우리가 예상하는 값(50)과 MySQL의 예상치가 일치한다는 사실을 알 수 있다.

기타 옵션

MySQL에서 질의를 최적화한 결과가 항상 완벽한 것은 아니다. 가끔씩 선택해야 할 인덱스를 선택하지 않는 경우도 있다. 이런 경우에는 isamchk/myisamchk 유틸리티를 활용하면 도움이 된다. MySQL에서는 인덱스의 값이 고르게 분포되어 있다고 가정한다. isamchk --analyze 또는 myisamchk --analyze 명령을 사용하면 테이블을 읽어서 각 열에 대한 테이터 분포 히스토그램을 구할 수 있다. 이 데이터로부터 얻은 정보를 바탕으로 최적화 단계에서 더 좋은 질의 계획을 만들 수 있다. 여기에서 --analyze 옵션은 질의와는 직접적인 관계가 없기 때문에 질의를 실행하기 전에 별도로 실행시켜야 한다.

또 다른 방법으로 질의에서 USE INDEX/IGNORE INDEX를 사용하는 방법이 있다. 이러한 절을 이용하면 어떤 인덱스의 사용 여부를 강제로 지정할 수 있다. 이 옵션에 대한 자세한 내용은 15장에 나와 있다.

데이터베이스 서버 튜닝

애플리케이션의 성능을 향상시키기 위해 MySQL 서버 수준에서 할 수 있는 일도 몇 가지 있다. 서버를 튜닝할 때에는 그 서버를 사용하는 모든 애플리케이션에 영향을 미치게 될 것이라는 점을 항상 염두에 두어야 한다. 그리고 설정을 바꿨을 때 어떤 애플리케이션은 성능이 향상되는 반면 같은 서버를 사용하는 다른 애플리케이션의 성능은 저하되는 경우도 있다.

MySQL 서버에서 몇가지 변수의 값을 고쳐서 성능을 향상시킬 수 있다. mysql -help 라는 명령을 사용하면 이런 매개 변수에 대한 자세한 내용을 볼 수 있다.

일반적으로 MySQL을 튜닝할 때에는 다음 두 변수가 가장 중요하다.

table_cache

table_cache는 MySQL 테이블 캐시의 크기를 조절하는 변수이다. 이 값을 키우면 매번 파일을 열거나 닫을 필요 없이 동시에 더 많은 테이블을 열어두 수 있다.

key_buffer_size

key_buffer_size는 인덱스를 담아두는 버퍼의 크기를 조절하는 변수다. 이 값을 키우면 인덱스 생성 및 수정 성능이 향상되고, 더 많은 인덱스 값을 메모리에 저장할 수 있다.

운영체제/하드웨어 튜닝

하드웨어 및 OS 튜닝에 대한 자세한 내용은 이 책의 범위를 벗어난다. 하지만 일반적으로 다음과 같은 점을 고려해야 한다.

일반적인 하드웨어 업그레이드를 통해 MySQL의 성능을 향상시킬 수 있다. 예를 들어, 시스템에 메모리를 추가하면 MySQL 캐시 및 버퍼에 더 많은 메모리를 할당할 수 있다. 더 빠른 디스크를 사용하면 I/O 속도로 빨라진다.
테이터베이스를 여러 개의 물리적 기기에 분산시키는 것도 도움이 된다. 예를 들어, 테이터 파일과 인덱스 파일을 서로 다른 디스크에 저장하면 성능을 향상시킬 수 있다.
정적 이진 파일이 동적 이진 파일보다 빠르다. MySQL을 만들때 동적으로 링크하지 않고 정적으로 링크하도록 설정하면 속도를 향상시킬 수 있다.
최적화 및 업그레이드를 할 때에는 우선 데이터베이스를 어떤 식으로 사용하는지 확실히 이해해야 한다.

저작자표시

'프로그래밍 > mysql' 카테고리의 다른 글

mysql - 행 열 변환 쿼리 (0)	2012.07.19
mysql - 플랜 보는 법 (0)	2012.07.19
mysql - 특정쿼리(query)로 덤프(dump)받기 (0)	2012.07.19
mysql - 트랜젝션 level 정보 (0)	2012.07.19
mysql - 테이블 잠금(LOCK TABLES)의 단점 보완방법 (0)	2012.07.19

맘스토리

mysql - 퍼포먼스 튜닝

'프로그래밍 > mysql' 카테고리의 다른 글

티스토리툴바

mysql - 퍼포먼스 튜닝

'프로그래밍 > mysql' 카테고리의 다른 글

'프로그래밍/mysql' Related Articles

티스토리툴바