TIL [0531 - 0604] : 대용량 처리를 위한 MySQL 이해

강의

패스트캠퍼스 - [스페셜] 백엔드 개발자를 위한 한 번에 끝내는 대용량 데이터 & 트래픽 처리 초격차 패키지 Online

 

source : https://github.com/koogk7/fastcampus-mysql

강의를 듣고 정리한 글입니다.

 

 

 

chapter1, chapter2의 강의 내용은 여기로 -> https://shout-to-my-mae.tistory.com/260

TIL 0417 - 0423

 

 

MYSQL을 학습하는 이유

여러 db 종료들

• 데이터베이스 랭킹 1~4위가 모두 관계형 데이터베이스

✅ 관계형 데이터베이스는 아직까지도 가장 범용적으로 사용된다. - 실무에서 다룰 확률이 높다.

 

• 그 중에서도 MYSQL은 가장 인기 많은 관계형 db이다.
  • 높은 접근성과 낮은 비용
  • 네이버, 카카오, 토스 등 대부분의 국내 IT 기업에서 가장 많이 사용된다.
  • sql 안식 표준을 지키고 있다.

 

MYSQL 아키텍처 소개

데이터베이스

파일을 관리하는 서버

 

전달한 SQL 요청은 데이터를 탐색하고 결과를 돌려준다.

MYSQL 엔진: 판단과 명령을 하는 두뇌

스토리지 엔진: 판단을 수행하고 동작하는 팔과 다리

 

MYSQL 엔진

쿼리 파서, 전처리기, 옵티마이저, 쿼리 실행기로 구성되어있다.

 

• 쿼리 파서
  • SQL을 파싱하여 Syntax Tree를 만듬
  • 이 과정에서 문법 오류 검사가 이루어짐
• 전처리기
  • 쿼리 파서에서 만든 Tree를 바탕으로 전처리 시작
  • 테이블이나 컬럼 존재 여부, 접근권한 등 Semantic 오류 검사

✅ 쿼리파서, 전처리기는 컴파일 과정과 매우 유사하다.

✅ 하지만 SQL은 프로그래밍 언어처럼 컴파일 타임때 검증할 수 없어 매번 구문 평가를 진행한다. (서버에서 동적으로 SQL문 전송)

 

• 옵티마이저
  • 쿼리를 처리하기위한 여러 방법들을 만들고, 각 방법등릐 비용정보와 테이블의 통계정보를 이용해 비용 산정
  • 테이블 순서, 불필요한 조건 제거, 통계정보를 바탕으로 전략 결정(실행 계획 수립) => 가장 최적의 방법으로 전략 결정
  • 옵티마이저가 결정한 전략에 따라 성능이 많이 달라진다.
  • 옵티마이저가 가끔씩 성능이 나쁜 판단을 할 수 있는데, 이 때 개발자는 힌트를 통해 도움을 줄 수 있다.

쿼리 실행기

• 쿼리 실행기
  • 옵티마이저가 결정한 계획대로 Handler API를 이용해 스토리지 엔진에 요청

⭐️ Handler API

스토리지 엔진에 요청 = 핸들러 요청

핸들러 API를 만족하는 스토리지 엔진을 구현할 수 있다면, 구현체를 스토리지 엔진으로 추가하여 사용할 수 있다.

 

쿼리캐시가 주는 인사이트

MYSQL 쿼리 캐시

쿼리 캐시 : SQL에 해당하는 데이터를 저장

✅ 쿼리 캐시는 데이터를 캐시하기때문에 테이블의 데이터가 변경되면 캐시의 데이터도 함께 갱신시켜줘야함.

 

- MYSQL 5.0까지는 쿼리 캐시가 있었지만 8.0에 들어와서 폐기됨

😯 이점보다 문제점이 더 크다고 판단되었기 때문

 

- MySQL에는 소프트 파싱이 없다. 

 

Oracle 소프트 파싱

소프트 파싱 : SQL, 실행 계획을 캐시에서 찾아 옵티마이저 과정을 생략하고 실행 단계로 넘어감

하드 파싱: SQL, 실행 계획을 캐시에서 찾지 못해 옵티마이저 과정을 거치고 나서 실행단계로 넘어감

 

- Oracle에는 소프트 파싱이 존재

- 실행계획까지만 캐싱

- 모든 SQL과 매핑하여 데이터까지는 캐싱하지 않음(힌트나 설정으로 가능하긴 함)

 

