(2주차 12일) 인덱스, 프로시저, 트리거

DB modeling

> 인덱스(Index)

• 테이블의 열 단위에 생성되는 데이터베이스의 개체로 데이터를 조회할 때 빠르게 접근하도록 도와주는 역할
  
  1. 클러스터형 인덱스(Clusterd Index): 테이블당 한 개만 생성 가능, 데이터가 정렬되어 저장 -> 검색 속도 빠름, 데이터 저장 느림
     * 기본 키 제약조건으로 지정되는 열에 자동으로 생성됨
  2. 보조 인덱스(Secondary Index): 테이블당 여러 개 생성 가능, 정렬 필요 X -> 검색 속도 느림, 데이터 저장 빠름
     * UNIQUE 제약 조건으로 지정되는 열에 자동으로 생성됨

• B-트리(B-tree)
  1. 이진 트리를 확장해 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리 구조
  2. 노드에 저장된 KEY들은 항상 정렬된 상태로 저장됨

/*
* 인덱스(Index)
*/
SELECT * FROM employees;
EXPLAIN SELECT * FROM employees;

SELECT * FROM employees;
EXPLAIN SELECT * FROM employees WHERE emp_no = 499886;

CREATE INDEX idx_employees_first_name ON employees(first_name);
CREATE INDEX idx_employees_first_last_name ON employees(first_name, last_name);

SELECT * FROM employees;
EXPLAIN SELECT * FROM employees WHERE first_name = 'moon' AND last_name = 'Yetto';

ANALYZE TABLE employees;

-- #테이블에 지정된 인덱스 확인
SHOW INDEX FROM employees;

-- 인덱스 삭제
DROP INDEX idx_employees_first_name ON employees;
DROP INDEX idx_employees_first_last_name ON employees;

-- 인덱스 여부에 따라 쿼리 실행 시간에 유의미한 차이
CREATE TABLE emp_copy ( SELECT * FROM employees );

SELECT * FROM employees WHERE emp_no = 100000;
SELECT * FROM emp_copy WHERE emp_no = 100000;

EXPLAIN SELECT * FROM employees WHERE emp_no = 100000;
EXPLAIN SELECT * FROM emp_copy WHERE emp_no = 100000;

EXPLAIN SELECT * FROM employees WHERE emp_no < 11000;
EXPLAIN SELECT * FROM emp_copy WHERE emp_no < 11000;

-- #인덱스를 사용하지 않는 경우: 데이터 범위의 약 절반 이상 검색하는 경우
SELECT * FROM employees;
SELECT * FROM emp_copy;

EXPLAIN SELECT * FROM employees;
EXPLAIN SELECT * FROM emp_copy;

EXPLAIN SELECT * FROM employees WHERE emp_no < 400000;
EXPLAIN SELECT * FROM emp_copy WHERE emp_no < 400000;

-- #인덱스로 지정된 열 자체에 연산이 적용되는 경우 인덱스로 검색하지 않음
EXPLAIN SELECT * FROM employees WHERE emp_no * 1 = 100000;
EXPLAIN SELECT * FROM emp_copy WHERE emp_no * 1 = 100000;
-- 인덱스로 지정된 열의 검색값에 연산이 적용되는 경우 정상적으로 인덱스를 사용함
EXPLAIN SELECT * FROM employees WHERE emp_no = 100000 * 1;
EXPLAIN SELECT * FROM emp_copy WHERE emp_no = 100000 * 1;

-- #중복되는 열이 다량 있는 경우, 인덱스로 검색하지 않음
ALTER TABLE employees ADD INDEX idx_employees_gender (gender);
ANALYZE TABLE employees;
SHOW INDEX FROM employees;

SELECT * FROM employees WHERE gender = 'M';
SELECT * FROM emp_copy WHERE gender = 'M';

EXPLAIN SELECT * FROM employees WHERE gender = 'M';
EXPLAIN SELECT * FROM emp_copy WHERE gender = 'M';

ALTER TABLE employees DROP INDEX idx_employees_gender;

•  

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

• MariaDB에서 제공되는 프로그래밍 기능으로, 쿼리문을 다시 입력할 필요 없이 호출해서 실행 결과를 얻을 수 있음

