서비스 사용 쿼리 정리 및 인덱스 설정

공통 테스트 환경

• 적재 데이터 : 500만건
• 측정 대상 : 조회 시간 (sec)

페어룸 서비스

사용 쿼리 목록

• 페어룸 생성 (pair_room)

INSERT INTO pair_room (access_code, created_at, driver, navigator, status, updated_at, id) 
VALUES (?, ?, ?, ?, ?, ?, default);

• 페어 교체 및 페어룸 상태 변경 (pair_room)

UPDATE pair_room
SET
  access_code=?,
  driver=?,
  navigator=?,
  status=?,
  updated_at=?
WHERE
   id=?

• 특정 access code를 가진 페어룸 데이터 조회 (pair_room)

SELECT
    pair_room.id,
    pair_room.access_code,
    pair_room.created_at,
    pair_room.driver,
    pair_room.navigator,
    pair_room.status,
    pair_room.updated_at
    		
FROM
    pair_room
    
WHERE
    pair_room.acess_code = ?

• 특정 access code를 가진 페어룸이 존재하는지 여부 조회 (pair_room)

SELECT 
    pair_room.id
    
FROM
    pair_room
    
WHERE
    pair_room.access_code= ?
LIMIT ?

• 특정 회원의 id가 포함된 페어룸-회원 참조 테이블 데이터 조회 (pair_room_member)

SELECT
  pair_room_member.id,
  pair_room.member.member_id,
  pair_room_member.pair_room_id
    		
FROM	
  pair_room_member
    
WHERE
  pair_room_member.member_id = ?

인덱스 설정 및 성능 테스트

페어룸 서비스에서 조회 쿼리의 조건으로 사용되는 컬럼은 다음과 같습니다.

• pair_room
  • access_code
• pair_room_member
  • member_id

두 컬럼은 각각 FK & UNIQUE 제약 조건이 걸렸기에 별도로 인덱스가 생성되어 있어 따로 인덱스 설정을 고려할 필요가 없다고 판단됩니다. 실제로 조회 쿼리를 실행해보면 굉장히 빠른 속도로 데이터가 조회됨을 확인할 수 있습니다.

• 특정 access code를 가진 페어룸 데이터 조회 성능 테스트 (pair_room)

• 조회 시간 : 0.01 sec

이미 Unique Index가 설정되어 빠른 조회 성능을 보여줍니다.

• 특정 회원의 id가 포함된 페어룸-회원 참조 테이블 데이터 조회 성능 테스트 (pair_room_member)

• 조회 시간 : 0.00 sec

이미 Index가 설정되어 빠른 조회 성능을 보여줍니다.

결론

페어룸 서비스에 관련된 테이블은 따로 인덱스를 설정하지 않기로 결정하였습니다.

---

회원 서비스

사용 쿼리 목록

• 회원 생성 (member)

SELECT
    m1_0.id,
    m1_0.access_token,
    m1_0.created_at,
    m1_0.provider_login_id,
    m1_0.profile_image,
    m1_0.updated_at,
    m1_0.provider_user_id,
    m1_0.user_name 
FROM
    member m1_0 
WHERE
    m1_0.provider_user_id=?

• provider_user_id로 특정 회원 정보 단건 조회 (member)

SELECT
    m1_0.id,
    m1_0.access_token,
    m1_0.created_at,
    m1_0.provider_login_id,
    m1_0.profile_image,
    m1_0.updated_at,
    m1_0.provider_user_id,
    m1_0.user_name 
from
    member m1_0 
where
    m1_0.provider_user_id=?

• provider_user_id로 특정 회원 정보 존재 여부 조회 (member)

SELECT
  m1_0.id 
FROM
  member m1_0 
WHERE
  m1_0.provider_user_id=? 
LIMIT ?

• user_id로 특정 회원 정보 존재 여부 (member)

SELECT
  m1_0.id 
FROM
  member m1_0 