MySQL의 쿼리캐시, Oracle의 소프트 파싱

• 공통점: 성능 최적화를 위해 캐시 기술 도입
• 차이점: 캐시의 범위
  • MySQL: SQL에 해당하는 데이터 저장 (실행 결과 캐싱)
  • Oracle: 실행계획까지만(옵티마이저 과정까지) 캐싱 - 실행 단계는 직접 수행
• 쿼리캐시는 소프트 파싱에 비해 조회 성능이 더 높다. 그러나 캐시 데이터 관리에 더 높은 비용이 들어간다.(db 데이터 변경시마다 갱신)
  • 소프트 파싱은 그 반대

• 모든 기술은 트레이드 오프다.
  • 캐시 범위에 따라 조회와 쓰기 성능의 가중치가 달라지고 trade-off 되어 성능에 영향을 준다.
    • 조회가 잘 되려면 구체적인 데이터를 가지고 있어야하므로 많이 써야한다(갱신되어야한다 = write 수 많음)
    • 쿼리 캐시는 조회 성능을 키움
    • 소프트 캐싱은 쓰기 성능도 가중치를 둠

 

 

스토리지 엔진

• 디스크에서 데이터를 가져오거나 저장하는 역할
• MYSQL 스토리지 엔진은 플러그인 형태
  • Handler API만 맞춘다면 직접 구현해서 사용할 수 있다.
• InnoDB, MyIsam 등 여러개의 스토리지 엔진이 존재
• 8.0대부터는 InnoDB 엔진을 디폴트

 

InnoDB 핵심 키워드

Clustered Index, Redo - Undo, Buffer pool

 

SNS 모델링으로 배우는 정규화 / 비정규화

정규화 / 비정규화란 무엇인가

정규화

중복을 최소화하게 데이터를 구조화하는 프로세스

🦋 중복을 제거하고 한 곳에서만 데이터를 관리

=> 데이터 변경이 일어날때 데이터 부정합이 일어나지 않는다는 장점

 

🦋 읽을 때는 항상 원본 데이터를 참조해야한다는 단점

 

👾 정규화 개념 이해하기

소설을 다 쓴 후 주인공의 이름을 바꾸어야한다면?

주인공의 이름을 {주인공이름집.1page.이름}으로 작성해두면 이름 변경시 주인공이름집만 변경하면 된다.(중복 최소화)

 

=> 테이블 설계 관점에서 조회와 쓰기 사이의 트레이드 오프

정규화를 잘하면

- 데이터 변경시 쓰기할 영역이 줄어든다(참조하는 곳만 바꾸면 된다), 

- 읽을때는 매번 원본 데이터를 참조해야하므로 조회를 자주 해야한다.

 

비정규화(반정규화)

읽기의 성능을 높이기위해 쓰기의 성능을 감소

 

ex)

{주인공이름집.1page.이름} -> 철수

 

정규화 vs 비정규화

정규화	반정규화(비정규화)
중복을 제거하고 한 곳에서 관리	중복을 허용
데이터 정합성 유지가 쉬움	데이터 정합성 유지가 어려움
읽기시 참조 발생	참조없이 읽기 가능

 

실습

회원정보 등록 구현

회원정보 요구사항

 

프로젝트 구조

 

record

java 16부터 공식 기능

getter,setter 자동으로 만들어주고 getter를 property로 사용 가능 (~~.nickname)

entity

@builder

lombok에서 지원하는 객체 생성 방법

생성자의 필드 순서를 알 필요없이 필드명을 통해서 새로운 객체를 만들 수 있다.

 

https://velog.io/@kshired/Spring-Lombok-Builder-%EA%B8%B0%EB%B3%B8%EA%B0%92%EC%97%90-%EA%B4%80%ED%95%98%EC%97%AC

[Spring] Lombok @Builder 기본값에 관하여

 

entity - Member

 

Service

read와 write를 분리하여 코드를 작성하면 좋다.

@RequiredArgsConstructor를 사용하면 final이나 NotNull인 필드의 생성자 주입을 해준다.

 

repository ( 틀만 만들기)

빈 Member객체를 반환할 것이므로 에러가 발생할 것이다. return member로 고쳐주면 에러가 안뜬다.

 

controller - restapi

 

java: records are not supported in -source 11 (use -source 16 or higher to enable records) 에러

 

record는 java16부터 공식으로 지원되기 시작했다.

build.gradle에서 sourceCompatibility를 16으로 지정하면 된다.

