MySQL INDEX, VIEW 사용하는 방법

 

MySQL 인덱스(INDEX) 사용하기

인덱스(Index)는 테이블의 조회 속도를 올리기 위해 일부 칼럼의 데이터만을 빠르게 검색할 수 있는 자료구조 입니다. 

 

테이블 내 특정 1개의 칼럼을 사용해서 인덱스 테이블을 새로 생성해서 데이터에 접근하기 용이한 상태로 변환하게 됩니다. 

 

인덱스와 테이블 데이터 저장공간의 여유를 두고, 조회 하기 용이한 특정 범위의 인덱스를 생성해주어야 합니다. 너무 넓은 범위의 데이터를 담은 인덱스는 불필요한 저장공간을 차지하게 되고, 오버헤드를 증가시킵니다.

 

인덱스는 MYI(MySQL Index) 파일에 저장됩니다. 만약 인덱스가 없다면 MySQL은 모든 테이블을 조회하게 되면서 성능이 저하될 가능성이 높기 때문에 인덱스를 사용합니다.

 

인덱스를 사용하면 SELECT 속도는 빨라질 수 있지만, UPDATE, DELETE, INSERT 등의 테이블을 변형해야 하는 쿼리문은 오히려 속도가 늦어지게 됩니다. 

 

인덱스(INDEX) 생성

일반적인 인덱스 생성 명령입니다.

// 단일 인덱스 생성
CREATE INDEX idx_name ON table_name(field_name);

// 중복 제거 인덱스 생성
CREATE UNIQUE INDEX idx_name ON table_name(field_name);

// 다중 인덱스 생성
CREATE INDEX idx_name ON table_name(field_name1, field_name2,,);

 

인덱스(INDEX) 조회

생성한 인덱스를 조회하는 명령어 입니다.

 

SHOW INDEX FROM table_name;

 

인덱스(INDEX) 삭제

생성한 인덱스를 삭제하는 명령어입니다.

ALTER TABLE table_name DROP INDEX idx_name;

 

인덱스(INDEX) 추가

새로운 인덱스를 테이블에 추가합니다.

 

mysql> ALTER TABLE tt3 ADD UNIQUE INDEX idx_name3(gender, ip_address);

 

인덱스(INDEX) 정렬

인덱스를 생성하는 시점에 정렬 방법을 명시해줍니다.

 

mysql> CREATE INDEX idx_name_ordered2 ON tt3 (asset DESC, gender ASC);

 

 

MySQL 뷰(VIEW) 사용하기

데이터 베이스 테이블로 부터 정의한 논리적인 가상 테이블입니다. 물리적으로는 존재하지 않는 데이터지만, 시스템 카탈로그에 저장되며 사용자가 접근할 때 필요한 외부 스키마를 구성할 때 사용합니다.

 

동일한 데이터를 다른 방법으로 사용자에게 제공할 수 있고, 뷰를 통해 또 다른 뷰를 생성할 수 있습니다.

 

뷰(VIEW)의 장점

뷰(VIEW)는 기존 테이블에 독립적으로 존재하는 논리적 데이터의 집합입니다. 따라서 테이블 구조 변경에 따른 영향을 최소화 할 수 있습니다.

 

테이블의 일부 데이터만 추출해 뷰를 생성할 수 있기 때문에 데이터 보안에 강합니다.

뷰(VIEW)의 단점

물리적으로 저장된 데이터가 아니기 때문에 자체 인덱스 사용은 불가능합니다. 뷰를 변경하기 위해서는 삭제 후 다시 생성해야 하는 번거로움이 있습니다.

 

뷰의 내용에 데이터를 삽입하거나 삭제하는 작업은 물리적으로 저장되어 있지 않기 때문에 불가능합니다.

 

뷰(VIEW) 생성

기존 테이블에서 SELECT ~ FROM 문으로 VIEW에 담을 데이터를 AS 명령어로 추가해줍니다. SELECT 문 뒤로는 옵션을 붙여줄 수 있습니다.

 

• REPLACE : 뷰가 존재하는 경우 재 생성
• FORCE : 원본 테이블 존재여부 상관없이 뷰 생성
• NOFORCE : 기본 테이블이 존재하는 경우에만 뷰 생성
• WITH CHECK OPTION : 서브 쿼리 조건을 만족하는 튜플만 변경
• WITH READ ONLY  : DML작업 불가능한 뷰 생성

mysql> CREATE VIEW view_name AS SELECT first_name, gender, asset FROM tt3 WHERE CAR LIKE "%B%";

 

뷰(VIEW) 조회

mysql> SELECT * FROM view_name;

 

뷰(VIEW) 삭제

mysql> DROP VIEW view_name;