[Spring][Seminar-hub] 4편. SQL 실행계획 분석과 Index 적용

passionfruit200 2024. 3. 4. 16:05

2024. 3. 4. 16:05

SQL 실행계획 분석과 Index 적용

안녕하세요! 네번째 글로 찾아오게 된 PassionFruit200 입니다!
첫번쨰 글에서는, 세미나 서비스 결제 기능의 기획을 정의하고, Spring Framework를 활용하여 DB 설계, 코딩을 진행하였습니다.
두번쨰 글에서는, 결제서비스에서 동시성 처리와 데이터 정합성을 고려하기 위하여 트랜잭션의 격리레벨과 트랜잭션의 동장방식 그리고 동시성 처리를 위한 배타락을 알아보고 구현해보았습니다.
세번째 글에서는 하나의 세미나를 구매하는 것이 아닌, 장바구니 기능을 만들어놓고 발생할 수 있는 문제점을 파악하고 교착상태 해결방안에 대해 이해하고 데드락을 해결해보았습니다.
이번 네번쨰 글에서는 결제서비스에서 사용된 쿼리들이 효과적으로 MySQL의 데이터를 호출하는지 알아보도록 하겠습니다. 추가로 이번 글은, 간단한 SELECT와 UPDATE, INSERT로 이루어져 있어서 SQL 튜닝 부분보다는 가장 기본적인 부분들을 충족시키는 가벼운 글로 읽어주시면 되겠습니다.

누구나 이해할 수 있도록 쉽게 작성하는데 주안점을 두고 작성했으니, 도움이 됐으면 좋겠습니다!

이번 개발을 진행하면서

SQL의 실행계획 분석을 통해 실행계획에서 나타내는 각 항목에 대해서 명확히 이해하게 되었습니다.
Index 설계 시 카디널리티를 기반으로 하여 Index를 생성합니다.

결제서비스에서 사용중인 쿼리 목록

먼저 저의 서비스에서 사용중인 코드들을 정리해보았습니다.

1. 기존의 회원정보 호출 함수 Member SQL 확인

기존의 회원정보 호출을 위해 실행되는 JPQL을 확인해보겠습니다.

@Query("SELECT m From Member m WHERE m.member_id = :member_id AND del_dt is null")
Optional<Member> findByMember_id(@Param("member_id") String member_id);

현재 호출하는 쿼리를 살펴보겠습니다.

불필요하게 모든 필드를 다 가져오고 있습니다.
필요한 데이터인 member_no만 가져오도록 하겠습니다.

@Query("SELECT m.member_no From Member m WHERE m.member_id = :member_id AND del_dt is null")
Optional<Member> findmember_noByMember_id(@Param("member_id") String member_id);

그에 맞추어서 함수를 모두 변경해줍니다.

1-1. 회원정보 호출 함수 Member SQL 실행계획 확인 문제점 찾기

시간측정을 위해 조회 쿼리를 실행시켜보겠습니다.

mysql> SELECT m1.member_no FROM member m1 WHERE member_id = "FORWHAT_WHAT_traveler567@gmail.com" AND del_dt IS NULL;

단순 조회 SQL에서 3.71초가 걸리고 있습니다. 이렇게 오래걸리는것이 정상인지 아닌지는 실행계획을 통해 확인해보겠습니다.

실행계획을 확인해보겠습니다.

EXPLAIN SELECT m1.member_no FROM member m1 WHERE member_id = "FORWHAT_WHAT_traveler567@gmail.com" AND del_dt IS NULL;

위의 실행 계획을 분석해보겠습니다.

type: ALL로 표시되어 있어 전체 테이블 스캔을 수행했다는 것을 나타냅니다.
possible_keys: MySQL이 인덱스를 사용할 수 있는 키 목록입니다. 여기서는 NULL로 표시되어 있어 어떠한 인덱스도 사용하지 않았다는 것을 나타냅니다.
key: NULL로써 인덱스를 사용하지 않았습니다.
ref: NULL로써, 행을 찾기 위해 사용된 인덱스나 const와 같은 참조 정보가 사용되지 않았습니다. .
rows: 1541885 개의 행을 Member 테이블에서 읽었습니다. 전체 테이블을 읽었다는 의미입니다..
filtered: 행 필터링의 효과를 나타내는 백분율입니다. 여기서는1.00%로 표시되어 있습니다. 즉 1개를 찾기 위해 100개의 ROWS를 Filtering 했다는 의미입니다.
Extra: 추가적인 정보입니다. 여기서는 Using where로 표시되어 있어 WHERE 절이 사용되었다는 것을 나타냅니다.