물론 그 전에 jdk 16이 설치되어있어야한다.

 

swagger에서 API 살펴보기

링크 : http://localhost:8080/swagger-ui/index.html#/hello-world-controller/helloWorld

 

swagger를 이용하면 편리하게 api spec과 테스트를 할 수 있다.

 

debug 모드로 실행

run - debug 모드로 실행하면 에러가 발생했을때 에러발생위치를 볼 수 있다.

 

예시 ) debug 모드로 실행하고 /members로 post요청을 보낸다.

 

repository 코드 생성 전,

 

IntelliJ에서 Member 테이블 만들기

navigation-jump to Query Console-open default

database 탭을 누르고 수행한다.

 

console에서 mysql 코드 실행하기

만들어진 테이블을 확인할 수 있다.

 

repository 코드 완성

JdbcTemplate을 이용하여 구현한다. JPA 인터페이스에 맞춘다.

 

SimpleJdbcInsert

sql을 직접 작성하지 않아도 된다.

primary key(PK) 자동 생성이 가능하다.

 

BeanPropertySqlParameterSource

빈 객체를 기반으로 파라미터를 담아 쿼리를 날린다.

 

https://velog.io/@lacomaco/Spring-Jdbc-Template-%EC%8B%A4%EC%8A%B5-%EC%A0%95%EB%A6%AC#beanpropertysqlparametersource

Spring Jdbc Template 실습 정리

 

회원 정보 등록 구현

update는 나중에

 

테스트

swagger로 등록 요청 보내기

응답 200 (OK)

잘 전송된다.

 

DB에서 확인하기

 

잘 저장되었다.

 

회원정보 조회 구현

MapSqlParameterSource

지정된 매개 변수 맵을 보유하는 SqlParameterSource 구현입니다. 이 클래스는 매개 변수 값의 간단한 맵을 NamedParameterJdbcTemplate 클래스의 메서드에 전달하기 위한 것입니다. 이 클래스의 addValue 메소드를 사용하면 여러 값을 더 쉽게 추가할 수 있습니다. 메서드는 MapSqlParameterSource 자체에 대한 참조를 반환하므로 단일 문 내에서 여러 메서드 호출을 함께 연결할 수 있습니다.

 

repository 코드

rowMapper 말고 BeanPropertyRowMapper를 사용하면 매핑 로직을 없앨 수 있다.

하지만 BeanPropertyRowMapper는 모든 필드에 대해 setter를 열어야한다.

 

setter는 필요할때만 열자 - 어디서든 변경이 가능하므로 변경될때 side effect 추정이 어렵다.

setter를 직접 여는 것보다는 의미있는 동작 단위로 묶어서 제공하는 것이 좋다.

 

readService 만들기

controller - 조회 api

 

domain 객체 반환

- JPA에서 컨트롤러까지 entity객체가 나온다면 OSIV 이슈 발생 가능

- presentation의 요구사항에 entity 변경 가능..

=> dto 만들기

 

DTO 만들기 

memberDTO

record로 생성

 

Service 코드 변경 - memberDto 반환으로 변경

Controller 코드 변경 - memberDto 반환으로 변경

 

API 테스트

 

응답 결과

good

닉네임 이름 변경 

object의 메서드

객체의 데이터는 객체안에서만 관리가능하는 것이 좋다. => 변경 사항이 많아졌을때 side effect 파악 쉬움

 

 

changeNickname

 

Builder 사용시 객체 생성마다 겹치는 코드 =>

해결방법 - ObjectMother : 테스트 필요한 객체를 만들어준다.

 

EasyRandom

test시 랜덤한 java bean 만들어주는 라이브러리

값을 랜덤하게 가지고 있는 객체를 만들어준다. 

⭐️ test 폴더 안에서 사용 ⭐️

✅ 값을 다르게 랜덤으로 생성하고싶으면 시드를 다르게 해주어야한다.

10개의 랜덤값을 가진 Member 객체 생성

 

출력

nickname

이름 변경 테스트

통과

 

이름 길이 테스트

결과

회원 이름 변경 내역 저장 구현

변경 내역을 저장하는 코드를 만들기 위해 memberRepository의 save 함수 부분을 보자.

 

새로운 id이면 insert하고, 그렇지 않으면 update한다.

 

update 함수는 틀만 작성했으므로

member를 갱신하고 변경 내역을 저장하는 메서드를 작성하자.

 