WHERE
  m1_0.user_id=? 
LIMIT ?

• user_id를 기준으로 삭제되지 않은 회원 정보 존재 여부 조회 (member)

SELECT
  m1_0.id 
FROM
  member m1_0 
WHERE
  m1_0.provider_user_id=? AND deleted_at IS NULL
LIMIT ?

인덱스 설정 및 성능 테스트

회원 서비스에서 조회 쿼리의 조건으로 사용되는 컬럼은 다음과 같습니다.

• member
  • user_id
  • provider_user_id

조건에 사용되는 컬럼에 인덱스 설정 전, 후 모두 조회 쿼리를 날려 시간을 측정해보았습니다.

• 이때 회원 서비스에 사용되는 두 쿼리는 거의 같게 실행되서 하나의 쿼리만 테스트 하였습니다.

• user_id를 사용하는 컬럼은, 해당 컬럼이 unique 제약 조건으로 인해 이미 인덱스가 걸려있어 제외합니다.

• provider_user_id로 특정 회원 정보 단건 조회 성능 테스트 (member)

인덱스 설정 전

• 조회에 소요된 시간 : 1.45 sec

인덱스 설정 후

• 조회에 소요된 시간 : 0.01 sec

결론

1.45 sec → 0.01 sec로 눈에띄게 조회 소요 시간이 단축되었음을 확인할 수 있었습니다.

결론

결론적으로, member 테이블의 provider_user_id 컬럼에 인덱스를 설정하기로 결정하였습니다.

• 인덱스를 걸었을때 조회 성능 향상이 보임.
• 해당 컬럼은 자주 변경되는 값이 아니기에 인덱스의 성능 문제를 크게 고려할 필요 없을거라 판단됨.

---

레퍼런스 링크 서비스

사용 쿼리 목록

• 페어룸 id를 기준으로 레퍼런스 링크 데이터 전체 조회 (reference_link)

SELECT
    rl.id,
    rl.category_id,
    rl.created_at,
    rl.pair_room_id,
    rl.updated_at,
    rl.url
FROM
    reference_link rl
WHERE
    rl.pair_room_id=?

• 레퍼런스 링크 id를 기준으로 레퍼런스 링크 데이터 단건 삭제 (reference_link)

DELETE
FROM
  reference_link
WHERE
  id=?

• 페어룸 id를 기준으로 레퍼런스 링크 카테고리 데이터 전체 조회 (category)

SELECT
    c.id,
    c.category_name,
    c.created_at,
    c.pair_room_id,
    c.updated_at
FROM
    category c
WHERE
    c.pair_room_id=?

• 페어룸 id, 카테고리 이름을 기준으로 해당 카테고리 존재 여부 조회 (category)

SELECT
    c.id
FROM
    category c
WHERE
    c.category_name=?
    and c.pair_room_id=?
LIMIT
    ?

• 카테고리 id, 카테고리 이름을 기준으로 해당 카테고리 존재 여부 조회 (category)

SELECT
    c.id
FROM
    category c
WHERE
    c.id=?
    and c.pair_room_id=?
LIMIT ?

• 레퍼런스 링크 id, 페어룸 id를 기준으로 카테고리 데이터 단건 삭제 (category)

DELETE
FROM
    category
WHERE
    pair_room_id=?
    and id=?

• 레퍼런스 링크 id를 기준으로 전체 오픈 그래프 데이터 조회 (open_graph)

SELECT
    og.id,
    og.created_at,
    og.description,
    og.head_title,
    og.image,
    og.open_graph_title,
    og.reference_link_id,
    og.updated_at
FROM
    open_graph og
WHERE
    og.reference_link_id=?

• 레퍼런스 링크 id를 기준으로 오픈 그래프 데이터 단건 삭제 (open_graph)

DELETE 
FROM 
  open_graph 
WHERE
  reference_link_id=?

인덱스 설정 및 성능 테스트

