주로 사용했던 방법은 Database Tool 에 접속해서 설계했던 해당 스키마를 메뉴얼 또는 실제 쿼리(DDL) 로 생성하는 방법으로 했었다.

이 방법엔 단점이 있는데, 각 환경별로 소스 코드 배포전에 스키마를 DB에 미리 반영해줘야하는 문제점이 있는데 자칫 human error 를 맞닥뜨릴 확율이 높다. 이뿐만 아니라 형상관리가 안되므로 팀 단위 개발환경에서는 팀원중에 누가 무엇을 언제 DB에 반영했는지 확인하기가 힘들었다.

지금의 회사를 다니면서 Flyway 라는 DB 마이그레이션툴을 사용해볼 수 있었는데 위 단점을 보완해줄 수 있는 아주 좋은 오픈소스였다.

Flyway 란 무엇인가?

Flyway 는 git 형상관리와 동일하게 DB를 형상관리할 수 있도록 도와주는 버전관리 도구이다. 즉, 스키마, 컬럼, 인덱스 를 생성 부터 수정, 삭제 까지 모든 기록을 SQL 파일로 보존하여 추적할 수 있다.

또한, 항상 배포전 수동으로 스키마를 반영해줘야했던 부분을 CI/CD 파이프라인에 통합, 자동화하여 human error 를 줄여줄 수 있고, 팀원간 DB 상태의 일관성을 보장할 수 있게 된다.

아래는 Spring 환경에서 마이그레이션을 적용하는 방법을 나타낸다.

1. gradle 설정

    implementation 'org.flywaydb:flyway-mysql'

2. application propertie 설정

아래는 각 설정 속성을 설명한다.

# enabled: 마이그레이션 실행 활성화
# locations: SQL 파일 경로
# baseline-on-migrate: flyway 적용 시점의 이전 모든 스키마 내역을 무시. 버전 1부터 시작한다는 뜻이다.
# baseline-version: 기준 시점의 버전번호를 설정 (기본적으로 1부터 시작)

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: 1

2. migration file 작성

SQL 파일 작성 format 은 아래와 같다. 보통 description 부분엔 스키마명을 입력해준다.

버전은 1부터 시작하여 증가시킨다.

V[version]__[description].sql

위 format 에 맞게 작성하게 된다면 아래와 같다.

--파일명--
V1__fruit.sql

##중요## 
쿼리 작성할 때 꼭 IF NOT EXISTS 절을 넣어줘야한다. 
개발 또는 운영 DB에 이미 반영했는데 까먹고 해당 쿼리를 배포하면 큰일 나기 때문에

--DDL 스키마 생성할 때--
CREATE TABLE IF NOT EXISTS fruit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    color VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--DDL 속성 추가할 때--
ALTER TABLE fruit
ADD COLUMN IF NOT EXISTS size int(11);

3. 프로젝트 실행

마지막으로 프로젝트를 실행하게되면

DB에 flyway_schema_history 라는 테이블이 생성되고 아래의 이미지와 같은 내용이 저장된다.

저자는 SQL 파일을 V1__init.sql 로 작성했고, script 내용엔 아무것도 작성하지 않았다.

이제 앞으로 스키마, 컬럼, 인덱스를 생성, 추가, 삭제할 때 2번 부터 시작하면된다.

4. 경험 Tip

테이블에 인덱스나, 컬럼을 추가할때 고려할 부분이 하나 있다.

만약 저장된 데이터가 천만건이상이라고 했을경우, 위 방법으로 변경된 스키마를 자동화로 반영하기엔 위험부담이 따를 수 있으니, 시스템 점검시간이나 트래픽이 적은 새벽시간에 직접 수동으로 미리 반영해준 다음 절차 대로 배포해줘야한다.

refer: https://documentation.red-gate.com/fd/getting-started-with-flyway-184127223.html

맨허튼 거리(Manhattan Distance) 는 2차원 평면 공간에서 두 점 p와 q 사이의 거리를 측정하는 방법 중 하나로, 두 점 사이의 수평 및 수직 이동 거리의 합으로 정의된다.

즉, 맨허튼 거리가 (p1,p2) 와(q1,q2) 사이면 맨허튼 거리 = ∣p1−q1∣+∣p2−q2∣ 이다.

여기서 |p| 또는 |q| 는 절대값을 나타내며, 수평방향(p) 수직방향(p)의 거리를 모두 더한 값이다.

맨허튼 거리는 유클리드 거리와는 다르게 대각선 방향의 이동을 고려하지 않는다. 오로지 수평 및 수직 방향의 이동만을 고려한다. 이로 인해 실제 도로 네트워크나 격자 형태의 구조에서 두 지점간의 이동 거리를 좀 더 정확하게 나타낼 수 있다.

맨허튼 거리(Manhattan Distance)는 다음과 같은 상황에서 유용하게 사용 될 수 있으므로 이해만 하고 넘어가자.

1. 물류

택배 회사나 배송 업체에서 배송 경로를 최적화 할때 사용 된다.

2. 도시 계획

도시의 도로 네트워크 및 고통 흐름 분석 및 도시 발전 방향을 계산할때 사용 된다.

3. 비전 및 머신러닝

객체 간의 거리를 측정하거나 이미지 처리에서 특징을 추출하는데 사용된다.

4. 게임 개발

게임내 캐릭터나 NPC의 이동 경로를 계산하거나 충돌 감지에 사용 된다.

다들아시다 시피 like 연산자는 정규식을 사용하지않고 %과 _ 와일드 카드를 사용해서 특정한 상수 문자열이 있는지 조회하는 연산자이다.

우선 위에서 언급한 2가지의 와일드 카드에 대해 알아보자.

• % : 0 또는 1개 이상의 모든 문자에 일치하는가? (문자의 내용과 관계 없이)
• _ : 정확히 1개의 문자에 일치하는가? (문자의 내용과 관계없이)

그리고 와일드 카드 문자는 1개 이상을 입력해서 사용기 가능하다. 예를들어 _a_,%a% 이런 식으로 가능하다.

아래는 like 연산자와 2가지 와일드 카드를 사용한 쿼리 예제다.

SELECT ‘abcdef’ LIKE ‘abc%’
+———————-+
|      1 |
+———————-+

SELECT ‘abcdef’ LIKE ‘%abc’
+———————-+
|      1 |
+———————-+

SELECT ‘abcdef’ LIKE ‘ab_’
+———————-+
|      1 |
+———————-+

SELECT ‘abcdef’ LIKE ‘_abc’
+———————-+
|      1 |
+———————-+

여기서 중요한 부분이 있는데 와일드 카드 문자인 %, _ 이 두개가 조회할 문자열 앞에 있다면 인덱스레인지 스캔이 아닌 인덱스 풀스캔방식으로 처리가 되어 성능면에서 좋지 않다. 그래서 항상 like 연산자를 사용할땐 조심해야된다.

refer : https://www.yes24.com/Product/Goods/103415767

ngrok 는 로컬 컴퓨터에서 실행되는 웹 서버를 외부에서 접근할 수 있게 해주는 터널링 프로그램이다. 일반적으로 로컬 컴퓨터에서 애플리케이션을 개발하고, 이를 외부에서 접근하기 위해서는 웹 서버를 구축해야 한다. 하지만, 로컬 컴퓨터에서 실행하는 웹 서버를 mgrok 서버에 연결하고, ngrok 서버가 외부에서 접근할 수 있는 URL을 제공함으로써 외부에서 애플리케이션에 접근할 수 있게 한다.

예를 들어, Slack Bot을 사용 할 때 Event Subscription을 설정해줘야 하는데, 이때 challenge 값을 응답해줄 Request URL이 필요하다. 이것을 위해 서버를 배포하거나 네트워크 환경을 구성하기엔 시간과 비용이 아까울 텐데, ngrok 만 사용하면 단 몇 분 만에 해결해줄 것이다.

localhost Server <--- ngrok Server <--- outside

mac 환경에서의 사용방법은 다음과 같다.

1. homebrew 을 통해 설치

brew install ngrok/ngrok/ngrok

2. 인증토큰 추가

ngrok config add-authtoken <token>

3. ngrok 시작

ngrok http <port>

또한, ngrok는 IP 제한, HTTP 기본 인증, OAuth 2.0, OpenID Connect, SAML, Webhook 확인 및 상호 TLS를 통한 다양한 방식의 보안을 제공한다.

1. 인메모리 모드

• 인메모리(In-Memory) 에 저장된다.
• dependency 설정만 해주면 사용 가능하다.
• 빠른 임시 데이터 저장이 필요한 애플리케이션에 적합하다.

2. 임베디드 모드

