RealMySql 8.0

MySQL 아키텍처

MySQL 엔진 관련

기본

• 핸들러, 엔진, 스토리지엔진, 하드웨어로 이루어짐
• 스레딩: 포어그라운드 스레드(클라이언트 스레드)와 백그라운드 스레드
  • MyISAM은 클라이언트스레드가 쓰기 작업까지
• 메모리: 글로벌영역과 클라이언트 로컬
  • 글로벌 : 테이블 캐시, 버퍼풀, 해시인덱스, 리두로그 버퍼
  • 로컬 : 커넥션, 정렬버퍼, 조인 버퍼

각 하위 작업이 MySQL 엔진 영역에서 처리되는지, 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야 한다.

쿼리 실행 구조

SQL 요청 -> (MySQL 엔진에서) 쿼리파서 -> 전처리기 -> 옵티마이저(쿼리 변환, 비용 최적화, 실행 계획 수립) -> 쿼리실행기 (스토리지 엔진에서 실행)

• 쿼리 파서
  • 사용자 요청 쿼리를 토큰으로 분리해 트리구조로 만듦, 문법체크도 여기서
• 전처리기
  • 문장의 구조적인 문제점 파악, 객체 존재여부나 접근권한 체크도 여기서
• 옵티마이저
  • 가장 저렴한 비용의 실행계획 결정

이 책에서는 옵티마이저가 선택하는 내용을 설명할 것이며, 어떻게 하면 옵티마이저가 더 나은 선택을 할 수 있게 유도하는가를 알려줄 것이다.

실행 엔진

만들어진 계획대로 각 핸들러에서 받은 요청을 다른 핸들러 요청의 입력으로 연결하는 역할

그 외

각각의 세부 주제로 잡는게 좋은 것 같다. 무튼 아래와 같은 정보들이 엔진에서 지원한다.

• 복제
• 쿼리 캐시
• 스레드 풀
• 트랜잭션 지원 메타데이터

InnoDB 스토리지 엔진 아키텍처

거의 유일하게 레코드 기반의 잠금을 제공한다.

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

• 프라이머리 키 값의 순서대로 디스크에 저장됨
• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용(MyISAM에서는 실제 물리적인 주소)

외래 키 지원

• 실제 엔진레벨에서의 외래키 제약 조건에 대한 지원을 함

MVCC

일반적으로 레코드 레벨의 트랜잭션을 지원 하는 dbms가 제공하는 기능이며, 잠금을 사용하지 않는 일관된 읽기를 제공하는데 목적이 있따.

• 언두로그를 통해 지원
• multi version이란 하나의 레코드에 대해 여러개의 버전이 동시 관리된다는 것
• 구문 실행과 동시에 (커밋 여부와 관련 없이) innodb버퍼 풀에 반영, 디스크에는 반영여부가 확실치 않음
• 이 시점에서의 조회는 격리레벨에 따라 다르다. 크게 살펴보면
• READ_UNCOMMITED : 버퍼에서 반환한다.
• READ_COMMITED 이상의 격리 레벨 : 커밋 전이면 언두로그에서 반환한다.
• 즉 innodb버퍼풀-디스크, 언두로그까지 멀티버전이 있는 것

잠금 없는 일관된 읽기 (Non-Locking Consitent Read)

• 간단하게 요약하면 읽기에 대한 락을 걸지 않고, 격리레벨설정에 따라서, 읽는 시점에 적절한 데이터를 읽어감

자동 데드락 감지

• 잠금 대기 목록을 그래프 형태로 관리
• 별도의 스레드가 해당 목록을 토대로 데드락을 감지
• 교착상태의 트랜잭션중 하나를 종료
• 트랜잭션 언두 로그를 기준으로(경제적인 관점에서) 강제종료할 트랜잭션을 선택
• 참고로 상위 레이어의 mysql엔진에서 테이블잠금이 된경우 데드락 판단이 불확실 할 수 있는데, 시스템 변수로 방지할수있음 innodb_table_locks
• 기본적으로 데드락 감지 스레드 자체의 부하나 리소스가 크지 않지만, 트랜잭션이 많아지면 부담이됨, 그리고 심지어는 위의 잠금 대기 목록에도 락을 걸기에, 다른 서비스 쿼리의 트랜잭션에도 지연을 유발함.
• 그래서 innodb_deadlock_detect를 끄고, innodb_lock_wait_timeout을 통해서 락시간을 통해 트랜잭션을 언두하는 방식으로 운영하기도 함.
  • 책에서 나온 내용은 아니지만, 아마도 교착상태의 트랜잭션을 경제적으로 종료하는것은 아니라, 결과적인 부화는 더 안좋을 수 있을것 같다. (상황에 따라서)

자동화된 장애 복구

• 일반적으로 매우 견고한 장애복구 시스템이 있음
• innodb_force_recovery : 장애가 나서 시작이 안되는경우 사용
• 위의 변수 설정으로 시작하면, 가능한 만큼 덤프로 데이터를 백업하고, 그 데이터로 테이블 다시 생성하는것이 좋다.
  • 1=SRV_FORCE_IGNORE_CORRUPT : 인덱스나 테이블의 손상을 무시하고 시작
  • 2=SRV_FORCE_NO_BACKUP : 메인스레드를 시작하지 않고 서버 시작, 메인스레드가 언두데이터 삭제하는과정에서 장애가 반복된다면 이거 사용
  • 3=SRV_FORCE_NO_TRX_UNDO : 커밋되지 않고 종료된 트랜잭션의 롤백을 막음
  • 4=SRV_FORCE_NO_IBUF_MERGE : 인서트 버퍼의 내용 무시하고 시작
  • 5=SRV_FORCE_NO_UNDO_LOG_SCAN : 언두로그를 스캔하지 않고, 장애시점의 트랜잭션을 커밋한것처럼 동작
  • 6=SRV_FORCE_NO_LOG_REDO : 마지막 체크포인트로 시작

이래도 안된다면, db백업과 바이너리를 통해서..

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분, 디스크 데이터파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다. 쓰기작업을 지연시켜 일괄 작업으로 처리해주는 버퍼 역할도 같이 한다.

버퍼 풀의 크기 설정

• os의 80퍼센트를 할당하라는 가이드라인은 잘못됨
• 경우에 따라서 레코드 버퍼의 메모리 사용될 수 있음
• 128mb의 청크를 단위로 조정 가능

버퍼 풀의 구조

거대한 메모리를 페이지크기로 쪼개서 관리한다. 크게 LRU 리스트, Flush 리스트, Free 리스트를 관리한다.

• LRU 리스트
  • 조금 더 정확하게는, LRU와 MRU 리스트가 결합된 형태
  • 각각 New 서브리스트와(MRU), Old 서브리스트(LRU)로 관리한다.
  • 새로운 페이지는 New 리스트의 tail과 Old 리스트의 head의 접점에 삽입된다.
  • 디스크 읽기를 최소화 하기 위해서 사용된다.
• InnoDB의 데이터 찾는 과정
  1. 필요한 레코드가 저장된 페이지가 버퍼 풀에 있는지 검사. (InnoDB 어댑티브 해시 인덱스로 페이지 찾기, 해당 테이블의 인덱스를 이용해 버퍼 풀에서 페이지 검색, 버퍼 풀에서 이미 페이지가 있다면 해당 페이지의 포인터를 New서브리스트 방향으로 승급)
  2. 디스크에서 필요한 데이터페이지를 버퍼 풀에 적재, 적재한 이후에 LRU 헤드 부분에 추가
  3. 버퍼 풀에 상주하는 페이지는 최근 접근 시간을 기준으로 age가 부여됨
  4. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
  5. 즉 자주 사용됐다면 MRU영역에서 오래 살아남고, 반대로는 LRU끝으로 밀려나서 메모리에서 해제됨
• flush 리스트
  • 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지의 변경 시점 기준의 페이지 목록을 관리.
  • 읽은 상태에서 수정이 있다면 플러시 리스트에 오르고, 관리되다가 디스크에 기록됨
  • 변경이 되어 페이지의 데이터가 변경 되면 아까 본 리두 로그에 기록하는 (변경을) 과정이 일어남.
  • 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는것을 항상 보장하지는 않음
