Skip to content

Latest commit

 

History

History
911 lines (899 loc) · 114 KB

Real MySQL 8.0 1권.md

File metadata and controls

911 lines (899 loc) · 114 KB

서평

  • 제목 없는 디자인 (7)
  • 서비스 운영 중 추천 시스템 배치에서 DB lock 운영 문제가 발생되었던 적이 있었습니다. 추천 결과를 DB temp table로 export 하는 과정에서 Lost connection to MySQL server during query 에러가 발생하였고 방어 로직으로 2회 재시도하였지만 lock wait timeout exceeded에러가 발생해서 배치 실패 경험이 있었습니다. 물론 spark와 airflow를 튜닝하면서 그 당시에는 쿼리 수정 및 에러 로그와 원론적인 부분만 팠었지만 현재 다른 서비스를 운영하면서 presto와 hive 엔진을 사용하며 sql 성능 최적화 및 튜닝 포인트에 관심이 가기 시작했습니다. MySQL에 대해서 디테일하게 배우고자 책을 선택하게 되었던 거 같습니다.
  • 이 책은 1권과 2권으로 나누어 지며 1권에서는 MySQL 설치와 서버 아키텍처, 트랜잭션과 잠금, 인덱스 종류의 구조 및 활용, GTID 기반 복제 및 InnoDB 클러스터 아키텍처, 쿼리의 실행 계획 분석 및 최적화에 관한 내용을 다루고 있습니다.
  • MySQL 엔진과 MySQL 서버에서 기본으로 제공되는 InnoDB 스토리지 엔진, MyISAM 스토리지 엔진을 구분해서 아키텍처를 살펴보았고 MySQL의 동시성에 영향을 미치는 잠금과 트랜잭션, 트랜잭션의 격리 수준(Isolation level)에 대해 배우며 MySQL 서버에서 사용 가능한 압축 방식은 테이블 압축과 페이지 압축에 관해 구분해서 살펴보았습니다. 또한 MySQL에서 사용 가능한 인덱스의 종류 및 특성에 대해서 세세하게 다뤄볼 수 있었고 MySQL 서버가 사용자의 요청을 처리하기 위해 데이터를 가공하는 기본 절차와 빠른 성능을 보장하기 위해 수행하는 최적화에 대해 옵티마이저와 힌트를 자세하게 살펴보았습니다. 마지막으로 실행 계획을 읽는 순서와 실행 계획에 출력되는 키워드, 알고리즘에 대해 자세하게 배울 수 있었습니다.
  • 이 책은 개발자와 DBA를 위한 MySQL 실전 가이드로 소개하고 있으며 단순히 SQL의 사용이나 튜닝이 아니라 MySQL 서버와 엔진에 대해서 디테일하게 다루고 있어서 난이도가 조금 있었던거 같습니다. 평소에 자주 사용하던 SQL이 관점에 따라서 그리고 다양한 방식으로 최적화를 할 수 있다는 점에서 아직 배울게 많다고 느꼈습니다.

소개

  • HBase와 MongoDB는 특정 유스케이스에 적합한 DBMS인 반면, MySQL 서버와 같은 RDBMS는 범용 DBMS 영역에 속함. 어떤 서비스를 개발하든 초기에는 범용 DBMS를 선택하고, 사용량이나 데이터의 크기가 커지면 일부 도메인 또는 테이블의 데이터만 전용 DBMS로 이전해서 확장하는 형태를 대부분 회사에서 선택하고 있음
  • MySQL 서버가 견고하게 자리잡고 있음, 다른 DBMS보다 MySQL 서버의 노력과 시간 투자 대비 효율이 가장 높다는 것을 의미함, 서비스 개발자라면 MySQL 서버를 이해하기 위해서 시간을 투자해야 하는 이유
  • ORM은 DBMS와의 인터랙션을 블랙박스로 만들어 버리기 때문에 ORM 도구가 DBMS로 어떤 쿼리르 실행하는지를 알기 어려움. ORM이 최적은 아니어도 최악의 쿼리를 만들어내는 경우를 회피하기 위해 서비스 개발자는 RDBMS와 쿼리 처리 방식을 이해할 필요가 있음
  • 어떤 DBMS를 사용해야 할지 모르겠습니다. 어떤 DBMS가 좋은가요? 저자는 항상 자기가 가장 잘 활용할 수 있는 DBMS가 가장 좋은 DBMS입니다.라고 답변함. 안정성, 성능과 기능, 커뮤니티나 인지도를 고려하기

