MySQL 기초 기능 (2)

테이블 외에 DB 개체 활용

 

- 인덱스

 

 방대한 데이터를 일일이 탐색해서 원하는 데이터를 추출하는 것은 DBMS 측면에서 부담스럽기도 하고 비효율성을 초래하기도 한다. 이를 고려해 실제로 데이터를 검색하기 위해 자주 쓰이는 요소(열)를 주소록처럼 색인으로 두고 해당 데이터를 빠르게 조회할 수 있게 하는 '인덱스'를 생성해 두고 활용하는 것이 DB 업무에 큰 효율을 가져다준다. 오늘 연습해 본 쿼리를 통해 어떤 식으로 사용하는지 간단히 알아보자.

 

CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name) ;

SELECT * FROM indexTBL WHERE first_name = 'Mary' ;

-- 결과창 우측 메뉴에 보면 'Execution Plan'이 있는데 인덱스가 있고 없고에서
-- 같은 데이터 검색 작업에서도 차이가 생긴다. 이는 데이터의 양이 크면 클 수록
-- 더 큰 격차를 만들어낸다. 인덱스를 적재적시에 사용하는 것이 실무에서 중요하다.

 

- 뷰

 

 '뷰'는 한 테이블에 링크된 가상의 테이블 같은 개념으로 특정 목적으로 보여줘야하는 칼럼만 따로 권한을 부여해 보여주는 기능이다. 예를 들어, 회원정보 중에서 주민번호 같은 민감한 데이터의 경우 간단한 주소 변경 작업에서 노출이 되는 것을 막는 것이 개인정보보호의 차원에서 필요하다. 이럴 때 뷰를 사용해 같은 테이블을 사용하지만 필요한 정보만 보여줄 수 있는 것이다. 연습 예시를 통해 사용법을 익혀보자.

 

CREATE VIEW uv_memberTBL -- uv (User View)
AS
	SELECT memberName, memberAddress FROM memberTBL ;
    
SELECT * FROM uv_memberTBL ;

 

가장 밑줄의 쿼리를 실행하면 앞에서 지정했듯이 회원명과 회원주소만 기존 테이블에서 따로 빼서 보여준다.

 

- 스토어드 프로시저

 

 해당 기능은 간단하게 MySQL에서 제공하는 프로그래밍 기능 같은 것이다. 바로 연습예제를 통해 살펴보자.

 

-- SELECT * FROM memberTBL WHERE memberName = '당탕이' ;
-- SELECT * FROM productTBL WHERE productName = '냉장고' ;
-- 앞의 두 줄의 쿼리문을 반복해서 사용하게 된다면 상당히 귀찮고 오타를 통한 오류가 많을 수도 있다.
-- 이때 사용하는 반복작업을 하나의 함수로 묶어주는 MySQL의 프로그래밍적 기능이 '스토어드 프로시저'이다. 

DELIMITER //
CREATE PROCEDURE myPROC()
BEGIN
	SELECT * FROM memberTBL WHERE memberName = '당탕이' ;
    SELECT * FROM productTBL WHERE productName = '냉장고' ;
END //
DELIMITER ;

CALL myPROC() ;

 

 추가적으로 설명하자면 'DELIMITER //'는 기존에 ;로 단락을 끝낸 것을 //로 단락 구분을 일시적으로 바꾼다는 것이다. 이를 통해서 중간에 '당탕이'와 '냉장고'를 검색하는 과정에서 ;를 사용해도 시행과정 설정문이 종료되지 않을 수 있는 것이다. 이처럼 이외에도 긴 쿼리를 자주 반복적으로 사용할 때 하나로 묶어 프로그래밍하는 것이 '스토어드 프로시저'다.

 

- 트리거 

 

 테이블에서 데이터 변경 작업을 실행할 때 '트리거'를 해당 테이블에 부착하여 자동으로 데이터 변경 과정에서 기록을 남기는 테이블을 형성해 정보의 히스토리를 이용한 작업을 간편하게 할 수 있도록 돕는 기능이다.

 

INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동') ;

-- SELECT * FROM memberTBL ; 으로 매번 정보처리가 제대로 됐는지 확인 가능하다. 

SELECT * FROM memberTBL ;

UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동' WHERE memberName = '연아' ;

DELETE FROM memberTBL WHERE memberName = '연아' ;

-- 위의 쿼리들로 회원정보를 테이블에 입력하고 변경하는 과정을 거쳤지만 지워진 정보는 어딘가에 저장되지 않고 해당 회원이 존재했다는 사실 증명이 안 된다. 
-- 이런 상황을 위해서 매번 데이터를 삭제할 때마다 지워진 정보를 자동으로 저장해주는 트리거를 사용하는 것이다. 정보를 지운 날짜도 기록하는 쿼리를 연습해보자. 

CREATE TABLE deletedMemberTBL (
	memberID CHAR(8) ,
    memberName CHAR(5) ,
    memberAddress CHAR(20) ,
    deletedDate DATE
) ;

DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL
	AFTER DELETE
    ON memberTBL
    FOR EACH ROW
BEGIN
	INSERT INTO deletedMemberTBL
		VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() ) ;
END //
DELIMITER ;

DELETE FROM memberTBL WHERE memberName = '당탕이' ;

SELECT * FROM deletedmembertbl ; -- 지운 행이 트리거를 통해 삭제 테이블에 잘 저장이 됐는지 확인.

 

 위에서는 트리거를 통해 회원의 정보를 삭제하더라도 나중에 해당 회원이 존재했다는 사실과 언제 데이터를 지웠는 지를 자동으로 알 수 있어 사용기록과 관련한 증명을 요구하는 일이 있다면 유용하게 쓸 수 있을 것이다.