• free 리스트
  • 실제 데이터로 채워지지 않는 여유공간
  • 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야하는 경우 사용됨

버퍼 풀과 리두 로그

• 논리적으로 디스크의 모든 데이터를 메모리에 올릴 수 있는 상황이 아니라면, 버퍼풀을 늘리면 쿼리 성능은 빨라짐
• 근본적으로 따져보면 InnoDB 버퍼 풀은 성능 향상을 위해 두가지 관점으로 접근함 데이터 캐시, 버퍼링
• 버퍼풀 늘리는건 전자에 관여
• 버퍼링을 늘리려면 버퍼풀과 리두 로그의 관계를 알아야 한다고 한다.
  • 버퍼풀은 클린페이지와 더티페이지(디스크에서 읽어온 이후 변경이 발생한 페이지) 모두를 가지고 있다.
  • 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용
  • 데이터 변경이 계속 발생하면 리두 로그파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어씌워짐
  • 그래서 리두 로그파일에서 재사용가능한 공간과 불가능한 공간을 구분해서 관리해야하는데, 불가능한공간을 활성 리두 로그라고 한다.
  • 기본적으로 리두 로그 파일은 순환되어 재사용되는데, 매 기록시마다 로그포지션이 증가 이것을 LSN이라고 한다.
  • 그리고 InnoDB 스토리지 엔진은 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼풀의 더티페이지를 디스크로 동기화하는데
  • 발생한 체크포인트 중 가장 최근 체크포인트 지점의 LSN이 활성 리두 로그 공간의 시작점
  • 하지만 활성 리두 로그 공간의 마지막은 계속해서 증가하기에 체크포인트와 무관
  • 그래서 가장 최근 체크포인트의 lsn과 마지막 리두 로그 엔트리의 lsn의 차이를 체크포인트에이지라고 함
  • 결론적으로 리두 로그 파일 크기가 적다면 너무 적은 더티 페이지의 제한때문에 쓰기가 자주일어나게 되고, 이러면 버퍼풀의 물리적인 메모리가 높아봐야 쓰기 버퍼링의 효과는 전혀 없다.
  • 반대로 리두 로그 파일 크기가 전체 버퍼풀에 비해 터무니 없이 높다면 단 한 순간에 너무 큰 디스크 쓰기가 일어나게 된다.

버퍼 풀 플러시

• 더티페이지들의 디스크 동기화를 위해 다음과 같은 두가지의 기능을 백그라운드에서 실행하다.
• 플러시 리스트 플러시
  • 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야한다.
  • 그러려면 당연히 더티페이지가 먼저 디스크로 동기화 돼야 한다.
  • 그러기 위해서 flush list flush 함수를 호출해서 이를 수행한다.
  • innodb_page_cleaners : 더티페이지를 디스크로 동기화하는 클리너 스레드의 갯수
  • innodb_buffer_pool_instances : 여러개의 버퍼풀 인스턴스를 동시에 사용하는데 그 갯수 여기서 언급된 것은 클리너 스레드와 갯수를 자동적으로 맞춰주는게 디폴트로 되어있으며 굳이 변경할 이유가 없다는 언급과 함께
  • innodb_max_dirty_pages_pct : 버퍼풀에서 더티페이지가 차지할 수 있는 비율
  • innodb_max_dirty_pages_lwm : 쓰기 폭발 (Disk IO Burst)을 대비하여 특정 비율 이상인경우 주기적으로 쓰기를 하도록 설정하는 퍼센테이지
  • 그외에도 capacity와 같은 값들이 있는데, 사실 여기 나머지 세세한 설정들은 hdd를 쓰던 시절 치명적이던 disk io를 줄이려고 한 노력에 더 가까워서 정말 예민하지 않으면 안 건들어도 될것 같다.
• LRU 리스트 플러시
  • 프리페이지로 옮긴다, 정도 제외하곤 큰 차이 없다.

버퍼 풀 상태 백업 및 복구

• 잘 메모리 즉 버퍼풀에 올라간상태(warmed up)의 쿼리는 그렇지 않은 경우 대비 수십배 빠름
• 예전에는 이부분에 대한 관리가 잘 안되어 서비스전 풀테이블 스캔을 유발시켜서 워밍업을 매뉴얼하게 수행했음
• 지금은 그럴 필요 없이 버퍼 풀 덤프를 통해서 재실행되어도 버퍼가 채워져 있도록 관리

버퍼 풀의 적재 내용 확인

• information schema -> innodb_buffer_page 테이블에서 버퍼풀 확인가능

select
	it.name table_name,
	ii.name index_name,
	ici.n_cached_pages n_cached_pages
from information_schema.innodb_tables it
	inner join information_schema.innodb_indexes ii on ii.table_id = it.table_id
	inner join information_schema.innodb_cached_indexes ici on ici.index_id = ii.index_id
where it.name=concat('employees','/','employess')
;

언두 로그

• 주 사용처는 위에서 언급한 것처럼 트랜잭션보장과 격리수준 보장
• 쿼리 실행 시점에 작성된다.(트랜잭션을 위해)

언두 로그 레코드 모니터링

• 일단 관리에 비용이 많이 든다. 1억건의 데이터를 날리거나 변경하는 dml이면?
• 그리고 트랜잭션의 관리가 안된다면, 언두로그가 다른트랜잭션의 영향을 받아 언두로그가 삭제가 안 될 수 있다.
• 트랜잭션 A가 실행되는 동안 B와 C가 dml 트랜잭션을 날린다면, b와 c의 Undo 로그는 삭제되지 않음.
• 이유는 트랜잭션 A가 여전히 해당 데이터를 읽을 가능성이 있기 때문.
• 8.0 이후 언두로그가 개선되어 언두로그를 순차적으로 사용하며 디스크 공간을 줄이는게 가능해졌음
• 참고로 테이블스페이스 인 만큼 이노디비 버퍼풀에 올라가기도 하지만 로그자체는 디스크

체인지 버퍼

• 체인지 버퍼는 인덱스 페이지의 변경 내용을 디스크가 아닌 메모리에 임시 저장하는 공간  
• InnoDB 버퍼 풀(Buffer Pool)의 일부로 관리되며 보조 인덱스(Secondary Index) 변경 사항을 저장  
• 데이터 페이지를 즉시 디스크에 기록하지 않고 변경 사항을 누적하여 성능 최적화  
• 데이터가 조회될 때 디스크와 동기화(merge)되며, 일정 주기마다 백그라운드 스레드가 자동으로 병합  
• 동작 예시
  1. 사용자가 보조 인덱스에 대한 DML(INSERT, DELETE, UPDATE)을 실행  
  2. 변경된 인덱스 페이지가 버퍼 풀에 없다면, 체인지 버퍼에 변경 사항을 기록  
  3. 이후 해당 인덱스 페이지가 조회되면 체인지 버퍼에서 변경 사항을 적용(merge)  
  4. 백그라운드 스레드가 일정 시간마다 디스크에 반영  
• innodb_change_buffer_max_size 설정을 통해 크기 조절 가능  
• 트랜잭션이 롤백되면 체인지 버퍼도 롤백됨  
• 기본 키(Primary Key)에는 적용되지 않으며 보조 인덱스에만 사용  
• 자주 변경되는 보조 인덱스가 많은 테이블에서 성능 최적화 효과가 큼

리두 로그

• 리두 로그는 트랜잭션 변경 내용을 디스크에 영구 반영하기 전에 로그로 기록하는 구조

• 시스템 장애 발생 시 리두 로그를 이용해 미완료된 변경 사항을 복구할 수 있음

• InnoDB는 변경 사항을 먼저 로그 파일에 기록한 후 데이터 페이지에 반영

• 리두 로그를 통해 MySQL이 ACID 특성을 보장하며, COMMIT된 데이터의 영속성을 유지

• 사용자가 DML(INSERT, UPDATE, DELETE)을 실행하면 변경된 데이터가 InnoDB 버퍼 풀에 반영됨

• 변경된 내용이 리두 로그 버퍼에 기록된 후, 일정 조건이 충족되거나 COMMIT 발생 시 리두 로그 버퍼의 내용을 디스크에 순차적으로 기록

