본문 바로가기
DB

[MySQL] MySQL 최적화에 대한 공부(1/2)

하고있자 2025. 5. 2. 20:27

MySQL에 대해서 여러 쿼리를 날려도 같은 값이 돌아오지만 실제로 걸리는 시간에 대해서는 고민하지 않았었다. 실제로 SQL을 작성할 때 결과 값만을 생각만 했고 이에 어떻게 최적화를 하는지 몰랐다. 그래서 최적화를 하기 위해서 책을 읽었고 이를 기억하기 위해서 글로 남긴다.

 

이 글은 Real MySQL을 기준으로 한다. 또한 여러 최적화 중 몇 가지를 알려주기에 더 많은 공부를 하고 싶다면 Real MySQL 책을 읽거나 공식문서를 읽기 바란다. 이 글의 MySQL 버전은 8.0.37 버전이라는 점을 유의하자.

 

NoOffSet전략

페이징 쿼리를 작성할때 LIMIT, OFFSET 구문을 사용하는데 실제로 LIMIT, OFFSET 구문을 사용하면 DBMS에 더 많은 부하를 발생한다. 실제로 LIMIT 10 OFFSET 100,000이라고 쿼리를 작성하면 100,000번째에서 10개의 레코드를 가져오는 게 아니라 100,000개의 레코드를 읽고 그 뒤에 10개의 레코드를 가져온다. 이 때문에 LIMIT, OFFSET 쿼리를 실행 횟수가 늘어나면 응답실행시간이 점점 늘어난다.

 

이에 원하는 만큼만 조회에서 레코드를 반환하는 방법으로 2 가지 있다.

  1. 범위 기반 방식
  2. 데이터 개수 기반 방식

이 존재한다.

 

범위 기반 방식은 날짜 기간이나 숫자 범위로 나누어서 데이터를 조회하는 방식이다. WHERE절에서 조회 범위를 직접 지정하여 LIMIT를 사용하지 않는다. 일정한 날짜/숫자 범위로 나누어서 조회할 때 사용한다.

 

데이터 개수 기반 방식은 지정된 데이터 건수만큼 데이터를 반환하는 형태로 구현된 방식이다. 배치보다 주로 서비스 단에서 많이 사용되는 방식, 쿼리에서 ORDER BY, LIMIT 절이 사용된다. 1회 차와 N회차 쿼리의 형태가 달라진다.

 

테이블 구성

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    created_at DATETIME
);

posts 테이블은 id, title, content, created_at으로 구성되어 있다.

테이블 컬럼 더미 데이터 생성

INSERT INTO posts (title, content, created_at)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT
    CONCAT('title', LPAD(n, 7, '0')) AS title,
    CONCAT('content', LPAD(n, 7, '0')) AS content,
    DATE_ADD('2019-01-01', INTERVAL FLOOR(RAND() * 1825) DAY) AS created_at
FROM seq;

레코드 개수는 1,000,000개이고 created_at은 2019.01.01~현재까지 날짜를 랜덤으로 값을 가진다. 

일반 쿼리 실행 시간 및 실행 계획

SELECT * 
FROM posts 
LIMIT 1000 OFFSET 500000;

위 쿼리를 실행하면 다음과 같다. 위 쿼리를 보면 posts 테이블 500,000번째의 쿼리에서 1000개의 레코드 값을 가져온다.

위 쿼리의 실행 계획을 실행하면 다음과 같다. 

type을 보면 ALL이다. 이는 테이블 풀 스캔으로 1,000,000개의 레코드 중 996,692개의 레코드를 조회해야 한다고 옵티마이저에서 판단했다.

실제 실행 측정치를 보면 실행 계획에서는 996,424개의 레코드를 조회한다고 했지만 실제로는 501,000개의 레코드를 반환했다. 이는 LIMIT/OFFEST을 사용하면 1부터 501,000까지 순서대로 조회하면서 레코드 값을 가져오게 된다. 실제 쿼리 시간을 보면 715ms의 시간이 걸렸다. 테이블 풀 스캔 시간은 678ms이 걸렸다.

 

최적화 쿼리 실행 시간 및 실행 계획

  • 범위 기반 방식