설치와 설정

  • MySQL 서버가 설치된 디렉터리는 /usr/local/mysql이며, 하위의 각 디렉터리 정보
    • bin : MySQL 서버와 클라이언트 프로그램. 유틸리티를 위한 디렉터리
    • data : 로그 파일과 데이터 파일들이 저장되는 디렉터리
    • include : C/C++ 헤더 파일들이 저장되는 디렉터리
    • lib : 라이브러리 파일들이 저장된 디렉터리
    • share : 다양한 지원 파일들이 저장돼 있으며, 에러 메시지나 샘플 설정 파일(my.cnf)이 있는 디렉터리
  • MySQL 서버의 시작과 종료

    • mysqld --defaults-file=/etc/my.cnf --initalize-insecure

      • mysqld 명령에 --initialize-insecure 옵션을 사용하면, 필요한 초기 데이터 파일과 로그 파일들을 생성하고 마지막으로 비밀번호가 없는 관리자 계정인 root 유저를 생성함
      • 비밀번호를 가진 관리자 계정을 생성하고자 한다면 다음과 같이 --initialize 옵션을 사용하면 됨. --initialize 옵션을 사용하면 생성된 관리자 계정의 비밀번호를 에러 로그 파일로 기록함
    • systemctl 유틸리티를 이용해 MySQL을 기동하거나 종료하는 것이 가능함

    • SHUTDOWN;

      • 원격으로 MySQL 서버를 셧다운하려면 MySQL 서버에 로그인한 상태에서 SHUTDOWN 명령을 실행하면 됨
      • 클린 셧다운(Clean shutdown) : 모든 커밋된 데이터를 데이터 파일에 적용하고 종료하는 것 , 클린 셧다운으로 종료되면 다시 MySQL 서버가 기동할 때 별도의 트랜잭션 복구 과정을 진행하지 않기 때문에 빠르게 시작할 수 있음
    • 서버 연결 테스트
      • telnet 명령이나 nc(Netcat) 명령을 이용해 원격지 MySQL 서버가 응답 가능한 상태인지 확인해볼 수 있음
      • Telnet과 Netcat 프로그램 모두 MySQL 서버로 접속해서 MySQL 서버가 보내준 메시지를 화면에 출력하는 것을 살펴볼 수 있음
      • 만약 Telnet이나 Netcat 프로그램이 서버의 버전 정보를 정상적으로 출력하는 상태에서도 응용프로그램이 MySQL 서버에 접속하지 못한다면 이는 MySQL 서버의 계정 비밀번호가 일치하지 않거나 MySQL 서버 계정의 host 부분이 허용되지 않은 경우일 가능성이 높음
  • MySQL 서버 업그레이드
    • 인플레이스 업그레이드(In-Place Upgrade) : MySQL 서버의 데이터 파일을 그대로 두고 업그레이드하는 방법
      • 여러 가지 제약 사항이 있지만 업그레이드 시간을 크게 단축할 수 있음
    • 논리적 업그레이드(Logical Upgrade) : mysqldump 도구 등을 이용해 MySQL 서버의 데이터를 SQL 문장이나 텍스트 파일로 덤프한 후, 새로 업그레이드된 버전의 MySQL 서버에서 덤프된 데이터를 적재하는 방법
      • 버전 간 제약 사항이 거의 없지만 업그레이드 시간이 매우 많이 소요될 수 있음
    • --upgrade 파라미터로 MiNIMAL과 FORCE의 차이는 서버 업그레이드 작업을 실행할지 여부, MINIMAL의 경우 서버 업그레이드를 건너뛰지만, FORCE의 경우 서버 업그레이드를 강제로 실행하게 됨. FORCE 옵션은 MySQL 서버의 시스템 테이블 구조가 잘못 변경됐거나 손상된 경우에 사용하면 됨
    • 일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용하는데, 리눅스를 포함한 유닉스 계열에서는 my.cnf라는 이름을 사용하고, 윈도우 계열에서는 my.ini라는 이름을 사용함
  • MySQL 시스템 변수의 특징
    • MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해 둠, MySQL 서버에서는 이렇게 저장된 값을 시스템 변수(System Variables)라고 함, SHOW VARIABLES 또는 SHOW GLOBAL VARIABLES라는 명령으로 확인할 수 있음
    • 시스템 변수가 가지는 5가지 속성
      • Cmd-Line : MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타냄
      • Option file : MySQL의 설정 파일인 my.cnf(또는 my.ini)로 제어할 수 있는지 여부를 나타냄
      • System Var : 시스템 변수인지 아닌지를 나타냄
      • Var Scope : 시스템 변수의 적용 범위를 나타냄. 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체(Global, 글로벌 또는 전역)를 대상으로 하는지, 아니면 MySQL 서버와 클라이언트 커넥션(Session, 세션 또는 커넥션)만인지 구분함
      • Dynamic : 시스템 변수가 동적인지 정적인지 구분하는 변수
  • 글로벌 변수와 세션 변수
    • 글로벌 범위의 시스템 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미하며, 주로 MySQL 서버 자체에 관련된 설정일 때가 많음. MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size) 또는 MyISAM의 키 캐시 크기(key_buffer_size)등이 가장 대표적인 글로벌 영역의 시스템 변수
    • 세션 범위의 시스템 변수는 MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용됨, 기본값은 글로벌 시스템 변수이며, 각 클라이언트가 가지는 값이 세션 시스템 변수. 각 클라이언트에서 쿼리 단위로 자동 커밋을 수행할지 여부를 결정하는 autocommit 변수가 대표적인 예
  • SET PERSIST
    • MySQL 서버의 max_connections라는 시스템 변수, MySQL 서버로 접속할 수 있는 최대 커넥션의 개수를 제한하는 동적 시스템 변수
    • SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 추가로 기록해 둠. MySQL 서버가 다시 시작될 때 기본 설정 파일(my.cnf)뿐만 아니라 자동 생성된 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수에 적용함. SET_PERSIST 명령을 사용하면 MySQL 서버 설정 파일(my.cnf)에 변경 내용을 수동으로 기록하지 않아도 자동으로 영구 변경이 되는 것
    • SET PERSIST 명령은 세션 변수에는 적용되지 않으며, SET_PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경함. 현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록해두고자 한다면 SET_PERSIST_ONLY 명령을 사용하면 됨
    • SET PERSIST_ONLY 명령은 정적인 변수의 값을 영구적으로 변경하고자 할 때도 사용할 수 있음. SET ERSIST 명령은 현재 실행 중인 MySQL 서버에서 동적인 변수들의 값을 변경함과 동시에 mysqld-auto.cnf 파일에도 기록하는 용도, 정적인 변수는 실행 중인 MySQL 서버에서 변경할 수 없음. innodb_doublewrite는 정적 변수로, MySQL 서버가 재시작될 때만 변경될 수 있음. 정적 변수를 mysqld-auto.cnf파일에 기록해두고자 할 때 SET PERSIST_ONLY 명령을 활용하면 됨
    • SET PERSIST 명령이나 SET PERSIST_ONLY 명령으로 시스템 변수를 변경하면 JSON 포맷의 mysqld-auto.cnf파일이 생성됨. mysqld-auto.cnf 파일에는 변경된 시스템 변수의 이름과 설정값, 추가로 언제 누구에 의해 시스템 변수가 변경됐는지 등의 정보도 함께 기록됨
    • SET PERSIST 또는 SET PERSIST_ONLY 명령으로 변경된 시스템 변수의 메타데이터는 performance_schema.variables_info 뷰와 performance_schema.persisted_variables 테이블을 통해 참조할 수도 있음
    • mysqld-auto.cnf 파일의 내용을 삭제해야 하는 경우에는 다음과 같이 REST PERSIST 명령을 사용하는 것이 안전함

사용자 및 권한

  • 사용자 식별
    • MySQL의 사용자는 다른 DBMS와는 조금 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP 주소)도 계정의 일부가 됨
    • 만약 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶다면 사용자 계정의 호스트 부분을 % 문자로 대체하면 됨, % 문자는 모든 IP 또는 모든 호스트명을 의미함
  • 사용자 계정 관리
    • 계정은 SYSET_USER 권한을 가지고 있느냐에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)으로 구분됨
    • 시스템 계정은 데이터베이스 서버 관리자를 위한 계정이며, 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하면 이해하기 쉬울 것
    • 시스템 계정
      • 계정 관리(계정 생성 및 삭제, 그리고 계정의 권한 부여 및 제거)
      • 다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
      • 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정
    • 시스템 계정과 일반 계정의 개념이 도입된 것은 DBA(데이터베이스 관리자) 계정에는 SYSTEM_USER 권한을 할당하고 일반 사용자를 위한 계정에는 SYSETM_USER 권한을 부여하지 않게 하기 위해서
      • 사용자 : MySQL 서버를 사용하는 주체(사람 또는 응용 프로그램)
      • 계정 : MySQL 서버에 로그인하기 위한 식별자(로그인 아이디)
    • MySQL 서버에 내장된 계정
      • 'mysql.sys'@'localhost': MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 그리고 프로시저)들의 DEFINER로 사용되는 계정
      • 'mysql.session'@'localhost': MySQL 플러그인이 서버로 접근할 때 사용되는 계정
      • 'mysql.infoschema'@'localhost' : information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
  • 계정 생성
    • 일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령