• 이후 InnoDB의 백그라운드 플러시 스레드가 변경된 데이터를 실제 데이터 파일로 반영

• 시스템 장애 발생 시 리두 로그를 사용하여 변경 사항을 복구

• 리두 로그 파일은 ib_logfile0, ib_logfile1 등의 파일로 저장되며 순환 방식으로 동작

• 특정 크기에 도달하면 덮어쓰기 방식으로 재사용

• innodb_log_files_in_group 설정을 통해 다중 로그 파일을 구성할 수 있음

• innodb_log_file_size는 각 리두 로그 파일의 크기를 설정하며, 크기가 너무 작으면 체크포인트가 자주 발생하여 성능이 저하될 수 있음

• 크기가 너무 크면 복구 시간이 길어질 수 있음

• innodb_flush_log_at_trx_commit 값에 따라 리두 로그의 디스크 반영 시점이 조정됨

• 0으로 설정하면 1초마다 리두 로그를 디스크에 기록하여 성능이 향상되지만 장애 발생 시 데이터가 유실될 수 있음

• 1은 매 트랜잭션 COMMIT 시 리두 로그를 디스크에 기록하여 안정성을 보장함

• 2는 매 COMMIT 시 OS 버퍼에 기록하고, 1초마다 디스크에 기록하여 성능과 안정성의 균형을 맞춤

• WAL 기법을 사용하여 데이터를 변경하기 전에 먼저 로그를 기록하여 장애 발생 시 복구가 가능함

• 로그 버퍼는 리두 로그가 먼저 저장되는 메모리 공간으로 이후 디스크로 플러시됨

• 체크포인트는 리두 로그 크기 초과를 방지하기 위해 특정 시점의 변경 사항을 데이터 파일에 반영하는 과정

• 리두 로그 파일은 순환 구조이므로 사용된 로그는 체크포인트 이후 덮어쓰기됨

• 트랜잭션 안전성을 보장하며 장애 발생 시 빠른 복구가 가능함

• 데이터 페이지 변경이 비동기적으로 디스크에 기록되므로 성능이 향상됨

• 순차적인 로그 쓰기로 인해 디스크 I/O 부하가 감소함

• 리두 로그가 가득 차면 새로운 트랜잭션 처리가 지연될 수 있으므로 적절한 크기 설정이 필요함

어댑티브 해시 인덱스

• 자주 사용되는 페이지에 innodb엔진이 직접 생성하는 인덱스
• b+ 트리는 종단노드까지 가야 레코드가 있으니까 이걸 그냥 해쉬로 최적화시킴
• 해쉬값의 키로는 인덱스 고유값 + 인덱스 실제 키값 을 씀
• 예전 버전까지는 어댑티브 해쉬 인덱스는 하나의 메모리 객체인 이유로 어댑티브 해시 인덱스의 경합이 심했다 그래서 8.0부터는 내부 잠금 경합을 줄이기위해 어댑티브 해쉬 인덱스의 파티션기능을제공한다 (대충 이것도 하나라 경합이 심했는데 파티션을 해준다는 이야기)
• 도움 잘되는 경우
  • 디스크데이터가 버퍼풀이랑 비슷한경우
  • 동등조건 검색동등비교와 in연산 많은경우
  • 쿼리가 데이터중에서 일부데이터에 집중되는경우
• 도움 안되는 경우
  • 디스크읽기가 많은 경우
  • 특정패턴의 쿼리가 많은경우 (join like)
  • 매우 큰 데이터를 가진 테이블레ㅔ코드를 폭넓게 읽는 경우

트랜잭션과 잠금

잠금과 트랜잭션은 서로 비슷한 개념 같지만, 사실 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.

MySQL 엔진의 잠금

글로벌 락

FLUSH TABLES WITH READ LOCK으로 획득

• MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
• 서버 전체에 영향을 미치며, 테이블은 물론 데이터베이스가 달라도 영향을 미친다.
• sqldump수준으로 백업을 할때나 사용하고 그마저도 8.0에서는 백업락을 사용
  • 백업락의 경우 객체 db/table등 모든 객체 생성 변경은 막히고, 유저관련도 막히지만 일반적인 테이블의 데이터 변경은 가능하다.

테이블 락

LOCK TABLES table_name [read | write] 로 획득

• 마찬가지로 특별한 상황이 아니면, 어플리케이션에서 사용할일 없다.
• DDL에 잠깐 걸림

네임드 락

• 말그대로 특정 문자열에대한 락을 획득하는것
• 여러 클라이언트의 경쟁조건이나 데이터 동기화에 쓰이기도함

메타데이터 락

• 명시적이지 않음

InnoDB 스토리지 엔진 잠금

• 레코드 기반 락을 지원
• 원래는 잠금 정보를 얻는게 까다로웠는데, information_schema 데이터베이스의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 확인가능
• 최근에는 Performance Schema를 이용해서 모니터링도 가능

레코드 락

• 다른 디비의 레코드락이랑 거의 똑같지만, InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
• 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 사용한다.

갭 락

• MySQL만의 특수한 락
• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격을 잠금
• 넥스터 키 락과 같이 설명되어야함

넥스트 키 락

• 레코드락과 갭락을 합쳐놓은 형태
• STATEMENT 포맷의 바이너리 로그를 사용하기 위해서 쓴다고함
• 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때, 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는것이 주목적
• 근데 데드락이 은근 걸려서 그냥 ROW 포맷의 로그를 쓰기를 권한다.
• 전체적인설명

AUTO_INCREMENT 락

• 테이블락이었다.
• 명시적으로 얻을 방법은 없다.(postgres 최고..)

인덱스와 잠금

• 인덱스 기반 락이기 때문에, 업데이트되는 레코드가 인덱스가 안걸려 있다면, 해당하는 모든 데이터가 락이걸린다. 풀테이블 스캔을 하면 테이블락이 걸린다.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    INDEX idx_customer (customer_id) -- customer_id에 인덱스 추가
) ENGINE=InnoDB;

  

INSERT INTO orders (customer_id, amount, status) VALUES
(1, 100.00, 'pending'),
(1, 200.00, 'pending'),
(1, 300.00, 'pending'),
(2, 400.00, 'pending'), 
(2, 500.00, 'pending');

BEGIN;
UPDATE orders SET status = 'completed' WHERE customer_id = 1 AND amount = 300;

-- customer id 1인 주문 rows 다잠김

레코드 수준의 잠금 확인 및 해제

information_schema는 deprecated 되는중 performance_schema의 data_locks, data_lock_waits를 위주로 사용할 것

조회 예시는 [[MySQL-Record-Lock-Queries]] 여기에 별도 정리

MySQL의 격리 수준

• Dirty Read: 다른 트랜잭션에서 아직 커밋되지 않은 데이터를 읽는 것
• Non-repeatable Read: 동일한 트랜잭션 내에서 같은 데이터를 읽었을 때 다른 값이 반환되는 것
• Phantom Read: 동일한 트랜잭션 내에서 새로운 데이터가 삽입되거나 삭제되는 것이 감지되는 현상
• 아래로 갈수록 격리(고립) 정도가 높아지며, 동시처리 성능이 떨어진다.
• SQL표준 상 REPEATABLE READ에서 Pantom read는 발생할수 있지만, MySQL특성상 발생하지 않는다.

일반적인 온라인 서비스 용도의 데이터베이스는 주로 READ COMMITTED, REPEATABLE READ 중 하나를 사용한다.

READ_UNCOMMITTED

• Dirty Read가 발생함.
• 트랜잭션 격리 수준으로 인정하지 않을정도로 정합성에 문제가 많음.

READ COMMITTED

• 요점은 다른 트랜잭션이 진행중이면, 언두로그에서 레코드를 읽어간다는 것.
• ‘일반적인’온라인 서비스에서 가장 많이 사용되는 트랜잭션 레벨
• Non-repeatable-read는 발생함
  • 참고로 READ_COMMITTED, REPEATABLE READ 에서의 가장 큰 차이점은 select
  • READ_COMMITED는 한 트랜잭션 진행중에 다른트랜이 커밋을 성공하면 그 트랜잭션은 동일한 select를 보장받지 못한다.

REPEATABLE READ

