[DB] View & Partitioning

이번 스터디를 준비하면서 느낀점은 용량산정, 뷰나 파티셔닝 등의 명령어(쿼리)의 경우 같은 rdbms더라도 종류 , 버전에 따라 차이가 크게 나기 때문에 실습환경이나 업무환경에 맞춰 레퍼런스를 참고하여 사용해야함

• 뷰 사용

**CREATE VIEW view_employees AS SELECT first_name,last_name,gender FROM employees;**

스키마 수정 가능 ( 데이터 수정은 불가능 )

but 집계함수( count , 평균( avg ), 총합( sum ), 최대값( max ), 최소값( min ) )가 들어간 뷰는 수정 불가능

alter view view_employees as select first_name from employees;

데이터 insert하는 것 가능. 테이블의 모든 필드의 값을 넣어줘야하는데 일부만 넣어주게 되면 불가능. 그래서 넣으려면 전체를 다 넣거나 디폴트를 지정해줍니다.

but inner join

DROP VIEW view_employees;

CREATE VIEW view2 AS
SELECT emp_name, emp_dept, emp_salary
FROM employees
WHERE emp_salary >= 1000000;

• MSSQL 뷰 옵션

  • WITH CHECK 옵션은 뷰를 통해 삽입, 업데이트, 삭제할 수 있는 데이터를 제한 가능

    CREATE VIEW emp_view WITH CHECK AS
    SELECT emp_id, emp_name, emp_dept, emp_salary
    FROM employees
    WHERE emp_dept = 'Sales';

  • WITH READ ONLY 옵션은 뷰를 통해 데이터를 읽을 수만 있고 삽입, 업데이트, 삭제할 수 없도록 하는 데 사용

    CREATE VIEW emp_view WITH READ ONLY AS
    SELECT emp_id, emp_name, emp_dept, emp_salary
    FROM employees;

  • WITH SCHEMABINDING 옵션은 뷰를 테이블과 연결하여 뷰를 변경할 수 없도록 하는 데 사용

    CREATE VIEW emp_view WITH SCHEMABINDING AS
    SELECT emp_id, emp_name, emp_dept, emp_salary
    FROM employees;

• 파티셔닝

range

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
    PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
    );

INSERT INTO tr VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'alarm clock', '1997-11-05'),
    (3, 'chair', '2009-03-10'),
    (4, 'bookcase', '1989-01-10'),
    (5, 'exercise bike', '2014-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'espresso maker', '2011-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '2006-09-16'),
    (10, 'lava lamp', '1998-12-25');

ALTER TABLE tr DROP PARTITION p2;

list

hash

Hash Partition 은 해시함수에 의해 자동으로 파티션 갯수만큼 데이터가 분할되는 파티션 테이블 입니다.
해시 파티션키로 사용할 수 있는 컬럼은 아무 타입이나 가능합니다. 숫자, 문자, 날짜 타입 모두 다 가능합니다.

Range 나 List 파티션과 달리 Hash 파티션의 경우에는 내 데이터가 어느 파티션으로 들어갈 지 알 수 없기 때문에, 전혀 관리 목적에는 맞지 않습니다.
Hash 파티션을 사용하는 이유는 데이터를 여러 위치에 분산배치해서 Disk I/O 성능을 개선하기 위함입니다.

실제 값을 넣고 확인해본 파티션의 데이터

이런식으로 테이블 생성시에 파티션의 네이밍을 정해주지 않았다면 임의의 이름으로 생성됩니다.

생성된 파티션의 이름을 확인해봤습니다.

추가적으로 용량산정할때 사용하는 명령어 몇개 알아보겠습니다.

용량 산정

DB 사용량 확인

• sql 쿼리

SELECT table_schema "Database",
ROUND(SUM(data_length+index_length)/1024/1024,1) "MB"
FROM information_schema.TABLES GROUP BY 1;

• linux cmd

du -h /var/lib/mysql

• 특정 테이블의 사용량 확인
  

SELECT count(**) ,
  concat(round(sum(table_rows)/1000000,2),'M') row_cnt,
  concat(round(sum(data_length)/(1024**1024**1024),2),'G') data,
  concat(round(sum(index_length)/(1024**1024**1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024**1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES;

db mysqldump로 백업할때 불필요하게 쌓인 로그정보같은것들의 용량을 확인하고 제외한 상태로 백업할 수도 있음