CREATE USER 'user'@'%'
  IDENTIFIED WITH 'mysql_native_password' BY 'password'
  REQUIRE NONE
  PASSWORD EXPIRE INTERVAL 30 DAY
  ACCOUNT UNLOCK
  PASSWORD HISTORY DEFAULT
  PASSWORD REUSE INTERVAL DEFAULT
  PASSWORD REQUIRE CURRENT DEFAULT;
  • IDENTIFIED WITH
    • 사용자의 인증 방식과 비밀번호를 설정함. IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시, 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시해야함
      • Native Pluggable Authentication : 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식
      • Caching SHA-2 Pluggable Authentication : 암호화 해시값 생성을 위해 SHA-2(256비트) 알고리즘을 사용함. Native Authentication과의 가장 큰 차이는 사용되는 암호화 해시 알고리즘 차이이며, SHA-2 Authentication은 저장된 해시값의 보안에 더 중점을 둔 알고리즘으로 이해할 수 있음
      • PAM Pluggable Authentication : 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Directory Access Protocol) 같은 외부 인증을 사용할 수 있게 해주는 인증 방식으로, MySQL 엔터프라이즈 에디션에서만 사용 가능함
      • LDAP Pluggable Authenticaiton : LDAP을 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식으로, MySQL 엔터프라이즈 에디션에서만 사용 가능함
    • Caching SHA-2 Authentication은 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기존 MySQL 5.7까지의 연결방식과는 다른 방식으로 접속해야 함. 보안 수준은 좀 낮아지겠지만 기존 버전과의 호환성을 고려한다면 Caching SHA-2 Authentication보다는 Native Authenticaiton 인증 방식으로 계정을 생성해야 할 수도 있음
  • REQUIRE
    • MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정함. 만약 별도로 설정하지 않으면 비암호화 채널로 연결하게 됨
  • PASSWORD EXPIRED
    • 비밀번호의 유효 기간을 설정하는 옵션, 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정됨
      • PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호 만료 처리
      • PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간 없음
      • PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호의 유효 기간을 설정
      • PASSWORD EXPIRE INTERVAL n DAY : 비밀번호의 유효 기간을 오늘부터 n일자로 설정
  • PASSWORD HISTORY
    • 한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
      • PASSWORD HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없음
      • PASSWORD HISTORY n : 비밀번호의 이력을 최근 n개까지만 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없음
  • PASSWORD REUSE INTERVAL
    • 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정함
      • PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수에 저장된 기간으로 설정
      • PASSWORD REUSE INTERVAL n DAY : n일자 이후에 비밀번호를 재사용할 수 있게 설정
  • PASSWORD REQUIRE
    • 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션
      • PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도로 설정
      • PASSWORD REQUIRE OPTIONAL : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
      • PASSWORD REQUIRE DEFAULT : password_require_current 시스템 변수의 값으로 설정
  • ACCOUNT LOCK / UNLOCK
    • 계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정함
      • ACCOUNT LOCK : 계정을 사용하지 못하게 잠금
      • ACCOUNT UNLOCK : 잠긴 계정을 다시 사용 가능 상태로 잠금 해제
  • 비밀번호 관리

    • INSTALL COMPONENT 'file://component_validate_password'; :validate_password 컴포넌트 설치

    • 비밀번호 정책
      • LOW : 비밀번호의 길이만 검증
      • MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자 배합을 검증
      • STRONG : MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증
    • 이중비밀번호 : 하나의 계정에 대해 2개의 비밀번호를 동시에 설정할 수 있는데, 2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분됨, 최근에 설정된 비밀번호는 프라이머리 비밀번호, 이전 비밀번호는 세컨더리 비밀번호. RETAIN CURRENT PASSWORD 옵션만 추가하면 됨
  • 권한(Privilege)
    • 글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
    • 객체 권한 : 데이터베이스나 테이블을 제어하는 데 필요한 권한
    • 객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 하며, 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야함
    • ALL(또는 ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용될 수 있는데, 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여하며, 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여하게 됨

    • GRANT SUPER ON . TO 'user'@'localhost';

      • *.*은 모든 DB의 오브젝트(테이블과 스토어드 프로시저나 함수 등)를 포함해서 MySQL 서버 전체를 의미함

아키텍처

  • MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있음, 손과 발의 역할을 담당하는 스토리지 엔진은 핸들러 API를 만족하면 누구든지 스토리지 엔진을 구현해서 MySQL 서버에 추가해서 사용할 수 있음
  • MySQL 엔진 아키텍처
    • MySQL은 일반 상용 RDBMDS와 같이 대부분의 프로그래밍 언어로부터 접근 방법을 모두 지원함. MySQL 고유의 C API부터 시작해 JDBC나 ODBC, 그리고 .NET의 표준 드라이버를 제공하며, 이러한 드라이버를 이용해 C/C++, PHP, 자바, 펄, 파이썬, 루비나 .NET 및 코볼까지 모든 언어로 MySQL 서버에서 쿼리를 사용할 수 있게 지원함
    • MySQL 엔진
      • 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룸
    • 스토리지 엔진
      • MySQL엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은스토리지 엔진이 전담함
      • 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있음
    • 핸들러 API
      • MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러(Handler) 요청, 여기서 사용되는 API를 핸들러 API
    • MySQL 스레딩 구조
      • MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있음
      • 스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션의 관계, 전통적인 스레드 모델에서는 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당됨. 스레드 풀에서는 커넥션과 포그라운드 스레드는 1:1 관계가 아니라 하나의 스레드가 여러 개의 커넥션 요청을 전담함
      • 포그라운드 스레드(크라이언트 스레드)
        • 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리함. 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시(Thread cache)로 되돌아감
        • 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리함. MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만(MyISAM도 지연된 쓰기가 있지만 일반적인 방식은 아님) InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리함
      • 백그라운 스레드
        • InnoDB는 여러 가지 작업이 백그라운드로 처리됨
          • 인서트 버퍼(Insert Buffer)를 병합하는 스레드
          • 로그를 디스크로 기록하는 스레드
          • InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
          • 데이터를 버퍼로 읽어 오는 스레드
          • 잠금이나 데드락을 모니터링하는 스레드
        • 쓰기 스레드(Write thread) : 로그 스레드(Log Thread)와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리
        • 사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없음
    • 메모리 할당 및 사용 구조
      • 글로벌 메모리 영역의 모든 메모리 공간은 MySQL 서버가 시작되면서 운영체제로부터 할당됨
      • 글로벌 메모리 영역
        • 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당됨
        • 필요에 따라 2개이상의 메모리 공간을 할당받을 수도 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개가 하더라도 모든 스레드에 의해 공유됨
          • 테이블 캐시
          • InnoDB 버퍼 풀
          • InnoDB 어댑티브 해시 인덱스
          • InnoDB 리두 로그 버퍼
      • 로컬 메모리 영역
        • 세션 메모리 영역, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역, 커넥션 버퍼와 정렬(소트) 버퍼 등이 있음
        • 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 함, 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 로컬 메모리 영역을 세션 메모리 영역이라고도 표현함
        • 로컬 메모리는 각 클랑이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징
        • 글로벌 메모리 영역의 크기는 주의해서 설정하지만 소트 버퍼와 같은 로컬 메모리 영역은 크게 신경 쓰지 않고 설정하는데, 최악의 경우(가능성은 희박하지만)에는 MySQL 서버가 메모리 부족으로 멈춰 버릴 수도 있으므로 적절한 메모리 공간을 설정하는 것이 중요함
        • 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 점
        • 로컬 메모리 공간은 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼나 결과 버퍼) 그렇지 않고 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있음
        • 대표적인 로컬 메모리 영역
          • 정렬 버퍼(Sort buffer)
          • 조인 버퍼
          • 바이너리 로그 캐시
          • 네트워크 버퍼
    • 플러그인 스토리지 엔진 모델
      • 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리됨
      • 핸들러(Handler) - 사람이 핸들(운전대)을 이용해 자동차를 운전하듯이, 프로그래밍 언어에서는 어떤 기능을 호출하기 위해 사용되는 운전대와 같은 역할을 하는 객체를 핸들러(또는 핸들러 객체)라고 표현함. MySQL 서버에서 MySQL 엔진은 사람 역할을 하고 각 스토리지 엔진은 자동차 역할을 하는데, MySQL 엔진이 스토리지 엔진을 조정하기 위해 핸들러라는 것을 사용하게 됨
      • MySQL 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야 한다는 점만 기억하자, Handler_로 시작하는 상태 변수는 MySQL 엔진이 각 스토리지 엔진에게 보낸 명령의 횟수를 의미하는 변수라고 이해하면 됨
    • 컴포넌트
      • 플러그인은 몇 가지 단점이 있는데 컴포넌트는 이러한 단점을 보완해서 구현함
        • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
        • 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음(캡슐화 안 됨)
        • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
    • 쿼리 실행 구조
      • 쿼리 파서
        • 사용자의 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조를 만들어내는 작업을 의미함
        • 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달함
      • 전처리기
        • 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인함
        • 각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행함
      • 옵티마이저
        • 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있음
      • 실행 엔진
        • 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정함
          • 1.실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
          • 2.다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
          • 3.읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
          • 4.데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
          • 5.최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
        • 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행함
      • 핸들러(스토리지 엔진)
        • 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당함
        • 핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 됨
      • 쿼리 캐시
        • MySQL 서버에서 쿼리 캐시(Query Cache)는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당함
        • 쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보임
        • 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제(Invalidate)해야 함, 이는 동시 처리 성능 저하를 유발함
      • 스레드 풀
        • 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적
        • 동시에 실행 중인 스레드들은 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능, 스케줄링 과정에서 PCU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처릭리가 더 느려지는 사례도 발생할 수 있다는 점에 주의
        • 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드(Worker thread)를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 함
    • 트랜잭션 지원 메타데이터
      • 데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 함
      • 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않기 때문에 테이블의 생성 또는 변경 도중에 MySQL 서버가 비정상적으로 종료되며 일관되지 않은 상태로 남는 문제가 있었음, 이를 데이터베이스나 테이블이 깨졌다라고 표현함
      • MySQL 서버가 작동하는 데 기본적으로 필요한 테이블들을 묶어서 시스템 테이블이라고 함, 대표적으로 사용자의 인증과 권한에 관련된 테이블들이 있음
  • InnoDB 스토리지 엔진 아키텍처
    • MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어남
    • 프라이머리 키에 의한 클러스터링
      • InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장됨, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻
      • 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용함
      • 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있음
    • 외래 키 지원
      • 외래 키에 대한 지원은 InnoDB 스토리지 엔진 레베렝서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없음
    • MVCC(Multi Version Concurrency Control)
      • 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있음
      • 멀티 버전이라 함은 하나의 레코드에 여러 개의 버전이 동시에 관리된다는 의미
    • 잠금 없는 읽관된 읽기(Non-Locking Consistent Read)
      • InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행함
      • 잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능함
    • 자동 데드락 감지
      • InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리함
      • InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료함, 이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 됨
    • InnoDB 버퍼 풀
      • InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
      • 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 함
        • 버퍼 풀의 구조
          • 버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 크게 LRU(Least Recently Used) 리스트와 플러시(Flush) 리시트, 그리고 프리(Free) 리스트라는 3개의 자료 구조를 관리함
          • 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이며 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됨
          • 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(이를 더티 페이지라고 함)의 변경 시점 기준의 페이지 목록을 관리함
          • LSN(Log Sequence Number) : 리두 로그 파일의 공간은 계속 순환되어 재사용되지만 매번 기록할 때마다 로그 포지션은 계속 증가된 값을 갖게 됨
          • 클리너 스레드(Cleaner Thread) : InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화하는 스레드
          • 워밍업(Warming Up) : 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태
    • 언두 로그
      • InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업함
      • 이렇게 백업된 데이터를 언두 로그라고 함
        • 트랜잭션 보장 : 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 언두 로그에 백업해둔 이전 버전의 데이터를 이용해 복구함
        • 격리 수준 보장 : 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 함
      • 언두 로그의 데이터의 사용 용도
        • 트랜잭션의 롤백 대비용
        • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공함
          • 트랜잭션의 격리 수준 : 도잇에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지를 결정하는 기준
      • 언두 테이블스페이스 관리
        • 언두 테이블스페이스(Undo Tablespace) : 언두 로그가 저장되는 공간
        • Undo tablespace truncate : 언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것
        • 언두 테이블스페이스의 불필요한 공간을 잘라내는(Truncate) 방법
          • 자동 모드 : InnoDB 스토리지 엔진의 퍼지 스레드(Purge Thread)는 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제하는 작업을 실행하는데, 이 작업을 언두 퍼지(Undo Purge)
          • 수동 모드
    • 체인지 버퍼
      • 체인지 버퍼(Change Buffer) : 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데 이때 사용하는 임시 메모리 공간
      • 체인지 버퍼 머지 스레드(Merge thread) : 체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 의미함
    • 리두 로그 및 로그 버퍼
      • 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치
      • 일부 DBMS에서는 리두로그를 WAL 로그라고도 함(Write Ahead Log의 줄임말로 데이터를 디스크에 기록하기 전에 먼저 기록되는 로그)
      • 리두 로그가 비활성화된 상태에서 MySQL 서버가 비정상적으로 종료된다면 MySQL 서버의 마지막 체크포인트 이후 시점의 데이터는 모두 복구할 수 없게 됨
    • 어댑티브 해시 인덱스(Adaptive Hash Index)
      • 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
      • 사용자는 innodb_adaptive_hash_index 시스템 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있음
      • 어댑티브 해시 인덱스는 B-Tree 검색 시간을 줄여주기 위해 도입된 기능
      • 해시 인덱스는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리됨, 인덱스 키 값은 B-Tree 인덱스의 고유번호(Id)와 B-Tree 인덱스의 실제 키 값 조합으로 생성됨
      • 성능 향상에 크게 도움이 되지 않는 경우
        • 디스크 읽기가 많은 경우
        • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
        • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
      • 성능 향상에 많은 도움이 되는 경우
        • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
        • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
        • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
      • 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다는 점
  • MyISAM 스토리지 엔진 아키텍처
    • 키 캐시
      • MyISAM 키 캐시는 인덱스만을 대상으로 작동하며, 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 함
    • 슬로우 쿼리 로그
      • MySQL 서버의 쿼리 튜닝
        • 서비스가 적용되기 전에 전체적으로 튜닝하는 경우 : 검토해야 할 대상 쿼리가 전부라서 모두 튜닝
        • 서비스 운영중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝 : 어떤 쿼리가 문제의 쿼리인지 판단하기 상당히 어려움
      • 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간(long_query_time 파라미터는 초단위로 설정하지만 소수점 값으로 설정하면 마이크로 초 단위로 설정 가능함) 이상의 시간이 소요된 쿼리가 모두 기록됨
      • 슬로우 쿼리 로그 파일에 기록되는 쿼리는 일단 정상적으로 실행이 완료됐고 실행하는 데 걸린 시간이 long_query_time에 정의된 시간보다 많이 걸린 쿼리인 것