update 메서드 - member 갱신

 

controller

결과

갱신이 잘 된다.

 

db에서 확인하기

잘 갱신된다.

 

닉네임 변경 내역 저장하기 - MemberNicknameHistory

 

(정규화 : 중복 최소화)

정규화의 대상

정규화를 하려고 할때 필드 이름이 같다고 중복이 아니다.

과거의 정보를 가지고있어야하는 ⭐️히스토리성 데이터는 정규화의 대상이 아니다⭐️

✅ 정규화를 하려는 대상이 항상 데이터의 최신성을 보장해야하는가?를 고려

 

❄️ 이커머스에서 주문상품의 제조사 식별자를 남긴다. 만약 제조사의 이름이 바뀌었다면 주문 내역에 제조사의 바뀐 이름이 들어가야할까?

아니면 과거 제조사의 이름으로 남겨야할까?

 

: 비즈니스 정책에 따라 다르다. 고려해야할 점은 과거의 기록을 그대로 남겨야하는지, 최신성을 보장해야하는지이다.

기획자에게 가서 요구사항 파내기

 

MemberNicknameHistory 엔티티 생성

변경내역 저장할 테이블 생성

repository 생성 - MemberRepository와 유사하다.

service

@RequiredArgsConstructor
@Service
public class MemberWriteService { //read와 write를 분리하여 코드 작성
    final private MemberRepository memberRepository;

    final private MemberNicknameHistoryRepository memberNicknameHistoryRepository;

    public Member create(RegisterMemberCommand command){
        /*
        목표 - 회원정보(이메일, 닉네임, 생년월일)을 등록한다.
        - 닉네임은 10자를 넘길 수 없다.
        파라미터 - memberRegisterCommand
        val member = Member.of(memberRegisterCommand)
        memberRepository.save(member)
         */
        var member = Member.builder()
                .nickname(command.nickname())
                .email(command.email())
                .birthday(command.birthday())
                .build();
        Member savedMember = memberRepository.save(member);
        //초기 이름 저장
        saveMemberNicknameHistory(savedMember);
        return savedMember;
    }

    public void changeNickname(Long memberId, String nickname){
        /*
        1. 회원의 이름 변경
        2. 변경 내역 저장
         */
        var member = memberRepository.findById(memberId).orElseThrow();
        member.changeNickname(nickname);
        memberRepository.save(member);
        //변경 이름 저장
        saveMemberNicknameHistory(member);

    }

    private void saveMemberNicknameHistory(Member member) {
        var history = MemberNicknameHistory.builder()
                .memberId(member.getId())
                .nickname(member.getNickname())
                .build();
        memberNicknameHistoryRepository.save(history);
    }


}

초기 이름, 변경 이름 저장

 

닉네임 변경 내역 조회

repository - memberId로 찾기

 

readService

 

entity 바로 반환하면 안좋으므로 dto 만들어서 바꿔주기

 

dto 만들기

readService - 닉네임 변경내역 조회시 dto 반환하도록 수정

controller

 

테스트

잘 된다.

 

변경 내역 조회

 

팔로우 등록, 조회 구현

Follow 엔티티 생성

Follow Table 생성

sql console

Repository

정규화

1. 정규화 여부 - 정규화가 필요한가?

팔로우 : 항상 최신의 데이터가 필요 => 정규화 수행하기

 

2. 정규화 수행 과정 - 연관된 데이터를 어떻게 가져올 것인지

Member와 Follow는 연관되어있다. - Follow시에 Member 정보 가져오기

 

데이터 가져오는 방법

1. join

가능하면 미루는게 좋다.

join한 두 테이블간의 결합성이 높아짐

☑️  유연성있는 아키텍처, 시스템이 되기 어렵다..

☑️ 아키텍처로 문제를 풀기 어렵다.

☑️ 리팩토링이 어렵다.

 

ex) Follow에서 Member를 조인하게되면, Follow 서비스에 Member가 침투하게 된다.

두 도메인과의 엄청난 결합이 이루어진다..

 

2. 쿼리 한번 더 날리기 -> 우리가 사용할 방법

3. 별도 데이터베이스(저장소) 이용

등등

 

Service 

member의 id로 Follow를 생성하면 member의 서비스와 레포지토리를 주입받아야하기때문에 결합이 심해진다..좋은 방법은 아님

=> MemberDto로 받기!

MemberDto는 어느 곳에서 주입받을까? 고민..

 