• dependency 설정만 해주면 사용 가능하다.
• 데이터를 디스크에 직접 저장할 수 있다.
• 애플리케이션과 H2 엔진이 동일한 JVM에서 실행된다.

3. 서버모드

• 데이터를 디스크에 직접 저장할 수 있다.
• 별도로 프로그램을 설치해야된다.
• 애플리케이션과 H2 엔진이 별도의 프로세스로 실행된다.
• 여러 애플리케이션이 동시에 H2 데이터베이스에 엑세스 할수 있다.

사용절차

나는 주로 인메모리, 임베디드용을 설정해서 사용하는데, 이 블로그에서는 임베비드용으로 설정을 하겠다.

1. dependency 설정

dependencies {
   implementation 'com.h2database:h2:2.1.212'
}

2. application.yml 설정

spring:
  datasource:
    url: url: jdbc:h2:file:~/test # 홈디렉토리 test파일로 저장했다.
    driverClassName: org.h2.Driver
    username: sa
    password:
  jpa.database-platform: org.hibernate.dialect.H2Dialect
  h2:
    console: // 웹브라우저에서 데이터베이스에 접속할 경로 설정
      enabled: true
      path: /h2-console

스프링에서는 위의 2개를 설정해주면 끝이다.

Lombok은 불필요한 코드와 작업을 줄여주는 좋은 라이브러리지만, 무분별하게 사용하면 코드를 분석하는 입장에서는 혼동이 올지도 모른다.

아래는 생성자를 자동 생성해주는 어노테이션 종류이다.

• @NoArgsConstructor : 파라미터가 없는 디폴트 생성자를 생성
• @AllArgsConstructor : 모든 필드 값을 파라미터로 받는 생성자를 생성
• @RequiredArgsConstructor : final이나 @NonNull으로 선언된 필드만을 파라미터로 받는 생성자를 생성

@NoArgsConstructor

@NoArgsConstructor 어노테이션은 파라미터가 없는 디폴트 생성자를 자동으로 생성한다. 이 어노테이션을 사용하면, 클래스에 명시적으로 선언된 생성자가 없더라도 인스턴스를 생성할 수 있다.

@NoArgsConstructor
public class Person {
    private String name;
    private int age;
    // getters and setters
}

NoArgsConstructor 사용하면 Java 코드는 다음과 같아진다.

public class Person {
    private String name;
    private int age;

    public Person(){}
}

@AllArgsConstructor

@AllArgsConstructor 어노테이션은 클래스의 모든 필드 값을 파라미터로 받는 생성자를 자동으로 생성한다. 이 어노테이션을 사용하면, 클래스의 모든 필드를 한 번에 초기화할 수 있다.

@AllArgsConstructor
public class Person {
    private String name;
    private int age;
    // getters and setters
}

AllArgsConstructor 사용하면 Java 코드는 다음과 같아진다.

public class Person {
    private String name;
    private int age;

    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }
}

@RequiredArgsConstructor

@RequiredArgsConstructor 어노테이션은 final이나 @NonNull으로 선언된 필드만을 파라미터로 받는 생성자를 자동으로 생성한다. 이 어노테이션을 사용하면, 클래스가 의존하는 필드를 간단하게 초기화할 수 있다.

@RequiredArgsConstructor
public class Person {
    private final String name;
    private final int age;
    private String address;
    // getters and setters
}

RequiredArgsConstructor 사용하면 Java 코드는 다음과 같아진다.

public class Person {
    private final String name;
    private final int age;
    private String address;

    public Person(final String name, final int age) {
        this.name = name;
        this.age = age;
    }
}

그리고 각 어노테이션에 세부 옵션을 지정해줄 수 있다. 아래는 옵션 설명을 한다.

• staticName : 정적 팩토리 메소드를 생성
• access : 접근제한자를 지정
• onConstructor : 생성자의 접근 제어자를 설정, 기본값은 public
• force : final 필드가 선언된 경우 컴파일 타임에 기본값을 0 / null / false 로 설정

staticName

모든 생성자 어노테이션에 사용될 수 있다. 아래는 AllArgsConstructor 에 옵션을 설정한 예시이다.

@AllArgsConstructor(staticName = "of")
public class Person {
    private String name;
    private int age;
    private String address;
}

@AllArgsConstructor 에 staticName 옵션을 사용하면 Java 코드는 다음과 같아진다.

@AllArgsConstructor(staticName = "of")
public class Person {
    private String name;
    private int age;
    private String address;

    public static Person of(String name, int age, String address) {
        new Person(name, age, address);
    }
}

access

access 옵션엔 다음과 같은 접근제어자가 있다.

• PUBLIC: 기본값이며, 모든 클래스에서 생성자에 접근 가능
• PROTECTED: 같은 패키지의 클래스와 상속받은 클래스에서 생성자에 접근 가능
• PACKAGE: 같은 패키지의 클래스에서 생성자에 접근 가능
• PRIVATE: 해당 클래스 내부에서만 생성자에 접근 가능

이 외에도 NONE과 MODULE 접근 제어자가 있지만, 일반적으로 사용하지 않는다.

force

@NoArgsConstructor 어노테이션에서만 사용이 가능하고 해당 옵션을 지정할경우 primitive type 에 맞는 기본 값이 설정된다.

@NoArgsConstructor(force = true)
public class Person {
    private final String name;
    private final int age;
    private String address;
}

@NoArgsConstructor 에 force 옵션을 사용하면 Java 코드는 다음과 같아진다.

public class Person {
    private final String name = null;
    private final int age = 0;
    private String address;
}

위 3개의 생성자 어노테이션들은 상황에 알맞게 조합해서 사용해야된다.

COUNT() 함수는 아래와같이 사용할수있다.

• COUNT(*)
• COUNT(컬럼명)
• COUNT(숫자)

차이점이라면 별표(*)와 숫자를 넣을경우 테이블의 모든 행, 즉 레코드를 반환 하기 때문에 결과는 같다. 또한, 성능차이도 없다.

하지만 컬럼명을 넣을 경우 결과는 달라진다. 해당 컬럼명 값이 NULL이 아닌 수의 개수를 반환하기 때문이다. 또한, 해당 컬럼명이 인덱스를 사용하지못할경우 검색 속도가 느려질 수 있다.

refer : https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

최근 간단한 사이드 프로젝트를 하면서 오랜만에 도커를 사용하여 서버를 올렸지만, 원하던 시간에 로그가 찍히질않아 확인하던중 Linux System은 기본적으로 UTC를 사용한다는걸 잊고 있었다.

그래서 Command 명령어로 UTC 로 설정된 기본 시간을 KST 로 변경하는 방법을 찾아 보았다. 방법은 2가지가 있다.

1. dpkg-reconfigure

1. 현재 시간대(TimeZone) 목록 조회

# dpkg-reconfigure tzdata

2. Asia 선택 후 Seoul 선택

# 6  <<< Asia 번호
# 69 <<< Sesoul 번호

2. datetimectl

1. 현재 시간대(TimeZone) 확인

# timedatectl

2. 시간대(TimeZone) 변경

# timedatectl set-timezone Asia/Seoul

보통 서비스회사에서 일하게 되면 CI, DI 값을 데이터베이스에 저장하고 관리를 하게 될 수 있는데 이것이 무엇인지 정확하게 알아보고자 한다.

CI

CI(Connecting Information) 연계정보라 한다. CI는 특정 개인의 식별을 위한 고유한 범용 Key값이다. 주민번호 기반으로 생성되기 때문에 유일성이 보장되고, 일방향 암호화를 이용하기때문에 복호화가 불가능하다. 즉, A, B, C 라는 각각 다른 사이트에서 인증받으면 A라는 사용자는 주민번호와 같은 유일한 암호화된 Key값을 받아 사용 될 수 있다는 뜻이다.

CI는 주빈번호 대체 수단을 제공하는 것이므로, 본인확인기관(예: NICE의 PASS앱)의 본인 확인 결과를 온라인 사업자에게 제공할 수 있다.

CI 생성과정은 아래와 같다.

주민번호(910000-1111111) + 비밀정보(본인확인기관, KISA) > HMAC(일반향 암호화) > 64Byte형 데이터 인코딩 > CI(88Byte) ye24Ypap...

DI

DI(Duplicated Joining Verification Information) 중복가입확인정보라 한다. DI는 특정 개인의 식별을 위한 해당 기관에서 고유한 로컬 Key값이다. 쉽게 말하자면, A, B, C 라는 각각 다른 사이트에서 인증을 받으면 A사용자는 사이트별로 각기 다른 암호화된 Key값을 받게 된다. 이 값을 사용하여 각 사이트 별로 중복가입 여부를 확인하는데 사용될 수 있다.