트랜잭션과 잠금

  • 트랜잭션은 작업의 안정성을 보장해 주는 것, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능
  • 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능
  • 트랜잭션
    • 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때) 함을 보장해 주는 것
    • InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구함, MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update), 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 냄
    • 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있음
  • MySQL 엔진의 잠금
    • 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락(Metadata Lock), 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock)이라는 잠금 기능도 제공함
    • 글로벌 락(GLOBAL LOCK)
      • MySQL에서 제공하는 잠금 가운데 가장 범위가 큼
      • 글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 검
    • 테이블 락(Table Lock)
      • 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있음
    • 네임드 락(Named Lock)
      • GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있음
      • 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하느 잠금
      • 배치 프로그램처럼 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 함. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수는 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수도 없음. 동일한 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있음
    • 메타데이터 락(Metadata Lock)
      • 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
  • InnoDB 스토리지 엔진 잠금
    • 레코드 락(Record lock, Record only lock) : 레코드 자체만은 잠그는 것, 다른 상용 DBMS의 레코드 락과 동일한 역할을 함
    • 갭 락(Gap lock) : 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미함, 갭 락의 역할을 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것
    • 넥스트 키 락(Next key lock) : 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
  • MySQL의 격리 수준
    • 트랜잭션의 격리 수준(isolation level) : 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것
    • READ UNCOMMITTED
      • 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보임
      • 더티 리드(Dirty read) : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
      • 더티 리드가 허용되는 격리 수준
    • READ COMMITTED
      • 오라클 DBMS에서 기본으로 사용되는 격리 수준, 온라인 서비스에서 가장 많이 선택되는 격리 수준
      • 어떤 트랜잭션에서 데이터르 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문
      • READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문
    • REPEATABLE READ
      • MVCC(Multi Version Concurrency Control) : InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경함
      • MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없음
    • SERIALIZABLE
      • 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 됨
      • 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다는 것

데이터 압축

  • MySQL 서버에서 디스크에 저장된 데이터 파일의 크기는 일반적으로 쿼리의 처리 성능과도 적절되지만 백업 및 복구 시간과도 밀접하게 연결됨
  • 페이지 압축
    • 페이지 압축은 Transparent Page Compression, MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지가 압축되어 저장되고, MySQL 서버가 디스크에 데이터 페이지를 읽어올 때 압축이 해제되기 때문
    • 버퍼 풀에 데이터 페이지가 한 번 적재되면 InnoDB 스토리지 엔진은 압축이 해제된 상태로만 데이터 페이지를 관리함
    • 페이지 압축 기능은 운영체제별로 특정 버전의 파일 시스템에서만 지원되는 펀치 홀(Punch hole)이라는 기능을 사용함
  • 테이블 압축
    • 단점
      • 버퍼 풀 공간 활용률이 낮음
      • 쿼리 처리 성능이 낮음
      • 빈번한 데이터 변경 시 압축률이 떨어짐

데이터 암호화

  • MySQL 서버의 데이터 암호화
    • TDE(Transparent Data Encryption), Data at Rest Encryption, Data at Rest는 메모리(In-Process)나 네트워크 전송(In-Transit) 단계가 아닌 디스크에 저장(At Rest)된 단계에서만 암호화된다는 의미로 사용되는 표현
    • 2단계 키 관리
      • MySQL 서버의 TDE에서 암호화 키는 키링(KeyRing) 플러그인에 의해 관리됨
      • 데이터 암호화는 마스터 키(master key)와 테이블스페이스 키(tablespace key)(프라이빗 키-private key)
      • HashiCorp Vault 같은 외부 키 관리 솔루션(KMS, Key Management Service), 디스크의 파일(keyring_file 또는 keyring_encrypted_file 플러그인 사용시)에서 마스터 키를 가져오고, 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급함

