2장 SQL 튜닝 용어를 직관적으로 이해하기 (작성중)

1. DB 엔진 

 

1.1 스토리지 엔진

스토리지 엔진은 데이터를 관리하는 엔진이며 데이터 저장 방식에 따라 엔진이 구분된다. MySQL은 InnoDB 스토리지 엔진을 기본적으로 사용하며 InnoDB는 클러스터링 인덱스 방식으로 데이터를 저장한다. MySQL 엔진이 요청한 데이터를 디스크나 메모리에서 찾아서 MySQL 엔진에게 전달한다.

 

1.2 MySQL 엔진

사용자가 요청한 SQL문을 SQL 파서와 전처리기(Preprocessor)에서 문법 검사를 진행하고 SQL 옵티마이저에 의해 데이터를 가져올 최적의 계획을 세운 후 스토리지 엔진에게 실행 계획대로 데이터를 가져오길 요청한다. 스토리지 엔진으로부터 넘겨받은 데이터에서 불필요한 데이터는 제거하고 가공 및 연산을 진행한다. 그리고 사용자에게 결과 데이터를 전달한다.

 

2. DB 오브젝트 용어

2.1 테이블

데이터를 저장하는 오브젝트로 행과 열로 이루어져 있다. 관계형 데이터베이스는 2차원 배열 형태로 테이블을 관리한다. 열의 갯수와 타입에 맞춰서 행을 저장할 수 있다.

 

2.2 Row (행)

Row는 행에 해당하는 용어로 데이터 항목들의 집합이다. 열의 갯수 및 구조는 동일하나 저장되는 값은 다를 수 있다. 레코드(Record), 튜플(Tuple) 로 부를 수 있다. 정리하면 아래 표와 같다.

파일 시스템	데이터베이스 모델링	관계형 데이터베이스
파일 (File)	엔터티 (Entity)	테이블 (Table)
레코드 (Record)	튜플 (Tuple)	행 (Row)
키 (Key)	유일값 (Identifier)	기본키 (Primary Key)
필드 (Field)	어트리뷰트 (Attribute)	컬럼 (Column)

 

2.3 Column (열)

사전에 정의한 데이터 유형으로 데이터값을 저장하며 열 별로 다른 데이터 유형을 가질 수 있다.

 

2.4 Primary Key (기본키)

행을 대표하는 열이며 유일값을 갖는다. 줄여서 PK라고 한다. 하나 혹은 여러 열의 조합으로 기본키가 될 수 있다. 인덱스 역할도 수행하며 InnoDB 스토리지에서는 클러스터형 인덱스로 작동한다. 

 

2.5 Foreign Key (외래키)

외부의 테이블의 컬럼을 참조하는 다른 테이블의 컬럼 간의 관계를 설정할 수 있다. 줄여서 FK라고 한다. 아래 그림(1) 이미지 기준으로 외부 테이블을 부모 테이블(Student Details), 참조하는 테이블을 자식 테이블(Student Marks)이라고 한다면 부모 테이블의 컬럼(ID)에 존재하는 값만 자식 테이블의 참조 컬럼(ID)에 존재할 수 있다. 이를 위반하면 에러가 발생한다.

그림 (1)

 

2.6 Index (인덱스)

인덱스는 테이블의 컬럼에 생성하며 해당 컬럼을 내부적으로 값을 정렬해 놓은 것을 의미한다. PK는 자동으로 인덱스가 생성된다. 여러 컬럼을 묶어서 인덱스 생성도 가능하며 컬럼의 순서대로 값을 정렬한다. 조건에 따라 인덱스를 이용하면 원하는 데이터를 빠르게 탐색할 수 있다.

그림 (2)

 

2.6.1 Unique Index (고유 인덱스 )

이름 그대로 중복값이 없는 인덱스를 생성하고 해당 컬럼에 중복된 값이 입력되지 않도록 한다. PK와 비슷하지만 큰 차이점으로 PK는 NULL을 입력할 수 없지만 고유 인덱스는 NULL 입력이 가능할 수 있다.

-- 테이블 생성 때 유니크 인덱스 생성
CREATE TABLE gamer (
      id bigint 
    , identification_number VARCHAR(14) NOT NULL UNIQUE    
    , primary key(id)
);


-- 생성된 테이블에 유니크 인덱스 추가 방법 2가지
-- (1)
ALTER TABLE {테이블 이름}
  ADD UNIQUE INDEX {인덱스 이름}( {인덱스 생성할 컬럼 이름} );

ALTER TABLE gamer
  ADD UNIQUE INDEX idx_gamer_identificationnumber(identification_number);
  
-- (2)  
CREATE UNIQUE 
 INDEX {인덱스 이름}
    ON {테이블 이름}( {인덱스 생성할 컬럼 이름} );  
    
CREATE UNIQUE 
 INDEX idx_gamer_identificationnumber 
    ON gamer(identification_number);

 

2.6.2 Non-Unique Index (비고유 인덱스 )

중복값을 가진 인덱스를 말한다.

-- 테이블 생성 때 비고유 인덱스 생성
CREATE TABLE gamer (
      id bigint 
    , identification_number VARCHAR(14) NOT NULL
    , primary key(id)
    , INDEX (identification_number)
);


-- 생성된 테이블에 비고유 인덱스 추가 방법 2가지
-- (1)
ALTER TABLE {테이블명}
  ADD INDEX {인덱스 이름}( {인덱스 생성할 컬럼명} );

ALTER TABLE gamer
  ADD INDEX idx_gamer_identificationnumber(identification_number);
  
  
  
-- (2)  
CREATE INDEX {인덱스 이름}
    ON {테이블 이름}( {인덱스 생성할 컬럼 이름} );  
    
CREATE INDEX idx_gamer_identificationnumber 
    ON gamer(identification_number);

 

인덱스 삭제 방법은 고유, 비고유 동일하다.

-- (1)
ALTER TABLE {테이블 이름}
 DROP INDEX {인덱스 이름};

ALTER TABLE gamer
 DROP INDEX idx_gamer_identificationnumber;
 
 
-- (2)
DROP INDEX {인덱스 이름}
  ON {테이블 이름};
  
DROP INDEX idx_gamer_identificationnumber 
  ON gamer;