4. SQL문 튜닝 연습-(1)

1. 한 번에 너무 많은 데이터를 조회하는 SQL문 튜닝하기

(id, name, age 컬럼으로 구성된 테이블이며, 인덱스 설정 안하고, 100만 개의 더미 데이터가 존재하는 상황)

SELECT * FROM users;

100만개의 모든 데이터 조회시 대략 500~600ms 

SELECT * FROM users LIMIT 10;

이번엔 10개의 데이터 조회(LIMIT 10) 해보니 대략 20ms 

왜 데이터 개수 줄이는 게 중요할까?

실제 페이스북, 인스타그램 같은 서비스들을 보면,한 번에 모든 게시글 데이터를 불러오지 않는다.

• 사용자가 스크롤할 때마다 필요한 데이터만 조금씩 불러오는 방식을 쓴다.
• 다른 커뮤니티 서비스들에서도 페이지네이션을 적용해, 일부 데이터만 조회하려고 한다.

이는 이유가 분명하다.
→ 조회하는 데이터의 양이 많아질수록, 성능에 큰 영향을 주기 때문이다.

 

직관적으로 생각해보면 100만개의 데이터에서 1개의 데이터를 찾는 것보다 10,000개의 데이터를 찾는 게 오래 걸릴 수 밖에 없다.

⭐ [이것만은 꼭 기억해두자!]

• 한 번에 너무 많은 데이터를 조회하고 있지는 않은지 항상 점검해라.
• 필요하다면:
  • LIMIT 사용
  • WHERE 조건으로 범위 제한
  • 페이지네이션이나 무한 스크롤 방식 적용
• 조회하는 데이터의 개수 자체를 줄이는 방법이 성능 최적화의 가장 기본이자 중요한 방법이다.

 

2. 최근 3일 이내에 가입한 유저 조회하기

(id, name, department , created_at 컬럼으로 구성된 테이블이며, 100만 개의 더미 데이터가 존재하는 상황)

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, department, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

department는 저렇게 User 랜덤 7자리수 10으로 나눈 나머지값에 따라 넣는다.

1️⃣ 인덱스 없는 상태에서 조회

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

대략 290ms 정도 걸린다.

 

실행 계획 확인

EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

• type: ALL
• rows: 약 996,810

즉,풀 테이블 스캔(Full Table Scan)을 수행했다는 의미다.

→ 테이블의 전체 데이터를 처음부터 끝까지 전부 확인하며 조건에 맞는 데이터를 찾았다.
→ 매우 비효율적이다.

 

2️⃣ created_at 에 인덱스 설정 후 조회

CREATE INDEX idx_created_at ON users (created_at);

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

• 이번에는 대략 20ms 정도 소요된다.
• 약 10배 정도 성능이 향상된다.

실행 계획 확인

 

• type: range
• 인덱스 range 스캔(Index Range Scan)을 수행했음을 알 수 있다.
• 인덱스를 활용해 특정 범위 내에서만 데이터를 빠르게 스캔한 것이다.

⭐ [이것만은 꼭 기억해두자!]

• WHERE 절에서 부등호(>, <, <=, >=, =), IN, BETWEEN, LIKE와 같은 조건으로 사용되는 컬럼은
• 해당 컬럼에 인덱스를 설정했을 때 성능 향상 가능성이 높다.

특히 대용량 데이터 테이블에서는
조건 컬럼에 인덱스가 없는 경우 → 풀 테이블 스캔 발생 → 심각한 성능 저하로 이어질 수 있다.

 

3. Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)

대략 200ms 정도의 시간이 걸린다.

 

실행계획, 세부 실행계획 보면 

type이 ALL인걸 보니 풀 테이블 스캔을 했다. 비효율적이다. rows가 996,810인 걸 보니 전체 데이터를 대부분 액세스 했다는 걸 추측할 수 있다.

세부 실행계획은 전에 말했듯이 밑에서 위로 읽어나가야하고, 보면 풀테이블 스캔을 했고 이때 액세스한 데이터의 개수는

1e+6(10의 6제곱이라는 뜻이므로 100만)이고 152ms걸렸다라는 뜻이다.

