JOIN Query에서 성능을 최적화 하는 방법을 알아보겠습니다.

Join Query가 MySQL에서 어떻게 동작 하는지를 이해해야 어떻게 INDEX를 만들어야할지를 알 수 있으니 우선 깊게 이해해보자.

테스트 데이터 생성하기

테이블 생성

관광지와 방문한 장소 테이블을 만들어보자.

-- 관광지 테이블
CREATE TABLE tourist_spot (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    address TEXT,
    lat DOUBLE NOT NULL,
    lng DOUBLE NOT NULL
);

-- 방문한 관광지 
CREATE TABLE visited_place (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tourist_spot_id BIGINT NOT NULL,
    visited_at DATETIME NOT NULL,
    description TEXT,
    photo_url VARCHAR(512),

    CONSTRAINT fk_tourist_spot
        FOREIGN KEY (tourist_spot_id)
        REFERENCES tourist_spot(id)
        ON DELETE SET NULL
);

내가 방문한 관광지 와 관광지 가 1:N의 관계로 이루어져 있다.

데이터예시

관광지 데이터

INSERT INTO tourist_spot (name, category, address, lat, lng) VALUES 
('남산타워', '전망대', '서울특별시 용산구 남산공원길 105', 37.5512, 126.9882),
('해운대 해수욕장', '해변', '부산광역시 해운대구 우동', 35.1587, 129.1604),
('경복궁', '고궁', '서울특별시 종로구 사직로 161', 37.5796, 126.9770);

방문한 관광지 데이터

INSERT INTO visited_place (tourist_spot_id, visited_at, description, photo_url) VALUES 
(1, '2025-06-01 18:30:00', '서울 야경 끝내줬다. 케이블카도 탔다.', 'https://example.com/photo1.jpg'),
(2, '2025-06-15 13:20:00', '여름에 해운대는 역시 미쳤다. 사람 진짜 많음.', 'https://example.com/photo2.jpg'),
(NULL, '2025-06-20 14:00:00', '지나가다 발견한 뷰 좋은 루프탑 카페. 지도에도 안 나옴.', 'https://example.com/photo3.jpg'),
(3, '2025-06-25 10:00:00', '경복궁에서 한복 입고 찍은 날! 외국인도 많았다.', 'https://example.com/photo4.jpg');

더미 데이터 생성

성능 측정을 위한 더미데이터 100만개를 생성해보자.

WITH RECURSIVE 구문은 SQL버전 반복문이라고 생각하면 된다.

WITH RECURSIVE [TABLE] AS (
		SELECT 1 AS n # Anchor member  ( 재귀 첫 루프에만 실행)  
		UNION ALL   # 빠른 생성을 위해 UNION ALL
		SELECT n + 1 FROM [TABLE]  # 반복적으로 실행시킬 부분 (재귀)
		WHERE n < 3  # 종료 조건
)
# 이러면 n [1,2,3] 이 생성된다.

# 최대 재귀 깊이 설정
SET SESSION cte_max_recursion_depth = 1000000;

# 관광지 데이터
INSERT INTO tourist_spot (name, category, address, lat, lng)
WITH RECURSIVE numbers AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100000
)
SELECT
    CONCAT("관광지", LPAD(n,7,'0')),
    CONCAT("카테고리", LPAD(n,7,'0')),
    CONCAT("주소", LPAD(n,7,'0')),
    33 + RAND() * 10,  --  한국 위도
    124 + RAND() * 8   --  한국 경도
FROM numbers;

# 방문한 광광지 더미 데이터
INSERT INTO visited_place (tourist_spot_id, visited_at, description, photo_url)
WITH RECURSIVE number AS (
		SELECT 1 as n
		UNION ALL
		SELECT n + 1 FROM number WHERE n < 1000000
)
SELECT
    (n % 100000) + 1 AS tourist_spot_id,  -- tourist_spot_id는 1~100000 사이
    DATE_ADD('2025-01-01', INTERVAL (n % 365) DAY) AS visited_at,  # 일 단위로 증가
    CONCAT('자동 생성 방문기록 ', n) AS description,
    CONCAT('https://example.com/photo', n, '.jpg') AS photo_url
FROM number;
		

---

성능 측정 및 개선

다양한 INDEX를 사용하면서 최적의 쿼리를 찾아보겠습니다.

성능 확인

성능 확인을 위한 세팅

SET profiling=1;
SHOW PROFILES; # duration (쿼리 실행 시간 확인)

성능 측정할 쿼리

