Mysql objects 개념 정리 for 개발자

Mysql objects 개념 정리 for 개발자

대표이미지

여러분들이 쓰는 Mysql 내에는 어떤 objects들이 있고, 각 object가 Mysql이라는 DBMS내에서 어떤 역할을 하는지 잘 알고 계십니까?

그동안 DB쪽에서 업무를 하면서 수없이 많은 개발자분들을 만나고, 굳이 개발자가 아니더라도 PM이나 PL, 퍼블리셔, 디자이너 분들을 보아오면서 Mysql DB를 쓰지만 정작 테이블내 데이터는 알지만 나머지 objects들에 관해서 잘 알고 계시는 분들은 많이 안계시더라구요~

물론 제가 내심 똥줄이 탈만큼 긴장 하게 만드시는 DB에 해박하신 개발자님들도 계시지만..

Mysql 안에서 SQL말고도 개발자 분들이 꼭 알고 넘어가야 하는 부분에 대해 간략하게 안내서를 작성 해 보겠습니다.

주로 쓰이는 objects를 위주로 정리를 하겠습니다.

※ 포스팅 내용은 Mysql을 기준으로 작성이 됨을 알려드립니다. 하지만 oracle 이나 MSSQL 이나 Postgres 나 대부분 objects들의 개념을 거의 같으니 구분짓지 않고 보셔도 될 것 같습니다.

목차

1. schema 2. synonym 3. table 4. view 5. index 6. sequence 7. procedule 8. function 9. trigger

먼저 스키마 입니다.

Mysql에서는 스키마와 데이터베이스란 용어를 혼용합니다.

Oracle을 먼저 접하신 분들은 조금 헷갈릴수 있으나 오라클과 에서 스키마라는 개념을 Mysql에서 스키마 혹은 데이터베이스라고 부르고 있습니다.

아래 그림을 보시면 이해가 쉽겠습니다.

위 그림에서 처럼 Mysql의 경우 스키마를 데이터베이스라고도 부릅니다.

그럼 스키마란 데이터베이스 내의 구조와 제약 조건의 정의 한 것 이라고 볼 수 있으며, 사용자의 따라 접근을 제한하는 등의 관리가 가능한 집합을 이야기 합니다. 하나의 스키마 안에 여러개의 테이블을 생성 할 수 있으며, 각각의 스키마 별로 케릭터셋을 구분하여 지정 할 수도 있습니다.

큰 회사의 경우 업무 영역 별로 스키마를 나누기도 하고, 어떤 테이블 성격에 따라 스키마를 나눌수도 있습니다.

사실 스키마를 object 라고 정의 할 순 없습니다. 스키마 내부에 다양한 objects를 생성 할 수 있기 때문 입니다.

◎생성예문

CREATE SCHEMA test DEFAULT CHARACTER SET utf8;

CREATE DATABASE test DEFAULT CHARACTER SET utf8;

시노님 입니다.

시노님은 권한과 함께 연계해서 생각해야 합니다.

데이터베이스내 권한이라는 개념과 밀접한 관련이 있기 때문입니다.

데이터베이스 내에 어떤 테이블이 있다고 가정 할때, 어떤 유저가 해당 테이블과 스키마에 권한을 가지지 못한채 해당 테이블 정보를 조회 하고 싶다면? 그럴때 시노님을 해당 유저가 권한을 가진 스키마내에 생성을 해주면 됩니다.

oracle의 경우 아래와 같이 시노님을 두가지 종류로 구분 할 수 있습니다.

또한 권한문제를 떠나서 시노님을 통해 어떤 테이블의 이름을 단순화 하거나 통일 시킬수 있기때문에 개발에 유용하게 사용 할 수도 있습니다.

원본 객체를 참조만 하는 시노님을 만듦으로 해서 원본 객체를 숨기거나 보안을 유지 할 수도 있다.

특정 테이블명이 변경되거나 했을때 시노님을 사용하므로 해서 시노님만 다시 정의 해주면 개발 소스를 그대로 유지 할 수 있는 장점이 있습니다.

◎생성예문

 CALL sys.create_synonym_db('INFORMATION_SCHEMA', 'info');

여러분들이 다 아는 테이블 입니다.

테이블은 열과 행으로 구성되어 있습니다. 더불어 테이블안에는 값이 들어가 있고, 이 값들을 관리하고 유지하기 위해 키라는 개념이 사용 됩니다.

또한 각 열은 해당 열에 알맞는 자료형을 가지고 있습니다. 텍스트면 텍스트, 숫자면 숫자, 날짜면 날짜. 각 열의 데이터 성격을 나타냅니다. 그리고 행은 레코드 혹은 튜플이라고도 부릅니다.

DBMS내 모든 데이터는 테이블에 다 담겨 있다고 생각 하시면 됩니다.

모든 DBMS내 objects들은 이 테이블의 데이터를 기반으로 생성되고 작동 된다고 보시면 됩니다.

◎생성예문

CREATE TABLE dept (

  dept_no INT(11) unsigned NOT NULL,

  dept_name VARCHAR(32) NOT NULL,

  PRIMARY KEY (dept_no)

);

