3. MySQL - 인덱스, 뷰, 저장 프로시저, 트리거

<인덱스>
대부분의 책의 제일 뒤에 붙어 있는 '찾아보기(또는 색인)' 같은 개념.
실무에서 사용되는 수천만~수억 건 이상의 데이터에서 인덱스없이 전체 데이터를 찾아본다는 것은 MySQL입장에서는 시간이 오래 걸리는 일임.
즉 쿼리에 대한 응답을 줄이기 위해 가장 집중적으로 보는 부분 중 하나가 '인덱스' 임.
인덱스를 적절히 활용하면 시스템의 성능이 몇 배, 심하게는 몇 십 배 이상 차이가 남.

예시테이블 생성

CREATE TABLE indexTBL
	(first_name varchar(14), last_name varchar(16), hire_date date);
    INSERT INTO indexTBL
    	SELECT first_name, last_name, hire_date
        FROM employees.employees
        LIMIT 500;

이중에서 이름이 Mary라는 사람 검색

SELECT * FROM indexTBL WHERE first_name='Mary';

Execution Plan을 선택(어떻게 찾았는지 보는방법임)

FULL Table Scan: 전체 테이블을 선택했다라는 의미. (Mary를 찾기위해 500개를 전부 봤다라는 의미)

인덱스 생성

CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);

Execution Plan을 선택

이번에는 Non-Unique Key Lookup 인덱스를 통해 찾았다라는 의미

<뷰>

가상의 테이블이라고 생각하면 됨. 사용자의 입장에서는 테이블과 동일하게 보이지만, 실제 행 데이터를 가지고 있지 않음. 진짜 테이블에 링크된 개념임.

뷰 생성문

CREATE VIEW uv_memberTBL
AS
	SELECT memberName, memberAddress FROM memberTBL;

뷰에서 조회

SELECT * FROM uv_memberTBL;

<저장 프로시저>
MySQL에서 제공해주는 프로그래밍 기능임.
실무에서는 SQL문(주로 SELECT)을 매번 하나하나 수행하기 보다는 저장 프로시저로 만들어 놓은 후에 저장 프로시저를 호출하는 방식을 많이 사용.

DELIMITER //
CREATE PROCEDURE 프로시저이름()
BEGIN
	SELECT문
END //
DELIMITER ;

예시

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

실행하려면

call myProc();

<트리거>
테이블에 부착되어서 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되면 실행되는 코드임.
트리거의 용도: 회원에서 탈퇴한 사람이 누구인지 정보를 알때

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

SELECT * FROM memberTBL

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

SELECT * FROM memberTBL;

DELETE FROM memberTBL WHERE memberName='연아';

SELECT * FROM memberTBL;

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
	-- OLD 테이블의 내용을 백업테이블에 삽입
    INSERT INTO deletedMemberTBL
    	VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() );
END //
DELIMITER ;

SELECT * FROM memberTBL;

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

SELECT * FROM memberTBL;

SELECT * FROM deletedMemberTBL;