MySQL DBMS의 전반적인 구조와 메커니즘을 파악하기 위해, SQL 튜닝에 관한 기본적인 용어를 반드시 알아야 한다.

물리 엔진과 오브젝트 용어

DBMS를 구성하는 엔진들과 내부 프로세스, 그리고 데이터를 저장하는 오브젝트를 가리키는 용어들을 알아보자.

SQL문의 수행 과정

SQL문 수행 요청이 들어오면, 다음과 같이 두 개의 엔진(MySQL Engine, 과정을 거쳐 실행된다.

1. MySQL Engine에 포함되는 오브젝트

MySQL 엔진의 역할은 다음과 같다.

• SQL 문을 넘겨받아 문법 검사와 적절한 오브젝트 활용 검사를 하고, 원하는 데이터를 빠르게 찾는 경로를 모색한다.
• 스토리지 엔진으로부터 전달받은 데이터를 대상으로 불필요한 데이터를 제거하거나 가공 및 연산하는 역할을 한다.

파서(Parser)

파서는 사용자가 요청한 SQL문을 MySQL이 이해할 수 있는 쪼개 최소 단위로 구성요소를 분리하고, 해당 구성요소로 트리를 만든다.
트리를 만들면서 문법 검사를 수행한다. 트리에 허용되지 않은 문법이 포함된다면 에러를 발생시키고 SQL문 실행을 종료한다.

전처리기(Preprocessor)

파서에서 생성한 트리를 토대로 SQL문에 구조적인 문제가 없는지 파악한다.

전처리기는 다음과 같은 유효성 검사를 수행한다.

• 이미 만들어진 테이블, 열, 뷰, 함수 등으로 구성되지는 않았는가?
• 존재하지 않은 열을 포함하지는 않았는가?
• 조회하려는 테이블에 접근 권한이 부여되어 있는가?

만약 유효하지 않은 오브젝트가 있거나, 권한이 없는 오브젝트를 호출하면 에러를 발생시킨다.

옵티마이저(Optimizer)

트리를 구성하는 오브젝트의 데이터를 효율적으로 가져오기 위해 시간/비용 효율적인 경로로 데이터를 검색하는 방법에 관한 실행 계획을 세운다.

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

옵티마이저가 수립하는 실행 계획은 다음의 내용을 포함한다.

• 어떤 순서로 테이블에 접근할 것인가?
• 인덱스를 사용할 것인지, 사용한다면 어떤 인덱스를 사용할 것인가?
• 정렬할 때 인덱스를 사용할 것인가, 임시 테이블을 사용할 것인가?

실행 계획으로 도출할 수 있는 경우의 수가 지나치게 많을 때는 모든 실행 계획을 판단하지 않는데, 이는 옵티마이저가 선택한 최적의 실행 계획이 최상의 실행 계획이 아닐 수도 있음을 의미한다.

실행 계획을 수립하는 작업만으로도 사용자의 대기시간과 하드웨어 리소스를 점유하므로, 시간과 리소스에 제한을 두고 실행 계획을 선정해야 한다.

2. Storage Engine에 포함되는 오브젝트

Storage 엔진의 역할은 다음과 같다.

• SQL문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할을 한다.
• 가져온 데이터를 MySQL 엔진으로 보낸다.

스토리지 엔진이 데이터를 저장하는 방식에 따라 다음과 같은 종류의 엔진을 사용한다.

• InnoDB 엔진: 일반적인 OLTP 환경인 경우
• MyISAM 엔진: 대량의 쓰기 트랜잭션이 발생하는 경우
• Memory 엔진: 메모리 데이터를 로드하여 빠르게 읽는 효과를 내고자 하는 경우

엔진 실행기(Engine Executor))

엄밀히 따지면 MySQL Engine, Storage Engine 모두에 걸치는 오브젝트로서, 다음 두 가지 역할을 한다.

• 데이터 가져오기: 옵티마이저가 수립한 실행 계획으로 스토리지 엔진을 호출해 필요한 데이터를 가져온다.
• 데이터 필터링: 스토리지 엔진을 통해서 가져온 데이터를 정렬하거나 조인하고, 불필요한 부분은 필터링하는 추가 작업을 통해 사용자가 원하는 결과를 전달한다.

따라서 MySQL 엔진의 부하를 줄이려면, 스토리지 엔진에서 가져오는 데이터 양을 줄이는 것이 매우 중요하다.

DB 오브젝트

데이터베이스 구성 요소에서 오브젝트(object)란, 2차원 형태의 관계형 데이터베이스에서 사용하는 표에서 드러나는 여러 개념들을 포함하는 용어이다.

1. 테이블(Table)

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

2. 행(Row)

테이블에서 동일한 구조의 데이터 항목들의 집합을 가리키는 오브젝트로, 테이블에서 가로 영역에 해당하는 부분이다.

행들은 서로 데이터의 값은 다르지만, 데이터 항목의 구조는 동일하다. 행은 하나의 데이터 항목 집합이며, 모든 행의 집합을 테이블이라고 할 수 있다.

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

3. 열(Column)

열(컬럼)은 데이터 유형에 관한 정보를 정의하는 오브젝트로서, 사전에 정의한 데이터 유형으로 데이터값을 일관되게 저장할 수 있도록 해 준다. 열별로 다른 데이터 유형을 가질 수 있다.

4. 기본 키(Primary Key)

특정 행을 대표하는 열을 가리키는 용어이다. 기본 키는 어떤 행과도 중복되지 않는 대푯값으로, 기본 키가 있음으로 인해 행들은 서로 중복되지 않는다.