DI 생성과정은 CI 와 비슷하지만, 주민번호와 연결되는 비밀정보가 다르다.

트랜잭션의 격리 수준(Isolation Level)이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

격리 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 의 4가지로 나뉜다.

1. READ UNCOMMITTED

READ UNCOMMITTED 격리에서는 각각 트랜잭션에서 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 조회할수 있다.

예시는 아래와 같다.

1. 사용자 A는 users id 가 1 이고 first_name 이 "Hong" 인 사원을 INSERT

   START TRANSACTION;
   INSERT INTO users VALUES(1, 'Hong');

2. 사용자 B는 사용자 A가 COMMIT 하기도전에 id 가 1이고 first_name 이 "Hong" 인 사원을 검색할 수 있다.

   SELECT * FROM users WHERE id = 1 AND first_name = 'Hong';

3. 사용자 A는 작업을 COMMIT 한다.

   COMMIT;

이처럼 하나의 트랜잭션에서 COMMIT 되지도 않았는데 다른 트랜잭션에서 조회할 수 있는 문제를 더티리드(Dirty Read) 라고 한다.

2. READ COMMITTED

READ COMMITTED는 오라클에서 기본으로 사용되는 격리 수준이며, 위에서 발생한 더티리드(Dirty Read)는 발생하지 않는다. COMMIT 완료된 데이터만 조회할 수있기 때문이다.

예시를 한번 보자.

1. 사용자 B가 first_name 이 "Hong" 인 사람을 검색

   START TRANSACTION;
   SELECT * FROM users WHER first_name = 'Hong';

2. 사용자 A가 id가 1번인 first_name 을 "Hong" 에서 "Hwang" 로 변경

   START TRANSACTION;
   UPDATE users SET first_name = 'Hwang';
   COMMIT;

3. 사용자 A가 COMMIT 하기 전에 사용자 B가 id 1번인 users 를 검색하게 되면 "Hong" 로 조회된다. 언두영역에 저장된 데이터 조회

그 이유는 커밋되기 전까지는 변경된 내역을 조회할 수 없기 때문이다.

이 격리수준에서는 NON-REPEATABLE READ 이라는 부정합의 문제가 발생을 한다. 이 문제는 하나의 트랜잭션내에서 똑같은 SELECT 쿼리를 실행 했을때 항상같은 결과를 조회해야되는데, 조회할 때마다 결과가 달라진다.

3. REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 에서 기본적으로 사용한다. 이 격리 수준에서는 동일한 트랜잭션 내에서는 동일한 쿼리 결과를 보장한다.

한 트랜잭션에서 데이터를 읽어오면 해당 데이터는 현재 트랜잭션이 끝날 때까지 다른 트랜잭션에 의해 수정되지 않는다. 따라서 위에서 발생한 NON-REPEATABLE READ 부정합이 발생하진 않는다.

그 이유는 트랜잭션이 RollBack 이 될 가능성에 대비해 변경되기 전 레코드를 언두 영역에 저장해두기 때문에 일관성 있는 데이터를 유지할 수있다.

언두영역에 저장된 모든 레코드에는 트랜잭션 번호가 있다. 레코드들은 스토리지 엔진으로 부터 필요없다고 생각되면 삭제를 하게 된다.

자... 해당 격리 수준이 작동하는 방식은 다음과 같다.

1. A의 트랜잭션번호는 12, B의 트랜잭션 번호는 10이라고 치자.

   START TRANSACTION;
   SELECT * FROM users WHERE = 1;

2. A의 트랜잭션에서 users 의 id가 1인 first_name 을 "Toto" 로 변경하고 커밋한다. "Hong" 값은 언두 영역으로 백업이 된다.

   START TRANSACTION;
   UPDATE users SET first_name = 'Toto' WHERE id = 1;
   COMMIT;

3. B의 트랜잭션에서 id가 1인 사원을 A 트랜잭션 변경 전,후 로 조회를 한다고 하면 항상 결과는 언두영역의 변경전의 first_name 을 가져올것이다.

   SELECT * FROM users WHERE id = 1;

그리고 REPEATABLE READ 에서도 부정합이 발생할 수 있는데 그것을 팬텀리드(Phantom Read) 라고 한다. 아래의 예시에서 확인해보자.

1. 사용자 B가 id가 1번 이상인 사람을 조회한다. 트랜잭션(T-ID:10)시작

   START TRANSACTION; 
   SELECT * FROM users WHERE id >= 1 FOR UPDATE;

2. 동시에 사용자 A가 id가 2번인 사람을 저장한다. 트랜잭션(T-ID:20)시작 및 커밋

   START TRANSACTION;
   INSERT INTO users VALUES (2, 'Hwang');
   COMMIT;

3. 사용자 B가 1번인상인 사람을 다시 조회한다.

   SELECT * FROM WHERE id >= 1 FOR UPDATE;

위의 3번의 결과는 1번의 결과와 같아야하지만 다르다. 이 현상을 위에서 말한 팬텀리드(Phantom Read)라고 한다.

이 현상은 갭 락과 넥스트 키락으로 REPEATABLE READ 격리 수준에서는 발생하지않으니 격리성을 한단계 더 높일 필요는 없다.

4. SERIALIZABLE

SERIALIZABLE이 설정되면 트랜잭션에서 읽기작업시 공유잠금을 무조건 획득해야 되며 다른 트랜잭션은 해당 레코드에 접근할 수 없게된다. 보통 SERIALIZABLE 설정까지 하진 않는다.

참고

http://www.yes24.com/Product/Goods/103415627

InnoDB는 일관성과 동시성을 보장하기 위해 레코드(Row) 수준의 잠금 방식을 사용하는 MySQL의 스토리지 엔진중 하나이다. 여기서 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식이다. 예를 들어 변경할 레코드를 찾기 위해 검색 된 레코드들을 모두 락 처리 한다.

UPDATE users SET phone_number = 01099998888 WHERE first_name = 'Hong' AND last_name = 'GilDong';

위 쿼리는 이름이 GilDong 이고 성이 Hong 인 사람의 전화번호를 변경하는 쿼리이다. first_name 에만 인덱스가 있다고 하자. 그럼 "Hong" 에 대한 모든 레코드를 잠금 처리를 하게 된다. 그래서 인덱스 설계가 가장 중요한 포인트다.

InnoDB를 사용하는 경우, 명시적으로 잠금을 사용하는경우는 드물고, 트랜잭션을 사용할 땐 Isolation Level에 따라 묵시적으로 사용된다.

InnoDB의 레코드 잠금 유형은 크게 공유 잠금(Shared Lock)과 배타적 잠금(Exclusive Lock)으로 나뉠 수 있다.

1. 공유 잠금(Shared Lock)

공유 잠금(S)는 특정 레코드르 읽을 때 사용된다. 공유 잠금을 획득한 트랜잭션은 해당 레코드를 읽을 수 있지만, 수정할 수는 없다. 여러 트랜잭션이 동시에 같은 레코드를 읽을 수 있다.

#아래의 쿼리는 공유 잠금을 사용하는 예시 이다.

BEGIN;
SELECT * FROM users WHERE first_name = 'Hong' LOCK IN SHARE MODE; #8.0이하
SELECT * FROM users WHERE first_name = 'Hong' FOR SHARE; #8.0이상
COMMIT;

위의 예시에서 LOCK IN SHARE MODE는 해당 레코드에 Shared Lock을 요청하도록 하는 명령어이다. 따라서 위의 쿼리를 실행하면, 해당 레코드를 다른 트랜잭션에서 동시에 읽을 수는 있지만, 수정할 수는 없다.

2. 배타적 잠금(Exclusive Lock)

배타적 잠금(X)는 트랜잭션이 특정 레코드를 수정할 때 사용된다. 배타적 잠금(X)을 획득한 트랜잭션은 해당 레코드를 읽고 수정할 수 있지만, 다른 트랜잭션이 해당 레코드를 읽거나 수정할 수 없다.

#아래의 쿼리는 배타적 잠금을 사용하는 예시 이다.

BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET first_name = 'Hwang' WHERE id = 1;
COMMIT;

위의 예시에서 FOR UPDATE 는 해당 레코드에 대한 Exclusive Lock 를 요청하도록 하는 명령이다. 따라서 위의 쿼리를 실행하면, 해당 레코드를 다른 트랜잭션에서 동시에 읽거나 수정할 수 없다.

3.의도 잠금(Intention Lock)

Intention Lock은 InnoDB 엔진에서 사용되는 내부적인 락 모드 중에 하나로, 다른 락모드를 사용하기전에 필요한 락을 미리 요청하는 용도로 사용된다.