서로 다른 도메인의 정보를 주고 받을때 설계 방법

• 헥사고날 아키텍처(Hexagonal Architecture)
• DDD(도메인 주도 설계)
• layered architecture

경계간의 통신을 하는데 여러 방법이 있다.

우리가 채택한 방법

단순한 구조를 사용하자.

application 계층안에 usecase 계층을 둔다.

usecase 계층은 여러 domain간의 흐름을 제어하는 역할을 한다.

☑️ 여기서는 Member와 Follow간의 흐름을 제어하는 역할로 사용할 것이다.

 

package와 class 만들기 - CreateFollowMemberUsecase

 

Usecase

usecase마다 기능 하나를 한다. - execute

usecase는 가능한 로직이 없어야한다. => 도메인 서비스의 흐름만 제어하는 역할 수행

 

✅ MemberReadService만 주입한다 => Member에 대해 읽기 권한만 가짐(쓰기 권한X) 👍 

 

FollowWriteService

Usecase에서 받은 정보로 Follow를 생성한다.

FollowController

 

테스트

결과

DB

 

Follow 조회하기

usecase 생성

service 만들기전에 서비스에서 사용할 repository의 조회 코드 만들기

FollowRepository

Follow 테이블에서 fromMemberId 조회 -> 한 member의 Follow하는 사람들을 반환

Followservice

Follow의 리스트를 반환한다.

 

✅반환받은 Follow들의 Member를 MemberDto로 반환해야한다. => 메서드 작성

 

memberRepository - Member의 id 리스트로 받으면 Member entity 리스트로 반환

멤버 조회하기

☑️ ids가 빈 리스트일때 오류 발생가능하다. => 리스트 사용시에는 항상 빈 리스트 경우 생각해보기

(뒤에서 해결)

 

MemberService - memberId 리스트를 입력받아 MemberDto로 반환

 

usecase 완성

member가 Follow하는 Member들을 조회하여 MemberDto로 반환

 

 

controller 만들기

 

테스트

결과

팔로우 하는 사람들의 MemberDto가 잘 반환된다.

 

팔로우 하는 사람이 없다면? 에러처리하기

어떤 member가 아무도 다른 사람을 팔로우하지않았다면? -> 빈 리스트가 반환되어 에러 발생

빈 리스트일때는 sql 실행하지않고 바로 빈 리스트만 반환되도록 처리

 

빈 리스트일때 결과

잘 나온다.

 

실무에서의 정규화, 비정규화에 대한 고민들

중복된 데이터이면 반드시 정규화를 해야할까?

정규화도 비용이다. 읽기 비용을 지불하고 쓰기 비용을 줄이는 것이다.

join을 하며 계속해서 원본 데이터를 찾아가야함

 

정규화시 고려해야하는것

데이터 양이 많아지면 매번 참조 => 부하 발생..

데이터 일관성, 최신성을 덜 보장해도 된다면 읽기 성능 향상 가능(대부분 웹 서비스는 읽기의 비율이 쓰기보다 압도적으로 높다)

 

변경주기가 빈번하다면 정규화가 유리

반대의 경우 조회의 이점 가져가기

 

 

객체(테이블)의 탐색 깊이

A에서 D까지 참조하려면 A->B->C->D 3 depth이므로 A->B->D 위치에 D를 두면 참조하기 쉽다.

하지만 C->D 수정시에 A->B->D의 D도 수정되어야한다.

 

즉 읽기 성능을 위해서 쓰기 성능을 낮추게 된다.(계속 변경되어야하므로)

 

정규화- 데이터 가져오기

 

조회 최적화를 위한 인덱스 이해하기

데이터베이스 성능 핵심

메모리 vs 디스크 비교

데이터베이스의 데이터는 결국에 디스크에 저장되어야한다. (디스크의 영속성)

 

데이터베이스의 성능 핵심은 디스크 접근(I/O)를 최소화하는 것

• 메모리 캐시 히트율을 높인다.
  • 메모리에 올라온 데이터로 최대한 요청 처리
• 값을 쓸때 디스크에 쓰지않고 메모리에 먼저 쓴다.

✅ 메모리의 데이터 유실을 고려하여 WAL(Write Ahead Log)를 사용한다.

랜덤 I/O vs 순차 I/O

순차 I/O : 연속된 데이터를 순차적으로 읽기

 

✅ 대부분의 트랜잭션은 랜덤 I/O(무작위하게 Write 발생)