• READ COMMITTED와의 차이점은, 언두영역에 백업된 레코드의 몇번째 이전 버전으로 찾아들어가냐에 있다.
• 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼있다. 그리고 언두영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
• REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 가 없다.
• 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한것은 아니다.
• 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두데이터가 보존돼야 한다.

1. A 트랜잭션이 100번으로 시작
2. B 트랜잭션이 101번으로 A가 보고있는 레코드를 변경하고 커밋
3. C 트랜잭션이 102번으로 동일한 데이터를 변경하고 커밋

이 상황에서 READ COMMITTED는 B,C 트랜잭션의 커밋과 함께 해당 레코드의 언두로그를 전부 삭제 할 수 '있지만',
REPEATABLE READ에서는 100번이 실행중인동안은 언두로그를 100번이전으로 삭제를 못하게 막아준다는 이야기!

SERIALIZABLE

• 가장 단순한 격리수준이자, 가장 엄격한 격리수준
• 동시 처리 작업은 떨어짐
• 현실성도 잘 없지만, 그나마 고려하는 이유가 pantom read인데 mysql에서는 그것 조차도 방지돼서 사용할 이유가 사실상 없음

데이터 압축

페이지압축

• 서버가 디스크에 저장하는 시점에 압축하고, 불러오면 압축이 해제됨 즉 버퍼풀에 데이터 페이지가 적재되면 압축이 해제된 상태로만 관리
• 운영체제와 하드웨어에서 둘 다 지원해야 유의미함
• 그래서 잘 사용안함

테이블압축

• 일반적으로 활용도 높음
• 아래와 같은 단점도 있음
  • 버퍼풀 공간 활용률이 낮음
  • 쿼리처리 성능이 낮음
  • 빈번한 데이터 변경시 압축률이 떨어짐
• 페이지 크기에 따라서 페이지 타겟(압축 목표 크기)를 설정하는데, 압축 결과가 다를수 있고 실제로 타겟보다 크게되면 스플릿을 한다.
• 테스트를 잘 해보고 설정해야함
  • 예를들어 타겟이 4kb, 8kb인데 실제 결과 차이가 별로 안난다면 8kb

기본적으로 테이블압축에서는 InnoDB 버퍼풀로올리면, 압축된페이지와 해제된 페이지 모두를 관리해야하고, 압축 자체가 cpu소모가 엄청나기에 신중하게 사용해야할것

LZ77, Huffman Encoding을 사용, 관련 내용 정리 -> [[데이터-압축]]

데이터 암호화

• 다른 부분에서는 암호화가 되지 않고 디스크 I/O 즉 쓰기, 읽기시에만 암호화 복호화가 이루어져 사용자는 신경을 쓸 필요는 없음
• master key와 tablespace key(private key)라는 두 종류의 키를 이용
• 외부 키 관리 솔루션 혹은 디스크의 파일에서 마스터키를 가져오고, 암호화된 테이블이 생성될 때 마다 해당 테이블을 위한 임의의 테이블 스페이스 키를 발급.
• 마스터키의 관리를 유의해야함, 마스터키가 변경되면 기존 키로 테이블스페이스키를 복호화 하고 다시 새로운 키로 암호화함
• 이런 구현때문에 기본적으로 꼭 필요한 경우가 아니면 db기능을 이용하기 위해서 db암호화를 하는게 나음(어플리케이션 암호화보다)
• 바이너리 로그나 언두로그는 평문으로 저장될 수 있어 설정에 유의

인덱스

디스크 읽기 방식

Random I/O와 Sequential I/O정리, 데이터베이스나 쿼리튜닝에 어느 정도 지식을 갖춘 사용자가 절감하고 있듯이, 데이터베이스의 성능 튜닝은 어떻게 Disk I/O를 줄이느냐가 관건일 때 가 상당히 많다.

HDD vs SSD

한번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 랜덤 I/O가 훨씬 빠르다는게 장점이다.

Random I/O vs Sequential I/O

• 원래는 하드디스크는 실제 원판을 돌려서 데이터가 저장된 위치로 디스크 헤더를 이동시킨다음 데이터를 읽었음
• 거기서 나온 용어
• 실제 3개의 페이지를 삽입하더라도, 연속적으로 삽입하는것과 랜덤한 위치에 삽입하는경우 디스크 헤더를 돌리는 시스템콜의 횟수가 세배 차이 + 디스크 회전에 걸리는 시간까지

사실 쿼리를 튜닝해서 랜덤 i/o를 순차 i/o로 바꿔서 실행할 방법은 그다지 많지 않다. 일반적으로 쿼리를 튜닝하는 것은 랜덤i/o 자체를 줄여주는것이 목적이다. 여리서 랜덤 i/o를 줄인다는 것은 쿼리를 처리하는데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

인덱스란?

칼럼의 값과 해당 레코드가 저장된 주소를 키밸류로 삼아 인덱스를 만든다. 정렬을 유지한다. sorted 결론적으로 데이터의 저장의 성능을 희생하고(정렬하느라), 읽기속도를 높이는 기능이다. 이 책에서는 키라는 말과 인덱스는 같은 의미로 사용하겠다

• 인덱스의 역할별 구분
  • Primary Key : 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스 (테이블에서 레코드를 식별하는 기준값 - 식별자)
  • Secondary Key : 프라이머리키를 제외한 나머지 모든 인덱스

B-Tree 인덱스

B-Tree 인덱스의 구조 및 특성

• 최상위 하나의 루트노드와 하위에 자식 노드가 붙어있음
• 가장 하위의 노드를 리프노드라고 하며, 중간에 있으면 브랜치 노드라고 함
• 리프노트드는 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있음

레코드는 당연히 정렬돼 있지 않고, insert된 순서로 저장되어있지도 않다.

• 참고로 MySQL, InnoDB의 세컨더리 인덱스의 리프노드는 레코드 주소를 프라이머리키로 논리적 주소로 쓰고 있다. 결론적으로 PK의 b트리를 다시 타야 실제 레코드 주소를 알 수 있다.

B-Tree 인덱스 키 추가 및 삭제

인덱스 키 추가

• 새로운 키 값이 B-Tree에 저장될 때, 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수 도 있고 그렇지 않을 수도 있다.
• 기본적으로 키를 저장하려면 적절한 위치를 탐색하고, 해당위치의 리프노드에 레코드 키값과, 주소정보를 리프노드에 저장해야한다.
• 리프노드가 꽉차면 split이 일어나야하는데, 이 split은 브랜치노드에도 영향을 미칠수 있어 복잡한 작업이다.
• 인덱스의 추가로 인해 Insert, Update에 갈 영향을 대략적으로 계산하는법
  • 기본 비용을 1이라 가정
  • 인덱스에 키를 추가하는 작업 비용을 1.5라 가정
  • 예를들어 3개인경우 (1.5 * 3 + 1) 5.5가 소요
  • 이건 인덱스마다 인덱스페이지를 읽고 써야해서 걸리는 것이다.
• InnoDB는 참고로 키추가에 약간의 최적화가 되어있다.
  • 만약 Unique가 아니면 필요한 경우 지연시킴

인덱스 키 삭제

• 삭제는 간단하다, 그냥 리프노드를 찾아서 삭제 마크만 하면 완료된다.(추후 재활용하거나 삭제 가능)
• 물론 스플릿같은 부분에서 절차적으로 간단한 것이지 disk i/o는 발생한다.

인덱스 키 변경

• 부하가 가장 심함, 삭제를 하고 추가하는 방식으로 진행된다.

인덱스 키 검색

• 트리탐색, log n 복잡도, 위의 것들을 감수한만큼 빠른 성능
• 100퍼센트 일치, 혹은 값의 앞부분만 일치하는 경우에 사용 가능 (like test%)
• 부등호 비교 조건에서도 인덱스를 활용 할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용 할 수 없다.
• 인덱스 키값에 변형이 가해진 후 비교되는경우에는 B-Tree의 빠른 검색기능을 이용할 수 없다.

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

InnoDB 스토리지 엔진의 인덱스는 더 특별한 의미가 있다. InnoDB 테이블에서 지원하는 레코드 잠금이나, 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 UPDATE, DELETE문장이 실행 될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다. 인덱스도 결국은 페이지 단위로 관리되며, 루트와 브랜치 리프를 구분한 기준이 바로 페이지 단위이다.

