Mysql architecture 에 대해서 한번 알아보자

Mysql은 머리 역할을 하는 Mysql Engine과 손발 역할을 하는 Storage Engine으로 구분 할 수 있다. 

이번 포스팅에서는  Mysql server에서 제공되는 Mysql Engine과 Storage Engine을 알아보자. 

Mysql Server는 다른 DBMS에 비해 구조가 독특하다. 우리가 체감하기는 쉽지 않지만 이러한 구조 때문에 다른 DBMS에서는 가질 수 없는 엄청난 혜택을 누릴수도 있고, 반대로 다른 DBMS에서는 전혀 문제가 되지 않는 것이 문제가 될수도 있다.

아래 사진은 Mysql의 전체 구조이다. 이제 하나씩 알아보자

Handler API(Connection Handler)

• Mysql Engine의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이 요청을 Handler 요청이라고 하고 여기서 사용하는 API이기도 하고 Mysql Engine이 Storage Engine을 조정하기 위해 사용 하는 것이다. Handler API라고 한다.
• InnoDB 스토리지 엔진 또한 이 Handler API 를 이용해 Mysql Engine과 데이터를 주고 받기도 한다. 

위 사진은 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지를 표시한다.

Mysql Engine

• Mysql engine은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌 역할을 한다.
• Mysql Engine은 Handler API 통해 접속된 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 Connection Handler,SQL Interface, SQL parser , 전처리기, 쿼리에 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

쿼리 요청시 처리하는 세부 실행 구조에 대해서 알아보자

SQL Interface

• 사용자가 입력하는 SQL 쿼리를 MySQL로 전달하는 계층입니다. 이 인터페이스는 MySQL이 SQL 쿼리를 받아들이는 첫 번째 지점이다.

SQL Parser

• 쿼리 파서는 사용자 요청이 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어내는 작업이다.
•  위 과정에서 SQL 쿼리를 구문 분석(Parsing)하여 쿼리의 구조가 올바른지 확인하고, 이를 MySQL이 처리할 수 있는 구조로 변환한다.
• 만약 오류가 존재한다면 사용자에게 오류메시지를 전달한다.

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. (위 쿼리 파서는 문법에 대한 검증이고 이 과정은 구조에 대한 검증이다.)
• 내장 팜수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 들을 확인하는 과정을 전처리기에서 수행한다.
• 권한상 사용이 불가한 개체는 전처리기에서 걸러진다.

8.0에서는 제거된 MYSQL 서버의 캐시

• 캐시와 버퍼 (Cache and Buffer)는 실행된 쿼리나 데이터 페이지를 메모리에 캐싱하여 성능을 최적화한다. MySQL은 결과를 저장해 재사용할 수 있도록 쿼리 캐시 또는 버퍼를 사용한다.

하지만 Mysql 8.0부터는 캐시 기능이 완전히 제거되고 시스템 변수도 모두 제거되었다. 가져다 주는 이점보다 버그가 더 많았기 때문이다.

SQL Optimizer 

• 사용자의 요청으로 들어온 쿼리를 가장 효율적으로 실행할 수 있도록 최적화한다. 옵티마이저는 여러 가지 실행 계획을 비교하고, 가장 빠르게 쿼리를 실행할 방법과 저렴한 비용으로 처리할지 결정한다. 
• SQL 옵티마이저: 같은 의미로, SQL 쿼리를 최적화하는 데 사용되는 구성 요소이다.

Storage Engine

• Mysql Engine에서 SQL 문장을 분석하거나 최적화하는 등 처리를 수행하면 Storage Engine은 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 역할을 담당한다. Mysql 서버에서 Mysql Engine은 하나지만 Storage Engine은 여러 개를 동시에 사용할 수 있다.
• 스토리지 엔진에는 대표적으로 InnoDB storage Engine 이 있다. 이 부분은 다음 포스팅에서 조금 더 상세하게 정리를 할 예정이다.

💻프로시저(스토어드 프로그램)

프로시저는 일련의 쿼리를 하나의  함수처럼 실행하기 위한 쿼리의 집합이다.  매개 변수를 받을 수 있고, 반복적으로 사용할 수 있는  BLOCK이다.
일반적으로 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는  PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성한다.

💻프로시저(스토어드 프로그램)를 사용하는 이유

🔑데이터베이스의 보안의 향상

MYSQL의 스토어드 프로그램은 자체적인 보안 설정 기능을 가지고 있기 때문에 스토어드 프로그램 단위로 실행 권한을 부여할 수 있다. 이러한 보안 기능을 조합해서 특정 테이블의 읽기와 쓰기 또는 특정 칼럼에 대해서만 권한을 설정하는 등 세밀한 권한 제어가 가능하다.

주요 기능을 스토어드 프로그램으로 작성한다면 SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있을 것이다.

🔑기능의 추상화

일련번호와 같은 복잡한 방식으로 생성되는 경우 생성 방식이 복잡하여 , MYSQL의 Auto_increment를 이용할 수가 없다. 만약 애플리케이션에서 일련번호 생성용 모듈을 개발한다면 개발하는 언어별로 호환되지 않을뿐더러 직접 SQL 클라이언트에서는 사용이 불가능하다. 일련번호 생성용 프로그램을 MYSQL 서버의 스토어드 프로그램으로 구현한다면 애플리케이션이나 SQL 클라이언트에서도 쉽게 사용할 수 있다. 