이를 지연시켜 랜덤 I/O 횟수를 줄이는 대신 순차적 I/O를 발생시켜 정합성을 유지

WAL(Write Ahead Logging)

파일에 순차적으로 실행한 로그가 남는다. 메모리의 데이터가 디스크에 저장되지 않고 유실되더라도,

파일에 순차적인 로그를 재실행하여 디스크에 있는 원본 데이터와 같아진다.

 

트랜잭션이 일어나기 전에 로그를 미리 기록하여 트랜잭션 undo, redo 를 할 수 있다.

출처: https://eyeballs.tistory.com/514

[SQL] WAL 이란 무엇인가?

 

 

로그 파일의 끝부분부터 순차적으로 write 수행 => 순차 I/O를 발생한다.

 

즉, 데이터베이스의 성능 핵심은 디스크 랜덤 접근(I/O)를 최소화하는 것

 

인덱스 자료구조

인덱스

인덱스의 핵심은 탐색(검색)범위를 최소화하는 것

 

검색이 빠른 자료구조? => Hash Map, List, BST..

 

HashMap

List

정렬시에는 바이너리 서치 이용

 

Tree

한쪽에 치우친 트리는 리스트와 같은 시간복잡도.. 균형 맞추는 트리 사용

 

B+ Tree

B+ Tree는 Red-Black 트리가 Binary Tree인 것과 달리 노드 하나가 여러개의 자식 노드를 가질 수 있다. (B tree도 동일)

 

B Tree vs B+ Tree

B트리: 각 노드가 데이터

B+ 트리

- 리프 노드에만 데이터 존재, 위의 노드들은 데이터를 찾아나기위한 키

    => 연속적인 데이터일 경우 리프노드만 찾아가면 된다.

- 노드 접근시 각 데이터를 직접 보는 것이 아니므로 데이터 reference 횟수도 적다.

 

=> 대부분의 IDMS의 index는 Red-Black Tree가 아닌 B+ Tree를 사용한다.

 

B+ Tree 시각화 링크

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

B+ Tree Visualization

Cherry 찾기

해당 인덱스 찾았을때

mySQL : PK 가짐, 해당 PK 인덱스로 이동하여 데이터 찾기

(PK의 크기 중요)

Oracle: 해당 데이터 주소 가짐 -> 데이터 찾기

 

index는 조회 성능은 높이지만 쓰기, 갱신 성능은 낮춘다.

 

클러스터 인덱스

 

클러스터 인덱스는 데이터 위치를 결정

클러스터 키 4 삽입

클러스터키는 정렬을 이루고, 정렬된 순서에 따라(클러스터 키 위치에 따라) 데이터의 주소가 결정된다.

5-D였지만 중간에 4가 삽입되어 4-D, 5번이 밑의 데이터 주소로 밀린다. => 데이터의 주소가 바뀌었다.

 

클러스터 키가 중간에 삽입되면 삽입 이후의 데이터는 다 밀린다.

 

MySQL의 PK는 클러스터 인덱스이다.

클러스터 인덱스

• 인덱스 자체의 리프 페이지가 곧 데이터이다. 즉 테이블 자체가 인덱스이다. (따로 인덱스 페이지를 만들지 않는다.)
• 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지한다.

넌클러스터 인덱스

• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지한다

출처 : https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC

[데이터베이스] 클러스터 인덱스와 넌클러스터 인덱스/ 개념 총정리

 

✅PK로 AutoIncrement vs UUID 장단점찾아보기 

성능 이슈 존재

 

MySQL에서 PK를 제외한 모든 인덱스는 PK를 가지고 있다.

PK의 사이즈가 인덱스 사이즈 결정

인덱스를 만들때마다 PK가짐 => PK의 사이즈가 인덱스 테이블의 사이즈 결정

 

인덱스 테이블 자체만으로도 요청 처리 가능

 

👽 왜 인덱스는 데이터 주소가 아닌 PK를 가지고있을까?

만약 인덱스가 데이터 주소를 가지고있다면, PK 변경시마다 데이터 주소도 변경되어 인덱스도 갱신되어야한다. => 부하존재

인덱스가 PK를 가지고있다면, 데이터 위치가 변경되더라도 PK로 변경위치 참조 가능

=> 그러므로 MYSQL 인덱스들을  PK를 가지고 있다.

 

-  PK인덱스를 통해서만 데이터를 찾아갈 수 있다. (PK가 아닌 보조인덱스로는 데이터 검색X)