/*
* 스토어드 프로시저(Stored Procedure)
*/
-- #프로시저 생성
DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN 
  SELECT * FROM usertbl;
END$$
DELIMITER ;

CALL userProc();

-- #매개변수 사용
DELIMITER $$
CREATE OR REPLACE PROCEDURE userProc (
	IN userName VARCHAR(10)
)
BEGIN
	SELECT * FROM usertbl WHERE NAME = userName;
END$$
DELIMITER ;

CALL userProc('조관우');

-- e.g. 지역, 전화번호 앞자리 3자를 입력받아 usertbl 데이터를 반환
DELIMITER $$
CREATE OR REPLACE PROCEDURE userProc (
	IN address VARCHAR(10), mobileFront VARCHAR(10)
)
BEGIN
	SELECT * FROM usertbl WHERE address = addr and mobile1 = mobileFront;
END$$
DELIMITER ;

CALL userProc('경기', '018');

-- e.g. 사용자의 아이디를 입력받아 이름을 userName 변수에 반환
DELIMITER $$
CREATE OR REPLACE PROCEDURE userProc (
	IN id CHAR(8),
	OUT userName CHAR(20)
)
BEGIN
	SELECT NAME INTO userName
	FROM usertbl WHERE userid = id;
END$$
DELIMITER ;

CALL userProc('JYP', @NAME);
SELECT @NAME;

-- #제어문
-- IF
DELIMITER $$
CREATE OR REPLACE PROCEDURE empProc (
	IN id CHAR(3)
)
BEGIN
	DECLARE YEAR INT;
	
	SELECT YEAR(hire_Date) INTO year
	FROM employee WHERE emp_id = id;
	
	IF YEAR >= 2010 THEN
		SELECT '2010년도에 입사하셨네요!';
	ELSEIF YEAR > 2000 THEN
		SELECT '2000년도에 입사하셨네요!';
	ELSE
		SELECT '1900년도에 입사하셨네요!';
	END IF;	
	
END$$
DELIMITER ;

CALL empProc('215');

-- CASE
DELIMITER $$
CREATE OR REPLACE PROCEDURE gradeProc (
	IN score INT
)
BEGIN
	DECLARE grade CHAR(1);
	
	CASE
		WHEN score >= 90 THEN
			SET grade = 'A';
		WHEN score >= 80 THEN
			SET grade = 'B';
		WHEN score >= 70 THEN
			SET grade = 'C';
		WHEN score >= 60 THEN
			SET grade = 'D';
		ELSE
			SET grade = 'F';
	END CASE;
	
	SELECT score AS '점수', grade AS '등급';
END$$
DELIMITER ;

CALL gradeProc(80);

DELIMITER $$
CREATE OR REPLACE PROCEDURE gradeProc (
	IN score INT
)
BEGIN
	DECLARE grade CHAR(1);
	
	IF score >= 90 THEN
			SET grade = 'A';
		ELSEIF score >= 80 THEN
			SET grade = 'B';
		ELSEIF score >= 70 THEN
			SET grade = 'C';
		ELSEIF score >= 60 THEN
			SET grade = 'D';
		ELSE
			SET grade = 'F';
	END IF;
	
	SELECT score AS '점수', grade AS '등급';
END$$
DELIMITER ;

CALL gradeProc(65);

-- #반복문
-- WHILE
DELIMITER $$
CREATE OR REPLACE PROCEDURE sumProc ()
BEGIN
	DECLARE i INT;
	DECLARE SUM INT;
	
	SET i = 1;
	SET SUM = 0;
	
	WHILE (i <= 10) DO
		SET SUM = SUM + i;
		SET i = i + 1;
	END WHILE;
	
	SELECT SUM;
END$$
DELIMITER ;

CALL sumProc();

-- e.g. 구구단 출력
DELIMITER $$
CREATE OR REPLACE PROCEDURE multiProc(
	IN num INT	
)
BEGIN
	DECLARE i INT;
	DECLARE result VARCHAR(100);
	
	SET i = 1;
	SET result = '';
	
	WHILE(i < 10) DO
		SET result = CONCAT(result, num, ' * ', i, ' = ', num * 1, '\n');
		SET i = i + 1;
	END WHILE;
	
	SELECT result;
