[이것이 MYSQL이다] 인덱스의 기초개념과 뷰 테이블

본 포스트는 2021년에 업로드된 포스트입니다. 개념이 아직 정리안된 나이에 업로드한 포스트이기에 수정사항이 있으면 연락주세요.

뷰란?

view table은 존재하지 않는 가상의 테이블을 지칭한다.

 

따라서 말 그대로 눈에 보이기만 하는 테이블이다.

 

뷰를 사용하는 이유는 다음 예시를 들으면서 설명할 수 있다.

뷰를 사용하는 이유

우리가 어떤 알바에게 회원 정보중 주소열을 모두 수정하라고 알바를 고용했다.

 

회원테이블에는 회원들의 수많은 개인정보가 들어있다.

 

알바는 주소를 수정할때 회원들의 다른 개인정보를 가져갈수도 있는 최악의 상황이 발생한다.

 

따라서 새로운 테이블을 하나 만들어서 알바에게 해당 테이블에게만 접근권한을 부여해서 그 테이블만 수정하게끔하면 되지만 그러면 알바가 수정해놓은 결과를 또 한번 메인테이블에 옮겨야 하는 흔히 알바를 고용할 필요가 없는 상황이 발생한다.

 

뷰를 사용하는 이유는 정리하면 아래와 같다.

1. 정보 보호
2. 쿼리문의 호출 빈도 단축

 

2번같은 경우에는 있다가 설명하겠다.

view table의 사용방법

뷰의 특징이 있다

SELECT * FROM usertbl;

 

이 쿼리문은 회원 테이블의 모든 행을 조회하는 코드이다.

 

해당 쿼리를 실행하면 테이블 형식의 결과를 얻을 수 있다.

 

뷰는 따라서 SELECT문으로 여러가지 조건을 추가하여서 테이블 형식으로 출력하는 것이므로 SELECT문이라고 봐도 무방하다.

 

이제부터 VIEW TABLE을 생성해보자.

CREATE VIEW test
	AS
		SELECT name, addr FROM userTBL;

SELECT * FROM test;

 

어렵지 않다. 이제 우리는 test라는 가상의 테이블로 AS안에 있는 쿼리문의 결과를 언제든지 볼 수 있게 되었다.

 

앞에서 설명한 2번같은 경우는 바로 이러한 용도로 VIEW를 사용하게 된다면 반복적인 쿼리문을 해당 test로 규약해서 쿼리문의 호출 빈도를 단축 할 수 있다.

 

또한 VIEW는 현재 usertbl과 서로 연결되어있다.

 

해당 test에서 데이터를 업데이트하면 메인테이블도 같이 업데이트 된다.

 

바로 이게 위에서 말한 1번의 정보 보호의 장점이라고 할 수 있다.

view와 CRUD

view 테이블은 일반 테이블과 동일하게 update와 delect문을 수행할 수 있다.

 

하지만 단점이 있다. 아래의 쿼리문을 보자.

CREATE VIEW test2
	AS
		SELECT name, addr, SUM(price*amount) AS 'total' FROM userTBL;

UPDATE test2 SET total = 2000 where name = "test2";

 

위의 쿼리문은 동작하지 않는다.

 

VIEW에서 UPDATE문을 수행할 수 없는 경우는 다음과 같다.

• 집계 함수를 사용하였을때
• GROUP BY 등의 그룹함수를 사용하였을때
• 메인테이블에만 있는 열을 수정하려고 할떄 (참조되지 않음)

 

3번리스트 같은 경우에는 무슨 말이냐면 userTBL에는 phone라는 열이 있는데 이 열 값을 test2라는 view table에서 수정하려고 하면 업데이트 되지 않는다는 것이다.

인덱스

우리는 지금까지 인덱스를 써왔었다.

 

인덱스란 데이터의 검색 속도를 더욱 빨라지게 할 수 있는 것이라고 할수 있다.

 

흔한 예시로 책에는 찾아보기라는 페이지가 있다.

 

해당 찾아보기로 어디에 어떤게 있는지 찾기가 쉽다.

 

우리가 일반적으로 썻던 쿼리는 FULL SEARCH 형식의 조회문이다. 따라서 모든 행을 다 비교하는 것이다.

 

이것은 테이블안에서 행의 길이가 늘어날수록 검색하는 속도와 시간도 기하급수적으로 늘어난다.

 

만약 MYSQL에서 찾아보기와 같은 개념이 들어간다면 검색할때 미리 어디위치에 무엇이 있는지 파악을 해놓은 상태여서 검색하기가 더욱 더 빠르고 간결하다.

인덱스의 종류

인덱스의 종류는 크게 2가지이다.

 

첫번째는 클러스터형 인덱스와 보조 인덱스 (논클러스터형 인덱스)가 있다.

 

첫번째 같은 경우에는 데이터를 미리 정렬해놓은 영어 사전과 같은 의미의 인덱스고, 논클러스터형 같은 인덱스는 앞서 말한 찾아보기와 같은 의미의 인덱스다.

 

클러스터형 인덱스는 테이블당 1개밖에 사용할수 없다.

 

하지만 논클러스터형 인덱스와 같은 경우에는 그러한 제약조건이 없다.

인덱스의 사용처

우리는 지금까지 인덱스를 써왔다는 말은 항상 PRIMARY KEY와 같은 KEY를 써오면서 우리는 인덱스를 써온것이다.

 

PRIMARY KEY같은 경우는 클러스터 형 인덱스다 기본키가 테이블에 여러개가 있는것을 본적 있는가?

 

기본키는 1개밖에 생성할수 없다. 또한 중복을 허용하지 않으며 NULL값도 허용하지 않는 제약조건의 경우에는 클러스터형 인덱스가 자동적으로 생성된다.

 

이외의 UNIQUNE 키 같은 경우에는 생성할시 자동적으로 논클러스터형 인덱스로 생성되고

NOT NULL을 추가 할 경우에는 클러스터형 인덱스가 된다.

 

하지만 NOT NULL 추가한 UNIQUNE 키와 기본키중에는 기본키가 우선순위가 더 높다.

 

정리하자면

• 키본키를 생성할 경우에는 자동적으로 클러스터형 인덱스가 생성된다.
• UNIQUNE 키를 생성할 경우에는 자동으로 논클러스터형 인덱스가 생성된다.
• UNIQUNE 키에서 NOT NULL을 추가할 경우에는 클러스터형 인덱스가 된다.
• 기본키과 UNIQUNE 키중에서는 기본키가 우선순위가 가장 높다.

 

기본키를 사용한 열은 자동으로 정렬되는 이유도 클러스터형 인덱스기 때문이다.

 

이제 다음에는 인덱스의 원리를 이용해서 인덱스의 장단점을 파악해 볼 것 이다.