보조인덱스 -> PK인덱스 -> 데이터 찾는 과정 수행된다.

 

 

보조 인덱스로 PK 인덱스 찾음

인덱스키 로 정렬되어있다.

PK인덱스로 넘어온 이후

보조인덱스들을 PK를 거친다.

보조 인덱스의 리프노드들은 PK를 가지고있다.

 

내가 쓴 글 캘린더 구현

 

게시글 entity 생성

repository

✅ 코드 작성시 가정한 내용을 녹이는게 좋다.

같은 post를 post할때는 예외상황 => throw Exception

 

PostWriteService

게시글 작성 메서드

 

PostCommand

입력 dto이다. id와 contents를 입력받는다.

 

controller 만들기

입력 요청으로 post를 작성한다.

 

테스트

결과

DB 결과

 

 

회원인증-Spring Security, OAuth도 나중에 해보기

 

입력

출력

 

JAVA의 Record

불변 데이터 객체를 쉽게 사용가능, 훨씬 간결한 형식으로 작성이 가능하다.

자세한 내용은: https://scshim.tistory.com/372

[Java] 자바의 레코드(Record)

 

PostReadService 

틀만 작성한다.

 

PostRepository

특정 member가 작성한 해당 날짜 범위의 Post를 조회하여 개수를 센다(count)

 

service

 

controller 작성

내 경우에는 getMapping으로 할 경우 DailyPostCountRequest가 안넘어갔기때문에 @PostMapping으로 해주었더니 됐다. 😼

 

테스트

결과

 

성능 테스트를 위한 게시물 벌크 인서트 구현

100만건 데이터 레코드 삽입 

✅ application.properties의 db 설정 해주기!

 

PostBulkInsertTest

틀 만들기

 

factory 만들기

 

테스트 - 10건 테스트

결과

 

테스트 - repo에 저장까지

결과

 

application.json

spring.datasource.url=jdbc:mysql://${url}?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999&characterEncoding=UTF-8&serverTimezone=Asia/Seoul

쿼리 찍히는 것을 볼 수 있다.

 

모아서 한번에 보내기

postRepository

 

test

 

 

결과

 

 

✅ JPA의 saveAll은 루프를 돌며 저장하기때문에, jdbcTemplate을 이용해 bulkInsert (모아서 한번에 insert)를 구현한다.

 

백만건의 요청 보내기

100만건의 Post를 만드는데 시간이 걸리므로, 병렬을 사용한다.

 

설정사항

 

메모리 indicator 사용

command+shift+a : memory indicator 사용하기(ON)

 

메모리 크기 확인

이정도면 백만건 잘 돌아간다.

테스트 시 힙 공간이 부족하다고 뜨면 VM 옵션 바꿔주기 (IntelliJ 상에서만 적용)

Preferences - Gradle- Run test using: IntelliJ IDEA 로 설정해야 적용된다.

 

활성 상태 보기(윈도우의 작업관리자)

 

cpu, memory 사용량 확인 가능

 

top 명령어 사용

결과

자세하게 볼 수 있다.

 

postRepository

package com.example.fastcampusmysql.domain.post;

import com.example.fastcampusmysql.domain.post.entity.Post;
import com.example.fastcampusmysql.domain.post.repository.PostRepository;
import com.example.fastcampusmysql.domain.post.service.PostReadService;
import com.example.fastcampusmysql.domain.post.service.PostWriteService;
import com.example.fastcampusmysql.util.PostFixtureFactory;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.util.StopWatch;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.stream.IntStream;

@SpringBootTest
public class PostBulkInsertTest {
    @Autowired
    private PostRepository postRepository;

    @Autowired
    PostWriteService postWriteService;

    @Test
    public void bulkInsert(){
        var easyRandom = PostFixtureFactory.get(3L,
                LocalDate.of(2022, 1,1),
                LocalDate.of(2022,2,1)
                );

        var stopWatch = new StopWatch();
        stopWatch.start(); //시간 재기 시작

        int _1만 = 10000;
        var posts = IntStream.range(0, _1만*100)
                .parallel()
                .mapToObj(i -> easyRandom.nextObject(Post.class))
                .toList();

        System.out.println("객체 생성 시간 : " + stopWatch.getTotalTimeSeconds());
        var queryStopWatch = new StopWatch();
        queryStopWatch.start();

        //post 리스트를 받아 한번에 insert
        postRepository.bulkInsert(posts);

        queryStopWatch.stop();
        System.out.println("DB 인서트 시간 : " + queryStopWatch.getTotalTimeSeconds());

    }
}

 

