N + 1 쿼리 MySQL로 직접 눈으로 확인해보자

N+1 문제란?

N+1 문제는 ORM을 사용할 때 자주 발생하는 성능 저하 현상이다.

한 번의 쿼리로 여러 데이터를 가져온 뒤, 각 데이터마다 개별 쿼리를 추가 실행하면서 

총 N+1번의 쿼리가 발생하는 상황을 말한다.

• N = 조회된 레코드 수 (행의 개수)
• +1 = 이를 조회하기 위한 최초의 메인 쿼리

즉, 초기 쿼리 1번 + 레코드 개수 N번 = 총 N + 1번의 쿼리가 실행된다.

 

*** ORM: Object-Relational Mapping(객체-관계 매핑)의 약자

객체와 관계형 데이터베이스의 데이터를 자동으로 매핑해주는 것

 

MySQL에서 직접 데이터를 넣고 실행해 보면서, 실제로 쿼리 횟수가 얼마나 늘어나는지 수치로 확인해보았다.

---

간단하게 데이터 3개로 테스트 하기

1. 스키마 생성

CREATE TABLE authors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE blogs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

 

2. 데이터 넣기

INSERT INTO authors (name) VALUES ('Kim'), ('Lee'), ('Park');

INSERT INTO blogs (title, author_id) VALUES
('Post A', 1),
('Post B', 2),
('Post C', 3);

 

3. N + 1 발생 상황 재현

-- (1) 블로그 전체 조회 → 쿼리 1번
SELECT * FROM blogs;

-- (2) 각 블로그 작성자 개별 조회 → 블로그 수(N)만큼 추가 쿼리
SELECT * FROM authors WHERE id = 1;
SELECT * FROM authors WHERE id = 2;
SELECT * FROM authors WHERE id = 3;

 

이렇게 하면 블로그가 3개일 때, 실제로는 총 4번(N + 1번) 쿼리가 실행된다.

• 1(blogs) + 3(authors) → 총 4번 실행

 

4. JOIN으로 최적화

 

이제 쿼리 1번으로 동일한 데이터를 가져올 수 있다.

 

하지만 데이터가 3개밖에 없어서 실감이 잘 나지 않았다.
다량의 데이터가 있을 경우 더 효과적일 것 같아 데이터 규모를 늘려서 테스트해봤다.

---

데이터 수를 키워서 테스트 하기

1. 데이터 넣기 (authors 1,000 / blogs 10,000)

스키마는 앞에서 만들어 놓았으므로 스킵한다.

더미 데이터를 삽입한다. 

USE n_plus_one;

-- authors 1,000명
INSERT INTO authors(name)
SELECT CONCAT('Author ', n) AS name
FROM (
  SELECT @row:=@row+1 AS n
  FROM information_schema.columns, (SELECT @row:=0) r
  LIMIT 1000
) x;

-- blogs 10,000개 (author 균등 분배)
INSERT INTO blogs(title, author_id)
SELECT CONCAT('Post ', n) AS title,
       1 + (n % 1000)     AS author_id
FROM (
  SELECT @row2:=@row2+1 AS n
  FROM information_schema.columns c1
  CROSS JOIN information_schema.columns c2
  , (SELECT @row2:=0) r
  LIMIT 10000
) y;

 

1) 작가(authors) 1,000명 생성

• CONCAT('Author ', n)으로 "Author1", "Author2", ... "Author1000" 형태의 이름 생성
• @row 변수를 1씩 증가시켜서 순번 만들기
• 시스템 테이블인 information_schema.columns를 활용해 충분한 행 수 확보

2) 블로그 글(blogs) 10,000개 생성

• 1 + (n % 1000)으로 균등분배를 하는데,
  • n % 1000은 0~999를 반복하므로 1을 더해서 1~1000 범위로 만든다.
  • 그래서 각 작가당 정확히 10개씩 글이 할당된다. 
• CROSS JOIN으로 충분한 행을 생성한다.
  • information_schema.columns c1 CROSS JOIN information_schema.columns c2
• @row2 변수로 1, 2, 3, 4 ... 순번을 매긴다.
• 처음 10,000개만 가져온다.
  • LIMIT 10000  ( n = 1, 2, 3, ... 10000 )

3) 결론

• n = 1 → 1 + (1 % 1000) = 2 → author_id = 2
• n = 2 → 1 + (2 % 1000) = 3 → author_id = 3
• ...
• n = 1000 → 1 + (1000 % 1000) = 1 → author_id = 1 (다시 처음으로!)

 

SELECT 문으로 조회해보면 현재 데이터 수를 알 수 있다. 

