4장 쿼리 성능 최적화

일반적으로 MySQL 은 WHERE 절을 세 가지 방법으로 사용할 수 있다. 제일 좋은 방법부터 알아보면 다음과 같다.

• 적용되지 않는 행을 제거하기 위해 인덱스 탐색에 조건을 적용한다. 이는 스토리지 엔진 계층에서 동작한다.
• 적절한 커버링 인덱스를 사용하여, 행에 접근하지 않고 인덱스로부터 결과를 가져온 뒤 조건에 맞지 않는 행을 제거한다 (Extra 컬럼의 Using Index) 이는 서버 계층에서 동작하지만 테이블로부터 행을 읽을 필요는 없다.
• 테이블로부터 값을 가져온 뒤, 조건에 맞지 않는 행을 제거하는 데 사용한다 (Extra 컬럼의 Using where) 이는 서버 계층에서 일어나며, 조건에 맞지 않는 것을 제거하기 전에 테이블로부터 값을 읽어들여야 한다.

상대적으로 적은 행을 얻기 위해 많은 수의 행을 조사하는 쿼리를 찾았다면 다음과 같은 방법을 통해 수정해보자.

• 스토리지 엔진이 전체 행을 가져올 필요가 없도록 데이터를 저장하고 있는 인덱스를 사용한다
• 스키마를 변경한다. 그 중 한가지 예는 집계 테이블을 사용하는 것이다.
• 복잡한 쿼리를 MySQL 옵티마이저가 적절하게 최적화할 수 있는 방식으로 다시 작성한다.

쿼리를 재구성하는 방법

문제 있는 쿼리를 최적화하는 것은 원하는 결과를 보이면서 이 쿼리를 대체할 수 있는 다른 쿼리를 찾는 것이다. 하지만 이 말은 MySQL 로부터 같은 결과를 돌려받아야 하는 것을 의미하지는 않는다. 같은 결과셋을 반환하면서도 성능이 더 좋게 나오도록 쿼리를 변경할 수 있는 경우도 있지만, 때로는 성능상의 이점을 고려하여 쿼리를 다른 결과셋을 반환하는 쿼리로 변경하는 것을 고려해야 한다.

복잡한 쿼리 vs 여러 개의 쿼리

전통적으로 데이터베이스를 설계할 때는 소수의 쿼리로 가능한 많은 일을 하도록 해왔다. 이런 방식은 네트워크 통신 비용과 쿼리 파싱 및 최적화 단계의 오버헤드 때문에 역사적으로 좋은 결과를 보여왔다.

하지만 MySQL 은 접속을 하고 끊는 걸 매우 효율적으로 하도록 설계되었고, 작고 간단한 쿼리들에 아주 빠르게 응답한다. 게다가 최신 네트워크는 예전에 비해 굉장히 빨라졌고, 네트워크 지연도 줄어들었으므로 MySQL 에서는 위에서 얘기한 문제가 발생하지 않는다.

MySQL 은 간단한 쿼리를 1초에 50,000 개도 넘게 처리할 수 있으므로 각각의 경우에 대한 비용을 계산해 본 뒤 더 가벼워 보이는 방법을 사용하자.

조인 분해

높은 성능을 내는 웹사이트들은 조인 분해 (join decomposition)를 사용한다. 다중 테이블을 조인하기보단 여러 개의 단일 테이블 쿼리들을 실행한 뒤 애플리케이션에서 값을 조인하는 방법을 통해 조인을 분해할 수 있다. 이런 식으로 쿼리를 재구성하면 성능상에선 확실히 이득이 있다.

• 많은 애플리케이션에서는 테이블에 직접 매핑되는 ‘객체’를 캐시하므로, 캐시가 더 효율적으로 동작할 수 있다. 어떤 한 테이블만 빈번하게 변한다면, 조인을 분해하는 것을 통해 캐시 무효화를 줄일 수 있다.
• 애플리케이션에서 조인을 하게 되면 테이블들을 여러 서버로 분산시켜서 큰 규모로 쉽게 만들 수 있다.
• 쿼리 자체도 효율성이 더 높아진다. 예를 들어 MySQL 에서 조인 대신 IN() 리스트를 사용하게 되면 조인을 사용할 때보다 더 적절한 방법을 이용해서 행 ID들을 정렬하고 가져온다.
• 행을 중복해서 접근하는 것을 줄일 수 있다. 애플리케이션에서 조인을 하게 되면 각 행을 단 한번만 가져오면 되는 반면 조인을 사용할 떄에는 같은 데이터에 반복해서 접근하는 비정규화가 필요해진다. 그렇기 때문에 이런 식으로 쿼리를 변경하면 전체 네트워크 트래픽이나 메모리 사용량을 줄일 수 있다

