db index & view

DB indexing

인덱스는 데이터베이스에서 조회 및 검색을 더 빠르게 할 수 있는 기술로, 자주 조회되는 Column 에 대한 인덱스 테이블을 따로 만들어 SELECT 문이 들어왔을 때 인덱스 테이블에 있는 값들로 결과 값을 조회해 오는 방법이다. 인덱스에 쓰이는 대표적인 검색 알고리즘으로는 이진트리(binary tree) 알고리즘이 있다.

인덱스는 CREATE INDEX 명령으로 만든다. 인덱스에 이름을 붙여서 관리하는데, 객체가 될지 열처럼 취급될지는 데이터베이스 제품에 따라 다르다. MySQL 은 인덱스도 테이블 내의 객체로 취급한다. 테이블에 의존하는 객체이므로, 대부분의 데이터베이스에서는 테이블을 삭제하면 인덱스도 함께 삭제된다.

인덱스는 따로 테이블 형태로 관리되므로 자원이 소모된다. 따라서 무분별한 인덱스의 사용은 성능에 부정적인 영향을 미칠수도 있다. 또, 인덱스 테이블은 이진트리를 사용하기 때문에 기본적으로 정렬되어 있어 WHERE 조건이 지정된 SELECT 명령의 처리 속도가 향상되어 검색이 많이 이루어지는 서비스에서는 인덱스를 사용하면 좋지만, 잦은 데이터의 변경이 이루어질 경우(생성, 삽입, 수정, 삭제 등의), 인덱스 테이블을 재정렬하는데에 드는 오버헤드(어떤 처리를 하기 위해 들어가는 간접적인 메모리) 때문에 오히려 성능 저하가 일어날 수도 있다.

이진트리

이진트리는 자식 노드가 최대 두 개인 노드를 의미한다.

일반적으로 테이블에 인덱스를 작성하면, 테이블 데이터와 별개로 인덱스용 데이터가 저장 장치에 만들어지는데 이때 이진트리라는 데이터 구조로 작성된다.

 

트리는 노드라는 요소로 구성되며, 각 노드는 두 개의 가지로 나뉜다. 노드의 왼쪽 가지는 작은 값, 오른쪽 가지는 큰 값으로 나뉘어져 있으며 가장 위의 노드를 root 노드라고 한다.

검색의 진행 방법은, 원하는 수치와 비교해서 더 크면 오른쪽을, 더 작으면 왼쪽의 가지를 조사하는 방식으로 진행된다. 이진트리 내에서는 중복되는 값을 가질 수 없으므로 기본키 제약을 이진트리를 이용하여 인덱싱하는 데이터베이스가 많다.

B- tree

b-tree 는 이진트리를 확장한것으로, 하나의 노드가 가질수있는 자식 노드의 최대숫자가 2보다 큰 트리구조를 의미한다. 방대한 양의 저장된 자료를 검색해야 하는 경우, 검색어와 자료를 일일이 비교하는 방식은 비효율적일 수 있다. b-tree 는 자료를 정렬된 상태로 보관하여 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다는 균일성의 장점을 가지고 있다. 루트로부터 리프까지의 거리가 일정한 균형트리이기 때문이다.

 

 

B+ tree

b-tree 에서는 특정 key값이 하나의 노드에서만 존재할 수 있지만, b+tree 에서는 leaf 노드들을 연결리스트 형태로 서로 연결하여 leaf 노드를 통해 순차적으로 탐색할 수 있다. branch 노드들은 데이터의 빠른 접근을 위한 인덱스 역할만 하기 때문에 키와 포인터로만 구성되어 있다.

 

EXPLAIN

EXPLAIN 명령은 SQL 을 가져오기 전 데이터를 어떻게 가져올것인지에 대한 실행 계획 및 과정을 알아보기 쉽게 DB 결과 셋으로 보여주는 것이다. 이를 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.

 

각 행의 항목별 의미

• id : select 아이디로 SELECT를 구분하는 번호
• table : 참조하는 테이블
• select_type : select에 대한 타입
• type : 조인 혹은 조회 타입
• possible_keys : 데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
• key : 실제로 사용할 인덱스
• key_len : 실제로 사용할 인덱스의 길이
• ref : Key 안의 인덱스와 비교하는 컬럼(상수)
• rows : 쿼리 실행 시 조사하는 행 수립
• extra : 추가 정보

뷰 View

뷰는 테이블과 같은 부류의 데이터베이스 객체 중 하나이다. SELECT 명령에는 실체가 없으므로 데이터베이스 객체가 될 수 없는데, 이 SELECT 명령에 이름을 붙여 관리할 수 있도록 한 것이 뷰이다. 가상 테이블이라고 생각하면 편하다.

뷰를 작성함으로써 자주 사용하거나 복잡한 SELECT 명령을 간략하게 표현하고 편리하게 사용할 수 있다. 실제 저장공간을 가지지 않으므로 테이블처럼 취급하지만 데이터를 쓰거나 지울 수 없어 갱신이나 삭제 등의 작업에 많은 제한사항을 가진다. 뷰는 저장공간을 소비하지 않는 대신 CPU를 사용한다. SELECT 명령이 데이터베이스의 테이블에서 행을 검색하여 반환하는 명령이기 때문이다.

생성과 삭제

생성은 CREATE 로, 삭제는 DROP 으로 한다.

// 생성
CREATE VIEW 뷰명 AS SELECT * FROM 테이블명

// 열을 지정하여 뷰 생성하기
CREATE VIEW 뷰명(열명1, 열명2, ...) AS SELECT 열명1, 열명2, ... FROM 테이블명

// 불러오기
SELECT * FROM 뷰명

// 삭제
DROP VIEW 뷰명