2.1 물리 엔진과 오브젝트

2.1.1 DB엔진 용어

사용자는 DB에서 원하는 데이터를 가져오고자 SQL문을 실행한다. 실행된 SQL 문은 MySQL 엔진에서

문법 에러가 있는지, DB에 존재하는 테이블 대상으로 SQL문을 작성했는지와 같은 세부 사항을

다양한 문법 및 구문으로 검사한다.(파싱 작업을 하는 parser 역할)

이후 사용자가 요청한데이터를 빠르고 효율적으로 찾아가는 전략적 계획을 수립한다.(optimizer 역할)

이 계획을 토대로 스토리지 엔진에 위치한 데이터까지 찾아간 뒤 해당 데이터를 MySQL엔진으로 전달한다.

MySQL 엔진은 전달된 데이터에서 불필요한 부분을 필터링(제거, 변경)하고 필요한 연산을 수행한 뒤

사용자에게 최종 결과를 알려준다.

▶︎ 스토리지 엔진

사용자가 요청한 SQL 문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를

가져오는 역할을 수행한다.

• 데이터를 저장하는 방식에 따라 각각의 스토리 엔진을 사용할 수 있음.
• 필요한 경우 외부의 스토리지 엔진 설치 파일을 가져와 활성화 할 수 있음.

▶︎ MySQL엔진

• SQL문의 시작 및 마무리 단계에 관여
• 스토리지 엔진으로 부터 필요한 데이터만 가져옴

사용자가 요청한 SQL 문을 넘겨 받은 뒤 SQL문법 검사와 적절한 오브젝트 활용 검사를 하고,

SQL문을 최소 단위로 분리하여 원하는 데이터를 빠르게 찾는 경로를 모색하는 역할을 수행한다.

이후 스토리지 엔진으로 부터 전달 받은 데이터 대상으로 불필요한 데이터는 제거, 가공, 연산하는

역할을 한다.

2.1.2 SQL 프로세스 용어

▶︎ 파서(parser)

parser는 MySQL 엔진에 포함되는 오브젝트로, 사용자가 요청한 MySQL 문을 쪼개

최소 단위로 분리하고 트리를 만든다. 트리를 만들면서 문법 검사를 한다.

▶︎ 전처리기(preprocessor)

MySQL 엔진에 해당하는 오브젝트로, 파서에서 생성한 트리를 토대로 SQL 문에 구조적인 문제가

없는지 파악한다.

SQL 문에 작성된 테이블, 열, 함수, 뷰와 같은 오브젝트가 실질적으로 이미 생성된 오브젝트인지,

접근 권한은 부여되어 있는지 확인하는 역할을 한다.

▶︎ 옵티마이져(optimizer)

전달된 파서 트리를 토대로 필요하지 않은 조건은 제거하거나 연산 과정을 단순화한다.

나아가 어떤 순서로 테이블에 접근할지, 인덱스를 사용할지, 사용한다면 어떤 인덱스를 사용할지,

정렬할 때 인덱스를 사용할지 아니면 임시 테이블을 사용할지와 같은 실행 계획을 수립한다.

참고로 옵티마이저가 수립한 실행 계획이 Best는 아닐 가능성도 있다는 점은 알고 있어야한다.

실행 계획을 수립하는 과정 자체만으로도 많은 리소스를 점유하므로, 개발자의 손길이 필요하다.

▶︎ 엔진 실행기(engine executor)

엔진 부하를 줄이기 위해선 스토리지 엔진에서 가져오는 데이터 양을 줄이는 것이 매우 중요!

MySQL 엔진과 스토리지 엔진 영역 모두에 걸치는 오브젝트로, 옵티마이저에서 수립한

실행 계획을 참고하여 스토리지 엔진에서 데이터를 가져온다. 이후 MySQL 엔진에서는

읽어온 데이터를 정렬하거나 조인하고, 불필요한 데이터는 필터링하는 추가 작업을 한다.

2.1.3 DB 오브젝트 용어

▶︎ 테이블

데이터를 저장하는 오브젝트로 행과 열의 정보를 담는다.

▶︎ 로우(행)

테이블에서 동일한 구조의 데이터 항목들의 집합을 가리킨다.

▶︎ 컬럼(열)

▶︎ 기본 키(PK)

• 특정 행을 대표하는 열을 가리키는 용어로 주 키라고도 한다.
• 상황에 따라 2개 이상의 열을 조합해 구성할 수 있다.
• 인덱스의 역할도 수행한다.