SELECT *
        FROM visited_place v
        JOIN tourist_spot t ON v.tourist_spot_id = t.id
        WHERE v.vistied_at = '2025-07-01'
        AND t.lat BETWEEN 34 AND 34.5
        AND t.lng BETWEEN 127 AND 128;

이제 좌표 범위 lat : 34~34.5, lng : 127 ~ 128 이고

날짜가 2025-07-01’ 인 데이터를 검색해보겠습니다.

1. 기본 인덱스만 존재할 경우

현재 인덱스

• tourist_spots
  • PRIMARY (id)
• visited_place
  • PRIMARY (id)
  • fk_tourist_spot (외래키)

어떻게 동작 하는가?

위와 같은 쿼리를 실행시켰을 때, 최적의 실행 경로를 예측해보면

JOIN 할 때, fk_tourist_spot과 PRIMARY를 사용할 수 있겠다. 이러면 해당 ID를 가지고 곧바로 관광지 데이터를 찾아낼 수 있으므로 굉장히 빠른 속도로 JOIN을 할 수 있을 것이다.

하지만, 2가지 조건의 경우에는 추출한 테이블을 FULL SCAN 하면서 데이터을 찾아야한다.

• 날짜가 2025-07-01 인 데이터
• 좌표 범위가 lat : 34~34.5, lng : 127 ~ 128 인 데이터

EXPLAIN ANALYZ 한 코드

-> Nested loop inner join (cost=16429 rows=1121) (actual time=5.85..1684 rows=47 loops=1)\n

1. -> Filter: ((t.lat between 34 and 34.5) and (t.lng between 127 and 130)) (cost=10290 rows=1228) (actual time=0.716..274 rows=1976 loops=1)\n

   -> Table scan on t (cost=10290 rows=99520) (actual time=0.694..239 rows=100000 loops=1)\n