요약하면, 이 쿼리는 member 테이블을 전체 테이블 스캔하고 WHERE 절에 따라 조건을 적용하여 결과를 가져오고 있습니다. 매우 비효율적인 코드입니다.

아래의 코드를 실제로 실행시 테이블 올스캔으로 인해 0.99가 걸립니다.(순차 I/O가 발생합니다.)

mysql> SELECT m1.member_no FROM member m1 WHERE member_id = "FORWHAT_WHAT_traveler567@gmail.com" AND del_dt IS NULL;

1-2. 멀티 컬럼(Multi-Column) 인덱스 추가를 통하여 속도개선시키기

위의 실행 계획에서 보았듯이 인덱스를 사용하지 않는것을 알 수 있었습니다.
현재 Member 테이블의 인덱스를 확인해보겠습니다.

mysql> SHOW INDEX FROM MEMBER;

현재 PRIMARY KEY 밖에 존재하지 않는것을 확인할 수 있습니다.

Member_id는 Unique 한 값을 가지고 있고, del_dt는 삭제되지 않았다면 NULL, 삭제되었다면 날짜값입니다. UNIQUE INDEX를 생성합니다.

멀티컬럼 (Multi-Column) Index를 생성해야할때는 인덱스 순서를 주로 카디널리티 기준으로 정합니다.( 카디널리티가 높은 열부터 먼저 처리해야 빠른 순서로 정렬된 인덱스에서 값을 찾습니다.
1. member_id의 카디널리티입니다.
현재 저의 테이블에서 member_id는 Unique 이므로 레코드 개수가 카디널리티일 것 입니다. (여담으로, 업무상으로 의미있는 번호를 반드시 해당 Table의 고유번호로 선정하는 것이 필수이지만, 이번 설계에서는 그렇지는 않습니다. 혹시 서비스업무를 진행할때는 반드시 업무와 직접적인 연관이 있는 member_id를 고유번호로 하는 것을 추천드립니다. )

mysql> SELECT COUNT(DISTINCT member_id) FROM MEMBER;

2. del_dt의 카디널리티입니다.
del_dt가 NULL 이 아니라면 삭제처리된 상황입니다. 회원탈퇴된 회원들이 일반 회원 중에서 많지 않을 것 이므로 del_dt의 카디널리티는 곧 회원탈퇴한 회원 수 입니다.

mysql> SELECT COUNT(DISTINCT del_dt) FROM MEMBER;

카디널리티가 높은 순서인 member_id, del_dt 순서대로 유니크 인덱스를 생성합니다. ( 유니크 인덱스를 통해 인덱스에서 해당 값을 찾는다면 바로 인덱스 스캔을 중단시키는 작업을 통해 탐색효율이 증가합니다. )

mysql> CREATE UNIQUE INDEX uix_member_id_del_dt ON MEMBER (MEMBER_ID, DEL_DT);

SQL 튜닝후 (유니크 인덱스 카디널리티 고려하여 생성)

SQL 튜닝 후 얼마나 빨라졌는지 확인해봅니다. 3.67초에서 0.01초로 줄었습니다.

mysql> SELECT m1.member_no FROM member m1 WHERE member_id = "FORWHAT_WHAT_traveler567@gmail.com" AND del_dt IS NULL;

실행계획을 분석해보겠습니다.

mysql> EXPLAIN SELECT m1.member_no FROM member m1 WHERE member_id = "FORWHAT_WHAT_traveler567@gmail.com" AND del_dt IS NULL;

위의 실행 계획을 분석해보겠습니다.
해당 쿼리의 실행 계획을 더 자세히 분석해보겠습니다.

id: 1 - 단일 SELECT 쿼리이며, 실행 계획에서 고유한 ID입니다.
select_type: SIMPLE - 간단한 SELECT 쿼리임을 나타냅니다.
table: m1 - 쿼리에서 사용되는 테이블 이름입니다.
partitions: NULL - 파티션 정보가 제공되지 않았습니다.
type: ref - 인덱스를 사용하여 참조(레인지 검색)하는 방법입니다.
possible_keys: uix_member_id_del_dt - 쿼리에서 사용 가능한 키(인덱스)입니다. 여기서는 "uix_member_id_del_dt"가 사용됩니다.
key: uix_member_id_del_dt - 실제로 사용된 인덱스입니다. 즉, "uix_member_id_del_dt" 인덱스가 사용됩니다.
key_len: 2012 - 사용된 인덱스의 길이를 나타냅니다. 여기서는 2012로 나와 있습니다.
ref: const,const - 인덱스를 참조하는데 사용된 값입니다. 여기서는 "const,const"로 나와 있습니다.
rows: 1 - 예상되는 검색 결과 행 수입니다. 여기서는 1로 나와 있습니다.
filtered: 100.00% - 결과 집합이 얼마나 필터링되었는지를 나타냅니다. 여기서는 100.00%로 나와 있습니다.
Extra: Using where; Using index - WHERE 조건과 인덱스를 사용하여 쿼리를 처리하고 있다는 것을 나타냅니다.

이 실행 계획을 종합하면 다음과 같은 내용을 알 수 있습니다:

쿼리는 member_id와 del_dt 열을 사용하여 테이블 member에서 데이터를 검색합니다.
"uix_member_id_del_dt" 인덱스를 사용하여 member_id와 del_dt 열에 대한 레인지 검색을 수행합니다.
예상 결과 행은 1건이며, WHERE 조건에 따라 100% 필터링됩니다.
인덱스를 사용하여 효율적으로 쿼리가 처리되고 있습니다.

2. 세미나 정보 호출 함수 Seminar SQL 확인

이번에는 세미나 호출 쿼리를 확인해보겠습니다.
기존에 작성된 querydsl 쿼리입니다.

public Optional<Seminar> findBySeminar_NameWithPessimisticLock(String seminar_name){
    QSeminar qSeminar = QSeminar.seminar;

    Seminar seminarEntityLock = queryFactory.selectFrom(qSeminar)
            .where(qSeminar.seminar_name.eq(seminar_name)
                    .and(qSeminar.seminar.del_dt.isNull()))
            .setLockMode(LockModeType.PESSIMISTIC_WRITE) // 비관적 락 설정
            .fetchOne();
    return Optional.ofNullable(seminarEntityLock);
}

모든 필드를 다 호출하는 것을 확인할 수 있습니다.

2.1 필요한 컬럼만 가져오도록 수정

1.결제서비스 신청 함수에서 seminarDTO가 사용중인 부분은 seminarDTO의 seminar_max_participants, seminar_participants_cnt, seminar_name, seminar_no 이 4가지 필드만 사용됩니다.
Querydsl의 Projections 를 활용해서 필요한 데이터만 가져오도록 하고 매핑합니다.

public Optional<Seminar> findBySeminar_NoWithPessimisticLock(Long seminar_no){
    QSeminar qSeminar = QSeminar.seminar;

    // 세미나 레코드를 PESSIMISTIC_WRITE 락으로 가져옵니다.
    Seminar seminarEntityLock = queryFactory.selectFrom(qSeminar)
            .select(Projections.fields(Seminar.class,
                    qSeminar.seminar_max_participants,
                    qSeminar.seminar_participants_cnt,
                    qSeminar.seminar_name,
                    qSeminar.seminar_no))
            .where(qSeminar.seminar_no.eq(seminar_no)
                    .and(qSeminar.seminar.del_dt.isNull()))
            .setLockMode(LockModeType.PESSIMISTIC_WRITE) // 비관적 락 설정
            .fetchOne();
    return Optional.ofNullable(seminarEntityLock);
}

필요한 데이터만 가져오도록 하겠습니다.

2.2 세미나 서비스 문제점 찾아보기

결론적으로 말하면, 세미나 서비스 검색시에는 문제점이 없었습니다.
이미, Primary Key에서 해당 인덱스가 존재하여 매우 효율적으로 검색하고 있었습니다.

어떤식으로 해당 방법을 알아내었는지 정리하였습니다.

Seminar에서 SELECT문을 실행해보겠습니다.

mysql> select * from seminar where seminar_no = 250000 AND del_dt IS NULL;

0.00 sec만에 검색하며 빠른 효율을 보여줍니다.
실행계획을 확인해보겠습니다.

type: const는로써, 250000로 하나의 행만 찾을 수 있으므로 빠릅니다.
possible_keys: 쿼리 실행에 사용될 수 있는 인덱스 목록입니다. PRIMARY 키를 사용할 수 있음을 나타내고 있습니다.
key: Primary Key가 사용되었습니다.
key_len: Primary Key인 Seminar_no의 Key Len은 8 입니다. 8 바이트입니다.
ref: const로 상수 값을 사용해 인덱스를 찾았음을 나타냅니다.
rows: ySQL이 추정하는 읽어야 할 행의 수로써 const 조건이 있기에 1행이며, 효율적입니다.
filtered: 쿼리 조건에 의해 필터링된 후 남은 행의 비율(백분율)입니다. 100.00은 모든 행이 조건에 부합함을 의미합니다.

실행 계획에서 보듯이 seminar_no를 PRIMARY 키로 사용하여 0.00초 만에 데이터를 조회하고 있습니다. del_dt가 NULL인 조건도 적용되지만, 굳이 적용하지 않아도 충분히 빠르므로 그대로 진행하겠습니다. del_dt는 인덱스에 존재하지 않아 Primary Key에서 seminar_no를 활용해 검색한 후 InnoDB 스토리지 엔진에서 MySQL 엔진으로 리턴됩니다. 이떄 MySQL 엔진에서 del_dt가 NULL인지 아닌지 따로 한번 더 필터링을 하는데 이번 경우에는 del_dt가 NULL이기에 Filtering이 100%입니다..

만약 아래의 쿼리를 통해 유니크 인덱스(seminar_no, del_dt)를 생성한다면, 모든 정보가 InnoDB Storage에서 처리되어 반환될 것 입니다.

CREATE UNIQUE INDEX uix_seminar_no_del_dt ON seminar (seminar_no, del_dt);

3. 세미나 참여인원 증가/감소 쿼리 점검하기

현재 실행되고 있는 증가, 감소 쿼리입니다.
증가/감소 쿼리 또한 결국에는 어떤 데이터를 찾아서 해당 레코드를 작업하는 것 이므로 검색조건에 의해 속도차이가 날 수 있습니다.

@Transactional
@Modifying
@Query("UPDATE Seminar s SET s.seminar_participants_cnt = s.seminar_participants_cnt + 1 WHERE s.seminar_no = :seminar_no AND del_dt is null")
void incrementParticipantsCnt(@Param("seminar_no") Long seminar_no);

@Transactional
@Modifying
@Query("UPDATE Seminar s SET s.seminar_participants_cnt = s.seminar_participants_cnt - 1 WHERE s.seminar_no = :seminar_no AND del_dt is null")
void decreaseParticipantsCnt(@Param("seminar_no") Long seminar_no);

3.1 실행계획 확인해보기

두개의 쿼리를 한번에 확인해보면

두 코드 모두 올바르게 작동합니다.

select_type: UPDATE 쿼리를 나타냅니다.
type: PRIMARY 키를 통해 단일 값 조회하므로 const로 표시될것이라 생각하였는데, range로 표시됩니다. 실제로 해당 쿼리를 실행시 0.01초 안에 실행되므로 range로 표시되나 실제로는 const로 검색되는 것으로 보입니다.
key: PRIMARY 키가 사용됩니다.
ref: const 값이 사용되어 PRIMARY 키를 찾습니다.
rows: 쿼리를 실행하기 위해 MySQL이 추정하는 읽어야 할 행의 수는 1입니다.
Extra: WHERE 절을 사용하여 특정 행을 필터링합니다.

type이 왜 range인지 모르겠으나 UPDATE 구문 시에는 index를 사용하더라도 이렇게 뜨는 것 같습니다. 그렇지 않다면, 실제 쿼리가 이렇게 빨리 나올 수 없을 것 입니다.

레코드 INSERT, UPDATE, DELETE 시 사용되는 체인지버퍼

저희 결제서비스에는 세미나 등록시 현재 인원수가 올라가는 UPDATE 쿼리와 세미나 참여내역, 세미나 결제내역에 INSERT되는 쿼리가 존재합니다. 이러한 작업들이 수행될때 MySQL 내부에서는 어떤 작업들이 일어날까요?

해당 사항에 대한 궁금증으로 정리해보게 되었습니다. 우선 위의 UPDATE 쿼리와 INSERT 쿼리가 실행되면, 실제로 데이터파일에 새로 레코드가 생기고, 해당 테이블에 포함된 인덱스를 업데이트 하는 작업 또한 포함됩니다. 그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 됩니다. 그래서 InnoDB는 변경해야할 인덱스 페이지가 버퍼풀에 있으면 바로 업데이트를 수행하지만 그렇지않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데, 이떄 사용하는 임시 메모리 공간을 체인지 버퍼(Change buffer)라고 합니다.

사용자에게 결과를 전달하기 전에 반드시 중복여부를 체크해야하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다. 즉, 제가 위에서 생성한 uix_member_id_del_dt 같은경우 유니크 인덱스이기에 항상 중복체크가 필요하여 체인지 버퍼를 사용할 수 없을 것 입니다. 이럴경우 만약 member_id가 변경되거나 이러한 작업이 빈번하게 일어난다면 성능에 부정적일 것 같습니다. 일반적으로 유니크 값으로 설정한 값을 마구 바꾸는 일은 흔하지는 않은 것 같습니다. 우선 저의 시스템에서는 member의 id는 변경되지 않습니다.

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge Thread) 라고 합니다. MySQL 5.5 이전 버전까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능(그래서 MySQL 5.5 이전 버전까지는 이 버퍼를 인서트 버퍼라고 칭했습니다)했는데, MySQL .5.5 버전부터 조금씩 개선되면서 MySQL 8.0 에서는 INSERT, DELETE, UPDATE 로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선돼었습니다. 또 MySQL 5.5 이전 버전에서는 별도의 시스템 변수 설정 없이 기본적으로 기능이 활성화됐지만 MySQL 5.5 부터는 innodb_change_buffering 이라는 시스템 변수가 새로 도입되어 작업의 종류별로 체인지 버퍼를 활성화할 수 있으며, 체인지 버퍼가 비효율적일때는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선되었습니다.