Intention Lock은 다른 락 모드와는 다르게, 특정 레코드에 직접 걸리는 것이 아니라 해당 페이지나 테이블에 대한 락을 걸기 위해 사용된다.

Intention Lock에는 Intention Shared Lock(IS), Intention Exclusive Lock(IX), Shared and Exclusive(SIX) 세가지 모드가 있다.

1.Intention Shared Lock(IS)

Intention Shared Lock(IS)은 트랜잭션이 해당 페이지나 테이블의 개별 행에 공유 잠금을 획득 하려는 락 이다. 다른 트랜잭션이 이미 Exclusive Lock을 걸고 있다면, Intention Shared Lock은 걸 수 없다.

2.Intention Exclusive Lock(IX)

Intention Exclusive Lock(IX)은 트랜잭션이 해당 페이지나 테이블의 개별 행에 배타적 잠금을 획득 하려는 락이다. 다른 트랜잭션이 이미 Shared Lock이나 Exclusive Lock을 걸고 있다면, Intention Exclusive Lock은 걸 수 없다.

예를 들어 위의 두가지 락은 SELECT ... FOR SHARE는 IS 잠금을 설정하고 SELECT ... FOR UPDATE는 IX 잠금을 설정한다.

3. Shared and Exclusive(SIX)

Shared and Exclusive(SIX)은 여러 개의 레코드를 동시에 락 걸 수 있고, 다수의 트랜잭션이 동시에 여러 개의 레코드를 수정하거나 조회하는 경우 유용하다.

SIX 락 모드는 주로 하나 이상의 레코드를 수정할때, 여러개의 레코드를 읽을때, 여러개의 레코드를 수정하거나 읽을때 주로 사용된다.

참고

https://www.letmecompile.com/mysql-innodb-lock-deadlock/ https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

데이터 암호화는 MySQL 5.7 버전부터 지원하며, MySQL 8.0 버전 부터는 리두 로그, 언두 로그, 복제를 위한 바이터리 로그 등도 모두 암호화 기능을 지원한다.

MySQL 서버의 데이터 암호화

MySQL 암호화 기능은 데이터베이스 서버와 디스크 사이의 데이터를 읽고 쓰기 지점에서 암호화 또는 복호화를 수행한다. 즉 InnoDB 스토리지엔진의 IO 레이어에서만 데이터의 암호화 복호화 과정이 실행된다는 뜻이다.

MySQL 서버에서는 사용자의 쿼리를 처리하는 과정에서 테이블의 데이터가 암호화 되어있는지 확인할 필요가없다. 왜냐하면, 스토리지에서 데이터를 쓰기전에 실시간으로 암호화하고 데이터를 읽을때도 실시간으로 복호화를 하기 때문이다. 이러한 암호화 방식을 TDE(Transparent Data Encryption) 라고 한다.

2단계 키 관리(2-Tier)

MySQL 서버의 TDE 에서 암호화 키는 키링(KeyRing) 플러그인에 관리된다. MySQL 에서 지원되는 키링 플러그인은 아래의 링크에서 확인할 수 있다. 👉👉👉 https://dev.mysql.com/doc/refman/5.7/en/keyring.html

MySQL 2단계 키 관리 아키텍처는 다음 그림과 같다.

키관리 진행 순서는

1. Vault와 같은 외부 키 관리 솔루션 또는 디스크의 파일 에서 마스터키를 가져온다.
2. 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블 스페이스키를 발급한다.
3. MySQL서버는 마스터키를 가지고 테이블 스페이스키를 암호화해서 각 테이블의 데이터 파일 헤더에 저장한다.

이렇게 생성된 테이블 스페이스키는 테이블이 삭제되지 않는 한 절대 변경 되지 않는다. 그리고 테이블 스페이스키는 서버내부에 저장되어 있기때문에 주기적으로 변경할 필요가 없고, 보안상 취약점도 없다. 하지만, 마스터키는 외부에 노출되어 있기때문에 주기적으로 변경 해줘야한다.

-- 마스터키 변경을 위한 쿼리 --
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

마스터키를 변경하면 MySQL 서버는 기존의 마스터 키를 사용해 각테이블의 테이블스페이스 키를 복호화 한다음 새로운 마스터키롤 암호화 처리를 한다.

Q. MySQL 에서 2단계 암호화 하는 이유는 무엇인가? 암호화 키 변경으로 인한 과도한 시스템 부하를 피하기 위해서 이다.

Q. TDE에서 지원하는 알고리즘은 무엇이 있는가? AES-256

암호화와 성능

MySQL 서버의 암호화는 TDE 방식이기 사용하므로, 디스크에서 한번 읽은 데이터 페이지는 복호화 되어 InnoDB 버퍼 풀에 적재된다. 그래서 페이지가 적재되면 암호화 되지 않은 테이블과 동일한 성능을 보인다.

존재하지 않는 데이터 페이지를 읽어 올 경우 복호화 하는 과정을 거치기 때문에 복호화 시간 동안 쿼리 지연이 발생할 수 있다. 그리고 암호화된 테이블이 변경 되면 다시 디스크로 동기화될 때 암호화돼야 하기 때문에 디스크에 저장할 때도 추가로 시간이 더 지연된다. 하지만, MySQL 서버의 백그라운드 스레드가 수행하기 때문에 실제 사용자 쿼리가 지연되는건 아니다.

SELECT 뿐만 아니라 UPDATE, DELETE 명령 또한 복호화 지연이 발생할 수 있다.

AES 암호화 알고리즘은 암호화할 대상의 평문 길이가 짧을 경우 암호화 키의 크기에 따라 암호화된 결과의 용량이 더 커질 수도 있지만, 이미 데이터 페이지는 암호화 키 보다 훨씬 크기 때문에 암호화 결과와 평문결과가 동일하다.

암호화 한다고 해서 InnoDB 버퍼 풀의 효율이 달라지거나 메모리 사용효율이 떨어지는 현상은 없다는 이야기 이다.

Q 암호화와 압축이 동시에 적용되면 어느것을 먼저 실행하는가?

서버는 압축을 먼저 실행하고 암호화를 적용한다.

1. 암호화된 데이터는 랜덤한 바이트 배열을 가지게 되는데, 압축률을 떨어뜨린다. 그래서 최대한 압축효율을 높이기 위해 사용자의 데이터를 그대로 압축해서 용량을 최소화 해야 한다.
2. 암호화된 테이블의 데이터 페이지는 복호화된 상태로 버퍼풀에 저장되지만, 압축된 데이터 페이지는 압축 도는 해제된 상태로 버퍼풀에 존재할 수 있다. 그래서 암호화가 먼저 실행되고 압축이 적용된다면, MySQL 서버는 버퍼풀에 존재하는 데이터 페이지에 대해서도 매번 암복호화 작업을 수 행해야한다.

암호화와 복제

MySQL 서버의 복제에서 레플리카 서버는 소스서버의 모든 사용자 데이터를 동기화하기 때문에 실제 데이터 파일도 동일할 것이라 생각이 되지만, 그렇지는 않다. MySQL 서버에서 기본적으로 모든 노드는 각자의 마스터키를 할당한다. 마스터 키 자체가 레플리카로 복제 되지 않기 때문에 테이블 스페이스 키. 또한 레플리카로 복제되지 않는다. 그러므로 평문의 데이터는 동일하더라도 암호화된 데이터의 내용은 다르다.

응용 프로그램 암호화의 비교

응용프로그램에서 직접암호화 하는 방식은 해당 데이터가 암호화된 것인지 여부를 MySQL 서버에서는 인지 하지 못한다. 그리고 인덱스를 생성하더라도 기능을 활용하지 못한다.

CREATE TABLE app_user(
    id BIGINT,
    enc_birth_year VARCHAR(50), // 응용프로그램에서 이미 암호화됨
    ...
    PRIMARY KEY(id)
);

// 응용프로그램에서 이미 암호회된 데이터 조회 쿼리
1. SELECT * FROM app_user WHERE enc_birth_year=#{encryptedYear};

2. SELECT * FROM app_user WHERE enc_birth_year BETWEEN #{encryptedYear} AND #{encryptedMaxYear};

3. SELECT * FROM app_user ORDER BY enc_birth_year LIMIMT 10;

위의 3개의 쿼리로는 조회는 할 수 있다. 그러나 이미 암호화된 값이기에 출생년도의 범위의 사용자를 검색하거나, 출생년도 기준으로 정렬은 불가능하다.

MySQL 서버의 암호화 기능(TDE)를 사용한다면 서버는 인덱스 관련된 작업을 처리후 최종 디스크에 데이터 페이지를 저장할 때만 암호화 하기때문에 제약은 없다.