🔑절차적 기능 구현 

DBMS 서버에서 사용하는 SQL쿼리는 절차적인 기능을 제공하지 않는다. 즉, SQL 쿼리에서는 IF나 WHILE과 같은 제어 문장을 사용할 수 없다. 그러나 스토어드 프로그램은 DBMS 서버에서 절차적인 기능을 실행할 수 있는 제어 기능을 제공한다. 가끔 SQL 문장으로 절대 처리할 수 없는 것들이 있다. 일반적으로 이런 상황에서는 데이터를 애플리케이션에서 가공한 후 다시 데이터 베이스에 저장하는 형태로 개발을 진행을 하지만  스토어드 프로그램을 이용해 절차적인 기능을 구현한다면 최소한 네트워크 경유에 걸리는 시간만큼은 줄일 수 있고 불필요한 애플리케이션 코드를 줄일  수 있다.

🔑개발 업무의 구분

순수하게 애플리케이션을 개발하는 조직과 DBMS 관련 코드를 개발하는 조직이 별도로 구분되어 있다면 DBMS코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스를 처리를 하는 스토어드 프로그램을 만들어 API처럼 만들어 제공하고 애플리케이션 개발자는 스토어드 프로그램을 호출해서 사용하는 형태로 역할을 구분해서 개발을 진행할 수도 있다.

🔑네트워크 소요시간 절감

일반적으로 애플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재하므로 SQL의 실행 성능에서 네트워크를 경유하는 데 걸리는 시간은 많은 신경을 쓰지 않는다. 그러나 하나하나 쿼리가 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간에서 이슈가 발생할 수 있다. 하나의 프로그램에서 100번, 200번씩 실행해야 하는 쿼리를 스토어드 프로그램으로 구현한다면 스토어드 프로그램을 호출할 때 한 번만 네트워크를 경유하면 되기 때문에 네트워크 소요 시간을 줄이고 성능을 개선할 수 있다.

💻프로시저(스토어드 프로그램)의 단점

🔨낮은 처리 성능 

스토어드 프로그램은 MYSQL 엔진에서 해석되고 실행된다. 하지만 MYSQL 서버는 스토어드 프로그램과 같은 절차적 코드 처리에 중점을 두고 처리하는 것이 아니라서 다른 프로그램 언어에 비해 성능이 떨어진다.

🔨애플리케이션 코드의 조각화

애플리케이션의 설치나 배포 작업이 갈수록 복잡해진다. 각 기능을 담당하는 프로그램 코드가 자바와 MYSQL스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 복잡해 서지고 유지보수 또한 어려워질 수 있다.

💻프로시저(스토어드 프로그램)의 사용 예제

1. 프로시저 생성

• DEFINER : 접근 권한
• PROCEDURE : 프로시저 이름
• 매개변수 + 타입
• SET 변수 설정
  • 기호가 없을 때 프로시저 실행이 끝나면 초기화
  • @가 있으면 프로시저가 끝나도 계속 유지
• CONCAT() : 문자열 합치기
• PREPARE : 실행 준비
• EXECUTE : PREPARE 된 것을 실행 

⚒프로시저 기본 형태

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`
BEGIN
  SELECT * 
  FROM 테이블명
END

DB에 접속하는 ID명을 적어준다. 여기에 적는 ID는 무조건 권한이 있는 ID를 넣어야 한다.

⚒프로시저 인자 값 및 파라미터를 받을 때

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`(
   IN 변수명 자료형   
   OUT 변수명 자료형 
)
BEGIN
    SELECT *
    FROM 테이블명
    WHERE 컬럼명 = 변수명
END

받을 변수명과 자료형 (INT, VARCHAR(40) 등)을 입력해준다. 받을 때는 IN을 사용하고 반환하는 변수의 자료형은 OUT

