[SQL 튜닝 실습] SQL 튜닝용어 정리 1

1. 물리 엔진과 오브젝트 용어

 

마리아DB SQL 수행 프로세스

1) 사용자로부터 SQL문 실행

2) MariaDB 엔진이 SQL문 문법 검사 (파싱)

3) 사용자가 요청한 데이터를 빠르고 효율적으로 찾아가는 전략적 계획 수립 (옵티마이저)

4) 이 수립된 계획대로 스토리지 엔진에 위치한 데이터까지 찾아간 후 해당 데이터를 마리아DB 엔진으로 전달

5) 마리아DB 엔진은 전달된 데이터에서 불필요한 부분을 필터링 후 필요한 연산을 수행 후 사용자에게 최종결과를 알려준다.

 

스토리지 엔진 (InnoDB, MyISAM, Memory 등)

사용자가 요청한 SQL문을 토대로 DB로부터 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할 수행 수행 후 해당 데이터를 마리아DB 엔진으로 보내준다.

데이터 저장 방식에 따라 각각의 스토리지 엔진을 선택하여 사용할 수 있고, 필요에 따라 스토리지 엔진설치파일을 가져와 활성화 후 즉시 사용가능

일반적으로 온라인상의 트랜잭션 발생으로 데이터 처리하는 OLTP환경이 대부분이므로 InnoDB 엔진을 사용한다.

대량의 쓰기 트랜잭션이 발생한다면 MyISAM 엔진을 사용한다.

메모리 데이터를 로딩해서 빠르게 읽으려면 Memory 엔진

 

스토리지 엔진 확인

SELECT *

FROM information_schema.engines;

 

마리아DB 엔진은 SQL문의 시작 및 마무리 단계에서만 관여하며,

스토리지 엔진으로부터 필요한 데이터만을 가져오는 핵심 역할 담당

 

SQL 프로세스 용어

 

SQL문 수행 절차

1. SQL문 실행 후 파싱

2. SQL 최소단위로 분리 및 구성요소 트리로 작성

3. 전처리기

4. 옵티마이저에서 엔진실행기로 실행계획 전달

5. 엔진 실행기가 스토리지 엔진들 중에서 1개를 선정하여 데이터 가져오기

6. 가져온 데이터를 최종결과 전달

 

파서

MySQL 엔진에 포함되는 오브젝트 사용자가 요청한 SQL을 최소단위로 분리 후 트리구조 형성함과 동시에 문법검사 수행

 

전처리기

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

 

옵티마이저

핵심엔진 중 하나로 DBMS의 두뇌역할 여러가지의 실행계획을 수립한다

 

엔진 실행기

MySQL 엔진과 스토리지 엔진 영역 모두 걸치는 오브젝트 옵티마이저에서 수립한 실행 계획을 참고 후 스토리지 엔진에서 데이터 조회 따라서, MySQL 엔진의 부하를 줄이려면, 스토리지 엔진에서 가져오는 데이터양을 줄이는 게 아주 중요하다

 

DB 오브젝트 용어

ROW

행 수가 많아지면 데이터 접근과정에서 시간이 오래 소요될 가능성이 높은데 이 때 파티셔닝 기법으로 SQL문의 성능 향상을 검토해볼 수 있다.

 

인덱스 주의사항

PK와 동일한 인덱스를 생성하면 인덱스가 저장되는 물리공간이 낭비되는 한편 데이터 삽입/삭제/수정에 따른 인덱스 정렬의 오버헤드 발생!

따라서, PRIMARY KEY 로 선정된 컬럼을 인덱스를 추가로 걸면 절대로 안된다. 이미 걸려있다면, 인덱스는 삭제해야한다

 

인덱스 : 키 기준으로 정렬된 오브젝트

 

고유 인덱스

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

NULL값이 입력가능하다

(*) 연락처, 주민번호

 

비고유 인덱스

중복이 발생할 수 있는 인덱스

(*) 이름

 

뷰

가상 테이블로 마치 테이블인 것처럼 활용가능하다

(*) 뷰를 사용하는 이유 : 일부 데이터에 대해서만 공개하며 보안때문에 설정한다

 

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

 

- 서브쿼리

SELECT 스칼라 서브쿼리 가장 외곽 SELECT 문인 메인쿼리를 기준으로 내부 SELECT 문을 추가로 작성해서 생성 스칼라 서브쿼리에서는 집계함수가 자주 쓰인다 FROM  인라인 뷰 WHERE 중첩 서브쿼리

 

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

 

비상관 서브쿼리

메인쿼리와 서브쿼리 간에 관계가 없으며, 서브쿼리가 독자적으로 실행 후 메인쿼리한테 그 결과를 리턴해주는 형태

예) 

서브쿼리 우선실행 후 메인쿼리 메인쿼리 실행 : 관계가 없으면, 서브쿼리 먼저 실행

 

상관 서브쿼리

메인쿼리와 서브쿼리간에 관계가 있으며, 서브쿼리가 실행되려면, 메인쿼리의 값이 필요하다 SELECT 절에 작성하는 스칼라 서브쿼리와 WHERE 절에 작성하는 중첩 서브쿼리일 때 발생

 

예) 

1) 관계 유무 확인 후 관계있음

2) 메인쿼리 실행 (학생.학번 데이터 가져오기)

3) 서브쿼리 실행 (지도교수.학번 = 학생.학번)

4) 메인쿼리 실행 후 결과 출력 (SELECT * FROM 학생)

 

관계가 있으면, 메인쿼리 먼저 실행

 

반환결과에 따른 SQL 용어

서브쿼리 결과 

1건의 행 데이터만 반환하는 경우 (단일행 서브쿼리)

2개 이상의 행 데이터를 반환하는 경우 (다중행 서브쿼리)

 

단일행 서브쿼리

SELECT 절에서 사용하는 스칼라 서브쿼리와 동일

 

다중행 서브쿼리

서브쿼리 결과가 여러 행으로 반환되는 쿼리로

메인쿼리의 조건절에서 IN 구문으로 서브쿼리에서 반환되는 값을 리턴받는다.

 

다중열 서브쿼리

서브쿼리 결과가 여러 열과 여러 행으로 반환되는 쿼리로 메인쿼리에서 반환되는 열들과 동일하게 WHERE (이름, 전공코드) IN 구문으로 서브쿼리 결과를 받고, 메인쿼리가 수행되고 최종결과를 출력한다.

 

 

조인 연산방식 용어

분리된 데이터 간의 공통된 정보들을 논리적으로 연결할 수 있다.

내부 조인 : 공통 데이터 영역 (교집합)

레프트 외부 조인 : 왼쪽 원 전체영역

라이트 외부 조인 : 오른쪽 원 전체영역