인덱스

  • 디스크 읽기 방식
    • 랜덤 I/O와 순차 I/O
      • 랜덤 I/O라는 표현은 하드 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미함
      • 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였음, 디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정됨
      • 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정됨
      • 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 큼
  • 인덱스란
    • DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸림. 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-Value pair)으로 삼아 인덱스를 만들어 두는 것
    • SortedList는 저장되는 값을 항상 정렬된 상태로 유지하는 자료 구조, ArrayList는 값을 저장되는 순서 그대로 유지하는 자료 구조
    • SortedList 자료 구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있음
    • B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘
    • Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원함
  • B-Tree 인덱스
    • B-Tree의 B는 Balanced를 의미함
    • 칼럼의 원래 값을 변형시키지 않고 (물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내애서 항상 정렬된 상태로 유지함
    • 인덱스 키 검색
      • INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서임
      • 트리 탐색 : 인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행함
    • B-Tree 인덱스 사용에 영향을 미치는 요소
      • B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받음
      • 인덱스 키 값의 크기
        • InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 단위가 됨
        • 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 함
    • 인덱스 레인지 스캔
      • 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식, 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식
      • 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현함
      • 인덱스 레인지 스캔의 3단계
        • 1.인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)
        • 2.1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔(Index scan), 1번과 2번을 합쳐서 인덱스 스캔으로 통칭하기도 함
        • 3.2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어옴
          • 쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 함, 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라짐
    • 인덱스 풀 스캔
      • 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 함
      • 대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용됨
      • 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적임, 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용됨
      • 인덱스 리프 노드의 제일 앞 또는 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트(Linked list, 리프 노드 사이를 연결하는 세로로 그려진 두 쌍의 화살표)를 따라서 처음부터 끝까지 스캔하는 방식
      • 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적
    • 루스 인덱스 스캔
      • 타이트(Tight) 인덱스 스캔 : 인덱스 레인지 스캔과 인덱스 풀 스캔
      • 루스 인덱스 스캔 : 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미함
      • 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리함, GROUP BY 또는 집함 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용됨
    • 인덱스 스킵 스캔
      • 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어진 것
      • 인덱스를 효율적으로 이용한다는 것은 일반적으로 우리가 인덱스를 이용한다라는 표현과 동일한 의미로 인덱스에서 꼭 피룡한 부분만 접근하는 것을 의미함
      • 단점
        • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
          • 쿼리 실행 계획의 비용과 관련된 부분, 만약 유니크한 값의 개수가 매우 많다면 MySQL 옵티마이저느 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해짐, 쿼리의 처리 성능이 오히려 더 느려질 수도 있음
        • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)
    • 다중 칼럼(Multi-column) 인덱스
      • 두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(또는 복합 칼럼 인덱스), 2개 이상의 칼럼이 연결됐다고 해서 Concatenated Index라고도 함
    • B-Tree 인덱스의 정렬 및 스캔 방향
      • 인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장됨
      • 내림차순 인덱스
        • 실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유
          • 페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조
          • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조 (InnoDB 페이지 내부에서 레코드들이 단방향으로만 링크를 가진 구조)
    • B-Tree 인덱스의 가용성과 효율성
      • 비교 조건의 종류와 효율성
      • SELECT * FROM dept_emp
WHERE dept_no = 'd002' AND emp_no >= 1004;
      • 케이스 A : INDEX(dept_no, emp_no)
      • 케이스 B : INDEX(emp_no, dept_no)
        • 필터링 : 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업
        • 케이스 A 인덱스에서 2번째 칼럼인 emp_no는 비교 작업의 범위를 좁히는 데 도움을 줌
        • 작업 범위 결정 조건 : 케이스 A 인덱스에서 두 조건(dept_no = 'd002'와 emp_no>=10144)과 같이 작업의 범위를 결정하는 조건
        • 필터링 조건, 체크 조건 : 케이스 B 인덱스의 dept_no = 'd002' 조건과 같이 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건
        • 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서(최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 처리 성능을 높이지는 못함, 오히려 쿼리 실행을 더 느리게 만들 때가 많음
    • 가용성과 효율성판단
      • B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없음
        • NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)
        • LIKE '%??'(앞부분이 아닌 뒷부분이 일치) 형태로 문자열 패턴이 비교된 경우
        • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우

          • WHERE SUBSTRING(column,1,1) = 'X'

          • WHERE DAYOFMONTH(column) = 1

        • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

          • WHERE column = determinstic_function()

        • 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
        • 문자열 데이터 타입의 콜레이션이 다른 경우
  • R-Tree 인덱스
    • 공간 인데스(Spatial Index), 공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스
    • R-Tree 인덱스는 2차원의 공간 개념 값
      • 공간 데이터를 저장할 수 있는 데이터 타입
      • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
      • 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)
    • MBR : Minimum Bounding Rectangel의 약자로 해당 도형을 감싸는 최소 크기의 사각형
    • R-Tree 인덱스의 용도
      • 공간(Spatial) 인덱스 : MBR 정보를 이용해 B-Tree 형태로 인덱스 구축하므로 Rectangle의 R과 B-Tree의 Tree를 섞어서 R-Tree라는 이름이 붙여졌음
      • WGS84(GPS) 기준의 위도, 경도 좌표 저장에 주로 사용됨
  • 전문 검색 인덱스
    • 전문(Full Text) 검색 : 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색
    • 전문 검색(Full Text search) 인덱스 : 문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘
    • 인덱스 알고리즘
      • 전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축함
      • 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있음
      • 어근 분석 알고리즘
        • MySQL 서버의 전문 검색 인덱스의 두 가지 중요한 과정
          • 불용어(Stop Word) 처리
          • 어근 분석(Stemming)
        • 불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 으미ㅣ함
        • 어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
      • n-gram 알고리즘
        • n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법
      • 불용어 변경 및 삭제
        • 전문 검색 인덱스의 불용어 처리 무시
          • 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것
          • InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시할 수도 있음
        • 사용자 정의 불용어 사용
          • 불용어 목록을 파일로 저장하고, MySQL 서버 설정 파일에서 파일의 경로를 다음과 같이 ft_stopword_file 설정에 등록하면 됨
          • InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용할 수 있는데, 불용어의 목록을 테이블로 저장하는 방식
    • 전문 검색 인덱스의 가용성
      • 전문 검색 인덱스를 사용하기 위한 두 가지 조건
        • 쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ...)을 사용
        • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유
  • 멀티 밸류 인덱스
    • 전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가짐, 인덱스 키와 데이터 레코드는 1:1 관계를 가짐
    • 멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
    • 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입의 필드에 저장된 원소(Element)들에 대한 인덱스 요건이 발생한 것
  • 클러스터링 인덱스
    • 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준응로)들끼리 묶어서 저장하는 형태로 구현
    • 클러스터링 인덱스
      • 테이블의 프라이머리 키에 대해서만 적용되는 내용
      • 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현함
      • 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것
      • 장점
        • 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
        • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 함)
      • 단점
        • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
        • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
        • ISNERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
        • 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림
      • 대부분 클러스터링 인덱스의 장점은 빠른 읽기(SELECT)이며, 단점은 느린 쓰기(INSERT, UPDATE, DELETE)라는 것을 알 수 있음
      • 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경(OLTP, On-Line Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요함
  • 유니크 인덱스
    • 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미함
    • 인덱스 쓰기
      • 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요함
      • 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느림
      • MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번이 발생함
      • 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못함
  • 외래키
    • 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됨
    • InnoDB의 외래키 관리에 중요한 두 가지 특성
      • 테이블의 변경(쓰기 잠금)이 발상해는 경우에만 잠금 경합(잠금 대기)이 발생함
      • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않음

옵티마이저와 힌트

  • 어떤 DBMS든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있으며, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어려운 부분, 하지만 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있음
  • 쿼리 실행 절차
    • MySQL 서버에서 쿼리가 실행되는 과정 - 첫 번째 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리함
      • 사용자로부터 요청된 SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)함 - SQL 파싱(Parsing)이라고 하며, MySQL 서버의 SQL파서라는 모듈로 처리함
      • SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택함
        • 불필요한 조건 제거 및 복잡한 연산의 단순화
        • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
        • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
        • 가져온 레코드들은 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
        • 두 번째 단계는 최적화 및 실행 계획 수립 단계, MySQL 서버의 옵티마이저에서 처리함, 두 번째 단계가 완료되면 쿼리의 실행 계획이 만들어짐
      • 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴
        • 세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행함
  • 옵티마이저의 종류
    • 옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당함. 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO)방법
      • 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출함
      • 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행함
    • 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, RBO)
      • 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선수위에 따라 실행 계획을 수립하는 방식을 의미함.
      • 통계 정보(테이블의 레코드 건수나 칼럼값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 냄
      • 사용자의 데이터는 분포도가 매우 다양하기 때문에 규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않음
      • 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법
  • 기본 데이터 처리
    • 풀 테이블 스캔과 풀 인덱스 스캔
      • 풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미함
      • 다음과 같은 조건이 일치할 때 선택
        • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
        • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
        • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
      • 일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요함
      • InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작됨
        • 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미함
        • 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread, 클라이언트 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘김
  • 병렬 처리
    • 병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리한다는 것을 의미함
    • innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있음
    • 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에서는 오히려 성능이 떨어질 수도 있으니 주의
  • ORDER BY 처리(Using filesort)
    • 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort 라는 별도의 처리를 이용하는 방법
      • 인덱스 이용
        • 장점
          • INSERT,UPDATE,DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠름
        • 단점
          • INSERT,UPDATE,DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느림
          • 인덱스 때문에 디스크 공간이 더 많이 필요함
          • 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요함
      • Filesort 이용
        • 장점
          • 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀜
          • 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠름
        • 단점
          • 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 응답 속도가 느림
    • 다음과 같은 이유로 모든 정렬을 인덱스로 이용하도록 튜닝하기란 거의 불가능함
      • 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
      • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
      • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
      • 랜덤하게 결과 레코드를 가져와야 하는 경우
    • 소트 버퍼
      • MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort buffer)라고 함
      • 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있음
      • 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납됨
      • 멀티 머지(Multi-merge) : 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 함. 이 병합 작업을 표현함
      • 소트 버퍼를 크게 설정해서 빠른 성능을 얻을 수는 없지만 디스크의 일긱와 쓰기 사용량은 줄일 수 있음. MySQL 서버의 데이터가 많거나 디스크의 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이됨, 하지만 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 메모리 부족을 겪을 수도 있기 때문에 소트 버퍼의 크기는 적절히 설정하는 것이 좋음
    • 정렬 알고리즘
      • 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글 패스(Single-pass)와 투 패스(Two-pass) 2가지 정렬 모드로 나눌 수 있음
      • 싱글 패스 정렬 방식
        • 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식
      • 투 패스 정렬 방식
        • 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식
        • 대략 128KB의 정렬 버퍼를 사용한다면 이 쿼리는 투 패스 정렬 방식에서는 대략 7,000건의 레코드를 정렬할 수 있지만 싱글 패스 정렬 방식에서는 그것의 반 정도밖에 정렬할 수 없음
        • MySQL 서버는 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용함
          • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
          • BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때
    • 정렬 처리 방법
      • 쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리됨
        • 정렬 처리 방법 - 실행 계획의 Extra 칼럼 내용
          • 1.인덱스를 사용한 정렬 - 별도 표기 없음
            • 인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 함, WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할수 있어야 함
            • 인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 됨
          • 2.조인에서 드라이빙 테이블만 정렬 - Using filesort 메시지가 표시됨
            • 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 함
          • 3.조인에서 조인 결과를 임시 테이블로 저장 후 정렬 - Using temporary; Using filesort 메시지가 표시됨
            • 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시테이블이 필요할 수도 있음
            • 정렬의 3가지 방법 가운데 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법
      • MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 2가지 방법 중 하나를 선택함
        • 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
        • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
      • 일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적임
      • 정렬 처리 방법의 성능 비교
        • 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 함
        • ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼만 가져와서는 처리할 수 없음, 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 비로소 LIMIT으로 건수를 제한할 수 있음
        • WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생함
        • 스트리밍 방식
          • 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식을 의미함
          • 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받음
          • 웹 서비스 같은 OLTP 환경에서는 쿼리의 요청으로부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요함
        • 버퍼링 방식
          • ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트르밍되는 것은 불가능하게 함, 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문
          • MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려짐
          • 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 함
          • JDBC 라이브러리
            • JDBC는 MySQL 서버로부터 받는 레코드를 일단 내부 버퍼에 모두 담아둠. 마지막 레코드가 전달될 때까지 기다렸다가 모든 결과를 전달받으면 그때서야 비로소 클라이언트의 애플리케이션에 반환함
            • JDBC 라이브러리가 자체적으로 레코드를 버퍼링하는 이유는 전체 처리(Throughput) 시간이 짧고 MySQL 서버와으 통신 횟수가 적어 자원 소모가 줄어들기 때문
            • MySQL 서버는 데이터의 크기에 관계없이 무조건 보내고, JDBC MySQL 서버로부터 전송되는 데이터를 받아서 저장만 하므로 불필요한 네트워크 요청이 최소화하기 때문에 전체 처리량이 뛰어남
            • Order By의 3가지 처리 방법 가운데 인덱스를 사용한 정렬 방식만 스트르밍 형태의 처리이며 나머지는 모두 버퍼링된 후에 정렬됨, 인덱스를 사용한 정렬 방식은 LIMIT으로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있음
            • 조인과 함께 ORDER BY 절과 LIMIT 절이 사용될 경우 정렬 처리 방법 
              • SELECT * 