테이블스페이스 이동

테이블을 다른 서버로 복사해야 하는 경우 또는 특정 테이블의 데이터 파일만 백업했다가 복구하는 경우라면 테이블 스페이스 이동(Export & Import) 기능이 레코드를 덤프 했다가 복구하는 방식보다 훨씬 효율적이고 빠르다.

아래 명령어로 테이블스페이스를 익스포트(Export) 할 수 있다.

mysql> FLUSH TABLES source_table FOR EXPORT;

위의 명령이 실행되면 ... 다음과 같은 과정을 거친다.

1. MySQL 서버는 source_table 의 저장되지 않은 변경 사항을 모두 디스크로 기록
2. 동시에 source_table 구조를 source_table.cfg 파일로 기록
3. source_table.ibd 파일과 source_table.cfg 파일을 목적지 서버로 복사
4. 복사가 완료되면 UNLOCK TABLES 명령어를 실행하여 source_table 사용할 수 있게 하면 끝난다.

언두 로그 및 리두 로그 암호화

테이블의 암호화를 적용하더라도 디스크로 저장되는 데이터만 암호화되고 MySQL 서버의 메모리에 존재하는 데이터는 복호화된 평문으로 관리 된다. 메모리에 존재하는 복호화된 평문 데이터를 테이블 데이터파일 이외에 저장하는 경우엔 여전히 평문으로 저장된다. 리두, 언두, 바이너리 로그도 마찬가지다.

MySQL 8.0.16 버전부터는 innodb_undo_log_encrypt, innodb_redo_log_encrypt 시스템 변수를 사용하여 리두, 언두 로그를 암호화한 상태로 저장할 수 있게 됐다.

실행중인 서버에서 리두, 언두로그를 활성화한다고 해서 모두 암호화되진 않는다. 활성화 시점부터 생성되는 리두, 언두로그만 암호화해서 저장한다. 반대도 비활성화하는 시점에 생성되는 리두, 언두로그만 복호화 된다.

리두, 언 로그 데이터 모두각각의 테이블스페이스 키로 암호화되고, 테이블스페이스 키(프라이빗 키)는 다시 마스터 키로 암호화된다. 암호화된 테이블스페이스키는 리두 로그파일 과 언두 로그 파일 헤더에 저장된다.

InnoDB 리두 로그가 암호화 됐는지는 다음 명령어로 확인할 수 있다.

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_encrypt';

바이너리 로그 암호화

바이너리 로그 암호화는 리두, 언두로그와는 다르게 상당히 긴 시간 동안 보관하는 서비스도 있고 때로는 증분(Incremental Backup) 을 위해 바이너리 로그를 보관하기도 한다. 이런 이유로 바이너리 로그 파일의 암호화는 상황에 따라 중요도가 높아 질 수 있다.

바이너리 로그와 릴레이 로그 파일 암호화 기능은 디스크에 저장된 로그 파일에 대한 암호화만 담당하고, MySQL 서버의 메모리 내부 또는 소스서버와 레플리카 서버 간의 네트워크 구간에서 로그 데이터를 암호화하지 않는다.

바이너리 로그 암호화 키 관리

바이너리 로그와 릴레이 로그 파일의 데이터는 파일 키(File Key)로 암호화해서 디스크로 저장하고, 파일 키는 바이너리 로그 암호화 키로 암호화해서 각 바이너리 로그와 릴레이 로그 파일의 헤더에 저장된다.

바이너리 로그 암호화 키 변경

바이너리 로그 암호화 키는 다음 명령어로 변경할 수 있다.

mysql> ALTER INSTANCE ROTATE BINLOG MASTER KEY;

바이너리 로그 암호화 키가 변경되면 다음의 과정을 거친다.

1. 증가된 시퀀스 번호화 함께 새로운 바이너리 로그 암호화 키 발급 후 키링 파일에 저장
2. 바이너리 로그 파일과 릴레이 로그 파일 스위치(새로운 로그 파일로 로테이션)
3. 새로 생성되는 바이너리 로그와 릴레이 로그 파일의 암호화를 위해 파일 키를 생성, 파일키는 바이너리 로그 파일키로 암호화해서 각 로그파일에 저장
4. 기존 바이너리 로그와 릴레이 로그 파일의 파일 키를 읽어서 새로운 바이너리 로그 파일 키로 암호화해서 다시 저장
5. 모든 바이너리 로그와 릴레이 로그 파일이 새로운 바이너리 로그 암호화 키로 다시 암호화됐다면 기존 바이너리 암호화 키를 키링 파일에서 제거

MySQL 서버의 바이너리 로그 파일이 암호화돼 있는지 여부는 다음 명령어로 확인할 수있다.

mysql> SHOW BINARY LOGS;

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있다.

MySQL 엔진

클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용할 수 있다. 아래의 예제와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

mysql > CREATE TABLE test_table (fd1 IT, fd2 INT) ENGINE = INNODB;

핸들러

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는데,이러한 요청을 핸들러(Handler) 요청이라고 하고, 여기서 사용되는 API를 핸들러 API라고 한다. 아래의 쿼리문으로 이 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있엇는지 확인가능하다.

SHOW GLOBAl STATUS LIKE 'Handler%';

MySQL 스레딩 구조

Mysql 에서 실행 중인 스레드의 목록은 다음과 ㅏㅌ이 pefomance_schema 데이터베이스의 therads 테이블을 통해 화인 할 수 있다.

SELECT thead_id, name, type, processlist_user, pocesslist_host
FROM perfomace_schema.threads ORDER BY type, thread_id;

목록 중에 동일한 스레드가 2개 이상씩 보이는 것은 여러 스레드가 동일 작업을 병렬로 처리하는 경우이다.

포그라운드 스레드(Client Thread)

포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무다. 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread pool)로 되돌아간다. 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드 만 스레드 캐시에 존재하게 한다. 스레드 캐시에 유지 할 수 있는 최대 스레드 개수는 thread_cache_size 시스템 변수로 설정한다. InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB 는 다음과 같이 여러가지 작업이 백그라운드로 실행된다.

1. 인서트 버퍼(Insert Buffer)를 병합하는 스레드
2. 로그를 디스크로 기록하는 스레드
3. InnoDB 버퍼 풀의 데이터를 디스크에 기로하는 스레드
4. 데이터를 버퍼로 일어 오는 스레드
5. 잠금이나 데드라을 모니터링하는 스레드

여기서 가장 중요한 것은 로그 스레드(Log thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을처리하는 쓰기 쓰레드(Write thread) 이다.

MySql 5.5 버전부터 데이터 쓰기 스레드와 데이터 읽기 스레드의 개수를 innodb_write_io_threads 와 innodb_read_io_therads 시스템 변수로 2개 이상 지정할 수 있다.

사용자의 요청을 처리하는 도중 데이터의 쓰기 자업은 지연(버퍼링) 되어 처리 될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다. 대부분의 DBMS 는 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재돼 있다.

메모리 할당 및 사용 구조

글로벌 메모리 영역

하나의 메모리 공간만 할당된다. 모든 스레드에 의해 공유된다. 필요에 따라 2개 이상이 될 수 있지만, 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역은 모든 스레드에게 공유됩니다.

대표적인 글로벌 메모리 영역

1. 테이블 캐시
2. InnoDB 버퍼 풀
3. InnoDB 어댑티브 해시 인덱스
4. InnoDB 리두 로그 버퍼

로컬 메모리 영역

MySql 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다. 대표적으로 커넥션 버퍼와 정렬(소트) 버퍼 등이 있다. 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다. 또 한 가지 중요한 특징이 있는데 가 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 에는 Mysql이 메모리 공간을 할당조차도 하지 않을 수 도 있다는 점이다.

대표적인 로컬 메모리 영역

1. 정렬 버퍼(Sort Buffer)
2. 조인 버퍼
3. 바이너리 로그 캐시
4. 네트워크 버퍼

플러그인 스토리지 엔진 모델

Mysl 의 독특한 구조 중 한가지는 바로 플러그인 모델이다. 스토리지엔진, 전문 검색 엔진을 위한 검색어 파서, 인증 등이 플러그인 형태로 구현되어 제공된다.

컴포넌트

MySql 8.0 부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.

1. 플러그인은 오직 MySql 서버와 인터페이스를 할 수있고 플러그인끼리는 통신 할 수 없다.
2. 플러그인은 MySql 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음
3. 플러그인은 상호 의존 관계를 설정할 수 없어서 최화가 어려움

쿼리 실행 구조

파서

사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의구조로 만들어 내는 작업 한다. 쿼리문장의 기본 문법 오류는 이 과정에서 발견되며 사용자에게 오류 메시지를 전달하게 된다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑에 해당 객체의 존재 여부와 객체의 접근권한 등을 확인하는 과정을 이 단계에서 수행한다. 존재 하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 한다.

실행 엔진

실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.   옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다고 가정했을때, ex) 실행엔진이 핸들러에게 테이블을 만들어, WHERE 절에 일치하는 레코드 읽어와, 읽어온 레코드들 임시 테이블로 저장해등을 요청하고 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈에게 넘긴다.