쿼리 실행의 기초

1. 클라이언트는 서버로 SQL 구문을 보낸다.
2. 서버는 쿼리 캐시를 체크하고, 만약 캐시에 쿼리가 있다면 캐시에 저장된 결과를 반환한다. 만약 캐시에 쿼리가 없다면 SQL 구문은 다음 단계로 넘어간다.
3. 서버는 SQL 을 파싱하고, 전처리하고, 최적화한 뒤 쿼리 실행 계획으로 만든다.
4. 쿼리 실행 엔진은 스토리지 엔진 API 를 호출하는 것을 통해 계획을 실행한다.
5. 서버는 결과를 클라이언트로 보내준다.

MySQL 클라이언트/서버 프로토콜

MySQL 의 클라이언트/서버 프로토콜은 반이중 방식이므로 MySQL 서버가 메시지를 보내고 받는 건 모두 가능하지만, 이 두 가지 동작을 동시에 수행하지는 못한다. 그렇기 때문에 메시지를 짧게 자를 수는 없다. 이런 프로토콜로 인해 MySQL 은 간단하고 빠르게 통신할 수 있지만 약간의 제약을 받기도 한다.

그 중 하나로는 흐름을 제어할 수 없다는 점이 있다. 한 쪽에서 메시지를 보내기 시작하면 다른 쪽에서는 메시지 전체를 받기 전에는 응답할 수 없다. 어떤 때에든 공은 한쪽만이 가지고 있고, 공을 가지고 있지 않는다면 공을 토스할 수 없다.

클라이언트는 쿼리를 단일 패킷 형태로 전송하므로 커다란 쿼리를 사용해야 한다면 max_packet_size 설정 값을 잘 설정해야 한다. 쿼리를 보내고 나면 클라이언트는 결과를 기다리는 것 외엔 아무 것도 할 수 없다.

반면에 서버로부터의 응답은 여러 패킷에 걸쳐서 전송된다. 서버가 응답하게 되면 클라이언트는 모든 결과 셋을 받아야 하며, 필요한 행 몇 개만 받은 뒤 나머지는 보내지 말라고 요청하는 것은 불가능하다. 이렇기에 적합한 LIMIT 절을 사용하는 것이 필요하겠다.

MySQL 서버가 행들을 생성하자마자 클라이언트에 밀어주고, 클라이언트는 밀려온 행들을 받는 역할밖에 하지 않는다. 서버 측에 행을 그만 보내라는 얘기를 전할 방법이 없기 때문에 클라이언트 측은 ‘소방 호스로부터 물을 마시는 상황’ (drinking from the fire hose) 일 수 밖에 없다.

MySQL 에 접속하기 위한 라이브러리들은 대부분 전체 결과 셋을 가져온 뒤 메모리에 저장해두거나 필요할 때 행을 하나씩 가져온다. 일반적으로 기본 동작은 전체 결과 셋을 메모리에 저장해두는 것이다. 서버는 행들을 모두 가져가기 전에는 쿼리에 필요한 락과 리소스들을 해제하지 않고, 쿼리를 ‘Sending data’ 상태로 만든다. 클라이언트 라이브러리가 결과를 한 번에 패치하게 되면, 서버가 할 일이 줄어들므로 쿼리를 가능한 빠르게 마무리할 수 있다.

쿼리 상태

각 MySQL 연결이나 스레드는 어떤 시점에 뭘 하고 있는지를 나타내는 상태를 가지고 있다. 이런 상태를 확인하는 방법은 여러 가지가 있고, 그 중 가장 사용하기 쉬운 것은 SHOW FULL PROCESSLIST 명령이다. 쿼리가 수행되는 동안 상태는 여러 번 바뀌게 되며, 이 상태는 여러 가지가 있다.

