데이터베이스의 개체 - 인덱스, 스토어드 프로시저, 트리거

데이터베이스에서 '테이블'은 가장 기본이 되는 요소이다.

하지만 테이블 외에도 데이터베이스를 구성하는 중요한 개체들이 있는데 바로 인덱스, 스토어드 프로시저, 트리거, 함수, 커서 등이다. 

 

이 부분에 대해서는 나도 아직 모르는 부분이 많아서, 상세한 내용은 더 공부를 한 후에 포스팅 하도록 하고, 이 포스팅에서는 각각의 개체들에 대해 간단한 개념만 정리하고자 한다. 

1. 인덱스 (Index)

인덱스는 흔히 책이나 논문 의 맨 마지막 페이지에서 본 적이 있을 것이다. 책에서 특정 단어가 나오는 내용을 찾고자 할 때 인덱스에서 해당 단어에 해당하는 페이지를 확인하면, 바로 그 단어를 찾을 수 있다. 책이 두꺼울 수록 그 효과는 증대된다. 

즉, 인덱스를 활용하여 우리는 훨씬 빠른 속도로 정보를 찾을 수 있다.

 

실무에서는 방대한 양의 데이터를 다루게 되는데, 인덱스를 잘 다룬다면 성능을 향상 시킬 수 있어 튜닝의 기본적이 요소다. 반대로 말하면 인덱스를 잘 다루지 못한다면 시스템의 속도가 매우 느려질 수 있다. 속도가 느린 부분은 기본적으로 인덱스를 활용하지 못해서 발생하는 경우가 있다고 하니, 인덱스에 대한 공부는 필수적인 것 같다. 

 

인덱스는 테이블의 컬럼 단위에 생성이 되는데, 기본 키에 자동으로 생성이 된다.

인덱스는 다음과 같이 SLECT INDEX ~ ON ~ 을 통해 생성할 수 있다.

SELECT INDEX idx_ex ON idxTable(first_name);

이 구문의 의미는 idxTable 라는 테이블에서 first_name 컬럼에 idx_ex 라는 이름의 인덱스를 생성한 것이다.

 

 

2. 뷰 (View)

뷰는  테이블과 동일하게 데이터를 볼 수 있지만, 실제로 데이터를 가지고 있지 않은 "가상의 테이블" 이다. 

예를 들어, 온라인 쇼핑몰 데이터베이스에서 쇼핑몰 회원 정보 중 전화번호를 다수 변경해야 할 일이 생겼을 때, 전체 테이블 자체를 수정할 권한을 직원에게 준다면 회원 정보 중 중요한 개인정보(주민번호, 주소 등) 가 유출 될 수 있다. 

이때, 회원 ID, 이름, 전화번호만 들어 있는 뷰를 생성하여 뷰에만 접근 권한을 준다면, 나머지 중요한 개인정보 유출을 방지 할 수 있다. 

 

뷰는 다음과 같이 생성할 수 있다.

CREATE VIEW uv_UserTBL
AS 
	SELECT user_name, address FROM UserTBL;

 

3. 스토어드 프로시저 (Stored Procedure)

DBMS에서 기본적으로 제공해주는 프로그래밍 기능이다. 

MSSQL 에서는 다음과 같이 다양한 스토어드 프로시저를 확인할 수 있다.

MSSQL의 Stored Procedure

 

또한 반복되는 작업을 직접 프로시저로 만들 수 있다. 

--프로시저 생성
CREATE PROCEDURE myProc(); 
BEGIN
	SELECT * FROM userTBL WHERE UserName = 'Jane';
	SELECT * FROM productTBL WHERE CarName = 'GV80';
END

-- 프로시저 실행
CALL myProc();

 

4.  트리거 (Trigger)

트리거는 테이블에 INSERT, UPDATE, DELETE 같은 작업이 발생되면 실행되는 코드를 의미한다.

예를 들어, 쇼핑몰에서 회원이 탈퇴하는 경우, 해당 회원 정보를 삭제하면 되는데, 나중에 탈퇴한 사람을 알지 못한다.

탈퇴한 사람의 데이터를 '탈퇴 회원 테이블'과 같이 다른 곳에 복사해 놓고, 회원 테이블에서 삭제하면 될 것이다. 

이러한 작업을 자동으로 해주는 것이 트리거이다. 

 

트리거는 다음과 같이 생성할 수 있다.

userTBL에 DELETE 작업이 일어나면, 백업 테이블(withdrawTBL)에 지워진 데이터가 입력되는 트리거이다.

CREATE TRIGGER trg_withdrawTBL -- 생성할 트리거 이름
	AFTER DELETE -- 삭제 후에 작동하도록
	ON userTBL -- 트리거를 부착할 테이블
	FOR EACH ROW -- 각 행마다 적용
BEGIN
    -- OLD 테이블의 내용을 백업테이블 withdrawTBL 에 삽입
	INSERT INTO withdrawTBL
	VALUES (OLD.userID, OLD.userName, OLD.address, CURDATE());      
END

 

 

[참고] 이것이 MySQL이다, 우재남