[MySQL] 데이터베이스 개체의 종류

DBMS (Database Management System) 중 하나인 MySQL에는 데이터베이스를 구성하는 다양한 개체가 존재한다. 어떠한 개체가 존재하고 그것이 무엇인지 간단히 정리하였다.

1. 테이블 (Table)

실질적인 데이터를 저장하는 개체로, 행과 열로 구분된다.

/* 테이블 생성 */
CREATE TABLE db1.table1 (
	column1 CHAR(10) NOT NULL,
	column2 INT(5) NOT NULL,
	PRIMARY KEY (column1));
 
/* 데이터 삽입 */
INSERT INTO db1.table1 (column1, column2)
	VALUE('value1', value2);

/* 테이블 조회 */
SELECT * FROM db1.table1;

2. 인덱스 (Index)

테이블의 열 단위에 인덱스를 생성하여 쿼리에 대한 응답 속도를 향상 시킬 수 있다 (색인 개념). Primary key와 Unique를 설정한 열에는 인덱스가 자동 생성된다(각각 클러스터형 인덱스, 보조 인덱스). 하지만 데이터 변경 작업이 느려질 수 있고, 인덱스를 남용하면 전체 데이터베이스를 훑는 것보다 작업량이 많아져 오히려 문제가 된다. 또 주의해야할 점은 인덱스가 생성된 열 자체에 연산을 가하면 인덱스를 사용하지 않으므로 (WHERE column1*2=5), 값에 연산을 가해야한다(WHERE column1=5/2).

/* 인덱스 생성 */
CREATE INDEX idx_table1_column1 ON table1(column1);

/* 인덱스를 활용한 조회*/
SELECT * FROM table1 WHERE column1 = 'value1';

/* 인덱스 제거 */
DROP INDEX idx_table1_column1 ON table1(column1);

인덱스가 적절한 경우	인덱스가 적절하지 않은 경우
WHERE문에 자주 사용되는 열	데이터의 중복도가 높은 열
JOIN문에 자주 사용되는 열

3. 뷰 (View)

테이블에서 유래한 가상의 테이블로, 원본에 링크되어 있다. 뷰를 통해 원본 테이블의 특정 부분만 접근하게 하여 수정할 수 있다.

/* 뷰 생성 */
CREATE VIEW v_table1
	AS
	SELECT column1 FROM table1;

/* 뷰를 활용한 조회*/
SELECT * FROM v_table1;

4. 스토어드 프로그램 (Stored Program)

특정 작업을 수행하도록 해주는 것으로, 스토어드 프로시저, 트리거, 커서, 스토어드 함수가 있다.

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

여러 개의 SQL문을 한번에 실행할 수 있도록 저장한 개체이다.

/* 스토어드 프로시저 생성 */
DELIMITER //
CREATE PROCEDURE testProc()
BEGIN
	SELECT * FROM table1 WHERE column1 = 'value1';
	SELECT * FROM table1 WHERE column2 = value2;
END //
DELIMITER ;

/* 스토어드 프로시저 실행 */
CALL testProc();

/* 스토어드 프로시저 제거 */
DROP PROCEDURE testProc();

2) 트리거 (Trigger)

어떤 이벤트가 발생했을 때 무엇을 할지 지정해주는 개체이다.

CREATE TABLE deletedTable1 (
	column1 CHAR(8),
    column2 INT(5),
    deletedDATE date
);

/* 트리거 생성 */
DELIMITER //
CREATE TRIGGER trg_deletedTable1
	AFTER DELETE -- INSERT, UPDATE도 가능
    ON table1
    FOR EACH ROW -- 각 행마다 적용
BEGIN
	INSERT INTO deletedTable1
    	VALUES (OLD.column1, OLD.column2, CURDATE());
END //
DELIMITER ;

/* 트리거 실행 */
DELETE FROM table1 where column1='value1';

3) 커서 (Cursor)

어떤 작업을 한 행씩 수행하고자 할 때 커서를 활용할 수 있다.

/* 스토어드 프로시저 생성 */
DELIMITER $$
CREATE PROCEDURE testProc()
BEGIN
    DECLARE testCursor CURSOR FOR
    	SELECT columnAge FROM table1; -- 커서 선언
        
	DECLARE age INT;
	DECLARE totalAge INT DEFAULT 0;
    DECLARE cnt INT DEFAULT 0;
    
    DECLARE endRow BOOLEAN DEFAULT FALSE;
    DECLARE CONTINUE HANDLER
    	FOR NOT FOUND SET endRow = TRUE; -- 행의 끝에 도달했는지
    
   	OPEN testCursor;
    cursorLoop: LOOP
    	FETCH testCursor INTO age;
        IF endRow THEN
        	LEAVE cursorLoop;
        END IF;
        
        SET cnt = cnt +1;
        SET totalAge = totalAge + age;
    END LOOP cursorLOOP;
    
    SELECT CONCAT('평균: ',(totalAge/cnt));
    CLOSE testCursor;
END $$
DELIMITER ;

/* 스토어드 프로시저 실행 */
CALL testProc();

4) 스토어드 함수 (Stored Function)

스토어드 프로시저가 여러 개의 SQL문을 실행하는 등의 용도로 사용된다면, 스토어드 함수는 계산을 통해 하나의 값을 반환하는데 주로 사용된다.

/* 스토어드 함수 생성 */
DROP FUNCTION IF EXISTS testFunc;
DELIMITER $$
CREATE FUNCTION testFunc(value INT)
	RETURN INT
BEGIN
	DECLARE result INT;
    SET result = value - 5;
    RETURN result;
END $$
DELIMITER ;

/* 스토어드 함수 실행 */
SELECT testFunc(10);

/* 스토어드 함수 제거 */
DROP FUNCTION testFunc;

Reference

• 우재남. 이것이 MySQL이다. 한빛미디어, 2016.