좋아요 테이블, 어떻게 구성하면 좋을까? PK 2개 vs. PK 1개(feat.FK 2개)

 

위 두 테이블은 같은 ‘좋아요’ 테이블이다.

다른 점은 feedback 테이블은 복합키로 구성되어 있고, rec_book_like 테이블은 그렇지 않다는 것. 이 둘은 각기 다른 pk와 fk, 그리고 인덱스를 지닌다.

 

[feedback 테이블]

• PK는 book_id와 child_id 모두를 합한 형태. 예를 들면, (1,2) 형태로 키를 지닌다. → mysql에서는 primary 키가 설정된 순서대로 컬럼을 정렬하기 때문에, book_id를 기준으로 먼저 정렬한 다음, child_id 순으로 정렬된다.
• FK는 book_id와 child_id를 지닌다.
• index는 (book_id, child_id)와 (child_id)를 지닌다.
• book_id에는 index가 걸려 있지 않음을 알 수 있다. → 이는(첫 번째 정렬 순서인) book_id가 PK가 되며, 별도의 인덱스를 만들지 않아도 자동으로 인덱스가 적용된다는 것을 의미한다.

[rec_book_like 테이블]

• PK는 rec_book_id를 지닌다.
• FK는 book_id와 child_id를 지닌다.
• index는 rec_book_id, child_id, book_id 세 개에 모두 걸려 있다. → 따라서 이들을 단일 조회하는 성능은 우수할 것으로 판단된다. → 하지만 (child_id, book_id)를 결합하여 조회를 할 때는 상대적으로 인덱스가 최적화되어 있지 않아서 성능이 떨어진 것이다.

 

실험 조건

• feedback 테이블 : 1,000,000건 (백만 건)
• rec_book_like 테이블: 1,000,000건 (백만 건)

먼저 백만건을 프로시져로 삽입한다.

-- 기존 프로시저가 존재하면 삭제
DROP PROCEDURE IF EXISTS InsertDummyDataIntoMember;
DROP PROCEDURE IF EXISTS InsertDummyDataIntoChild;
DROP PROCEDURE IF EXISTS InsertDummyDataIntoBook;
DROP PROCEDURE IF EXISTS InsertDummyDataIntoFeedback;
DROP PROCEDURE IF EXISTS InsertDummyDataIntoRecBook;
DROP PROCEDURE IF EXISTS InsertDummyDataIntoRecBookLike;

-- 프로시저 시작
DELIMITER $$

-- member 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoMember()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10000 DO
            INSERT INTO member (created_at, modified_at, email, member_name, password, phone, provider, provider_id, role_code)
            VALUES (
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY,
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY,
                       CONCAT('user', i, '@example.com'),
                       CONCAT('MemberName', i),
                       'password',
                       CONCAT('010-', LPAD(FLOOR(RAND() * 10000), 4, '0'), '-', LPAD(FLOOR(RAND() * 10000), 4, '0')),
                       'provider',
                       CONCAT('provider', i),
                       'ROLE_USER'
                   );
            SET i = i + 1;
        END WHILE;
END$$

-- child 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoChild()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10000 DO
            INSERT INTO child (age, birthdate, gender, profile_image, child_name, member_id)
            VALUES (
                       FLOOR(1 + (RAND() * 10)),
                       DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3000) DAY),
                       IF(RAND() > 0.5, 'M', 'F'),
                       CONCAT('profile_image_', i, '.jpg'),
                       CONCAT('ChildName', i),
                       FLOOR(1 + (RAND() * 10000)) -- member_id는 1~100 사이에서 랜덤 선택
                   );
            SET i = i + 1;
        END WHILE;
END$$

-- book 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoBook()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10000 DO
            INSERT INTO book (created_at, modified_at, author, genre_code, bookcover_image, published_at, publisher, rec_age, summary, title)
            VALUES (
                       NOW() - INTERVAL FLOOR(RAND() * 365) DAY,
                       NOW() - INTERVAL FLOOR(RAND() * 365) DAY,
                       CONCAT('Author', i),
                       CONCAT('Genre', FLOOR(1 + (RAND() * 10))),
                       CONCAT('cover_image_', i, '.jpg'),
                       DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 10000) DAY),
                       CONCAT('Publisher', FLOOR(1 + (RAND() * 100))),
                       CONCAT(FLOOR(5 + (RAND() * 10)), '세 이상'),
                       CONCAT('This is the summary for book ', i),
                       CONCAT('Title', i)
                   );
            SET i = i + 1;
        END WHILE;
