[Spring] 성능 최적화 3편 - 인덱스 활용하기

인덱스

• 테이블의 동작 속도를 높여주는 자료 구조
•  데이터의 위치를 빠르게 찾아준다.
• 예를 들어, 책의 찾아보기가 있을 때 특정 주제나 단어를 찾기 위해 책의 모든 페이지를 찾아보지 않고, 찾아보기에서 해당 단어를 찾아 바로 해당 페이지로 갈 수 있듯이, 데이터베이스 인덱스도 비슷한 역할을 한다.

 

인덱스의 장점 & 효과

1. 검색 성능 향상: WHERE 절에서 특정 칼럼을 이용한 조건 검색이 빨라진다. 인덱스가 없으면 데이터베이스 엔진은 테이블 전체를 스캔하여 조건에 맞는 행을 찾아야 한다. 하지만 인덱스가 있다면 인덱스를 사용하여 더 빠르게 검색이 가능하다.
2. 조인 성능 향상: JOIN 절에서 조인할 때 인덱스가 있는 경우, 데이터베이스 엔진은 인덱스를 이용하여 조인을 수행할 수 있다. 이는 두 테이블 간의 매칭되는 행을 효율적으로 찾을 수 있도록 도와준다.
3. 정렬 및 그룹화 성능 향상: 인덱스는 정렬 및 그룹화 작업에도 도움을 준다. 특히, **ORDER BY**나 GROUP BY 절에서 인덱스를 사용하면 정렬이나 그룹화 작업이 빨라진다.
4. 범위 검색 최적화: 일부 인덱스는 범위 검색에 특히 유용하다. 예를 들어, 날짜 범위나 숫자 범위에 대한 검색에서 인덱스를 사용하면 효율적으로 원하는 결과를 얻을 수 있다.

 

---

우선 성능 테스트를 하기 위해 더미 데이터를 만들어줘야 한다.

아래의 사이트에 가면 쉽게 더미데이터 생성이 가능하다.

많은 더미데이터가 있을수록 성능 비교가 더 잘된다.

 

더미 데이터 생성하는 사이트

https://www.mockaroo.com

 

아래 코드를 입력하면 현재 데이터 개수를 알수있다.

SELECT count(*) FROM 테이블명 ;

필자는 약 6천개 정도 만들었다.

 

이제 인덱스를 생성해 보자!

 

1. 컬럼 분석하기

효과적인 인덱스 생성하려면 어떤 컬럼으로 인덱스를 만들어야 할지 먼저 분석할 필요가 있다. 

• 개발하면서 작성한 쿼리 분석하기
• where, join 등에서 사용하는 컬럼들 분석하기

 

하지만 where절, join절에 칼럼이 여러 가지라면 아래 4가지 기준으로 인덱스를 결정하면 된다.

• 카디널리티 (Cardinality)
  • 특정 칼럼이나 관계에서 고유한 값을 가지는 정도
  • 카디널리티가 높을수록 인덱스 설정에 좋은 컬럼이다.
• 선택도 (Selectivity)
  • 데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표
  •  선택도가 낮을수록 인덱스 설정에 좋은 컬럼이다.
• 활용도
  • 해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값
  • 활용도가 높을수록 인덱스 설정에 좋은 컬럼이다.
• 중복도
  • 중복 인덱스 여부에 대한 값
  • 중복도가 없을수록 인덱스 설정에 좋은 컬럼이다.

SELECT
    CONCAT(ROUND(COUNT(DISTINCT dog_id_fk) / COUNT(*) * 100, 2), '%') AS dog_id_cardinality,
    CONCAT(ROUND(COUNT(DISTINCT date) / COUNT(*) * 100, 2), '%') AS date_cardinality,
    CONCAT(ROUND(COUNT(DISTINCT created_date) / COUNT(*) * 100, 2), '%') AS created_cardinality,
    CONCAT(ROUND(COUNT(DISTINCT status) / COUNT(*) * 100, 2), '%') AS status_cardinality
FROM tb_attendance;

 

나오는 지표를 보고서 높은 카디널리티인 칼럼을 인덱스로 정하면 된다.

 

2. 인덱스 설정하기

-- 인덱스 생성
CREATE INDEX idx_attendance_dog_id_fk ON tb_attendance ( dog_id_fk );

-- 인덱스 확인
show index from tb_attendance;

 

인덱스를 생성한 후 확인해 보자.

 

 

실행계획을 통해 인덱스 확인하기

데이터베이스에서 쿼리가 어떻게 실행되는지, 인덱스가 사용되는지 여부를 확인할 수 있다.