1. -> Filter: (v.visited_at = TIMESTAMP\'2025-07-01 00:00:00\') (cost=4.09 rows=0.912) (actual time=0.707..0.712 rows=0.0238 loops=1976)\n

   -> Index lookup on v using fk_tourist_spot (tourist_spot_id=t.id) (cost=4.09 rows=9.12) (actual time=0.188..0.707 rows=10 loops=1976)\n

순서를 설명하면

1. tourist_spot 의 FULLSCAN 으로 좌표 범위 필터링
2. tourist_spot을 훑으면서 id를 읽고, 해당 id에 맞는 visited_place를 idx_tourist_spot 인덱스를 통해 필터링
3. 필터링된 데이터를 날짜 기준으로 추가 필터링
4. 최종 데이터 JOIN

참고용 쿼리플랜

table	type	key	ref	rows	filtered	Extra
t	ALL	(null)	(null)	99520	1.23	Using where
v	ref	fk_tourist_spot	idx_test.t.id	9	10.00	Using where

FULLSCAN이 있기때문에 성능이 굉장히 구립니다. 똥이에요.

실제 실행 속도 약 1700 ms 정도로 나오는 것 같죠?

---

2. Range 타입으로 변경하기

좌표 데이터를 필터링하는데 FULL SCAN을 하였으니, 이를 개선해보겠습니다.

좌표를 빠르게 찾을 수 있게 lat, lng 전용 INDEX를 추가해보도록 하겠습니다.

CRAETE INDEX idx_lat_lng ON tourist_spot (lat asc, lng asc);

SELECT *
        FROM visited_place v
        JOIN tourist_spot t FORCE INDEX (idx_lat_lng)
        ON v.tourist_spot_id = t.id
        WHERE v.visited_at = '2025-07-01'
        AND t.lat BETWEEN 34 AND 34.5
        AND t.lng BETWEEN 127 AND 130;

예측해보자

이전 방법에서 좌표를 필터링할때, FULL SCAN 대신 INDEX를 통한 RANGE로 개선하면 되겠죠?

'-> Nested loop inner join  (cost=9183 rows=4546) (actual time=32.8..1301 rows=47 loops=1)\n    -> Index range scan on t using idx_lat_lng over (34 <= lat <= 34.5 AND 127 <= lng <= 130), with index condition: ((t.lat between 34 and 34.5) and (t.lng between 127 and 130))  (cost=2808 rows=4983) (actual time=0.0444..76 rows=1976 loops=1)\n    -> Filter: (v.visited_at = TIMESTAMP\'2025-07-01 00:00:00\')  (cost=3.31 rows=0.912) (actual time=0.616..0.619 rows=0.0238 loops=1976)\n        -> Index lookup on v using fk_tourist_spot (tourist_spot_id=t.id)  (cost=3.31 rows=9.12) (actual time=0.187..0.614 rows=10 loops=1976)\n'
# 전부 펼치기 귀찮아서 참고용으로 읽어보세요

순서를 요약하면 다음과 같아요.

1. tourist_spot 의 INDEX를 통한 RANGE SCAN으로 좌표 범위 필터링
2. tourist_spot을 훑으면서 id를 읽고, 해당 id에 맞는 visited_place를 idx_tourist_spot 인덱스를 통해 필터링
3. 필터링된 데이터를 날짜 기준으로 추가 필터링
4. 최종 데이터 JOIN

EXPLAN

table	type	key	rows	filtered	Extra
t	range	idx_lat_lng	4983	11.11	Using index condition
v	ref	fk_tourist_spot	9	10.00	Using where

range 타입으로 개선하였지만, 실제 속도는 여전히 구립니다…

한 대충 1500ms 정도 나오겠네요. 200ms 개선하긴 했네요.

근데 DB에서만 1.5초면 실제 서비스에서 사용자가 이미 웹사이트를 나가기 충분한 시간인듯 합니다.

---

3. 복합 인덱스 - JOIN할 때, 날짜 필터링까지 동시에 해보자.

이전 과정에서 더 개선할 점이 뭐가 있을까요? 다음은 이전 순서입니다.

1. tourist_spot 의 INDEX를 통한 RANGE SCAN으로 좌표 범위 필터링
2. tourist_spot을 훑으면서 id를 읽고, 해당 id에 맞는 visited_place를 idx_tourist_spot 인덱스를 통해 필터링
3. 필터링된 데이터를 날짜 기준으로 추가 필터링
4. 최종 데이터 JOIN

여기 보면, 필터링된 데이터를 다시 날짜 기준으로 추가적으로 필터링 시킵니다. 필터링된 데이터를 전부 FULLSCAN 하면서 다시 필터링 해야합니다.

CREATE INDEX idx_touristspot_visitedat ON visited_place (tourist_spot_id, visited_at asc);

SELECT *
        FROM visited_place v FORCE INDEX (idx_touristspot_visitedat)
        JOIN tourist_spot t FORCE INDEX (idx_lat_lng)
        ON v.tourist_spot_id = t.id
        WHERE v.visited_at = '2025-07-01'
        AND t.lat BETWEEN 34 AND 34.5
        AND t.lng BETWEEN 127 AND 130;

잡다한거 생략하고 바로 순서로 넘어가겠습니다.

-> Nested loop inner join  (cost=3259 rows=4983) (actual time=0.747..15.9 rows=47 loops=1)\n    -> Index range scan on t using idx_lat_lng over (34 <= lat <= 34.5 AND 127 <= lng <= 130), with index condition: ((t.lat between 34 and 34.5) and (t.lng between 127 and 130))  (cost=2453 rows=4983) (actual time=0.0622..6.97 rows=1976 loops=1)\n    -> Index lookup on v using idx_touristspot_visitedat (tourist_spot_id=t.id, visited_at=TIMESTAMP\'2025-07-01 00:00:00\')  (cost=0.556 rows=1) (actual time=0.00437..0.00439 rows=0.0238 loops=1976)\n

작동 순서

1. tourist_spot 의 INDEX를 통한 RANGE SCAN으로 좌표 범위 필터링
2. tourist_spot을 훑으면서 id를 읽고, 해당 id에 맞는 visited_place를 인덱스를 읽으면서 날짜 를 동시에 필터링
3. 최종 데이터 JOIN

성능

약 17 ms 정도 소요되었습니다.

성능이 비트코인마냥 증가했는데, 이 원리를 이해하려면 MySQL에서 InnoDB가 어떻게 INDEX를 타고 데이터를 가져오는 지를 이해해야 합니다.

이전 방식(1,2번) 은 visited_place 데이터를 가져올 때,

FK_INDEX → PRIMARY_INDEX → 날짜 필터링 → JOIN

이렇게 2번에 걸쳐서 INDEX를 타고 실제 데이터를 가져와야 했습니다.

하지만 현재 방식은

idx_touristspot_visitedat → JOIN 으로 바로 가져올 수 있는 것입니다.

---

4. 서브쿼리 조인

여기에서 더 줄일 수 있습니다.

바로 서브쿼리 조인을 이용하는 것입니다.

SELECT  *
        FROM visited_place v FORCE INDEX (idx_touristspot_visitedat)
        JOIN (
			SELECT *
            FROM tourist_spot FORCE INDEX (idx_lat_lng)
			WHERE lat BETWEEN 34 AND 34.5
			AND lng BETWEEN 127 AND 130
        ) AS t
        ON v.tourist_spot_id = t.id
        WHERE v.visited_at = '2025-07-01';

-> Nested loop inner join  (cost=3259 rows=4983) (actual time=0.426..13.9 rows=47 loops=1)\n    -> Index range scan on tourist_spot using idx_lat_lng over (34 <= lat <= 34.5 AND 127 <= lng <= 130), with index condition: ((tourist_spot.lat between 34 and 34.5) and (tourist_spot.lng between 127 and 130))  (cost=2453 rows=4983) (actual time=0.0281..6.26 rows=1976 loops=1)\n    -> Index lookup on v using idx_touristspot_visitedat (tourist_spot_id=tourist_spot.id, visited_at=TIMESTAMP\'2025-07-01 00:00:00\')  (cost=0.556 rows=1) (actual tie=0.00368..0.0037 rows=0.0238 loops=1976)\n

성능

약 13ms 의 시간이 소요됩니다.

이렇게 까지 해야하나 싶을 정도로 짧을 시간입니다.

왜 이럴까?

옵티마이저가 쿼리를 어떤 과정으로 실행해야할지를 생각하지 않아도 되기 때문입니다.

• 3번 쿼리는 옵티마이저 입장에서 “어떤 테이블을 먼저 스캔할까”, “lat/lng 조건은 어디서 적용해야 할까?” 등 여러 판단을 해야합니다. → 이 과정에서 비용 소모
  
• 하지만 쿼리 B는 tourist_spot을 먼저 필터링한 서브쿼리 형태라서 옵티마이저가 “이걸 먼저 돌리면 돼”라고 확정된 상태로 시작합니다. → 플랜 계산 시간이 줄고, 실행 계획이 단순
  

그래서 큰 시간적인 차이는 발생하지 않습니다.

---

5. 커버링 인덱스

여기에서 더 읽기 속도를 향상 시킬 수 있습니다.

읽기 속도가 중요할 때 많이 사용하지만, 쓰기 속도가 느려지기 때문에 신중한 사용이 필요합니다.

CREATE INDEX idx_lat_lng_id ON tourist_spot(lat asc, lng asc, id);

SELECT  *
        FROM visited_place v FORCE INDEX (idx_touristspot_visitedat)
        JOIN (
			SELECT id, lat, lng
            FROM tourist_spot FORCE INDEX (idx_lat_lng_id)
			WHERE lat BETWEEN 34 AND 34.5
			AND lng BETWEEN 127 AND 130
        ) AS t
        ON v.tourist_spot_id = t.id
        WHERE v.visited_at = '2025-07-01';

성능

소요시간이 약 10ms로 더욱 개선된 것을 볼 수 있습니다.

여기에서는 이전과 크게 차이가 없지만, 데이터가 더욱 많아지고 조건이 복잡해지면 차이가 많이 생깁니다.

이유

MySQL의 InnoDB에서는 생성한 INDEX에 데이터의 주소를 저장하지 않고 해당 데이터의 PRIMARY ID를 저장해놓습니다. 따라서 실제 데이터를 가져오려면 PRIMARY INDEX 의 leaf 노드에 있는 데이터의 저장 위치를 읽고, DISK에서 해당 위치의 데이터를 가져와야합니다. 즉, INDEX를 2번 읽어야 하는 것입니다.

하지만 커버링 인덱스를 사용하면, INDEX의 leaf 노드에 필요한 모든 데이터가 저장되어있기 때문에 PRIAMRY INDEX 를 찾아가 검색할 필요가 없습니다. INDEX를 1번만 타도 되는 것입니다.

---

마무리

이렇게 쿼리 읽기 성능을 개선하는 방법을 알아보았습니다.

읽기 속도가 빠르다고 항상 좋은 것은 아닙니다.

쓰기 속도가 느려진다는 점, 용량이 커진다는 점 등의 문제가 발생합니다. 여기서 더 나아가면 JAVA 등의 실제 코드로 옮길 때, 유지보수 및 가독성이 좋냐? 도 생각할 필요가 있습니다.

따라서 얼마나 많이 읽느냐, 얼마나 많이 쓰느냐 등을 모두 고려하여 적절한 쿼리를 선택합시다.