[이것이 MYSQL이다] MYSQL 인덱스, 트리거, 뷰, 스토어드 프로시저 간단한 개념 정리

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

인덱스

인덱스는 데이터베이스에서 SELECT 문을 사용할때 쓰이는 키이다.

 

데이터베이스안에 데이터가 많이 없을경우에는 인덱스를 쓸 필요는 없지만 데이터가 2만개 3만개....100만개 이렇게 늘어난다고 가정하면 데이터베이스에서 원하는 row를 찾을때 엄청난 시간이 소요될뿐 아니라 과부하가 일어날 가능성이 크다.

 

바로 이때 인덱스 키를 사용한다.

 

책으로 가정해보면 책에서는 맨뒤에 <찾아보기>라는 칸이있다. 원하는 위치 부분의 쪽수를 알려주면 그쪽 부분으로 바로 가서 원하는 부분을 읽을수 있다.

 

이 개념과 비슷한 것이 바로 인덱스 키이다. 만약 책에서 <찾아보기>가 없다면 우리가 원하는 페이지를 찾고 싶을떄 일일히 페에지를 뒤적거려야 할것이다.

 

MYSQL에서 SELECT 문은 하나하나의 row를 다 조건을 대입해보면서 조건의 맞는 row를 찾아주는 방식이다.

 

이 방식은 데이터가 늘어날수록 더더욱 반복되는 과정이다 따라서 데이터가 늘어나는 만큼 찾는 시간이 증가하므로 정비례한다.

 

이러한 환경을 방지할수 있도록 만들어 준것이 바로 인덱스 이다.

 

인덱스를 만들어 준다는 것은 책 뒤의 <찾아보기> 페이지를 만들어 주는것과 비슷하다.

 

원하는 테이블의 컬럼에 인덱스 키를 생성해주면 나중에 SELECT를 하여 데이터를 찾을때도 인덱스라는 것으로 데이터의 위치를 지정해주었기에 그부분으로 바로 가서 원하는 row를 출력해준다.

 

인덱스를 사용하지 않았을때 FULL TABLE SCAN

 

인덱스를 사용하였을때 Non-Unique Key Lookup

 

인덱스라는 것은 데이터가 적을때는 굳이 인덱스를 만들필요가 없다.

 

따라서 인덱스는 잘쓰면 매우 유용한 기술이고 못쓰면 매우 불필요한 기술이 됨으로 사용용도를 꼭 알고 사용해야할것이다.

트리거

트리거라는 것은 간단히 설명하면 백업 테이블과도 같다.

 

트리거를 이해하기 쉽도록 한가지 시나리오를 가정해보겠다.

1. 회원 "정건우"라는 사람이 사이트에 가입을 했다.
2. 회원 "정건우"라는 사람은 이 사이트의 회원을 탈퇴하였다.
3. 회원이였던 "정건우"라는 사람이 자신이 여기 사이트의 회원이라고 증명을 요구한다.
4. 증명해줄수가 없다.

회원 탈퇴를 할시 DELETE로 회원 row를 지워버렸기 때문에 증명해줄수가 없다.

 

이러한 최악의 시나리오를 방지할수 있도록 만들어진 기술이 바로 트리거이다.

 

트리거는 INSERT, SELECT, DELETE의 행동들을 모두 기록해주는 것이다.

 

이 상황을 방지하려면 "deleteMemberTable" 라는 테이블을 생성하여서 DELETE를 할때마다 작동하도록하는 트리거를 설정한다.

 

트리거를 설정하면 이제부터 "MemberTable"에서 데이터를 삭제할때마다 "deleteMemberTable" 라는 테이블에 삭제된 row를 기록해줄수 있다.

 

만약 트리거가 없더라면 삭제되기 전에 "deleteMemberTable" 에 직접 insert를 해서 데이터를 기록한다음 삭제를 해야한다.

 

만약에 데이터가 많을때를 가정해보면 이 작업은 데이터를 넣고 넣은 데이터를 확인한 다음 delete를 해야한다.

 

이 과정은 서버에 매우 부담을 주며 수많은 시간을 기다려야한다.

 

위와 같은 상황을 방지하도록 도와주는것이 트리거 이므로 마찬가지로 적합한 상황에 쓰면 매우 유용한 기술이 된다.

뷰

뷰는 말그대로 가상의 테이블을 생성해주는 기술이다.

 

트리거 상황과 마찬가지로 이해하기 쉽도록 한가지 시나리오를 가정해보겠다.

1. 나는 회원 테이블에 주소부분을 수정을 할 필요가 있어서 알바를 고용했다.
2. 알바에게 회원 테이블안에 회원들의 주소부분을 수정하라고 시켰다.
3. 회원 테이블에는 주소뿐만 아니라 이름, 전화번호, 주민등록번호 등도 있는데 알바는 그 데이터를 모두 열람할수 있다.

완전 최악 중의 최악 시나리오다.

 

이 상황을 해결하려면 회원 테이블과 똑같은 테이블을 만들어서 회원 테이블안에 있는 데이터를 복제한 테이블안에 데이터를 넣고 알바가 작업이 끝나면 사람이 직접 수작업으로 원래 테이블에 데이터를 알바가 작업한 테이블에 데이터로 바꿔 넣어야 한다.

 

알바를 시킨 이유도 없어지며 완전 비효율적이다.

 

이 상황을 방지하도록 하는것이 바로 뷰이다.

 

뷰는 회원 테이블에서 중요한 정보를 빼고 가상의 테이블을 생성한다.

 

뷰 테이블과 원래 회원 테이블은 연결되어 있으므로 뷰에서 수정한 데이터는 그대로 회원테이블에도 수정된다.

 

이러한 방식을 이용해서 알바에게 작업을 시키면 개인정보 노출도 없고 수작업을 할필요도 없다.

 

또한 알바는 작업해야할 데이터외에는 그 어떤 데이터에 열람할수 없어진다.

 

이것이 바로 뷰의 가상 이상적인 사용법이라고 할수 있다.

스토어드 프로시저

이 기술은 시나리오가 없다.

 

스토어드 프로시저는 DBA가 많이 사용하는 기술이라고 할수 있으며 프로그래밍 언어로 따지면 함수나 모듈화 같은 개념이라고 볼수 있다.

 

데이터 쿼리문을 일일히 하나씩 작성하는 것은 쿼리로 작업할게 많이 없을때 사용해도 별로 상관은 없지만 쿼리문이 30줄이상 넘어가면 쿼리를 일일히 계속해서 작성하면서 테스트해야하는데 많이 귀찮고 구분하기 어려워진다.

 

스토어드 프로시저는 여러 쿼리문을 묶어서 하나의 이름으로 저장한다.

 

쿼리문을 묶어서 저장해놓으면 나중에 쿼리문을 작성할때 같은 쿼리를 계속해서 일일히 작성할필요없이 원래 만들어놓은 쿼리를 쉽게 불러서 사용할수 있다.

 

구분도 쉬우며 쿼리를 묶어서 처리하기 때문에 계속해서 사용할수 있다.