. innodb_change_buffer 시스템 변수에 설정할 수 있는 값은 다음과 같습니다.

all : 모든 인덱스 관련 작업(inserts + delets + purges) 을 버퍼링
none : 버퍼링 안함
inserts : 인덱스에 새로운 아이템을 추가하는 작업만 버퍼링
deletes : 인덱스에서 기존 아이템을 삭제하는 작업(삭제됐다는 마킹 작업)만 버퍼링
changes : 인덱스에 추가하고 삭제하는 작업만(inserts + deletes) 버퍼링
purges : 인덱스 아이템을 영구적으로 삭제하는 작업만 버퍼링(백그라운드 작업)

체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정돼있으며, 필요하다면 InnoDB 버퍼풀의 50%까지 사용하게 설정할 수 있다. 체인지 버퍼가 너무 많은 버퍼 풀 공간을 사용하지 못하도록 한다거나 INSERT나 UPDATE 등이 너무 빈번하게 실행되어 체인지 버퍼가 더 많은 버퍼 풀을 사용할 수 있게 하고자 한다면 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 됩니다.

mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME='memory/innodb/ibuf0ibuf';

이벤트 이름 (EVENT_NAME): memory/innodb/ibuf0ibuf
- 이는 InnoDB 삽입 버퍼(ibuf)에 대한 메모리 사용을 나타냅니다.
현재 사용 중인 바이트 수 (CURRENT_NUMBER_OF_BYTES_USED): 136
- 현재 이벤트에 할당된 메모리의 바이트 수입니다.