결과

백만건의 요청이 잘 처리되었다.

 

API부터 부하 가능하다.

이건 가장 쉬운 구현..부하 테스트를 최적화할 수 있다. API 부하 테스트도 해보기

 

DB 확인

테스트 3번 수행했으므로 3만 건 이상이 있을것이다.

결과

인덱스 추가 후 성능 비교

조회 테스트

결과

한 쿼리에 2초정도 걸리고 cpu가 쿼리 하나때문에 100프로 넘게 차버리면 대형사고이다.. 고치기!

 

explain 

optimizer가 얼마나 많은 필드를 접근했고 쿼리 날렸는지 확인 가능

type:ALL // 테이블의 모든 데이터 접근

rows:398만.. //거의 모든 데이터를 접근했다.

=> index를 걸어주면 rows를 줄일 수 있다.

 

실제 3번의 데이터 개수 확인

실제 데이터 개수는 300만 건인데 거의 100만건을 더 접근하고있다.

 

데이터 분포 확인

unique한 값들이 몇개 있는지 확인

 

 

인덱스 생성하기

결과

 

member_id 인덱스 사용

결과

9초가 넘게 걸린다..

 

explain으로 확인

인덱스를 주지 않았을때보다 시간이 훨씬 걸린다..

 

이유

member id는 2번, 3번 아니면 4번인데,

3번에 해당하는 3백만 건의 데이터는 인덱스도 보고 테이블도 봐야한다.

✅ 인덱스가 존재하므로 인덱스 테이블과 물리 테이블 둘 다 봐야한다. 인덱스가 탐색 범위를 좁혀주지 못해 시간이 더 걸린다.

 

만약 1번을 조회했다면 데이터가 존재하지않으므로 굉장히 빠른 조회 시간을 가진다

✅ 데이터의 분포도에 따라 쿼리 시간이 달라진다.

 

createdDate 인덱스 사용

결과

총 400만건의 데이터 중에서 createdDate는 33개의 종류가 있다.

탐색범위가 넓은 상황에서 id가 4인 데이터만 가져온다. => 많은 시간이 걸린다.

 

복합 인덱스

과일로 정렬하고 원산지로도 정렬한다

 

 

member_id_created_date 인덱스 생성

 

member_id_created_date  인덱스 사용

결과

굉장히 빠르다.

member_id로 정렬하고 created_Date로 정렬한 후 조회

 

memberId 몇번으로 조회해도 빠른 속도를 보인다.

 

인덱스 사용시 고려사항

✅ 데이터 분포도

✅ 어떤 조건문이 group by, order by..등등에 들어가는지 확인

✅ explain으로 인덱스 어떻게 풀리는지 확인하기

 

 

인덱스를 다룰 때 주의해야 할 점

 

인덱스 필드 가공

인덱스를 가공하게 되면 인덱스를 탈 수 없다.

age에 10을 곱하여 컬럼값 변경 => index 탈 수 없다.

B Tree의 key값으로만 인덱스 검색 가능 (*10이면 검색 안됨)

 

마찬가지로 type이 달라도, B Tree의 key값과 다르므로 인덱스를 탈 수 없다.

 

 

복합 인덱스

과일 - 인덱스 타기 가능

과일,원산지 - 인덱스 타기 가능

원산지 - 인덱스 타기 불가

                                                                                                                                                                                                             

하나의 쿼리에는 하나의 인덱스

where문이 여러개이고, 관련 인덱스가 여러개여도

하나의 쿼리에는 하나의 인덱스만 탄다. 

여러 인덱스 테이블을 동시에 탐색하지 않는다.

 

✅ WHERE, ORDER-BY, GROUP-BY 혼합해서 사용시 인덱스 고려하기

=> 인덱스가 where은 탈 수 있지만 order-by를 탈 수 없을땐 재정렬해야한다.

 

 

추가 팁

                                                                                                                                                                                                              인덱스 대신 별도의 db, 조회 쓰기 모델 분리, 캐싱 등등 사용 가능하다.

+

인덱스 사용시 데이터 식별 정도가 높은 값으로 설정하자.

ex) 성별은 2가지밖에 없기때문에 탐색범위가 절반..

 

=> 탐색 범위를 많이 줄일 수 있는 것으로 인덱스 설정하기