기본 키는 보통 1개 열만으로 생성하지만, 상황에 따라 2개 이상의 열을 조합해 기본키를 구성할 수도 있다. 참고로 기본 키는 Index의 역할도 수행한다.

MySQL에서 기본 키는 클러스터형 인덱스(clustered index)로 작동하여, 기본 키의 구성 열 순서를 기준으로 물리 스토리지에 데이터가 쌓인다. 비슷한 기본 키 값들이 근거리에 적재되므로 기본 키를 활용하여 인덱스 스캔을 수행하면, 테이블 데이터에 더 빠르게 접근할 수 있다.

5. 외래 키(Foreign Key)

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

한 번 어떤 열에 외래 키를 설정하면, 해당 열의 어떤 행을 외부 테이블에 없는 값으로 삽입, 수정을 시도하면 에러가 발생한다.

외래 키에 해당하는 컬럼의 값에 변경사항이 발생할 때마다 외래 키 설정조건을 항상 검증하므로, 데이터 정합성 향상을 위해 외래 키를 설정하는 경우도 있다.

6. 인덱스(Index)

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

원하는 데이터를 찾기 위해서는 테이블을 처음부터 끝까지 전부 차례로 검색하는 것이 가장 정확하고 빠를 것이다. 그러나 행 수가 많을수록, 검색 속도도 급격히 느려진다. 대신 데이터가 어느 곳에 있는지 위치를 기억한다면 쉽게 찾을 수 있을 것이다. 이것이 인덱스의 장점이다. 마치 책의 맨 뒤편에 'ant'는 999페이지에, 'computer'는 2페이지에 있다는 식으로 구성된 Index 페이지와 같다.

인덱스로 설정한 컬럼의 값으로 데이터를 검색하면, 조회 성능이 많이 향상된다. 따라서, 저장된 데이터를 검색할 일이 많을 때는 인덱스를 설계하는 과정이 매우 중요하다.

-주의사항-
기본 키와 똑같은 인덱스를 생성하면 인덱스가 저장되는 물리적 공간이 낭비되는 한편, 데이터 INSERT/DELETE/UPDATE에 따른 인덱스 정렬의 오버헤드가 발생한다.
따라서, 기본 키와 같은 열로 만들어진 인덱스가 있다면 해당 인덱스는 삭제하는 것이 좋다.

인덱스는 생성하려는 열의 속성에 따라 고유 인덱스, 비고유 인덱스로 구분된다(흔히 거론되는 인덱스는 '비고유 인덱스' 이다).

고유 인덱스(unique index)

인덱스를 구성하는 열들의 데이터가 유일하다. 차례로 정렬되는 인덱스 열의 데이터는 서로 중복되지 않고 유일성을 유지한다. 만약 동일한 데이터가 생성되면 고유 인덱스의 중복 체크 과정에서 에러가 발생한다.

어떤 열에 동일한 데이터가 등록되는 일을 방지하고자 한다면, 해당 열을 고유 인덱스로 생성해야 한다(ex. 연락처 컬럼)

-기본 키와 고유 인덱스의 차이-
기본 키와 고유 인덱스는 모두 '데이터의 유일성을 보장'하며, '효율적인 데이터 접근을 위한 인덱스'로 사용된다는 점에서 유사하다.
하지만, 기본 키에는 NULL을 입력할 수 없지만 고유 인덱스에는 NULL을 입력할 수 있다는 점이 다르다.

비고유 인덱스(non-unique index)

일반적으로 '인덱스'라 함은 이 '비고유 인덱스'를 가리킨다. 비고유 인덱스란, 고유 인덱스에서 데이터의 유일한 속성만 제외한 키이다. 데이터가 신규 입력되어 인덱스가 재정렬되더라도, 인덱스 열의 중복 체크를 거치지 않고 단순 정렬만 수행한다.

중복이 발생할 수 있는 열을 기준으로 조회할 때가 많은 경우, 해당 열에 비교유 인덱스를 생성한다(ex. 회원 이름(실명)).

7. 뷰(View)

뷰란 일종의 가상 테이블이다. 보통 테이블과 같이 물리적인 실체로서 존재하지는 않지만, 간접적으로 원하는 대상을 확인할 수 있도록 가상으로 생성한 테이블을 가리킨다.

기존의 물리 테이블을 이용해 뷰를 만든다. 어떤 테이블의 뷰를 생성할 때, 해당 테이블의 일부 컬럼들만 조회하도록 생성할 수 있다. 예를 들면 학생 테이블(학번, 이름, 생년월일, 연락처, 전공코드)에서 학번, 이름만 조회하는 뷰를 만들 수 있는 것이다.

테이블의 제한된 정보만을 보여줄 수 있다는 점에서, 뷰를 만들면 개인정보 등을 외부에 직접적으로 공개하지 않고도 안전하게 운영할 수 있으므로 시스템의 보안성 향상에 유리하다.

테이블의 데이터가 변경되면, 뷰에서도 곧바로 변경된 데이터를 조회할 수 있다. 반대로 뷰에서 데이터를 변경하면 역시 테이블의 해당 데이터도 즉시 변경된다.

-뷰를 사용하는 이유-
* 보안성: 일부 데이터에 대해서만 데이터를 공개하고, 노출에 민감한 데이터에 대해서는 제약을 설정할 수 있음.
* 성능 최적회: 여러 개의 테이블을 병합(join)해서 활용할 때 성능을 고려한 최적화된 뷰를 생성함으로써 일관된 성능을 제공할 수 있음.

• 출처: 업무에 바로 쓰는 SQL 튜닝