뷰는 무엇일까요?

뷰란 어떤 사용자에게 접근이 허용된 자료를 제한적으로 보여주기 위하여 하나 이상의 테이블로 부터 쿼리한 데이터의 집합 입니다. 이 뷰는 DBMS내에 물리적으로 존재하지 않습니다. 하지만 마치 테이블처럼 사용자가 select 할 수 있습니다. 

뷰에 update 나 delete, insert는 불가능 합니다. 뷰내에 정의된 원 데이터를 가진 테이블내 데이터를 조작해야 뷰의 데이터가 변경 될 수 있습니다.

자주 사용하는 복잡한 select SQL을 뷰로 생성 해두면 편하게 조회 할 수 있습니다.

뷰를 통해 데이터 접근성을 높일수도 있고, 보안을 강화 할 수 있습니다. 그리고 한번 정의 된 뷰는 다른 뷰에서 호출 하여 사용 할 수도 있습니다. 하지만 주의 할 점은 뷰에 정의된 테이블이나 뷰를 삭제 하면 해당 뷰도 함께 삭제 됩니다.

또한 독립적으로 뷰에 인덱스를 생성 할 수 없으니, 이점을 잘 알고 있어야 합니다.

뷰는 변경 할 수 없습니다. 삭제 후 재 생성 해야 합니다.

◎생성예문

--문법--

CREATE VIEW 뷰이름[(속성이름[,속성이름])]AS SELECT문;

--고객 테이블에서 주소가 서울시인 고객들의 성명과 전화번호를 서울고객이라는 뷰로 만들어라--

CREATE VIEW 서울고객(성명, 전화번호)

AS SELECT 성명 전화번호

FROM 고객

WHERE 주소 = '평택시';

인덱스에 대해 알아 보겠습니다.

인덱스는 말그대로 색인 입니다.

이 인덱스가 있음으로 해서 우리는 테이블내 수많은 데이터중 우리가 원하는 데이터를 빠르게 찾아 올 수 있게 됩니다.

일반적으로 Mysql 에서는 B-Tree 인덱스를 사용하는데, 이 B-Tree 인덱스는 Root - Branch - Leaf 순으로 디스크 저장소에 데이터가 저장이 됩니다.

인덱스는 일반적으로 잘 만들어 졌다면 select 및 update, delete 속도를 높여주지만 또한 update와 delete, insert의 속도는 낮아지는 단점이 있습니다. 하지만 그 단점이 모두 커버 될만큼의 빠른 조회 속도를 가지고 올 수 있으므로 유용하게 쓴다면 정말 좋습니다.

인덱스에 관해서는 아래 다른 포스팅에 더욱 자세히 기술 하였으니 참고 하시기 바랍니다.

2020/07/10 - [Database/sql 강의] - index 의 중요성과 이해 SQL 독학 강의#24편

index 의 중요성과 이해 SQL 독학 강의#24편

◎생성예문

-- 인덱스 테이블과 함께 생성

CREATE TABLE test

(

    Column

 

    INDEX <Index name> ( column 1, column 2 )

    OR

    UNIQUE INDEX <Index name> ( column )

)

-- 인덱스 단독 생성

CREATE INDEX <Index name> 

ON <Table name> ( column 1, column 2, ... );

 

또는

 

ALTER TABLE <Table name> 

ADD INDEX <Index name> ( column 1, column 2, ... );

-- 인덱스 삭제

ALTER TABLE <Table name> DROP INDEX <Index name>;

시퀀스 입니다.

시퀀스란 하나씩 증가하는 자동 생성 번호를 말 합니다.

로그성 데이터를 입력하거나, 어떤 센서값을 입력하거나 할때 유용하게 쓰이는 object 입니다.

Mysql에서는 auto_increment 옵션으로 테이블 생성시에 적용하여 사용 할 수 있습니다.

시퀀스는 중간에 값을 초기화 할수도 있고, 어떤 특정 숫자부터 시작 하도록 설정 할 수도 있습니다. 이 auto_increment는 Mysql이 재시작 하거나 기존 데이터를 삭제하거나 했을때 시작하는 순번에 갭ㅇ ㅣ생길수 있습니다. 스토리지 별로도 다를 수 있습니다. 이를 잘 유념하여 운영에 적용 하여야 합니다.

◎생성예문

ALTER TABEL 'TEST' MODIFY 'num' INT NOT NULL AUTO_INCREMENT;

-- auto increment를 특정값으로 초기화 할때

ALTER TABLE test AUTO_INCREMENT=1;

프로시저에 관해서 알아보겠습니다.

프로시저는 조금 어려운 말로 저장 프로시저 또는 stored procedule 라고도 부릅니다. 여러개의 SQL들을 한번에 순차적으로 실행 할 수 있습니다. 이 프로시저를 통해서 관계형 데이터베이스인 Mysql에서 어떤 유기적인 데이터 흐름을 제어하거나 만들어 낼 수 있습니다.

또한 아래에서 설명 드릴 펑션과는 달리 여러개의 row를 리턴 할 수도 있습니다.