END$$

-- feedback 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoFeedback()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_child_id INT;
    DECLARE random_book_id INT;

    -- feedback 테이블에 더미 데이터 삽입
    WHILE i <= 1000000 DO
            SET random_child_id = FLOOR(1 + (RAND() * 10000)); 
            SET random_book_id = FLOOR(1 + (RAND() * 10000)); 

            -- 중복 방지를 위한 INSERT IGNORE 사용
            INSERT IGNORE INTO feedback (child_id, book_id, like_status, hate_status, created_at)
            VALUES (
                       random_child_id,
                       random_book_id,
                       IF(RAND() > 0.5, 1, 0),
                       IF(RAND() > 0.5, 1, 0),
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY
                   );
            SET i = i + 1;
        END WHILE;
END$$

-- rec_book 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoRecBook()
BEGIN
    DECLARE i INT DEFAULT 1;

    -- rec_book 테이블에 더미 데이터 삽입
    WHILE i <= 1000000 DO
            INSERT INTO rec_book (created_at, modified_at, book_id, child_id)
            VALUES (
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY,
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY,
                       FLOOR(1 + (RAND() * 10000)), 
                       FLOOR(1 + (RAND() * 10000)) 
                   );
            SET i = i + 1;
        END WHILE;
END$$

-- rec_book_like 테이블에 더미 데이터 삽입
CREATE PROCEDURE InsertDummyDataIntoRecBookLike()
BEGIN
    DECLARE i INT DEFAULT 10001;

    -- rec_book_like 테이블에 10,000개의 데이터 삽입
    WHILE i <= 100000 DO
            INSERT INTO rec_book_like (created_at, child_id, book_id)
            VALUES (
                       NOW() - INTERVAL FLOOR(RAND() * 30) DAY, 
                       FLOOR(1 + (RAND() * 10000)),
                       FLOOR(1 + (RAND() * 10000)) 
                   );
            SET i = i + 1;
        END WHILE;
END$$

DELIMITER ;

-- 프로시저 실행
CALL InsertDummyDataIntoMember();
CALL InsertDummyDataIntoChild();
CALL InsertDummyDataIntoBook();
CALL InsertDummyDataIntoFeedback();
CALL InsertDummyDataIntoRecBook();
CALL InsertDummyDataIntoRecBookLike();

 

소요된 시간

복합키를 쓰는 feedback 테이블은 9분이, 하나의 PK를 쓰는 RecBookLike는 15분이 소요되었다.

(RecBookLike에 데이터가 잘못 들어갔다는 것을 알고 수정을 진행. 이후 따로 프로시져를 수행했는데, 나의 로컬 상황에 영향을 받았는지 하나의 PK를 쓰는 RecBookLike가 더 오랜 소요 시간이 걸렸다. 이 부분은 좀더 확인해 봐야 할 부분.)

 

이제 각각의 실행 계획을 살펴보자.

쿼리는 다음과 같이 날리고 조회 성능을 비교해본다.

select * from feedback where book_id = #{임의의 숫자};
select * from feedback where child_id = #{임의의 숫자};
select * from feedback where book_id = #{임의의 숫자} and child_id = #{임의의 숫자};

select * from rec_book_like where book_id = #{임의의 숫자};
select * from rec_book_like where child_id = #{임의의 숫자};
select * from rec_book_like where book_id = #{임의의 숫자} and child_id = #{임의의 숫자};

 

먼저, book_id를 조회하는 쿼리

• 위의 feedback 테이블에는 book_id가 PK로 걸려 있으며, rec_book_like에는 FK 인덱스로 걸려 있는데, 확실히 PK를 이용한 조회가 비용이 더 낮다.

[바용 차이의 이유]

• Feedback 테이블에서 book_id는 PK로 설정되어 있으므로, PK에 대한 인덱스가 자동으로 생성다. 그러나 단순히 인덱스가 생성되는 것뿐만 아니라 데이터가 PK를 기준으로 물리적으로 정렬되어 있어, book_id 기준 조회가 아주 잘 된다.
• rec_book_like 테이블에서는 book_id가 PK가 아니며, 해당 열의 FK 인덱스는 물리적 정렬을 보장하지 않기 때문에 조회에 더 많은 비용이 든다.

[비용 차이가 발생시킬 문제]