FROM tb_test1 t1, tb_test2 t2 -- test1 레코드 100건, test2 레코드 1000건, test1 레코드 1건당 test2의 레코드가 10건씩 존재한다고 가정 
WHERE t1.col1 = t2.col1
ORDER BY t1.col2
LIMIT 10;
              • test1이 드라이빙 되는 경우
              • 정렬 방법 - 읽어야 할 건수 - 조인 횟수 - 정렬해야 할 대상 건수
              • 인덱스 사용 - test1:1건, test2:10건 - 1번 - 0건
              • 조인의 드라이빙 테이블만 정렬 - test1:100건, test2:10건 - 1번 - 100건(test1 테이블의 레코드 건수 만큼 정렬 필요)
              • 임시 테이블 사용 후 정렬 - test1:100건, test2:1000건 - 100번(test1 테이블의 레코드 건수만큼 조인 발생) - 1000건(조인된 결과 레코드 건수를 정부 정렬해야 함)
            • 어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만듬, 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법
    • GROUP BY 처리
      • GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 Having 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없음
        • 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
          • ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리함
        • 루스 인덱스 스캔을 이용하는 GROUP BY
          • 루스(Loose) 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미함
          • 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있음
          • 프리픽스 인덱스(Prefix index, 칼럼값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없음
          • 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상됨, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어냄
          • 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않음
    • DISTINCT 처리
      • SELECT DISTINCT ...
        • 단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용함, GROUP BY와 동일한 방식으로 처리됨
        • DISNTCT는 SELECT하는 레코드(튜플)를 유니크하게 SELECT 하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아님
        • MySQL 서버는 DISTINCT 뒤의 괄화를 그냥 의미 없이 사용된 괄호로 해석하고 제거해 버림, DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것
        • SELECT 절에 사용된 DISTINCT키워드는 조회되는 모든 칼럼에 영향을 미침
      • 집합 함수와 함께 사용된 DISTINCT
        • COUNT() 또는 MIN(), MAX() 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있음, 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달ㄷ된 칼럼값이 유니크한 것들을 가져옴
    • 내부 임시 테이블 활용
      • MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑 할 때는 내부적인 임시테이블(Internal temporary table)을 사용함
      • 사용자가 생성한 임시테이블(CREATE TEMPORARY TABLE)과는 달리 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제됨
      • 메모리 임시 테이블과 디스크 임시 테이블
        • MySQL 8.0 버전부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하고 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됨
      • 임시 테이블이 필요한 쿼리
        • MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스
          • ORDER BY나 GROUP BY에 명시된 칼럼이 다른 쿼리
          • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
          • DISTINCT나 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
          • UNION 이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
          • 쿼리의 실행 계획에서 select_type이 DERIVED 쿼리
        • 어떤 쿼리의 실행 계획에서 임시 테이블을 사용하지는 Extra 칼럼에 Using temporary라는 메시지가 표시되는지 확인하면됨, Using temporary가 표시되지 않을 때는 1~3번이 해당함, 첫 번째부터 네 번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어짐. 마지막 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성, 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 처리 성능이 상당히 느림
      • 임시 테이블이 디스크에 생성되는 경우
        • 다음의 경우 디스크 기반의 임시 테이블을 사용함
          • UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
          • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
          • 메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우
      • 임시 테이블 관련 상태 변수
        • FLUSH STATUS 명령을 실행해 현재 세션의 상태 값을 초기화함
        • Created_tmp_tables : 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값, 이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지를 구분하지 않고 모두 누적함
        • Created_tmp_disk_tables : 디스크 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값
  • 고급 최적화
    • MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 됨. 옵티마이저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있음, 조인 관련된 옵티마이저 옵션은 MySQL 서버 초기 버전부터 제공되던 옵션이지만, 많은 사람이 그다지 신경 쓰지 않는 편
    • 옵티마이저 스위치는 MySQL 5.5 버전부터 지원되기 시작했는데, MySQL 서버의 고급 최적화 기능들을 활성화할지를 제어하는 용도로 사용됨
    • 옵티마이저 스위치 옵션
      • 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어하는데, optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용함
    • MRR과 배치 키 액세스(mrr & batched_key_access)
      • MRR은 Multi-Range Read, 매뉴얼에서는 DS-MRR(Disk Sweep Multi-Range Read)
      • 네스티드 루프 조인 : MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블(조인되는 테이블에서 드라이빙이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인을 수행하는 것
      • MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링함. 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것, 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청함. 이렇게 함으로써 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화할 수 있는 것
      • BKA(Batched Key Access) : MRR을 응용해서 실행되는 조인 방식
    • 블록 네스티드 루프 조인(block-nested_loop)
      • 네스티드 루프 조인(Nested Loop Join) : 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식
      • 네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이는 조인 버퍼(join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되냐
      • 조인 알고리즘에서 Block이라는 단어가 사용되면 조인용으로 별도의 버퍼가 사용됐다는 것을 의미함, 쿼리의 실행 계획에서 Extra 칼럼에 Using Join buffer라는 문구가 표시되면 그 실행 계획은 조인 버퍼를 사용한다는 것을 의미함
      • 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리됨, 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽는다는 것을 의미
      • 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느려지며, 옵티마이저는 최대한 드리븐 테이블의 검색 인덱스를 사용할 수 있게 실행 계획을 수립함
      • 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리함, 이때 사용되는 메모리의 캐시를 조인 버퍼(Join Buffer)
    • 인덱스 머지(index_merge)
      • 인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립함
      • 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리함
    • 인덱스 머지 - 교집합(index_merge_intersection)
    • 인덱스 머지 - 합집합(inddex_merge_union)
      • 인덱스 머지의 Using union은 where 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용하는 최적화
      • 우선순위 큐(Priority Queue) : 정렬된 두 집합의 결과를 하나씩 가져와 중복 제거를 수행할 때 사용된 알고리즘
      • 2개의 조건이 AND로 연결된 경우에는 두 조건 중 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 쿼리가 실행됨, 2개의 WHERE 조건이 OR 연산자로 연결된 경우에는 둘 중 하나라도 제대로 인덱스를 사용하지 못하면 항상 풀 테이블 스캔으로밖에 처리하지 못함
    • 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)
      • 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 MySQL 서버는 인덱스 머지 최적화의 Sort union 알고리즘을 사용함
    • 세미 조인(semijoin)
      • 다른 테이블과 실제 조인을 수행하지는 않고, 단지 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리
      • MySQL 서버 메뉴얼에서는 아래 최적화 전략들을 모아서 세미 조인 최적화라고 부름
        • Table Pull-out
          • 테이블 풀-아웃 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화
          • 서브쿼리 최적화가 되입되기 이전에 수동으로 쿼리르 튜닝하던 대표적인 방법
        • Duplicate Weed-out
          • 중복 제거는 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘
        • Fisrt Match
          • 퍼스트 매치 최적화 전략은 IN(subquery) 형태의 세미 조인을 EXIST(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행됨
        • Loose Scan
          • 인덱스를 사용하는 GROUP BY 최적화 방법에서 살펴본 Using index for group-by의 루스 인덱스 스캔과 비슷한 읽기 방식을 사용함
        • Materialization
          • 구체화 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미
          • 구체화는 쉽게 표현화면 내부 임시 테이블을 생성한다는 것을 의미함
      • Table Pull-out 최적화 전략은 사용 가능하면 항상 세미 조인보다 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않음, First Match와 Loose Scan 최적화 전략은 각각 firstmatch와 loosescan 옵티마이저 옵션으로 사용 여부를 결정할 수 있고, Duplicate Weed-out과 Materialization 최적화 전략은 materialization 옵티마이저 스위치로 사용 여부를 선택할 수 있음
    • 컨디션 팬아웃(condition_fanout_filter) : 조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미침
      • MySQL 옵티마이저가 실행 계획을 수립할 때 테이블이나 인덱스의 통계 정보만 사용하는 것이 아니라 다음의 순서대로 사용 가능한 방식을 선택함
        • 1.레인지 옵티마이저(Range optimizer)를 이용한 예측
        • 2.히스토그램을 이용한 예측
        • 3.인덱스 통계를 이용한 예측
        • 4.추측에 기반한 예측(Guesstimates)
    • 파생 테이블 머지(derived_merge)
      • 파생 테이블(Derived Table) : From 절에 사용된 서브쿼리
    • 스킵 스캔(skip_scan)
      • 인덱스의 핵심은 값이 정렬돼 있다는 거싱며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요함
      • 인덱스 스킵 스캔 최적화는 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능함
    • 해시 조인(hash_join)
      • MySQL 서버는 범용 RDBMS, 범용이라 함은 온라인 트랜잭션 처리를 위한 데이터베이스 서버를 지칭하는 것, 아마도 대용량 데이터 분석을 위해서 MySQL 서버를 사용하지는 않을 것
      • MySQL 서버는 주로 조인 조건의 칼럼이 인덱스가 없다거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설계돼어 있음,
      • MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책(Fallback strategy) 같은 기능으로 생각하는 것이 좋음
      • 빌드 단계(Build-phase)
        • 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성(빌드)하는 작업을 숳애함
        • 빌드 테이블 : 빌드 단계에서 해시 테이블을 만들 때 사용되는 원본 테이블
      • 프로브 단계(Probe-phase)
        • 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 의미함, 읽는 나머지 테이블을 프로브 테이블
      • 해시 조인(메모리에서 모두 처리가능한 경우) 경우에 클래식 해시 조인(Classic hash join) 알고리즘을 사용하고 해시 조인 1차 처리(해시 테이블이 조인 버퍼 메모리보다 큰 경우)의 경우에 그레이스 해시 조인(Grace hash join)알고리즘을 하이브리드(Hybrid)하게 활용하도록 구현되어 있음
  • 조인 최적화 알고리즘
    • Exhaustive 검색 알고리즘
      • MySQL 5.0과 그 이전 버전에서 사용되던 조인 최적화 기법, FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법
    • Greedy 검색 알고리즘
      • Exhaustive 검색 알곡리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 조인 최적화 기법
  • 쿼리 힌트
    • 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법, RDBMS에서는 이런 목적으로 힌트가 제공되며, MySQL에서도 다양한 옵티마이저 힌트를 제공함
    • MySQL 서버에서 사용 가능한 쿼리 힌트
      • 인덱스 힌트
        • STRAIGHT_JOIN과 USE_INDEX 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능
        • STRAIGHT_JOIN
          • 옵티마이저 힌트인 동시에 조인 키워드, SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 함
        • USE INDEX / FORCE INDEX / IGNORE INDEX
          • 조인의 순서를 변경하는 것 당므으로 자주 사용되는 것이 인덱스 힌트인데, STRAIGHT_JOIN 힌트와는 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 함
          • USE INDEX : 가장 자주 사용되는 인덱스 힌트로, MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트 정도로 생각하면 됨
          • FORCE INDEX : INDEX와 비교해서 다른 점은 없으며, USE_INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트로 생각하면 됨
          • IGNORE_INDEX : USE_INDEX나 FORCE INDEX와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트
        • SQL_CALC_FOUND_ROWS : SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우에는 LIMIT을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행함
      • 옵티마이저 힌트
        • 인데스 : 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
        • 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
        • 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
          • 하나의 SQL 문자에서 SELECT 키워드는 여러 번 사용될 수 있음, 이때 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록이라 함
        • 글로벌(쿼리 전체) : 전체 쿼리에 대해서만 영향을 미치는 힌트
        • 예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 항상 내부 임시테이블로 생성함, 이렇게 생성된 내부 임시테이블을 파생 테이블(Derived table), 이는 불필요한 자원 소모를 유발함

실행 계획

  • 대부분의 DBMS는 많은 데이터를 안전헤가 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는 것이 주목적, 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 하는 쿼리의 실행 계획을 수립할 수 있어야 함
  • 통계 정보
    • MySQL 8.0 버전부터는 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램(Histogram) 정보가 도입됨
    • 테이블 및 인덱스 통계 정보
      • 비용 기반 최적화에서 가장 중요한 것은 통계 정보
    • 히스토 그램
      • Singleton(싱글톤 히스토그램) : 칼럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불림
      • Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로, Height-Balanced 히스토그램이라고 불림
      • 히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리되는데, 싱글톤 히스토그램은 칼럼이 가지는 값별로 버킷이 할당되며 높이 균형 히스토그램에서는 개수가 균등한 칼럼값의 범위별로 하나의 버킷에 할당됨
    • 인덱스 다이브(Index Dive) : 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴봄
    • 코스트 모델(Cost Model)
      • MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 함
        • 디스크로부터 데이터 페이지 읽기
        • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
        • 인덱스 키 비교
        • 레코드 평가
        • 메모리 임시 테이블 작업
        • 디스크 임시 테이블 작업
      • MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾음
      • 코스트 모델 : 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업을의 비용
  • 실행 계획 확인
    • MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있음
EXPALIN ANALYZE
SELECT e.emp_no, avg(s.salary)
FROM employees e
  INNER JOIN salaries s ON s.emp_no = e.emp_no
        AND s.salary > 50000
        AND s.from_date <= '1990-01-01'
        AND s.to_date > '1990-01-01'
WHERE e.first_name = 'Matt'
GROUP BY e.hire_date \G

-- A) -> Table scan on <temporary> (actual time=0.001..0.004 rows=48, loops= 1)
-- B)   -> Aggregate using temporary tabel (actual time=3.799...3.808 rows=48, loops=1)
-- C)     -> Nested loop inner join (cost=685.24 rows = 135)
--                        (actual time = 0.367 ...3.602 rows=48 loops=1)
-- D)       -> Index lookup on e using ix_firstname (first_name='Matt') (cost=215.08 rows=233)
--                        (actual time = 0.348..1.046 rows=233 loops=1)
-- E)       -> Filter: ((s.salary > 50000) and (s.from_date <= DATE '1990-01-01') and (s.to_date > DATE'1990-01-01)) (cost=0.98 rows 1)
--                        (actual time = 0.009..0.011 rows = 0 loops = 233)
-- F)         -> Index lookup on s using PRIMARY (emp_no = e.emp_no) (cost=0.98 rows=10)  
--                        (actual time = 0.007..0.009 rows=10 loops=233)
  • TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며 실제 실행 순서는 다음 기준으로 읽으면 됨
    • 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
    • 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
    • 실행 순서
      • D) Index lookup on e using ix_firstname
      • F) Index lookup on s using PRIMARY
      • E) Filter
      • C) Nested loop inner join
      • B) Aggregate using temporary tabel
      • A) Table scan on
  • EXPLAIN ANALYZE 명령의 결과에는 단계별로 실제 소요된 시간 (actual time)과 처리한 레코드 건수(rows), 반복 횟수(loops)가 표시됨
  • SUBQUERY
    • 서브쿼리는 사용하는 위치에 따라 각각 다른 이름을 지니고 있음
      • 중첩된 쿼리(Nested Query) : SELECT 되는 칼럼에 사용된 서브쿼리를 네스티드 쿼리라고 함
      • 서브쿼리(Subqyery) : WHERE 절에 사용된 경우에는 일반적으로 그냥 서브쿼리라고 함
      • 파생 테이블(Derived Table) : FROM 절에 사용된 서브쿼리를 MySQL에서는 파생 테이블이라고 하며, 일반적으로 RDBMS에서는 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부름
    • 서브쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분함
      • 스칼라 서브쿼리(Scalar Subquery) : 하나의 값만(칼럼이 단 하나인 레코드 1건만) 반환하는 쿼리
      • 로우 서브쿼리(Row Subquery) : 칼럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리
  • DERIVED
    • MySQL 5.5 버전까지는 서브쿼리가 FROM 절에 사용된 경우 항상 select_type이 DERIVED인 실행 계획을 만듬
    • MySQL 5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 함
    • 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 함, 서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장함
  • partitions 칼럼
    • 파티션 생성 시 제약 사항(파티션 키로 사용되는 칼럼은 프라이머리 키를 포함한 모든 유니크 인덱스의 일부여야 함)으로 인해 프라이머리 키에 emp_no칼럼과 함께 hire_date 칼럼을 추가해서 테이블을 생성했음
    • create table `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) engine=innodb default charset=utf8mb4
partition by range(year(hiredate)) (
partition p0 values less than(2011) engine=innodb,
partition p1 values less than(2012) engine=innodb,
partition p2 values less than(2013) engine=innodb,
partition p3 values less than(2014) engine=innodb,
partition p999 values less than maxvalue engine=innodb);
    • 파티션 프루닝(Partition pruning) : 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정
    • MySQL을 포함한 대부분의 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문
  • type 칼럼
    • 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야 할 중요한 정보
    • ALL을 제외한 나머지는 모두 인덱스를 사용한 접근 방법, ALL은 인덱스를 사용하지 않고, 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근방법, 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있음, index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용함
    • 실행 계획의 type 칼럼에 표시될 수 있는 값
      • system
        • 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법
        • 이 접근 방법은 InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법
      • const
        • 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식
        • 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)
        • 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)
      • eq_ref
        • 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됨, 조인에서 처음 읽은 테이블의 칼럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할때를 가리켜 eq_ref라고 함
        • 조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
      • ref
        • eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키등의 제약 조건도 없음
        • 조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)
      • fulltext
        • MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미함
      • ref_or_null
        • ref 접근 방법과 같은데, NULL 비교가 추가된 형태
      • unique_subquery
        • WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법, unique_subquery의 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용함
      • index_subquery
        • IN 연산자의 특성상 IN(subquery) 또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 함
        • 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용됨
          • unique_subquery : IN (subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음
          • index_subquery : IN (subquery) 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음
      • range
        • 인덱스 레인지 스캔 형태의 접근 방법, range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미, 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용됨
        • 인덱스 레인지 스캔이라고 하면 const,ref,range라는 세 가지 접근 방법을 모두 묶어서 지칭하는 것 , 인덱스를 효율적으로 사용한다, 작업 범위 결정 조건으로 인덱스를 사용한다라는 표현 모두 이 세가지 접근 방법을 의미함
      • index_merge
        • index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식
        • 특징
          • 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어짐
          • 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다
          • index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요함
      • index
        • index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미함
        • 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적
        • (첫 번째 + 두 번째) 조건을 충족하거나 (첫 번째 + 세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식
          • range나 const,ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
          • 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우)
          • 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)
      • ALL
        • 풀 테이블 스캔을 의미, 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때) 하고 반환함, 풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법
        • 리드 어헤드(Read Ahead) : 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공함
  • Recursive
    • CTE(Common Table Expression)를 이용해 재귀 쿼리를 작성할 수 있음, MySQL 서버에서는 재귀 쿼리는 WITH 구문을 이용해 CTE를 사용하면 됨
    • WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
    • n이라는 칼럼 하나를 가진 cte라는 이름의 내부 임시 테이블을 생성
    • n칼럼의 값이 1부터 5까지 1씩 증가하게 해서 레코드 5건을 만들어서 cte 내부 임시 테이블에 저장
  • Rematerialize
    • 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장함, 이 과정을 Rematerializing
  • Using index(커버링 인덱스)
    • 인덱스만으로 쿼리를 수행할 수 있을 때 실행 계획의 Extra 칼럼에는 Using index라는 메시지가 출력됨, 인덱스만으로 처리되는 것을 커버링 인덱스(Covering index)
    • Using intersect(...) : 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과를 교집합을 추출해내는 작업을 수행했다는 의미
    • Using union(...) : 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미
    • Using sort_union(...) : Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로 대량의 range 조건들) 이 방식으로 처리됨. Using sort_union과 Using union의 차이점은 Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환할 수 있다는 것
    • Using temporary : MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용함