핸들러

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다. 핸들러는 결국 스토리지엔진을 의미한다.

복제

MySQL의 복제는 레플리케이션이라고도 하는데, 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술이다.

복제에는 INSERT나 UPDATE와 같은 쿼리를 이용해 데이터를 변경할 수 있는 MySQL서버와 SELECT 쿼리로 데이터를 읽기만 할 수있는 MySQL서버로 나뉜다.

MySQL에서는 쓰기와 읽기 역할로 구분해, 전자를 마스터라고 하고 후자를 슬레이브라고 한다. 서버의 복제에서는 머스터는 반드시 1개이며 슬레이브는 1개 이상으로 구성될 수 있다.

** 마스터 **

MySQL의 바이너리 로그가 활성화되면 어떤 MySQL 서버든 마스터가 될 수 있다. 애플리케이션의 입장에서 본다면 마스터 장비는 주로 데이터가 생성 및 변경, 삭제되는 주체(시작점)이라고 볼 수 있다. 일반적으로 복제에 참여하는 여러 서버 가운데 변경이 허용되는 서버는 마스터로 한정할 때가 많다. 그렇지 않은 경우 복제되는 데이터의 일관성을 보장하기 어렵기 때문이다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비 는 그 바이너리 로그를 읽어 슬레이브로 넘긴다. 마스터 장비의 프로세스 가운데 "Bimlog dump"라는 스레드가 이 일을 전담하는 스레드다.

** 슬레이브 **

데이터(바이너리 로그)를 받아 올 마스터 장비의 정보(IP주소와 포트 정보 및 접속 계정)을 가지고 있는 경우 슬레이브가 된다. (마스터나 슬레이브라고 해서 별도의 빌드 옵션이 필요하거나 프로그램을 별도로 설치해야 하는 것은 아니다.)마스터 서버가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있다. 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고 받아 온 변경 내역을 릴레이 로그에 기록한다.그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지한다. I/O 스레드와 SQL 스레드는 마스터 MySQL에서는 가동되 않으며, 복제가 설정된 슬레이브 MySQL 서버에서 자동적으로 가동하는 스레드다.

쿼리 캐시

쿼리 캐시는 MySql 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보인다. 하지만, 변경된 테이블과 관련된 것들을 삭제(Invalidate) 해야 하는 이슈가 있어, 동시 처리 성능 저하를 유발했다. 결국 MySql 8.0 버전으로 올라오면서 쿼리 캐시 기능은 제거 되었다.

스레드풀

커뮤니티 에디션에서는 지원하지 않지만 엔터프라이즈 에디션에서는 스레드 풀(Thread pool) 기능을 제공한다. Percona Server 스레드 풀을 기반으로 설명한다.

스레드 풀의 목적은 사용자의 요청마다 생성되는 스레드 개수를 줄여 동시 요청수가 많아도 제한된 개수의 스레드 처리만 집중할 수 있도록 하는 것이다. 매우 성능이 향상될 것 같지만 실제로 눈에 띄는 성능 향상을 보여주는 경우는 많이 없다. 제한된 수의 스레드를 CPU가 잘 처리할 수 있도록 하는 기능인데 스케줄링 과정에서 CPU 자원이 잘 확보되지 못하면 쿼리 처리가 더 느려진다. 이것이 잘 처리되도록 유도한다면 CPU 프로세서 친화도도 높이고 운영체제 입장에서 컨텍스트 스위치도 줄여서 오버헤드를 줄일 수 있다.

Percona Server와 같은 경우 기본적으로 CPU 코어 개수만큼 스레드를 생성하여 사용한다. (그래야 CPU 프로세서 친화도를 높일 수 있다) 요청이 들어왔을 때 스레드 풀이 처리중인 작업이 있다면 thread_pool_oversubscribe 변수 만큼 추가로 스레드를 받아드려서 처리한다. 만약에 이 값이 너무 크면 스레드가 많아지고 스케줄링을 해야해서 스레드 풀이 비효율적으로 작동할 수 있다.

만약에 모든 스레드가 일을 처리하고 있다면 새로운 worker thread를 추가할지 기다릴지 결정할 수 있다. 이때는 thread_pool_stall_limit 만큼 기다렸다가 그래도 작업이 끝나지 않으면 새로운 스레드를 추가하여 작업을 처리한다. 하지만 여전히 thread_pool_max_threads 를 넘을수는 없다.

Perconam Server는 선순위 큐와 후순위 큐를 사용하여 먼저 처리할 수 있는 트랜잭션이나 쿼리를 우선적으로 처리하여 잠금 경합을 낮추고 전체적인 성능을 향상시키도록 하는 기능을 제공한다.

트랜잭션 지원 메타데이터

선 데이터베이스 서버에서 테이블 구조 정보나 스토어드 프로그램 등의 정보를 데이터 딕셔너리 혹은 메타데이터라고 한다. 이전에는 이것을 파일 기반의 메타데이터로 저장했다. 이 데이터 생성 및 변경 작업을 트랜잭션을 지원하지 않기 때문에 중간에 에러가 나거나 종료가 되면일관되지 않은 상태로 남아있어 문제가 생기게 된다.

따라서 MySQL8.0 부터는 테이블의 구조 정보나 스토어드 프로그램의 코드는 모두 InnoDB의 테이블에 저장하도록 했다. MySQL 서버가 실행하는데 기본적으로 필요한 테이블들을 시스템 테이블이라고 하며 사용자 인증 및 권한 등과 관련된 테이블이 있다. 이런 시스템 테이블과 데이터 딕셔너리 정보를 mysql DB에 저장하고 그것을 mysql.ibd 라는 테이블스페이스에 저장하므로 이것은 각별히 주의하여 관리해야 한다.

이제 트랜잭션 기반의 InnoDB에 저장이 되므로 중간에 실패를 하면 트랜잭션의 원자성 특성에 따라서 완전히 성공하거나 실패한 것은 완전히 정리된다.

InnoDB 스토리지 엔진 아키텍처

프라이머리 키에 의한 클러스터링

외래키 지원

MVCC(Multi Version Concurrency Control)

잠금 없는 일괄된 읽기(Non-Locking Consistent Read)

자동 데드락 감지

자동화된 장애복구

InnoDB 버퍼 풀

버퍼 풀의 크기 설정

버퍼 풀의 구조

버퍼 풀과 리두 로그

버퍼 풀 플러시(Buffer Pool Flush)

플러시 리스트 플러시

LRU 리스트 플러시

버퍼 풀 상태 백업 및 복구

버퍼 풀의 적재 내용 확인

Double Write Buffer

언두 로그

언두 테이블스페이스 관리

체인지 버퍼

리두 로그 및 로그 버퍼

리두 로그 아카이빙

리두 로그 활성화 및 비활성화

어댑티브 해시 인덱스

IPnnoDB 와 MyLSAM, MEMORY 스토리지 엔진 비교

MylSAM 스토리지 엔진 아키텍처

키 캐시

운영체제의 캐시 및 버퍼

데이터 파일과 프라이머리 키(인덱스) 구조

MySQL 로그 파일

에러 로그 파일

MySQL 이 시작하는 과정과 관련된정보성 및 에러 메시지

마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

비정상적으로 종료된 커넥션 메시지(Aborted connection)

InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은) 결과 메시지

MySQL의 종료 메시지

제너럴 쿼리 로그 파일(제너럴 로그파일, General log)

슬로우 쿼리 로그

슬로우 쿼리 통계

실행 빈도 및 누적 실행 시간순 랭킹

쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

보통 서비스의 규모가 작은 경우에 한개의 DataBase 에서 Read 와 Write 작업을 모두 수행 하도록 구현된다. 하지만, 점차 서비스 규모가 커짐에 따라 한 곳에서 모든 작업을 처리하기엔 병목 현상이 발생할 위험이 높아진다.

이를 예방하기 위해 Replication 방법을 사용하게 된다. 이 블로그에서는 DataBase Replication 설정은 건너 뛴다.

여기서 Replication 란 하나 이상의 다른 데이터 베이스(복제본)로 데이터를 복사하는 방법이다. master / slave 나눠 양쪽에 동일한 데이터를 저장 후, master 엔 Write 작업을 slave 엔 Only Read 작업만 처리 되도록 Spring Boot 에서AbstractRoutingDataSource 라는 기능을 제공한다.

