만들고자 했던 클래스의 기능은 다음과 같습니다.

1. 타입 안정성

2. 클래스 외부에서 클래스 속성의 직접 변경이 불가능해야 합니다.

   메소드를 통해서만 변경 가능합니다.

    serviceMethod() {
        const article = new Article(...)
   
        // ❌ 직접 변경시 타입 오류 발생해야함
        article.title = 'newTitle'
   
        // ✅ 메소드를 통해 변경 가능
        article.changeTitle('newTitle')
    }

3. 모든 속성 값을 읽을 수 있어야 합니다.

   필요한 값을 함수로 추출하는 것과 별개로 DB 저장을 위해 모든 값을 가져올 수 있어야 합니다.

4. 불완전한 객체를 생성하지 못하도록 합니다

    // ❌ 전체 속성을 가진 완전한 객체만 만들 수 있어야 합니다.
    const article = new Article()
   
    // ✅ 생성자 또는 팩토리 함수에서 모든 속성이 만들어져야 합니다.
    const article = Article.of({ ... })
    const article = new Article({ ... })

5. (optional) key-value 형식으로 인스턴스 생성이 가능하도록 합니다.

    // ❌
    new Article('id', 'title', 'contents')
   
    // ✅
    new Article({ id: 'id', title: 'title', contents: 'contents' })

게시글(Article) 모델을 예시로 만들어봅니다.

팩토리 메소드 create()와 changeTitle() 메소드만 가지고 있는 단순한 엔티티입니다.

0. 초기 코드

type ArticleProps = {
  id: string
  title: string
  contents: string
  updatedAt: Date
  createdAt: Date
}

export class Article {
  constructor(
    public readonly id: string,
    private title: string,
    private contents: string,
    private createdAt: Date,
    private updatedAt: Date,
  ) {}

  static create(
      args: Pick<ArticleProps, 'id' | 'title' | 'contents'>,
      current = new Date(),
  ) {
    return new Article(
      args.id,
      args.title,
      args.contents,
      current,
      current,
    );
  }

  changeTitle(title: string, current = new Date()) {
    this.title = title;
    this.updatedAt = current;
  }

  toPayload(): Readonly<ArticleProps> {
    return {
      id: this.id,
      title: this.title,
      contents: this.contents,
      createdAt: this.createdAt,
      updatedAt: this.updatedAt,
    };
  }
}