• Sleep : 스레드가 클라이언트로부터 새로운 쿼리를 기다림
• Query : 스레드가 쿼리를 실행하거 있거나 결과를 클라이언트로 보내고 있음
• Locked : 스레드가 서버 레벨에서 권한을 얻기 위해 테이블 락을 기다리고 있음. 락은 InnoDB 의 레코드 락처럼 스토리지 엔진에 의해 구현되며, 스토리지 엔진이 구현한 락은 스레드 자체를 Locked 상태로 만들진 않는다.
• Analyzing 과 statics : 스레드가 스토리지 엔진의 통계 체크 및 쿼리 최적화를 수행 중임
• Copying to tmp table (on disk) : 스레드가 쿼리를 실행하고, GROUP BY, UNION, 파일 정렬 등을 처리하기 위해 임시 테이블로 결과를 복사하고 있음. 상태가 ‘on disk’로 끝난다면 MySQL 은 메모리에 있는 테이블을 디스크에 있는 테이블로 변환하고 있다는 것을 의미한다
• Sorting result : 스레드는 결과 셋을 정렬 중임
• Sending data : 이 상태는 여러 가지 상황을 의미한다. 현재 스레드는 쿼리의 단계 사이에서 데이터를 전송하고 있을 수도 있고, 결과를 만들어내고 있을 수도, 클라이언트에게 결과 셋을 보내주고 있을 수도 있다.
  

쿼리 캐시

캐시가 활성화된 경우 MySQL 은 쿼리를 파싱하기도 전에 쿼리 캐시부터 검사한다. 검사를 위해서는 대소문자를 구별하는 해시 탐색을 이용하며, 모든 바이트가 정확히 일치하는 쿼리가 없다면 캐시에 쿼리가 없다고 판단하고, 다음 단계로 넘어가게 된다.

MySQL 이 캐시에서 동일한 쿼리를 찾으면, 캐시된 쿼리를 돌려주기 전에 권한을 검사해야 한다. MySQL 에서는 캐시된 쿼리가 사용하는 테이블 정보를 저장해두고 있기 때문에 쿼리를 파싱하지 않더라도 권한 체크가 가능하다.

쿼리 최적화 단계

생략…

특정 형식의 쿼리 최적화하기

1. count() 쿼리 최적화하기

COUNT() 는 두 가지 방식으로 동작하는 특별한 함수다. 이 함수를 통해 값을 셀 수도 있고, 행을 셀 수도 있으며 값은 NULL 이 아닌 것을 의미한다. 괄호 안에 컬럼 이름이나 다른 표현을 넣는다면, COUNT() 는 이 표현이 몇 번이나 값을 가지는지를 세게 된다.

다른 형태의 COUNT() 는 단순히 결과에 있는 행 수를 세게 된다. 괄호 안에 있는 표현이 절대 NULL 을 가질 수 없는 경우 이런 식으로 동작하게 되며 일반적인 예로는 COUNT(*) 를 들 수 있다.

일반적으로 자주 하는 실수로는 행 수를 세기 위해 괄호 안에 컬럼 이름을 적어주는 경우를 들 수 잇다. 결과에 있는 행 수를 알고 싶을 때에는 꼭 COUNT(*) 를 사용해야 한다.

2. 복잡한 최적화

일반적으로 COUNT()를 사용하는 쿼리들은 많은 행을 세어야 하며 따라서 많은 데이터에 접근해야 하므로 최적화하기가 쉽지 않다. 그나마 사용 가능한 방법으로는 커버링 인덱스를 사용할 수 있게 만드는 정도 밖에 없으며, 이 정도로 충분하지 않은 경우에는 애플리케이션 구조를 바꾸는 것 외에는 도리가 없다. 집계 테이블을 사용하거나 memcached 같은 외부 캐시 시스템을 사용하는 것도 한번 생각해보자.

조인 쿼리 최적화하기

