인덱스, 뷰, 조인

인덱스(Index)

인덱스가 없다면 모든 테이블의 데이터를 스캔(Full Table Scan)해야 합니다.

테이블을 가리키는 대상이기 때문에 테이블이 삭제되면 같이 삭제됩니다.

인덱스 부작용

인덱스를 저장할 추가적인 공간이 필요합니다.

인덱스 작성 시간이 있기 때문에 행 데이터가 너무 많으면 이 시간이 오래 걸릴 수 있습니다.

INSERT, UPDATE, DELETE 작업 할 때 인덱스를 다시 작성하기 때문에 검색(SELECT)가 아닌 작업(INSERT, UPDATE, DELETE) 시 성능이 낮아집니다.

 

※ INSERT가 나머지 UPDATE, DELETE보다 성능이 더 낮아질 수 있습니다.

 

그래서 인덱스를 사용하기 좋은 열은?

• SELECT이 많고 INSERT, UPDATE, DELETE가 적은 열
• 카디널리티(Cardinality)가 높은 열

인덱스 종류

클러스터형 인덱스(Clustered index)

테이블 당 하나만 있습니다.

PRIMARY KEY(우선 지정) / UNIQUE + NOT NULL 제약 조건 지정 시 자동 생성합니다.

행 데이터가 클러스터형 인덱스로 지정된 데이터에 대해 자동 정렬합니다.

 

※ 대부분 PRIMARY KEY에 클러스터형 인덱스가 걸릴 거라 생각합니다.

 

보조인덱스(Secondary index)

테이블당 여러 개 가능합니다.

중복값을 가질 수 있습니다.

고유키로 지정된 컬럼(UNIQUE 제약 조건)은 자동으로 보조 인덱스가 생성됩니다.

행 데이터가 인덱스로 지정된 데이터에 대해 자동 정렬하지 않습니다.

 

보조 인덱스 사용 방법

-- 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);

-- 인덱스 조회
SHOW INDEX FROM 테이블명;

-- 인덱스 삭제
DROP INDEX 인덱스명 FROM 테이블명;

 

뷰(View)

select의 결과를 하나의 테이블처럼 사용하는 가상의 테이블입니다.

뷰를 사용하며 쿼리가 단순화되고 모든 데이터를 보여줄 필요가 없게 됩니다.

 

뷰 사용 방법

-- 뷰 생성
USE VIEW 뷰이름 AS SELECT문;

-- 뷰 삭제
DROP VIEW 뷰이름

 

조회는 제한이 없을지 모르나 삽입, 수정, 삭제는 안될 수도 있습니다.

 

뷰는 실제 테이블이 아니기 때문에 뷰에 대한 조작을 하면 참조한 테이블이 조작됩니다.

그렇기 때문에 참조한 테이블의 제약조건에 위배되는 뷰에 대한 조작을 할 수 없습니다.

또한 집계함수를 이용하여 뷰를 만든 경우에는 조회만 가능하게 됩니다.

 

조인(Join)

RDB의 강력한 기능 중 하나입니다.

여러 테이블을 하나로 묶는 방법입니다.

 

조인 종류

Outer Join에서 어느 한쪽에 없는 데이터는 null로 채워지게 됩니다.

 

조인 사용 방법

-- inner join
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;

-- left outer join
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.ID;

-- right outer join
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
RIGHT OUTER JOIN Departments ON Employees.DepartmentID = Departments.ID;

-- full outer join
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.ID;

-- union
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID
UNION
SELECT Employees.Name, Employees.Position, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.ID;

 

※ MySQL에서는 Full Outer Join을 지원않아서 UNION을 사용하여 구현한다고 합니다.