타입스크립트에서 private(or #) 으로 정의한 속성은 type에 표시되지 않습니다.

외부 변경을 막기위해 모든 속성을 private으로 하고,

DB 저장을 위한 toPayload() 함수를 만들어주었습니다.

가진 기능이 얼마 없음에도 클래스의 크기가 큽니다.

1. public readonly 사용

모든 속성을 private 으로 만들기보다, public readonly로 설정해서 외부 변경을 막아봅니다.

• 공통 타입

export type Mut<T> = {
  -readonly [Key in keyof T]: T[Key]
}

• 도메인 클래스

type ArticleProps = NonFunctionProps<Article>;

export class Article {
  constructor(
    public readonly id: string,
    public readonly title: string,
    public readonly contents: string,
    public readonly updatedAt: Date,
    public readonly createdAt: Date,
  ) {}

  static create(
    args: Omit<ArticleProps, 'updatedAt' | 'createdAt'>,
    current = new Date(),
  ) {
    return new Article(
      args.id,
      args.title,
      args.contents,
      current,
      current,
    );
  }

  changeTitle(
    this: Mut<Article>,
    title: string,
    current = new Date(),
  ) {
    this.title = title;
    this.updatedAt = current;
  }
}

러스트(&mut self) 참고했습니다.

모든 속성을 public readonly 로 설정해서 외부에서 조회할 수 있도록 하고,

클래스 내부에서는 this에서 readonly 제거하여 값의 변경이 가능합니다.

public 으로 설정하여 속성들이 type에 표시되고, 조회도 가능하므로 toPayload 함수도 필요 없습니다.

원하는 기능들이 어느정도 구현된 상태입니다.

실제로 변하지 않는 속성을 구분할 수 없는 단점이 있습니다.

2. 상위 클래스 사용

클래스 상속으로 해결해봅니다.

• 공통 상위 클래스 정의

export abstract class BaseEntity<Props> {
  readonly #props: Props;

  constructor(
    props: Props,
  ) {
    this.#props = { ...props };
  }

  protected get props(): Props {
    return this.#props;
  }

  toPayload(): Readonly<Props> {
    return { ...this.#props };
  }
}

• 도메인 클래스

interface ArticleProps {
  readonly id: string;
  title: string;
  contents: string;
  updatedAt: Date;
  readonly createdAt: Date;
}

class Article extends BaseEntity<ArticleProps> {
  changeTitle(
    title: string,
    current = new Date(),
  ) {
    this.props.title = title;
    this.props.updatedAt = current;
  }

  static create(
    args: Omit<ArticleProps, 'createdAt' | 'updatedAt'>,
    current = new Date(),
  ) {
    return new Article({
      ...args,
      updatedAt: current,
      createdAt: current,
    });
  }
}

많이 사용되는 패턴입니다.

속성 인터페이스가 클래스 외부로 나오면서 클래스에서는 메소드만 남았습니다.

protected 접근자의 props를 통해 내부에서만 값의 수정이 가능합니다.

클래스 외부에서는 toPayload()를 통해 모든 속성을 조회할 수 있습니다.

프로퍼티에 접근할 때마다 props 에서 가져와야 하지만 원하는 기능이 모두 구현되었습니다.

3. 상위 클래스 사용 두번째 - props 제거

this.props.title 말고 this.title로 접근할 수 있을까

• 상위 클래스 생성 메소드

export const BaseEntity: new <Props extends Record<string, any>>(args: Props) => Props =
  class {
    constructor(args: any) {
      Object.assign(this, args);
    }
  } as any;

• 도메인 클래스

interface ArticleProps {
  readonly id: string;
  title: string;
  contents: string;
  updatedAt: Date;
  readonly createdAt: Date;
}

class Article extends BaseEntity<ArticleProps> {
  changeTitle(
    title: string,
    current = new Date(),
  ) {
    this.title = title;
    this.updatedAt = current;
  }

  static create(
    args: Omit<ArticleProps, 'updatedAt' | 'createdAt'>,
    current = new Date(),
  ) {
    return new Article({
      ...args,
      updatedAt: current,
      createdAt: current,
    });
  }
}

상속을 사용하면서 props 를 안써보려고 시도한 방법입니다.

타입으로 만들었기 때문에 각 속성에 protected를 사용할 수 없고, 클래스 외부에서도 변경이 가능해졌습니다.

모든 속성을 readonly로 설정할 수도 있지만, 그러면 클래스 내부에서도 변경이 불가능 해집니다.

타입스크립트에서는 클래스의 속성을 외부에서만 readonly로 설정하는 기능이 없습니다.

하지만 미리 만들어진 클래스를 변경할 수는 있습니다.

4. 기존 클래스에 readonly 설정

• 공통 함수

export const makeReadonly: <T extends ClassType<any>>(args: T)
        => new (...args: ConstructorParameters<T>)
        => Readonly<InstanceType<T>> =
        (baseClass: ClassType<any>) => baseClass;

• 도메인 클래스

type ArticleProps = NonFunctionProps<Article>;

class Article extends makeReadonly(class {
  constructor(
    public readonly id: string,
    public title: string,
    public contents: string,
    public updatedAt: Date,
    public readonly createdAt: Date,
  ) {}

  changeTitle(
    title: string,
    current = new Date(),
  ) {
    this.title = title;
    this.updatedAt = current;
  }
}) {
  static create(
    args: Omit<ArticleProps, 'updatedAt' | 'createdAt'>,
    current = new Date(),
  ) {
    return new Article(
      args.id,
      args.title,
      args.contents,
      current,
      current,
    );
  }
}

이미 만들어진 클래스에 함수를 래핑해서 외부에서는 readonly가 되도록 하였습니다.

래핑함수로 뭔가 할 수 있다는걸 알았으니 추가적으로 기능을 붙여봅니다

5. 기존 클래스 래핑 함수 두번째 - 생성자 추가

• 공통 함수

export const makeEntity: <T>(args: ClassType<T>) => new (args: NonFunctionProps<T>) => Readonly<T> =
  (entityClass: any) =>
      class extends entityClass {
        constructor(args: any) {
          super();
          Object.assign(this, args);
        }
      } as any;

• 도메인 클래스

type ArticleProps = NonFunctionProps<Article>;

class Article extends makeEntity(class {
  public readonly id!: string;
  public title!: string;
  public contents!: string;
  public updatedAt!: Date;
  public readonly createdAt!: Date;

  changeTitle(
    title: string,
    current = new Date(),
  ) {
    this.title = title;
    this.updatedAt = current;
  }
}) {
  static create(
    args: Omit<ArticleProps, 'updatedAt' | 'createdAt'>,
    current = new Date(),
  ) {
    return new Article({
      ...args,
      updatedAt: current,
      createdAt: current,
    });
  }
}

기존 클래스를 래핑해서 클래스 외부에서 readonly를 붙여주고, 생성자를 Key-Value 형식으로 만들어주었습니다.

원하는 기능은 모두 구현됬지만, 일반적으로 사용되는 문법이 아니라 사용이 꺼려집니다.

6. 불변 객체 사용

이제 클래스를 사용하지 않고, 불변의 인터페이스를 활용합니다.

• 불변 도메인 정의

interface Article {
  readonly id: string;
  readonly title: string;
  readonly contents: string;
  readonly updatedAt: Date;
  readonly createdAt: Date;
}

namespace Article {
  export const of =
    (self: Article): Article =>
      self;

  export const changeTitle =
    (title: string, current = new Date()) =>
      (self: Article) =>
        of({
          ...self,
          title,
          updatedAt: current
        });

  export const create =
    (
      args: Omit<Article, 'updatedAt' | 'createdAt'>,
      current = new Date(),
    ) =>
      of({
        ...args,
        updatedAt: current,
        createdAt: current,
      });
}

• 사용 예시

const article = Article.create({ id: 'id', title: 'title', contents: 'contents' });

// effect-ts의 pipe 사용
const changedArticle = pipe(
  article,
  Article.changeTitle('newTitle'),
);

도메인을 불변으로 정의하고, 변경이 필요한 경우 새로운 객체를 생성합니다.

네임스페이스를 통해 클래스의 정적 메소드처럼 사용할 수 있습니다.

클래스를 상속하거나, 생성자를 정의하는 등의 복잡한 방법이 없이 직관적인 것이 장점입니다.

객체가 수정되지 않으므로 실수할 가능성도 줄어듭니다.

Effect-TS와 같은 함수형 라이브러리를 사용하는 경우 적용할만한 방법입니다.

공통 주의사항

• Object.assign 사용하는 경우

  생성자에서 Object.assign(this, props) 을 사용하는 경우, 원하지 않는 값이 들어갈 수 있습니다.

  
    const propsWithOther = {
        id: 'id',
        title: 'title',
        contents: 'contents',
        changeTitle: 123
    }
  
    const article = Article.create(propsWithOther);
  
    article.changeTitle; // 123

• 구조분해(destructuring) 사용하는 경우

  getter 로 만드는 경우 {...} 사용시 값이 출력되지 않습니다.

    const props = {
        id: 'id',
        get title() {
            return 'title'
        },
        contents: 'contents'
    }
  
    const article = Article.create({...props})
  
    article.title // undefined

두 경우 모두 생성 메소드에 key-value를 직접 입력해주어야 문제가 없습니다.

const param = someMethod()

// ❌ 외부에서 가져온 값을 그대로 입력하지 않습니다.
Article.create(param)

// ✅ key-value 형태로 입력합니다.
Article.create({
  id: param.id,
  title: param.title,
  contents: param.contents,
})

정리

자바의 lombok 처럼 깔끔하게 해결되지 않습니다.

상속을 사용한 2번 방법이 실무에서 사용하기에 안정적으로 보입니다.

모든 코드는 GitHub 에 올려두었습니다.

BrandedType이란 기본형 타입에 brand 속성을 추가한 것입니다.

type UserId = string & { brand: 'UserId' }

UserId는 문자열 타입이지만 brand 속성이 추가되었으므로 문자열보다 더 구체화된 타입입니다.

즉 문자열이 필요로 하는 곳에 들어갈 수 있지만, 일반 문자열이 UserId에 들어갈 순 없습니다.

function inputString(param: string) { /* ... */ }
function inputUserId(param: UserId) { /* ... */ }

// ✅ userId가 string 에 들어가는 건 가능
inputString('userId' as UserId)

// ❌ 일반 문자열이 UserId가 될 순 없음
inputUserId('notUserId')

BrandedType은 개발자의 입력 실수를 막고 VO 타입을 대체하는 등 많은 곳에서 사용됩니다.

export type Hour = number & { brand: 'Hour' }
export type Duration = number & { brand: 'Duration' }
export type UserId = string & { brand: 'UserId' }
export type OrderId = string & { brand: 'OrderId' }
export type Timestamp = number & { brand: 'Timestamp' }
export type OrderPrice = number & { brand: 'OrderPrice' }

declare userId: UserId;
declare orderId: OrderId;

const myFunction = 
    (userId: UserId, orderId: OrderId) => 
        { ... }

// ❌ 두 파라미터의 순서가 바뀌어 type이 맞지 않기 때문에 컴파일시 타입 오류 발생
myFunction(orderId, userId)

BrandedType 은 기본형 타입이기 때문에 기본형 타입에서 가능한 연산과 메소드도 모두 사용할 수 있습니다.

export type Hour = number & { brand: 'Hour' }
declare const value1: Hour;
declare const value2: Hour;

// number 타입이라 덧셈이 가능하다
console.log(value1 + value2)

BrandedType도 관련 함수를 클래스처럼 한 곳에서 관리하고, dot 표현식으로 사용하고 싶지만

타입스크립트에서 type은 인스턴스 메소드나 정적 메소드, 생성자를 가질 수 없습니다.

하지만 네임스페이스를 활용하면 팩토리 메소드를 만들거나 관련 메소드를 가질 수 있습니다.

BrandedType과 네임스페이스

타입스크립트에서 namespace는 type과 동일한 이름으로 생성할 수 있습니다.

선언 병합: https://www.typescriptlang.org/ko/docs/handbook/declaration-merging.html

type A = ...
namespace A {
  ...
}

즉 BrandedType을 Namespace와 함께 사용할 수 있습니다.

// BrandedType 선언
export type Duration = number & { brand: 'Duration' }

// 동일한 이름의 namespace 안에 팩토리 메소드와 관련 메소드 구현
export namespace Duration {

  const of =
    (value: number) =>
      value as Duration;

  export const from =
    (param: {
      hour?: number
      minute?: number
      second?: number
      millisecond?: number
    }) =>
      of(
        ((param.millisecond || 0)) +
        ((param.second || 0) * 1000) +
        ((param.minute || 0) * 1000 * 60) +
        ((param.hour || 0) * 1000 * 60 * 60),
      );

  export const add = 
      (
          value1: Duration,
          value2: Duration,
      ) => 
          of(value1 + value2)
}

사용

const fiveMinutes: Duration = Duration.from({ minute: 5 });
console.log(fiveMinutes); // 300000

const complexDuration: Duration = Duration.from({ hour: 1, minute: 30, second: 15 });
console.log(complexDuration); // 5415000

const added = Duration.add(
        Duration.from({ minute: 5 }),
        Duration.from({ second: 10 })
)
console.log(added); // 310000

Namespace와 함수형 라이브러리 함께 사용하기

네임스페이스로도 인스턴스 메소드는 만들 수 없는 점이 아쉽습니다.

duration1.add(duration2) 와 같이 사용할 순 없지만

함수형 라이브러리의 pipe와 함께 고차함수나 커링을 사용하는 경우 가독성에 더 유리할 수 있습니다.

export namespace Duration {
    // ...    

  // 초 단위로 변환
    export const toSecondsWithoutMs =
    (self: Duration) =>
      Math.floor(self / 1000);

  // 고차 함수로 구현한 add
  export const add =
    (value: Duration) =>
      (self: Duration) =>
        of(self + value);

  // curry 사용해서 구현한 sub
  export const sub = curry(
    (value: Duration, self: Duration) =>
      of(self - value),
  );
}

//-------------------------------------

// 사용
const result = pipe(
  Duration.from({ hour: 10 }),
  Duration.add(
      Duration.from({ minute: 100 })
  ),
  Duration.sub(
      Duration.from({ hour: 3 })
  ),
  Duration.toSecondsWithoutMs
);

console.log(result); // 31200

그 외 사용하는 선언 병합

• Enum 과 Namespace

  namespace는 type 뿐 아니라 enum 과 함께 사용 가능합니다.

        export enum MyEnum {
      a = 'A',
      b = 'B',
      c = 'C'
    }
  
    export namespace MyEnum {
      export const initial: MyEnum = MyEnum.a;
  
      export const next =
        (before: MyEnum) =>
          ({
            [MyEnum.a]: MyEnum.b,
            [MyEnum.b]: MyEnum.c,
            [MyEnum.c]: MyEnum.a,
          })[before];
    }
  
    const init: MyEnum = MyEnum.initial;
    const next: MyEnum = MyEnum.next(init);

• Type 과 Function

  단순 팩토리 메소드만 필요한 BrandedType은 네임스페이스가 아닌 일반 함수로 간단하게 표현할 수 있습니다.

    // 선언
    export type UserId = string & { brand: 'UserId' }
    export function UserId(value: string) {
        return value as UserId
  
    /*------------------*/
  
    // 사용
    const userId: UserId = UserId('123')

제가 이해한대로 작성되어 올바르지 않은 단어나 정보가 있을 수 있습니다

문제 상황

자바 언어에는 메소드에서 어떤 예외를 던질 수 있는지 명시적으로 작성합니다

public void myMethod throws SomeException {
  // ..
}

타입스크립트는 위와 같은 기능이 없기 때문에 어떤 함수를 실행할 때 발생할 수 있는 예외에 대한 정보를 얻을 수 없습니다.

interface UserService {
  signIn(email: string, password: string): Promise<AuthToken>
}

위 서비스를 실행했을 때, 패스워드가 틀린경우 or 일치하는 이메일이 없는 경우

어떤 오류가 발생하는지 알 수 없습니다.

위와 같은 문제를 해결하기 위한 방법을 찾던 중 Effect-TS를 알게 되어 사용해보게 되었습니다.

Effect-ts는 아주 많은 기능을 제공하지만 가장 기본이 되는 Effect 타입부터 알아보겠습니다.

Effect<A, E, R> 타입

Effect 타입은 아래와 같이 생겼습니다

export interface Effect<out A, out E = never, out R = never> ...

Effect<A, E, R>은 다음과 같습니다.

• A: 성공시 반환값
• E: 문제가 발생한 경우 오류 타입
• R: 필요한 의존성

DI 관련 기능인 R 타입은 NestJS 를 사용하므로 일단 보류하고

A와 E만 사용하는 예제로 시작합니다.

// 1. 기존 방식
const divide =
  (dividend: number, divisor: number): number => {
    if (divisor === 0) {
      throw Error();
    }
    return dividend / divisor;
  };


// 2. Effect
const divideEffect =
  (dividend: number, divisor: number): Effect.Effect<number, DivideByZero> =>
    divisor === 0
      ? Effect.fail({ _tag: 'DivideByZero' })
      : Effect.sync(() => dividend / divisor);

나누기를 하는 함수를 일반적인 방법과 Effect를 사용한 방법으로 작성하였습니다.

Effect.fail()은 실패를 나타내고, Effect.sync()는 동기 함수로 실행하는 성공케이스를 나타냅니다.

Effect를 사용한 경우 반환 타입만 봐도 어떤 오류가 발생할 수 있는지 알 수 있습니다.

기존 함수의 경우 간단한 예시라 구현을 봐도 바로 알 수 있지만

일반적으로 함수 안에서 여러 함수를 호출하고 그 안에서도 다른 함수를 호출하기 때문에

실제로 어떤 오류가 발생할지 하나하나 확인하기는 아주 어렵습니다

Effect 의 경우 함수 내에서 실행한 다른 함수의 오류를 그대로 누적하여 반환 타입에 넘겨줍니다

declare const methodA: (value: number) => Effect.Effect<number, A>;
declare const methodB: (value: number) => Effect.Effect<number, B>;
declare const methodC: (value: number) => Effect.Effect<number, C>;

const myMethod =
  (value: number) =>
    pipe(
      methodA(value),
      Effect.andThen((value) => methodB(value)),
      Effect.andThen((value) => methodC(value)),
    );

const myEffect: Effect.Effect<number, A | B | C> = myMethod(100)

각각 A B C 오류가 발생할 수 있는 함수 methodA, methodB, methodC 가 있을 때,

methodA, methodB, methodC 를 순차적으로 실행하면

각 단계를 거치면서 발생할 수 있는 오류 A,B,C가 누적되어 myMethod()의 결과인 myEffect 에 나타납니다.

myEffect를 실행하는 경우 성공하여 number 가 나오거나, A,B,C 중의 한 오류가 발생할 수 있습니다.

에러 핸들링

Effect 를 실행하기 전 오류를 처리하는 것이 가능합니다.

declare const methodA: (value: number) => Effect.Effect<number, A>;
declare const methodB: (value: number) => Effect.Effect<number, B>;
declare const methodC: (value: number) => Effect.Effect<number, C>;

const myMethod =
  (value: number) =>
    pipe(
      methodA(value),
      Effect.andThen((value) => methodB(value)),
      Effect.andThen((value) => methodC(value)),

      // A 오류만 처리
          Effect.catchTag('A', () => Effect.succeed(1000)),

          // // B, C 오류 처리
           // Effect.catchTags({
           //   'B': () => Effect.succeed(1000),
          //   'C': () => Effect.succeed(1000)
          // }),
          //
          // // 모든 오류 처리
          // Effect.catchAll(() => Effect.succeed(1000))
    );
const myEffect: Effect.Effect<number, B | C> = myMethod(500)

처리한 오류는 타입에서 제외되어 B 와 C만 남은 모습입니다.

NestJS 로그인 예시

NestJS에서 Effect 를 사용하여 간단한 로그인 기능을 구현해봅니다.

// auth.service
@Injectable()
class AuthService {
   // ...

     signIn(
        email: string,
        password: string
  ): Effect.Effect<AuthToken, UserNotFound> {
    return pipe(    

      // 1. 입력한 email로 유저를 가져옵니다.
      // -> Effect<User, UserNotFound>
      this.repository.findByEmail(email),

      // 2. 패스워드를 검증합니다.
      // -> Effect<User, UserNotFound | InvalidPassword>
      Effect.tap((user) =>
        this.passwordEncryptor.compare(password, user.password),
      ),     

      // 4. 유저 정보로 인증 토큰을 생성합니다.
      // -> Effect<AuthToken, UserNotFound | InvalidPassword>
      Effect.andThen((user) =>
        this.authTokenFactory.create(user),
      ),

      // 4. 보안상 InvalidPassword 에러를 UserNotFound 오류로 변경합니다.
      // -> Effect<AuthToken, UserNotFound>
            Effect.catchTag('InvalidPassword', () => new UserNotFound())

    );
  }
}

user.repository

@Injectable()
class UserRepository {

  findByEmail(email: string) {
    return pipe(

            // DB 에서 유저 조회
      // -> Effect<UserEntity | null>
      Effect.promise(() =>
        this.repository.findOne({
          where: { email },
        }),
      ),

      // null인 경우 실패 처리
      // -> Effect<UserEntity, UserNotFound>
      Effect.filterOrFail(
        (userEntityOrNull) => userEntityOrNull !== null,
        () => new UserNotFound(),
      ),

      // 도메인 모델로 변환
      // -> Effect<User, UserNotFound>
      Effect.andThen((userEntity) =>
        UserMapper.toDomain(userEntity),
      ),

    );
  }  
}

password.encryptor

// 의존성 주입을 위해 interface 대신 abstract class 사용
abstract class PasswordEncryptor {
    abstract compare(
        input: string, 
        hashed: string
    ): Effect.Effect<void, InvalidPassword>
}

마지막으로 컨트롤러를 구현해봅니다

@Controller(...)
class UserController {

  @Post('sign-in')
  signIn(
    @Body() dto: SignInDto,
  ) {
        return pipe(
            this.authService.signIn(dto.email, dto.password),

            // 오류 처리
            // Effect.catchTags(...)

            // 실행
            Effect.runPromise,
        )
  }
}

컨트롤러에서 runPromise로 Effect를 실행시켜주어야 실제 로직이 실행됩니다.

컨트롤러에서 매번 runPromise로 실행시키는 것 보다

interceptor로 분리하여 전역으로 설정해줍니다. 필요한 경우 로깅이나 다른 기능도 추가할 수 있습니다.

// 컨트롤러에서 Effect를 반환하는 경우 실행합니다.
@Injectable()
export class EffectInterceptor implements NestInterceptor {
  intercept(
    context: ExecutionContext,
    next: CallHandler,
  ): Observable<any> | Promise<Observable<any>> {
    const ctx = context.switchToHttp();
    const response = ctx.getResponse<Response>();

    return next
      .handle()
      .pipe(
        map(async (value) => {

            // 반환 타입이 Effect가 아닌 경우 그대로 리턴
          if (!Effect.isEffect(value)) {
            return value;
          }

                    // Effect 실행
          const result = await pipe(
            value as TEffect<any>,
            Effect.runPromiseExit,
          );

          // 성공한 경우 성공객체 리턴
          if (Exit.isSuccess(result)) {
            return result.value;
          }

          const cause = result.cause;          
          // Die인 경우 처리
          if (Cause.isDieType(cause)) {
            response.status(500);
            return cause.defect;
          }

                    // 일반 실패인 경우 처리
          if (Cause.isFailType(cause)) {
            response.status(400);
            return cause.error;
          }

                    // 예외
                  // ... logging
          return cause;
        }),
      );
  }
}

컨트롤러에서 이제 Effect 타입을 그대로 반환할 수 있습니다.

class UserController {

  @Post('sign-in')
  signIn(
    @Body() dto: SignInDto,
  ) {
        return pipe(
            this.authService.signIn(dto.email, dto.password),

            // 필요한 오류 처리
            // Effect.catchTags(...)
        )
  }
}

Effect-TS-Typeorm 라이브러리

Effect 타입은 실행 전까지 실제 로직을 실행하지 않습니다.

const random = () => Effect.sync(() => Math.random())
const myEffect = random()

console.log(Effect.runSync(myEffect))
console.log(Effect.runSync(myEffect))
console.log(Effect.runSync(myEffect))

// 0.9610924668137069
// 0.8621419846703764
// 0.46989516306422563

random() 함수를 실행해서 myEffect를 얻는 시점에 Math.random() 함수가 아직 실행되지 않습니다.

myEffect를 실제로 run할때 내부 함수인 Math.random() 함수가 실행되기 때문에

실행할때마다 각각 다른 랜덤 값을 출력하는 것을 볼 수 있습니다.

일반적인 경우 컨트롤러/인터셉터와 같은 가장 끝단에서 Effect를 실행시키는 방식이 별 문제가 되지 않습니다.

하지만 트랜잭션 기능을 데코레이터로 사용한다면 문제가 됩니다.

@Transactional()
myFunction(): Effect.Effect<...> {
  pipe(
      this.repository.usingTransactionMethod()
  )
}

트랜잭션 데코레이터 라이브러리는

실제론 조금 더 복잡하지만 간단하게 표현하면 이런식으로 되어있습니다.

const Transactional = (
  target: any,
  methodName: string | symbol,
  descriptor: TypedPropertyDescriptor<unknown>,
) => {
  const originalMethod = descriptor.value as () => unknown;
  descriptor.value = function(this: unknown, ...args: unknown[]) {
    try {
        await transactionBegin()

        const result = await originalMethod.apply(this, args)

        await transactionCommit()

        return result
    } catch {
        await transactionRollback()
    }
  }
};

즉 데코레이터가 붙은 기존 함수(originalMethod)의 실행 전후로 트랜잭션 기능을 실행하도록 되어있습니다

트랜잭션을 사용하는 함수가 Promise를 반환하기 때문인데

Effect를 사용하는 경우 originalMethod는 실제 실행 시점이 아닌 effect를 반환하는 시점이기 때문에 올바르지 않습니다.

effect 반환 함수에서 Transactional 사용하는 경우

try {
  await transactionBegin()

  // 트랜잭션 필요 없는 부분
  const effect = await originalMethod.apply(this, args)

  await transactionCommit()

  return effect
} catch {
  await transactionRollback()
}

// ------------------------------------------

// 트랜잭션이 필요한 부분
await Effect.runPromise(effect)

데코레이터를 쓰지 않고 트랜잭션을 실행해서 객체를 넘기는 방법도 있지만

Effect 연습겸 라이브러리로 제작하였습니다.

NPM: https://www.npmjs.com/package/@jys9962/effect-ts-typeorm

https://github.com/jys9962/effect-ts-typeorm

사용 후기

Effect 라이브러리를 간단하게 사용해보았습니다.

위 내용 뿐 아니라 Effect 에서는 Array, Stream, Date, Either, Struct, Schema, Logging, DI 등의 많은 기능이 있고 웹프레임워크도 개발되어 있습니다.

아직까지는 DI나 웹프레임워크까지 사용해서 NestJS를 대체하기엔 이르다는 느낌이 듭니다.

실제 사용 예시를 찾기가 어렵고 공식 문서에도 빠진 부분이 많습니다.

pipe에서 사용이 편하도록 만들어진 Effect의 Array, Stream, String, Number 등의 간단한 기능과

Either<R, L>, Effect<A,E> 정도는 실무에서도 적용할만 한 것 같습니다.

AWS 공식 블로그에서도 관련 글을 찾아볼 수 있습니다

CF 사용시 아래와 같은 장점이 있습니다.

1. 네트워크 응답이 빠릅니다

   • CF의 많은 엣지 로케이션 중 현재 사용자와 가장 가까운 엣지 서버에 접근하여 가져오므로 웹서버보다 가까울 확률이 높습니다.

2. 트래픽 전송 비용이 더 저렴합니다.

   • CF를 통해 데이터를 전송하는 것이 EC2에서 직접 전송하는 것보다 비용이 저렴합니다.
   • EC2→CF는 무료*입니다

3. 정적 데이터 캐싱이 가능합니다

4. 하나의 도메인에 여러 경로를 각각 다른 원본에 매핑할 수 있습니다.

   ex:

   • https://mydomain.com/image/* ⇒ s3/image
   • https://mydomain.com/api/* ⇒ ec2
   • https://mydomain.com/* ⇒ s3/website
   • …

5. 이 외에도 SSL 적용이 쉽고 ddos 방지 등의 장점이 있습니다.

기본 사용 방법

CF에서 사용되는 단어 몇가지만 알면 쉽게 사용할 수 있습니다.

1. 배포: CF로 제공되는 하나의 도메인과 매핑됩니다.

2. 원본: CF가 데이터를 가져올 실제 서버입니다.
   S3, EC2, Lambda 또는 외부의 서버가 될 수 있습니다.
   하나의 배포에 하나 이상의 원본을 가질 수 있습니다.

3. 동작: 요청 받은 경로에 대해 어떻게 처리할지 설정합니다.
   위에서 설정한 원본 중 어디서 데이터를 가져올지
   가져온 데이터는 얼마나 오래 저장할지, 캐시키는 어떻게 할지 등
   데이터의 전/후 처리 등

4. 무효화: 하나의 배포에서 캐시된 데이터를 삭제합니다.
   전체 또는 원하는 경로에 대해서만 진행할 수 있습니다.

5. 정책: 동작에서 사용할 미리 정의된 설정 템플릿 원본 요청, 응답 헤더, 캐시에 대한 설정이 미리 만들어져서 제공됩니다.
   새로운 정책을 만들거나 기존 정책을 사용해도 되고, 정책을 사용하지 않고 동작에서 직접 설정해도 됩니다.

하나의 배포를 만들고, 사용할 원본들을 모두 등록 후 경로에 맞게 동작탭에서 매핑해주면 됩니다.

웹 서버에서 CF를 통해 데이터를 가져올 때는 ‘AllViewer’ 원본 요청 정책을 설정하는 것이 일반적입니다.

웹서버 캐시 사용 사례

CF 응답 캐싱은 변경이 발생하지 않는 데이터에 사용하기 좋습니다.

예를 들어, RDB에서 append-only(추가만 되는 구조)의 내역 테이블이 있습니다

CREATE TABLE articles
(
    id         BIGINT UNSIGNED PRIMARY KEY,
    writer_id  BIGINT UNSIGNED NOT NULL,
    created_at DATETIME        NOT NULL
) comment '게시글'
;
CREATE TABLE article_histories
(
    id         BIGINT UNSIGNED PRIMARY KEY,
    article_id BIGINT UNSIGNED NOT NULL,
    title      VARCHAR(255)    NOT NULL,
    contents   MEDIUMTEXT      NOT NULL,
    created_at DATETIME        NOT NULL,
    FOREIGN KEY (article_id) REFERENCES articles (id)
) comment '게시글 변경내역'

게시글을 작성 한 후 수정하더라도 테이블에 수정(update) 없이 article_histories 테이블에 행이 하나 추가(insert)됩니다.

/article/{id}/histories/{historyId} 응답을 CloudFront에서 캐싱하면, 데이터가 수정되지 않으므로 무효화가 필요 없이 항상 최신 데이터를 받을 수 있습니다.

즉, 캐싱 이후 클라이언트의 요청은 서버에 전혀 접근하지 않고 빠르게 데이터를 가져올 수 있습니다.

원본 데이터가 수정된 경우, 무효화 기능이 있지만 비용이 발생하고 느립니다. (1회당 0.005 USD = 약 7원)

데이터가 바뀔때마다 무효화 기능을 사용하기엔 무리가 있습니다.

대신, 버전을 나타내는 쿼리 파라미터를 활용하는 것이 일반적입니다.

CloudFront는 기본적으로 URL의 Path가 다르면 다른 데이터로 판단하여 원본을 다시 조회합니다.

또한 설정을 통해 Header나 Query 문자열, Cookie 값이 달라도 다른 데이터로 판단하도록 할 수 있습니다.

가장 다루기 쉬운 Query 문자열로 원하는 파라미터(_v)를 설정합니다.

위 처럼 설정하는 경우 아래 두 요청은 Path는 동일하지만 _v 쿼리가 다르므로 다른 데이터로 판단합니다.

• /article/1234?_v=1

• /article/1234?_v=2

수정이 발생할 때마다 _v를 1씩 증가시키거나 하는 방법으로 최신 데이터를 조회할 수 있습니다.

하지만 _v 값을 어딘가에 저장한 후 요청할 때마다 조회해야 하는 단점이 있습니다.

1. 열려 있지 않은 앱은 실행
2. 열려있는 앱은 활성화
3. 여러 창이 열려있는 앱은 키를 여러번 눌러서 원하는 창 활성화 (오래된순 정렬)

Mac에서 같은 기능을 찾으려고 많은 무료/유료 앱을 사용해봤지만 3번 기능을 지원하는 앱이 없었습니다.

그러던 중 Hammerspoon 을 알게되어 직접 구현해서 사용하고 있습니다.

Hammerspoon이란?

Mac에서 사용할 수 있고, Lua 라는 언어로 다양한 API를 제공합니다.

키보드, 앱, 창, 마우스, 볼륨, Alert 등을 컨트롤 할 수 있습니다.

구현

해머스푼 API로 필요한 기능은 다음과 같습니다.

1. 앱 실행하기
2. 앱에 대한 모든 창(window) 정보 가져오기 - 열린 순서대로
3. 창 활성화
4. 창 목록을 표시할 모달창
5. 키보드 이벤트 바인딩

다행히 위 기능이 모두 있습니다.

hs api 사용해서 키 입력에 대한 이벤트 함수를 바인딩합니다.

local function register(key, appName, launchName)
    local function initOrNext()
        controller.initOrNext(appName, launchName)
    end

    hs.hotkey.bind({ 'option' }, key, initOrNext, nil, initOrNext)
end

컨트롤러를 작성해줍니다.

function initOrNext(appName, launchName)
    local tabName = getTabName(appName)
    local currentTabName = tabAlert.getTabName()
    if tabName == currentTabName then
        tabAlert.nextTab()
        return ;
    end

    local windowList = getSortedWindows(appName)
    if windowList == nil or #windowList == 0 then
        hs.application.launchOrFocus(launchName or appName)
        return ;
    end

    local title = appName
    tabAlert.startTab(tabName, title, windowList, 1)
end

⚠️ 문제 해결과정

• 모달창 구현시 성능 이슈 (hs.drawing)
  • 반응이 너무 느려서 대신 hs.alert API를 사용했습니다.(only 텍스트)
• 모달창으로 Alert API 사용하다보니 모달창의 가로 크기가 텍스트 길이에 따라 바뀌는 이슈
  • 헤더/푸터에 고정된 길이의 긴 가로선(—)을 넣어서 가로 크기가 고정되도록 했습니다.
• 특정 앱의 오래된순으로 정렬된 창을 가져오는 API의 성능 이슈 (hs.window.filter)
  • 키를 누를때마다 api를 사용하지 않고 초기에 구독(subscribe) 함수를 사용해서 변수로 관리합니다.
    위 함수 때문에 초기 로딩이 몇 초 걸립니다.

✨ 완성

github : https://github.com/jys9962/my-hammerspoon/tree/main/libs/optionKey

위와 같이 만들어서 사용한지 이제 반년정도 된 것 같습니다.

가끔 앱을 추가로 등록하거나 수정하면서 쓰고 있습니다.

IDE, 웹브라우저, AI, DB Tool, iterms, notion, 카톡, 메모, 캡처 등 자주 쓰는 앱은 모두 등록해서 사용중입니다.

참고한 블로그: https://johngrib.github.io/wiki/hammerspoon/ 공식문서: https://www.hammerspoon.org/docs/index.html

저번글에서 사용한 테이블에 추가하여 사용합니다.

CREATE TABLE products
(
    id            INT UNSIGNED                       NOT NULL
        PRIMARY KEY,
    name          VARCHAR(255)                       NOT NULL,
    description   VARCHAR(255)                       NOT NULL,
    price         INT UNSIGNED                       NOT NULL,    
    review_count  INT UNSIGNED                          NOT NULL COMMENT '리뷰 개수',  
    review_score  INT UNSIGNED                       NOT NULL COMMENT '리뷰 점수 총합',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
) COMMENT '상품';

CREATE TABLE categories
(
    id         SMALLINT UNSIGNED NOT NULL
        PRIMARY KEY,
    parent_id  SMALLINT UNSIGNED NOT NULL,
    name       VARCHAR(255)      NOT NULL,
    created_at DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '카테고리';

CREATE TABLE product_category_mapping
(
    product_id  INT      UNSIGNED NOT NULL,
    category_id SMALLINT UNSIGNED NOT NULL,

    PRIMARY KEY (category_id, product_id)
) COMMENT '상품-카테고리 매핑'

이번글에서는 상품 목록 검색 쿼리 작성을 목표로 합니다. 카테고리는 총 1000개이며 N단계로 나눠지고 부모의 id를 참조하고 있습니다.

상품 목록의 기능은 다음과 같습니다.

• 카테고리를 0개 이상 선택할 수 있습니다. 선택한 카테고리의 자식과 매핑된 상품도 표시되야 합니다.
• 등록일자/가격/리뷰개수/리뷰점수로 정렬(오름차순/내림차순) 할 수 있어야 합니다.

카테고리가 3단계로 [옷] - [상의] - [티셔츠]로 구성되있다면 [상의]를 선택하면 [상의]와 [티셔츠] 에 매핑된 상품이 나와야 합니다. [옷]을 선택한다면 [옷], [상의], [티셔츠]에 매핑된 상품들이 모두 나와야 합니다.

우선 성능을 고려하지 않은 쿼리입니다.

[Query]
    WITH RECURSIVE cte AS (
                                    SELECT c.id
                                    FROM categories c
                                    WHERE c.id IN (11, 101, 201)
                                    UNION ALL
                                    SELECT c.id
                                    FROM categories c
                                         INNER JOIN cte
                                                    ON cte.id = c.parent_id
                                )
          SELECT *
          FROM (
                   SELECT DISTINCT pcm.product_id
                   FROM product_category_mapping pcm
                        INNER JOIN cte
                                   ON cte.id = pcm.category_id
               ) a
               INNER JOIN products p
                          ON p.id = a.product_id
          ORDER BY p.price DESC
          LIMIT 500, 100    

[Result]
100 rows / 59.79275ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬─────────────────┬─────────────────┬─────────┬────────────────┬──────┬──────────┬───────────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type     │ possible_keys   │ key             │ key_len │ ref            │ rows │ filtered │ Extra                             │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼─────────────────┼─────────────────┼─────────┼────────────────┼──────┼──────────┼───────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ '<derived2>' │ null       │ 'ALL'    │ null            │ null            │ null    │ null           │ 5252 │ 100      │ 'Using temporary; Using filesort' │
│ 1       │ 1  │ 'PRIMARY'   │ 'p'          │ null       │ 'eq_ref' │ 'PRIMARY'       │ 'PRIMARY'       │ '4'     │ 'a.product_id' │ 1    │ 100      │ null                              │
│ 2       │ 2  │ 'DERIVED'   │ '<derived3>' │ null       │ 'ALL'    │ null            │ null            │ null    │ null           │ 10   │ 100      │ 'Using where; Using temporary'    │
│ 3       │ 2  │ 'DERIVED'   │ 'pcm'        │ null       │ 'ref'    │ 'PRIMARY'       │ 'PRIMARY'       │ '4'     │ 'cte.id'       │ 525  │ 100      │ 'Using index'                     │
│ 4       │ 3  │ 'DERIVED'   │ 'c'          │ null       │ 'range'  │ 'PRIMARY'       │ 'PRIMARY'       │ '4'     │ null           │ 3    │ 100      │ 'Using where'                     │
│ 5       │ 4  │ 'UNION'     │ 'cte'        │ null       │ 'ALL'    │ null            │ null            │ null    │ null           │ 3    │ 100      │ 'Recursive; Using where'          │
│ 6       │ 4  │ 'UNION'     │ 'c'          │ null       │ 'ref'    │ 'idx_parent_id' │ 'idx_parent_id' │ '4'     │ 'cte.id'       │ 2    │ 100      │ 'Using index'                     │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴────────────────┴──────┴──────────┴───────────────────────────────────┘
-> Limit/Offset: 100/500 row(s)  (actual time=38.3..38.3 rows=100 loops=1)
    -> Sort: p.price DESC, limit input to 600 row(s) per chunk  (actual time=38.3..38.3 rows=600 loops=1)
        -> Stream results  (cost=3559 rows=5253) (actual time=7.9..32.1 rows=19647 loops=1)
            -> Nested loop inner join  (cost=3559 rows=5253) (actual time=7.9..24.7 rows=19647 loops=1)
                -> Table scan on a  (cost=1653..1721 rows=5253) (actual time=7.89..8.99 rows=19647 loops=1)
                    -> Materialize  (cost=1653..1653 rows=5253) (actual time=7.89..7.89 rows=19647 loops=1)
                        -> Table scan on <temporary>  (cost=1059..1127 rows=5253) (actual time=5.88..7.04 rows=19647 loops=1)
                            -> Temporary table with deduplication  (cost=1059..1059 rows=5253) (actual time=5.87..5.87 rows=19647 loops=1)
                                -> Nested loop inner join  (cost=534 rows=5253) (actual time=0.0896..3.39 rows=21710 loops=1)
                                    -> Filter: (cte.id is not null)  (cost=7.26..3.62 rows=10) (actual time=0.0552..0.0612 rows=29 loops=1)
                                        -> Table scan on cte  (cost=7.91..10.3 rows=10.6) (actual time=0.0549..0.0586 rows=29 loops=1)
                                            -> Materialize recursive CTE cte  (cost=7.66..7.66 rows=10.6) (actual time=0.0546..0.0546 rows=29 loops=1)
                                                -> Filter: (c.id in (11,101,201))  (cost=2.26 rows=3) (actual time=0.0179..0.0209 rows=3 loops=1)
                                                    -> Index range scan on c using PRIMARY over (id = 11) OR (id = 101) OR (id = 201)  (cost=2.26 rows=3) (actual time=0.0168..0.0195 rows=3 loops=1)
                                                -> Repeat until convergence
                                                    -> Nested loop inner join  (cost=4.34 rows=7.56) (actual time=0.0026..0.0146 rows=13 loops=2)
                                                        -> Filter: (cte.id is not null)  (cost=2.84 rows=3) (actual time=500e-6..0.00152 rows=14.5 loops=2)
                                                            -> Scan new records on cte  (cost=2.84 rows=3) (actual time=291e-6..646e-6 rows=14.5 loops=2)
                                                        -> Covering index lookup on c using idx_parent_id (parent_id=cte.id)  (cost=0.334 rows=2.52) (actual time=624e-6..766e-6 rows=0.897 loops=29)
                                    -> Covering index lookup on pcm using PRIMARY (category_id=cte.id)  (cost=5.76 rows=525) (actual time=0.0327..0.0856 rows=749 loops=29)
                -> Single-row index lookup on p using PRIMARY (id=a.product_id)  (cost=0.25 rows=1) (actual time=691e-6..708e-6 rows=1 loops=19647)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 39544  │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 19708  │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 21736  │
│ Handler_read_prev                │ 0      │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 39355  │
└──────────────────────────────────┴────────┘

with 문이 가장 먼저 실행됩니다. 선택한 카테고리id로 카테고리를 찾고 자식 카테고리를 재귀적으로 찾도록 되어있습니다. 다음으로 서브쿼리에서는 with문으로 찾은 카테고리id로 모든 상품번호를 매핑테이블에서 찾습니다. 이때 각 카테고리id에 대해 수직탐색1회와 상품개수만큼의 수평탐색이 일어납니다. 상품 번호가 중복될 수 있으므로 distinct 처리를 해줬습니다. 서브쿼리에서 찾은 상품의 id로 각각 수직탐색하여 상품을 찾고, price로 정렬하여 결과를 출력합니다.

가장 문제가 되는 부분은 페이징 목록임에도 정렬을 위해 일치하는 모든 상품을 id로 각각 조회하는 것 입니다. 정렬을 위해서는 where 조건과 정렬 조건이 하나의 인덱스에 있어야 하는데 정렬을 위한 price는 상품테이블에, 조회를 위한 카테고리id는 매핑 테이블에 있습니다.

상품 테이블에 매핑된 카테고리의 id들을 json으로 넣는 방법을 사용해보겠습니다.

# json 컬럼 추가
ALTER TABLE products
    ADD COLUMN category_ids JSON NOT NULL AFTER id;

# 추가한 컬럼 업데이트
UPDATE products p
SET p.category_ids = IFNULL(
        (
            SELECT JSON_ARRAYAGG(category_id)
            FROM product_category_mapping pcm
            WHERE pcm.product_id = p.id
        ), JSON_ARRAY())
WHERE TRUE

상품 테이블에 매핑된 카테고리id를 모두 넣었습니다. json 타입은 일반 인덱스를 사용할 수 없고, 멀티밸류 인덱스를 사용해야 합니다.

ALTER TABLE products
    ADD INDEX idx_category_ids ((CAST(category_ids AS UNSIGNED ARRAY)))

쿼리를 수정해봅니다.

[Query]
    WITH RECURSIVE cte AS (
                              SELECT c.id
                              FROM categories c
                              WHERE c.id IN (49, 220, 582)
                              UNION ALL
                              SELECT c.id
                              FROM categories c
                                   INNER JOIN cte
                                              ON cte.id = c.parent_id
                          )
    SELECT p.*
    FROM products p
    WHERE JSON_OVERLAPS(
                  p.category_ids,
                  (
                      SELECT JSON_ARRAYAGG(id) AS ids
                      FROM cte
                  )
          )
    ORDER BY p.price
    LIMIT 500, 100

[Result]
100 rows / 26.741ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬─────────┬────────────────────┬────────────────────┬─────────┬──────────┬──────┬──────────┬───────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type    │ possible_keys      │ key                │ key_len │ ref      │ rows │ filtered │ Extra                         │
├─────────┼────┼─────────────┼──────────────┼────────────┼─────────┼────────────────────┼────────────────────┼─────────┼──────────┼──────┼──────────┼───────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ 'p'          │ null       │ 'range' │ 'idx_category_ids' │ 'idx_category_ids' │ '9'     │ null     │ 3861 │ 100      │ 'Using where; Using filesort' │
│ 1       │ 2  │ 'SUBQUERY'  │ '<derived3>' │ null       │ 'ALL'   │ null               │ null               │ null    │ null     │ 10   │ 100      │ null                          │
│ 2       │ 3  │ 'DERIVED'   │ 'c'          │ null       │ 'range' │ 'PRIMARY'          │ 'PRIMARY'          │ '4'     │ null     │ 3    │ 100      │ 'Using where'                 │
│ 3       │ 4  │ 'UNION'     │ 'cte'        │ null       │ 'ALL'   │ null               │ null               │ null    │ null     │ 3    │ 100      │ 'Recursive; Using where'      │
│ 4       │ 4  │ 'UNION'     │ 'c'          │ null       │ 'ref'   │ 'idx_parent_id'    │ 'idx_parent_id'    │ '4'     │ 'cte.id' │ 2    │ 100      │ 'Using index'                 │
└─────────┴────┴─────────────┴──────────────┴────────────┴─────────┴────────────────────┴────────────────────┴─────────┴──────────┴──────┴──────────┴───────────────────────────────┘
-> Limit/Offset: 100/500 row(s)  (cost=1739 rows=100) (actual time=10.6..10.6 rows=100 loops=1)
    -> Sort: p.price, limit input to 600 row(s) per chunk  (cost=1739 rows=3861) (actual time=10.5..10.6 rows=600 loops=1)
        -> Filter: json_overlaps(cast(category_ids as unsigned array),json'[49, 220, 582, 424, 529, 977]')  (cost=1739 rows=3861) (actual time=0.0237..9.03 rows=3830 loops=1)
            -> Index range scan on p using idx_category_ids over (977 MEMBER OF (category_ids)) OR (582 MEMBER OF (category_ids)) OR (4 more)  (cost=1739 rows=3861) (actual time=0.0222..6.58 rows=3830 loops=1)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 11801  │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 15     │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 3860   │
│ Handler_read_prev                │ 0      │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 13     │
└──────────────────────────────────┴────────┘

json_overlaps 함수는 두 json 배열을 비교해서 하나라도 동일하면 TRUE를 반환하는 함수입니다. status 값이 많이 줄었지만 첫 번째 쿼리와 동일하게 모든 상품을 가져와서 정렬 후 반환하고 있습니다. mysql의 멀티밸류 인덱스 특성상 여러 컬럼으로 구성할 수 없어서 정렬을 위한 price를 인덱스에 추가할 수 없습니다.

카테고리가 많고 각 카테고리에 매핑된 상품의 개수가 적다면 이 방법도 괜찮을 것 같습니다.

검색용 테이블 분리

위 방법이 카테고리에 맞는 상품을 먼저 모두 찾은 다음 정렬하였다면, 이제 해볼것은 정렬되어있는 목록을 찾으면서 카테고리가 조건에 맞는지 확인하는 방법입니다. 이미 정렬이 되있기 때문에 원하는 개수를 찾으면 검색을 종료할 수 있습니다.

필요한 인덱스는 (price, category_ids) 입니다. 하지만 json 타입은 일반 인덱스에 포함할 수 없으니 테이블을 새로 만듭니다.

CREATE TABLE product_search
(
    product_id   INT UNSIGNED NOT NULL,
    category_id  INT UNSIGNED NOT NULL,
    price        INT UNSIGNED NOT NULL,
    review_count INT UNSIGNED NOT NULL,
    review_score INT UNSIGNED NOT NULL,
    review_avg   DOUBLE UNSIGNED AS (IF(review_count > 0, review_score / review_count, 0)) VIRTUAL,

    PRIMARY KEY (product_id, category_id),
    INDEX idx_price (price, product_id),
    INDEX idx_review_count (review_count, product_id),
    INDEX idx_review_avg (review_avg, product_id)
)
;

상품-카테고리 매핑과 비슷하지만 정렬용 컬럼들이 추가되었습니다. 하나의 상품이 카테고리별로 여러번 입력되는 구조입니다. 리뷰 평균 점수를 위해 개수와 총합으로 가상 컬럼을 생성했습니다.

쿼리를 해봅니다.

[Query]
    WITH RECURSIVE cte AS (
                              SELECT c.id
                              FROM categories c
                              WHERE c.id IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 21, 23, 24, 25, 49, 220, 582)
                              UNION ALL
                              SELECT c.id
                              FROM categories c
                                   INNER JOIN cte
                                              ON cte.id = c.parent_id
                          )
    SELECT p.*
    FROM (
             SELECT ps.product_id
             FROM product_search ps
             WHERE ps.category_id IN (
                                         SELECT id
                                         FROM cte
                                     )
             GROUP BY ps.price
                    , ps.product_id
             ORDER BY ps.price DESC
             LIMIT 500, 100
         ) a
         INNER JOIN products p
                    ON p.id = a.product_id

[Result]
100 rows / 11.3725ms

[Explain]
┌─────────┬────┬────────────────┬───────────────┬────────────┬──────────┬───────────────────────┬───────────────────────┬─────────┬───────────────────────┬──────┬──────────┬────────────────────────────────────┐
│ (index) │ id │ select_type    │ table         │ partitions │ type     │ possible_keys         │ key                   │ key_len │ ref                   │ rows │ filtered │ Extra                              │
├─────────┼────┼────────────────┼───────────────┼────────────┼──────────┼───────────────────────┼───────────────────────┼─────────┼───────────────────────┼──────┼──────────┼────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'      │ '<derived2>'  │ null       │ 'ALL'    │ null                  │ null                  │ null    │ null                  │ 600  │ 100      │ null                               │
│ 1       │ 1  │ 'PRIMARY'      │ 'p'           │ null       │ 'eq_ref' │ 'PRIMARY'             │ 'PRIMARY'             │ '4'     │ 'a.product_id'        │ 1    │ 100      │ null                               │
│ 2       │ 2  │ 'DERIVED'      │ 'ps'          │ null       │ 'index'  │ 'idx_price'           │ 'idx_price'           │ '8'     │ null                  │ 600  │ 100      │ 'Backward index scan; Using index' │
│ 3       │ 2  │ 'DERIVED'      │ '<subquery3>' │ null       │ 'eq_ref' │ '<auto_distinct_key>' │ '<auto_distinct_key>' │ '3'     │ 'blog.ps.category_id' │ 1    │ 100      │ 'Using where'                      │
│ 4       │ 3  │ 'MATERIALIZED' │ '<derived4>'  │ null       │ 'ALL'    │ null                  │ null                  │ null    │ null                  │ 70   │ 100      │ null                               │
│ 5       │ 4  │ 'DERIVED'      │ 'c'           │ null       │ 'range'  │ 'PRIMARY'             │ 'PRIMARY'             │ '2'     │ null                  │ 20   │ 100      │ 'Using where'                      │
│ 6       │ 5  │ 'UNION'        │ 'cte'         │ null       │ 'ALL'    │ null                  │ null                  │ null    │ null                  │ 20   │ 100      │ 'Recursive; Using where'           │
│ 7       │ 5  │ 'UNION'        │ 'c'           │ null       │ 'ref'    │ 'idx_parent_id'       │ 'idx_parent_id'       │ '2'     │ 'cte.id'              │ 2    │ 100      │ 'Using index'                      │
└─────────┴────┴────────────────┴───────────────┴────────────┴──────────┴───────────────────────┴───────────────────────┴─────────┴───────────────────────┴──────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join  (cost=100186 rows=100) (actual time=9.15..9.69 rows=100 loops=1)
    -> Table scan on a  (cost=99616..99620 rows=100) (actual time=9.14..9.15 rows=100 loops=1)
        -> Materialize  (cost=99616..99616 rows=100) (actual time=9.14..9.14 rows=100 loops=1)
            -> Limit/Offset: 100/500 row(s)  (cost=99606 rows=100) (actual time=7.26..9.13 rows=100 loops=1)
                -> Group (no aggregates)  (cost=99606 rows=600) (actual time=0.182..9.1 rows=600 loops=1)
                    -> Nested loop inner join  (cost=99546 rows=600) (actual time=0.174..8.95 rows=983 loops=1)
                        -> Covering index scan on ps using idx_price (reverse)  (cost=1.09 rows=600) (actual time=0.0386..3 rows=9637 loops=1)
                        -> Filter: (ps.category_id = `<subquery3>`.id)  (cost=1421..1421 rows=1) (actual time=483e-6..500e-6 rows=0.102 loops=9637)
                            -> Single-row index lookup on <subquery3> using <auto_distinct_key> (id=ps.category_id)  (cost=1465..1465 rows=1) (actual time=347e-6..358e-6 rows=0.102 loops=9637)
                                -> Materialize with deduplication  (cost=44.3..44.3 rows=70.4) (actual time=0.133..0.133 rows=135 loops=1)
                                    -> Filter: (cte.id is not null)  (cost=33.9..37.2 rows=70.4) (actual time=0.111..0.123 rows=135 loops=1)
                                        -> Table scan on cte  (cost=33.9..37.2 rows=70.4) (actual time=0.111..0.118 rows=135 loops=1)
                                            -> Materialize recursive CTE cte  (cost=33.8..33.8 rows=70.4) (actual time=0.11..0.11 rows=135 loops=1)
                                                -> Filter: (c.id in (1,2,3,4,5,6,7,9,10,11,12,13,14,21,23,24,25,49,220,582))  (cost=12 rows=20) (actual time=0.00542..0.0148 rows=20 loops=1)
                                                    -> Index range scan on c using PRIMARY over (id = 1) OR (id = 2) OR (18 more)  (cost=12 rows=20) (actual time=0.00458..0.0128 rows=20 loops=1)
                                                -> Repeat until convergence
                                                    -> Nested loop inner join  (cost=14.8 rows=50.4) (actual time=0.00237..0.0435 rows=57.5 loops=2)
                                                        -> Filter: (cte.id is not null)  (cost=4.75 rows=20) (actual time=500e-6..0.00408 rows=67.5 loops=2)
                                                            -> Scan new records on cte  (cost=4.75 rows=20) (actual time=312e-6..0.00185 rows=67.5 loops=2)
                                                        -> Covering index lookup on c using idx_parent_id (parent_id=cte.id)  (cost=0.263 rows=2.52) (actual time=418e-6..489e-6 rows=0.852 loops=135)
    -> Single-row index lookup on p using PRIMARY (id=a.product_id)  (cost=0.927 rows=1) (actual time=0.00519..0.00522 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 599    │
│ Innodb_buffer_pool_reads         │ 55     │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 9893   │
│ Handler_read_last                │ 1      │
│ Handler_read_next                │ 115    │
│ Handler_read_prev                │ 9636   │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 372    │
└──────────────────────────────────┴────────┘

선택한 카테고리의 개수가 많고, 대부분의 상품이 해당할수록 성능이 좋습니다.

마지막으로 cte를 제거합니다. 1000개행 정도는 recursive cte로 재귀적으로 검색하는거보다, 전체를 조회해서 서버 코드에서 해결하는게 좋습니다.

SELECT id
     , parent_id
FROM categories

전체 조회해서 id에 맞는 카테고리와 자식 카테고리를 서버에서 가져옵니다. 가져온 카테고리 목록을 통해 조회합니다.

[Query]
    SELECT p.*
    FROM (
             SELECT ps.product_id
             FROM product_search ps
             WHERE ps.category_id IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 49, 220, 582)
             GROUP BY ps.price
                    , ps.product_id
             ORDER BY ps.price DESC
             LIMIT 500, 100
         ) a
         INNER JOIN products p
                    ON p.id = a.product_id

[Result]
100 rows / 15.113ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬───────────────┬─────────────┬─────────┬────────────────┬──────┬──────────┬─────────────────────────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type     │ possible_keys │ key         │ key_len │ ref            │ rows │ filtered │ Extra                                           │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼───────────────┼─────────────┼─────────┼────────────────┼──────┼──────────┼─────────────────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ '<derived2>' │ null       │ 'ALL'    │ null          │ null        │ null    │ null           │ 600  │ 100      │ null                                            │
│ 1       │ 1  │ 'PRIMARY'   │ 'p'          │ null       │ 'eq_ref' │ 'PRIMARY'     │ 'PRIMARY'   │ '4'     │ 'a.product_id' │ 1    │ 100      │ null                                            │
│ 2       │ 2  │ 'DERIVED'   │ 'ps'         │ null       │ 'index'  │ 'idx_price'   │ 'idx_price' │ '8'     │ null           │ 600  │ 50       │ 'Using where; Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴───────────────┴─────────────┴─────────┴────────────────┴──────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join  (cost=583 rows=0) (actual time=5.93..6.35 rows=100 loops=1)
    -> Table scan on a  (cost=63.6..63.6 rows=0) (actual time=5.91..5.92 rows=100 loops=1)
        -> Materialize  (cost=61.1..61.1 rows=0) (actual time=5.91..5.91 rows=100 loops=1)
            -> Limit/Offset: 100/500 row(s)  (cost=61.1 rows=0) (actual time=4.86..5.9 rows=100 loops=1)
                -> Group (no aggregates)  (cost=61.1 rows=300) (actual time=0.0545..5.89 rows=600 loops=1)
                    -> Filter: (ps.category_id in (1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24,25,49,220,582))  (cost=31.1 rows=300) (actual time=0.0495..5.85 rows=663 loops=1)
                        -> Covering index scan on ps using idx_price (reverse)  (cost=31.1 rows=600) (actual time=0.0463..4.82 rows=32613 loops=1)
    -> Single-row index lookup on p using PRIMARY (id=a.product_id)  (cost=0.866 rows=1) (actual time=0.00419..0.00421 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 558    │
│ Innodb_buffer_pool_reads         │ 82     │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 101    │
│ Handler_read_last                │ 1      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 32612  │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 101    │
└──────────────────────────────────┴────────┘

상품 검색이 완료되었습니다. 검색용 테이블이 추가되어 상품 테이블이 변경될때마다 수정해줘야 합니다. 화면에 보여지는 금액과 같은 데이터는 원본 상품 테이블을 참조하기 때문에 큐나 이벤트 등을 사용해 비동기적으로 수정해주어도 됩니다.

상품의 개수나 카테고리의 개수, 매핑된 개수에 따라 쿼리의 방법이 달라집니다. 일반적으로 현재 데이터에서 가장 최악의 성능을 피하는 방법을 택해야 합니다.

인덱스를 특정 부분만 읽지만 테이블에 조금 더 많은 수직탐색을 할 것인지, 인덱스를 정렬된 채로 읽어서 원하는 항목을 찾아 테이블에 수직탐색을 줄일지, 아니면 데이터의 성격과 기능에 따라 다른 방법이 필요할 수 있습니다.

앞으로 사용할 예제 테이블입니다.

CREATE TABLE users
(
    id         INT UNSIGNED PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '유저'
;
CREATE TABLE products
(
    id          INT UNSIGNED PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL,
    price       INT UNSIGNED NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '상품'
;

CREATE TABLE reviews
(
    id         INT UNSIGNED PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    contents   VARCHAR(255) NOT NULL,
    score      TINYINT UNSIGNED COMMENT '점수 (1, 2, 3, 4, 5)',
    deleted_at DATETIME     NULL COMMENT '삭제된 경우에만 입력',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '상품 리뷰'
;

CREATE TABLE review_images
(
    id         INT UNSIGNED PRIMARY KEY,
    review_id  INT UNSIGNED     NOT NULL,
    sequence   TINYINT UNSIGNED NOT NULL COMMENT '표시 순서',
    file_name  VARCHAR(255)     NOT NULL,
    file_path  VARCHAR(255)     NOT NULL,
    file_size  INT UNSIGNED     NOT NULL,
    created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT '리뷰 첨부 이미지'
;

유저와 상품에 10만건을 넣었습니다. 리뷰와 리뷰이미지에 각 1억건의 더미데이터를 입력한 상태입니다.

상품 상세페이지에 보여질 리뷰 목록을 페이징처리하는걸 목표로 진행합니다.

우선 product_id로 조회하므로 인덱스를 추가합니다. == product_id+id로 구성된 BTree를 생성합니다

ALTER TABLE reviews
    ADD INDEX idx_product_id (product_id);

첫 번째 리뷰 페이징 쿼리

[Query]
    SELECT r.*
         , u.name AS 'user.name'
    FROM reviews r
         LEFT OUTER JOIN users u
                         ON u.id = r.user_id
    WHERE r.product_id = 100
    ORDER BY r.id DESC
    LIMIT 100

[Result]
100 rows / 18.9575ms

[Explain]
┌─────────┬────┬─────────────┬───────┬────────────┬──────────┬─────────────────┬─────────────────┬─────────┬──────────────────┬───────┬──────────┬───────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type     │ possible_keys   │ key             │ key_len │ ref              │ rows  │ filtered │ Extra                 │
├─────────┼────┼─────────────┼───────┼────────────┼──────────┼─────────────────┼─────────────────┼─────────┼──────────────────┼───────┼──────────┼───────────────────────┤
│ 0       │ 1  │ 'SIMPLE'    │ 'r'   │ null       │ 'ref'    │ 'idx_product_id'│ 'idx_product_id'│ '4'     │ 'const'          │ 42980 │ 100      │ 'Backward index scan' │
│ 1       │ 1  │ 'SIMPLE'    │ 'u'   │ null       │ 'eq_ref' │ 'PRIMARY'       │ 'PRIMARY'       │ '4'     │ 'blog.r.user_id' │ 1     │ 100      │ null                  │
└─────────┴────┴─────────────┴───────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴──────────────────┴───────┴──────────┴───────────────────────┘
-> Limit: 100 row(s)  (cost=61954 rows=100) (actual time=0.421..0.682 rows=100 loops=1)
    -> Nested loop left join  (cost=61954 rows=42980) (actual time=0.42..0.677 rows=100 loops=1)
        -> Index lookup on r using idx_product_id (product_id=100) (reverse)  (cost=46911 rows=42980) (actual time=0.411..0.428 rows=100 loops=1)
        -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.25 rows=1) (actual time=0.00231..0.00234 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 723    │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 101    │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 99     │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 0      │
└──────────────────────────────────┴────────┘

Handler_read_key는 수직탐색 횟수, Handler_read_next/Handler_read_prev는 인덱스 수평탐색(앞으로/뒤로) 횟수를 나타냅니다

시간도 얼마 안걸리고 상태값도 문제가 없어 보입니다. 20번째 페이지를 조회해봅니다.

20번째 페이지 조회

[Query]
    SELECT r.*
         , u.name AS 'user.name'
    FROM reviews r
         LEFT OUTER JOIN users u
                         ON u.id = r.user_id
    WHERE r.product_id = 9000
    ORDER BY r.id DESC
    LIMIT 1900, 100

[Result]
100 rows / 184.15375ms

[Explain]
┌─────────┬────┬─────────────┬───────┬────────────┬──────────┬─────────────────┬─────────────────┬─────────┬──────────────────┬────────┬──────────┬───────────────────────┐
│ (index) │ id │ select_type │ table │ partitions │ type     │ possible_keys   │ key             │ key_len │ ref              │ rows   │ filtered │ Extra                 │
├─────────┼────┼─────────────┼───────┼────────────┼──────────┼─────────────────┼─────────────────┼─────────┼──────────────────┼────────┼──────────┼───────────────────────┤
│ 0       │ 1  │ 'SIMPLE'    │ 'r'   │ null       │ 'ref'    │ 'idx_product_id'│ 'idx_product_id'│ '4'     │ 'const'          │ 378200 │ 100      │ 'Backward index scan' │
│ 1       │ 1  │ 'SIMPLE'    │ 'u'   │ null       │ 'eq_ref' │ 'PRIMARY'       │ 'PRIMARY'       │ '4'     │ 'blog.r.user_id' │ 1      │ 100      │ null                  │
└─────────┴────┴─────────────┴───────┴────────────┴──────────┴─────────────────┴─────────────────┴─────────┴──────────────────┴────────┴──────────┴───────────────────────┘
-> Limit/Offset: 100/1900 row(s)  (cost=545764 rows=100) (actual time=4.36..4.44 rows=100 loops=1)
    -> Nested loop left join  (cost=545764 rows=378200) (actual time=0.191..4.4 rows=2000 loops=1)
        -> Index lookup on r using idx_product_id (product_id=9000) (reverse)  (cost=413394 rows=378200) (actual time=0.184..2.75 rows=2000 loops=1)
        -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.25 rows=1) (actual time=727e-6..743e-6 rows=1 loops=2000)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 13569  │
│ Innodb_buffer_pool_reads         │ 1486   │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 1995   │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 1999   │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 0      │
└──────────────────────────────────┴────────┘

실행계획도 괜찮고 시간도 얼마 안걸려서 괜찮아보이지만 status 값이 많이 증가했습니다. 약 2000번의 수직/수평탐색이 발생했습니다.

BTree에서 어떻게 진행됬을지 생각해보니 idx_product_id 인덱스 트리에서 2000건의 리뷰id를 찾아서 리뷰테이블에 2000번 수직탐색이 진행됬고, 조인된 users 테이블에도 2000번의 수직탐색이 일어났습니다.

필요한 100건 외에 1900건에 대해서도 리뷰테이블과 유저테이블에 각각 1900번의 불필요한 수직탐색이 추가됬습니다.

쿼리 최적화의 핵심은 논리적io를 줄이는 것입니다.

필요한 수직탐색만 발생하도록 쿼리를 수정해봅니다.

서브쿼리로 수정한 쿼리

[Query]
    SELECT r.*
         , u.name AS 'user.name'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 400
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id


[Result]
100 rows / 71.8225ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬─────────────────┬─────────┬──────────────────┬───────┬──────────┬────────────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type     │ possible_keys        │ key             │ key_len │ ref              │ rows  │ filtered │ Extra                              │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼─────────────────┼─────────┼──────────────────┼───────┼──────────┼────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ '<derived2>' │ null       │ 'ALL'    │ null                 │ null            │ null    │ null             │ 2000  │ 100      │ null                               │
│ 1       │ 1  │ 'PRIMARY'   │ 'r'          │ null       │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY'       │ '4'     │ 'a.id'           │ 1     │ 100      │ null                               │
│ 2       │ 1  │ 'PRIMARY'   │ 'u'          │ null       │ 'eq_ref' │ 'PRIMARY'            │ 'PRIMARY'       │ '4'     │ 'blog.r.user_id' │ 1     │ 100      │ null                               │
│ 3       │ 2  │ 'DERIVED'   │ 'r'          │ null       │ 'ref'    │ 'idx_product_id'     │ 'idx_product_id'│ '4'     │ 'const'          │ 42112 │ 100      │ 'Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴─────────────────┴─────────┴──────────────────┴───────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join  (cost=6770 rows=100) (actual time=2.1..3.1 rows=100 loops=1)
    -> Nested loop inner join  (cost=6260 rows=100) (actual time=2.09..2.64 rows=100 loops=1)
        -> Table scan on a  (cost=4263..4267 rows=100) (actual time=2.06..2.08 rows=100 loops=1)
            -> Materialize  (cost=4263..4263 rows=100) (actual time=2.06..2.06 rows=100 loops=1)
                -> Limit/Offset: 100/1900 row(s)  (cost=4253 rows=100) (actual time=2..2.03 rows=100 loops=1)
                    -> Covering index lookup on r using idx_product_id (product_id=400) (reverse)  (cost=4253 rows=42112) (actual time=1.64..1.91 rows=2000 loops=1)
        -> Single-row index lookup on r using PRIMARY (id=a.id)  (cost=0.991 rows=1) (actual time=0.0053..0.00534 rows=1 loops=100)
    -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.25 rows=1) (actual time=0.00425..0.00429 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 528    │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 201    │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 1999   │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 101    │
└──────────────────────────────────┴────────┘

Handler_read_key 상태값이 많이 줄었는데 확인해봅니다.

서브쿼리에서 idx_product_id 인덱스 트리를 탐색하여 2000개를 읽습니다. 인덱스에는 자동으로 pk가 들어가기 때문에 select에 필요한 review id를 알기 위해 테이블 트리를 탐색할 필요가 없습니다. 이렇게 인덱스에서만 모든 처리를 끝내는걸 커버링인덱스 라고 하며 실행계획엔 using index가 표시됩니다.

서브쿼리가 반환한 100개의 리뷰의 id로 각각 리뷰테이블에서 수직탐색을 하고, 조인된 유저테이블에서도 찾은 리뷰테이블의 user_id로 수직탐색을 하면 쿼리가 종료됩니다.

총 201회의 수직탐색(Handler_read_key)이 일어난 것을 볼 수 있는데 reviews와 users 테이블에 각 100번씩 200회와 서브쿼리에서 product_id 값을 인덱스에서 찾은 1회 입니다.

수평탐색(Handler_read_prev)은 1999회 일어났는데 서브쿼리 내에서 limit이 2000(1900+100)이라 최초 탐색한것부터 1999번 이동해서 2000개를 찾은 것 입니다.

Handler_read_rnd_next은 서브쿼리의 결과가 100건이라 없는거 확인할때까지 총 101번입니다.

수평탐색이 저번 쿼리와 동일하지만 크게 걱정할 건 없습니다. mysql이 데이터를 읽을 때 하나의 페이지(16kb) 단위로 읽기 때문에, 수직탐색은 필요한 곳을 찾아가느라 1회에 몇개의 페이지를 읽지만, 수평탐색은 하나의 페이지에서 수십~수백개의 행을 읽습니다.

위 쿼리는 더이상 문제가 없는 것 같습니다.

정렬

위 쿼리는 ORDER BY reviews.id DESC 로 정렬하고 있습니다. 그런데 실행계획을 봐도 별도의 정렬을 하고있지 않고 필요한 2000개만 읽고 쿼리를 종료하고 있습니다. 왜 product_id가 일치하는 모든 행을 읽어서 id로 정렬한 후 반환하지 않는지 BTree에서 확인해봅니다.

현재 리뷰 테이블의 인덱스를 product_id로 생성한 상태입니다. 트리의 정렬이 product_id에 따라 정렬됩니다.

인덱스 트리의 리프노드는 가장 앞에 product_id 1이 있고 마지막엔 가장 끝번호인 100000이 있을 것 입니다. 그런데 mysql은 인덱스에 자동으로 pk를 추가한다고 했습니다

모든 리프노드의 각 행에 product_id에 id가 추가되어 있고 product_id가 같으면 id로 정렬되어 있습니다.

# idx_product_id 인덱스 리프노드 예시
┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000       │ 9000       │ 9000       │ 9001       │ 9001       │ 9001       │
│ id         │ 99812310   │ 99900520   │ 99901200   │ 155        │ 212235     │ 1021235    │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘

즉 product_id가 9000인 가장 앞쪽 값을 검색하고 뒤로 수평탐색하면 id로 오름차순 정렬, product_id가 9000인 가장 뒤쪽 값을 수직탐색해서 앞쪽으로 읽으면 id로 내림차순으로 정렬된 결과를 얻을 수 있습니다. 위 서브쿼리 실행계획에서 'Backward index scan;'가 있는데 원하는 결과가 내림차순이기 때문에 mysql이 뒤에서부터 앞으로 인덱스를 읽은 것 입니다.

별점 필터 추가

리뷰 목록 중 별점(1~5점)을 통한 필터링 기능이 추가됬습니다 이제 원하는 점수의 리뷰만 선택하여 보여줄 수 있어야 합니다. 이전 쿼리에서 where문만 수정하면 어떻게 될지 확인해봅시다.

[Query]
    SELECT r.*
         , u.name AS 'user.name'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 1000
               and r.score = 3        # 추가된 where 조건
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id

[Result]
100 rows / 817.189ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬─────────────────┬─────────┬──────────────────┬────────┬──────────┬────────────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type     │ possible_keys        │ key             │ key_len │ ref              │ rows   │ filtered │ Extra                              │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼─────────────────┼─────────┼──────────────────┼────────┼──────────┼────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ '<derived2>' │ null       │ 'ALL'    │ null                 │ null            │ null    │ null             │ 2000   │ 100      │ null                               │
│ 1       │ 1  │ 'PRIMARY'   │ 'r'          │ null       │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY'       │ '4'     │ 'a.id'           │ 1      │ 100      │ null                               │
│ 2       │ 1  │ 'PRIMARY'   │ 'u'          │ null       │ 'eq_ref' │ 'PRIMARY'            │ 'PRIMARY'       │ '4'     │ 'blog.r.user_id' │ 1      │ 100      │ null                               │
│ 3       │ 2  │ 'DERIVED'   │ 'r'          │ null       │ 'ref'    │ 'idx_product_id'     │ 'idx_product_id'│ '4'     │ 'const'          │ 398160 │ 10       │ 'Using where; Backward index scan' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴─────────────────┴─────────┴──────────────────┴────────┴──────────┴────────────────────────────────────┘
-> Nested loop inner join  (cost=399916 rows=100) (actual time=43.8..44.3 rows=100 loops=1)
    -> Nested loop inner join  (cost=398914 rows=100) (actual time=43.8..43.9 rows=100 loops=1)
        -> Table scan on a  (cost=396927..396930 rows=100) (actual time=43.8..43.8 rows=100 loops=1)
            -> Materialize  (cost=396927..396927 rows=100) (actual time=43.8..43.8 rows=100 loops=1)
                -> Limit/Offset: 100/1900 row(s)  (cost=396917 rows=100) (actual time=41.4..43.8 rows=100 loops=1)
                    -> Filter: (r.score = 3)  (cost=396917 rows=39816) (actual time=0.178..43.8 rows=2000 loops=1)
                        -> Index lookup on r using idx_product_id (product_id=1000) (reverse)  (cost=396917 rows=398160) (actual time=0.178..43.5 rows=10220 loops=1)
        -> Single-row index lookup on r using PRIMARY (id=a.id)  (cost=0.987 rows=1) (actual time=969e-6..981e-6 rows=1 loops=100)
    -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.496 rows=1) (actual time=0.00306..0.00308 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 48500  │
│ Innodb_buffer_pool_reads         │ 6832   │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 201    │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 10219  │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 101    │
└──────────────────────────────────┴────────┘

수평탐색이 많이 증가했습니다. 중요한 부분은 서브쿼리의 using index;가 없어졌고 커버링 인덱스로 해결하지 못했습니다.

인덱스로 수평탐색한 pk로 테이블을 수직탐색해도 Handler_read_prev가 1만 증가합니다. 즉 실제로는 리뷰테이블에 대한 수직탐색도 많이 증가했다고 볼 수 있습니다.

기존에 커버링 인덱스로 해결되던 서브쿼리가 score 필터로 인해 테이블까지 탐색해야 처리할 수 있도록 바뀌었기 때문입니다.

잘못된 인덱스 변경 (product_id, score)

커버링 인덱스를 위해 idx_product_id 인덱스를 (product_id, score)로 변경한다면 어떻게 될까요

# 인덱스를 product_id, score 로 구성한 경우 리프노드 예시
┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000       │ 9000       │ 9000       │ 9000       │ 9000       │ 9000       │
│ score      │ 1          │ 1          │ 1          │ 2          │ 2          │ 2          │
│ id         │ 22         │ 999        │ 1232132    │ 222        │ 55555      │ 1123555    │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘

첫 번째 문제는 인덱스 트리에서 같은 product_id에 대해 id보다 score가 우선으로 정렬되게 됩니다. 점수 필터링이 없는 기존 쿼리에서 id로 정렬하기 위해 찾는 product_id 의 모든 행을 인덱스에서 다 읽고 정렬해야 합니다.

두 번째 문제는 score를 여러개 선택한 경우에도 발생합니다. score가 1과 2인것을 검색하려면 1과 2의 검색결과를 합친 후 id로 정렬해서 출력해야 합니다.

커버링 인덱스와 정렬을 고려한 인덱스 변경

이 상황에서 어떻게 해야 커버링 인덱스는 유지하면서 정렬 문제까지 해결할 수 있을지 보겠습니다. mysql에서 pk는 모든 인덱스에 자동으로 추가됩니다. 하지만 이미 pk가 있는 인덱스는 뒤에 추가로 생성되지 않는 점을 알고 있습니다.

product_id가 동일한 행들에 대해 id로 정렬되어있고, score도 갖고있는 인덱스를 생성해야 합니다. idx_product_id 인덱스를 변경합니다.

# 기존 인덱스 제거 후 다시 생성
ALTER TABLE reviews
    DROP INDEX idx_product_id,
    ADD INDEX idx_product_id (product_id, id, score)

인덱스를 (product_id, id, score)로 id를 중간에 추가했습니다.

┌────────────┬────────────┬────────────┬────────────┬────────────┬────────────┬────────────┐
│ product_id │ 9000       │ 9000       │ 9000       │ 9000       │ 9000       │ 9000       │
│ id         │ 22         │ 222        │ 999        │ 55555      │ 1232132    │ 1123555    │
│ score      │ 1          │ 2          │ 1          │ 2          │ 1          │ 2          │
└────────────┴────────────┴────────────┴────────────┴────────────┴────────────┴────────────┘

이제 product_id가 같은 행들에 대해 id로 정렬되어있고, score도 갖고있는 인덱스가 되었습니다. 다시 위 쿼리를 실행해봅니다.

[Query]
    SELECT r.*
         , u.name AS 'user.name'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 2000
               and r.score = 3        
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id


[Result]
100 rows / 6.8045ms

[Explain]
┌─────────┬────┬─────────────┬──────────────┬────────────┬──────────┬──────────────────────┬──────────────────┬─────────┬──────────────────┬────────┬──────────┬─────────────────────────────────────────────────┐
│ (index) │ id │ select_type │ table        │ partitions │ type     │ possible_keys        │ key              │ key_len │ ref              │ rows   │ filtered │ Extra                                           │
├─────────┼────┼─────────────┼──────────────┼────────────┼──────────┼──────────────────────┼──────────────────┼─────────┼──────────────────┼────────┼──────────┼─────────────────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'   │ '<derived2>' │ null       │ 'ALL'    │ null                 │ null             │ null    │ null             │ 2000   │ 100      │ null                                            │
│ 1       │ 1  │ 'PRIMARY'   │ 'r'          │ null       │ 'eq_ref' │ 'PRIMARY,idx_user_id'│ 'PRIMARY'        │ '4'     │ 'a.id'           │ 1      │ 100      │ null                                            │
│ 2       │ 1  │ 'PRIMARY'   │ 'u'          │ null       │ 'eq_ref' │ 'PRIMARY'            │ 'PRIMARY'        │ '4'     │ 'blog.r.user_id' │ 1      │ 100      │ null                                            │
│ 3       │ 2  │ 'DERIVED'   │ 'r'          │ null       │ 'ref'    │ 'idx_product_id'     │ 'idx_product_id' │ '4'     │ 'const'          │ 426582 │ 10       │ 'Using where; Backward index scan; Using index' │
└─────────┴────┴─────────────┴──────────────┴────────────┴──────────┴──────────────────────┴──────────────────┴─────────┴──────────────────┴────────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join  (cost=8644 rows=100) (actual time=4.88..5.74 rows=100 loops=1)
    -> Nested loop inner join  (cost=7018 rows=100) (actual time=4.87..5.35 rows=100 loops=1)
        -> Table scan on a  (cost=5005..5009 rows=100) (actual time=4.77..4.78 rows=100 loops=1)
            -> Materialize  (cost=5005..5005 rows=100) (actual time=4.76..4.76 rows=100 loops=1)
                -> Limit/Offset: 100/1900 row(s)  (cost=4995 rows=100) (actual time=4.53..4.73 rows=100 loops=1)
                    -> Filter: (r.score = 3)  (cost=4995 rows=42658) (actual time=0.111..4.66 rows=2000 loops=1)
                        -> Covering index lookup on r using idx_product_id (product_id=2000) (reverse)  (cost=4995 rows=426582) (actual time=0.105..4.01 rows=9960 loops=1)
        -> Single-row index lookup on r using PRIMARY (id=a.id)  (cost=1 rows=1) (actual time=0.00501..0.00504 rows=1 loops=100)
    -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.808 rows=1) (actual time=0.00369..0.00372 rows=1 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 633    │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 201    │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 9959   │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 101    │
└──────────────────────────────────┴────────┘

status 값은 큰 차이가 나지 않고 using index;가 다시 생겼습니다. 서브쿼리에서 1회 수직탐색 후, 원하는 id를 찾을때까지 테이블 트리에 검색하지 않고 인덱스에서만 해결할 수 있습니다. 2000개를 찾기위해 수평탐색으로 약 1만개를 찾고있으나, 위에서 말햇듯이 수직탐색을 줄이는게 중요합니다.

가상컬럼 활용

위 문제와 비슷하게, 삭제된 리뷰를 결과에서 제거하려면 어떻게 해야될까요? 인덱스를 (product_id, id, score, deleted_at)로 수정하고 where문에 deleted_at is not null 조건을 추가하면 됩니다. 하지만 deleted_at은 datetime 타입으로 5byte를 차지하며, 현재 상황에서 삭제한 시간이 인덱스에 필요하지 않습니다.

mysql8의 가상컬럼 기능을 사용해 인덱스 크기를 줄여봅니다.

ALTER TABLE reviews
    ADD COLUMN is_deleted BOOLEAN AS (deleted_at IS NOT NULL) VIRTUAL AFTER deleted_at,
    DROP INDEX idx_product_id,
    ADD INDEX idx_product_id (product_id, id, score, is_deleted)
;

is_deleted라는 가상컬럼을 추가했습니다.
insert나 update에 해당 컬럼을 작성하지 않고 컬럼을 사용할때마다 자동으로 값이 계산되어 나옵니다. 인덱스 트리에는 계산된 값이 저장됩니다. boolean(tinyint)이기 때문에 1byte를 차지하게 됩니다. 실제 테이블트리에는 사용할때마다 계산되기 때문에 용량을 차지하지 않습니다.(virtual 대신 stored로 설정하면 실제 저장까지 됩니다.) 이제 where 문에서 deleted_at is not null 대신 is_deleted = FALSE 로 수정하면 원하는 결과를 얻을 수 있습니다.

리뷰 이미지 추가

마지막으로 첨부된 리뷰의 이미지도 한번에 가져오도록 해보겠습니다.

우선 인덱스를 만듭니다.

ALTER TABLE review_images
    ADD INDEX idx_review_id (review_id, sequence);

이미지는 review_id로 검색할 것이고, 검색 결과가 sequence로 정렬되도록 만듭니다.

잘못된 1:N 관계 조인

    SELECT r.*
         , u.id         AS 'user.id'
         , u.name       AS 'user.name'
         , ri.id        AS 'image.id'         
         , ri.file_name AS 'image.file_name'
         , ri.file_path AS 'image.file_path'
         , ri.file_size AS 'image.file_size'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 2000
               AND r.score = 3
               AND r.is_deleted = FALSE
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id
         LEFT OUTER JOIN review_images ri
                         ON ri.review_id = r.id

리뷰와 리뷰이미지는 1:n 관계입니다. 즉 리뷰 한건에 2개 이상의 이미지가 있을 수 있습니다. 이런 경우 일반 join으로 해결하면 하나의 리뷰가 여러번 출력될 수 있고, 이런 테이블이 여러개 있다면 n * m * ... 으로 출력 크기가 기하급수적으로 증가합니다.

하나의 리뷰 행에 여러개의 이미지를 한번에 보여줘야 합니다. mysql의 json 기능을 사용해봅시다.

select 서브쿼리로 변경

[Query]
    SELECT r.*
         , u.id   AS 'user.id'
         , u.name AS 'user.name'
         , IFNULL(
            (
                SELECT JSON_ARRAYAGG(JSON_OBJECT(
                        'id', ri.id,
                        'file_name', ri.file_name,
                        'file_size', ri.file_size,
                        'file_path', ri.file_path
                                     )) AS images
                FROM review_images ri
                WHERE ri.review_id = r.id
            ),
            JSON_ARRAY()
           )      AS 'images'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 44000
               AND r.score = 3
               AND r.is_deleted = FALSE
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id


[Result]
100 rows / 46.54575ms

[Explain]
┌─────────┬────┬──────────────────────┬──────────────┬────────────┬──────────┬──────────────────────┬──────────────────┬─────────┬──────────────────┬────────┬──────────┬─────────────────────────────────────────────────┐
│ (index) │ id │ select_type          │ table        │ partitions │ type     │ possible_keys        │ key              │ key_len │ ref              │ rows   │ filtered │ Extra                                           │
├─────────┼────┼──────────────────────┼──────────────┼────────────┼──────────┼──────────────────────┼──────────────────┼─────────┼──────────────────┼────────┼──────────┼─────────────────────────────────────────────────┤
│ 0       │ 1  │ 'PRIMARY'            │ '<derived3>' │ null       │ 'ALL'    │ null                 │ null             │ null    │ null             │ 2000   │ 100      │ null                                            │
│ 1       │ 1  │ 'PRIMARY'            │ 'r'          │ null       │ 'eq_ref' │ 'PRIMARY,ix_user_id' │ 'PRIMARY'        │ '4'     │ 'a.id'           │ 1      │ 100      │ 'Using where'                                   │
│ 2       │ 1  │ 'PRIMARY'            │ 'u'          │ null       │ 'eq_ref' │ 'PRIMARY'            │ 'PRIMARY'        │ '4'     │ 'blog.r.user_id' │ 1      │ 100      │ null                                            │
│ 3       │ 3  │ 'DERIVED'            │ 'r'          │ null       │ 'ref'    │ 'idx_product_id'     │ 'idx_product_id' │ '4'     │ 'const'          │ 418080 │ 10       │ 'Using where; Backward index scan; Using index' │
│ 4       │ 2  │ 'DEPENDENT SUBQUERY' │ 'ri'         │ null       │ 'ref'    │ 'idx_review_id'      │ 'idx_review_id'  │ '4'     │ 'blog.r.id'      │ 4      │ 100      │ null                                            │
└─────────┴────┴──────────────────────┴──────────────┴────────────┴──────────┴──────────────────────┴──────────────────┴─────────┴──────────────────┴────────┴──────────┴─────────────────────────────────────────────────┘
-> Nested loop inner join  (cost=8646 rows=100) (actual time=2.61..2.97 rows=100 loops=1)
    -> Nested loop inner join  (cost=7020 rows=100) (actual time=2.61..2.82 rows=100 loops=1)
        -> Table scan on a  (cost=5007..5011 rows=100) (actual time=2.6..2.6 rows=100 loops=1)
            -> Materialize  (cost=5007..5007 rows=100) (actual time=2.6..2.6 rows=100 loops=1)
                -> Limit/Offset: 100/1900 row(s)  (cost=4997 rows=100) (actual time=2.45..2.58 rows=100 loops=1)
                    -> Filter: (r.score = 3)  (cost=4997 rows=41808) (actual time=0.0474..2.53 rows=2000 loops=1)
                        -> Covering index lookup on r using idx_product_id (product_id=2000) (reverse)  (cost=4997 rows=418080) (actual time=0.0448..2.15 rows=9960 loops=1)
        -> Filter: (r.user_id is not null)  (cost=1 rows=1) (actual time=0.00193..0.00199 rows=1 loops=100)
            -> Single-row index lookup on r using PRIMARY (id=a.id)  (cost=1 rows=1) (actual time=0.00183..0.00185 rows=1 loops=100)
    -> Single-row index lookup on u using PRIMARY (id=r.user_id)  (cost=0.808 rows=1) (actual time=0.00132..0.00134 rows=1 loops=100)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: json_arrayagg(json_object('id',ri.id,'file_name',ri.file_name,'file_size',ri.file_size,'file_path',ri.file_path))  (cost=5.1 rows=1) (actual time=0.00159..0.00161 rows=1 loops=100)
        -> Index lookup on ri using idx_review_id (review_id=r.id)  (cost=4.68 rows=4.25) (actual time=0.00144..0.00144 rows=0 loops=100)


[Status]
┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 1139   │
│ Innodb_buffer_pool_reads         │ 214    │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 301    │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 9959   │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 101    │
└──────────────────────────────────┴────────┘

select 절의 서브쿼리에서 review_images 테이블을 조회합니다. (lateral join는 데이터가 커질수록 성능이 안좋아져서 사용하지 않았습니다.) json_object 함수로 여러개의 컬럼을 하나의 object로 담고, json_arrayagg 집계함수로 여러개의 object를 하나의 array에 담았습니다. 이미지가 없는 경우 빈 배열을 담기위해 ifnull 함수에 json_array()를 추가합니다.

select는 from절 이후에 동작하기 때문에 원하는 100건의 리뷰에 대해서만 서브쿼리가 실행됩니다. 서브쿼리는 매번 수직탐색 1회와, 수평탐색이 찾은개수 만큼 실행됩니다. (2개인 경우 3번째까지 확인해야되서 2번 필요)

정리

추가/수정한 인덱스와 쿼리 입니다.

# 리뷰 테이블의 가상컬럼 is_deleted
ALTER TABLE reviews
    ADD COLUMN is_deleted BOOLEAN AS (deleted_at IS NOT NULL) VIRTUAL AFTER deleted_at;

# 리뷰 테이블의 인덱스
CREATE INDEX idx_product_id
    ON reviews (product_id, id, score, is_deleted);

# 리뷰 이미지 테이블의 인덱스
CREATE INDEX idx_review_id
    ON review_images (review_id, sequence);

# 페이징 쿼리
    SELECT r.*
         , u.id   AS 'user.id'
         , u.name AS 'user.name'
         , IFNULL(
            (
                SELECT JSON_ARRAYAGG(JSON_OBJECT(
                        'id', ri.id,
                        'file_name', ri.file_name,
                        'file_size', ri.file_size,
                        'file_path', ri.file_path
                                     )) AS images
                FROM review_images ri
                WHERE ri.review_id = r.id
            ),
            JSON_ARRAY()
           )      AS 'images'
    FROM (
             SELECT r.id
             FROM reviews r
             WHERE r.product_id = 44000
               AND r.score = 3
               AND r.is_deleted = FALSE
             ORDER BY r.id DESC
             LIMIT 1900, 100
         ) a
         INNER JOIN reviews r
                    ON r.id = a.id
         INNER JOIN users u
                    ON u.id = r.user_id

같은 실행계획이라도 실제 동작이 다른 경우가 많습니다. 실제 BTree에서 어떤식으로 탐색이 되는지 이해하는게 중요합니다.

정렬은 대부분 정렬하는데 걸리는 시간보다 정렬하기위해 필요한 데이터를 모두 조회하는 게 성능상 문제가 됩니다. 대부분의 페이징 조회에는 정렬에 인덱스를 사용할 수 있도록 만드는게 중요합니다.

저번글에서 수직탐색과 수평탐색을 알아보았습니다.

수직탐색과 수평탐색 모두 데이터를 읽는 과정입니다. 데이터는 모두 디스크에 있고, 일부는 메모리에도 있습니다.

한번 읽은 데이터는 메모리에 올라가며, 메모리가 가득차면 우선순위에 따라 다시 메모리에서 제거됩니다. mysql에서는 버퍼풀(innodb buffer pool) 이라는 할당된 메모리 공간에서 이뤄집니다. 트리의 수평/수직탐색에서 한번 탐색한 노드는 노드 단위로 버퍼풀로 이동합니다.

하나의 노드는 기본 16KB이고 설정에서 변경이 가능합니다(aws aurora는 변경불가능)

3층 구조의 트리에서 수직탐색하여 한 건의 데이터를 읽더라도
루트노드-중간노드-리프노드의 총 3개의 노드를 탐색하여 총 48KB를 읽게 됩니다.

논리적IO는 메모리를 포함해서 읽은 페이지(=노드)의 횟수이고, 물리적IO는 메모리에 없어서 실제로 디스크에서 읽은 횟수입니다.

3개의 노드를 읽어야 하는데 이미 메모리에 2개가 있어서 디스크에서는 1개만 읽었다면 논리적IO는 3이며 물리적IO는 1이 됩니다.

쿼리의 최적화는 기본적으로 논리적 IO를 최소화하는걸 목표로 합니다.

페이지 16KB

16KB는 몇 개 정도의 데이터가 들어갈지 가늠해보겠습니다. int타입의 컬럼 하나를 갖는 인덱스를 만든 경우입니다. int타입의 컬럼 사이즈는 4byte를 차지합니다. 인덱스엔 자동으로 pk가 추가된다고 했으니 int타입의 pk라고 가정하면 4byte를 차지합니다. 마지막으로 자식노드의 주소를 저장해야 하므로 약 12byte가 들어갑니다. = 하나의 행이 약 20byte를 차지합니다. 16kb = 16 * 1024 = 16,384 byte 이므로 하나의 페이지에 약 800개가 들어갈 수 있습니다.

인덱스 컬럼과 pk의 타입을 uuid로 변경해보겠습니다.(binary) 인덱스컬럼 16byte + pk 16byte + 자식노드의 주소 12byte = 44byte 16384 / 44 = 372 개 입니다.

실제로는 모든 페이지를 가득 채우지 않으니 더 적게 들어간다고 볼 수 있습니다. 인덱스가 작을수록 하나의 페이지에 더 많은 개수의 데이터가 들어가고, 원하는 개수를 검색하기 논리적/물리적IO가 더 작아지게 됩니다.

참고

물리적/논리적 IO 횟수

mysql의 status 값을 확인해서 물리적io와 논리적io를 추적할 수 있습니다.

# 논리적 IO
show status like 'Innodb_buffer_pool_reads';
# 물리적 IO
show status like 'Innodb_buffer_pool_read_requests';

쿼리 실행 전후의 차이를 계산하면 해당 쿼리에서 논리적io와 물리적io가 몇 회 발생했는지 알 수 있습니다.

Tree의 노드 개수

mysql 쿼리로 특정 테이블/인덱스 트리의 페이지 개수를 알 수 있습니다. (권한이 없을 수 있습니다.)

SELECT *
FROM mysql.innodb_index_stats s
WHERE table_name = '{tableName}'

하나의 테이블/인덱스별로 여러개 나오는데 stat_name=n_leaf_pages 에 해당하는 stat_value가 리프 페이지의 개수, stat_name=size 에 해당하는 stat_value가 총 페이지의 개수 입니다.

쿼리 성능을 최적화하기 위해서는 B+Tree 자료구조를 이해하는 것이 시작입니다.
(a, b, c) 컬럼으로 구성된 인덱스가 있을 때 a, b 정렬은 인덱스를 타고 a, c 정렬은 못타고...
이런식으로 외우는건 큰 도움이 되지 않습니다.

mysql(innodb)의 테이블과 인덱스 데이터는 모두 트리 구조로 저장됩니다.
하나의 테이블은 하나의 트리로, 하나의 인덱스도 하나의 트리로 저장됩니다.
(파티셔닝 된 테이블은 각 파티션별로 나눠집니다.)

어떤 테이블에 (a, b, c) 컬럼으로 구성된 인덱스 idx1이 있습니다.
아래는 idx1 인덱스의 트리 구조 예시입니다.

빨간 사각형이 하나의 노드 입니다.
하나의 노드에 여러개의 값이 들어있습니다.(실제론 더 많습니다)
리프노드는 현재 테이블에 저장된 모든 행의 a,b,c 의 값을 정렬된 채로 갖고 있습니다. 위 이미지는 행이 10개 저장된 테이블의 인덱스 입니다. 자식노드가 있는 루트노드와 중간노드는 자식이 갖고있는 최소값과 자식노드의 위치정보를 갖고 있습니다.
중간 노드 중 가장 왼쪽에 있는 노드는 자식 노드가 두개이며 두 자식 노드의 (a,b,c)의 최소값이 (1,1,1)과 (3,1,2) 입니다.

수직탐색과 수평탐색

인덱스 트리에서 (a,b,c)가 (10, 1, 2)인 행을 찾는 과정을 보겠습니다. 루트노드에서 시작해서 자식노드 3개 중에 (a, b, c)가 (10, 1, 2)보다 작은 값 중 최대값을 갖는 노드를 찾습니다.
(1,1,1), (3,3,1) (10,1,3) 중 위 조건에 맞는 값은 (3,3,1) 입니다.
해당 노드에서도 마찬가지로 (10, 1, 2)보다 작은 값 중 최대값인 노드를 찾습니다.
(3, 3, 1) 노드로 이동했더니 해당 노드에 찾는 결과가 있습니다.

B+Tree의 특징은 각 노드가 바로 이전과 다음 노드로 갈 수 있도록 위치정보를 갖고 있습니다. (10, 1, 2)를 찾은 후 오른쪽 노드로 가서 최소값을 봤더니 (10, 1, 3) 입니다. 조건에 맞는 값이 아니므로 검색을 종료합니다.

위 예시에서 루트노드로부터 내려오며 찾는 과정을 수직탐색.
오른쪽(또는 왼쪽)으로 이동하며 다음값을 찾는 과정을 수평탐색 이라고 합니다.

인덱스의 pk

인덱스로 값을 조회해서 어떻게 테이블의 모든 데이터를 가져올 수 있는지 보겠습니다.
위 이미지에 누락됬지만 mysql의 모든 인덱스는 pk가 자동으로 뒤에 추가되어 생성됩니다.
(a, b, c) 컬럼으로 인덱스를 만든다면 실제로는 (a, b, c, pk) 인덱스가 생성됩니다.
pk가 복합키라면 (a, b, c, pk1, pk2 ...)로 생성됩니다.
인덱스에 이미 pk가 포함되도록 (a, pk1, b, c) 로 구성했다면 누락된 pk만 뒤에 추가됩니다
(a, pk1, b, c, pk2 ...)
모든 인덱스에 pk가 포함된다고 했으니 인덱스로 값을 찾으면 찾은 행의 pk를 알 수 있습니다.

자동으로 추가된 pk를 포함한 인덱스 구조입니다. 리프노드 중 가장 오른쪽 노드에서처럼 a,b,c 의 값이 모두 같다면 pk를 기준으로 정렬됩니다. 위 예시에서 (10, 1, 2)를 가진 행의 pk는 10인 것을 찾았습니다.

테이블 트리

mysql의 innodb는 클러스터링 테이블로써 테이블도 하나의 트리로 구성됩니다.
테이블에 해당하는 트리는 인덱스와 조금 다릅니다.
리프노드엔 테이블의 모든 데이터가 들어있지만 그 위 노드들에는 pk만 존재합니다.

pk는 중복되지 않으므로 pk로만 정렬해도 중복되는 값이 없습니다.
pk가 10인 값을 찾는 과정은 인덱스와 동일합니다.
10보다 작은값 중 최대값을 가진 자식노드로 이동하는 것을 반복합니다.
루트노드의 자식노드가 pk를 1, 5, 7 갖고있으므로 7로 이동합니다. 자식노드가 7과 9를 갖고있으므로 9로 이동하면 pk가 10인 행이 존재합니다.

정리

SELECT *
FROM my_table t
WHERE t.a = 10
  AND t.b = 1
  AND t.c = 2

이제 위 쿼리가 어떤식으로 인덱스를 이용해 값을 가져오는지 알 수 있습니다.
인덱스 idx1을 통해 (a, b, c)가 (10, 1, 2)인 인덱스 값을 수직탐색을 통해 찾습니다.
인덱스 데이터에서 pk가 10인 것을 확인하여 테이블 트리에서 수직탐색하여 데이터를 가져옵니다. 인덱스 트리에서 수평탐색을 통해 다음값으로 이동 후 (10, 1, 2)가 맞는지 확인합니다.
찾으려는 (10, 1, 2)가 아니므로 종료합니다.

다음 글로 이어집니다.

FM (Foundation Model)

많은 데이터로 학습된 AI 모델 텍스트, 이미지, 음성 등 여러 형식의 데이터를 처리할 수 있는 멀티모달 만드는데 비용이 많이 들어서 일반 기업에서는 직접 만들 수 없다 openai나 meta 등에서 만든다.

LLM (Large Language Model)

FM의 한 종류로 자언어 처리에 특화된 모델 텍스트입력->텍스트출력 ex] gpt4o, gpt3.5 ...

Llama (Large Language Model Meta AI)

메타에서 오픈소스로 공개한 LLM

Embedding

텍스트, 이미지, 오디어 등을 벡터로 변환한다 고차원(openai의 small:1536, large:3072)의 숫자 배열로 변환된다. 높은 차원으로 변환할수록 더 세세한 구분이 되지만 비용이 증가한다. 같은 의미를 갖거나 비슷한 내용의 단어는 가까운 공간에 배치되어 유사도 검색에 주로 사용된다. (코사인, 유클리드..) openai 등에서 embedding api를 제공한다.

RAG (Retrieval-Augmented Generation)

LLM 에 질문을 하기 전, 외부 데이터베이스에 관련 정보를 검색하여서 프롬프트에 관련 정보를 제공하며 질문을 하는 시스템 주로 벡터 데이터베이스가 사용되며 특정 지식에 대한 질문에 많이 사용된다(Faq챗봇) 관련 정보를 같이 넘기기 때문에 AI 특유의 이상한 답변을 하지 않는다. 데이터베이스를 업데이트하여 최신 정보도 답변할 수 있다.

1. 특정 도메인에 대한 지식을 벡터화 하여 벡터DB에 미리 넣어둔다.
2. 질문을 동일한 벡터API를 사용하여 벡터화하여 벡터DB에 검색해 유사한 내용을 찾는다.
3. 찾은 내용을 바탕으로 chat API를 사용한다. 임베딩API도 저렴하기 때문에 chromaDB를 사용한 기본 예제 수준의 챗봇은 파이썬 몇십~몇백줄 정도로 가볍게 만들 수 있고 고도화가 관건인 듯 하다. openai에서 제공하는 assistant api로도 만들 수 있다.

fine-tuning

LLM 모델에 원하는 지식을 추가하는 것. 오픈소스 FM에 직접 할 수 있으나 openai 에서 fine-tuning api를 제공한다. 아마 다른데서도 할듯 [질문: 이름이 뭐야 / 답변: 내이름은 개발봇이야] 와 같은식의 데이터를 제공하여 훈련시킬 수 있다. RAG와 비교하여 모델 자체에 데이터를 훈련시키기 때문에 답변 퀄리티가 더 좋을 수 있다. 최신화 하려면 추가로 훈련시켜야 한다. 파인튜닝 한 모델에 RAG와 함께 사용할 수도 있다.

Few-shot

프롬프트에 답변받고자 하는 예시를 포함하여 질문하는 프롬프팅 기법. 특정 포멧으로 답변받고자 하는 경우 유용하다. RAG와 다르게 정보의 최신화나 정확성과는 별개로 답변의 출력 형식과 관련이 있다.

LangChain

ai 관련 오픈소스 프레임워크로 python이 메인이고 nodejs도 지원은 하는데 전부는 아닌듯. ai 분야는 랭체인 외에도 대부분 파이썬이 메인인듯 ai api나 벡터 데이터베이스, 임베딩 등 ai 관련 기능들을 구성하기 쉽도록 한다. 예시로 Chroma 벡터DB에 저장할 때 임베딩API를 자동으로 연동하거나 RAG 시스템을 chat, vectordb, embedding 각각의 API를 사용하지 않고 랭체인 sdk로만 구성할 수 있다. 그 외에도 기능이 많아보이는데 아직 많이 써보진 못했다.

Vector Database

• Chroma sqlite를 사용하는 벡터DB. 로컬에서 사용할 가벼운 작업이나 간단한 테스트에서 많이 사용되는 듯 하다. 때문에 대용량에서의 성능이나 확장성이 부족하다.
• pinecone 클라우드 서비스로만 제공된다. 가격이 비싸다는데 어느정돈지는 아직 감이 안온다. 서버 공급자로 aws 등을 쓸 수 있는데 한국이 없어서 괜찮을지 프로덕션 전 테스트가 필요할 것 같다. 인덱스 하나를 무료로 사용해볼 수 있다.
• postgresql 벡터DB를 지원한다는데 성능이 벡터 전용 DB에 비해 느리다.
• Elastic 벡터를 지원은 하지만 벡터를 위한 DB가 아니기에 전용으로 쓰기엔 메리트가 없는 것 같다.

openai API

• chat 기본 채팅 api 상태 비저장이기 때문에 chatgpt처럼 이전 대화내용을 기반으로 질문을 하려면 직접 넣어줘야한다. 파라미터로 이전 대화 내용을 입력하거나 원하는 응답 포멧(few-shot)을 예시화하여 추가할 수 있다. 대화 내용 목록 파라미터에서 role=system은 모델에게 지시할 규칙, AI의 역할 등을 입력 role=assistant는 모델의 응답 role=user 는 유저의 질문으로 구성된다.

• assistant 초기 모델을 선택한 후 원하는 role을 적용하여 챗봇을 만들 수 있다. openai에서 제공하는 벡터스토어와 연계가 되어 파일을 미리 넣어두고 RAG처럼 활용할 수도 있다.

• fine tuning 파인 튜닝을 API를 사용해서 할 수 있다. 초기 모델을 고를 수 있다 (gpt4o, gpt4o mini ..)

• embedding 벡터화 하는 API를 제공한다. ada v2 이후에 text-embedding-3-large, text-embedding-3-small이 신규버전인 듯 하다. ada v2와 small은 1536차원, large는 3072차원으로 벡터화한다. 가격은 adav2:small:large = 10:2:13 정도

더 알아볼거/공부할거 ; 트랜스포머 ; 허깅페이스 ; 벡터DB 구조, 기능, 최적화 ; etc...

publish용. aws sns와 같은 역할

바인딩 된 queue로 메시지를 전송

선언 (declare)

exchange 생성

• type

  • direct routing key와 일치하는 큐에 전송

  • topic routing key와 패턴 일치하는 큐에 전송

  • headers routing key 무시하고 header 일치하는 큐에 전송

  • fanout 바인딩된 큐 전체에 전송

• passive true인 경우 미리 선언했을 경우 새로 생성하지 않음

• durable true: 디스크에 저장, false: 메모리 저장(transient)

• autoDelete consumer가 없으면 자동 삭제됨

전송(publish)

메시지 게시

• mandatory true인 경우 하나 이상의 queue가 메시지 받아야 성공

Queue

메시지 대기열

선언(declare)

큐 생성

• exclusive true인 경우 혼자만 사용함. 해당 subscriber 종료시 큐 삭제됨

• durable true: 디스크에 저장, false: 메모리 저장(transient)

• autoDelete 모든 소비자 연결 끊기면 자동 삭제됨

• arguments

  • x-dead-letter-exchange : {exchange} nack 호출시 설정한 exchange로 전송
  • max-lenght, message-ttl 등 다양한 값 있음

소비(consume)

큐 메시지 수신

• content 메시지 내용

• properties 메시지 정보

  • timestamp
  • reply-to
  • 기타 정해진 값들 있음, 전송할 때 미설정시 모두 null

• ack, nack

  • 성공, 실패
  • nack(true)로 해서 다시 큐에 담을 수 있음 ( 다시 받은 큐메시지는 redeliveryed = true )

고성능, 고가용성 최적화

1. 대기열 짧게 유지

   대기열이 길면 RAM 사용 많아짐, 성능 저하

2. connection/channel 재사용

   연결시 핸드셰이크 과정 복잡함

3. connection/channel 수 낮게 유지

   하나의 프로세스당 하나의 커넥션, 스레드당 하나의 채널이 이상적

4. publish/consume 커넥션 분리

   게시용, 소비용 분리

5. 스레드 간 채널 공유하지 않음

   스레드 세이프 하지 않음

6. 프리페치값 설정

   메모리부족, 충돌 문제 해결

7. 지연대기열 사용 (고가용성)

   속도는 느려지지만 RAM 사용량 최소화

8. 수동 ack 비활성화 (고성능)

   성능 향상

SELECT id                         AS id     
     , parent_id                  AS parent_id     
     , value                      AS value

     # 1부터 순서대로 - 공동순위 없음
     , row_number() OVER w        AS rownumber

     # 순위 %
     , PERCENT_RANK() OVER w      AS 'percent_rank'

     # 순위 - 공동1위 있으면 다음순위 3등
     , RANK() OVER w              AS 'RANK'

     # 순위 - 공동1위 있어도 다음순위 2등
     , DENSE_RANK() OVER w        AS 'DENSE_RANK'

     # 첫 번째 값
     , FIRST_VALUE(value) OVER w  AS 'FIRST_VALUE'

     # 마지막 값
     , LAST_VALUE(value) OVER w   AS 'LAST_VALUE'

     # 이전 값
     , LAG(value) OVER w          AS 'LAG'

     # 이후 값
     , LEAD(value) OVER w         AS 'LEAD'

     # 파티션 내 3번째 'value'값
     , NTH_VALUE(value, 3) OVER w AS 'NTH_VALUE'

     # 파티션을 N개로 나눈 후 현재 분할된 파티션의 번호
     , NTILE(3) OVER w            AS 'NTILE'
FROM (
         VALUES
             /* parent_id = 1 */
             ROW(1, 1, 100)
            ,ROW(2, 1, 100)
            ,ROW(3, 1, 300)
            ,ROW(4, 1, 400)
            ,ROW(5, 1, 500)
            ,ROW(6, 1, 600)
            /* parent_id = 2 */
            ,ROW(7, 2, 900)
            ,ROW(8, 2, 100)
            ,ROW(9, 2, 500)
            ,ROW(10, 2, 700)
            ,ROW(11, 2, 600)
            ,ROW(12, 2, 300)
            ,ROW(13, 2, 300)
            ,ROW(14, 2, 300)
            ,ROW(15, 2, 300)
            ,ROW(16, 2, 300)
            ,ROW(17, 2, 300)
            ,ROW(18, 2, 300)
         ) AS t(id, parent_id, value)
    WINDOW w AS (
        PARTITION BY parent_id 
        ORDER BY value
        # 기본값 - 첫 번째 행부터 현재 행까지
#         RANGE BETWEEN UNBOUNDED PRECEDING
#             AND CURRENT ROW

        # 1행 이후부터 2행 이후까지
#         RANGE BETWEEN 1 FOLLOWING
#                   AND 2 FOLLOWING

        # 첫 번째 행부터 마지막 행까지
#         RANGE BETWEEN UNBOUNDED PRECEDING
#                   AND UNBOUNDED FOLLOWING

        # 1행 전부터 1행 이후까지
#         RANGE BETWEEN 1 PRECEDING
#                   AND 1 FOLLOWING
    )
ORDER BY id

order by가 있을 경우

처음 행부터 현재 행까지 집계

order by가 없을 경우

처음 행부터 마지막 행까지 집계

결과

참조 https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

조인은 아니지만 조인과 같이 사용하는 준조인

1. Table Pullout

서브쿼리를 조인으로 바꾼다. 다른 최적화도 사용할 수 있다.
전체 semijoin 플래그 외에는 비활성화 시키는 variable이 없다.

2. FirstMatch

In 서브쿼리를 exists로 바꿔서 실행시켜서 각 로우에 대해 handler_read_key 1회씩만 실행한다. 아우터쿼리에서 적절한 인덱스를 사용할 수 있을 때 사용한다.

# before
SELECT *
FROM employees e
WHERE e.emp_no IN (
    SELECT emp_no
    FROM salaries
)

# after
SELECT *
FROM employees e
WHERE exists(
              SELECT *
              FROM salaries s
              WHERE s.emp_no = e.emp_no
          )

위 쿼리에서 before 쿼리가 after처럼 실행된다. 서브쿼리에 필요한 부분만 읽기 때문에 서브쿼리(salaries)의 행이 많을수록 유리하다.

3. Materialization

임시테이블로 구체화한 후 드라이빙 또는 드리븐 테이블로 사용한다. 드리븐 테이블로 사용할 때는 자동으로 생성되는 인덱스인 distinct_key가 사용된다. 서브쿼리는 상관서브쿼리가 아니어야 한다. (독립적으로 실행할 수 있어야 한다.) 서브쿼리에 Group by가 있어도 가능하다.

SELECT *
FROM employees e
WHERE e.emp_no IN (
    SELECT emp_no
    FROM salaries
)

서브쿼리가 먼저 실행되기 때문에 서브쿼리 비용이 높을 경우 (salaries 행이 많을수록) 불리하다.

4. LooseScan

서브쿼리를 루스스캔하여 읽고 드라이빙 테이블로 사용한다.

SELECT /*+
         SEMIJOIN(@subq1 LOOSESCAN)
       */ *
FROM departments d
WHERE d.dept_no IN (
    SELECT /*+ QB_NAME(subq1) */ DISTINCT de.dept_no
    FROM dept_emp de
);

# explain
[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "de",
    "partitions": null,
    "type": "index",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "20",
    "ref": null,
    "rows": 331143,
    "filtered": 0,
    "Extra": "Using index; LooseScan"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "d",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "16",
    "ref": "employees.de.dept_no",
    "rows": 1,
    "filtered": 100,
    "Extra": null
  }
]

루스 스캔 할 수 있는 서브쿼리에서 가능하다. 이상한 점은 departments가 9건, dept_emp 약 30만건인데 약 100m가 걸리고 인덱스 레인지스캔(handler_read_next)도 30만건으로 표시된다. explain에는 loosescan 표시됬지만 실제로는 mateiralizaion으로 실행되는건지 확인 필요하다

SELECT d.*
FROM departments d
     INNER JOIN (
        SELECT DISTINCT de.dept_no
        FROM dept_emp de
    ) de ON d.dept_no = de.dept_no
;

# explain
[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "<derived2>",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 9,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "d",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "16",
    "ref": "de.dept_no",
    "rows": 1,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "de",
    "partitions": null,
    "type": "range",
    "possible_keys": "PRIMARY,ix_fromdate,ix_empno_fromdate",
    "key": "PRIMARY",
    "key_len": "16",
    "ref": null,
    "rows": 9,
    "filtered": 100,
    "Extra": "Using index for group-by"
  }
]

세미조인의 루스스캔 말고 일반 루스스캔으로 서브쿼리 처리 후 조인하니까 10ms 소요된다. 인덱스 스캔(Handler_read_key)도 20건으로 정상적으로 표시된다.

5. Duplicate Weedout

서브쿼리를 조인으로 바꿔 실행한 후 중복을 제거한다. extra에 start temporary, end temporary가 표시된다. 조인으로 바꾸기 때문에 상관서브쿼리에서도 가능하다.

Skip Scan

다중 컬럼 인덱스에서 첫 번째 컬럼이 WHERE 조건에 없더라도 사용할 수 있게 한다. 일반적인 상황과 다르게 첫 번째 컬럼의 카디널리티가 낮을 수록 좋다. 쿼리가 커버링 인덱스인 상황에서만 사용할 수 있다. 첫 번째 컬럼도 IN 조건으로 모두 적용하면 커버링 인덱스 외에도 사용할 수 있다.

# index (gender, birth_date)

SELECT gender
     , birth_date
FROM employees e
WHERE birth_date > '1965-02-01'

WHERE에 gender가 없지만 인덱스를 사용할 수 있다. Using index for skip scan가 표시된다.

SELECT *
FROM employees e
WHERE gender IN ('M', 'F')
  AND birth_date > '1965-02-01'

WHERE문에 gender가 가질 수 있는 모든 값을 IN 으로 줘서 실행하면 스킵스캔 사용하지 않아도 되고 커버링 인덱스가 아니어도 가능하다

Loose Scan

주로 Group by 쿼리에서 MIN 이나 MAX 값을 구할 때 필요한 값만 읽는다.

SELECT dept_no
     , min(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no

Extra에 Using index for group-by가 표시된다.

인덱스 머지

한 쿼리에서 두 개의 인덱스를 같이 사용하는 방식

교집합

AND 조건에 각각 다른 인덱스를 사용할 경우 두 인덱스 모두 사용 후 교집합으로 결과를 구한다.

optimizer_switch의 index_merge_intersection으로 활성화/비활성화 할 수 있다.

Using intersect로 표시된다.

합집합

OR 조건으로 각각 다른 인덱스를 사용하며 같은 컬럼(PK)로 정렬 되어 있을 경우

우선순위 큐 알고리즘 사용하여 중복 없는 합집합을 구한다.

optimizer_switch의 index_merge_union으로 활성화/비활성화 할 수 있다.

Using union으로 표시된다.

정렬 후 합집합

OR 조건으로 각각 다른 인덱스를 사용하며 정렬 컬럼이 다른 경우

같은 컬럼(PK)로 정렬 후 합집합과 동일한 방식으로 집계한다.

optimizer_switch의 index_merge_sort_union으로 활성화/비활성화 할 수 있다.

Using sort-unoin으로 표시된다.

url='http://localhost/'

# 10명이 총 100회 요청
ab -n 100 -c 10 $url

# 100명이 총 1000회 요청
ab -n 1000 -c 100 $url

결과

Benchmarking localhost (be patient).....done


Server Software:
Server Hostname:        http://localhost/
Server Port:            80

Document Path:          /
Document Length:        0 bytes

# 동시 실행한 유저
Concurrency Level:      10
# 전체 테스트 소요시간
Time taken for tests:   2.595 seconds
# 응답 받은 요청
Complete requests:      100
# 실패한 요청
Failed requests:        0
Write errors:           0
Non-2xx responses:      100
Total transferred:      11300 bytes
HTML transferred:       0 bytes
# 초당 요청 수
Requests per second:    38.53 [#/sec] (mean)
# 요청 1회(동시 실행한 유저만큼)의 소요시간
Time per request:       259.544 [ms] (mean)
# 요청 1건의 소요시간
Time per request:       25.954 [ms] (mean, across all concurrent requests)
Transfer rate:          4.25 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0   26  33.0     16     201
Processing:     0  216 140.2    195     595
Waiting:        0  119 113.2     86     500
Total:         16  242 152.2    217     649

Percentage of the requests served within a certain time (ms)
  50%    217
  66%    316
  75%    347
  80%    394
  90%    480
  95%    518
  98%    602
  99%    649
 100%    649 (longest request)

ETC

응답의 길이가 다를 경우 실패로 표시

텍스트

• Text Analyzer로 토큰화하여 풀텍스트 검색 가능 index 필드 false로 두면 토큰화 하지 않아서 검색 불가능 집계나 정렬이 필요하면 fileddata:true 로 하거나 field로 keyword 사용

• Keyword 풀텍스트 검색 불가능 Analyzer 대신 Normalizer로 캐릭터필터와 토큰필터는 적용할 수 있음

그 외

• 숫자: long, integer, short, byte
• 날짜: date
• 불리언: boolean
• 객체: object, nested

쿼리

풀 텍스트 쿼리

• match_all : 전체 검색
• match: 쿼리에 있는 각 단어가 하나라도 포함된 문서 검색
• match_phrase: 쿼리와 정확히 일치하는 문장이 있는 문서 검색

Bool 복합쿼리

• must: 쿼리가 일치
• must_not: 쿼리가 불일치
• should: 일치하는 쿼리 점수 증가
• filter: 쿼리 일치하는 문서 검색, 점수 계산 안하며 캐싱 가능

범위 쿼리

• gte: greater then equal
• gt: greater then
• lte: less then equal
• lt: less then

애널라이저

• [캐릭터필터, 캐릭터필터 ...]
• 토크나이저
• [토큰 필터, 토큰 필터 ...]

캐릭터필터: 문장 전체에 대한 전처리 도구, 하나 이상 설정 가능

• HTML strip : html문을 일반텍스트로 변환
• Mapping: 특정 단어 변환
• Pattern Replace:

토크나이저: 문장을 토큰으로 분리하는 역할로 하나만 가능

• Standard : 띄어쓰기 단위로 분리 + 특수문자 제거
• Letter: 알파벳 제외한 모든 문자를 기준으로 토큰 분리
• Whitespace : 띄어쓰기 단위로 토큰 분리

토큰 필터 : 분리된 토큰을 처리하는 도구, 하나 이상 설정 가능

• Uppercase, Lowercase: 대문자 소문자
• Stop : 불용어 제거
• synonyms: 동의어로 변환

Aggregation

• 매트릭: sql의 select의 집계함수 개념 min, max, sum, avg ...
• 버킷: sql의 group by 개념 range, term ...
• sub-aggregaion: 버킷 안에 매트릭 또는 새로운 버킷 추가
• pipeline: 다른 매트릭의 결과를 입력으로 하여 다른 결과 냄

Real Mysql 8.0 참고

모든엔진은 performance_schema 엔진 사용 디스크에 테이블의 구조만 저정하며 모든 데이터 메모리에 저장함 바이너리 로그에 기록되지 않아서 레플리카 서버로 복제되지 않음

사용설정

performance_schema=ON

데이터 수집 및 저장 설정

setup_actors

수집할 유저(host, user) 설정

setup_objects

수집할 스키마, 테이블, 트리거 설정

setup_consumers

이벤트 종류별 설정

setup_threads

수집할 스레드 설정

setup_instruments

이벤트 타입

Connection 관련

accounts : 유저(user,host)별 커넥션 host: host별 커넥션 user: user별 커넥션

Variable 관련

global_variables : show variable 동일 global_status : show status 동일 variables_by_thread : 스레드별 변수 persisted_variables : set persist로 수정한 변수 variables_info : 변수의 마지막 수정일, 수정한 유저 호스트 user_variables_by_thread: 현재 연결된 세선들에서 생성한 사용자 정의 변수 session_status : 현재 세션에 대한 세션 범위의 상태변수 status_by_thread : 스레드별 상태변수

Event 관련

Transaction > Statement > Stage > Wait

Transaction: 트랜잭션에 대한 정보 Statement: 실행한 쿼리들의 정보 Stage: 쿼리들의 처리단계에 대한 정보 Wait: 대기하고 있는 이벤트들의 정보

이벤트별 테이블

current : 가장 최신 1건 history : 스레드별로 가장 최신 이벤트가 정해진 갯수만큼 history_long: 전체 스레드에 대한 최근 이벤트

show VARIABLES like ''%performance_schema_events_%_size%'';

LOCK

data_locks: 현재 잠금 중인 정보 data_lock_waits: 잠금으로 대기중인 정보 metadata_locks : get_lock()으로 잠근 정보 table_handles : 테이블 락 정보

Clone

clone_status: 현재 또는 마지막으로 실행된 클론 작업에 대한 상태 정보 clone_progress: 현재 또는 마지막으로 실행된 클론 작업에 대한 진행 정보

ETC

error_log: 에러 로그 파일 내용 host_cache: 호스트 캐시 정보 performance_schemas: performance_schema에서 사용 가능한 이벤트 타임들과 그 특성에 대한 정보 prosesslist: show processlist; thread: MYSQL 백그라운드 스레드 및 클라이언트 연결에 대한 포그라운드 스레드에 대한 정보 모네터링 및 과거 이벤트 데이터 보관 설정 여부 확인 tls_channel_status: MYSQL 연결 인터페이스별 TLS 속성 정보

사용 예

호스트 접속 이력

SELECT * FROM performance_schema.hosts;

총 사용중인 메모리

SELECT * FROM sys.memory_global_total;

스레드별 메모리

SELECT * FROM sys.memory_by_thread_by_current_bytes;

미사용 인덱스

SELECT * FROM sys.schema_unused_indexes;

중복 인덱스

SELECT * FROM sys.schema_redundant_indexes;

테이블별 작업량

SELECT * FROM sys.schema_table_statistics;

풀테이블 스캔 쿼리

SELECT * FROM sys.x$statements_with_full_table_scans;

자주 실행되는 쿼리

SELECT * FROM sys.statement_analysis order by exec_count desc;

실행시간 긴 쿼리

SELECT * FROM sys.x$statement_analysis order by avg_latency desc;

정렬 작업을 수행한 쿼리

SELECT * FROM sys.x$statements_with_sorting;

임시 테이블 생성한 쿼리

SELECT * FROM sys.x$statements_with_temp_tables;

alter 진행률 확인

SELECT * FROM performance_schema.events_stages_current ps_estc INNER JOIN performance_schema.events_statements_current ps_esmc ON ps_estc.NESTING_EVENT_ID = ps_esmc.EVENT_ID WHERE ps_estc.EVENT_NAME LIKE 'stage/innodb/alter%'

락 확인

SELECT * FROM sys.x$innodb_lock_waits;

InnnoDB와 같은 스토리지 엔진은 Handler API를 구현한다.

MYSQL은 모든 쿼리에서 handler Api를 이용해서 실제 데이터 작업을 처리한다.

이 때 상태변수에 이러한 작업 처리 횟수를 기록해 놓는다.

# 상태변수 초기화
flush status;

# 실행해볼 쿼리
SELECT ~

# 상태변수 조회
show status like 'handler_%'

select에 관련된 주요 상태 변수

Handler_read_first 인덱스 처음 값을 읽은 횟수 Handler_read_key 인덱스로 값 찾은 횟수 Handler_read_last 인덱스 마지막 값을 읽은 횟수 Handler_read_next 인덱스로 값 찾은 후 다음값으로 이동한 횟수 Handler_read_prev 인덱스로 값 찾은 후 이전값으로 이동한 횟수 Handler_read_rnd

테이블에서 고정된 위치 찾은 횟수 Handler_read_rnd_next 테이블 다음 데이터 읽기

Example

1. 인덱스로 검색

SELECT *
FROM salaries
WHERE salary = 100

salary에 인덱스가 걸려있는 경우

인덱스로 salary가 100인 첫번째 값 찾고 ( handler_read_key + 1 )

다음값을 읽어서 salary가 100 만족하는지 확인한다. ( handler_read_next + 1 )

유니크 인덱스인 경우 다음값이 없으므로 handler_read_next가 증가하지 않으며

아닌 경우 아닌 값이 나올 때 까지 다음 값을 검색하므로 찾은 값보다 1 더 많이 증가한다.

2. 조인 (Nested Loop)

SELECT *
FROM employees e
     INNER JOIN dept_emp de
                ON e.emp_no = de.emp_no
WHERE e.emp_no BETWEEN 20001 AND 20010

employees가 드라이빙 테이블이 되는 경우

제일 먼저 emp_no로 20001인 값을 찾는다 (handler_read_key + 1)

emp_no가 20011될 때까지 뒤로가며 찾는다 (handler_read_next + 10)

찾은 employees 행들에 대해 dept_emp인 값을 찾는다.

하나의 emp_no마다 dept_emp를 1회씩 찾고 (handler_read_key + 1)

유니크값이 아니므로 다음 값도 확인해봐야한다. (handler_read_next + 1)

이것을 모든 로우에 대해 반복한다 ( 20001~20010 모두 존재하는 경우 10회 )

해당 쿼리로 handler_read_key 11회, handler_read_next는 21회가 증가한다.

풀스캔

SELECT *
FROM employees
LIMIT 10000

employees가 innodb인 경우 PK가 테이블이므로 시작위치로 포인터 이동 후 10000건 찾으므로

handler_read_first, handler_read_key가 1씩 증가하고 handler_read_rnd_next가 10000 증가하는 듯 하다. handler_read_first와 handler_read_key가 myISAM에서는 증가하지 않았다.

Handler API 직접 사용

테이블에 대해 handler를 open하고 직접 사용해볼 수 있다.

각 인덱스와 테이블에 대해 행을 가리키는 포인터가 있는 듯 하다.

인덱스에서 포인터는 시작부분이나 끝부분, 또는 특정 값으로 검색해서 이동할 수 있다

테이블의 경우 시작부분으로 이동하거나 다음 값으로 이동할 수 있다.

Example

(1) 우선 테이블에 대해 핸들러를 open.

HANDLER employees OPEN

(2) PK 값으로 1건 검색

HANDLER employees READ 'PRIMARY' = (20000)

# pk값이 20000인 행 한건 검색 (limit 없는 경우 1건)
# handler_read_key + 1

(3) 위 (2)에서 이동한 포인터에서 10건을 추가로 읽기

HANDLER employees READ 'PRIMARY' NEXT LIMIT 10

# pk값이 20001~20010인 행이 검색됨
# handler_read_next + 10

(4) 인덱스 이용해서 검색

HANDLER employees READ ix_firstname = ('Parto') LIMIT 10

# firstname 값이 Parto인 행 최대 10건까지 검색됨
# handler_read_key + 1
# handler_read_next + 9 (10건 검색될경우)

(5) 인덱스 없이 테이블 읽기

HANDLER employees READ NEXT LIMIT 10;

# 테이블에 대한 현재 포인터가 없으므로 시작부터 10건 검색됨
# innoDB인 경우 PK=테이블 이지만 pk가 위치한 포인터에서 시작하지 않음
# 하지만 테이블 읽기 할 경우 pk 포인터 초기화됨
# handler_read_rnd_next + 9

(5) 위 (4)에서 사용한 인덱스 추가 검색

HANDLER employees READ ix_firstname NEXT WHERE emp_no < 20000 LIMIT 5;

# emp_no가 20000보다 아래인 값 5건 찾을 때 까지
# (4)에서 실행했던 위치부터 정방향으로 찾는다.
# (4)에서 검색값이었던 Parto가 10건이 넘은 경우 Parto인 행부터 나온다. 

(6) PK로 시작값부터 검색

HANDLER employees READ 'PRIMARY' FIRST;
HANDLER employees READ 'PRIMARY' = (10001) # PK 첫번째 값이 10001

# 위 값의 경우 handler_read_first도 1 증가하며
# 두 쿼리 모두 handler_read_key가 1씩 증가한다.

handler_read_first : 전체든 부분이든 인덱스를 시작부터 읽는 인덱스 풀스캔이 있다.

handler_read_key : 인덱스를 찾는 random i/o의 횟수로 너무 크지 않다면 좋음.

handler_read_rnd_next : 테이블을 읽는 순차 i/o의 횟수로 OLTP 환경에서는 안좋은 경우가 대부분.

handler_read_next: 인덱스 사용하는 대부분의 경우에도 발생한다.

handler_read_prev: 인덱스를 역방향으로 읽는 경우로 인덱스를 이용한 DESC 정렬이 있다.

기본키는 파티션키와 추가로 정렬키를 가질 수 있다. AWS에서 DynamoDB는 물리적으로 여러 파티션에 데이터를 나눠서 저장한다. 저장될 파티션의 위치를 정하는게 파티션키이며 파티션 내에선 정렬키로 정렬되어 저장된다.

파티션키 선택 기준

1. 카디널리티가 높아야한다.

카디널키의 중복도가 높을 경우 특정 물리적 파티션에 부하가 몰릴 수 있다. 카디널리티가 높은 경우여도 월별이나 일별로 키를 잡는 경우 해당 날짜의 모든 데이터가 하나의 파티션으로 들어가므로 좋지 않다.

2. 검색

파티션키는 HASH와 같이 동작하므로 BTREE처럼 범위검색이 되지 않는다. (실제 자료구조는 모름) 파티션키로 페이징이나 범위검색을 해야 될 경우 인덱스가 필요하다

로컬 보조 인덱스(LSI)

로컬 보조 인덱스는 파티션키는 같이 쓰고 정렬키만 다르게 설정한다. LSI는 테이블과 동일한 파티션 내에 있기 때문에 테이블에서의 쓰기 작업을 일관성 있게 가져올 수 있다. (강력한읽기사용) 한 파티션에서 실행하기 때문에 최대 5개만 지원하는 듯 하다.

글로벌 보조 인덱스(GSI)

글로벌 보조 인덱스는 정렬키를 포함해 파티션키도 다르게 설정한다. 파티션키가 다르기 때문에 비동기적으로 실행되므로 강력한 읽기를 지원하지 않는듯 하다. 최대 20개 지원한다.

인덱스 특징

다른 RDB와 다르게 인덱스를 이용해서 테이블을 찾는데 사용되지 않는다. 인덱스를 만들 때 테이블에서 복사할 컬럼을 지정해서 인덱스에도 저장하며 인덱스 검색시 해당 값으로 조회한다. 같은 값을 갖는 테이블을 새로 만드는 개념과 비슷한 것 같다.

사용 패턴

(1) 다른 DB와 다르게 하나의 테이블에 모든 데이터를 파티션키로 구분해서 저장하는 식으로 사용한다. ex] order#123 user#123

하나의 테이블을 사용함으로서 각각 테이블을 사용하는 것 보다 파티션 하나에 몰릴 확률을 줄일 수 있다.

(2) 정렬키를 컬럼명으로 사용함으로써 하나의 파티션키 검색으로 JOIN 효과를 낼 수 있다.

데이터(pk, sk)

• order#123, 주문자
• order#123, 배송정보
• order#123, 결제정보

(3) 글로벌 보조 인덱스 사용

파티션키와 정렬키를 반대로 지정한 인덱스로 특정 상황에서 검색을 용이하게 한다.

(4) 정렬키로 여러 값 정렬

여러 컬럼으로 정렬키를 못하므로 일반적으로 인덱스당 하나의 컬럼만 정렬할 수 있다. 한 컬럼에 주문자#주문금액#주문날짜 이런식으로 값을 넣으면 여러 값으로 정렬할 수 있다.

파티션

물리적인 파티션이 실제로 몇개로 나눠져있는지 확인해볼 수 없다. 사용량이나 데이터 크기에 따라 정해지는 것 같다. 사용이 몰리는 핫 파티션이 생길 경우 프로비저닝 모드에서 지정한 용량을 모두 효율적으로 사용하지 못한다. 온디맨드 환경에서는 자세한 내용은 못찾았지만 비슷할 것 같다

백그라운드 스레드

• 인서트버퍼를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드
• 버퍼풀의 데이터를 디스크에 기록하는 스레드
• 데이터를 버퍼로 읽어오는 스레드
• 잠금이나 데드락 모니터링 하는 스레드

innodb는 쓰기작업을 백그라운드에서 처리하기 때문에 디스크에 저장할 때 까지 기다리지 않아도 된다.

메모리

글로벌

모든 글로벌메모리는 MySQL서버가 시작하면서 할당된다.

시작부터 바로 100% 할당할 수도 있고 조금씩 할 수도 있다.

• 테이블캐시
• innoDB 버퍼풀
• innoDB 어댑티브 해시 인덱스
• innoDB 리두 로그 버퍼

로컬

필요할 때만 공간이 할당된다.

커넥션동안 할당되거나 (커넥선버퍼, 결과버퍼), 쿼리 실행하는 순간에만 할당(소트버퍼, 조인버퍼)한다

• 정렬 버퍼 (Sort buffer)
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

아키텍처

언두로그, MVCC

데이터 변경시 innodb 버퍼풀에서 언두로그로 기존 데이터를 복사하고 버퍼풀을 바로 수정한다.

• 언두로그를 읽으면 되서 일반 읽기에 잠금이 없다
• 모든 트랜잭션에 필요없는 데이터가 될 경우 언두로그 영역의 기존 데이터를 삭제한다.
• 트랜잭션이 안끝나고 언두로그가 계속 커지면 서버가 느려진다.
• 자동 데드락감지시 종료할 스레드 기준을 언두로그가 작은순으로 정한다

버퍼풀

데이터나 인덱스를 메모리에 캐시해주는 공간

동적으로 늘리는 과정보다 줄이는 과정이 시스템 영향도가 크므로 50%에서 점점 늘려가본다.

버퍼풀 인스턴스는 기본8개로 메모리 40G이하는 기본값, 그 이상은 인스턴스당 5G정도 되게 한다

버퍼풀 크기 = (128MB * 버퍼풀인스턴스)의 배수

리두로그

버퍼풀은 데이터 캐시와 쓰기작업의 버퍼링 두가지 용도를 갖는다.

버퍼풀 사이즈만 늘릴 경우 데이터 캐시 용도로서의 기능만 향상되며 리두로그까지 늘려야 쓰기 버퍼링으로서의 용도도 성능이 향상된다.

가장 최근 체크포인트의 LSN과 마지막 리두 로그 엔트리의 LSN의 차이를 체크포인트 에이지라고 한다. 체크포인트 에이지는 활성 리두 로그 공간의 크기이다.

100GB 이하 에서는 5~10GB로 설정하고 조금씩 늘려가본다.

버퍼풀 플러시

• 플러시 리스트 플러시
• LRU 리스트 플러시

큰 문제가 없으면 건드릴 필요 없다.

플러시 리스트 플러시 관련 변수

innodb_page_cleaners

클리너 스레드의 수, 버퍼풀 인스턴스 수랑 동일하게 맞추자. 버퍼풀 인스턴스보다 클 수 없다.

inndb_max_dirty_page_pct

더티페이지의 비율, innodb_io_capacity 기준으로 더티페이지를 하드에 쓰며 이 값을 유지한다.

기본값 사용하자

inndb_max_dirty_page_pct_lwm

기본10%, 이 값보다 더티페이지 비율이 클 경우 조금씩 디스크로 기록한다.

디스크 쓰기가 많이 발생하고 더티페이지 비율이 너무낮다면 좀 올리자

innodb_io_capacity, innodb_io_capacity_max

어느정도 디스크 읽기,쓰기가 가능한지 설정하는 값으로 일반적인 상황과 최대값을 의미한다.

이 값을 기준으로 더티페이지 쓰기를 실행한다.

스토리지 엔진은 다른 작업도 하므로 디스크의 성능을 그대로 쓰면 안된다.

innodb_adaptive_flushing

기본값 사용, 위 innodb_io_capacity 같은 설정값에 의존하지 않고 새로운 알고리즘 사용한다.

innodb_adaptive_flushing_lwm

기본값 10%, 이 값 이하에서는 어댑티브 플러시 알고리즘이 작동하지 않는다.

innodb_flush_neighbors (이웃페이지 동시쓰기)

기본값 비활성, 더티페이지를 디스크에 기록할 때 근접한 페이지에 더티페이지가 있다면 함께 묶어서 기록할지 여부

하드디스크에서는 2(활성)을 추천하지만 ssd에서는 기본값으로 쓰자

어댑티브 해시 인덱스

innodb에서 사용자가 자주 요청하는 데이터에 자동으로 생성하는 인덱스

innodb_adaptive_hash_index 활성화 여부

innodb_adaptive_hash_index_parts 파티션 개수, 도움이 된다면 늘려서 잠금경합 줄인다.

기본개념

소스(or도커이미지) + 기본설정 -> 배포, 로드밸런싱, 오토스케일링, 모니터링 ...

어플리케이션

제목 설명만 있는 껍데기

환경

어플리케이션에 종속되며 하나의 어플리케이션에 여러개 생성 가능 (ex] dev, release, production) 소스부터 설정까지 여기에 다 있음

어플리케이션

이름 잘 지어서 생성

환경

티어

• 웹 서버 환경 일반적인 웹사이트
• 작업자 환경 크론 작업, queue 작업 같은거 처리

플랫폼

java, nodejs, dotnet, docker 등등 인기있는 언어들 많은데 도커만 써봤음

• Docker running on 64bit Amazon linux 2 이거 쓰면 됨 Dockerrun.aws.json 사용할 경우 "AWSEBDockerrunVersion": "1"
• 도커 멀티 컨테이너 예전꺼임 AMI (AMI2 이전꺼) 사용함 Dockerrun.aws.json 사용할 경우 "AWSEBDockerrunVersion": "2"

소스

도커(ami2)의 경우 docker-compose.yml 파일만 올리면 자동으로 실행함 ECR에 있는 이미지 사용할 경우 aws-elasticbeanstalk-ec2-role 역할에 AmazonEC2ContainerRegistryReadOnly 권한 필요함 그 외 참조 https://docs.aws.amazon.com/ko_kr/elasticbeanstalk/latest/dg/tutorials.html

CLI 참조

eb cli

aws cli 대신 사용하는 elastic beanstalk 전용 cli

eb init

현재 디렉토리 프로젝트를 새로운 애플리케이션으로 생성하거나 기존 애플리케이션에 연결 대화형 인터페이스 다하면 .elastic~폴더 생기면서 연결됨 그 후 프로젝트 폴더에서 eb 사용 되는듯 함

https://docs.aws.amazon.com/ko_kr/elasticbeanstalk/latest/dg/eb-cli3-getting-started.html

배포

젠킨스에서는 eb 말고 그냥 aws cli 사용했음 환경 그대로 다시 업데이트 시켜서 배포함 (docker-compose.yml 이미지:latest)

aws elasticbeanstalk update-environment \
    --region ap-northeast-2 \
    --environment-name {env_name} \
    --version-label {label_name}

롤링 배포시 EC2 타겟그룹 헬스체크 시간/횟수 줄여서 배포속도 올리기