MySQL/MariaDB에서 기본 키는 클러스터 형 인덱스로 작동한다.

이는 기본 키의 구성 열 순서를 기준으로 물리적인 스토리지에 데이터가

쌓인다는 뜻이다. 즉, 비슷한 시본 키 값들이 근거리에 적재되므로 기본 키를

활용하여 인덱스 스캔을 수행하면 데이터에 더 빠르게 접근할 수 있다.

▶︎ 외래 키(FK)

외래 키는 외부에 있는 테이블을 항상 참조하면서, 외부 테이블의 데이터가

변경되면 함께 영향을 받는 관계를 설정하는 키이다.

▶︎ 인덱스

인덱스는 데이터베이스에서 키값으로 실제 데이터 위치를 식별하고 데이터 접근

속도를 높이고자 생성되는, 키 기준으로 정렬된 오브젝트이다.

⒈ 고유 인덱스

고유 인덱스란 인덱스를 구성하는 열들의 데이터가 유일하다는 의미이다.

• 기본키 : Not Null
• 고유 인덱스 : Nullable

⒉ 비교유 인덱스

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

데이터가 신규 입력되어 인덱스가 재정렬되더라도 인덱스 열의 중복 체크를

거치지 않고 단순한 정렬 작업을 수행한다.

▶︎ 뷰

뷰는 일명 가상 테이블이라고 이해하면 된다. 물리적으로 잡히지 않는 유령과

같은 오브젝트이다.

아래는 일반 테이블이다.

아래는 뷰 테이블이다.

뷰 테이블은 제한된 정보만을 제공하여, 보안성 측면에서 이점을 얻을 수 있다.

뷰를 사용하는 이유는 일부 데이터에 대해서만 데이터를 공개하고, 노출에 민감한 데이터에

대해서는 제약을 설정할 수 있는 보안성 때문이다. 한편 여러 개의 테이블을 병합해서 활용할

때는 성능을 고려한 최적화된 뷰를 생성함으로써 일관된 성능을 제공할 수 있다.

여기서 드는 생각은 진짜 join이 너무 많아서 성능이 안나오는 경우라면 뷰를 만들어서

작업하는 방법도 나쁘진 않을듯? 근데 결국에 insert를 또 해줘야하는 거니깐 개발하는

입장에서는 번거로울 수도 있을거 같음.

추가적으로 application 레벨에서 view를 생성하는 것이 리소스 측면에서 보다 효율적임.

2.2 논리적인 SQL 개념 용어

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

▶︎ 스칼라 서브쿼리

select 
    name,
    (select count(*) from member where age > 10)
from
    member

▶︎ 인라인 뷰

select 
    s1.name,
    s1.age
from (select * from member where gender = "M") as s1

▶︎ 중첩 서브쿼리

select *
from member
where age = (select avg(age) from member where gender = "M") 

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

▶︎ 비상관 서브쿼리

메인쿼리와 서브쿼리 간에 관계성이 없을을 의미한다.

select * 
from member
where team_name = (select name from team where id = 1)

위 예제를 보면 메인쿼리(외부쿼리)와 서브쿼리(내부쿼리)가 서로 독립적인 관계이다.

실행순서는 메인쿼리 → 서브쿼리이다.

▶︎ 상관 서브쿼리

메인 쿼리와 서브쿼리 간에 관계성이 있음을 의미한다. 서브쿼리가 수행되려면

메인쿼리의 값을 받아야 하므로, 둘은 서로 끈끈한 관계를 유지한다.

이러한 상관 서브 쿼리는 스칼라 서브쿼리와 중첩 서브쿼리일 때 발생한다.

select *
from 학생
where ... in (select ... from 지도교수 where 학생.학번 = ...)

위 쿼리의 전체적인 수행 순서는 아래와 같다.

1. 메인 쿼리 실행 (학생.학번 가져오기)
2. 서브 쿼리 실행 (지도교수.학번 = 학생.학번)
3. 다시 메인 쿼리 실행한 뒤 결과 출력 (select * from 학생 ~ )

2.2.3 반환 결과에 따른 SQL 용어

▶︎ 단일행 서브쿼리

• 서브쿼리 결과가 1건의 행으로 반환되는 쿼리
• 메인 쿼리의 조건절에서는 =, >, < 등의 연산자와 비교

▶︎ 다중행 서브쿼리

• 서브쿼리 결과가 여러 건의 행으로 반환되는 쿼리
• 메인쿼리의 조건절에서는 IN 구문으로 서브쿼리에서 반환되는 값들을 받는다.

▶︎ 다중열 서브쿼리

• 서브쿼리 결과가 여러 개의 열과 행으로 반환
• 메인쿼리의 조건절에서는 IN 구문과 함께 서브쿼리에서 반환된 열들을 동일하게 나열

select ...
from ...
where (이름, 전공코드) IN (select 이름,전공코드 from 학생 where 이름 like '김%')

2.2.4 조인 연산방식 용어

▶︎ 자연조인

자연 조인은 2개 테이블에 동일한 열명이 있을 때 조인 조건절을 따로 작성하지 않아도

자동으로 조인을 수행해주는 방식이다. 조인이 제대로 성사되면 내부 조인과 동일한 결과가

출력된다. 만약 동일한 열명이 없는 경우에는 교차 조인을 수행하게 된다.

2.2.5 조인 알고리즘 용어

▶︎ 드라이빙 테이블

• 먼저 접근하는 테이블
• 가능한한 적은 결과가 반환될 것으로 예상되는 테이블로 선정한다.
• 조인 조건절의 열이 인덱스로 설정되도록 구성해야 한다.

▶︎ 드리븐 테이블

• 나중에 접근하는 테이블

▶︎ 중첩루프 조인

Chap1에서 정리함.

Random Access를 유발하는 인덱스는 기본 키가 아닌 비고유 인덱스일 경우에 해당한다.

기본 키는 클러스터 인덱스이므로 기본 키의 순서대로 테이블의 데이터가 적재되어 있어

조회 효율이 매우높다.

▶︎ 블록 중첩 루프 조인

탄생 배경 : 드리븐 테이블에 인덱스가 없는 경우 때문에..?

• 중첩루프의 효율성을 높이기 위해 탄생
• 드라이빙 테이블에 대해 조인 버퍼(Join Buffer)라는 개념을 도입.

동작원리는 아래와 같다.

1. 드라이빙 테이블에서 조건에 맞는 데이터를 선택한다.
2. 선택된 데이터를 Join Buffer에 가득찰 때까지 적재
3. Join Buffer와 드리븐 테이블의 모든 데이터를 비교한다.

이처럼 Join Buffer의 데이터들과 드리븐 테이블의 한 번의 Table Full Scan으로

원하는 데이터를 모두 찾을 수 있다. 이 과정은 드리븐 테이블의 TFS를 줄이는게

목저으로, 성능 저하를 개선하는 조인 알고리즘 방식이다.

▶︎ 배치 키 엑세스 조인

Random Access의 단점을 해결하고자 접근할 데이터를 미리 예상하고 가져오는 데

착안한 조인 방식이다. BKA 조인은 BNL 조인에서 활용한 Join Buffer 개념을 그대로

사용한다. 그리고 드리븐 테이블에 필요한 데이터를 미리 예측하고 정렬된 담는

랜덤 버터(Random Buffer)의 개념을 도입한다. 이때 드리븐 테이블의 데이터를 예측하고

정렬된 상태로 버퍼에 적재하는 기능을 다중 범위 읽기(Multi Range Read)라고 한다.

동작원리는 아래와 같다.

1. 드라이빙 테이블에서 필요한 데이터를 추출하여 조인 버퍼에 적재한다.
2. 드리븐 테이블의 인덱스 기반으로 필요한 데이터를 예측하여 랜덤 버퍼에 적재한다.
3. 조인 조건절을 기준으로 조인버퍼와 랜덤버퍼를 비교한다.
4. 동일한 데이터가 있을 경우, 드리븐 테이블의 데이터에 접근하고 결과를 조인하여 반환한다.

▶︎ 해시 조인

Chap1에서 정리함.

2.3 개념적인 튜닝 용어

2.3.1 기초 용어

아래는 예제로 이해하기 위해 만든 emplyees 테이블이다.

▶︎ 오브젝트 스캔 유형

⒈ 테이블 풀 스캔

• 인덱스를 거치지 않고 테이블로 바로 직행하여 처음부터 끝까지 데이터를 훑어보는 방식
• 모든 데이터를 검색하므로 성능 측면에서는 부정적으로 해석된다.
• where 절의 조건문이 없거나, 대량의 데이터가 필요할 때

-- 테이블 풀 스캔을 사용하여 employees 테이블의 모든 레코드 검색
SELECT * FROM employees;

⒉ 인덱스 범위 스캔

• 인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾는 방식
• SQL문에서 BETWEEN ~ AND, <, >, LIKE 구문 등 비교 연산 및 구문에 포함될 경우
• 좁은 범위에서는 효율적일 수 있으나, 넓은 범위에서는 비효율적

-- emp_age 열에 인덱스 생성
CREATE INDEX idx_emp_age ON employees(emp_age);

-- emp_age 열에 인덱스를 사용하여 emp_age가 25보다 큰 레코드 검색
SELECT emp_age FROM employees WHERE emp_age > 25;

⒊ 인덱스 풀 스캔

• 인덱스를 처음부터 끝까지 수행하는 방식
• 인덱스로 구성된 열 정보만 요구하는 SQL문에서 인덱스 풀 스캔 수행
• 테이블 풀 스캔 보다는 적은 양을 차지하기 때문에 성능상 유리
• 하지만, 검색 범위를 최대한 줄이는 방향으로 튜닝을 해야 함.

-- emp_age 열에 인덱스 생성
CREATE INDEX idx_emp_age ON employees(emp_age);

-- emp_age 열의 인덱스를 풀 스캔하여 모든 레코드 검색
SELECT emp_age FROM employees ORDER BY emp_age; 

⒋ 인덱스 고유 스캔

• 기본 키나 고유 인덱스로 테이블에 접근하는 방식
• 인덱스를 사용하는 방식 중 가장 효율적인 방식 → 고유한 값만 읽기 때문
• ‘단일 값’을 검색할 때에만 사용할 수 있기 때문에 제한적인 경우에만 사용
• where 절에 = 조건으로 작성하며, 해당 조인 열이 기본 키 또는 고유 인덱스의 선두 열로 설정되었을 때 확용된다.

-- emp_id(기본 키) 열에 인덱스 생성
CREATE INDEX idx_emp_id ON employees(emp_id);

-- 인덱스 고유 스캔을 사용하여 emp_id가 3인 레코드 검색
SELECT * FROM employees WHERE emp_id = 3;

⒌ 인덱스 루스 스캔

• 인덴스의 필요한 부분만 골라서 스캔하는 방식
• WHERE 절 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스키는 무시
• 보통 GROUP BY, MAX(), MIN() 함수가 포함되면 동작

-- emp_age 열에 인덱스 생성
CREATE INDEX idx_emp_age ON employees(emp_age);

-- 인덱스 루스 스캔을 사용하여 emp_age가 25보다 큰 모든 레코드 검색
SELECT emp_age FROM employees WHERE emp_age > 25;

위의 SQL문에서 WHERE emp_age > 25 부분이 인덱스 루스 스캔을 활용하는 부분이다.

이 쿼리를 실행할 때, 데이터베이스는 emp_age 열의 인덱스를 사용하여 emp_age가

25보다 큰 레코드만 검색한다. 이는 인덱스를 통해 해당 조건을 만족하는 레코드들을

빠르게 찾아내기 때문에 성능상 이점을 얻을 수 있습니다.

⒍ 인덱스 병합 스캔

• 테이블 내에 생송된 인덱스들을 통합해서 스캔하는 방식
• 통합하는 방법으로는 union과 intersetion 방식이 있다.
• OR 조건이 포함된 쿼리에서 각각의 조건에 해당하는 인덱스를 사용하는 경우에 발생
• UNION ALL 연산자를 수행하는 경우에 발생

-- emp_age, emp_department 열에 인덱스 생성
CREATE INDEX idx_emp_age ON employees(emp_age);
CREATE INDEX idx_emp_department ON employees(emp_department);

-- 인덱스 병합 스캔을 사용하여 emp_age가 25보다 크거나 emp_department가 'Engineering'인 레코드 검색
SELECT emp_age, emp_department FROM employees 
WHERE emp_age > 25 OR emp_department = 'Engineering';

위의 SQL문에서 WHERE emp_age > 25 OR emp_department = 'Engineering' 부분이 인덱스 병합 스캔을

활용하는 부분이다. 이 쿼리를 실행할 때, 데이터베이스는 emp_age와 emp_department 열의 인덱스를

병합하여 두 조건 중 하나 이상을 만족하는 레코드들을 검색한다. 이렇게 병합 스캔을 사용하면 두 개의

인덱스를 따로 검색하는 것보다 효율적으로 데이터를 찾아낼 수 있다.

▶︎ 디스크 접근 방식

⒈ 시퀀셜 엑세스

• 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식
• 보통 테이블 풀 스캔에서 활용
• 디스크 헤더의 움직임을 최소화하여 작업 시간과 리소르 점유 비용을 줄인다.
• Tabl Full Scan일 때는 인접한 페이지를 여러 개 읽는 다중 페이지 읽기(Multi-Page-Read) 방식으로 수행
• BigInt는 순차 접근이 가능하다.

⒉ 랜덤 엑세스

• 물리적으로 떨어지 페이지들에 임의로 접근하는 방식
• 디스크의 물리적인 움직임이 필요하고 MPR이 불가능, 데이터의 접근 수행시간이 오래 걸림
• 따라서 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이게 끔 튜닝해줘야 함
• UUID는 Random Access를 유발.

▶︎ 조건 유형

1. WHERE 절 조건문 기준으로 데이터가 저장된 디스크에 접근
2. 필요한 데이터에 access하는 조건문으로 데이터를 가져옴
3. 가져온 데이터에서 다시 한번 출력할 데이터만 추출

위 과정에서 처음 디스크에서 데이터를 검색하는 조건을 엑세스 조건이라고 하고,

디스크에서 가져온 데이터에서 추가로 추출하거나 가공 및 연산하는 조건을 필터 조건이라 한다.

⒈ 엑세스 조건

• SQL 튜닝에서 가장 중요한 핵심 사항

SELECT *
FROM TEAM as t
WHERE t.id = 1 AND t.league_name = 'EPL';

id 열로 생성된 인덱스를 활용해서 TEAM 테이블의 일부 데이터에 접근한다.

즉, t.id = 1 조건문이 엑세스 조건이 된다. 만약 t.league_name = 'EPL' 조건문이

엑섹스 조건이 된다면, 인덱스 활용 없이 대량의 데이터에 접근할 것으로 예측할 수 있다.

⒉ 필터 조건

• 엑세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건
• 위 예제에서 사용한 SQL문에서는 t.league_name = 'EPL'을 적용해 필터링 작업을 한다.
• 필터 조건으로 제거되는 데이터 비율을 확인하고 특정 SQL 문이 튜닝이 필요한지 판당할 수 있다.

2.3.2 응용 용어

▶︎ 선택도

• 테이블의 특정 열을 기준으로 해당 열의 조건절에 따라 선택되는 데이터 비유
• 중복되는 데이터가 많다 = 선택도가 높다
• 낮은 선택도가 대용량 데이터에서 원하느 데이터만 골라내는 능력이라고 생각할 수 있다.
• 따라서 낮은 선택도를 가지는 열은 데이터를 조회하는 SQL 문에서 원하는 데이터를 빨리 찾기 위한 인덱스 열을 생성할 떄 주요 고려대상이 된다.
• 선택도 = 선택한 데이터 건수 / 전체 데이터 건수
• 변형된 선택도 = 1 / DISTINCT(COUNT 열명)

▶︎ 카디널리티

• 사전적 의미 : 하나의 데이터 유형으로 정의되는 데이터 행의 개수
• 현업 : 전체 행에 대한 특정 중복 수치를 나타내는 지표
• 카디널리티 = 전체 데이터 건수 x 선택도
• “유일하다”라고 생각하기

▶︎ 힌트

• 데이터를 빨리 찾을 수 있게 추가 정보를 전달하는 것

SELECT 학번, 전공코드
FROM 학생 USE INDEX (학생_IDX01)
WHERE 이름 = '홍길동';

명시적으로 힌트를 작성해도 옵티마이저는 무조건 힌트를 참고하지 않는다.

옵티마이저가 비효율적이라고 예측하면 사용자가 작성한 힌트는 무시될 수 있다.

기존에 힌트를 적용했더라도 상황에 따라 힌트가 삭제 및 변경될 수 있다.

따라서 SQL문에 직접 힌트를 작성하기보다는 별도로 관리하는 것이 장애 방지에 도움이 된다.

▶︎ 콜레이션

• 특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙을 의미
• 열의 콜레이션이 우선순위를 가진다.

▶︎ 통계 정보

튜닝을 해도 성능이 안나오면 고려할만한 옵션

• 시스템 변수를 통해 통계 정보를 관리할 수 있다.
• 테이블 통계 정보, 인덱스 통계정보, 선택적인 열 통계정보
• 최신성 유지 및 관리가 매우 중요
• 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보

▶︎ 히스토그램

튜닝을 해도 성능이 안나오면 고려할만한 옵션

• 테이블의 열 값이 어떻게 분포되어 있는지를 확인하는 통계정보
• 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보
• MySQL 내부적으로 열의 분포를 저장할 때는 높이 균형 히스토그램 방식을 사용한다.