END$$ 
DELIMITER ;

CALL multiProc(2);

-- 오류처리 테스트
DELIMITER $$
CREATE OR REPLACE PROCEDURE errorProc()
BEGIN
	DECLARE CONTINUE handler FOR 1146 SELECT '테이블이 존재하지 않습니다.'
	AS '메시지'; SELECT * FROM noTable;
END$$ 
DELIMITER ;

CALL errorProc();

DROP PROCEDURE empProc;

 

> 트리거(Trigger)

• 테이블에 삽입, 수정, 삭제 등의 작업이 발생할 때 자동으로 작동되는 개체
  
  1. AFTER 트리거
  2. BEFORE 트리거

/*
* 트리거
*/
-- 트리거 e.g.
-- 상품 정보 테이블 생성
CREATE TABLE tb_product (
	pcode INT AUTO_INCREMENT PRIMARY KEY,
	pname VARCHAR(100),
	brand VARCHAR(100),
	price INT,
	stock INT DEFAULT 0
);

INSERT INTO tb_product (pname, brand, price)
VALUES ('아이폰12미니', '애플', 950000);

INSERT INTO tb_product (pname, brand, price)
VALUES ('갤럭시23', '삼성', 1000000);

-- 2. 상품 입/출고 상세 이력 테이블 생성
CREATE TABLE tb_prodetail (
	dcode INT AUTO_INCREMENT PRIMARY KEY,
	pcode INT REFERENCES tb_product,
	STATUS VARCHAR(2) CHECK(STATUS IN ('입고', '출고')),
	amount INT,
	ddate DATE DEFAULT CURDATE()
);

-- 1번 상품이 2024-05-01 날짜로 10개 입고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (1, '입고', 10, '2024-05-01');

-- 1번 상품의 재고 수량도 변경되어야 한다.
UPDATE tb_product SET stock = stock + 10
WHERE pcode = 1;

-- 1번 상품이 2024-05-02 날짜로 5개 출고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (1, '출고', 5, '2024-05-02');

UPDATE tb_product SET stock = stock - 5
WHERE pcode = 1;

-- 2번 상품이 2024-05-06 날짜로 20개 입고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (2, '입고', 20, '2024-05-06');

-- 2번 상품의 재고 수량도 변경되어야 한다.
UPDATE tb_product SET stock = stock + 20
WHERE pcode = 2;

-- #tb_prodetail 테이블에 데이터 삽입 시 tb_product 테이블에 재고 수량이
-- 자동으로 업데이트 되도록 트리거를 생성
DELIMITER $$
CREATE OR REPLACE TRIGGER trg_pro_stock
AFTER INSERT ON tb_prodetail
FOR EACH ROW 
BEGIN
	-- 상품이 입고된 경우(재고 증가)
	IF NEW.status = '입고' THEN
		UPDATE tb_product
		SET stock = stock + NEW.amount
		WHERE pcode = NEW.pcode;
	END IF;
	-- 상품이 출고된 경우(재고 감소)
	IF NEW.status = '출고' THEN
		UPDATE tb_product
		SET stock = stock - NEW.amount
		WHERE pcode = NEW.pcode;
	END IF;
END$$
DELIMITER ;

-- 2번 상품이 2024-05-07 날짜로 20개 입고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (2, '입고', 20, '2024-05-07');
SELECT * FROM tb_prodetail;
SELECT * FROM tb_product;

-- 2번 상품이 2024-05-07 날짜로 30개 출고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (2, '출고', 30, '2024-05-07');
SELECT * FROM tb_prodetail;
SELECT * FROM tb_product;

-- 1번 상품이 오늘 날짜로 100개 입고
INSERT INTO tb_prodetail(pcode, STATUS, amount, ddate)
VALUES (1, '입고', 100, DEFAULT);
SELECT * FROM tb_prodetail;
SELECT * FROM tb_product;

-- 트리거 삭제
DROP TRIGGER trg_pro_stock;
DROP TABLE tb_prodetail, tb_product;