DB 인덱스 활용을 통한 성능개선 2

작성일 | In

DB 인덱스 활용을 통한 성능개선 1 : https://rustywhite404.github.io/mysql/2024/10/03/DB_index_1/#
앞선 글에서는 새로 인덱스를 거는 방법과, 어떤 경우에 인덱스를 걸어야 하는지 정리해보았다. 이번에는 이미 인덱스가 걸려있는 테이블에서 인덱스 변경이 필요한 경우 어떻게 해야 할 지, 그리고 인덱스 별 처리 시간은 어떻게 차이를 확인해야 할 지 알아보기로 했다.

ex) A테이블에 a, b라는 인덱스가 걸려 있다. 쿼리 성능이 좋지 않아 실행 계획을 확인해보니 a테이블의 카디널리티 수치가 더 좋은데도 b 인덱스를 타고 있다. 이런 경우 힌트 기능을 활용하여 a 인덱스를 타도록 설정 해줄 수 있다.

1. 힌트를 이용해서 참조할 인덱스를 변경해보자

이미지
우선 어떤 인덱스가 걸려있는지 확인해보자. 위와 같이 createDate, who 컬럼에 대해 인덱스를 작성해두었다.

이미지
실행 계획을 확인해보면, 카디널리티 수치가 더 높은 createDate 컬럼의 인덱스를 참조하고 있다. 하지만 지금은 who컬럼의 인덱스를 타도록 만들어야 하는 상황이라고 가정하고, 힌트 기능을 이용해서 강제로 idx_notice_who 인덱스를 사용하도록 만들어보자.

1
2
3
4
5
6
explain
SELECT * FROM notice use index(idx_notice_who)
WHERE who = 'rustywhite404'
and
    createDate BETWEEN '2023-01-15 00:00:00' AND '2023-01-15 23:59:59'
;

이미지
use index(사용할 인덱스명)을 선언해준 후 실행계획을 확인해보면 idx_notice_who 인덱스를 타는 것을 확인 할 수 있다.

2. MySQL Profiling으로 쿼리 처리 속도 확인해보기

카디널리티 수치가 높다고 해서 무조건 그 컬럼에 인덱스를 걸어야 하는 건 아니다. 인덱스를 걸어본 후 전후 비교를 통해 판단을 해야 하는데, MySQL Profiling으로 쿼리 처리 속도를 비교해 볼 수 있다.

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

    이미지
    지금은 OFF로 되어있는 게 보인다. profiling 기능을 활성화 해주자.

    1
    2
    3
    // profiling 기능 활성화하기
set profiling=1;
set profiling_history_size=100;
  2. 확인하고자 하는 쿼리를 우선 한 번 실행(query_id를 찾기 위함)한 후 show profiles.
    이미지 
    1
    2
    3
    4
    SELECT * FROM study_db.notice
WHERE createDate BETWEEN '2023-01-15 00:00:00' AND '2023-02-14 23:59:59'
;
show profiles ;

    show profiles를 입력해보면 앞서 실행한 쿼리들과 query_id를 확인 할 수 있다. 지금 실행 속도를 확인해 볼 query_id는 5번이 되겠다.

  3. 찾은 query_id에 대해 profiling 기능으로 확인하기 
    1
    2
    3
    4
    //해당 쿼리문의 수행시간 분석   
show profile for query 5;  
//해당 쿼리문의 CPU 사용량 분석 
show profile cpu for query 5;

    이미지
    이미지
    CPU 외에도 아래 항목들에 대해 분석 할 수 있다.

    • BLOCK IO
    • MEMORY
    • CPU
    • CONTEXT SWITCHES
    • IPC
    • PAGE FAULTS
    • SOURCE
    • SWAPS
  4. profiling 기능을 활용하여 전후비교
    이미지 소요시간을 확인해보면 인덱스를 건 후의 쿼리 실행 속도가 훨씬 빨라졌다는 것을 알 수 있다.

3. nGrinder로 쿼리 처리 속도 확인해보기

이미지 이미지 성능 테스트가 필요한 쿼리를 호출할 api를 작성한다. 이 예제는 아래와 같이 기간을 설정해서 호출하면 그 기간동안 쌓인 데이터들을 모두 출력해주는 쿼리다.

1
http://localhost:8080/api/notices/dates?startDate=2023-01-15%2000:00:00&endDate=2023-02-14%2023:59:59

이미지
이런 식으로 조회가 된다.
api를 만들었으면 nGrinder로 돌아가서 검증할 스크립트를 작성해보자.
이미지
이미지
스크립트 생성 후 테스트 코드 부분을 위와 같이 수정한다. 날짜를 고정해서 Map으로 넘겨 반복 테스트를 쉽게 만들었다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
	public void test() {
        // API 호출을 위한 URL 및 파라미터 설정
        String apiUrl = "http://127.0.0.1:8080/api/notices/dates";
        Map<String, String> params = new HashMap<>();
        params.put("startDate", "2023-01-15 00:00:00");
        params.put("endDate", "2023-02-12 23:59:59");

        // HTTP 요청 및 응답 획득
        HTTPResponse response = request.GET(apiUrl, params);

		if (response.statusCode == 301 || response.statusCode == 302) {
			grinder.logger.warn("Warning. The response may not be correct. The response code was {}.", response.statusCode)
		} else {
			assertThat(response.statusCode, is(200))
		}

이제 이 스크립트를 실행해서 성능 차이를 볼 예정이다. Vuser 20, Duration은 1분으로 설정해서 인덱스가 있을 때와 없을 때를 각각 비교했다.
이미지
이미지

  • 평균 TPS : 60 -> 72.2
  • Peak TPS : 65 -> 80
  • Mean Test Time : 334.47ms -> 276.73
  • Successful Tests : 3,376 -> 4,064

성능이 조금 더 향상된 것을 확인 할 수 있다. 이런 조그만 속도 차이들이 쌓이고 쌓여 무시 할 수 없는 성능 차이를 내게 되는 것 같다.