• ON 이나 USING 절에 사용되는 컬럼에 대해 꼭 인덱스를 추가해주자. 그리고 인덱스를 추가해줄 때는 조인이 되는 순서를 고려해야 한다. 만약 컬럼 c 를 이용해서 테이블 A 와 B 를 조인하고, 쿼리 옵티마이저가 테이블을 B, A 순서로 조인하게 된다면 테이블 B 에는 c 컬럼에 대한 인덱스를 만들 필요가 없다. 사용되지 않는 인덱스는 불필요한 오버헤드일 뿐이다. 일반적으로 다른 이유가 없으면 조인할 때 두 번째 테이블에만 인덱스를 추가하게 된다.
• GROUP BY 나 ORDER BY 를 사용할 때는 MySQL 이 인덱스를 활용할 수 있게끔 한 테이블에 있는 컬럼만 이용하도록 하자
• 조인 구문이나 명령어 우선 순위, 혹은 어떤 동작 방식 등이 바뀔 수 있으므로 MySQL 업그레이드 할 때는 항상 조심해야 한다.

파티션 테이블

파티션 테이블의 중요한 이점은 다음과 같다.

• 특정 행들이 한 파티션에 저장됨을 명시할 수 있는데 이는 서버가 검사해야 할 데이터 양을 줄이고 쿼리를 더 빠르게 한다. 예를 들어, 날짜 범위에 따라 파티션한 후 한 파티션에만 접근하는 날짜 범위로 질의하면 서버는 그 파티션만 읽는다.
• 파티션된 데이터는 그렇지 않은 데이터보다 관리하기 쉽고 파티션 전체를 제거하는 식으로 오래된 데이터를 버리기도 쉽다.
• 파티션된 데이터는 물리적으로 분산될 수 있고 이는 서버가 여러 개의 하드 드라이브를 더욱 효율적으로 사용하게 해준다.

파티션에 관한 수많은 자료를 읽어본 후 CREATE TABLE, SHOW CREATE TABLE, ALTER TABLE, INFORMATION_SCHEMA.PARTITIONS, EXPLAIN 에 대한 설명을 살펴봐야 한다. 파티션 때문에 CREATE TABLE, ALTER TABLE 이 훨씬 복잡하다.

파티션 테이블은 실제로는 스토리지 엔진 수준에서 별개의 인덱스를 가진 별개의 테이블(파티션)을 묶은 컬렉션이다. 이는 파티션 테이블의 메모리와 파일 기술자에 대한 요구사항이 파티션 테이블의 경우와 비슷하지만, 파티션은 테이블에서 독립적으로 접근이 안 되며 각 파티션은 한 테이블에만 속한다.

MySQL 은 파티션 함수를 이용해 어느 행을 어느 파티션에 저장할지 결정한다. 함수는 상수가 아닌 확정적으로 정수값을 반환해야 한다. 파티션은 여러 종류가 있는데 범위 파티션은 파티션마다 일정 범위의 값을 정하고, 행의 값이 어느 범위에 속하는지에 따라 행을 파티션에 할당한다. MySQL 은 키, 해시, 리스트 할당 메서드도 제공한다.

파티션이 효율적인 이유

MySQL 에 파티션 테이블을 설계할 때의 핵심은 파티션을 결이 거친 인덱스 정도로 생각하는 것이다. 날짜별 쿼리의 속도를 높이는 접근방법 중 하나는 (day, itemno) 컬럼에 기본 키를 추가하고 InnoDB 를 쓰는 것이다. 이렇게 하면 각 날짜의 데이터가 물리적으로 묶이고, 따라서 범위 질의는 데이터를 더 적게 검사한다. 기본 키를 빼고 데이터를 날짜별로 파티션하는 방법도 있다. 파티션은 MySQL 에 해당 행을 어디서 찾으면 되는지 대략 알려준다.

파티션 사례

1. 날짜 기반의 데이터를 저장하는 파티션 테이블을 설계하는 법을 살펴보자. 제품별 주문 및 판매에 대한 성능 통계를 모았다고 해보자. 날짜 범위에 따라 쿼리를 자주 실행하기 때문에 기본 키의 첫 번째 위치에 주문 날짜를 두고 InnoDB 스토리지 엔진을 이용해 날짜별로 데이터를 클러스터링 한다. 이제 날짜 범위에 따라 테이블을 파티션 함으로써 상위 수준에서 데이터를 ‘클러스터링’ 할 수 있다.

아래는 파티션 명세를 제외한 기본적인 테이블 정의 예시다.