• 기본적으로 innoDB의 페이지 엔진 단위는 16kb (설정 가능)
• 인덱스 키가 16byte, 자식노드의 주소가 대력적으로 12byte라고 가정했을때 하나의 인덱스 페이지(16kb)에 585개 정도의 키를 저장할 수 있다.
• 문제는 인덱스 키값이 커지면 페이지에 저장되는 인덱스가 줄어들것이고, 그 줄어든게 성능에 직접적인 영향을 미친다.
  • 예를들어 500개의 범위를 긁어오는 쿼리를 날렸는데,
  • 키가 커져서 페이지당 300개의 인덱스를 보관한다면,
  • 최소 두번의 disc I/O가 발생
• 추가적으로 인덱스 키값이 커지면, 인덱스 자체의 크기가 커지는것으로, 메모리에 캐시해둘 수 있는 레코드수는 감소한다.

B-Tree의 깊이

• 키값이 늘어나서 페이지당 키값이 줄어들면 깊이도 늘어난든다.
• 사실 큰 문제가 되는건 아니고 인덱스 키값을 줄이는게 낫다는 이야기를 하기위해서 강조했다고 한다.

Selectivity(Cardinality)

• 거의 같은 의미로 사용되며 모든 인덱스 키값가운데 유니크한 값의 수를 의미
• 전체 인덱스 키값이 100개인데 10개가 유니크하다면 selectivity 는 10
• 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
• 물론 정렬이나 그루핑과 같은 작업을 위해 좋지않은 선택도라도 인덱스를 넣는경우도 많다.

-- country, city칼럼이 있으며, 전체 레코드는 1만건, country테이블만 인덱스가 있을 때
-- 케이스 A: country 칼럼의 유니크한 값의 개수가 10개
-- 케이스 b: country 칼럼의 유니크한 값의 개수가 1000개

select *
from tb_test
where country='KOREA' and city='SEOUL';

-- 만약 이 조건을 만족하는 레코드가 단 한 건이라면?

• 위의 예시에서, a의경우 평균적으로 1000건을 조회하게되고, b의 경우 평균적으로 10건을 조회하게 된다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는것보다 높은 비용이 드는 작업이다.

• 일반적으로는 레코드 한건을 그냥읽는 것 대비 인덱스를 타고 읽는게 4~5배 정도 비용이 많이 든다고 언급된다.
• 즉 만약 테이블의 20~25%가 조회대상인경우, 전체를 읽고 필요한 레코드만 가려내는 방식으로 처리하는것이 효율적이다.
• 이런 계산은 옵티마이저가 해주긴 하지만, 알고 있어야 한다.
  • 예를들어 실제 조회때마다 테이블의 30퍼센트 남짓을 퍼가야 하는데 그것 ‘때문에’ 인덱스를 추가하는짓은 하지 말아야한다.

B-Tree 인덱스를 통한 데이터 읽기

어떠한 경우에 인덱스를 사용하게 유도할지, 반대로 사용하지 못하게 할지 판단하려면 MySQL더 정확하게는 스토리지엔진이 어떻게 인덱스를 이용해서 실제 레코드를 읽어내는지 알아야 한다.

인덱스 레인지 스캔

• 가장 대표적인 접근 방식이고, 뒤에 설명할 나머지 두가지 접근 방식보다는 빠른 방법.
• 검색해야할 인덱스의 범위가 결정됐을때 사용하는 방식
• 처음에는 리프노드까지 가서 찾고, 순차적으로 읽어서 긁어온다(스캔)
• 보통 리프노드에서 레코드(의 주소는) 연결리스트로 되어있어 다음 노드도 바로 접근이 가능하다.
• 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 가져온다.
• 별도의 정렬과정이 수반되는것이아니라 인덱스 자체의 정렬 특성때문에 그런것
• 인덱스의 리프노드에서 검색조건에 일치하는 건들은 데이터파일에서 레코드를 읽어오는 과정이 필요하다.
• 이 때 리프노드에 저장된 레코드 주소로 데이터파일의 레코드를 읽어오는데 레코드 한건 한건 단위로 랜덤 I/O가 한번 씩 일어난다.
• 결론적으로
  • Index seek : 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.
  • Index sacn : 탐색된 위치부터 필요한 만큼 인덱스를 쭉읽는다.
  • Index scan의 결과로 읽어들인 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.
• 위의 3번의 과정이 필요없는 경우(즉 인덱스 내의 처리에서만으로 조회 대상이 끝나는 경우)를 Covering Index라고 하는데 성능이 좋다.

show status like 'Handler%';

• Handler_read_key     → 인덱스를 이용하여 데이터를 조회한 횟수     값이 높다면 인덱스를 잘 활용하고 있는 것   값이 낮다면 테이블 스캔이 발생하고 있을 가능성이 있음.

• Handler_read_rnd_next     → 전체 테이블 스캔을 통해 데이터를 읽은 횟수     값이 높다면 인덱스 없이 전체 테이블을 반복 탐색하고 있을 가능성이 높음.     인덱스를 추가하여 최적화할 필요가 있음.

• Handler_read_first     → 인덱스의 첫 번째 행을 읽은 횟수     ORDER BY 또는 GROUP BY 쿼리에서 주로 발생.

• Handler_read_next / Handler_read_prev     → 인덱스 범위 스캔이 얼마나 일어나는지 측정     Handler_read_next 값이 높다면, 인덱스 범위 검색이 활발하게 이루어지고 있는 것.

• Handler_read_rnd     → 랜덤하게 특정 위치에서 데이터를 읽은 횟수     값이 높다면 파일 정렬(File Sort) 이 자주 발생할 가능성이 있음.

• Handler_commit & Handler_rollback     → 트랜잭션이 얼마나 자주 커밋되고 롤백되는지     트랜잭션이 너무 잦으면 성능 저하의 원인이 될 수 있음.

인덱스 풀 스캔

• 인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만, 레인지스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식
• 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫번째 칼럼이 아닌 경우 인덱스 풀스캔 반식이 사용된다.
• 즉 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는경우에 이렇게 처리된다.

루스 인덱스 스캔

• 집계 쿼리에서 필요없는값 넘기는 인덱스
• a, b 칼럼 복합인덱스인데, b가 정렬되어있으면
• a를 기준으로 건너뛰면서 첫줄의 b값만 가져와서 처리하면된다.

인덱스 스킵 스캔

• 인덱스를 스킵한 것처럼 최적화 해주는것
• 복합인덱스에서, 인덱스가 없는 선행칼럼의 유니크값들을 다 뽑아서 여러번의 쿼리를 날리는것과 비슷한 형태의 최적화
• MySQL에서는 커버링 인덱스인경우만 가능

다중 칼럼 인덱스

• 복수의 키
• 왼쪽부터 정렬
• 칼럼의 위치를 신중하게 결정해야함

B-Tree 인덱스의 정렬 및 스캔 방향

• 항상 오름차순이거나 내림차순으로 정렬됨
• 옵티마이저가 실시간으로 정리해서 실행계획 수립

인덱스의 정렬

설정할 때 가능 8.0 이상

create index ix_teamname_userscore on employees (team_name asc, user_score desc)

스캔 방향

• 옵티마이저가 알아서 잘 선택

B-Tree 인덱스의 가용성과 효율성

select * from dept_emp
where dept_no='d002' and emp_no >= 10114
;

케이스 a: index(dept_no, emp_no)
케이스 b: index(emp_no, dept_no)

• 케이스 a : “dept_no = ‘d002’ and emp_no >= 10114” 인 레코드를 찾고, d002가 아닐때까지 쭉 읽기만 하면 됨 - 효율적
• 케이스 b : “dept_no = ‘d002’ and emp_no >= 10114” 인 레코드를 찾고, 그 이후 모든 레코드가 d002인지 비교해야한다.
• case a 에서는 두조건이 작업 범위를 결정하도록 동작했다.
• case b 에는 dept_no가 필터링역할만 했다.

가저오는 레코드는 줄었기때문에 이것을 쿼리의 비용이 줄었다고 착각하기 쉬워서 나온 예시 같다. 결론적으로는 조건절에서 주는 조건들이 작업범위를 제한해줘서 쿼리의 성능을 높이는지, 그게아니면 필터링역할만 하는지 잘 구분해야한다는것.