mysql> SHOW ENGINE INNODB STATUS;

위의 내용은 MySQL의 INSERT BUFFER와 ADAPTIVE HASH INDEX에 관한 정보를 보여주고 있습니다. 각 부분들을 해석해보겠습니다.

INSERT BUFFER (삽입 버퍼):
- Ibuf는 삽입 작업에 사용되는 버퍼입니다.
- 현재 버퍼 크기는 1이며, 빈 리스트는 없고, 세그먼트 크기는 2입니다.
- 0번의 병합 작업이 있었으며, 병합된 작업은 모두 삽입 작업입니다.
- 버린 작업은 없습니다.
ADAPTIVE HASH INDEX (적응형 해시 인덱스):
- 해시 테이블의 크기는 2267이며, 노드 힙에 버퍼가 없습니다.
- 0.07 해시 검색/초, 0.85 비-해시 검색/초의 속도로 작업이 수행되고 있습니다.

마무리

이번글에서는 세미나 결제서비스에서 사용되는 쿼리들의 실행 계획을 확인해보고, 인덱스를 추가함으로써 더욱 빠르게 접근할 수 있도록 처리했습니다.
이번 글에서는 아쉽게도 단순 인덱스 처리를 통해서만 쿼리를 튜닝했는데요, 다음에는 세미나허브 시스템의 기능을 활용하여 직접적으로 SQL 튜닝을 진행해보도록 하겠습니다.

참고

real mysql 8.0

'Spring Boot > Project' 카테고리의 다른 글

[Spring][Seminar-hub] 3편. 장바구니 결제 기능에서 발생하는 DeadLock 170.27% 개선시키기 ( 원형대기예방 ) (0)	2024.03.04
[Spring][Seminar-hub] 2편. 세미나 허브 결제서비스에 1000명이 동시에 세미나 신청한다면? ( 트랜잭션 격리레벨, 배타락, 언두로그 ) (0)	2024.03.04
[Spring][Seminar-hub] 1편. 세미나허브에 결제서비스 개발해보기 (0)	2024.02.08
[Spring Boot][Seminar-hub] AOP(Aspect Oriented Programming) 활용하여 함수 실행시간 로깅 처리해보기 (0)	2023.12.18
[Spring Boot][Seminar-hub][ELK] Filebeat, Logstash, Elasticsearch를 활용한 로그 수집 및 분석하고 Kibana로 로그 시각화 (0)	2023.11.04

passionfruit200