SELECT * 
FROM posts 
WHERE id > 500000 AND id < 501001;

컬럼인 id의 값을 기준 범위로 값을 가져오기에 id > 500,000, id <501,001로 1000개의 레코드를 가져온다.

 

위 쿼리를 실행하면 다음과 같다. 처음 실행 쿼리와 같은 레코드를 가져온다.

위 쿼리의 실행 계획을 보면 위 사진과 같다. type에서 range라고 한다. 또한 key에서 PRIMARY 키인 id 컬럼을 기준으로 인덱스 레인지 스캔을 한다. rows 수를 보면 필요로 하는 1000개의 레코드를 스캔한다.

 

실제 쿼리 실행 측정치는 다음과 같다. 인덱스 레인지 스캔을 통해서 1000개의 레코드를 반환한다. 실제 실행 시간은 2.96ms의 시간이 걸렸다. 

 

  • 데이터 개수 기반 방식
SELECT * 
FROM posts 
WHERE id>500000 
LIMIT 1000;

위 쿼리를 실행하면 위 2개와 같은 레코드 값을 반환한다. 위 코드는 id값을 500,000번째 이후에서 1000개의 레코드 값을 반환한다.

실행 계획을 보면 인덱스 레인지 스캔을 사용한다. 하지만 조회 레코드 값인 rows 값을 보면 498,346개를 조회한다고 계획한다.

쿼리 실행 측정치를 보면 먼저 id >500,000의 값에 대해서 PRIMARY키인 id컬럼을 기준으로 인덱스 레인지 스캔을 한다. 이에 1000개의 레코드 값을 반환한다. 그리고 Filter와 LIMIT를 통해서 1000개의 레코드 값을 반환한다. 걸린 시간은 2.87ms이다.

 

OFFSET을 사용하면 500,000번째의 값으로 시작해서 1000개의 값을 가져오는 게 아니라 500,000개를 count 하고 LIMIT인 1000개를 조회하는 과정을 거친다. 실제로 OFFSET을 사용하는 사용자는 이런 실행 방식을 원하지 않았겠지만 실제로 쿼리는 테이블 풀 스캔으로 처음부터 OFFSET을 한 값까지 조회를 하게 된다.

 

결론

위 3개의 실행 쿼리를 봤듯이 쿼리를 어떻게 짜느냐에 따라서 715ms에서 2ms으로 시간을 줄이는 게 가능하다. 실제로 페이징 쿼리를 작성할 때 NoOffSet 전략을 사용해서 OFFSET을 사용하지 않고 처음 페이징 쿼리에서는 그냥 값을 가져오고 다음 쿼리부터는 조회하는 컬럼이나 날짜를 조건으로 주고 LIMIT를 사용해서 원하는 값을 조회하도록 한다. 이를 통해서 테이블 풀 스캔이 아닌 인덱스 레인지 스캔을 하도록 유도한다.

 

 

COUNT(*) vs COUNT(column)

MySQL을 사용하면 COUNT(*)하고 COUNT(idx1)에 대해서 어떤 것을 사용해야 할지 고민하는 경우가 있다. 조건에 따라서 쿼리에 속도가 달라지는데 조건은 아래와 같다.

 

먼저 테이블을 생성할 때 그 컬럼이 null인지 not null인지가 중요하다. 또한 WHERE 조건이 있냐 없냐에 따라서 COUNT의 실행시간이 변동된다. 조건이 있으면 Covering Index 나 Non-Covering Index를 사용한다. 조건이 없다면 ha_recods() 스토리지 API나 ha_index_next() 스토리지 API를 사용한다.

 

테이블 구성

CREATE TABLE count_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(50) NOT NULL,
    salary INT,
    INDEX idx_department (department)
);

테이블 구성에서 봐야 하는 점은 department는 not null의 형태이고 salary는 null을 받을 수 있다. department는 idx_department 인덱스를 가진다.

 

테이블 컬럼 더미 데이터 생성

INSERT INTO count_test (name, department, salary)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT 
    CONCAT('User_', LPAD(n, 7, '0')) AS name,
    ELT(FLOOR(1 + (RAND() * 10)), 'IT', 'HR', '디자이너', '기획', '마케팅', '영업', '개발', '보안', '회계', 'QA') AS department,
    IF(n = 500000, NULL, FLOOR(RAND() * 10000000)) AS salary  
