[Mysql] 인덱스 생성 시 고려 사항 (테이블, 쿼리 예제)

📌 MySQL 인덱스 생성 시 어떤 키로 만들어야 할까?

아래와 같은 room_message 테이블과 쿼리가 있다고 가정해보겠습니다.

CREATE TABLE room_message (
    id             BIGINT AUTO_INCREMENT PRIMARY KEY,
    create_date    TIMESTAMP(6)            NULL,
    update_date    TIMESTAMP(6)            NULL,
    delete_date    TIMESTAMP(6)            NULL,
    delete_flag    BIT         DEFAULT b'0' NULL,
    send_id        BIGINT                  NULL,
    status         VARCHAR(10) DEFAULT 'DONE' NOT NULL,
    text           LONGTEXT                NULL,
    type           VARCHAR(10)             NOT NULL,
    unread_count   BIGINT      DEFAULT 0   NULL,
    room_member_id BIGINT                  NULL,
    room_id        BIGINT                  NULL,
    number         DECIMAL(38, 2)          NOT NULL,
    CONSTRAINT FK5g8f3n7ay2igcaoaf8prm36gs
        FOREIGN KEY (room_member_id) REFERENCES room_member (id),
    CONSTRAINT FKp4uu1f5nnjoss6y4ghq444u3
        FOREIGN KEY (room_id) REFERENCES room (id)
);

SELECT * 
FROM room_message 
WHERE room_id = 1 AND room_member_id = 2 
ORDER BY create_date DESC;

✅ 인덱스는 이렇게 생성하면 좋습니다

CREATE INDEX idx_room_message_lookup 
ON room_message (room_id, room_member_id, create_date DESC);

이 인덱스를 추천하는 이유는 다음과 같습니다.

• room_id, room_member_id: WHERE 절에서 필터링 조건으로 사용됩니다.
• create_date: ORDER BY 정렬에 사용되므로 인덱스에 포함시켜 정렬 성능을 향상시킵니다.

💡 커버링 인덱스를 사용해도 좋을 때

만약 아래처럼 필요한 컬럼만 조회하는 경우, 커버링 인덱스를 고려할 수 있습니다.

SELECT id, text 
FROM room_message 
WHERE room_id = 1 AND room_member_id = 2 
ORDER BY create_date DESC 
LIMIT 10;

이 경우 인덱스를 이렇게 확장하면, 쿼리를 인덱스만으로 처리할 수 있어 더 빠릅니다.

CREATE INDEX idx_room_message_partial 
ON room_message (room_id, room_member_id, create_date DESC, id, text);

⚠️ 인덱스 생성 시 주의할 점

• 너무 많은 컬럼을 인덱스에 포함하면 INSERT/UPDATE 성능 저하로 이어질 수 있습니다.
• 실제 EXPLAIN 결과를 활용하여 쿼리 플랜을 분석하고 튜닝하는 것이 중요합니다.

필요한 경우 EXPLAIN 결과와 함께 더 자세히 분석해보는 것도 좋은 방법입니다 🙂