• 비용이 12에서 37로 증가했다는 것은, 쿼리가 3배 이상 더 많은 자원을 사용한다는 의미. 만약 이 쿼리가 빈번하게 호출되는 쿼리라면, 차이가 누적되며 많은 성능 차이를 일으킬 것이다.
• 좋아요 같은 경우, 해당 책에 몇 개의 좋아요가 붙었는지 보여주는 경우가 많다. (이 부분은 추후에 '좋아요의 수'를 저장하는 테이블을 따로 만든다는 결론에 도달하지만 일단 이렇게 적는다) 따라서 복합키 테이블을 쓰는 게 더 효율적이며, 중복으로 좋아요를 하는 상황도 자연스레 방지할 수 있다.

 

child_id를 조회하는 쿼리

 

 

[비용 차이의 이유]

• 두 테이블 모두 child_id에 대해 인덱스가 걸려 있지만, feedback 테이블의 경우 복합 인덱스의 첫 번째 키가 book_id이기 때문에(book_id 기준으로 정렬되어 있음 따라서) child_id로 단독 조회할 때 추가 비용이 발생한다. (InnoDB는 복합 인덱스에서 첫 번째 키(book_id)를 우선으로 탐색한 뒤 child_id를 필터링하기 때문에, child_id 단독 인덱스의 효과가 떨어진다)
• 그럼에도 불구하고 둘다 같은 fk인데 왜이렇게 비용이 차이가 나는지 잘 모르겠다 ㅠㅠ

[비용 차이가 발생시킬 문제]

• '내가 좋아요한 책들 조회’은 꼭 들어가야 하지만, 동시에 많이 조회될까?에 대한 의문이 든다. 인스타그램을 생각해 보면, 좋아요를 누른 후 다시 찾아가는 일이 그렇게 많지 않기 때문이다. 가끔 어떤 것에 좋아요를 눌렀는지 보거나, 좋아요를 일단 누른 다음 다시 게시글을 찾아가는 경우가 있다. 

 

book_id와 child_id를 조회하는 쿼리

• 복합키를 모두 활용하여 조회할 때 feedback 테이블의 코스트는 0!
• rec_book_like 테이블의 fk 2개로 하는 조회의 비용은 0.7이 나왔다.
• rec_book_like 테이블에서는 두 개의 index scan 이 사용되었다. 하나는 book_id, 하나는 child_id겠지. 이 두 개의 인덱스 스캔 결과가 intersect 단계에서 결합되어 최종 행을 선택한다.

 

[임시 결론]

• 좋아요 테이블의 경우 복합키를 사용하는 것이 좋겠다. 조회 성능을 생각하면!

 

[인덱스 공부하기]

이제, 인덱스에 대해서 좀 더 공부를 해 보자. 참고한 영상은 다음과 같다. 

https://www.youtube.com/watch?v=iNvYsGKelYs

https://www.youtube.com/watch?v=IMDH4iAQ6zM

https://www.youtube.com/watch?v=edpYzFgHbqs

 

 

영상을 정리해 보자면 이렇다. 

 

Q1. db의 성능을 개선한다는 건 무슨 의미일까?

DB 성능 최적화는 디스크 I/O를 줄여 원하는 데이터를 빠르게 가져오는 것을 의미한다. 디스크에서 데이터를 읽어오기 위해서는 물리적 I/O가 발생하는데, 하드디스크와 메모리의 속도 차이는 매우 크다. 이러한 속도 차이를 줄이기 위해 DB의 I/O를 최소화해야 하며, 이를 위해 주로 사용하는 방법 중 하나가 ‘인덱스’이다.

 

 

Q2. 그래서 인덱스가 뭔데?

인덱스는 데이터베이스 테이블의 검색 성능을 높이기 위한 자료구조이다. 주로 WHERE 절, GROUP BY 절, ORDER BY 절에 사용되며, 데이터가 정렬된 상태를 유지해 빠른 조회를 가능하게 한다. 마치 책의 목차처럼, 특정 위치로 바로 점프할 수 있게 해주는 역할이다.

• 인덱스는 항상 최신의 정렬 상태를 유지하며,
• 하나의 데이터베이스 객체로서 기능한다.
• 따라서 데이터베이스의 약 10%를 차지하는 저장공간을 필요로 한다.

 

Q3. 페이지란 무엇일까?

페이지는 데이터가 저장되는 단위를 의미하며, MySQL에서는 페이지 크기가 기본적으로 16KB이다. 데이터베이스에서 데이터를 읽거나 쓸 때는 항상 페이지 단위로 I/O가 이루어지며, 여러 행(row)이 하나의 페이지에 저장된다.

페이지 단위의 I/O는 성능에 중요한 영향을 미친다. 예를 들어, 필요한 데이터가 같은 페이지 내에 있다면 한 번의 디스크 I/O로 여러 데이터를 가져올 수 있어 효율적이다. 반대로 데이터가 여러 페이지에 흩어져 있으면 더 많은 I/O가 필요하다. 따라서 데이터를 효율적으로 페이지에 배치하고, 자주 조회되는 데이터는 페이지 캐시에 유지하는 것이 데이터베이스 성능 최적화의 핵심 중 하나이다.

 

 

Q4. 인덱스의 종류가 있다고?

인덱스는 크게 클러스터링 인덱스와 논클러스터링 인덱스로 구분된다.

• 클러스터링 인덱스: 실제 데이터가 인덱스에 의해 정렬된 방식으로 저장되는 인덱스이다. 보통 Primary Key(PK)나 NOT NULL + UNIQUE 조합으로 설정되며, 테이블당 하나만 설정할 수 있다. 클러스터링 인덱스는 데이터 페이지 자체를 정렬하며, 인덱스를 사용해 실제 데이터를 바로 조회할 수 있다.
• 논클러스터링 인덱스: 실제 데이터는 정렬되지 않고, 데이터가 저장된 위치 주소를 정렬하는 방식의 인덱스이다. Unique나 CREATE INDEX 구문을 통해 생성할 수 있으며, 여러 개의 논클러스터링 인덱스를 설정할 수 있다. 이는 클러스터링 인덱스보다 조회 성능은 다소 떨어지지만, 다양한 조회 조건에 적합하다.

 

Q5. 인덱스를 쓰면 조회 성능에는 좋지만 수정, 삭제, 생성은 느려진다는데?

이는 맞는 얘기다. 하지만 실제 비즈니스에서 Get 요청과 Get이 아닌 요청의 비율은 8:2 혹은 9:1에 이른다고 한다. (정말일까? 서비스 호출율을 확인해 보면 좋겠는데, 어떻게 호출율을 확인해 볼 수 있을까?) 이렇듯 조회가 압도적으로 많기 때문에, Get 요청을 최적화하면 전반적인 애플리케이션의 성능이 좋아진다.

 

 

Q6. 그럼 어디에 인덱스를 걸어야 할까?

1. 서비스의 특성을 파악하여, 무엇에 대한 조회가 많이 일어나는지 파악한다. 예를 들어, 사용자 ID나 주문 날짜처럼 자주 검색하는 컬럼에 인덱스를 설정한다.
2. cardinality(특정 집합의 유일한 값의 개수)가 높은 컬럼에 인덱스를 생성한다. 예를 들어, 주민등록번호나 상품 ID처럼 유니크한 값이 많은 컬럼에 인덱스를 설정하면 검색 성능이 향상된다.

 

Q7. 복합키를 생각해보자.

복합키는 복합 인덱스를 생성한다. 복합인덱스는 두 개 이상의 컬럼을 합쳐서 만든 인덱스를 말하는 것인데, 예를 들면 (book_id, child_id)가 하나의 인덱스가 되는 것을 말한다.

book_id, child_id를 하나씩 인덱스로 만들었을 때보다 더 적은 데이터분포를 보이기 때문에 탐색할 데이터 수가 줄어든다.

 

Q8. 옵티마이저란?

MySQL에는 쿼리 성능을 최적화하기 위한 옵티마이저가 존재한다. 옵티마이저는 쿼리를 실행할 때 최적의 실행 계획을 결정하는 역할을 한다. 예를 들어, 전체 데이터의 20~25% 이상을 조회하는 쿼리라면 인덱스를 사용하는 것보다 Full Table Scan이 더 효율적이라고 판단하여, 인덱스를 사용하지 않고 모든 데이터를 순차적으로 조회할 수 있다.

예를 들어, name 컬럼에 인덱스가 걸려있다고 가정하고, 이름이 "이"로 시작하는 사람들을 조회한다고 하자.

 

SELECT * FROM member m WHERE name = '김철수';

 

name	PK
김철수	34
김철수	3
김철수	16

 

