SQL(3)-뷰, 인덱스, 스토어드 프로시저, 트리거

뷰

뷰는 한마디로 '가상의 테이블'이라 부릅니다. 일반 사용자 입장에서는 테이블과 동일하게 보이기 때문입니다. 뷰를 사용하면 사용자에게 테이블의 필요한 내용만 보이도록 할 수 있습니다. 뷰를 만드는 기본형식은 다음과 같습니다.

create view 뷰_이름
as
select 문;

뷰를 사용하는 이유는 무엇이 있을까요?

• 보안에 도움이 됩니다. 데이터베이스는 사용자마다 테이블에 접근하는 권한에 차별을 둬서 처리가 가능합니다.
• 복잡한 SQL문을 단순하게 만들 수 있습니다.
• 뷰를 생성하면 뷰의 열 이름을 테이블과 다르게 지정할 수 있으며, 띄어쓰기나 한글이 가능합니다.

뷰의 실제 작동해보겠습니다.

use market db;
create view v_viewtest1
as 
select B.mem_id 'Member ID', M.mem_name as 'Member Name',
B.prod_name "Product Name",
CONCAT(M.phone1,M.phone2) AS "Office Phone"
FROM buy B
INNER JOIN member M
on B.mem_id = M.mem_id;

SELECT DISTINCT `Member ID`, `Member Name` from v_viewtest1;

뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지저할 수도 있는데, 이떄 중간에 띄어 쓰기사용이 가능합니다.단, 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(`) 으로 묶어줘야 합니다.

---

인덱스

 

1.인덱스 개념

인덱스는 추가적인 쓰기 작업과 저장공간을 활용한 데이터베이스 테이블의 검색속도를 향상시키기 위한 자료구조입니. 인덱스에는 클러스터형 인덱스, 보조형인덱스가 존재합니다. 클러스터형 인덱스는 기본키로 지정하면 자동 생성되며 테이블에 1개만 만들 수 있습니다. 영어사전처럼 이미 순서대로 정렬되있는 것을 의미합니다. 보조 인덱스는 고유키로 지정하면 자동 생성되며 여러 개를 만들 수도 있지만 자동 정렬되지 않습니다. 찾아보기 책처럼 별도로 있고, 찾아보기에서 해당 단어를 찾은 후에 옆에 따로 펼쳐놓는 것 처럼 비슷합니다.

인덱스의 장점으로는 SELECT 문으로 검색하는 속도가 매우 빨라지고, 그 결과 컴퓨터의 부담이 줄어들어서 결국 전체 시스템의 성능이 향상됩니다.

단점으로는 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요로 하고, 처음에 인덱스를 만드는데 시간이 오래 걸릴 수 가 있습니다. 또한 CREATE, UPDATE, DELETE 등 속성에 인덱스를 자주 걸게되면 인덱스의 크기가 비대해져 성능이 오히려 저하되는 역효과가 발생합니다. 이유는 어떤 테이블에 UPDATE와 DELETE등을 자주 발생한다면 실제 데이터보다 훨씬 많은 인덱스가 존재하게 되어, SQL문 처리시 비대해진 인덱스에 의해 오히려 성능이 떨어집니다. 

 

클러스터형 인덱스를 생성하기 위해서는 기본키인 PRIMARY KEY로 설정하면 데이터가 자동 정렬되면서 균형 트리 형태의 인덱스가 형성 됩니다. 각 테이블마다 기본키는 하나만 설정이 가능하므로, 하나에 대한 정렬만 가능한게 특징입니다.

보조형 인덱스를 생성하기 위해서는 UNIQUE KEY를 활용합니다. 

직접 인덱스도 생성이 가능한데, 기본 형식은 다음과 같습니다.

CREATE [UNIQUE] INDEX 인덱스_이름
 ON 테이블_이름(열_이름) [ASC OR DESC]

 

2.인덱스 내부작동 및 자료 구조

인덱스의 내부 작동원리를 이해하면 인덱스를 사용해야할 경우를 선택할때 도움이 됩니다. 인덱스가 늘 좋은 것이 아니므로 정확히 판단하는 것이 중요합니다.

인덱스를 구현하기 위해서는 다양한 자료 구조를 사용할 수 있는데, 대표적인 해시 테이블과 B-Tree가 있습니다.

1)해시 테이블(Hash Table)

 

해시 테이블은 key와 value로 데이터를 저장하는 자료구조로 빠른 데이터 검색이 필요할 떄 유용하다.해시 테이블은 key값을 이용해 고우한 index를 생성하여 그 index에 저장된 값을 꺼내오는 구조입니다.해시 테이블 기반 DB 인덱스는 key와 value를 사용하여 칼럼의 값으로 생성된 해시를 통해 인덱스를 구현했습니다.

 

하지만 DB 인덱스에서 해시 테이블이 사용되는 경우는 제한적인데, 그러한 이유는 등호 연산에만 특화되었기 때문입니다.부등호 연산이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않습니다. 따라서 주로 B-tree가 일반적으로 사용됩니다.

 

 

2)B-Tree

 

리프노드와 루트노드에 대한 얘기는 전에 자료구조 공부할떄 많이 들었습니다. sql에서는 노드대신에 페이지라는 개념을 사용합니다.

리프 페이지만 있는 경우, 찾고 싶은 정보를 처음부터 끝까지, 전체 테이블 검색 방식으로 일일이 확인해보는 수 밖에 없습니다. 하지만 균형 트리라면 무조건 루트 페이지부터 검색을 합니다. 루트에서 몇번쨰 리프 페이지에 해당 정보가 있을지 검색하고 바로 해당 리프 페이지로 이동하여 찾기 시작합니다. 데이터가 크면 클수록 유리해질 수 밖에 없는 방식입니다. 

---

스토어드 프로시저

 

1.스토어드 프로시저 기본

 프로시저는 여러 쿼리문을 한번에 실행하는 방법입니다. SQL문법의 함수와 아주 유사한 느낌입니다.  하지만 둘은 좀 차이가 있는데 아래와 같이 정리 됩니다. 속도면에서는 프로시저가 더 빠른 성능을 보입니다. 하지만 프로시저는 처리 성능과 재사용면에서 좋지 않습니다. 그럼에도 프로시저를 사용해야 하는 이유가 여러가지 존재합니다. 하나의 요청으로 여러 SQL문 실행가능하면서 네트워크 소요시간을 줄일 수 있고, 보수성이 뛰어납니다.

함수: 클라이언트에서 처리, 리턴값 필수, 리턴값 하나만 반환 가능
프로시저:서버로 보내서 처리, 리턴값 선택, 리턴값 여러개 반환 가능

 

DELIMITER $$
CREATE PROCEDURE 프로시저이름(매개변수)
BEGIN

  SQL코드 작성
  
END $$
DELIMITER ;

 

2.스토어드 함수와 커서

스토어드 함수는 MYSQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공합니다. 스토어도 함수는 스토어드 프로시저와 모양이 비슷하지만, 세부적으로는 다릅니다. 특히 용도가 다르며,  RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 갖습니다. 

DELIMITER $$
CREATE FUNCTION 스토어드함수이름(매개변수)
	RETURNS 반환형식
BEGIN

	코딩영역
    RETURNS 반환값;
    
END $$

DELIMITER ;
SELECT 스토어드함수이름();

 

커서는 스토어드 프로시저 안에서 한행씩 처리할 떄 사용하는 프로그래밍 방식입니다.

 

커서문은 행 단위로 작업을 하는 방식으로 진행 되기 때문에 데이터가 많아질수록 속도가 느려지는 것을 피하지 못합니다. 그렇기에 커서문은 최대한 지양하면서 특수하게 행 별로 작업이 필요할때만 사용해야합니다.

---

트리거

트리거는 DML문이 작동할 때 자동으로 실행되는 프로그래밍 기능입니다. 트리거는자동으로 수행하여 사용자가 추가작업을 잊어버리는 실수를 방지해줍니다. 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있습니다. 이것을 데이터의 무결성이라고 부르기도 합니다. 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW테이블에 잠깐 저장됩니다. OLD 및 NEW 테이블은 MySQL이 내부적으로 관리합니다.

 

트리거 작용하는 예

어떤 쇼핑몰에는 하루에 수만 건의 주문이 들어옵니다. 주문데이터는 주문일자, 수량, 가격이 있으며 수천명의 임직원이 실시간으로 온라인 상에서 조회할때,  매 사람이 조회할때마다 데이터를 읽고 계산해야합니다. 만약 임직원이 수만명이고, 데이터가 수백만건이라면, 또 거의 동시다발적으로 실시간 조회가 요청되면 시스템 퍼포먼스가 떨어집니다. 따라서 트리거를 사용하여 자동으로 업데이트하게 할 수 있습니다.