파트 6-3. 뷰와 테이블스페이스

1. 뷰


뷰는 일반 사용자 입장에서는 테이블과 동일하게 사용하는 개체다. 뷰는 한 번 생성해 놓으면 테이블이라고 생각해도 될 정도로 사용자 입장에서는 테이블과 거의 동일한 개체로 여겨진다. select문으로 출력된 결과가 결국 테이블의 모양을 가지고 있으므로 이 결과를 그대로 테이블로 다시 사용하겠다는 것이 뷰의 개념이다. 뷰를 select 조회시 열 이름은 백틱(`) 을 이용해야한다.
복합뷰(join 으로 만든 )는 update, insert가 불가능하다.

-- 형식
create view 뷰이름
as 
    select ...;

-- 예시
create view v_usertbl
as 
    select userid,name,addr from usertbl where addr in ('서울','경남');
    with check option -- 나중에 insert문 사용시 where절 조건에서 거르라는 뜻
    -- 안 적으면 걸러지지 않고 들어오는데 조회는 안된다.


select * from v_usertbl; -- 뷰를 테이블이라고 생각해도 무방 


뷰의 장점

  • 보안에 도움이 된다.
  • 복잡한 쿼리를 단순화 시켜 줄 수 있다.
    • 예를 들어, 복잡한 select문의 결과를 계속 사용해야 한다면 뷰로 만들어 두고 뷰에 접근하면 된다.


뷰 실습

USE sqlDB;
-- 뷰 생성
CREATE VIEW v_userbuytbl
AS
   SELECT U.userid AS 'USER ID', U.name AS 'USER NAME', B.prodName AS 'PRODUCT NAME', 
		U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
      FROM usertbl U
	INNER JOIN buytbl B
	 ON U.userid = B.userid;

SELECT `USER ID`, `USER NAME` FROM v_userbuytbl; -- 주의! 백틱을 사용한다.
-- 뷰 삭제
DROP VIEW v_userbuytbl;

-- 뷰를 만들거나 있으면 덮어씌우기
CREATE OR REPLACE VIEW v_usertbl
AS
	SELECT userid, name, addr FROM usertbl;

-- 뷰의 정보 확인
DESCRIBE v_usertbl;

-- 뷰의 소스코드 내용 확인 -> 결과 전체가 안보이면 오른쪽 [form editor]클릭
SHOW CREATE VIEW v_usertbl;

-- 뷰 수정
UPDATE v_usertbl SET addr = '부산' WHERE userid='JKW' ;

-- 오류 발생
-- 뷰는 usertbl에서 가져왔는데 지금 삽입하는 userid,name,addr
-- 이외의 열에 대해서 usertbl에 not null인 열이 있다면 뷰에서 삽입이 불가능
-- 삽입하려면 usertbl의 not null을 null로 바꿔줘야 하는데
-- 뷰를 위해 기존 테이블을 수정하는 것은 바람직하지 않음 
INSERT INTO v_usertbl(userid, name, addr) VALUES('KBM','김병만','충북') ;

-- 뷰 생성
-- 집계함수와 group by를 사용한 뷰는 업데이트 불가능
CREATE VIEW v_sum
AS
	SELECT userid AS 'userid', SUM(price*amount) AS 'total'  
	   FROM buytbl GROUP BY userid;

SELECT * FROM v_sum;

-- 특정 데이터베이스의 특정 뷰를 확인할 수 있다.
-- 결과창에서 is_updateable이 no면 업데이트가 불가능하다는 뜻
SELECT * FROM INFORMATION_SCHEMA.VIEWS
     WHERE TABLE_SCHEMA = 'sqldb' AND TABLE_NAME = 'v_sum';

-- 뷰 생성
CREATE VIEW v_height177
AS
	SELECT * FROM usertbl WHERE height >= 177 ;

SELECT * FROM v_height177 ;

-- 기존 테이블에 해당 행이 있어도, 뷰에서는 없으므로 삭제될 행이 없음
DELETE FROM v_height177 WHERE height < 177 ;

-- 삽입은 오류없이 되나 조회시 보이지 않음 -> 뷰에 조건에 안맞기 때문
INSERT INTO v_height177 VALUES('KBM', '김병만', 1977 , '경기', '010', '5555555', 158, '2023-01-01') ;

-- 위와 같은 경우를 막기 위해 입력시 조건을 체크해주는 옵션사용
ALTER VIEW v_height177
AS
	SELECT * FROM usertbl WHERE height >= 177
	    WITH CHECK OPTION ;

-- 체크옵션에 걸려서 삽입 오류
INSERT INTO v_height177 VALUES('WDT', '서장훈', 2006 , '서울', '010', '3333333', 155, '2023-3-3') ;

-- 복합 뷰(두 개 이상의 테이블)
CREATE VIEW v_userbuytbl
AS
  SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS mobile
   FROM usertbl U
      INNER JOIN buytbl B
         ON U.userid = B.userid ;

-- 복합 뷰의 경우 insert, update가 불가능
INSERT INTO v_userbuytbl VALUES('PKL','박경리','운동화','경기','00000000000','2023-2-2');

-- 뷰가 참조하는 테이블 삭제 -> 삭제 가능
DROP TABLE IF EXISTS buytbl, usertbl;

-- 뷰 조회시 오류 발생 -> 참조하는 테이블이 삭제되었기 때문
SELECT * FROM v_userbuytbl;

-- msg_text에 references가 없다고 나온다.
CHECK TABLE v_userbuytbl;


2. 테이블스페이스


소용량의 데이터를 다룰 때는 테이블이 저장되는 물리적 공간인 테이블스페이스에 대해서는 신경쓰지 않아도 되지만, 대용량의 데이터를 다룰 때는 성능 향상을 위해서 테이블 스페이스에 대한 설정을 하는 것이 좋다. 데이터베이스가 테이블이 저장되는 논리적 공간이라면, 테이블스페이스는 테이블이 실제로 저장되는 물리적인 공간을 말한다.

성능 향상을 위한 테이블스페이스 추가

기본 설정은 모든 테이블이 시스템 테이블스페이스에 저장되지만, 대용량 테이블을 동시에 여러 개 사용하는 상황이라면 테이블마다 별도의 테이블스페이스에 저장하는 것이 성능에 효과적이다. 그림1

다음 상황을 가정하고 테이블스페이스를 분리해서 사용해보자.
먼저 각 에티블이 별도의 테이블스페이스에 저장되도록 시스템 변수 innodb_file_per_table이 on으로 설정되어 있어야 한다. show variables like ‘innodb_file_per_table’; 로 확인할 수 있다.

-- on인지 확인
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- 확장명은 반드시 ibd, 테이블스페이스 만들기
CREATE TABLESPACE ts_a ADD DATAFILE 'ts_a.ibd';
CREATE TABLESPACE ts_b ADD DATAFILE 'ts_b.ibd';
CREATE TABLESPACE ts_c ADD DATAFILE 'ts_c.ibd';

USE sqldb;
-- 테이블스페이스를 ts_a로 지정하고 테이블 생성
CREATE TABLE table_a (id INT) TABLESPACE ts_a;

-- 기본적인 default 테이블스페이스에 만들고 옮기기
CREATE TABLE table_b (id INT);
ALTER TABLE table_b TABLESPACE ts_b;



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony