1. MySQL 서버 = MySQL 엔진 + 스토리지 엔진 (물리 엔진 용어)
MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 나눠볼 수 있다.
MySQL 엔진은 클라이언트로부터 오는 요청 처리(요청된 SQL 문장을 분석, 최적화, ...)를 담당하고, 스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 조회하는 부분을 담당한다.
1) MySQL 엔진
사용자가 요청한 SQL 문을 넘겨받은 뒤 SQL 문법 검사와 적절한 오브젝트 활용 검사를 하고, SQL 문을 최소 단위로 분리하여 원하는 데이터를 빠르게 찾는 경로를 모색하는 역할을 수행한다. 또한 스토리지 엔진으로부터 전달받은 데이터 중 불필요한 데이터는 제거하거나 가공 및 연산하는 역할을 한다. 즉, SQL 문의 시작 및 마무리 단계에 MySQL 엔진이 관여하며, 스토리지 엔진으로부터 필요한 데이터만을 가져오는 핵심 역할을 담당한다고 할 수 있다.
- Connection Handler : 커넥션 및 쿼리 요청을 처리 담당
- SQL 인터페이스 : DML, DDL, Procedure, View 등 SQL 인터페이스 제공 담당
- SQL 파서(parser) : SQL문법 오류 탐지 및 SQL 쿼리 문장을 MySQL이 처리하기 좋은 토큰 단위로 나눠서 트리 형태로 파싱 하는 작업 담당
- SQL 옵티마이저(optimizer) : 쿼리의 최적화된 실행 담당
- 캐시와 버퍼 : 성능 향상을 위한 보조 저장소 기능 담당
2) 스토리지 엔진
서버 엔진이 필요한 데이터를 물리적인 장치에서 가져오는 역할을 한다. 플러그인 방식으로 스토리지 엔진을 필요에 따라 더 추가하거나 삭제할 수 있다. 즉, MySQL에서 스토리지 엔진은 여러 개를 동시에 사용할 수 있고, 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 해당 스토리지 엔진이 처리하므로 상황에 맞는 스토리지 엔진을 선택하는 것이 중요하다.
- 실제 데이터를 디스크 스토리지에 저장하거나 조회하는 부분을 담당
- 요청된 SQL 문장을 분석하거나 최적화 담당
- 스토리지 엔진은 MySQL 엔진과 플러그인 형태로 연동/분리 가능하고 핸들러 API(핸들러 요청)를 통해 스토리지 엔진에 읽기/쓰기 요청이 가능
- 일반적으로 OLTP환경이 대다수인 만큼 주로 InnoDB 엔진을 사용하고. 그 외에 대량의 쓰기 트랜잭션이 발생하면 MyISAM 엔진을, 메모리 데이터를 로드하여 빠르게 읽는 효과를 내려면 Memory 엔진을 사용하는 식으로 응용하여 스토리지 엔진을 선택
2-2) 스토리지 엔진 비교
＃ MyISAM
1. 특징
- MySQL 5.5 이전까지 기본 스토리지 엔진
- 데이터를 매우 효율적으로 저장하고 데이터 저장에 실제적인 제한이 없음
- 트랜잭션은 지원하지 않음
- 테이블 레벨의 락을 지원 (안전하지만, 작업시 특정행을 수정하려고 하면 테이블 전체에 락이 걸려서 다른사람이 작업할수없다)
- 백업, 복구 지원
2. 장단점
2.1) 장점
- 데이터 모델이 단순하고 직관적
- 속도가 빠름
- Full Text 인덱싱이 가능해서 복합 검색이 가능함
2.2) 단점
- 테이블 전체에 락을 걸기 때문에 갱신이 많은 시스템에서는 불리함 (Insert, Update, Delete)
- 트랜젝션을 지원하지 않으므로 무결성이 보장되지 않음
3. 적합한 프로젝트
- 트래픽이 많은 웹
- 정적인 Table
- Select 위주의 테이블
- 갱신을 자주 하지 않는 시스템
============================================
＃ InnoDB
1. 특징
- MySQL 5.5 부터 기본 스토리지 엔진
- ACID 트랜잭션 지원
- 데이터 로드 속도가 느림
- 데이터 압축이 불가능
- 백업, 복구 지원
- 테이블 레벨이 아닌 ROW 레벨의 락을 지원
- 외래키 지원
- 자동 에러 복구
2. 장단점
2.1) 장점
- 트랜젝션을 통한 데이터 무결성이 보장
- 제약조건, 외래키 생성 가능
- 장애를 자동으로 복구할 수 있음
- Row 레벨 락을 사용하기 때문에 Insert, Update, Delete 속도가 빠름
2.2) 단점
- 기능이 많아서 데이터 모델에 큰 노력 필요
- 시스템 자원을 많이 사용함
- Full Text 인덱싱이 안됨
3. 적합한 프로젝트
- 데이터 입력 및 수정이 잦은 시스템에서 적합
- 온라인 트랜젝션을 지원하는 시스템
- 보안성이 요구 되는 테이블 (트랜젝션이 가능하기 때문)
- 대량의 Table
MyISAM 심화 정리
· MySQL 5.5 이전까지 기본 스토리지 엔진
· Data 저장에 실제적인 제한이 없다.(논리적-물리적 제한은 있음)
· Data를 매우 효율적으로 저장한다.
· 빈번한 data 사용시 효과적이다.(select문)
· 블로그나 게시판처럼 한사람이 글을 쓰면 다른 많은 사람이 글을 읽는 방식에 최적의 성능을 발휘한다.
· index는 B-Tree, R-tree, Full-text Index를 지원한다.
· 특정 Index에 대한 Memory Cache를 지원한다.
· data 압축에 대한 옵션을 제공한다.
· 지리적 Data를 지원한다.
· table 단위의 lock을 제공한다.
· non-transactional-safe : transaction을 제공하지 않는다.
· backup 및 특정 시점으로 복구를 지원한다.
· .frm : table 구조 정보(스키마 정보), .myd : data, .myl : index 정보
· index만 MySQL서버가 관리하고, data는 관리하지 않는다.(data는 OS캐싱에 의존한다.)
· 적합한 사용처
· 트레픽이 많은 웹사이트
· Data ware house
· 정적인 table, 로그 table
· 쓰기작업이 별로 없는 select 위주의 table.
· current insert기능이 read시에 insert가 가능하게 하므로 로그 table에 사용될 수 있다.
InnoDB 심화정리
· MySQL 5.5 부터 기본 스토리지 엔진이다.
· transactional-safe : ACID Transaction 지원한다. space당 64TB Data의 저장을 지원힌다.
· MyISAM보다 data 저장 비율이 낮다.
· MyISAM에 비해 약1.5~2.5배 정도의 큰 파일 사용한다.
· MVCC/Snapshot read를 지원한다.
· 다른 Engine들에 비해 data 로딩 속도가 느리다.
· index는 B-Tree, Clustered를 지원한다.
· 특정 data와 index에 대한 Memory Cache를 지원한다.
· 외부키(foreign key)를 지원한다.
· data 압축 옵션을 제공하지 않는다.
· row 단위 lock을 제공한다.
· 자동 에러 복구 기능을 지원한다.
· backup 및 특정 시점으로 복구를 지원한다.
· index와 data를 table space개념을 사용하여 저장한다.
· 적합한 사용처
· Online Transaction을 지원하는 Application
· 민감한 정보를 갖는 table(회원table, 돈에 관련된 table)
· 갱신(읽기/쓰기) 위주의 트랜잭션이 요구되는 table.
· index가 많이 걸린 대량의 table.
3) 핸들러 API
쿼리 실행기에서 데이터를 쓰고나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는 요청을 핸들러 요청이라고 하는데, 여기에 사용되는 API를 핸들러 API 라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받는다.
2. SQL문의 처리 과정
1) 예시 : 서울에서 택시 운전을 하는 택시 기사 A 씨에 비유
1. 첫 번째 손님을 받았다. 탑승한 손님은 몽골어로 "bycan pyy Asuraas."라고 요 구했으나 무슨 뜻인지 알 수 없어 목적지까지 안내할 수 없었다.
2. 두 번째 손님은 "뉴욕으로 가주세요"라고 요구했고, A 씨는 어떤 요구사항인지 이해했으나 한국에는 뉴욕이라는 도시가 없으므로 목적지까지 안내할 수 없었다.
3. 세번째 손님은 "부산으로 가주세요"라고 요구했고, A 씨는 손님이 원하는 내용을 충분히 이해했고, 부산의 위치도 알고 있었기 때문에 짧고 빠른 경로로 손님을 목적지까지 안내했다.
2) 실제 SQL 수행 과정 쿼리 실행 구조
- 파서(Parser)가 들어온 쿼리 문장에 대해서 문법(Syntax) 확인을 하고 쿼리 문장을 적절한 단위로 분할하여 파서 트리(parser tree)를 만든다. 트리의 최소 단위는 >, <, = 등의 기호나 SQL 키워드로 분리하며, 트리를 만드는 과정에서 문법 오류가 있는지 검토한다. 트리에 허용되지 않는 문법이 포함된다면 에러 발생과 동시에 실행을 종료한다. (기본 문법 오류를 이 과정에서 발견)
- 전처리기(pre-processor)가 앞에서 만들어진 파서 트리를 보고 구조적으로 문제가 있는지 확인한다. (예를 들면 없는 테이블, 컬럼 등에 접근한다거나 테이블에 접근하려고 보니 권한이 없다거나 하는 등...). 만약 유효하지 않은 오브젝트가 있거나 권한이 없는 오브젝트를 호출하면 바로 에러를 발생시킨다.
- 옵티마이저(Optimizer)가 요청받은 쿼리를 어떻게하면 가장 적은 비용으로 빠르게 즉, 최적으로 처리할지를 결정한다.
- 실행 엔진은 옵티마이저에 의해 결정된 실행 계획대로 핸들러에게 지시한다. (임시테이블 만들어라 → where 조건대로 레코드 읽어와라 → 읽은 레코드를 임시 테이블에 써라 → ...)
- 핸들러(스토리지 엔진)는 실행 엔진이 내려준 지시대로 작업을 수행한다.
3) SQL 수행 과정의 핵심 오브젝트 정리
1.파서(Parser) :
- 사용자가 요청한 SQL문을 쪼개 최소 단위로 분리하고 트리를 만들면서 문법 검사를 수행
2.전처리기(preprocessor):
- 파서에서 생성한 트리를 토대로 SQL 문에 구조적인 문제가 없는지 파악. SQL 문에 작성된 테이블, 열, 함수, 뷰와 같은 오브젝트가 실질적으로 이미 생성된 오브젝트인지, 접근 권한은 부여되어 있는지 확인하는 역할
3.옵티마이저(optimizer) :
- MySQL 의 핵심 엔진 중 하나. 전달된 파서 트리를 토대로 필요하지 않은 조건은 제거하거나 연산 과정을 단순화한다. 나아가 어떤 순서로 테이블에 접근할지, 인덱스를 사용 할지, 사용한다면 어떤 인덱스를 사용할지, 정렬할 때 인덱스를 사용할지 아니면 임시 테이블을 사용할지와 같은 실행 계획을 수립
- 단, 실행 계획을 수립하는 작업 자체만으로도 대기 시간과 하드웨어 리소스를 점유하므로, 시간과 리소스에 제한을 두고 실행 계획을 선정해야 한다. 만약 실행 계획으로 도출할 수 있는 경우의 수가 지나치게 많을 때는 각각의 비용 산정 및 최적의 실행 계획을 선택하기까지 시간이 오래 걸리므로 모든 실행 계획을 판단하지는 않는다. 따라서 옵티마이저가 선택한 최적의 실행 계획이 항상 최상의 실행 계획이 아닐 가능성도 있다.
4. 실행 엔진 :
- 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결 하는 역할
- 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행
GROUP BY 예시 1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청 2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청 3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청 4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청 5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김 => 임시 테이블 생성 - WHERE 조건 일치하는 데이터 읽기 - 임시 테이블에 저장 - 다시 조건 수행 - 결과 전송
5. 핸들러(스토리지 엔진)
- MySQL 서버 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할
- 핸들러는 결국 스토지지 엔진을 의미하며 , InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 됨
옵티마이저는 회사의 경영진, 실행 엔진은 중간관리자 , 핸들러는 각 업무의 실무자로 비유
3. 테이블 관련 용어 (DB 오브젝트 용어)
테이블
- 데이터를 저장하는 오브젝트로 행과 열의 정보를 담는다. 관계형 데이터베이스인 MySQL은 2차원 배열 형태로 테이블을 관리한다.
- 테이블에서는 저장 방식과 저장 구조에 따라 스토리지 엔진 속성을 정의할 수 있습니다. InnoDB 스토리지 엔진은 보동 OLTP 환경에서 주로 사용하는 기본 DB 엔진이며 그 외에도 MyISAM, Memory, Blackhole 엔진 등이 존재한다.
행(row)
- 테이블에서 동일한 구조의 데이터 항목들의 집합. 위 예시에선 '10001 / 홍길동/20001021/010-123-4567/CS' 라는 5개의 데이터 항목이 하나의 행을 이룬다. 행은 하나의 데이터 항목 집합이며 모든 행의 집합을 테이블이라고 할 수 있다.
열(column)
- 사전에 정의한 데이터 유형으로 데이터값을 저장하며, 열별로 다른 데이터 유형을 가질 수 있다.
기본키(primary key)
- 특정 행을 대표하는 열을 가리키는 용어. 기본키는 인덱싱의 역할도 수행한다. MYSQL/Maria DB에서 기본키는 클러스터형 인덱스로 작동한다. (기본키의 구성 열 순서를 기준으로 물리 스토리지에 데이터가 쌓임)
외래키(Foreign key)
- 외부에 있는 테이블을 항상 참조하면서, 외부 테이블의 데이터가 변경되면 함께 영향을 받는 관계를 설정하는 키. 외부 테이블을 부모 테이블, 외부 테이블을 참조하는 테이블을 자식 테이블이라고 생각하면 이해가 쉽다.
인덱스(index)
- DB에서 키값으로 실제 데이터 위치를 식별하고 데이터 접근 속도를 이고자 생성되는 정렬된 오브젝트
뷰(view)
- 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블. 기본 테이블은 디스크에 공간이 할당되어 데이터를 저장하지만, 뷰는 데이터 딕셔너리(Data Dictionary) 테이블에 뷰에 대한 정의만 저장되어 디스크 저장 공간 할당이 이루어지지 않는다. 또한 전체 데이터 중에서 일부만 접근할 수 있도록 할 수도 있다.
- 뷰를 사용하는 이유는 노출에 민감한 데이터에 대해 제약을 설정할 수 있는 보안성이 있기 때문이고, 복잡한 질의문을 단순화 하여 질의 성능을 높일 수 있기 때문
4. 서브쿼리 관련 용어
Subquery
- SQL문 하부의 FROM 이나 WHERE 절에서 사용되는 쿼리로, 비교형태로 사용됨
- SELECT 문 안쪽에 위치한 SELECT 문은 어느 위치에 작성되었는지에 따라 부르는 용어가 달라진다.
예) 부서번호 100인 직원들 중 평균급여보다 많이받는 직원 출력
SELECT * FROM employees
WHERE salary > ( SELECT avg(salary)
FROM employees
WHERE department_id = 100 );
1) 서브쿼리 위치에 따른 용어
1. Scala subquery
- scala (sql에서 단일값을 칭함) + subquery , 단일값을 갖는 subquery
- SELECT 절에서 함수처럼 사용되는 쿼리문
- 반환값은 항상 1개여야 한다. ( 메인쿼리의 SELECT 절에는 최종 출력하려는 열들이 나열되므로, 출력 데이터 1건과 스칼라 서브쿼리의 결과 건수가 일치해야 한다. 즉, 스칼라 서브쿼리의 결끈값은 1행 1열의 구조 로 출력되어야 한다.)
- 보통 스칼라 서브쿼리는 출력되는 데이터 건수가 1건이어야 하므로 집계함수 (max. nin, avg. Sum, count 등)가 자주 쓰임
예) 직원정보 및 평균급여 출력하되, 평균급여는 각 행에 모두 출력이 되게 하며, 컬럼명은 avg
SELECT employee_id, last_name, salary, job_id,
‘평균급여: ’ || (SELECT trunc(AVG(salary)) FROM employees) as AVG
FROM employees;
2. Inline view
- FROM절에 있는 subquery문
- TOP-N, ROWNUM 사용 가능
- 인라인 뷰의 결과는 내부적으로 메모리 혹은 디스크에 임시 테이블을 생성하여 활용한다.
예) SELECT * FROM (
SELECT * FROM EMPLOYEES --> select와 * 사이에 존재하는 rownum
WHERE department_id = 50
)
WHERE rownum <= 5;
3. 중첩 서브쿼리
- WHERE 절에 있는 subquery문
- 보통 비교 연산자 ( . . . , , !)를 비롯해 IN, EXISTS, NOT IN, NOT EXISTS 문을 많이 사용
- 한 개의 값만 필요한 경우 단일행 서브쿼리가 되고, IN 등 여러 개의 값이 필요한 경우 다중행 서브쿼리가 된다
예 ) SELECT *
FROM STUDENT
WHERE ID IN ( SELECT ID FROM CS_STUDENT)
4) 참고) with절
FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어지기 때문에 사용
WITH
[별칭1] AS (SELECT문 1),
[별칭2] AS (SELECT문 2),
...,
[별칭N] AS (SELECT문 N)
SELECT
FROM 별칭1, 별칭2, ..., 별칭NWITH E AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D AS (SELECT * FROM DEPT)
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM E, D
WHERE E.DEPTNO = D.DEPTNO;2) 메인쿼리와의 관계성에 따른 용어
1. 비상관 서브쿼리(NON CORRELATED SUBQUERY)
- 메인쿼리와 관련 없는 서브쿼리
- 서브쿼리 자체만으로도 실행이 가능한 쿼리
- 서브쿼리가 독자적으로 실행된 뒤 메인쿼리에게 그 결과를 던져주는 형태의 쿼리
- 비상관 서브쿼리에서는 서브쿼리가 먼저 실행된 뒤에 그 결과를 메인쿼리가 활용. 즉, 서브쿼리 실행 → 메인쿼리 실행의 순서로 실행
SELECT *
FROM 학생
WHERE 학번 IN ( SELECT 학번
FROM 학생
WHERE 성별 = '남')- 예제의 비상관 서브쿼리는 성별 = '남' 조건으로 학생 테이블에서 데이터를 가져온 뒤 그 결과를 메인쿼리의 학생 테이블로 전달하여 최종 데이터를 출력한다. 이때 DB 버전 및 옵티 마이저에 따라 서브쿼리가 제거되고 하나의 메인쿼리로 통합되는 뷰 병합(view merging), 즉 SQL 재작성(rewrite) 이 작동할 수도 있다.
2. 상관 서브쿼리(CORRELATED SUBQUERY)
- 메인 쿼리와 상관 관계가 있는 쿼리
- 서브 쿼리가 수행되려면 메인쿼리의 값을 받아야 함
- 상관 서브쿼리는 SELECT 절에 작성하는 스칼라 서브쿼리와 WHERE 절에 작성하는 중첩 서브쿼리일 때 발생
- 메인 쿼리 실행 (학생, 학번 데이터 가져오기) → 서브쿼리 실행 (지도교수 학번 · 학생 학번) → 다시 메인쿼리 실행한 뒤 결과 출력 (SELECT * FROM 학생)과 같습니다. 다만 이때도 DB 버전 및 옵티마이저에 따라 서브쿼리가 제거되고 하나의 메인쿼리로 통합되는 뷰 병합, 즉 SQL 재작성 으로 작동할 수 있다.
3) 메인쿼리와의 관계성에 따른 용어
1. 단일행 서브쿼리
- single-row subquery : 실행 결과가 단 하나의 행으로 나오는 서브쿼리
- 단일행 연산자(>, >=, =, <=, <, <>, ^=, !=)를 사용하여 값 비교
-- JONES의 급여보다 높은 급여를 받는 사원 정보 (서브쿼리 이용)
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'JONES'); -- 참고로, 이때 JONES라는 이름이 여러 개 존재하면 에러발생-- 서브쿼리 결과 값이 날짜형 (SCOTT보다 빨리 입사한 사원 목록)
SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
FROM EMP
WHERE ENAME = 'SCOTT'); -- 참고로, 이때 JONES라는 이름이 여러 개 존재하면 에러발생
2. 다중행 서브쿼리(multiple-row subquery)
- 실행 결과 행이 여러 개로 나오는 서브쿼리
- 다중행 연산자(IN, ANY, SOME, ALL, EXISTS)를 사용하여 값 비교
- IN : 하나라도 일치한 데이터가 있다면 TRUE
- ANY, SOME : 만족하는 결과가 하나 이상이면 TRUE
- ALL : 결과가 모두 만족하면 TRUE
- EXISTS : 결과가 존재하면 TRUE (행이 1개 이상이면 TRUE)
-- <IN> 부서별 최고 급여와 동일한 급여를 받는 사원 정보
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);-- <SOME> 등가 비교 연산자(=)와 ANY, SOME을 함께 사용하면 IN 연산자와 같은 기능 수행
SELECT *
FROM EMP
WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
3. 다중 열 서브쿼리(multiple-column subquery )
- 서브쿼리 결과가 여러 개의 열과 행으로 반환
- 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);
5. 조인(Join) 관련 용어
0) 접근하는 테이블 선후관계에 따른 용어
조인시 먼저 액세스되는 쪽을 드라이빙 테이블(DRIVING TABLE, OUTER TABLE)이라고 하며,
나중에 액세스 되는 테이블을 드리븐 테이블(DRIVEN TABLE, INNER TABLE)이라고 한다.
인덱스(INDEX)의 존재 및 우선순위 혹은 FROM절에서의 TABLE 지정 순서에 영향을 받으며, 어느 테이블이 먼저 엑세스되느냐에 따라 속도의 차이가 크게 날 수 있으므로 많은 양의 데이터를 다룰 때, 드라이빙 테이블은 중요하다.
EX) 조건을 만족하는 5000만 건인 A테이블과 조건을 만족하는 1000건인 B테이블을 조인
=> A를 먼저 드라이빙하면 5000만번을 반복하며 B테이블을 탐색하고, B를 먼저 드라이빙하면 1000번 A테이블 탐색 반복
=> 즉, 작업 대상이 되는 행(rows)의 수가 적은 테이블부터 액세스 되어야 전체 탐색이 줄어든다.
# 결정 규칙
1. 규칙기반 옵티마이저(Rule-Based Optimizer, RBO)에서는 연산자, 인덱스의 유무, 조건절형태 등 정해진 규칙의 우선순위에 따라 실행계획을 생성한다.
테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이므로, 통계정보(레코드 건수, 컬럼 분포도)를 조사하지 않고, 실행계획이 수립되기 때문에 쿼리가 거의 항상 같은 실행 방법을 만들어낸다.
규칙기반 최적화는 인덱스의 통계정보가 거의 없고 상대적으로 느린 CPU 연산탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다.
- 인덱스를 이용한 액세스 방식 > 전체 테이블 액세스 방식
- 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재할 때, 우선순위가 높은 테이블 선택
- 만약 조인 테이블의 우선순위가 동일하지않다면, FROM 절에 나열된 테이블의 역순으로 수행
- 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재할 때, 우선순위가 높은 테이블을 선택
2. 비용기반 옵티마이저(Cost-Based Optimizer, CBO)는 쿼리를 수행하는데 소요되는 예상 비용을 바탕으로 실행계획을 생성한다. 통계정보, DBMS 설정정보, DBMS 버전 등의 차이로 인해 똑같은 SQL문이라도 서로 다른 실행계획이 생성될 수 있다.
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;1 ) 두 칼럼 모두 각각 인덱스가 있는 경우
각 테이블의 레코드 건수에 따라 employees 가 드라이빙 테이블이 될 수도 있고, dept_emp 테이블이 드라이빙 테이블이 될 수도 있다.
2) dept_emp.emp_no 에만 인덱스가 있는 경우
employees 테이블의 반복된 풀 스캔을 막기 위해 employees 테이블을 드라이빙 테이블로 선택하고, 인덱스가 있는 dept_emp 테이블을 드리븐 테이블로 조인을 수행하도록 실행 계획을 수립한다.
3) employees.emp_no에만 인덱스가 있는 경우
ept_emp 테이블을 드라이빙 테이블로, employees 테이블을 드리븐 테이블로 선택하게 된다.
4) 두 칼럼 모두 인덱스가 없는 경우
어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 스캔되는 레코드 수에 따라 적절히 드라이빙 테이블을 선택하게 된다.
조인이 수행될때 양쪽 테이블의 칼럼에 모두 인덱스가 없을 때만 드리븐 테이블을 풀스캔한다. 나머지 경우에는 드라이빙 테이블을 풀 테이블 스캔을 사용할 수는 있어도 드리븐 테이블을 풀 테이블 스캔으로 접근하는 실행 계획은 옵티마이저가 거의 만들어내지 않는다.
비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행한다.
1) Join 방식에 따른 용어
* Join의 종류 (논리적 Join)
- INNER Join
- OUTER Join
- CROSS Join
- LEFT Join
- RIGTH Join
* Join의 방식 (물리적 Join)
- Nested Loop - 루프를 돌며 두 테이블의 로우를 연결하는 형식
- Sort-Merge - 참조할 인덱스가 없는 두테이블을 정렬하여, 순차적으로 연결시켜주는 방식
- Hash - 두테이블이 너무 큰 경우, 해쉬맵에 저장하여, 연결시켜 조인하는 방식
1. Nested Loop
- 드라이빙 테이블의 데이터 1건 당 드리븐 테이블을 반복 검색하며, 양쪽 테이블에 공통된 데이터를 출력
원리
1. 선행테이블에서 첫번째 행을 접근.
2. 그 행의 조인할 속성을가지고 후행테이블 인덱스를 거쳐 접근
3. 1-2번의 작업을 반복하여 맵핑하며 결과를 버퍼에 저장하여 마지막에 출력함
특징
- 랜덤 액세스 방식 - 레코드 하나를 읽기 위해 블록 전체 Read (대량 데이터 조인시 NL조인 불리한 이유)
- 한 레코드씩 순차 진행 - 부분범위 처리가 가능한 상황에서 빠른 응답
- 인덱스 구성 전략 중요 - 조인 컬럼 인덱스가 있는지, 없는지 등..
- 결과적으로 소량 데이터 처리, 부분 범위 처리가 가능한 OLTP(온라인 트랜잭션 처리 시스템)에 적합
- 조인 컬럼에 인덱스가 존재해야 함
추가 설명
- 랜덤 엑세스의 예로는 인덱스 스캔(Index Scan)이 있다. 즉, 인덱스 스캔은 NL방식으로 조인을 수행한다.
1-2. Block nested Loop Join(BNL)
학생 테이블이 드라이빙 테이블이고, 비상연락망 테이블은 인덱스 없이 생성되어있다고 하자.
이 때, NL 조인을 수행한다면 학생 인덱스로 1번 학생 데이터를 찾은 뒤, 인덱스가 없는 비상연락망 테이블 전체 데이터에 모두 접근해야 한다. 즉, 인덱스가 없는 드리븐 테이블에 대해 매번 전체 데이터를 비효율적으로 검색해야 한다.
이 때, 중첩 루프 조인의 효율성을 높이고자 탄생한 것이 바로 블록 중첩 루프 조인이며, 드라이빙 테이블에 대해 조인 버퍼를 도입한 개념이다.
원리
1. 드라이빙 테이블인 학생 테이블에서 학번 1과 100에 해당하는 데이터를 검색한다.
2. 검색된 데이터를 조인 버퍼에 가득 채워질 때까지 적재한다.
3. 조인 버퍼와 비상연락망 테이블(드라이븐 테이블)의 데이터를 비교한다. 즉 조인 버퍼와 2번 데이터를 조인하고, 다시 조인 버퍼와 3 데이터를 조인하는 식으로 반복하여 비상연락망 데이터에 모두 접근한다.
4. 조인 버퍼의 데이터들과 비상연락망 테이블의 한 번의 테이블 풀 스캔 으로 원하는 데이터를 모두 찾을 수 있다.
1-3) Batched Key Acess Join(BKA)
NL 조인 방식은 필연적으로 데이터 접근 시 인덱스에 의한 랜덤 액세스가 발생하므로, 액세스할 데이터의 범위가 넓다면 분명 비효율적인 조인 방식이다. 이러한 랜덤 액세스의 단점을 해결하고자 접근할 데이터를 미리 예상하고 가져오는데 착안한 알고리즘을 배치 키 액세스 조인 batched key access toin (BKA 조인) 이라고 한다.
방법
1. 드라이빙 테이블에서 필요한 데이터를 추출하여 조인 버퍼에 적재한다.
2. 드리븐 테이블의 인덱스 기반으로 필요한 데이터를 예측하여 랜덤 버퍼에 적재한다.
3. 학생.학번 = 비상연락망.학번에 대해 조인 조건절로 비교하여 동일한 데이터가 존재 시
4. 드리븐 테이블에 데이터에 접근하여 결과를 조인하여 반환
특징
1. BKA 조인은 BNL 조인에서 활용한 조인 버퍼 개념과 드리븐 테이블에 필요한 데이터를 미리 예측하고 정렬된 상태로 담는 랜덤 버퍼의 개념을 도입한다.
2. 드리븐 테이블의 데이터를 예측하고 정렬된 상태로 버퍼에 적재하는 기능을 다중 범위 읽기(multi range read, MRR)라고 한다. 즉, 미리 예측된 데이터를 가져 와 정렬된 상태에서 랜덤 버퍼에 담기 때문에, 드리븐 테이블에 대해 랜덤 액세스가 아닌 시퀀셜 액세스를 수행하는 방식
2. Sort-Merge Join
NL Join에서의 랜덤 액세스로는 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법으로, 주로 Full Table Scan 방식으로 데이터를 읽는 기법이다.
방법
1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행
1 ~ 2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
3. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
4. 후행 테이블의 조인 키를 기준으로 정렬 작업을 수행
3 ~ 4번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
5. 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣음
NL과의 차이점
1. 인덱스가 없어도 가능한 조인법
2. 조인시 '=' 이 아닌 경우, NL보다 유리한 방법
3. 두 테이블의 사이즈가 비슷한경우에 유리하며, 사이즈 차이가 큰 경우에는 불리하고, 비효율적인 방법 (NL이 유리)
4. NL Join 은 랜덤 액세스 방식으로 데이터를 읽는 반면, Sort Merge 는 스캔 방식으로 데이터를 읽음
특징
1. 넓은 범위의 데이터를 처리할 때 이용되는 기법이다.
2. 조인 컬럼 기준으로 데이터를 정렬하기 때문에 임시 영역이 필요하다.
( 항상 정렬 작업이 발생하는 것은 아니다. 예를 들어, 조인할 테이블 중에서 이미 앞 단계의 작업을 수행하는 도중에 정렬 작업이 미리 수행되었다면 조인을 위한 정렬 작업은 발생하지 않을 수 있다. )
3. 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있다.
4. 따라서 일반적으로 대량의 조인 작업에서 정렬 작업을 필요로 하는 Sort Merge Join 보다는 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리하다.
5. Sort Merge Join은 Hash Join과는 달리 동등 조인 (Equi Join) 뿐만 아니라 Non-Equi Join에 대해서도 조인 작업이 가능 하다.
3. Hash Join
Sort Merge로는 대용량 데이터 처리하기에 메모리 부하가 있어 고안된 방법
방법
- 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블을 생성 ( 조인 컬럼과 SELECT 절에서 필요로 하는 컬럼도 함께 저장됨)
- ~2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
- 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음 -> 조인 키를 이용해서 실제 조인될 데이터를 찾음
- 조인에 성공하면 추출버퍼에 넣음
- ~5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행
특징
1. CPU 작업 위주로 데이터 처리하는 Hash Join은 NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬 작업 의 부담을 해결 위한 대안으로 등장하였다.
2. 메모리사용이 큰 대용량 테이블 조인시 메모리외에 임시영역까지 사용하여 저장할수있어 유리
3. HASH JOIN 은 조인 컬럼의 인덱스가 존재하지 않아도 사용할 수 있다.
4. 해쉬 함수를 이용해서 조인을 수행하기 때문에 =로 수행하는 조인, 즉 동등 조인에서만 사용할 수 있음
5. HASH JOIN 은 조인 작업을 수행하기 위해 해쉬 테이블을 메모리에 생성해야 한다. HASH 테이블의 크기가 메모리 적재할 수 있는 크기보다 더 커지면 임시 영역에 해쉬 테이블을 저장하므로, 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.
6. HASH JOIN 에서는 선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행 테이블을 Build Input 이라고도 하며, 후행 테이블은 만들어진 해쉬 테이블에 대해 해쉬 값의 존재여부를 검사한다고 해서 Pove Input 이라고도 한다.
4. Join 선택 방법
NL Join, Sort Merge Join, Hash Join의 공통점
- 위 3개의 조인은 모두 조인에 성공하면 결과를 추출버퍼에 넣는다는 공통점이 있다.
- 추출버퍼는 SQL문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출버퍼를 채울 것이 없으면 결과를 사용자에게 반환한다. ( ※추출버퍼: 운반단위, Array Size, Prefetch Size라고도 함.)
** 출처 **
https://yzink.tistory.com/48 [양디로그]https://thefif19wlsvy.tistory.com/26 https://jeong-pro.tistory.com/239
https://velog.io/@fortice/MySQL-MySQL-%EC%97%94%EC%A7%84-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98-2
https://jaehoney.tistory.com/31
'전공 > DB' 카테고리의 다른 글
| MySQL튜닝3) 실행계획 살펴보기1 (0) | 2022.01.19 |
|---|---|
| MySQL튜닝2) SQL 튜닝 용어 이해하기2 (0) | 2022.01.12 |
| 정규화 (0) | 2021.12.15 |
| [DB 기술면접 질문 리스트] : JOIN (0) | 2021.10.17 |
| [DB 기술면접 질문 리스트] : 인덱스 (2) | 2021.10.14 |