FROM seq;

더미 데이터 생성을 보면 salary는 500,000번째 값은 null이고 나머지는 10,000,000 중 랜덤 한 값을 가진다. 즉 레코드에서 salary 컬럼은 999,999의 갯수를 가지고 나머진 모든 컬럼은 1,000,000의 레코드 개수를 가진다. 

 

실제로 레코드 전체 갯수 값을 보면 다음과 같다.

 

조건이 없을 때 쿼리 실행 시간 및 실행 계획

  • COUNT(*)
SELECT COUNT(*) FROM count_test;

위 쿼리를 실행하면 전체의 레코드의 개수를 조회한다.

 

실제로 전체 레코드의 개수는 다음과 같이 1,000,000개이다.

 

실행 계획을 보면 idx_department 인덱스를 사용해서 인덱스 풀 스캔을 하고 rows가 996239개의 레코드를 조회한다고 한다. 여기서 추가적으로 보면 Using index를 사용하는데 이는 커버링 인덱스를 사용한다. 커버링 인덱스는 해당 인덱스만 읽어도 쿼리가 처리된다. 즉 인덱스를 조회할 때 실제 데이터 파일을 읽는 과정이 필요하지 않다.

 

여기에서 중점적으로 봐야 하는 것은 key를 보면 idx_department를 사용하는데 이는 MySQL에서 PRIMARY key는 레코드 데이터를 가지고 있기에 인덱스의 크기가 크다. 그래서 컬럼을 하나만 가지고 있는 idx_department 인덱스를 사용한다.

 

실제 측정치를 보면 다음과 같다. 

실제 측정치는 356ms로 값으로 rows=1을 반환한다. 커버링 인덱스로 값을 전체 값을 조회하고 레코드 개수를 반환한다. 커버링 인덱스로 조회하기에 불필요한 실제 데이터를 읽는 과정을 하지 않는다.

 

COUNT(*)은 테이블이 not null이든 null 이든 전체 값을 조회한다. 전체 레코드의 값을 조회해야 할 수도 있기에 SELECT COUNT(*)의 쿼리 시간은 레코드의 개수에 따라서 결과가 달라진다. 이에 COUNT(*) 쿼리를 최적화하려면 커버링 인덱스를 사용해서 쿼리를 처리하면 성능 최적화가 가능하다. 하지만 실제로 장점보다는 단점이 많아질 수 있다. 그래서 꼭 필요한 부분에서만 사용해야 한다.

 

  • COUNT(column) - NOT NULL
SELECT COUNT(department) FROM count_test;

위 쿼리를 실행하면 department의 레코드의 개수를 조회한다.

 

위 쿼리를 실행하면 1,000,000개의 레코드를 반환한다.

실행 계획을 보면 type은 index이다. Extra는 Using index로 커버링 인덱스를 사용한다. rows는 999044개의 레코드를 조회한다. 인덱스는 키는 idx_department를 사용한다.

실제 측정치를 보면 COUNT(*)와 같은 측정치를 가진다. COUNT(*)와 같은 방식으로 실행된다. 이는 NOT NULL이기에 COUNT(*)과 같은 실제 측정치가 나온 것이다. 만약 NOT NULL 이 아니라면 하나하나 데이터가 null인지 칼럼값을 조회해야 한다. 인덱스가 있기에 커버링 인덱스 스캔으로 전체 레코드를 조회를 하고 개수 결과를 반환한다.

 

  • COUNT(column)
SELECT COUNT(salary) FROM count_test;

위 쿼리를 실행하면 salary의 레코드의 개수를 조회한다.

위 쿼리를 실행하면 999,999개의 레코드를 반환한다.

실행 계획을 보면 type은 ALL이다. 즉 테이블 풀 스캔을 한다. 

위 실제 측정치를 보면 풀 테이블 스캔을 하고 1,000,000개의 레코드를 반환하고 Aggregate count는 집계함수로 총개수를 계산한다. 하지만 풀 테이블 스캔으로 실제 데이터가 있는 디스크에 I/O에서 입출력한다. 1,000,000건의 레코드를 전부 조회하기에 이는 많은 비용과 시간이 든다. 그래서 레코드 값에 null이 있는 COUNT(column)은 576ms로 COUNT(*)인 356ms보다 느리다.

 

