인덱스 설계 수정본
✅ Used DataBase
MySQL
✅ ERD
✅ Indexes
member
Clustered Index : user_id
Non Clustered Index: email
post
Clustered Index : post_id
post_reply
Clustered Index : reply_id
post_image
Clustered Index : image_id
post_like
Clustered Index : composite_idx (post_id, user_id)
Non Clustered Index: post_id, user_id
member_follow
Clustered Index : composite_idx (follower, followee)
Non Clustered Index: follower, followee
📌 INDEX
✔️ 인덱스 설계 기준
- 데이터를 조작하는 작업보다 검색 작업의 수행 비율이 더 높은지?
- 검색 시 조건절에 사용하는지?
- 데이터 양은 많은지?
✔️ 설계한 인덱스 확인 요소
- 쿼리 실행 계획을 분석했을때 옵티마이저가 설계한 인덱스를 이용하는지?
- 복합 인덱스 설계 시, 자주 조회하는 컬럼을 앞에 두었는지?
🤔 인덱스를 기준없이 낭비하면 안 되는 이유?
-
조회를 제외한 DML 성능 저하
쓰기 및 수정, 삭제 작업이 이루어질 때 인덱스에서도 작업이 이루어져야 하고, 정렬을 위해 입력 블록을 찾는 탐색 시간도 있으며, 입력 블록에 여유 공간이 없을 시 인덱스 분할이 일어날 수 있어 시간이 지체될 수 있다.
-
디스크 공간 낭비
인덱스를 저장해야 하는 공간도 필요해서 데이터베이스의 사이즈가 증가하고, 인덱스가 없을때보다 10~20% 정도의 디스크 공간을 더 사용한다.
💡Issue - 성능 개선
배경 → 문제 → 해결 → 결과의 흐름으로 작성했습니다
배경
follow 테이블에서 사용하는 조회 쿼리
-
팔로우하려는 유저가 이미 팔로우하고 있는지 확인을 위한 쿼리
SELECT follower, followee FROM memeber_follow WHERE follower = ${follower} AND followee = ${followee}
-
유저 아이디로 팔로우 수 조회를 위한 쿼리
SELECT COUNT(followee) FROM member_follow WHERE follower = ${follower}
-
유저 아이디로 팔로잉 수 조회를 위한 쿼리
SELECT COUNT(follower) FROM member_follow WHERE followee = ${followee}
사용 인덱스
- 1번을 많은 메소드에서 사용하고, 유저가 자주 사용하게 될 것이라 판단하여 속도 및 성능을 향상 시키기 위해 follower, followee의 순서로 복합 인덱스를 설계
✔️ follow_composite_idx (follower, followee 복합 인덱스)
❓왜 follower_id를 선행 인덱스로 선택했는지?
계획 중인 프로젝트와 비슷한 인스타를 보고 고민해봤을때, 팔로워 아이디 ( = 로그인한 사용자)를 기준으로 검색이 더 많을 것 같아서 따로 follower_id 컬럼의 인덱스를 두지 않아도 인덱스 스캔을 할 것 같아, 위와 같은 순서로 설계 했습니다
인덱스 예시
실행 쿼리: SELECT follower, followee FROM member_follow WHERE follower = 2 AND followee = 8;
문제
자주 사용하는 쿼리 중 하나가 인덱스 스캔이 아닌 테이블 풀 스캔을 통해 실행하기 때문에, 조회 시 속도가 저하되어 성능이 좋지 않다는 문제를 발견
(쿼리 실행 계획 첨부 + 어떤 쿼리를 통해서 검색되어 가는지)
✅ 좋은 성능을 가진 쿼리
✔️ 1번 & 2번
1번 쿼리 - 쿼리 실행 계획 결과를 분석했을 때, 설계한 인덱스를 조건절에 사용했기 때문에 예상한 인덱스 (follow_composite_idx)를 사용하여 Index Range Scan을 통해 일치하는 값을 탐색
2번 쿼리 - 복합 인덱스의 선행 컬럼을 조건절에 사용하도록 설정했기에, 조건을 통해 검색했을 때 Index Scan을 통해 조회
❌ 성능이 좋지 않은 쿼리
✔️ 3번
3번 쿼리 - 조건절에 사용하는 컬럼은 복합 인덱스의 후행 컬럼으로 설정했으나, 데이터베이스에 follower, followee 컬럼만 있었기에, Table Full Scan을 실행하여 속도 ⬇️
해결
✔️ followee 컬럼을 인덱스로 추가 설정
결과
3번 쿼리도 설계한 인덱스가 조건절에 사용되기 때문에, 조회 시에 인덱스를 사용하여 성능 향상 (원래는 테이블 풀스캔)
💡Issue 2 - 클러스터링 인덱스 VS 세컨더리 인덱스
배경
회원 수정 및 탈퇴, 게시물 조회 등 회원이 존재하는지 확인할 때 사용하는 쿼리
SELECT EXISTS(SELECT user_id FROM member WHERE email || userId = #{email} || #{userId});
email 컬럼은 세컨더리 인덱스 / memberId 컬럼은 클러스터링 인덱스
문제
🤔 세션에 email VS memberId 중 어떤 것을 저장하여 조회할지? 고민
email 컬럼은 세컨더리 인덱스이고, memberId는 클러스터링 인덱스라서 둘을 각각 사용하여 조회했을때
세컨더리 인덱스는 데이터 값에 PK를 저장하기 때문에, 클러스터링 인덱스에 비해 낮은 성능
해결
✔️ 클러스터링 인덱스인 memberId를 세션에 저장하고, 세션 값으로 조회
애플리케이션에서 사용하는 쿼리
SELECT EXISTS(SELECT user_id FROM member WHERE userId = #{userId};
결과
→ 클러스터링 인덱스를 사용함으로써 속도 및 성능 향상
💡Issue 3 - UK 설정
배경
게시물을 좋아요할 때 사용하는 쿼리
INSERT INTO post_like(post_id, user_id) VALUES(#{post_id}, #{user_id});
문제
트랜잭션의 격리 수준을 Repeatable Read로 설정하긴 했으나, 이 격리 수준도 다른 트랜잭션에 의해 새로운 데이터가 생기면 인지하지 못하여 데이터 부정합 문제가 발생할 수 있기 때문에 같은 데이터가 중복 저장 가능
해결
✔️ 조회를 위해 복합 인덱스로 설계했던 컬럼은 Unique Key로 설정하여 같은 값이 데이터베이스에 저장 불가능하도록 변경
결과
트랜잭션이 충돌나서 데이터가 중복 저장되지 않고, 원하던 하나의 튜플만 저장