AbstractRoutingDataSource 는 조회 키를 기반으로 getConnection() 호출을 다양한 DataSource 중 하나로 라우팅하는 추상 클래스 이다. 이 동적 데이터 소스 라우딩을 구하는데 필요한 4가지가 있다.

1. determineCurrentLookupKey() 는 AbstractRoutingDataSource 의 추상 메소드로 현재 대상 DataSource를 검색한다. 현재 조회 키를 결정하고, targetDataSources 맵에서 조회를 수행하고, 필요한 경우 지정된 기본 대상 DataSource로 대체한다.

2. Threadlocal 는 컨텍스트가 현재 실행 중인 스레드에 바인딩되도록 Threadlocal을 사용하여 스레드 바인딩되는 컨텍스트를 결정하는 Context Holder 구성 요소이다.

3. ReplicationType 는 현재 대상 DataSource 를 결정하기 위한 조회 키로 사용된다.

4. DataSource Bean, Entity 클래스

2. 구현

2.1 application.yml

master, slave 데이터 베이스 정보를 입력한다.

spring:
  datasource:
    master:
      url: jdbc:mysql://localhost:3306/employees?
      username: root
      password: 1234
      driver-class-name: com.mysql.cj.jdbc.Driver

    slave:
      url: jdbc:mysql://localhost:3307/comployees?
      username: root
      password: 1234
      driver-class-name: com.mysql.cj.jdbc.Driver

2.2 Department Entity

package com.spring.oauth2.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Department {

    private String deptNo;
    private String deptName;

}

2.3 DepartmentService

package com.spring.oauth2.service;

import com.spring.oauth2.domain.Department;
import com.spring.oauth2.repository.DepartmentsRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional(readOnly = true)
public class DepartmentsService {

    @Autowired
    DepartmentsRepository departmentsRepository;

    public List<Department> getDepartments() {

        List<Department> deptManagers = departmentsRepository.getDepartments();
        return deptManagers;
    }

    @Transactional
    public void updateDeptNameByDeptNo() {
        int updateCount = departmentsRepository.updateDeptNameByDeptNo();
        System.out.println(updateCount);
    }


}

2.4 DepartmentRepository

package com.spring.oauth2.repository;


import com.spring.oauth2.domain.Department;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface DepartmentsRepository {

    List<Department> getDepartments();

    int updateDeptNameByDeptNo();
}

2.5 MasterDetail, SlaveDetail Configuration

package com.spring.oauth2.config.datasource;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@Data
@ConfigurationProperties(prefix = "spring.datasource.master")
public class MasterDetails {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
}

package com.spring.oauth2.config.datasource;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@Data
@ConfigurationProperties(prefix = "spring.datasource.slave")
public class SlaveDetails {
    private String url;
    private String username;
    private String password;
    private String driverClassName;

}

2.6 DataSource Bean 등록

Master 또는 Slave DataSource 로 Routing 할 DataSource 를 구성한다.

package com.spring.oauth2.config.datasource;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class ReplicationRoutingConfiguration {

    @Autowired
    MasterDetails masterDetails;

    @Autowired
    SlaveDetails slaveDetails;

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

    @Bean
    public DataSource replicationDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        DataSource masterDataSource = masterDataSource();
        DataSource slaveDataSource = slaveDataSource();
        targetDataSources.put(ReplicationType.WRITE, masterDataSource);
        targetDataSources.put(ReplicationType.READ, slaveDataSource);

        ReplicationDataSourceRouter clientRoutingDatasource = new ReplicationDataSourceRouter();
        clientRoutingDatasource.setTargetDataSources(targetDataSources);
        clientRoutingDatasource.setDefaultTargetDataSource(masterDataSource);
        return clientRoutingDatasource;
    }

    @Bean
    public DataSource masterDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(masterDetails.getDriverClassName());
        hikariDataSource.setJdbcUrl(masterDetails.getUrl());
        hikariDataSource.setUsername(masterDetails.getUsername());
        hikariDataSource.setPassword(masterDetails.getPassword());
        hikariDataSource.setMaximumPoolSize(10);
        return hikariDataSource;
    }

    @Bean
    public DataSource slaveDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(slaveDetails.getDriverClassName());
        hikariDataSource.setJdbcUrl(slaveDetails.getUrl());
        hikariDataSource.setUsername(slaveDetails.getUsername());
        hikariDataSource.setPassword(slaveDetails.getPassword());
        hikariDataSource.setMaximumPoolSize(10);
        return hikariDataSource;
    }

}

2.7 Replication Type

데이터 소스에 대한 조회 키 역할을 하는 Enum 이다.

package com.spring.oauth2.config.datasource;

public enum ReplicationType {
    READ, WRITE
}

2.8 ReplicationDataBaseContextHolder

ReplicationDataBaseContextHolder는 Thread 바인딩된 컨텍스트의 저장소 역할을 하는 구성 요소이다. 컨텍스트 설정, 검색 및 삭제하는데 사용 된다.

package com.spring.oauth2.config.datasource;

import org.springframework.util.Assert;

public class ReplicationDataBaseContextHolder {

    private static ThreadLocal<ReplicationType> CONTEXT = new ThreadLocal<>();

    public static void set(ReplicationType dataSourceType) {
        Assert.notNull(dataSourceType, "dataSourceType cannot be null");
        CONTEXT.set(dataSourceType);
    }

    public static ReplicationType get() {
        return CONTEXT.get();
    }

    public static void clear() {
        CONTEXT.remove();
    }
}

3. 결론

해당 코드 깃헙에서 확인할 수 있다. GitHub

기본적으로 이러한 UNION 방법은 쉽게 쿼리를 작성 할 수 있게 해준다. 하지만, 성능적인 측면에서 굉장히 큰 단점을 가질 수 있다.

위의 송금진행건을 조회하는 SELECT 구문과 송금완료된 건을 조회하는 SELECT 구문을 합치는 것이기 때문에 각 테이블을 2회 접근하게 된다. 데이터를 읽는 비용이 선형적으로 증가함은 물론, 긴 표현 때문에 개발자가 쿼리를 보는데 있어 쉽게 피로해질 수 있다.

이 문제를 최적화 할 수 있는 방법이 한가지가 있는데 SELECT 구문에서 CASE 식으로 조건을 분기처리하면 된다.

즉, 현재 송금진행, 송금완료건들의 상태를 CASE 식으로 작성하여 UNION 을 사용한 구문 없이 올해 송금액을 구할 수 있게 된다.

위의 방법을 사용하면 테이블에 대한 접근이 1회로 줄어들며, UNION 을 사용한 방법보다 성능이 2배 향상되고, 가독성도 좋아져 개발자의 피로를 덜어 줄 수 있다.

UNION을 사용한 분기는 SELECT 구문을 단위로 분기처리한다. 반면 CASE 키워드는 식을 바탕으로 하는 사고 이기때문에 최적화 하는데 있어 중요하다.

reference : https://www.hanbit.co.kr/channel/category/category_view.html?cms_code=CMS8208581587

오늘 정리할 내요은 선택정렬이다. 선택정렬(Selection Sort)은 기준이되는 원소를 정한 후 전체 원소중 가장 작은 원소를 찾아 자리를 교환하는 방식이다.

다음은 선택 정렬을 수행하는 과정을 나타낸다.

step 1. 첫 번째 자리를 기준 위치로 정한다음, 전체 원소 값중에서 가장 작은 값을 선택하여 기준 위치에 있는 원소와 자리를 교체한다.

step 1 [ 기준(50), 20, 30, 작은원소(5), 18, 9, 35, 26 ] step 1 [ 기준(5), 20, 30, 50, 18, 9, 35, 26 ]

step 2. 두 번째 자리를 기준 위치로 정한다음, 나머지 원소 값중에서 가장 작은 원소 값을 선택하여 기준 위치에 있는 원소와 자리를 교체한다.

step 2 [ 5, 기준(20), 30, 50, 18, 작은원소(9), 35, 26 ] step 2 [ 5, 기준(9), 30, 50, 18, 20, 35, 26 ]

*step 3. * 이 방식을 마지막 원소(n-1) 까지 진행하면된다.

public void selectionSort(int[] list, int length) {

    int temp;
    int min;
    for (int i = 0; i < length; i++) {
        min = i;
        for (int j = i + 1; j < length; j++) {
            if (list[j] < list[min]) {
                min = j;
            }
        }
        temp = list[i];
        list[i] = list[min];
        list[min] = temp;
    }
}

public void print(int[] list) {

    for (int element : list) {
        System.out.printf("%d ", element);
    }
}