여러가지 복잡하고, 하나의 SQL로 처리 하기 힘든 작업들을 한번에 처리 할 수 있는 장점이 있고, if, for, while 문등의 프로그래밍 적인 요소를 이용 할 수도 있습니다. 이를 이용해서 동적이고 반복적인 SQL문을 단번에 실행 할 수 있습니다.

굳이 자바나 파이썬 같은 프로그래밍 언어에 의존 하지 않고도 어느정도 프로그램을 구현 할 수 있는 수준을 제공 할 수 있습니다.

다만 디버깅이 어렵고 복잡하기 때문에 유지보수에 비용이 많이 들고, Mysql에서는 처리성능이 떨어진다는 단점이 있습니다.

◎생성예문

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`( IN b_code varchar(255))

BEGIN

DECLARE _STATEMENT VARCHAR(2500);

    SET _STATEMENT = CONCAT('SELECT * ');

    SET _STATEMENT = CONCAT(_STATEMENT, ' FROM ',b_code,' WHERE 1=1 ');

    SET _STATEMENT = CONCAT(_STATEMENT);  

    SET @STATEMENT = _STATEMENT;

    PREPARE DYNQUERY FROM @STATEMENT;

    EXECUTE DYNQUERY;

    DEALLOCATE PREPARE DYNQUERY;

 

 

END

펑션이란?

펑션은 어떤 값들이 들어와서 로직을 거쳐 가공된 값을 출력 해주는 object 입니다.

return으로 값을 반환 하며, 우리가 흔히 쓰는 max, substr, left, round 등도 이 펑션의 한 종류 입니다.

어떤 값들이 들어오고, 그 값들을 이용해서 어떤 로직을 통해 결과값이 나오게 되는것 이죠.

데이터를 가공하는데 있어서 복잡한 SQL을 간단하게 만들어줄수 있는 장점이 있습니다.

또한 프로시저와 마찬가지로 내부에서 어떤 약속된 데이터 흐름을 제어하거나 데이터를 가공을 할 수 있습니다.

프로시저와 다른점은 펑션은 오직 하나의 값만 리턴 할 수 있다는 점이 되겠습니다.

◎생성예문

DELIMITER $$

 

CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)

    DETERMINISTIC

BEGIN

    DECLARE return varchar(10);

 

    IF p_creditLimit > 50000 THEN

        SET lvl = 'PLATINUM';

    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN

        SET lvl = 'GOLD';

    ELSEIF p_creditLimit < 10000 THEN

        SET lvl = 'SILVER';

    END IF;

 

 RETURN (return);

END

마지막 트리거 입니다.

트리거는 어떤 테이블에 데이터가 입력, 삭제, 또는 변경 되었을때 해당 작업을 자동으로 감지하여 다른 작업을 수행하게 하는 것을 말합니다. 이때 특정 작업을 이벤트라고 부르는데, DML, DDL, 데이터베이스 동작을 감지 할 수 있습니다.

또한 트리거는 아래와 같은 옵션을 가지고 있습니다.

BEFORE or AFTER : 트리거가 실행되는 시기를 지정합니다.

INSTEAD OF : 트리거를 원래 문장 대신 수행합니다.

WHEN : 트리거를 시작하는 조건식을 지정합니다.

예를 들어서, 트리거를 통해서 특정 테이블에 값이 갱신 되면 다른 테이블에 어떤 로그를 기록 하게 할 수도 있고, 어떤 값이 들어오기전에 다른 어떤 테이블에 특정 값을 넣고 들어가도록 할 수도 있습니다.

이와 같이 트리거는 복잡한 작업을 자동으로 수행 할 수 있게 해주는데, 이 트리거를 사용함에 있어서는 많은 주의가 필요합니다.

우선 트리거는 DB내 작업 수행 속도를 떨어뜨리게 하는 주요요인중 하나 입니다. 또한 트리거를 잘못 지정하면 쓸때 없는 잘못된 데이터들의 갱신이나 입력, 삭제가 일어날 수 있습니다.

그래서 현업에서는 대부분 트리거의 사용을 지양하고 있습니다.

◎생성예문

CREATE TRIGGER dbo.trg_Test ON dbo.trg_table

AFTER INSERT

AS

BEGIN

  WAITFOR DELAY '00:00:00'

END

GO

DECLARE @ST DATETIME

DECLARE @ET DATETIME

DECLARE @I INT

SET @I = 1

SET @ST = GETDATE()

WHILE @I <= 20000

BEGIN

  INSERT INTO trg_table (C1, C2) VALUES (@I, 'Trg Test')

  SET @I = @I + 1

END

이상으로 위 9개의 objects 들에 대해서 간단하게 설명을 해보았습니다.

최대한 핵심만 간략하게 정리를 하려고 노력하다보니 좀 빠진것도 있을것 같습니다.

위 내용과 관련하여 궁금한게 있으신 분들은 댓글로 남겨 주시면 답변을 드리도록 하겠습니다.

감사합니다!!!

open.kakao.com/o/szfhqYec

Database/남/db개발님의 오픈프로필

by.sTricky