조건이 있을 때 쿼리 실행 시간 및 실행 계획

  • COUNT(*)
SELECT COUNT(*) FROM count_test WHERE department = '개발';

위 쿼리를 실행하면 department = '개발'인 레코드를 반환한다.

위 쿼리를 실행하면 다음과 같이 99941개의 레코드를 반환한다.

실행 계획을 보면 type은 ref다. ref는 인덱스의 종류와 상관없이 equal(동등) 조건이 사용될 때 사용되는 접근 방법이다. 전체 쿼리에서 조건에 맞는 레코드만 접근하기에 빠르다. 그리고 Using index로 커버링 인덱스를 사용한다. rows는 173,828개 조회한다. 

실제 측정치를 보면 department = '개발'인 레코드를 커버링 인덱스로 99941개 반환한다. 그리고 이에 맞는 총개수를 계산하고 반환한다. 시간을 보면 65.8ms로 조건이 없을 때 보다 휠씬 빠른 속도로 조회한다. 이는 WHERE문인 조건을 먼저 실행하고 이 조건에 맞는 레코드를 조회하기에 조건이 없을때 보다 빠른 속도를 보인다.

 

  • COUNT(column) - INDEX
SELECT COUNT(department) FROM count_test WHERE department = '개발';

 

위 쿼리를 실행하면 department = '개발'인 레코드를 반환한다.

위 쿼리를 실행하면 다음과 같이 99941개의 레코드를 반환한다.

실행 계획을 보면 type은 ref다. ref는 인덱스의 종류와 상관없이 equal(동등) 조건이 사용될 때 사용되는 접근 방법이다. 전체 쿼리에서 조건에 맞는 레코드만 접근하기에 빠르다. 그리고 Using index로 커버링 인덱스를 사용한다. rows는 173,828개 조회한다. 위 COUNT(*)와 같은 실행 계획을 가진다

실제 측정치를 보면 department = '개발'인 레코드를 커버링 인덱스로 99941개 반환한다. 그리고 이에 맞는 총개수를 계산하고 반환한다. 69.2ms의 속도를 가진다. NOT NULL과 관계없이 INDEX로 커버링 인덱스 조회를 한다.

 

  • COUNT(column)
SELECT COUNT(salary) FROM count_test WHERE department = '개발';

위 쿼리를 실행하면 department = '개발'인 레코드를 반환한다.

위 쿼리를 실행하면 다음과 같이 99941개의 레코드를 반환한다.

실행 계획을 보면 type은 ref다. ref는 인덱스의 종류와 상관없이 equal(동등) 조건이 사용될 때 사용되는 접근 방법이다. 전체 쿼리에서 조건에 맞는 레코드만 접근하기에 빠르다. rows는 173,828개 조회한다. 

실제 측정치를 보면 department = '개발'인 레코드를 인덱스로 99941개 반환한다. 그리고 이에 맞는 총개수를 계산하고 반환한다. 504ms의 속도를 가진다.

 

위 코드와 다르게 커버링 인덱스가 아닌 인덱스를 사용하기에 504ms의 시간이 걸린다. 

 

결론

위 쿼리에서 보듯이 조건이 있든 없든 결국 COUNT(*)를 사용하면 다른 상황보다 가장 빠르게 답을 얻는 게 가능하다. 만약 특수한 조건을 가져서 특정 레코드의 개수를 가져와야 하는 사황이 아니라면 COUNT(*) 쓰는 게 가장 빠른 속도로 레코드 조회가 가능하다. 

 

 

참조

Real MySQL 8.0 

https://www.inflearn.com/course/real-mysql-part-1/dashboard

https://www.inflearn.com/course/real-mysql-part-2/dashboard

 

'DB' 카테고리의 다른 글

[MySQL] MySQL 최적화에 대한 공부(2/2)  (1) 2025.05.19
[MySQL] MySQL 아키텍처 구조에 대한 공부  (0) 2025.04.07

'DB' Related Articles

티스토리툴바