인덱스의 가용성

• 항상 왼쪽 범위 정렬을 염두에 둬야한다.
• %like vs like% : 후자만 인덱스를 효율적으로 이용한다.
• 마찬가지로 케이스 a에서 만약 그냥 dept_no를 안줬다면 직전의 예시와 똑같다.

가용성과 효율성 판단

• 기본적으로 아래의 조건에서는 작업 범위 조건으로 인덱스를 활용할 수 없다
  • NOT-EQUAL로 비교된 경우 <>, not in, is not null
  • like 앞의 %
  • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
  • 비결정적 함수가 비교조건에 사용된경우
  • 데이터타입이 서로 다른 비교
  • 문자열 데이터 타입의 콜레이션이 다른 경우

클러스터링 인덱스

• pk를 기준으로 물리적으로 가까운 위치에 데이터를 저장
• 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장방식임
• 레코드 저장이나 프라이머리 키의 변경이 상대적으로 느림
• 기본적으로 지원하며, 그래서 pk가 없어도 기준에따라 클러스터링 키를 정함
• 그래서 프라이머리키를 논리적주소로 사용하는것
• 왜나하면 프라이머리키의 변경이 있을때마다 실제 레코드 주소가 변경되면 모든 인덱스의 물리적 주소를 바꿔줘야 하기 때문에
• 그래서 커버링인덱스를 조금 더 잘 활용해야함
• 마지막으로 장단점 요약은 읽기에서 거의 득을 보지만 쓰기에서 실이 있는 구조라 온라인서비스에 더 적합
• 아 진짜 마지막으로, 그래서 snowflake같은걸 써주면 좋음 (snowflake의 생성시간 기반 unique)

유니크 인덱스

더 성능이 좋지는 않다, 쓰기성능은 살벌하고

외래키

부모 테이블(PK 테이블) 변경 시

• INSERT: 자식 테이블에서 참조하는 PK가 있는 경우, 부모 테이블의 해당 PK에 대해 S(공유) 락이 걸림.
• UPDATE: 부모 테이블의 기본 키(PK)를 변경하려면, 자식 테이블이 이를 참조하고 있기 때문에 X(배타적) 락이 발생함.
• DELETE: 부모 테이블의 PK를 삭제하려면 자식 테이블의 FK가 이를 참조하는지 확인해야 하므로 X(배타적) 락이 발생.

자식 테이블(FK 테이블) 변경 시

• INSERT: 부모 테이블에 존재하는 PK를 참조해야 하므로, 부모 테이블에서 해당 PK에 대해 S(공유) 락이 발생.
• UPDATE: 자식 테이블의 FK 값을 변경하는 경우, 기존 및 새로운 FK 값이 부모 테이블에 존재하는지 확인해야 하므로 부모 테이블에 S(공유) 락이 발생.
• DELETE: 자식 테이블의 행을 삭제하는 것은 일반적으로 락을 유발하지 않지만, ON DELETE CASCADE 설정이 되어 있다면 부모 테이블에도 X(배타적) 락이 발생할 수 있음.

옵티마이저와 힌트

결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다. 이런 다양한 방법중에서 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야한다. (동일한 결과 다양한 방법중에서의 최적화가 옵티마이저)

개요

쿼리 실행 절차

• 첫 단계 : 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리 (parse tree) 한다.
  • 문법적으로 잘못됐으면 여기서 걸러짐
  • MySQL 서버는 파스트리를 이용해 쿼리를 실행한다.