회원 서비스에서 조회 쿼리의 조건으로 사용되는 컬럼은 다음과 같습니다.

• reference_link
  • id
  • pair_room_id
• category
  • pair_room_id
  • pair_room_id & category_name
  • category_id & category_name
  • reference_link_id & pair_room_id
• open_graph
  • reference_link_id

기본적으로 인덱스가 설정된 PK & FK 컬럼들을 제외하고, category_name 컬럼에 대한 인덱스 설정을 고민했습니다.

이때 category_name은 조건문에 혼자 사용되지 않고 FK 로 설정된 값들과 함께 사용되기에 따로 인덱스를 걸지 않아도 빠른 조회가 가능하지 않을까 생각이 들어 실험해보았습니다.

• 비슷한 조건이므로 pair_room_id & category_name 을 활용하는 쿼리만 테스트를 진행하였습니다.

• 페어룸 id, 카테고리 이름을 기준으로 해당 카테고리 존재 여부 조회 (category)

• 조회에 소요된 시간 : 0.01 sec

이미 인덱스가 설정된 pair_room_id 와 함께 조회하니 빠른 성능을 보이는걸 확인했습니다.

결론

결론적으로, 레퍼런스 링크 서비스에 관련된 테이블은 따로 인덱스 설정을 하지 않기로 결정했습니다.

---

투두 서비스

사용 쿼리 목록

• 투두 id를 기준으로 전체 투두 데이터 조회 (todo)

SELECT * 
FROM todo
WHERE id = ?;

• 페어룸 id를 기준으로 전체 투두 데이터를 오름차순 정렬 후 조회 (todo)

SELECT * 
FROM todo td
WHERE 
	td.pair_room_id = ?
ORDER BY 
    td.sort ASC;

• 페어룸 id를 기준으로 sort 컬럼의 값 기준 오름차순 정렬 후 가장 큰 값 조회 (todo)

SELECT *
FROM 
    todo td
WHERE 
    td.pair_room_id = ?
ORDER BY 
    td.sort DESC
LIMIT 1;

인덱스 설정 및 성능 테스트

투두 서비스에서 조회 쿼리의 조건으로 사용되는 컬럼은 다음과 같습니다.

• todo
  • id
  • pair_room_id

결론

조건에 사용되는 컬럼이 모두 기본적으로 인덱스가 설정된 PK * FK 이므로 따로 인덱스 설정을 고려할 필요가 없다 판단됩니다.

---

타이머

사용 쿼리 목록

• 페어룸 id를 기준으로 타이머 데이터 조회 (timer)

SELECT
    te1_0.id,
    te1_0.created_at,
    te1_0.duration,
    te1_0.pair_room_id,
    te1_0.remaining_time,
    te1_0.updated_at 
FROM
    timer te1_0 
WHERE
    te1_0.pair_room_id=?

• 페어룸 access code를 기준으로 타이머 데이터 조회 (timer, pair_room)

SELECT
    te1_0.id,
    te1_0.created_at,
    te1_0.duration,
    te1_0.pair_room_id,
    te1_0.remaining_time,
    te1_0.updated_at 
FROM
    timer te1_0 
    left join
    pair_room pre1_0 
    on pre1_0.id=te1_0.pair_room_id 
WHERE
    pre1_0.access_code=?
SELECT
    pre1_0.id,
    pre1_0.access_code,
    pre1_0.created_at,
    pre1_0.driver,
    pre1_0.navigator,
    pre1_0.status,
    pre1_0.updated_at 
FROM
    pair_room pre1_0 
WHERE
    pre1_0.id=?

인덱스 설정 및 성능 테스트

타이머 서비스에서 조회 쿼리의 조건으로 사용되는 컬럼은 다음과 같습니다.

• timer
  • pair_room_id

결론

기본적으로 인덱스가 설정된 FK 이므로 따로 인덱스를 걸지 않기로 판단하였습니다.