이것이MySQL이다 - 02) 테이블과 뷰

테이블

 

 

테이블 압축

압축 기능은 대용량 테이블의 공간 절약하는 효과

MySQL 5.0부터 자체적으로 테이블 압축 기능 제공

MySQL 8.0에서 내부적인 기능이 더욱 강화

MySQL이 허용하는 최대 용량의 데이터도 오류 없이 압축 가능

 

 

테이블 압축

예제 실습(P. 337~338)

• 테스트용 DB 생성 – 동일한 열을 지닌 간단한 두 테이블 생성
  • 하나는 열 뒤에  ROW_FORMAT=COMPRESSED문을 붙여서 압축되도록 설정

 

두 테이블에 데이터 30만 건 입력

 

쿼리 실행 결과

 

 

두 테이블 상태 확인

SHOW TABLE STATUS FROM compressDB;

실습한 DB 제거

DROP DATABASE IF EXISTS compressDB;

 

 

임시 테이블

임시로 잠깐 사용되는 테이블

 

 

세션(Session) 내에서만 존재

• 세션이 닫히면 자동 삭제

생성한 클라이언트에서만 접근 가능

• 다른 클라이언트에는 접근 불가

임시 테이블 삭제 시점

• 사용자가 DROP TABLE로 직접 삭제
• Workbench를 종료하거나 mysql 클라이언트를 종료하면 삭제됨
• MySQL 서비스가 재시작되면 삭제됨

 

예제 실습 (P. 340 ~ 341)

• Workbench 실행 – [Local instance MySQL] 접속  
• 왼쪽 상단 [Home] 탭 클릭 - [Local instance MySQL] 접속

 

임시 테이블 2개 생성

• 두 번째는 기존의 employees 테이블과 동일한 이름으로 생성

 

• 데이터 입력하고 확인

 

(Workbench 2) Workbench 1에서 생성한 테이블에 접근

tempTBL은 아예 그런 테이블이 없다는 오류 메시지 나옴,

세션이 다르면 임시 테이블에 접근할 수 없음

employees 테이블은 기존의 employees 테이블이 접근해옴

임시 테이블 employees 접근할 수 없음

 

 

(Workbench 1) 임시 테이블 삭제

• DROP TABLE temptbl;

Workbench를 종료 후 다시 접속

다음 쿼리로 확인 시 임시 테이블이 아닌 기존의 테이블 조회 됨

• USE employees;
• SELECT * FROM employees;

 

 

 

테이블 삭제

 

외래 키 제약 조건의 기준 테이블은 삭제할 수가 없음

• 먼저 외래 키가 생성된 외래 키 테이블을 삭제해야 함
  • 구매 테이블이 존재하는데 회원 테이블을 삭제 할 수 없음, 구매 테이블 삭제가 선행 되어야 함

동시에 여러 테이블 삭제도 가능

• DROP TABLE 테이블1, 테이블2, 테이블3;

 

 

테이블 수정

ALTER TABLE문 사용

• 테이블에 무엇인가 추가/변경/수정/삭제 모두 ALTER TABLE문 사용

열의 추가

• 기본적으로 가장 뒤에 추가
• 순서를 지정하려면 제일 뒤에 ‘FIRST’ 또는 ‘ALTER 열 이름’ 지정
• ex) 회원 테이블(usertbl)에 회원 홈페이지 주소 추가

 

 

 

열의 삭제

 

• 제약 조건이 걸린 열을 삭제할 경우 제약 조건을 먼저 삭제한 후에 열을 삭제해야 함

열의 이름 및 데이터 형식 변경

• ex) 회원 이름(name)의 열 이름을 uName으로 변경하고 데이터 형식을 VARCHAR(20)으로 변경, NULL 값도 허용하는 경우

 

 

열의 제약 조건 추가 및 삭제

• ex) 기본 키를 삭제 하는 경우

 

 

• 오류가 발생
  • usertbl의 기본 키인 userID열은 buytbl에 외래 키로 연결되어 있기 때문에 외래 키를 제거한 후에 다시 기본 키를 제거해야 함

 

 

 

뷰

 

뷰의 개념

일반 사용자 입장에서 테이블과 동일하게 사용하는 개체

• 뷰를 생성한 후에는 테이블처럼 접근 가능하여 동일한 결과 얻을수 있음

 

뷰의 작동 방식

 

뷰 생성 구문

 

 

 

뷰의 장점

보안에 도움

• 사용자가 중요한 정보에 바로 접근하지 못함

복잡한 쿼리 단순화

• 긴 쿼리를 뷰로 작성, 뷰를 테이블처럼 사용 가능

 

 

 

 

 

 

테이블스페이스

 

테이블스페이스의 개념

물리적인 공간을 뜻함

데이터베이스는 논리적 공간

테이블스페이스를 지정하지 않은 경우

• 시스템 테이블스페이스(System Tablespace)에 테이블 저장됨

시스템 변수  innodb_data_file_path에 관련 내용 저장됨

 

 

시스템 테이블 스페이스 파일 확인

• MySQL 8.0에서 테이블스 페이스 파일은 기본적으로 ‘C:\Programdata\MySQL\MySQL Server 8.0\Data’ 폴더에 저장 되어 있음

 

 

 

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

소용량의 데이트를 사용하는 경우에는 테이블스페이스 고려하지 않아도 되나 대용량의 데이터를 운영할 경우에는 성능 향상을 위해 테이블스페이스의 분리를 적극 고려

 

 

 

 

테이블스페이스 실습 (P.366 ~ 369)

• 각 테이블이 별도의 테이블스페이스에 저장되도록 시스템 변수 innodb_file_per_table이 ON으로 설정 되야함
• 확인 방법
  • SHOW VARIABLES LIKE 'innodb_file_per_table;

 

• 테이블 스페이스 3개 생성
  • 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';

 

• 파일 탐색기에서 ‘C:\Programdata\MySQL\MySQL Server 8.0\Data’ 폴더 확인

 

 

 

• 각 테이블스페이스에 파일 생성
  • USE sqldb;
  • CREATE TABLE table_a (id INT) TABLESPACE ts_a;
• 테이블을 만든 후에 ALTER TABLE문으로 테이블스페이스 변경 가능
  • CREATE TABLE table_b (id INT)
  • ALTER TABLE table_b TABLESPACE ts_b;

 

 

• 쿼리 응답 시간 제한 없애기
  • Workbench 메뉴의 [Edit] >> [Preferences]를 선택 - 왼쪽에서 [SQL Editor]를 선택
  • [MySQL Session] 부분의 ‘DBMS connection read timeout interval’와
  • ‘DBMS connection timeout interval’ 두 개를 0으로 설정 - <OK> 클릭

 

 

 

• 대용량의 테이블을 복사한 후 테이블 스페이스 지정
  • CREATE TABLE table_c (SELECT * FROM employees.salaries); ALTER TABLE table_c TABLESPACE ts_c;