• 두번째 단계 : SQL의 파싱 정보 (파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
  • 불필요한 조건제거하고 가능하다면 복잡한 연산을 단순화한다.
  • 여러 테이블의 조인이 있는 경우 어떤 순서로 읽을지 결정한다.
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정한다.
  • 가져온 레코드들을 임시 테이블에 넣고 다시 한 번 가공해야 하는지 결정한다.
• 세번째 단계 : 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

옵티마이저의 종류

• CBO (Cost Based Optimizer) : 여러가지 가능한 방법을 만들고 각 단위작업의 비용정보와 대상 테이블의 예측된 통계 정보를 이용해 실행계획별 비용을 산출한다.
• RBO (Rule Based Optimizer) : 그냥 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다. 다만 요즘은 거의 CBO를 이용한다. 인덱스나 테이블의 통계정보가 없고 CPU연산이 느리던 시절에 사용했었읍

기본 데이터 처리

풀 테이블 스캔과 풀 인덱스 스캔

• 풀테이블 스캔은 말 그대로 테이블의 테이터를 처음부터 끝까지 읽어서 요청을 처리하는것
• 옵티마이저가 풀테이블 선택을 선택하는경우
  • 테이블 레코드 건수가 너무 작아서 인덱스보다 풀테이블스캔이 빠른경우 (페이지 1개로 구성된경우)
  • where절이나 on절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (인덱스 b-tree를 샘플링해서 조사한 통계정보 기준)
• 리드 어헤드(read ahead)가 일어남
  • 포어그라운드 스레드가 페이지를 읽다가, 읽는 작업을 백그라운드 스레드에 양도하는일
  • 일반적으로 디폴트로 리드어헤드 설정을 쓰지만, 데이터 웨어하우스용으로 사용하면 이 옵션을 더 낮은 값으로 설정해서 리드어헤드를 빠르게 유발하는게 나은 경우도 있다. (애초에 시퀀셜 스캔이 많고, 백그라운드가 빨리 처리해놓는게 낫기 때문에)
• 풀 인덱스 스캔은 집계쿼리등에서 자주 일어남
  • 레코드의 데이터가 필요 없고 적절한 인덱스를 찾을 수 있다면 데이터 크기가 압도적으로 적어서 유리

병렬 처리

하나의 쿼리를 나눠서 처리한다는걸 이야기한다. 여러개의 스레드가 각각의 쿼리는 원래도 됐었다.

set session innodb_parallel_read_threads=1;
set count(*) from salaries;
1 row in set (0.32 sec)

set session innodb_parallel_read_threads=2;
set count(*) from salaries;
1 row in set (0.20 sec)

set session innodb_parallel_read_threads=3;
set count(*) from salaries;
1 row in set (0.13 sec)

Order by 처리 (Using filesort)

• 메모리에서 별도의 정렬을 진행하는것
• 레코드 크기에 비례해서 쿼리의 속도가 줄어든다
• 아래와 같은 경우에 주로 사용
  • 정렬기준이 너무 많아서 요건별로 모두 인덱스 생성이 불가능한경우
  • group by의 결과 또는 distict 같은 처리의 결과를 정렬해야하는경우
  • Union의 결과 같이 임시 테이블의 결과를 다시 정렬해야하는경우
  • 랜덤하게 결과 레코드를 가져와야하는 경우

소트 버퍼

• 메모리내에서 정렬하는데 쓰이는 버퍼
• 문제는 레코드가 너무 크면 disk i/o가 발생해서 성능이 현저히 줄어든다.
• 그렇다고 메모리를 늘리는건 부작용이 너무 커서 신중하게 결정해야한다. (전체적인 서버풀의 메모리 부족인경우 종료대상 프로세스 1순위)

정렬 알고리즘

• 크게는 투패스와 싱글패스가 있다.
• 레코드 전체를 소트버퍼에 담으면 싱글패스, 정렬 기준 칼럼을 분리하면 투패스

Single Pass Sort

• 말 그대로 쿼리 요청의 모든 칼럼을 버퍼에 담고, 그 버퍼를 정렬시킨뒤 반환

Two Pass Sort

• 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리키로 select

• 최신 버전에서는 보통은 Single Pass 를 이용하지만, 경우에따라 Two Pass를 사용하기도 한다.

  • 레코드 크기가 시스템 변수에 설정된 소트할 대상 레코드 값보다 클 때
  • BLOB or Text Type Comlumn이 Select 대상에 포함될 때
  • 그 외에도 일반적으로 레코드 크기가 커질수록 Two Pass Sort가 빠르다. (다만 큰 경우는 인덱스를 태울것 같아서 이렇게 이야기 한 것 같다)

정렬 처리 방법

• 쿼리에 order by가 사용되면 반드시 아래 세개 처리방법이 사용된다.
  • 인덱스를 이용한 정렬 : (extra column에) 별도 표기 없음
  • 조인에서 드라이빙 테이블만 정렬 : (extra column에) “Using filesort”
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 : (extra column에) “Using temporary; Using filesort”
• 만약 조인이 낀다면
  • 조인의 드라이빙 테이블만 정렬한 다음 조인 수행
  • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
• 당연하지만, 전자가 훨씬 공간효율성이 높기때문에 전자를 먼저 고려할 것이다.

인덱스를 이용한 정렬

• 반드시 order by에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, order by 순서대로 생성된 인덱스가 있어야함.
• 또한 where절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 orderby는 같은 인덱스를 사용할 수 있어야 한다.
• 여러 테이블이 조인되는 경우 네스티드 루프 방식의 조인에서만 이 방식을 이용할 수 있다.
• 이렇게 조건을 만족하면 엔진에서 별도의 처리를 하지는 않는다 정렬된 인덱스대로 읽어온다.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

  

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE INDEX idx_orders_order_date ON orders(order_date);

EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-02-02'
ORDER BY o.order_date;

-- Index Scan using idx_orders_order_date on orders o
  -- Index Cond: (order_date >= '2024-02-02')
  -- -> Nested Loop Join with customers c
     -- Filter: o.customer_id = c.customer_id

SELECT o.order_id, o.order_date, c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-02-02'
ORDER BY c.customer_name; -- Warn : customers 테이블의 컬럼으로 정렬

조인에서 드라이빙 테이블만 정렬

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

• employees 에서 e.emp_no BETWEEN 10002 AND 10010 만족하는 9건을 검색
• 이 단계에서 Filesort by last_name
• 정렬된 결과로 조인을 수행

임시 테이블을 이용한 정렬

SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 10002 AND 10010
ORDER BY s.salary;

• employees 에서 e.emp_no BETWEEN 10002 AND 10010 만족하는 9건을 검색
• 이 단계에서 Filesort를 할 수 없음
• 조인 이후 정렬해서 반환 (메모리는 많이 낭비)

정렬 처리 방법의 성능 비교

• 잘못된 order by나 group by 때문에 쿼리가 느려지는 경우가 있음
• 왜 쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 느리게 동작하는지 확인
• 그러기 위해서 쿼리가 처리되는 방식을 “스트리밍처리”, “버퍼링처리"로 구분해야한다.

스트리밍 방식

• 레코드가 검색될 때마다 바로바로 클라이언트로 전송
• 클라이언트는 곧바로 원했던 첫 번째 레코드를 전달받음
• OLTP 환경에서는 쿼리의 첫 요청에서부터 첫 레코드를 전달받게까지의 응답시간이 가장 중요해서 자주쓰임
• 이 방식에서는 LIMIT이 쿼리의 비용을 상당히 줄여줌

버퍼링방식

• ORDER BY나 GROUP BY같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다.
• 일단 모든 레코드를 가져온 다음에 처리를해야 논리적으로 충족되기 때문
• 네트워크로 전송되는 레코드의 건수를 줄일수는 있으나 성능향상에는 도움이 별로 안된ㄷ다 (서버작업 측면에서는 기다지 변화가 없음)
• 아래를 다시 보면
  • 인덱스를 이용한 정렬 : 스트리밍 방식으로 limit 충족할때까지 읽어서 그때그때 던져주면 된다.
  • 조인에서 드라이빙 테이블만 정렬 : limit 의미없음, filesort 대상이 되는 레코드들을 불러와 정렬한뒤 잘라서 던져줘야한다.
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 : 위와 마찬가지

select *
from tb_test1 t1, tb_test2 t2
where t1.col1 = t2.col2
order by t1.col2
limit 10
;

-- tb_test1의 레코드가 100건이고, tb_test2의 레코드가 1000건 (1건의 tb_test1당 test2에 10건 존재 가정)

• tb_test1이 드라이빙 되는 경우
  • (인덱스)읽어야할 건수 : t1 - 1건, t2 - 10건
  • (인덱스)조인 횟수 1번
  • (인덱스)정렬대상 건수 : 0건
  • (조인의 드라이빙 테이블만 정렬) 읽어야할 건수 : t1 - 100건, t2-10건
  • (조인의 드라이빙 테이블만 정렬) 조인 횟수 : 1번
  • (조인의 드라이빙 테이블만 정렬) 정렬 대상 건수 : 100건 (t1의 레코드 건수 만큼)
  • (임시 테이블 사용후 정렬) 읽어야할 건수 : t1 - 100건, t2 - 1000건
  • (임시 테이블 사용후 정렬) 조인 횟수 : 100번 (t1의 레커드 건 수 만큼)
  • (임시 테이블 사용후 정렬) 정렬대상 건수 : 1000건
• tb_test2이 드라이빙 되는 경우
  • (인덱스)읽어야할 건수 : t1 - 10건, t2 - 10건
  • (인덱스)조인 횟수 10번
  • (인덱스)정렬대상 건수 : 0건
  • …

결론은 가능하면 인덱스를 사용하도록 유도하고, 그게 어렵더라도 드라이빙 테이블만 정렬해도 되도록 처리해야한다.

정렬 관련 상태 변수

Group by 처리

Group by 또한, order by와 같이 쿼리가 스트리밍된 처리를 할 수 없게함 group by에 사용된 조건을 인덱스를 사용해서 처리될 수 없으므로 having절을 튜닝하려고 인덱스를 생성하거나 할 필요는 없음

인덱스 스캔을 이용하는 group by (타이트 인덱스 스캔)

• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑 할 때 group by 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리함
• 물론 집계함수등을 쓰면서 임시 테이블이 필요 할 수 있긴 함

루스 인덱스 스캔을 이용하는 group by

• GROUP BY 연산 시 일부 인덱스 키만 읽고, 나머지 행을 건너뛰는 방식
• GROUP BY 대상 컬럼이 인덱스의 선두(prefix) 부분과 일치해야 한다.  

-- min, max 이외의 집합 함수 사용시 루스인덱스 안탐
select col1, sum(col2) from test group by col1;

-- group by에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않기 때문에 사용 불가
select col1, col2 from test group by col2, col3;

-- select절의 칼럼이 group by와 일치하지 않기 때문에 사용 불가
select col1, col3 from test group by col1, col2;

임시 테이블을 사용하는 group by

• GROUP BY 대상 컬럼이 적절한 인덱스를 활용하지 못할 때  
• GROUP BY에 포함되지 않은 컬럼을 SELECT 할 때 (Loose Index Scan 적용 불가)  
• ORDER BY가 GROUP BY와 다른 경우  
• HAVING 절이 포함된 경우  

Distinct 처리

특정 칼럼의 유니크한 값만 조회하려면 distinct를 사용한다. distinct는 min, max 또는 count같은 집합함수와 함께 사용되는경우와 그렇지 않은 경우가 다르다. 그리고 집합함수와 같이 distinct가 사용되는 쿼리의 실행 계획에서 distinct처리가 인덱스를 사용하지 못할 때 항상 임시 테이블이 필요하다. (실행계획의 extra column에서는 using temporary가 출력되진 않는다.)

select distinct …

-- 8.0 이상에서는 group by 수행하는 쿼리에 orderby절이 없으면 정렬을 사용하지 않기 때문에, 
-- 다음의 두 쿼리는 내부적으로 같은 작업을 수행한다.
select distinct emp_no from salaries;
select emp_no from salaries group by emp_no;

집합 함수와 함께 사용된 distinct

• 집합 함수가 없는 select쿼리에서 distinct는 조회하는 모든 칼럼의 ‘조합이’ 유니크한 것들만 가져온다.
• 하지만 집합함수 내에서 사용된 distinct는 그 집합 함수의 ‘인자로 전달된 칼럼값이 유니크한 것들’을 가져온다.

-- 쿼리 A
select distinct first_name, last_name
from employees
where emp_no between 10001 and 10200;

-- 쿼리 B
select distinct count(distinct first_name), count(distinct last_name)
from employees
where emp_no between 10001 and 10200;

-- 쿼리 C
select count(distinct first_name, last_name)
from employees
where emp_no between 10001 and 10200;

• 첫 번째 쿼리는 first_name, last_name을 포함하는 임시 테이블을 만들고, 전체 데이터를 스캔하여 중복을 제거한 후 반환한다.
• 두 번째 쿼리는 first_name과 last_name을 각각 다루므로, 내부적으로 두 개의 별도 집합을 생성하여 각 컬럼에서 유니크한 값을 카운트한다.
• 세 번째 쿼리는 (first_name, last_name)의 조합이 유니크한 개수를 찾기 위해 단일 임시 테이블을 생성하고, 해당 조합을 정렬한 후 중복을 제거하여 개수를 반환한다. 인덱스 사용 여부
• first_name, last_name에 개별 인덱스가 있다면, 두 번째 쿼리는 인덱스를 활용할 가능성이 높다.
• 하지만 첫 번째와 세 번째 쿼리는 두 개의 컬럼을 동시에 고려해야 하므로, 단일 컬럼 인덱스만으로 최적화되기 어렵고, 정렬(Sort) 또는 임시 테이블을 사용하여 처리될 가능성이 높다.
• (first_name, last_name) 복합 인덱스가 존재한다면, 첫 번째와 세 번째 쿼리는 성능이 개선될 수 있다.

결론

• 단순한 DISTINCT 조회(SELECT DISTINCT first_name, last_name)는 결과 집합을 반환해야 하므로, 임시 테이블을 생성하고 정렬을 수행해야 한다.
• **COUNT(DISTINCT 컬럼)을 사용할 경우, 컬럼별로 중복을 제거하는 방식이 다르며, 인덱스를 더 효과적으로 활용할 가능성이 있다.
• 두 개 이상의 컬럼을 DISTINCT로 개수를 세는 경우(COUNT(DISTINCT first_name, last_name))는 (first_name, last_name)을 하나의 조합으로 인식하여 처리하는 단일 임시 테이블을 사용한다.

내부 임시 테이블 활용

정렬 혹은 그루핑 할 때 암시적으로 사용하는 테이블, 즉 직접 만든 create temporary table과는 다름 디스크에 임시테이블이 만들어질때도 있음

disk vs memory

• 1gb가 넘으면 디스크로 넘김
• 오버헤드가 적은 mmap으로 넘기는게 기본 변수로 설정되어있음

임시테이블이 필요한 쿼리

• order by, group by에 명시된 칼럼이 다른 쿼리
• order by, group by에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• distinct와 order by가 동시에 쿼리에 존재하는경우 또는 distinct가 인덱스로 처리되지 못하는 쿼리
• union이나 union distinct가 사용된 쿼리
• 쿼리 실행 계획에서 select_type이 derived인 쿼리

고급 최적화

• 고급최적화
• 인덱스 푸쉬 다운
• 고급회적화2
• 쿼리 힌트

실행 계획

통계 정보

• 8.0 버전 이후부터 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램이 도입됐다.

테이블 및 인덱스 통계정보

• 비용기반 최적화에서 가장 중요한건 통계정보
• 8.0 이전까지는 통계정보가 지나치게 부정확해서 이슈였음

MySQL 서버의 통계 정보

• 기존에는 통계정보가 메모리에만 있어서 휘발성이 있었음
• 그러나 최근에는 테이블로 관리함으로써 유지

• 각 칼럼의 의미

• stat_name 항목 설명

히스토그램

• 분포를 보기위함
• 직접 수집되지는 않고 명시적으로 analyze table ... update histogram이라는 쿼리로 수행해야함
• 샘플링 갯수등을 설정 가능함
• 환경변수값으로 옵티마이저가 히스토그램을 참고할지 말지 정할 수 있음

히스토그램의 용도

• 버킷별로 레코드의 갯수와, 유니크한 값의 개수 정보를 가진다.
• 이 정보를 토대로 성능적으로 유리한 선택을 한다.
• 주로 인덱스 되지 않은 칼럼에 분포를 사용할 때 씀

조인 전에 작은 테이블을 필터링하면 빠름, 분포가 25퍼센트가 넘는다고 판단하면 풀테이블 스캔이 빠름 등등으로 유리한 선택을 한다는 뜻.

히스토그램과 인덱스

• 사실 통계정보만 활용하는것이 아니라, 인덱스를 샘플링해서 계획을 수립하는 경우가 많음
• 조건절의 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 b-tree를 샘플링해서 살펴본다.
• 이 작음을 Index Dive라고 함

코스트 모델

• mysql 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.
  • 디스크로부터 데이터 페이지 읽기
  • 메모리로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
• 위와 같은 작업이 얼마나 필요할지 예측하고 전체 작업ㅂ 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.
• 이렇게 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 Cost Model이라고 한다.
• 결론적으로 8.0 이상의 코스트모델은 다음 두개의 설정값을 사용한다.
  • server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  • engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
  • 두 값은 테이블로 관리되는데 다음과같은 칼럼들을 가지고 있다.
    • 이하 공통
      • cost_name: 코스트 모델의 각 단위 작업
      • default_vallue: 각 단위 작업의 비용
      • cost_value : dbms관리자가 설정한 값
      • last_updated : 단위 작업의 비용이 변경된 시점
      • comment : 비용에 대한 추가 설명
    • 이하 engine_cost에만 추가적으로 있는 칼럼
      • engine_name : 비용이 적용된 스토리지 엔진
      • device_type : 디스크 타입

• row_evaluate_cost : 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업
  • 이 값이 증가할수록 풀테이블 스캔과 같이 많은 레코드를 처리하는 비용이 높아지고,
  • 반대로 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아진다.
• key_compare_cost : 키값의 비교작업에 필요한 비용을 의미하는데, 레코드 정렬과 같이 키값 비교 처리가 많은 경우 쿼리의 비용이 높아진다.

확실히 DBA의 영역인 것 같다

실행 계획 확인

실행 계획 출력 포맷

• FORMAT= 으로 줄 수 있음 (json, tree 등등)

쿼리의 실행 시간 확인

• EXPLAIN ANALYZE로 트리형식으로 단계별 소요시간을 볼 수 있음

EXPLAIN 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;

A) -> Table scan on <temporary> (actual time=0.001..0.004 rows=48 loops=1)
B)   -> Aggregate using temporary table (actual time=3.779..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: (...where절 필터들)
F)         -> Index lookup on s using primary (emp_no=e.emp_no)

