[MySQL] 쿼리 튜닝 전략 - NoOffset

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

배경

Offset과 이를 사용하지 않는 NoOffset 쿼리 튜닝에 대해서 알아본다. 동일한 결과를 반환하는 쿼리에 대해 성능을 개선할 수 있다.

Offset

Paginatation을 구현할 때 사용하는 쿼리, 데이터를 어디서부터 가져올지 지정한다. Offset을 사용할 때는 쿼리가 지정된 Offset 값만큼의 레코드를 모두 읽어들인 후에 필요한 데이터를 반환한다. 이 때문에 Offset 값이 커질수록 MySQL이 불필요하게 많은 레코드를 스캔하여 성능 저하를 일으킬 수 있다.

 

예를 들어 아래 예시에서 데이터베이스는 처음 25개의 row를 조회한다. 이후 6번째부터 25번째 row(총 20개의 row)를 반환한다.

출처 : https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-LIMIT-OFFSET

-- 5번째 행 부터 25행 까지 출력 (6 ~ 25)
SELECT * FROM 테이블명 ORDERS LIMIT 20 OFFSET 5;

NoOffset

Offset을 사용하지 않고 Paginatation을 구현하는 쿼리 튜닝 방안. Offset 사용 여부에 따라 불필요한 데이터를 조회하는 부분을 개선하며 Offset 사용 여부는 쿼리 성능에 큰 차이를 만든다.

MySQL의 쿼리 실행 순서

NoOffset을 활용한 쿼리 튜닝 방안에 살펴보기에 앞서, MySQL의 쿼리 실행 순서를 알아야한다. MySQL의 쿼리는 다음과 같은 순서로 실행된다.

 

1. WHERE 조건 및 JOIN
2. GROUP BY
3. DISTINCT
4. HAVING
5. ORDER BY
6. LIMIT

실행 순서에서 알 수 있듯이 ORDER BY를 실행하기 전에 WHERE 절과 HAVING 절에서 가능한 row를 필터링하여 row 개수를 줄이는 것이 중요하다. 특히 ORDER BY는 많은 연산을 발생시키므로, 그 전에 처리해야 할 데이터 양을 줄이면 성능 향상에 도움이 된다.

No Offset과 쿼리 튜닝

다음과 같은 테이블과 쿼리가 있다고 가정하자.

CREATE TABLE test2 (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 300개의 데이터 적재
SET @i = 0;
INSERT INTO test2 (id, name)
SELECT (@i := @i + 1) AS id, CONCAT('Name', @i)
FROM information_schema.tables
LIMIT 300;

test2의 로우를 조회하는 아래 쿼리는 테이블의 모든 row에 대해 ORDER BY를 수행하고 150개의 Row를 조회한뒤 50번째부터 150번째 로우를 반환한다. 실제로 필요한건 100개의 row이다. 하지만 모든 row를 조회하면서 연산이 발생하고, ORDER BY까지 수행하며 정렬 연산까지 추가로 소요된다. 즉 불필요한 연산이 발생한다.

SELECT * FROM test2 ORDER BY id DESC LIMIT 100 OFFSET 50;

 

이때 No Offset을 사용하여 불필요한 row를 조회하지 않도록 개선할 수 있다. 아래 쿼리는 WHERE절을 통해 row의 개수를 1차적으로 필터링한다. 이후 ORDER BY를 수행하면 정렬 대상 row의 개수가 줄어든다. 결국 동일한 row를 조회하는 쿼리에 대해 비효율을 개선하고 성능을 향상할 수 있다.

-- 쿼리 튜닝 결과, 위 쿼리와 동일한 결과를 반환한다.
SELECT * FROM test2 WHERE id < 300 - 50 ORDER BY id DESC LIMIT 100;

 

NoOffset을 활용하여 ORDER BY 전에 데이터를 필터링(WHERE 절)하면 성능을 크게 향상시킬 수 있다. 특히 대규모 데이터에서 OFFSET을 사용할 경우 쿼리의 비효율적인 데이터 스캔이 발생할 수 있는데, NoOffset 방식을 적용하여 쿼리 성능을 최적화하는 것이 좋다.

 

예시로 사용한 위 튜닝 방식은 id가 순차적이고 연속적인 경우 유효하다. 만약 id가 연속적이지 않거나, 값에 갭이 있다면 다른 필터링 방법을 고려해야 한다. 또한 id가 인덱스로 구성되어 있어야 좋은 성능을 발휘할 수 있다.