아까 데이터 3개를 미리 만들어놓았기 때문에, 각각 1003, 10003개임을 알 수 있다.

2. 쿼리 실행 횟수 측정하기

1) 결과 출력하는 쿼리

FLUSH STATUS;

DELIMITER //
CREATE PROCEDURE n_plus_one_test(IN p_limit INT)
BEGIN
  DECLARE v_author_id INT;
  DECLARE done INT DEFAULT 0;

  DECLARE cur CURSOR FOR
    SELECT author_id FROM blogs LIMIT p_limit;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_author_id;
    IF done = 1 THEN LEAVE read_loop; END IF;

    -- N번 개별 조회 (의도적으로 비효율)
    SELECT id, name FROM authors WHERE id = v_author_id;
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

CALL n_plus_one_test(1000);

SHOW SESSION STATUS
WHERE Variable_name IN ('Questions','Com_select');

 

실행하다가 멈췄다. 찾아보니 MySQL 워크벤치의 안전모드에 걸려서 그렇다고 한다.

 

 

그리고 어떤 SQL문을 실행하든 에러가 났다.

Error: DBMS Connection is not available이라고 뜬다.

상단 메뉴에서 Server 클릭 후 Reconnect 를 누른다.

 

 

2) 결과 출력 방지 쿼리

USE n_plus_one;
DROP PROCEDURE IF EXISTS n_plus_one_test;

DELIMITER //

CREATE PROCEDURE n_plus_one_test(IN p_limit INT)
BEGIN
  DECLARE v_author_id INT;
  DECLARE done BOOL DEFAULT FALSE;

  -- 결과 탭에 찍히지 않도록 변수로 흡수
  DECLARE sink_id INT;
  DECLARE sink_name VARCHAR(100);

  DECLARE cur CURSOR FOR
    SELECT author_id FROM blogs LIMIT p_limit;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_author_id;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SELECT id, name
      INTO sink_id, sink_name
    FROM authors
    WHERE id = v_author_id;
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;

 

 

중요한 건 아래처럼 쓰면 결과가 Result Grid에 표기가 되어

탭이 렉걸린 것처럼 마구마구 생성되므로,

SELECT id, name FROM authors WHERE id = v_author_id;

 

이런 식으로 변수를 선언한 뒤, 안에 저장되도록 해야한다.

DECLARE sink_id INT;
DECLARE sink_name VARCHAR(100);

SELECT id, name
  INTO sink_id, sink_name  -- 여기에 저장됨

 

3. 결과 확인

1) N + 1 발생 상황

n_plus_one_test라는 프로시저에 파라미터 1000을 넣는다.

 

• blogs 테이블에서 처음 1,000개 블로그의 author_id 조회
• 각각에 대해 개별적으로 authors 테이블에서 작가 정보 조회
• 총 1,001번의 쿼리 실행 (1 + 1,000)

 

FLUSH STATUS;
CALL n_plus_one_test(1000);

SHOW SESSION STATUS
WHERE Variable_name IN ('Questions','Com_select');

 

Questions

• MySQL 서버가 받은 총 명령문(statement) 수
  • 1번: SELECT author_id FROM blogs LIMIT 1000 
  • 1,000번: SELECT id, name FROM authors WHERE id = ? (개별 조회)

 

Com_select

• SELECT 명령문만 실행된 횟수
• Questions의 부분집합 

→ N+1 발생 상황에서는 Questions: 2, Com_select: 1001로 나왔다.

 

2) 정상적인 JOIN 쿼리 상황

 

FLUSH STATUS;

SELECT b.id, b.title, a.id AS author_id, a.name AS author_name
FROM blogs b
JOIN authors a ON a.id = b.author_id
LIMIT 1000;

SHOW SESSION STATUS
WHERE Variable_name IN ('Questions','Com_select');

 

→ 정상 JOIN 쿼리 상황에서는 Questions: 2, Com_select: 1로 나왔다.

 

3) 결론 분석

• Question: 2 (둘 다 동일)

N + 1 쿼리	정상 JOIN 쿼리
CALL n_plus_one_test(1000) 또는 SELECT ... JOIN ... ← 1개 SHOW SESSION STATUS WHERE ... ← 1개	SELECT b.id, a.id, a.name FROM blogs b JOIN authors a... ← 1개 SHOW SESSION STATUS WHERE ... ← 1개

 

• Com_select : 1 vs 1001

N + 1 쿼리	정상 JOIN 쿼리
SELECT author_id FROM blogs LIMIT 1000 ← 1번 SELECT id, name FROM authors WHERE id = ? ← 1000번 (개별 조회)	SELECT b.id, a.id, a.name FROM blogs b JOIN authors a... ← 1번