1
2
3
4
5
6
7

CREATE TABLE sales_by_day (
	day DATE NOT NULL,
	product INT NOT NULL,
	sales DECIMAL(10, 2) NOT NULL,
	returns DECIMAL(10,2) NOT NULL,
    PRIMARY KEY(day, product)
) ENGINE=InnoDB;

연도별 파티션은 날짜별 파티션과 더불어 날짜 기반의 데이터를 다룰 때 흔히 쓰는 방법이다. YEAR() 와 TO_DAYS() 함수는 이런 경우에 파티션 함수로 쓰기 좋다. 일반적으로 범위 파티션에 좋은 함수는 파티션하고자 하는 값과 선형 관계가 있고 YEAR() 와 TO_DAYS() 같은 함수는 그러한 조건에 잘 맞는다. 이제 연도별로 파티션 해보자

1
2
3
4
5
6
7
8

ALTER TABLE sales_by_day 
PARTITION BY RANGE(YEAR(day)) (
	PARTITION p_2014 VALUES LESS THAN (2015),
	PARTITION p_2015 VALUES LESS THAN (2016),
	PARTITION p_2016 VALUES LESS THAN (2017),
	PARTITION p_2017 VALUES LESS THAN (2018),
	PARTITION p_catchall VALUES LESS THAN MAXVALUE);
)

이제 행을 삽입하면 날짜(day) 컬럼의 값에 따라 알맞은 파티션에 저장된다.

1
2
3

INSERT INTO sales_by_day (day, product, sales, returns) VALUES 
   ('2017-01-15', 19, 50.00, 52.00),
   ('2017-09-23', 11, 41.00, 42.00);

그런데 이 테이블 구조에는 한계점이 존재한다. 나중에 연도를 추가하려면 테이블을 고쳐야 하는데(alter) 테이블이 클 땐 비용이 많이 든다. 일단 필요하리라 생각하는 양보다 더 많은 연도를 정의하는 편이 좋을지 모른다. 미리 포함시킨다고 해서 성능에 영향을 미치지는 않기 때문이다.

파티션 테이블은 큰 테이블의 행을 단순히 분산시킬 때에도 많이 쓴다. 거대한 테이블이 있고 쿼리 여러 개가 그 테이블에서 동작한다고 가정해보자. 물리적으로 다른 여러 디스크가 데이터를 제공하길 바란다면, MySQL 이 행을 여러 디스크에 걸쳐 분산시켜야 한다. 이 경우에는 관련 있는 데이터를 가까이 두는 것에는 관심이 없으며, 그저 데이터를 고르게 분산시킬 수만 있으면 된다. 아래의 DDL 은 MySQL 이 행을 기본 키 계수에 따라 행을 분산하게 한다. 이는 파티션 사이에 데이터를 고르게 퍼뜨리기에 좋은 방법이다.

1
2
3
4
5

ALTER TABLE mydb.very_big_table
PARTITION BY KEY (primary key columns) (
	PARTITION p0 DATA DIRECTORY = '/data/mydb/big_table_p0',
	PARTITION p1 DATA DIRECTORY = '/data/mydb/big_table_p1'
);

파티션 테이블의 제약 사항

• 모든 테이블이 동일한 스토리지 엔진을 써야 한다.
• 파티션 테이블의 유니크 인덱스는 모두 파티션 함수가 참조하는 컬럼을 포함해야 한다.
• MySQL 이 쿼리 처리 중에 파티션 테이블의 모든 파티션에 접근하지 않아도 될지라도 여전히 모든 파티션을 잠근다.
• 파티션 함수에 써도 되는 함수와 표현식에 제약이 아주 많다.
• 외래 키가 동작하지 않는다
• LOAD INDEX INTO CACHE 를 못 쓴다.

파티션된 테이블용 쿼리 최적화하기

WHERE 절에 파티션된 키를 명시하는 일은 아주 중요하다. 이렇게 하지 않으면 쿼리 실행 엔진이 테이블의 모든 파티션에 접근해야 한다. MySQL 은 파티션 함수의 컬럼과 비교한 결과에 따라 쳐내는 것만 가능하다. 표현식의 결과에 근거를 두고 쳐내진 못한다.