@Test
public void should_Calculation_When_Input() {

    int[] list = {50, 20, 30, 5, 18, 9, 35, 26};
    int length = list.length;
    selectionSort(list, length);
    print(list);

}

이러한 방법으로 각 단계에서는 i 번째 원소를 기준으로 n-1 개의 원소를 비교하게 되는데 전체 비교 횟수는 다음 식으로 나타낼 수 있다.

시간복잡도 : O(N^2)

아래의 salaries 테이블의 Parimary key 가 (emp_no + from_date) 로 설정되어 있을경우 조회 결과는 중복된 emp_no 가 발생할 수 있다.

SELECT * FROM employee e
WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary > 150000)

하지만, 아래의 쿼리엔 GROUP BY 절을 넣어주었는데 결과는 위의 세미 조인 서브쿼리와 동일한 결과를 얻었다.

SELECT e.* FROM employees.employees e , employees.salaries s 
WHERE e.emp_no  = s.emp_no AND s.salary > 150000
GROUP BY e.emp_no ;

실제로 Duplicate Weedout 최적화 알고리즘은 원본 쿼리를 위와 같이 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다.

Weedout 최적화 알고리즘 처리과정은 다음과 같다.

1. salaries 테이블의 ix_salary 인덱스를 스캔해서 salary 가 150000 보다 큰 사원을 검색하여 employees 테이블의 조인을 실행
   2. 조인된 결과를 임시 테이블에 저장
   3. 임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
   4. 중복을 제거하고 남은 레코드를 최종적으로 반환

Weedout 최적화 알고리즘은 다음과 같은 제한사항이 있다.

1. 서브 쿼리가 상관 서브쿼리라 하더라도 사용할 수 있는 최적화이다.
2. 서브 쿼리가 GROUP BY 나 집합 함수가 사용된 경우에는 사용될 수 없다.
3. Duplicate Weedout 은 서브 쿼리의 테이블을 조인으로 처리하기 때문에 최적화를 할 수 있는 방법이 많다.

1. paimary key(emp_no)

   SELECT MAX(emp_no), MIN(emp_no) FROM employees;

2. paimary key(emp_no, from_date)

   SELECT MAX(from_date), MIN(from_date) FROM salaries WHERE emp_no = 10001;

1 번째 쿼리는 employees 테이블에 emp_no 컬럼이 인덱스로 생성되어 있으므로 최적화가 가능하다.

2 번째 쿼리는 salaries 테이블에 emp_no, from_date 컬럼이 인덱스로 생성되어 있으므로 인덱스가 emp_no = 10001 인 레코드를 검색하고, 검색된 결과 중에서 오름차순 또는 내름차순으로 하나만 조회하면 되는 최적화 방법이다.

일반 적으로 MariaDB 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지만 디스크로 옮겨진다.

임시테이블이 필요한 쿼리

1. ORDER BY 와 GROUP BY 에 명시된 컬럼이 다른 쿼리
2. ORDER BY 나 GROUP BY 에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 경우
3. DISTINCT 와 ORDER BY 가 동시에 쿼리에 존재하는 경우 또는 DISTINCT 가 인덱스로 처리되지 못하는 경우
4. UNION 이나 UNION DISTINCT 가 사용된 쿼리(select_type 컬럼이 UNION RESULT인 경우)
5. UNION ALL 이 사용된 쿼리(select_type 컬럼이 UNION RESULT 인 경우)
6. 쿼리의 실행 계획에서 select_type 이 DERIVED 인 쿼리

1번부터 4번까지는 유니크 인덱스를 가진 내부 임시테이블이 만들어진다. 5번과 6번은 인덱스가 없는 내부 임시 테이블이 만들어진다. 일반적으로 유니크 인덱스가 있는 내부 임시테이블이 처리능력이 더 좋다.

임시테이블 관련 상태 변수

explain Extra 에 Using temporary 가 한번 표시됐다고 해서 임시 테이블을 하나만 사용했다는 것을 의미 하지 않는다. 임시 테이블이 메모리나 디스크에 생성됐는지 확인하려면 MariaDB 서버의 상태변수 (SHOW SESSION STATUS LIKE 'Created_tmp%';) 확인하면된다.

인데스를 가지는 내부 임시 테이블

MySQL 5.5, MariaDB 5.2 버전까지는 내부 임시 테이블은 항상 인덱스가 없이 생성됐다. 그래서 내부 임시 테이블(DERIVED)이 드라이빙이 아니라 드리븐 테이블로 사용되거나 검색용으로 사용되는 경우에는 성능이 저하됐다. 이후 버전부터는 자동으로 인덱스를 추가한 상태로 임시 테이블을 생성한다.

위의 explain 에서 key 필드에 출력된 auto_key0은 dept_name 에 인덱스를 생성했다는 것을 의미 한다.

내부 임시 테이블(Internal Temporary Table)의 주의사항

SELECT * FROM employees GROUP BY last_name ORDER BY first_name;

위 쿼리는 GROUP BY 와 ORDER BY 칼럼이 다르다. 그리고 last_name 에 인덱스가 없다고 하면 임시 테이블과 정렬작업까지 수정해야 하는 쿼리 형태이다.

내부 작업 과정은 다음과 같다.

 1. employees 테이블의 모든 컬럼을 포함한 임시테이블 생성(MEMORY 테이블)
 2. employees 테이블로부터 첫 번째 레코드를 innoDB 스토리지 엔진으로부터 가져와서 
 3. 임시 테이블에 같은 last_name 이 있는지 확인
 4. 같은 last_name 없으면 임시 테이블에 insert
 5. 같은 last_name 있으면 임시 테이블에 update 또는 무시
 6. 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블을 Aria 스토리지 엔진을 이용해 서 디스크로 이동 
 7. employess 테이블에서 더 읽을 레코드가 없을 때까지 2~5번 과정 반복
 8. 최종 내부 임시 테이블에 저장된 결과에 대해 정렬작업을 수행
 9. 클라이언트에 결과 반환

여기서 중요한점은 가능한 인덱스를 사용해 처리하고, 임시 테이블을 생성하지 않도록 처리해야한다. 또한, 임시 테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성되는 경우도 주의 해야한다. SELECT 절의 컬럼은 최소화하면서 BLOB나 TEXT컬럼은 배제, 데이터 타입 선정도 가능한 적게해주는것이 좋다.

** TIP ** 쿼리중에서 FROM 절 사용되는 서브 쿼리는 무조건 임시 테이블을 생성하므로 주의해야한다. 또한, UNION, UNION ALL 이 사용된 쿼리도 항상 임시테이블을 생성하여 결과를 병합한다.

인덱스를 사용하지 못하는 정렬 작업은 임시 버퍼 공간을 사용하는데, 정렬할 레코드가 많아지면 결국 디스크 자원을 사용한다. 정렬에 사용되는 버퍼도 결국 임시 테이블과 같다.

인덱스 정렬

ORDER BY에 명시된 컬럼이 제일 먼저 읽은 테이블(드라이빙)에 속하고 ORDER BY 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE절에 첫 번째 읽은 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야한다. 그리고 B-TREE 계열 인덱스만 정렬을 사용할 수 있다.

그 이유는 B-TREE는 인덱스가 키값으로 정렬돼 있기 때문에 순서대로 읽기만 하면 되기때문이다.

드라이빙 테이블만 정렬

조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 뒤 조인을 실행하는 것이 모든 레코드를 정렬하는 것보다 더 효율 적이다.

SELECT * FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 10001 AND 10010
ORDER BY e.last_name

위 쿼리의 ORDER BY 절에 명시된 컬럼은 employees 테이블의 primary key 와 연관이 없으므로 인덱스를 사용한 정렬은 불가능하다. 하지만, e.last_name 에 포함된 컬럼이므로 옵티마이저는 employees(드라이빙) 테이블만 먼저 정렬 하고 그결과와 salaries 테이블과 조인을 수행한다.

임시 테이블을 이용한 정렬

임시 테이블을 이용한 정렬은 위의 정렬처리 방식중 가장 느린 방식이다.

SELECT * FROM employees e, salaries s
WHERE s.emp_no = e.emp_no AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary

위의 쿼리는 employees 테이블이 드라이빙 테이블이지만 salaries 드리븐 테이블의 컬럼을 사용하여 정렬을 수행하였다. 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 이 쿼리는 반드시 조인된 데이터를 가지고 정렬할 수 밖에 없다.

일반적으로는 조인이 수행이 되면 레코드 건수는 거의 배수로 늘어나기 때문에 가능하다면 드라이빙 테이블을 정렬한 다음 조인을 수행하는 방법이 효율적이다.