여기서 액세스한 1,000,000개의 데이터 중 department 이 ‘Sales’와 created_at 이 3일 이내를 만족하는 데이터를 필터링해온다.(필터에서 걸린 시간은 204가 아닌 밑에 작업 152 빼야하니 204-152 = 52ms)

이렇게 해서 조건을 만족한 데이터의 개수는(rows) 127개이다.

➡️ 불필요하게 많은 데이터 읽기 → 비효율적

 

📌 인덱스 적용 실험

1️⃣ created_at 단일 인덱스 생성

CREATE INDEX idx_created_at ON users (created_at);

 

응답 속도	200ms → 20ms (10배 개선)
type	range (인덱스 레인지 스캔)
rows	1,043개 액세스

세부계획 보면

1. created_at >= 최근 3일 조건으로 1043개 데이터 조회 (인덱스 레인지 스캔)
2. 이 중 department = 'Sales' 조건 적용 → 100개 결과 반환

 2️⃣ department 단일 인덱스 생성

ALTER TABLE users DROP INDEX idx_created_at; # 기존 created_at 인덱스 삭제
CREATE INDEX idx_department ON users (department);

응답 속도	200ms → 140ms (개선폭 낮음)
type	ref (비고유 인덱스)
rows	191,000개 액세스

세부계획 보면

1. department = 'Sales' 조건으로 100,000개 데이터 조회
2. 그 중 created_at >= 최근 3일 필터링 → 114개 결과 반환

⚠️ 문제점:
department 에 Sales 부서 데이터가 많아서 데이터 액세스량이 여전히 큼 → 데이터 액세스를 많이 하면 할수록 시간이 오래 걸려 성능 개선폭 적음

 

3️⃣ department, created_at 둘 다 생성(단일로 둘다 생성)

CREATE INDEX idx_created_at ON users (created_at); # department는 2에서 이미 추가했으므로 생략

응답 속도	20ms (created_at 단일 인덱스와 동일)
type	created_at 인덱스만 사용
rows	1,043개 액세스

실행 계획 확인:
→ created_at 인덱스만 실제 사용됨. department 인덱스는 무시됨.

왜? => 옵티마이저가 created_at 인덱스 단독 사용이 더 효율적이라 판단.

그래서 세부 계획은 created_at 컬럼 인덱스만 생성한 결과와 동일하다. 

 

그러면 당연히 created_at 단독 인덱스로 해야한다. 이유는 department까지 하면 쓰기,수정작업시 당연 더 안좋으니

📌 1~3번 결론

created_at 컬럼 단독 인덱스가 가장 효과적!

 

없음	200ms	1,000,000개	ALL (풀 스캔)	전체 데이터 모두 읽음
created_at	20ms	1,043개	range (레인지 스캔)	최근 3일 필터로 데이터 액세스 최소화
department	140ms	191,000개	ref (비고유 인덱스)	Sales 데이터 많아 성능 개선 폭 적음
department + created_at	20ms	1,043개	created_at만 사용	옵티마이저가 created_at 인덱스만 사용함. department 인덱스는 무의미

4️⃣(created_at, department) 멀티 컬럼 인덱스 생성

ALTER TABLE users DROP INDEX idx_created_at;
ALTER TABLE users DROP INDEX idx_department;
CREATE INDEX idx_created_at_department ON users (created_at, department);

대략 30ms 정도 걸린다.

순서 바꿔서 (department, created_at) 멀티 컬럼 인덱스 생성후 해봐도 대략 30ms 걸린다

 

위 2가지 멀티 컬럼 인덱스를 활용해서 설정해봤지만, created_at 인덱스만 걸었을 때와 크게 성능 차이가 없다. 이런 경우에는 굳이 멀티 컬럼 인덱스를 사용하지 않고 단일 컬럼에만 인덱스를 적용시키는 게 낫다. 

⭐ [이것만은 꼭 기억해두자!]

• 단일 컬럼에 설정하는 일반 인덱스를 설정했을 때와 멀티 컬럼 인덱스를 설정했을 때의 성능 차이가 별로 나지 않는다면, 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.