2. SQL 튜닝 용어

SQL 튜닝을 수행하기 전에 알아야 하는 물리 엔진의 구조, DB 오브젝트 관련 용어, 논리적인 쿼리문 작성 관련 용어, 개념적으로 통용되는 튜닝 용어를 알아보자

---

물리 엔진과 오브젝트 용어

DB 엔진 용어

 

DB엔진 = 스토리지 엔진 + MySQL엔진

 

스토리지 엔진 

• 역할 : 요청한 SQL문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할을 수행
• 스토리지 엔진 선택
  • InnoDB 엔진 : 온라인상의 트랜잭션 발생으로 데이터를 처리 - 일반적임
  • MyISAM 엔진 : 대량의 쓰기 트랜잭션 처리
  • Memory 엔진 : 메모리 데이터를 로드하여 빠르게 읽는 효과를 내려면

MySQL 엔진 

• 역할
  • SQL문법 검사
  • 오브젝트 활용 검사
  • SQL문을 최소 단위로 분리하여 원하는 데이터를 빠르게 찾는 경로를 모색
  • 스토리지 엔진으로부터 전달받은 데이터 대상으로 불필요한 데이터 제거 or 가공

---

SQL 프로세스 용어

파서 

• 사용자가 요청한 SQL문을 최소 단위로 쪼개 트리를 만듦
• 문법 검사

 

전처리기

• 생성한 트리를 토대로 SQL문에 구조적인 문제가 없는지 파악
• 이미 생성된 오브젝트인지
• 접근권한 확인

 

옵티마이저 

• 전달된 파서 트리를 토대로 필요하지 않은 조건은 제거하거나 연산 과정을 단순화함
• 실행 계획 수립
  • 어떤 순서로 테이블에 접근할지
  • 인덱스를 사용할지
  • 어떤 인덱스를 사용할지
  • 정렬할 때 인덱스를 사용할지 아니면 임시 테이블을 사용할지

 

엔진 실행기 

• 옵티마이저에서 수립한 실행 계획을 참고하여 스토리지 엔진에서 데이터를 가져옴
• 정렬, 조인, 필터링 등 추가 작업

---

 

DB 오브젝트 용어

테이블 : 데이터를 저장하는 오브젝트

 

행 : 테이블에서 동일한 구조의 데이터 항목들의 집합

 

열 : 사전에 정의한 데이터 유형

 

기본키 : 특정 행을 대표하는 열

인덱스 주의사항 : 기본 키와 같은 열로 인덱스 생성 시 오버헤드 발생

 

외래 키 : 외부에 있는 테이블을 항상 참조하면서, 외부 테이블의 데이터가 변경되면 함께 영향을 받는 관계를 설정하는 키

 

인덱스 : 실제 데이터 위치를 식별하고 데이터 접근 속도를 높이고자 생성되는, 키 기준으로 정렬된 오브젝트

 

고유 인덱스 : 인덱스를 구성하는 열들의 데이터가 유일함

기본키와 차이점 : Null을 입력할 수 있음

DDL : ALTER TABLE 학생 ADD UNIQUE INDEX 이름(연락처)

 

비고유 인덱스 : 교유인덱스에서 데이터의 유일한 속성만 제외한 키

중복첸크를 거치지 않고 단순한 정렬 작업을 수행

 

뷰 : 가상 테이블

보안성 때문에 사용 - 일부 데이터에 대해서만 공개

---

논리적인 SQL 개념 용어

 

서브쿼리 위치에 따른 SQL 용어

• 스칼라 서브쿼리 : SELECT 절, 집계함수
• 인라인 뷰 : FROM 절, 내부적으로 일시적으로 뷰 생성 -> 내부적으로 메모리 돈느 디스크에 임시 테이블을 생성
• 중첩 서브쿼리 : WHERE 절, 비교 연산하기 위해서 사용

 

메인쿼리와의 관계성에 따른 SQL 용어

• 비상관 서브쿼리 : 메인쿼리와 서브쿼리 간에 관계성이 없음을 의미, 메인쿼리가 활용
• 상관 서브쿼리 : 메인쿼리와 서브쿼리 간에 관계성이 있음을 의미, 스칼라 서브쿼리, 중첩서브쿼리에 발생

 

 

반환 결과에 따른 SQL 용어

• 단일행 서브쿼리 : 서브쿼리 결과가 1건의 행으로 반환되는 쿼리
• 다중행 서브쿼리 : 서브쿼리 결과가 여러 건의 행으로 반환되는 쿼리
• 다중열 서브쿼리 : 버스쿼리 결과가 여러 개의 열과 행으로 반환되는 쿼리

 

 

조인 연산방식 용어

• 내부 조인 : 양쪽에 모두 존재하는 데이터만 반환
• 왼쪽 외부 조인 : 왼쪽 테이블 기준으로 조인
• 오른쪽 외부 조인 : 오른쪽 테이블 기준으로 조인
• 교차 조인 : 곱집합, 오베헤드 발생 주의
• 자연 조인 : 동일한 열명이 있을 때 자동으로 조인을 해주는 방식

 

조인 알고리즘 용어