⚒프로시저 변수 할당

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`(
    IN 변수명 자료형
)
BEGIN
    SET @v_code = '123';
    SELECT *
    FROM 테이블명
    WHERE 컬럼 = 변수명 AND
    	  컬럼 = @v_code
EN

변수를 할당할 때 바로 값을 적어도 되지만 서브 쿼리를 사용하여 (@v_code = (select,,,,from...))할당할 수 있다.

⚒프로시저 조건문 사용하기 IF, ELSEIF, OR, AND..

CREATE DEFINER=`DB아이디`@`%` PROCEDURE `프로시저명`(
    IN 변수명 자료형
)
BEGIN
    SET @v_code = '123';
    IF @v_code!='' THEN
      SELECT *
      FROM 테이블명
      WHERE 컬럼 = 변수명 AND
    	    컬럼 = @v_code
    END IF;
END

IF 조건문 OR 조건문 THEN
실행 문구
ELSE IF 조건문 THEN
실행 문구
END IF;
등의 형태로 쓸 수 있으며
NULL 체크는 IS NOT NULL / IS NULL을 쓸 수 있다.

⚒프로시저 생성 예제

CREATE DEFINER='root'@'%' PROCEDURE test_prc(id int(10))
BEGIN
SET @t1 = CONCAT('SELECT * FROM test_', id);
PREPARE s FROM @t1;
EXECUTE s;
END

이런 식의 프로시저는 테이블 이름을 배열 변수에 담아서 LOOPING 돌려 한번에 처리 가능하다. 

​

REFERENCE

https://unlimitedcoding.tistory.com/15

https://velog.io/@ym1085/MySQL-%ED%94%84% EB% A1% 9C% EC% 8B% 9C% EC% A0%80% EB% 9E%80

MyBatis에서 choose 절을 활용한 동적 쿼리를 작성할 수 있다. 보통 특정 조건에 따라 쿼리의 형태가 다이나믹해지므로 MyBatis에서 많이 사용되는 방법으로 <choose>절이 있다.

<choose></choose>,<when></when>,<otherwise></otherwise>

우선 위 세가지 종류를 사용한다.

select *
from table1
where deleted = false
<choose>
    <when test="a != null and a == 1">and a = 1</when>
    <when test="a != null and a == 2">and a = 2</when>
		<when test="a != null and a == 3">and a = 3</when>
    <otherwise>
        and a is not null
    </otherwise>
</choose>

전체 choose 조건문의 범위를 결정하는 <choose> 태그를 시작으로 <when>의 검증부분을 통해 조건을 건다. a 라는 변수는 자바 Interface Mapper에서 넘어온 파라미터 그대로 사용할 수 있다.

<otherwise>태그는 else랑 비슷한 느낌이지만, 떄로는 default의 의미로 사용될 수 있다. 위 쿼리처럼 존재하는지 여부만 확인할 경우 default값을 is not null로 설정할 수도 있다.

그리고 mybatis의 특징으로 <when> 태그의 내부는 모든 태그가 들어올 수 있다. 또, when 절 안에 if 절로 세분화가 가능하고,foreach 절을 사용해서 loop를 돌릴 수도 있다.

select *
from tableA
where deleted = false
<choose>
    <when test="a != null and a.length > 0">
    	and a in <foreach item="a" collection="a_list" open="(" seperate="," close=")">#{a}</foreach>
    </when>
    <otherwise>
        and a is not null
    </otherwise>
</choose>

MYSQL_Partition

파티션은 크기가 큰 테이블을 여러 개로 분할하는 기능이다.  크기가 큰 테이블에 쿼리를 수행할 때, 인덱스를 사용하더라도 테이블의 크기가 매우 크다면 MYSQL에 부하가 걸릴것이다. 이런 문제를 줄이고 성능  향상을 위해 사용하는 기능이다.

Partition을 사용하는 이유

인덱스는 SELECT 성능을 위해 사용하긴 하지만 오로지 SELECT를 위해 사용하는 것은 아니다. UPDATE,DELETE,INSERT를 위해서도 사용된다.데이터를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 추가적인 작업이 발생하지만 UPDATE 나 DELETE 를 위해서는 해당 데이터를 검색하려면 인덱스가 필수적이다.

그러나 인덱스가 커지게 된다면 SELECT의 성능 저하는 당연한것이고 INSERT나 UPDATE,DELETE 작업도 마찬가지로 느려진다.

테이블에서 데이터를 파티셔닝 할 수 있다면 효과적으로 데이터 처리가 가능하고 성능을 개선할 수 있다.

크기가 큰 테이블을 파티셔닝하지 않고 사용하는 것과 작은 파티션으로 나누어 크기를 줄였을 때 인덱스가 어떤식으로 사용되는지 아래 그림을 보자.

왼쪽 그림처럼  파티셔닝을 하지 않고 그냥 사용하면 인덱스도 매우 커지고 물리적인 메모리 공간도 많이 필요해서 효율적이지 못하다.

그러나 오른쪽처럼 작은 파티션으로 나누어 사용한다면 물리적 메모리를 효율적으로 사용할 수 있고 성능적인 측면에서도 파티셔닝 하지 않은 것보다 효율적으로 사용할 수 있다.

MYSQL Partition 내부 동작

REATE TABLE rt_blog (

id INT NOT NULL,
writeDate DATETIME NOT NULL,
...
PRIMARY KEY(id)

)

PARTITION BY RANGE ( YEAR(writeDate) ) (

PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN MAXVALUE

);

동작 방식을 확인하기 위해 임시로 테이블을 생성했다.

이 테이블에서 writeDate에서 연도는 파티션을 하기위한 파티션 키로서 해당 레코드가 어느 파티션에 저장될지를 결정하는 역할을 한다.

[PartitionTable INSERT]

INSERT 쿼리가 실행되는 MYSQL서버는 INSERT되는 컬럼 값 중에서 파티션 키인 writeDate컬럼 값을 이용해 파티션 표현식을 평가하고, 그 결과에 따라 저장될 파티션을 결정한다.

INSERT되는 데이터가 파티션이 결정되면 그 후에는 일반적인 과정과 동일하게 진행된다.

[PartitionTable UPDATE]

UPDATE 쿼리를 실행하려면 변경 대상 데이터가 어느 파티션에 저장되어있는지 찾아야 한다. 이때 UPDATE쿼리의 WHERE조건에 파티션  키 컬럼이 조건으로 존재하면 그 값을 통해  데이터가 저장된 파티션에서 빠르게 해당 데이터를 검색할 수 있다.

만약 MYSQL에서 파티션 키 이외의 컬럼만 변경될 떄는 파티션이 적용되지 않은 일반 테이블과 마찬가지고 컬럼 값만 변경한다. 그러나 파티션 키 컬럼이 변경될 때는 해당 파티션에서 데이터를 삭제한 후 해당 데이터를 새로운 파티션으로 복사한다. 그 다음 파티션 키 컬럼을 변경 시켜준다.

[PartitionTable SELECT]

가장 중요한 SELECT 쿼리 성능이다. 파티션 테이블을 검색할 때 아래 조건에 따라 성능에 영향을 크게 준다.

SQL이 수행되기 위해 파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건

• WHERE 절의 조건으로 검색해야 할 파티션을 선택 가능한가
• WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가

첫번째 조건의 결과에 의해 두번째 선택사항의 작업이 달라질 수도 있다.

그리고 두 조건이 모두 성립할 때 성능적으로 가장 효율적으로 처리될 수 있다. 두 조건이 성립되면 파티션의 개수와 상관없이 검색을 위해 필요한 파티션의 인덱스만 스캔한다.

MYSQL_Partition 형식

1 . Range

범위를 기반으로 파티션을 나누는 형식이다.

Range Partition은 날씨 기반 데이터가 누적되고 날짜에 따라 분석 삭제할 경우, 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있는 경우, 대량의 과거 데이터 삭제 같은 경우에 사용한다.

Range Partition에서 null은 어떤 값 보다 작은 값으로 취급되기 때문에 컬럼에 null인 데이터가 insert 된다면 가장 작은 값을 저장하는 파티션에 저장된다.

아래는 연도를 범위를 기준으로 파티션을 생성한 것이다.

CREATE TABLE test (
    id INT NOT NULL,
    lname VARCHAR(30),
    datt DATE NOT NULL DEFAULT '2000-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    store_id INT NOT NULL
) PARTITION BY RANGE (YEAR(datt)) (
    PARTITION p0 VALUES LESS THAN (2020) ,
    PARTITION p1 VALUES LESS THAN (2021) ,
    PARTITION p2 VALUES LESS THAN (2022) ,
    PARTITION p3 VALUES LESS THAN MAXVALUE
    );

Partition 추가 

ALTER TABLE test ADD PARTITION(PARTITION p4 VALUES LESS THAN (2023));

Partition 삭제

ALTER TABLE test DROP PARTITION p4;

Partition 병합

ALTER TABLE test 
REORGANIZE PARTITION p2,p3 INTO (
PARTITION p23 VALUES LESS THAN (2020)
);

그리고 날짜 컬럼에 대한 Range 파티션 적용시 YEAR(),TO_DAYS()함수만 사용하길 권장한다. 두 함수는 MYSQL 서버 내부적으로 파티션 프루닝 처리가 되어 성능상의 문제가 발생하지 않지만 그 외의 함수는 파티션 프루닝이 제대로 작동하지 않을 수도 있기 때문에 지양하길 권장한다.

2 . List

List Partition은 RangePartition과 비슷하고,코드나 카테고리 등 특정 값을 기반으로 파티션을 나눈다.

List Partition은 파티션 키 값이 코드 값이나 카테고리와 같이 고정 값일 경우에 사용하고 파티션 키 값을 기준으로 레코드 건수가 균일하고 검색 조건에 파티션 키가 자주 사용되는 경우에 사용한다.

List는 Range와 다르게 null을 명시할 수 있지만, MAXVALUE는 지정할 수 없다.

CREATE TABLE test (
    id INT NOT NULL,
    name VARCHAR(30),
    datt DATE NOT NULL DEFAULT '2000-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
) PARTITION BY LIST (job_code) (
    PARTITION p0 VALUES IN (2) ,
    PARTITION p1 VALUES IN (1,9) ,
    PARTITION p2 VALUES IN (3,6,7) ,
    PARTITION p3 VALUES IN (4,5,8, NULL)
    );

Partition 추가 

ALTER TABLE test ADD PARTITION(PARTITION p4 VALUES IN (3,10));

Partition 삭제

ALTER TABLE test DROP PARTITION p4;

Partition 병합 

ALTER TABLE test 
REORGANIZE PARTITION p2,p3 INTO (
PARTITION p23  VALUES IN (3,6,7,4,5,8,NULL)
);

3 . Hash

Hash Partition은 Hash 함수에 의해 레코드가 저장될 파티션을 결정하는 방식이다.

Hash Partition은 테이블의 모든 레코드가 비슷한 사용빈도를 보이지만 너무 커서 파티션이 필요한 경우 사용된다.

CREATE TABLE test (
    id INT NOT NULL,
    name VARCHAR(30),
    datt DATE NOT NULL DEFAULT '2000-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
) PARTITION BY HASH (id)
PARTITIONS 4;

Partition 추가 

파티션의 갯수로 MOD 연산한 결과에 따라 각 레코드를 저장할 파티션을 결정하므로 새로이 파티션이 추가될 경우 파티션에 저장된 모든 레코드는 재배치 되어야 하므로 많은 부하가 발생한다.

//파티션 이름을 부여
ALTER TABLE test ADD PARTITION(PARTITION p5 ENGINE = INNODB);

Partition 삭제 

파티션 키 값을 이용하여 데이터를 각 파티션으로 분산한 것이므로 각 파티션에 저장된 레코드의 부류를 사용자가 예측할 수 없기에 해시나 키를 이용한 파티션에서는 파티션 단위의 삭제는 불가능하다.

Partition 분할 ,Partition 병합

분할은 불가능 하고 추가만 가능하다. 병합 기능도 제공하지 않지만 갯수를 줄이는 것은 가능하다.

4 . Key

MD5() 함수를 이용한 Hash 값을 기반으로 파티션을 나누고 Hash Partition과 거의 동일하다.

Key Partition은 선정된 파티션 키값에 대하여 내부적으로 MD5()를 이용하여 해시값을 계산하고, 그 값에 MOD를 적용하여 저장할 파티션을 결정한다.

예제 상황으로 학교에는 학생이 존재하고 학생마다 학생증을 하나씩 가지고 있다.

🤖 Association(has one)

Association은 has one 타입의 관계를 다룰 때 사용한다. 예를 들어 학생이; 가지고 있는 학생증은 학생 한 명만 가질 수 있다. 예를 들어 해당 학생증을 조회하려고 할 때 association 관계를 걸어서 같이 조회하면 다음과 같다.

class StudentCard {
  ...
  Student student;
}

class Student {
  int studentId;
  ...
}

우선 학생과 학생증은 1 : 1 관계이다.

<select id="selectStudent" resultType="Student">
  SELECT student_id studentId, ... FROM student WHERE student_id = #{studentId}
</select>
<resultMap id="studentCardResult" type="com.xxx.StudentCard">
  <association property="student" column="stuendId" javaType="Student" select="selectStudent"/>
</resultMap>

<select id="selectStudentCard" resultMap="studentCardResult">
  SELECT ..., student_id studentId, ... FROM studentCard WHERE studentCard_id = #{studentCardId}
</select>

association 태그에 colume은 조회할 selectStudent의 파라미터로 전달되고 studentCard를 조회하면서 함께 student를 조회하여 StudentCard 인스턴스 안에 멤버 변수로 있는 Student 인스턴스의 내용도 채워지게 된다.

{
“studentCard_id”:”1”,
“student”:{
 	“studentId”: “1”,
	“studentName” : “kim”,
	“age”:”19”
	}
	.
    .
    .
}

이런식으로 학생증을 정보를 조회했을 때 학생 정보가 같이 반환된다고 생각하면 된다.

🤖 Collection (has many)

class School {
 int schoolId;
 List<StudentCard> studentCards;
...
}

class StudentCard {
  int studentCardId;
  Student student;
  ....
}

StudentCard(학생증)과  School(학교)은 1 :N 관계이다. 

<resultMap id="schoolResult" type="School">
  <collection property="studentCards" javaType="java.lang.ArrayList" column="schoolId" ofType="StudentCard" select="selectStudentCardId"/>
</resultMap>

<select id="selectStudentCards" resultType="StudentCard">
  SELECT * FROM studentCard WHERE school_id = #{schoolId}
</select>

<select id="selectSchool" resultMap="schoolResult">
  SELECT school_id schoolId, .... , FROM school WHERE school_id = #{schoolId}
</select>

collection 태그에 javaType으로 ArrayList로 지정해주고 해당 List의 Generic Type을 StudentCard로 지정해주었다. 이런 식으로 작성하면 학교를 조회하면 학교에 존재하는 학생증 정보를 List에 넣을 수 있다.

{
“school_id”:”1”,
“school_name : “kkk”,
“studentCards”:{
		studentCard1:{
			“studentCardId”: “1”,
		},
        
		studentCard2:{
			“studentCardId”: “2”,
			.
			.
			.
		},

		studentCard3:{
			“studentCardId”: “3”,
			.
			.
			.
		},
			.
			.
			.
 		}

}

위처럼 조회 시 학교에 포함된 학생증 인스턴스도 포함되어 표시된다.

🧑🏻‍💻DB 파티셔닝

서비스의 크기가 점점 커지고 DB에 저장하는 데이터의 구모 또한 대용량화 되면서, 기존에 사용하는  DB 시스템의 용량의 한계와 성능의 저하를 가져오게 되고 VLDB와 같이 하나의 DBMS에 너무 큰 table이 들어가면서 용량과 성능 측면에서 많은 이슈가 발생하게 되었고 , 이런 이슈를 해결하기 위한 방법으로 테이블을 파티션이라는 작은 단위로 나누어 관리하는 파티셔닝 기법이 나타나게 되었다.

파티셔닝은 논리적인 데이터 element들을 다수의 entity로 쪼개는 행위를 뜻하는 일반적인 용어이다. 큰 테이블이나 인덱스를 관리하기 쉬운 파티션이라는 작은 단위로 물리적으로 분할하는 것을 의미한다. DB에 접근하는 Application의 입장에서는 물리적인 데이터 분할이 있지만 인식하지 못한다.

파티셔닝의 목적으로는 성능, 가용성, 관리 이용성 등이 있다.

성능적인 측면에는 특정 DML(Data Manipulation Language : 데이터 조작 언어)과 Query의 성능을 향상하고, Full Scan에서 데이터 Access의 범위를 줄여 성능 향상을 가져온다. 또, 대용량 Data Write 환경에 효율적이다.

많은 Insert가 있는 OLTP 시스템에서 Insert작업을 작은 단위인 partition들로 분산시켜 경합을 줄인다.

가용성적인 측면에는 물리적인 파티셔닝으로 인해 전체적인 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상되고 , 각 분할 영역(partiton 별로)을 독립적으로 백업하고 복구할 수 있다.

테이블의 patition 단위로 Disk I/O를 분산하여 경합을 줄이기 때문에 UPDATE 성능을 향상한다.

관리 이용성 측면에는 큰 테이블들을 제거하여 관리를 쉽게 해 준다.

🛠파티셔닝 장단점

장점

• 관리적 측면에서는 파티션 단위의 백업, 추가, 삭제, 변경이 가능하여 전체 데이터를 손실할 가능성이 줄어들어 데이터 가용성이 향상된다.
• 파티션 단위로 백업 및 복구가 가능하다.
• 파티션 단위로 I/O분산이 가능하여 UPDATE 성능을 향상한다.
• 성능적 측면에서는 파티션 단위 조회 및 DML 수행이 가능하여 FULL SCAN에서 데이터 허용 범위를 줄여 성능 향상을 가져온다.
• 필요한 데이터만 빠르게 조회할 수 있기 때문에 쿼리 자체가 가볍다.

단점

• 테이블 간 조인에 대한 비용이 증가하고 테이블과 인덱스를 별도로 파티셔닝 할 수 없다.
• 테이블과 인덱스를 같이 파티셔닝 해야 한다.

➡️수평 파티셔닝

하나의 테이블의 각 행을 다른 테이블에 분산시키는 것이다.

샤딩()과 동일한 개념이고 스키마를 복제한 후 샤드 키를 기준으로 데이터를 나누는 것을 말한다.

즉, 스키마가 같은 데이터를  두 개 이상의 테이블에 나누어 저장하는 것을 말한다.

특징

• 퍼포먼스, 가용성을 위해 key 기반으로 여러 곳에 분산 저장하고 일반적으로 분산 저장 기술에서 파티셔닝은 수평 분할을 의미한다.
• 보통 수평 분할을 한다고 했을 때는 하나의 데이터베이스 안에서 이루어지는 경우를 지칭한다.

장점 

• 데이터의 개수를 기준으로 나누어 파티셔닝 한다.
• 데이터의 개수가 작아지고 따라서 인덱스의 개수도 작아지게 된다. 자연스럽게 성능은 향상된다.

단점 

• 서버 간의 연결과정이 많아진다.
• 데이터를 찾는 과정이 기존보다 복잡하기 때문에 lantency가 증가하게 된다.
• 하나의 서버가 고장 나게 되면 데이터의 무결성이 깨질 수 있다.

➡️수직 파티셔닝

테이블의 일부 열을 빼내는 형태로 분할한다.

모든 칼럼들 중 특정 컬럼들을 쪼개서 따로 저장하는 형태이고 스키마를 나누고 데이터가 따라 옮겨 가는 것을 말한다.즉 , 하나의 엔티티를 2개이상으로 분리하는 작업이다.

🛠수직 파티셔닝 장점

• 자주 사용하는 컬럼 등을 분리시켜 성능을 향상할 수 있다.
• 한 테이블을 SELECT 하면 결국 모든 칼럼을 메모리에 올리게 되므로 필요없는 컬럼까지 올라가서 한 번에 읽을 수 있는 ROW가 줄어든다. 이는 I/O 측면에서 봤을 때 필요한 컬럼만 올리면 훨씬 많은 수의 ROW를 메모리에 올릴 수 있으니 성능상의 이점이 있다.
• 같은 타입의 데이터가 저장되기 때문에 저장 시 데이터 압축률을 높일 수 있다.

💡파티셔닝(partitioning) 분할 기준

📁Range Partitioning [범위 파티셔닝]

create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) )
partition by range(주문일자) (
  partition p2009_q1 values less than('20090401')
, partition p2009_q2 values less than('20090701')
, partition p2009_q3 values less than('20091001')
, partition p2009_q4 values less than('20100101')
, partition p2010_q1 values less than('20100401')
, partition p9999_mx values less than( MAXVALUE )
);

• 파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 데이터 조회 시 성능을 크게 향상해준다.
• 파티션 키로는 하나 이상의 칼럼을 지정할 수 있고, 최대 16개까지 허용된다. 보관 주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 것도 큰 장점이다.
• 관리자의 실수로 신규 파티션 생성을 빠트리면 월초 또는 연초에 데이터가 입력되지 않는 에러가 발생하므로, maxvalue 파티션을 반드시 생성해 두는 것이 좋다.

📁Hash Partitioning [해시 파티셔닝]

• 파티션 키에 해시함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식
• 검색할 때는 조건절 비교 값에 해시함수를 적용해 읽어야 할 파티션을 결정하며, 해시 알고리즘 특성상 “==” ,“In-List” 이 두 조건으로 검색할 때만 파티션 Pruning이 동작한다.
• 테이블 파티셔닝 여부를 결정할 대는 데이터가 얼마나 고르게 분산될 수 있느냐가 중요한 관심사이다. 해시 파티셔닝 할 때 특히 데이터 분포를 신중히 고려해야 하는데, 사용자가 직접 파티션 기준을 정하는 Range, 리스트 파티셔닝과 다르게 해시 파티셔닝은 파티션 개수만 사용자가 결정한다.

📁List Partitioning

create table 인터넷매물( 물건코드varchar2(5), 지역?류varchar2(4) )
partition by list(지역?류) 
(partition p_지역1 values ('서울'), 
partition p_지역2 values ('경기', '인천'), 
partition p_지역3 values ('부산', '대구', '대전', '광주'),
 partition p_기타values (DEFAULT) ) ;

• 순서와 상관없이 불연속적인 값의 목록으로써 결정된다.
• 단일 칼럼으로만 파티션 키를 지정해야 한다.
• 리스트 파티션에도 default파티션을 생성해 두어야 안전하다.

📁결합 파티셔닝

• 결합 파티셔닝을 구성하면서 서브 파티션마다 세그먼트를 하나씩 할당하고 서브 파티션 단위로 데이터를 저장한다.
• 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다.

📁DB Replication

https://jarvics.tistory.com/68

• 데이터베이스 이중화 방식 중 하나로 Master DB + Slave DB로 구성한다.
• Master DB에 데이터 변경이 감지되면 Master DB 로그를 기반으로 Slave DB에 복제한다.
• Master DB에는 데이터 변경이 필요한 INSERT,DELETE,UPDATE 등의 쿼리가 사용하고, Slave DB에는 SELECT문이 사용한다.

Master DB 와 Slave DB를 나눠 구성한 다음 @Transaction의 readonly 속성이 true이면 Slave DB를 사용하고 false이면 Master DB를 사용한다.

우선 SpringBoot와 RDS(MYSQL)이 연동이 완료되었다는 가정하에 진행할 것이다.

우선 AWS 접속한 뒤 서비스에서 RDS로 들어가 본인이 생성한 DB인스턴스를 선택한다.

선택한 다음 작업을 클릭하여 읽기 전용 복제본 생성을 클릭한다.

클릭한 다음  퍼블릭 엑세스를 허용 해준다. 그러면 복제본 생성이 완료된다.

여기까지는 딱히 어려움이 없다.

다음은 스프링 프로젝트로 가서 설정을 해보자 

spring:
  datasource:
    url: jdbc:mysql://본인 RDS 앤드포인트URL:3306/사용할 데이터베이스 이름?useSSL=false&useUnicode=true&characterEncoding=utf8
    slave-list:
      - name: slave
        url: jdbc:mysql://본인 RDS 복제본 앤드포인트 URL:3306/사용할 데이터베이스 이름?useSSL=false&useUnicode=true&characterEncoding=utf8
    username: RDS설정 username
    password: RDS설정 password
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL8Dialect
    database: mysql
    hibernate:
      ddl-auto: update
    generate-ddl: true

우선 RDS설정 정보를 적어 놓은 yml파일에 slave-list,name,url 을 추가해준다.

다음은 DataSource를 직접 설정해야하기 때문에 Spring을 실행할 때 DataSourceAutoConfiguration을 제외시켜준다.

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

이제 DB 설정파일을 가져올 DatabaseProperty클래스를 만든다.(yml에서 설정을 가져오는 클래스이다.)

@Getter
@Setter
@Component
@ConfigurationProperties("spring.datasource")
public class DatabaseProperty {
    private String url;
    private List<Slave>slaveList;
    private String username;
    private String password;
    private String driverClassName;
    
    @Getter
    @Setter
    public static class Slave {
        private String name;
        private String url;
    }
}

여러대의 Slave DB를 순서대로 로드밸런싱 하기위해  CircularList클래스를 만든다.

public class CircularList<T> {
    private List<T> list ;
    private Integer counter = 0;


    public CircularList(List<T> list) {
        this.list = list;
    }

    public T getOne(){
        if (counter + 1 >= list.size()){
            counter = -1;
        }

        return list.get(++counter);
    }
}

• 여러개의 DataSource를 묶고 필요에 따라 분기처리를 위해 AbstractRoutingDataSource클래스를 사용해야 한다.
• 여러대의 Slave DB를 순서대로 사용하기 위해 CircularList에 Slave DB 키를 추가 해준다.
• determineCurrentLookup 메서드에서 현재 @Transactional(readOnly = true)일 경우 Slave DB로, 아닐 경우 Master DB의 DataSource의 키를 리턴하도록 설정해준다. 

public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {

    private CircularList<String> dataSourceList;
    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        dataSourceList = new CircularList<>(
                targetDataSources.keySet()
                        .stream()
                        .filter(key -> key.toString().contains("slave"))
                        .map(key -> key.toString())
                        .collect(toList()));
    }

    @Override
    protected Object determineCurrentLookupKey() {
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();

        if (isReadOnly){
            return dataSourceList.getOne();
        }else{
            return "master";
        }
    }
}

이제 최종적으로 DataSource, TransactionManager, EntityManager 설정을 해야한다.

우선 DataConfig 클래스를 생성한다.

@Configuration
public class DatabaseConfig {

    @Autowired
    private DatabaseProperty databaseProperty;
	
    //아래 routingDataSource에서 사용할 설정 메서드 
    public DataSource routingDataProperty(String url){
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(databaseProperty.getUrl());
        hikariDataSource.setDriverClassName(databaseProperty.getDriverClassName());
        hikariDataSource.setPassword(databaseProperty.getPassword());
        hikariDataSource.setUsername(databaseProperty.getUsername());

        return hikariDataSource;
    }


    @Bean
    public DataSource routingDataSource(){
        
        ReplicationRoutingDataSource replicationRoutingDataSource = new ReplicationRoutingDataSource();
        //#1
        DataSource master = routingDataProperty(databaseProperty.getUrl());
		
        Map<Object,Object> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("master",master);
		
        //#2
        databaseProperty.getSlaveList().forEach(slave -> {
            dataSourceMap.put(slave.getName() , routingDataProperty(slave.getUrl()));
        });
   		//#2
        replicationRoutingDataSource.setTargetDataSources(dataSourceMap);
        
        //#3
        replicationRoutingDataSource.setDefaultTargetDataSource(master);
        return replicationRoutingDataSource;
    }

    @Bean
    public DataSource dataSource() {
        return new LazyConnectionDataSourceProxy(routingDataSource());
    }

#routingDataSource 

1. 우선 이전에 만들었던 ReplicationRoutingDataSource 클래스에 Master DB와 Slave DB를 추가해준다.
2. replicationRoutingDataSource 의 replicationRoutingDataSourceNameList 세팅한다.(Slave Key 이름 리스트 세팅)
3. 디폴트는 Master로 설정 

#dataSource

• LazyConnectionDataSourceProxy는 실제 쿼리가 실행될 때 Connection을 가져온다.
• LazyConnectionDataSourceProxy는 실질적인 쿼리 실행 여부와 상관없이 트랜잭션이 걸리면 무조건 Connection객체를 확보하는 Spring의 단점을 보완하며 트랜잭션 시작시에 Connection Proxy객체를 리턴하고 실제로 쿼리가 발생할 때 데이터 소스에서 getConnection()을 호출하는 역할을 한다.
• TransactionSynchronizationManager가 현재 트랜잭션을 상태를 읽어올 수 있지만 트랜잭션 동기화 시점과 Connection이 연결되는 시점이 다르기 때문에 LazyConnectionDataSourceProxy를 사용하여 Connection객체를 가져온다.

이제 기본설정은 어느정도 마무리 되었고 JPA에서 사용할 EntityManager과 TransactionManager 설정을 해준다.

@Configuration

public class DatabaseConfig {
		
       
   		.
		.
		.
        
        
	@Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(){
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPackagesToScan("엔티티가 위치한 패키지 경로" ex)com.example);
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);


        return entityManagerFactoryBean;
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(entityManagerFactory);
        return tm;
    }
}

이제 JPA 설정까지 마무리 했다.

테스트를 한번 해보자

Test

@Entity
@Table(name = "member")
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Member {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;


    @Builder
    public Product(String name) {
        this.na,e = name;
    }
}

public interface MemberRepository extends JpaRepository<Member, Long> {}

@RestController
@RequestMapping("/api/members")
public class MemberController {

    @Autowired
    private MemberService memberService;

    @GetMapping
    public ResponseEntity<?> getMembers() {
        List<Member> memberList = memberService.getMembers();
        return new ResponseEntity<>(memberList, HttpStatus.OK);
    }
    @GetMapping("/masterDB")
    public ResponseEntity<?> getMembersFromMasterDB() {
        List<Member> memberList = memberService.getMembersMaster();
        return new ResponseEntity<>(memberList, HttpStatus.OK);
    }
}

@Service
public class MemberService {

    @Autowired
    private MemberRepository memberRepository;

    @Transactional(readOnly = true)
    public List<Member> getMembers() {
        return memberRepository.findAll();
    }
    @Transactional
    public List<Member> getMembersMaster() {
        return memberRepository.findAll();
    }
}

Master/Slave DB에 쿼리가 날아가는 것을 확인하기 위해 yml 파일에 설정을 추가해준다.

logging:
  level:
    org.springframework.jdbc.datasource.SimpleDriverDataSource: DEBUG
    org.hibernate.SQL: DEBUG

이제 DB에 member Table을 생성할것이다. 그러나 원래 JPA는 자동으로 Table을 생성할 수 있지만 처음에 DataSourceAutoConfiguration를 제외시켰기 때문에 직접 설정해주어야한다.

SQL

CREATE TABLE `member` (
  `id` long NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into `member` (name) values ('name1'), 
('name2'), ('name3');

서버를 실행 후 /api/members 에 GET요청을 하면 아래와 같은 결과값이 나온다.

[
    {
        "id": 1,
        "name": "name1",
    },
    {
        "id": 2,
        "name": "name2",
    },
    {
        "id": 3,
        "name": "name3",
    }
]

SimpleDriverDataSource의 로그를 확인해보면

Creating new JDBC Driver Connection to [jdbc:mysql://campshop-slave.concrp2jli...

이렇게 Slave DB를 사용합니다.

/api/member/master에 요청을 보내면

Creating new JDBC Driver Connection to [jdbc:mysql://campshop-db.concrp...

이렇게 Master DB를 사용합니다.

이런식으로 대용량 트래픽으로 인해 생기는 데이터베이스 문제를 효율적으로 분산시키며 해결할 수 있다.

REFERENCE

• http://kwon37xi.egloos.com/m/5364167
• https://www.bespinglobal.com/techblog-rds-20180627/