마리아DB (7) _ 인덱스, 프로시저, 트리거

드디어 마리아DB 정리 마지막 게시글이다. 이번에는 인덱스와 프로시저, 트리거에 대해 정리해보려고 한다.

 

1. 인덱스 (index) 

인덱스는 데이터베이스의 개체 중 하나로, 데이터를 빠르게 조회하기 위해 테이블의 열 단위로 생성되는 개체이다. 책에서의 책갈피와 비슷한 역할을 한다.

 

인덱스를 생성하면 테이블의 모든 데이터를 조회하는 것이 아니라, index를 생성한 열을 별도로 저장해 검색속도를 빠르게 한다. 잘만 사용하면 시스템 성능에 도움이 되지만, 그렇지 않다면 오히려 성능을 떨어뜨릴 수 있다. 

 

인덱스의 종류

인덱스는 크게 클러스터형 인덱스와 보조인덱스가 있다.

• 클러스터형 인덱스
  
  • 기본키를 생각하면 된다. 기본키에 자동으로 클러스터형 인덱스가 생성된다.
• 보조 인덱스
  
  • 보조인덱스는 별도 공간에 생성되는 인덱스이다.
  • create index 명령어를 사용하거나 unique 조건을 지정하면 보조인덱스가 생성된다.

 

인덱스의 장점과 주의사항

장점

• 검색 속도를 향상시킨다.

주의사항

• where절에서 자주 사용되는 열에 만드는 게 좋음
• join에 자주 사용되는 열에 만드는 게 좋음
• DNL (Insert, Update, Delete) 작업이 자주 일어나는 열에는 생성하지 않는 게 좋다.
  • 데이터가 바뀔 때마다 인덱스의 구조가 변경되기 때문에 성능이 나빠진다.
• 사용하지 않는 인덱스는 제거한다.

 

✅ 인덱스 생성하기

CREATE INDEX 인덱스명 ON 테이블명(열이름)

 

위 명령어를 이용해 인덱스를 생성한다. 이렇게 생성되는 인덱스는 보조 인덱스이다.

 

 

 ✅인덱스 삭제하기

DROP INDEX 인덱스 이름 ON 테이블 이름;
ALTER TABLE 테이블 이름 DROP INDEX 인덱스 이름;

 

위 명령어를 이용해 생성된 인덱스를 삭제할 수 있다.

 

 

 

---

2. 스토어드 프로시저

스토어드 프로시저는 쉽게 이야기 하면 특정 작업을 수행하기 위해 명령어들을 모아놓은 것으로 함수랑 비슷한 느낌이다.

변수도 설정할 수 있고, 일반 프로그래밍 언어처럼 IF문, 반복문 등을 사용할 수도 있고, 사용자로부터 입력을 받을 수도 있다. 

 

✅ 프로시저 생성

프로시저를 생성하고 싶다면 아래 코드처럼 작성해주면 된다.

DELIMITER $$
CREATE PROCEDURE 프로시저이름
BEGIN
  -- SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

CALL 프로시저이름();

 

 

프로시저 명령어는 아래에서 예시를 통해 자세히 설명하려고 한다.

 

위 코드를 보면서 설명을 하자면,

 

• delimiter 로 구문의 시작을 알려준다. 위 코드에서 delimiter $는 $를 쓰면 구문을 끝낸다는 뜻이다.
• CREATE PROCEDURE 로 프로시저를 생성할 수 있고, OR REPLACE로 이미 같은 이름의 프로시저가 있을 경우 대체하도록 할 수도 있다. 인자에는 IN으로 입력 받을 변수를 지정할 수 있다.
• BEGIN, END 사이에 쿼리문을 작성해주면 된다. 
• DECLARE로 변수를 설정할 수 있다.

 

입력을 받지 않을 때는 () 안에 안 쓰면 된다. 아래 코드는 1~10까지의 합계를 구하는 코드이다.

 

 

이 외에도 IF문, CASE문 등을 사용할 수 있다! 사용할 때 세미콜론 빼먹지 않도록 주의하자.

왼 : CASE문, 오 : IF문

 

 

✅ 프로시저 삭제

프로시저를 삭제할 때에는 DB나 테이블 삭제하는 것과 마찬가지로 DROP을 사용한다.

DROP PROCEDURE 프로시저이름;

 

---

3. 트리거

쇼핑몰의 DB를 구축한다고 할 때, 누군가가 물건을 사갈 때마다 재고 수량이 자동으로 변경되어야 할 것이다. 이렇게 테이블에 수정, 삽입, 삭제 등 작업이 발생할 때, 자동으로 작동되도록 하는 것이 트리거이다.

 

 

트리거 종류

트리거가 작동되는 타이밍에 따라 두가지 종류로 나눌 수 있다.

• AFTER 트리거 : 테이블의 변화 후에 작동하는 트리거
• BEFORE 트리거 : 테이블의 변화 전에 작동하는 트리거

 

트리거 생성

마찬가지로 CREATE TRIGGER 구문을 사용한다.

DELIMITER $$
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER INSERT|UPDATE|DELETE ON 테이블명
FOR EACH ROW
BEGIN
    -- SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

 

 

아래 예시코드는 입고 되면 재고 수량을 늘리고, 출고되면 줄이는 트리거를 생성한 쿼리문이다.

 

여기서 NEW는 새로 들어온 행을 의미한다. NEW는 AFTER 트리거에서만 사용할 수 있다!

 

트리거 삭제

마찬가지로 DROP 명령어를 사용한다.

DROP TRIGGER 트리거 이름;

 

 

이렇게 마리아DB 정리가 끝났다 !