• 드라이빙 테이블과 : 조인 연산시 먼저 접근하는 테이블 -> 적은 결과 반환되는 것을 드라이빙으로 추천
• 드리븐 테이블 : 조인 연산시 후순위에 접근하는 테이블
• 중첩 루프 조인(NL) : 데이터 1건당 드리빙 테이블과 드리븐 테이블을 반복해 검색하며, 최종적으로는 양쪽 테이블에 공통된 테이블을 출력, 인덱스가 없는 경우 다 탐색해야 되서 비효율적
• 블록 중첩 루프 조인(BNL) :
  • 1.드라이빙 테이블을 검색하면서 조인 버퍼에 넣기
  • 2. 조인 버퍼와 드리븐 테이블 조인 반복
• 블록 해시 조인 : 블록 중첩 루프 조인 방식과 유사, 조인 버퍼에 쌓인 데이터를 해식밧을 적용하고 그 값을 기준으로 드리븐 테이블과 조인
• 배치 키 액세스 조인 : 
  • 1. 드라이빙 테이블을 검색하면서 조인 버퍼에 넣기
  • 2. 드리븐 테이블의 인덱스 기반으로 필요한 데이터를 예측하여 랜덤 버퍼에 적재
  • 3. 랜덤 버퍼에 저장
  • 4. 조인한 조건절 비교
  • 5. 동일한 데이터가 있다고 판단되면 드리븐 테이블에 접근하고 결과를 조건하여 반환

---

개념적인 튜닝 용어

기초 용어

오브젝트 스캔 유형

테이블 스캔 : 인덱스를 거치지 않고 디스크에 위치한 테이블 데이터에 접근하는 유형

• 테이블 풀 스캔 : 인덱스를 거치지 않고 테이블로 바로 직행하여 데이터를 처음부터 끝까지 훑어보는 방식인덱스 없이 사용하는 유일한 방식

 

인덱스 스캔 : 인덱스로 테이블 데이터를 찾아가는 유형

• 인덱스 범위 스캔 : 인덱스 범위 기준으로 스캔한 뒤, 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식, BETWEEN~AND, <,>,LIKE구분, 비요 연산시 사용, 좁은 범위에 효율적
• 인덱스 풀 스캔 : 인덱스를 처음부터 끝까지 수행하는 방식, 테이블에 접근하지 않고 인덱스 열  정보만 스캔하는 경우 사용
• 인덱스 고유 스캔 : 기본 키나 고유 인덱스로 테이블에 접근하는 방식, WHERE 열(기본키, 고유인덱스의 선두열)=조건
• 인덱스 루스 스캔 : 인덱스의 필요한 부분들만 골라 스캔하는 방식, WHERE 절 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시 GROUP BY, MAX(), MIN()
• 인덱스 병합 스캔 : 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식
  • WHERE 조건절의 열들이 서로 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식
  • 방식 : 결합, 교차
  • 인덱스 접근 시간이 배로 걸림 - 하나의 인덱스로 통합하여 SQL튜닝을 수행 OR 독립된 하나의 인덱스만 수행하도록 변경

 

디스크 접근 방식

• 시퀀셜 액세스 : 인접한 페이지를 차례대로 순차 접근 방식, 테이블 풀 스캔에서 활용, 디스크 헤더의 움직임을 최소화하여 작업시가, 리소스 점유 비용 줄일 수 있음, 다중 페이지 읽기 수행
• 랜덤 액세스 : 물리적인 위치를 고려하지 않고 접근하는 방식, 다중 페이지 읽기가 불가능, 데이터 접근 수행 시간이 오래 걸림, 접근 범위를 줄이고 인덱스를 활용해야 됨

 

조건 유형

• 액세스 조건 : 데이터에 어떻게 접근할 것인지를 다루는 조건, 
• 필터 조건 : 액세스 조건을 이용해 가져온 데이터를 가공하는 조건

 

 

응용 용어

선택도

• 개념 : 특정 열을 기준으로 해당 열의 조건절에 따라 선택되는 데이터 비율
• 계산 방법 : 1 / DISTINCT(COUNT열명)
• 데이터 중복과 관계 : 비례

 

 

키디널리티

• 개념 : 중복을 제외한 유일한 데이값의 수
• 계산 방법 : 전체 데이터 건수 x 선택도
• 데이터 중복과 관계 : 중복이 적으면 높음

 

힌트

• 개념 : 데이터를 빨리 찾을 수 있게 추가 정보를 전달하는 것
• 자주 사용하는 힌트
  • STRAIGHT_JOIN : FROM절에 작성된 테이블 순으로 조인을 유도하는 힌트
  • USE INDEX : 특정 인덱스를 사용하도록 유도하는 힌트
  • FORCE INDEX : 특정 인덱스를 사용하도록 강하게 유도하는 힌트
  • IGNORE INDEX : 특정 인덱스를 사용하지 못하도록 유도하는 힌트
• 주의점 : 데이터 건수가 수시로 급변할 수 있어서 오류 발생 가능성 있음 -> sqL문에 힌트를 작성하면 별도록 관리해야 함

 

콜레이션

• 특정 문자셋으로 db에 저장된 값을 비교하거나 정렬하는 작업의 규칙

 

통계정보

• 옵티마이저는 통계정보에 기반을 두고 sql문의 실행 계획을 수립함 -> 통계정보 최신화가 중요함

 

히스토그램

• 개념 : 테이블의 열값이 어떻게 분포되어 있는지를 확인하는 통계정보
• 사용법 : ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 열명;
• 히스토그램 수집되지 않으면 : COUNT(DISTINCT 열명)