이렇게 인덱스 검색에서 도출된 pk와 일치하는 데이터를 찾기 위해 각각의 disk i/o가 일어난다. 여기서는 3번이지만, 데이터가 수십만 건이라면, 매번 인덱스를 통해 조회하는 것은 많은 I/O를 유발할 수 있다.

 

이를 해결하기 위해 커버링 인덱스를 사용하면 불필요한 I/O를 줄일 수 있다. 커버링 인덱스란, 인덱스에 포함된 컬럼만을 조회하여 결과를 얻는 방법으로, 쿼리에 필요한 데이터를 인덱스에서 직접 가져와 테이블 접근을 피할 수 있다.

 

예를 들어 다음과 같이 name 컬럼만을 조회하는 경우가 있다.

SELECT name FROM member m WHERE name = '김철수';

 

이 경우 name만을 조회하기 때문에 인덱스에서 바로 데이터를 가져올 수 있어 불필요한 I/O를 줄일 수 있다. 이 경우 실행 계획의 type은 RANGE로 표시되며, extra에 using index가 표시된다. 참고로, extra 컬럼은 쿼리의 실행계획에서 성능에 대한 중요한 내용을 표시한다. 내부적으로 어떻게 처리가 되고 있는지를 보여주기 때문이다.

여기서 자주 볼 수 있는 게 ‘using where’인데, inno db 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤, mysql 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소한 것을 말한다.

 

select member_id from member m where name = '김철수' and 
created_at between '2024-11-09 00:00' and '2024-11-10 00:00';

 

이 경우, inno db는 인덱스가 걸려 있는 ‘김철수에 대한 데이터’를 모두 가져온 후에 메모리에 올린다. 이후 mysql 엔진이 created_at 이 설정한 범위에 있는지 체크한 후에 데이터를 넘기는데, 이런 상황을 using where이라고 한다.

이때 불필요한 ‘김철수 데이터’가 모두 올라가기 때문에, (이 쿼리가 정말 많이 조회된다면) name과 created_at 복합키를 거는 방안도 고려할 수 있다. 이후 using where은 using index condition으로 바뀐다. 

이렇듯 인덱스를 탄 후에도 어떻게 쿼리가 진행되는지 확인하기 위해서 extra 부분을 확인하는 것이 도움이 된다.

 

 

영상을 보고 나니, 다음과 같은 생각과 의문이 든다. 

1. 인덱스 조회를 한번 해보자! → 워크벤치에서 인덱스를 보려면 Table maintenance로 들어간다. 
2. FK의 인덱스는 참조 무결성을 보장하지 조회 성능을 보장하지 않는다는 말이 무슨 말일까? 논클러스터링 인덱스일 뿐이라는 말일까?
3. 복합키 테이블은 insert를 할 때 어떻게 비트리가 정렬될까? 그냥 일반키에 비해 insert가 느려지게 될 텐데, 이것을 조회 성능으로 메꾸는 방식이 더 나을까?
   • 복합키는 단일 키보다 정렬 조건이 더 복잡해서, 인서트 시 B-트리의 여러 레벨에서 위치를 찾아가며 데이터를 삽입해야 한다. 특히, 데이터가 많아질수록 B-트리를 재구성하거나 레벨이 깊어져 인서트 시간이 늘어날 수 있다.
   • 따라서 실험에 따르면 복합키 테이블이 인서트는 느려질 수 있지만 조회 성능이 뛰어나다는 점이 특징.
4. 복합 인덱스 두 개 설정하기
   • (child_id, book_id) / (book_id, child_id) 이렇게 순서를 바꾼 두 개의 복합 인덱스가 있다면 어떨까? 사용자가 좋아요한 책, 책에 좋아요를 누른 사용자 목록을 빠르게 찾을 수 있지 않을까?
5. '좋아요'와 '좋아요 수' 테이블을 분리한다. + 좋아요 수만 Redis에 캐시한다. 
   • 실시간으로 하나하나 다 +1, -1 해줄 순 없으니 Redis에 캐시해 두었다가 한번에 mysql로 이동하는 게 좋겠다.
6. 좋아요 내용은 RabbitMQ같은 메시지큐에 받으면 어떨까?
   • 사용자가 좋아요를 누르면 → Rabbit MQ에 이벤트가 전송되고 → 이를 순차적으로 처리한다!
   • 만약 좋아요를 눌렀을 때 → 작성자에게 알림을 보내야 한다거나… 등 다른 일들을 처리해야 한다면 메시지큐를 도입하는 것도 좋아 보인다.