• 들여쓰기가 같은 레벨에서는 상단의 라인이 먼저
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저

그래서 아래처럼 실행된다

1. D) Index lookup on e using ix_firstname // employees 테이블의 인덱스를 통해 first_name=Matt조건의 레코드 찾기
2. F) Index lookup on s using primary // salaries 테이블의 pk로 emp_no가 1번 결과의 emp_no와 동일한 레코드를 찾아서
3. E) filter // 2번을 filter하고 일치하는값 뽑기
4. C) Nested  loop inner join // 1,3번의 결과를 조인해서
5. B) aggregate using temporary table // 임시테이블에 결과를 저장하며 group by 집계
6. A) table scan on <temporary> // 임시테이블의 결과를 반환

실행 계획 분석

id칼럼

• select별로 부여되는 id
• 접근 순서를 의미하지는 않음

select_type 칼럼

• 각 단위 select쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼
• simple : union이나 서브쿼리를 사용하지 않는 단순한 select, 쿼리가 아무리 복잡해도 단 하나(일반적으로 제일 바깥)
• primary : union이나 서브쿼리의 실행계획에서 가장 바깥쪽, 마찬가지로 단하나
• union : 유니온으로 결합하나는 단위 셀렉트 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위의 셀렉트 쿼리의 타입, 유니온의 첫번째 단위 셀렉트의 타입은 union되는 쿼리 결과들을 모아서 저장하는 임시테이블(derived)가 select_type으로 설정됨
• dependent union : union 혹은 union all로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는걸 의미
• union result : union결과를 담는 테이블 (실제 쿼리에서 단위 쿼리가 아니기에 별도의 id 없음)

explain
select emp_no from salaries where salary > 100000
union  distinct
select emp_no from dept_emp where from_date > '2001-01-01'

• subquery : from절 이외에서 사용되는 서브쿼리만을 의미