explain
SELECT * FROM tb_attendance
WHERE dog_id_fk = 1
;

 

실행 계획의 주요 필드

• id: 쿼리 내의 SELECT 명령어에 대한 식별자. 하위 쿼리나 조인이 있는 경우, 각각의 SELECT에 대해 고유한 id가 할당된다.
• select_type: 쿼리의 유형. 예를 들어, SIMPLE은 단순 SELECT 쿼리, SUBQUERY는 하위 쿼리, DERIVED는 FROM 절에서 파생된 테이블을 나타낸다.
• table: 해당 라인이 참조하는 테이블의 이름
• type: 조인 유형이나 테이블 접근 방식. ALL은 전체 테이블 스캔, index는 인덱스 스캔 등을 의미한다.
• possible_keys: 쿼리 실행에 사용될 수 있는 인덱스의 목록
• key: 실제로 쿼리 실행에 사용된 인덱스
• key_len: 사용된 인덱스의 최대 길이. 이는 인덱스의 어느 부분까지 사용되었는지를 나타낸다.
• ref: 인덱스를 사용할 때 참조된 컬럼이나 상수
• rows: 쿼리 실행을 위해 읽어야 할 행의 추정치
• Extra: 기타 정보. 여기서 **"Using index condition"**은 인덱스 조건을 사용하여 필터링되었음을 나타낸다.

 

3. Mysql Profiling으로 수치 확인해 보기

 

Mysql Profiling

• MySQL에서는 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인할 수 있는 기능을 제공한다.

-- 프로파일링 기능을 활성화 여부 확인
show variables like '%profiling%';

-- profiling 기능 활성화하기
set profiling=1;

set profiling_history_size=100;

 

위의 코드를 순서대로 입력하면 된다.

 

그럼 profiling = on으로, profiling_history_size = 100으로 바뀐다.

 

원하는 쿼리를 분석하기 위해서는 쿼리 아이디를 먼저 찾아줘야 한다.
이를 위해서 프로파일링 기능을 통해 확인하고자 하는 쿼리를 먼저 실행을 해주면 된다. 

원하는 쿼리를 입력하고서 show profiles 명령어를 통해서 쿼리 아이디를 찾아보자.

select * from tb_attendance
where dog_id_fk = 1;

show profiles;

 

단계별 쿼리를 볼 수 있고 여기서 원하는 쿼리를 찾으면 된다.

 

• 찾은 Query_id에 대해 profiling 기능으로 확인하기.
  • Profile을 통해 조회할 수 있는 세부 목록
    • BLOCK IO
    • MEMORY
    • CPU
    • CONTEXT SWITCHES
    • IPC
    • PAGE FAULTS
    • SOURCE
    • SWAPS

-- 해당 쿼리문의 수행 시간을 더 상세한 단위로 확인
show profile for query 37;

 

이전에 입력했던 쿼리가 실행됐을 때 각 단계별로 수행된 시간을 세부적으로 볼 수 있다.

 

-- 해당 쿼리의 CPU 사용량을 분석
show profile cpu for query 37;

 

 

인덱스를 사용하기 전과 후에 실행 시간 차이

 

 

 

0.002361 -> 0.000158 

로 실행시간이 확연히 줄였다.!!

이로써 인덱스를 걸었을 때 수치가 개선이 되는 것을 확인할 수 있다.

 

ngrinder로 성능 테스트

우선 만든 인덱스를 삭제하고서 성능을 테스트해보자

필자는 임의의 api를 만들고서 ngrinder에서 실행시켰다.

 

스크립트를 만들어 검증후, 통과하면 성능테스트를 진행해 본다.

 

 

인덱스를 생성 전 결과

 

 

인덱스 생성 후 결과

 

 

TPS : 41.0 -> 41.8

최고 TPS: 69.5 -> 55.0

평균 테스트 시간 : 246.30 -> 240.35

총 실행 테스트 : 2385 -> 2433

 

TPS는 아주 미세하게 좋아졌지만 실행테스트는 많이 증가했다.

테이블마다 인덱스를 생성한다면 성능상 개선이 많이 될 것 같다!

 

 

끝!!

 

 

 

 

 

참고

https://www.inflearn.com/course/성능-개선-초석-다지기

백엔드 개발자 성능 개선 초석 다지기 강의 - 인프런

 

https://rachel0115.tistory.com/entry/MySQL-인덱스-INDEX-정리-동작-방식-생성-삭제-설계

[MySQL] - 인덱스 (INDEX) 정리 (동작 방식, 생성, 삭제, 설계)

https://yurimkoo.github.io/db/2020/03/14/db-index.html

유림's Blog