<!doctype html>
25) 데이터베이스 객체
데이터베이스 객체란? 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의해서 사용하는 실체를 가진 것을 말한다. 데이터베이스 객체는 스키마 내부에 만들어진다. 스키마를 정의하고, 그 안에 테이블 등 객체들을 담는다. MySQL에서 스키마를 생성하는 명령어는 다음과 같다.
CREATE DATABASE [스키마 명];이렇게 생성한 스키마에 테이블이나 뷰 등 여러 객체를 생성할 수 있다. 스키마가 다르면 내부의 객체 이름이 같아도 상관없다.
26) 테이블 작성·삭제·변경
SELECT, INSERT, DELETE, UPDATE 같은 SQL은 데이터를 조작하는 DML로 분류된다. DDL은 데이터를 정의한는 명령으로, 스키마 내의 객체를 생성 및 관리할 떄 사용한다.
xxxxxxxxxxCREATE TABLE 테이블명 (열 정의1, 열 정의2, ...);DROP TABLE 테이블명ALTER TABLE 테이블명 하부명령
테이블 작성
테이블 생성은 다음과 같이 CREATE TABLE명령어를 사용한다.
CREATE TABLE 테이블명 ( 열이름1 데이터타입(자리수). 열이름2 데이터타입(자리수). ...)열 정의에도 형식이 있다.
MySQL 데이터 타입
CHAR 데이터 타입
CHAR와 VARCHAR(VARiable length CHARacter string)은 모두 텍스트 문자열을 허용하고, 필드의 크기를 제한한다. 두 타입의 차이점은 CHAR 필드의 모든 문자열은 크기가 정해진다는 것. 즉 더 작은 문자열을 입력하면 공백으로 채워진다. 반면 VARCHAR의 경우, 텍스트를 채우지 않으며, 입력한 텍스트 크기에 맞게 가변적으로 크기를 가진다. 그러나, VARCHAR는 각 값의 크기를 추적할 수 있는 약간의 오버헤드가 필요하기 때문에 모든 데이터의 크기가 비슷하다면, CHAR가 더 효율적이다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| CHAR(n) | 정확히 n (<=255) | CHAR(5) 'Hello'는 5바이트 사용CHAR(50) 'Hello'는 50바이트 사용 |
| VARCHAR(n) | 최대 n 까지(<=65535) | VARCHAR(100)'Hello'는 5바이트 사용 VARCHAR(5) 'Hello'는 5바이트 사용 |
BINARY 데이터 타입
BINARY 데이터 타입은 관련된 문자 세트가 없는 문자의 전체 바이트를 저장하는데 사용된다. 예를 들면 GIF 이미지를 저장하는데 사용할 수 있다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| BINARY(n) 혹은 BYTE(n) | 정확히 n (<=255) | CHAR이지만 바이너리 데이터를 가짐 |
| VARBINARY(n) | 최대 n 까지(<=65535) | VARCHAR이지만 바이너리 데이터를 가짐 |
TEXT와 VARCHAR 데이터 타입
TEXT와 VARCHAR에는 작은 차이점이 있다.
TEXT 필드는 기본 값을 가질 수 없다. MySQL은 TEXT 열의 처음 n 개의 문자만 인덱싱 할 수 있다. 이것은, 만약 전체 내용을 검색할 때는 VARCHAR이 더 알맞고, 빠르다는 것이다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| TINYTEXT(n) | 최대 n (<=255) | 문자열로 취급 |
| TEXT(n) | 최대 n (<=65535) | 문자열로 취급 |
| MEDIUMTEXT(n) | 최대 n (<=16777215) | 문자열로 취급 |
| LONGTEXT(n) | 최대 n (<=4294967295) | 문자열로 취급 |
BLOB 데이터 타입
BLOB(Binary Large OBject)는 65535 바이트를 넘는 바이너리 데이터에 유용하며, 기본 값을 가질 수 없다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| TINYBLOB(n) | 최대 n (<=255) | 바이너리 데이터로 취급 |
| BLOB(n) | 최대 n (<=65535) | 바이너리 데이터로 취급 |
| MEDIUMBLOB(n) | 최대 n (<=16777215) | 바이너리 데이터로 취급 |
| LONGBLOB(n) | 최대 n (<=4294967295) | 바이너리 데이터로 취급 |
숫자형 데이터 타입
| TYPE | 사용되는 바이트 | 최소 값(signed/unsigned) | 최대 값(signed/unsigned) |
|---|---|---|---|
| TINYINT | 1 | -128 0 |
127 255 |
| SMALLINT | 2 | -32768 0 |
32767 65535 |
| MEDIUMINT | 3 | -8388608 0 |
8388607 16777215 |
| INT or INTEGER | 4 | -2147483648 0 |
2147483647 4294967295 |
| BIGINT | 8 | -9223372036854775808 0 |
9223372036854775807 18446744073709551615 |
| FLOAT | 4 | -3.40E+45 (no unsigned) |
3.40E+45 (no unsigned) |
| DUBLE or REAL | 8 | -1.7976E+320 (no unsigned) |
1.7976E+320 (no unsigned) |
참고로 INT[(n)]에서 (n)의 의미는 ZEROFILL을 위한 값이다.
xxxxxxxxxxid INT(5) ZEROFILL이런식으로 열을 생성 했을 때, 5자리에 맞게 0이 들어간다.
xxxxxxxxxx열명 자료형 [DEFAULT 값] [ NULL|NOT NULL]
테이블 삭제
필요없는 테이블을 삭제할 때 사용되는 명령이다.
xxxxxxxxxxDROP TABLE 테이블명데이터 행 삭제
DROP TALBLE 명령은 데이터베이스에서 테이블을 삭제하며 테이블에 저장된 데이터도 함께 삭제된다. 테이블 정의를 그대로 두고 데이터만 삭제하기 위해 DELETE 명령을 사용한다. 하지만 DELETE 명령은 행 단위로 여러가지 내부처리가 일어나므로 삭제할 행이 많으면 처리속도가 상당히 늦어진다. 이 경우 DDL 중 하나인 TRUNCATE TALBLE명령을 사용한다. TRUNCATE TABLE 명려은 삭제할 행을 지정할 수 없고WHERE 구를 지정할 수 없지만, 모든 행을 삭제해야 할 때 빠른 속도로 삭제할 수 있다.
xxxxxxxxxxTRUNCATE TABLE 테이블명
테이블 변경
테이블을 생성한 뒤에도 열 구성을 변경할 수 있다.
xxxxxxxxxxALTER TABLE 테이블명 변경명령테이블을 DROP TABLE로 삭제하고 새로 생성하는 것도 가능하지만 데이터가 이미 존재하는 경우에는 쓸 수 없는 방법이다. 테이블에 저장된 데이터를 그대로 가진채 테이블의 구조를 변경할 수 있다.
열 추가
xxxxxxxxxxALTER TABLE 테이블명 ADD 열 정의
27) 제약
제약조건(Constraint)이란, 데이터의 무결성을 지키기 위해 제한된 조건을 의미합니다. 즉, 데이터를 삽입할 때 무조건적으로 삽입되는 것이 아니라 어떠한 조건을 만족했을 경우에만 데이터가 삽입되도록 제약을 할 수 있는 것이라고 생각하시면 됩니다.
MySQL에서 사용할 수 있는 제약조건은 다음과 같다.
xxxxxxxxxxNOT NULLUNIQUEPRIMARY KEYFOREIGN KEYDEFAULTNOT NULL
해당 칼럼의 값에 NULL을 넣을 수 없다.
UNIQUE
칼럼의 값이 중복되지 않게 하는 제약이다. 이를 통해 테이블의 각 레코드는 유일한 레코드가 된다.
PRIMARY KEY
제약 조건으로 기본 키를 생각한다면, 고유 제약 조건에 NOT NULL 제약을 가한 것이 기본 키 제약 조건이라고 생각할 수 있다
FOREIGN KEY
다른 테이블의 칼럼을 참조하여 데이터 무결성을 유지한다.
DEFAULT
해당 열에 삽입되는 데이터의 기본 값을 설정한다.
28) 인덱스 구조
테이블에는 인덱스를작성할수 있다. 인덱스의 역할은 검색속도의 향상이다. 여기서 검색이란 SELECT명령에 WHERE 구로 조건을 지정해 데이터를 찾는 과정이다. 데이터베이스의 인덱스에는 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.
검색에 사용하는 알고리즘
- 풀 스캔 : 전체 순회하며 조회
- 이진 탐색 : 이미 정렬된 데이터를 잘 탐색
- 이진 트리
테이블 내의 행을 언제나 정렬된 상태로 두는 것은 힘들다. 일반적으로 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어 지는데 이 때 이진트리 구조로 작성된다. 이진트리에서는 집합 내 중복하는 값을 가질 수 없다. 그래서 기본키 제약을 통해 이진트리로 인덱스를 작성하는 데이터베이스가 많다.
29) 인덱스 작성과 삭제
xxxxxxxxxxCREATE INDEXDROP INDEX인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제한다. 표준 SQL에는 CREATE INDEX 명령은 없다. 인덱스 자체가 데이터베이스 제품에 의존한는 선택적인 항모기으로 취급된다. 하지만 대표적인 데이터베이스 제품에는 모두 인덱스 구조가 도입되어 있으며, 모두 비슷한 관리 방법으로 인덱스를 다룰 수 있다.
1. 인덱스 작성
xxxxxxxxxxCREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ... )ex)CREATE INDEX isample65 ON sample62(no);2. 인덱스 삭제
xxxxxxxxxxDROP INDEX 인덱스명ex)CREATE INDEX isample65 ON sample63(no);3. EXPLAIN
인덱스 작성을 통해 쿼리의 성능 향상을 기대할 수 있는데, 실제 인덱스를 사용해 검색하는지를 확인하기 위해 EXPLAIN 명령을 사용한다.
xxxxxxxxxxEXPLAIN SQL문4. 최적화
SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택할 수 있는데 이는 데이터베이스 내부의 최적화에 의해 처리되는 부분이다. 내부처리에서는 SELECT명령을 실행하기에 앞서 실행계획을 세운다. 실행계획에서는 인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으면 인덱스를 사용해 처리하도록 한다. EXPAIN명령은 이 실행계획을 확인하는 명령이다. 실행계획에서는 인덱스 유무 뿐만 아니라 인덱스 사용 여부도 내부 최적화 처리를 통해 판단한다. 판단할 떄 인덱스의 품질도 고려한다. 예를 들어 Y/N 값만 가지는 열일 경우 좋은 성능을 나타낼 수 없다.
EXPAIN
DBMS에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다. DBMS에서 옵티마이저가 이러한 기능을 담당한다.
실행 계획을 이해할 수 있어야 실행 계획의 불합리한 부분을 찾아내고, 더욱 최적화된 방법으로 실해 계획을 수립하도록 유도할 수 있다.
쿼리는 다음과 같은 절차로 실행된다.
- SQL 문장을 잘게 쪼개 MySQL서버가 이해할 수 있는 수준으로 분리 : 파싱
- SQL 파싱 정보(파스트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지엔진으로부터 데이터를 가져온다.
첫 번째에서 MySQL서버의 파서 모듈로 SQL을 파싱한다. 문법적 적합을 확인 후 파스트리를 생성한다. MySQL서버는 파스트리를 이용해 쿼리를 실행한다.
두 번째 단계에서는 생성된 파스트리를 참조하여 다음을 수행한다.
- 불필요한 조건의 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
두 번째 단계는 최적화 및 실행 게획 수립 단계이며 MySQL 서버의 옵티마이저가 처리한다. 이 과정이 완료되면 실행 계획이 만들어진다.
세 번째 단계에서 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
첫 번쨰 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.
실행 계획을 확인하기 위해 EXPLAIN명령을 사용한다.
cluster index VS non-cluster index
클러스터 인덱스는 데이터를 물리적으로 정렬되게 저장한다. 인덱스 자체의 리프 페이지가 곧 데이터 페이지이다. 즉, 인덱스 자체에 데이터가 포함되어 있다. 비클러스트형보다 검색속도는 빠르지만 데이터의 입력/수정/삭제는 느리다. 또한 테이블에 한개만 생성할 수 있다. 비클러스터인덱스는 별도의 공간에 인덱스 테이블을 생성하여 정렬된 데이터를 가진다. 물리적으로 저장된 데이터가 정렬된것을 보장하지 않기 때문에 정렬 순서를 인덱스 테이블에 유지한다.
| 구분 | 클러스터드 인덱스 | 넌클러스터드 인덱스 |
|---|---|---|
| 차이 | 물리적으로 행을 재배열 | 물리적으로 행을 재배열하지 않는다. |
| 크기 | 비교적 작다. | 클러스터드 인덱스보다 크다. |
| 선택도 | 30% 정도면 사용한다. | 3% 이내면 사용한다. |
| 최대 갯수 | 테이블당 1개 | 테이블당 249개 |
- 넌클러스트 인덱스는 데이터의 물리적 위치를 가르키는 포인터를 가지고 있기 때문에 크기가 상대적으로 크다.
- 선택도란 인덱스를 적용할 칼럼에 데이터가 다른 정도를 말한다.
실습
INDEX 생성 효과 확인
실습을 위해 다음과 같은 테이블을 생성했다.
xxxxxxxxxxCREATE TABLE IF NOT EXISTS health_data( id INT, gender INT, age_code TINYINT, area_code TINYINT, height SMALLINT, weight SMALLINT, waist SMALLINT, eye_left FLOAT, eye_right FLOAT, ear_left TINYINT, ear_right TINYINT);테이블에 100만개의 데이터를 삽입했다.
xxxxxxxxxxSELECT COUNT(*) FROM health_data;
먼저 INDEX를 생성하지 않고 SELECT문을 5번 실행해 시간을 측정했다.
xxxxxxxxxxSELECT *FROM health_dataWHERE id > 0;
전체 조회에는 인덱스 유무와 상관 없이 비슷하다.
다음은 id를 기준으로 인덱스를 생성했다.
xxxxxxxxxxCREATE INDEX hd_index ON health_data(id);
다음의 SELECT문을 10번씩 수행하여 시간을 측정해봤다.
xxxxxxxxxxSELECT *FROM health_dataWHERE id > 0;인덱스X
인덱스O
인덱스 유/무와 상관 없이 수행 시간에는 의미가 없었다.
이번엔 랜덤으로 100개의 id를 하나씩 검사해봤다
xxxxxxxxxxSELECT *FROM health_dataWHERE id = {0}//이런 select문 100개
인덱스 X
인덱스 O
100개를 모두 수행하는데 걸린 시간을 측정했다. 이번에는 의미있는 차이가 보였다. 처음 10000개로 테스트하다 인덱스 없이 수행하는데 너무 오래걸려 개수를 줄였다. 인덱스가 없을때는 각 SELECT문이 대부분 0.6sec 걸리다가 일부에서 10~30초가 걸렸다. 인덱스를 생성하면 worckbench에서는 0.000sec으로 표시되었다. 테스트 결과 칼럼에 인덱스를 지정할 경우 검색속도의 향상을 확인할 수 있었다.
30) 뷰 작성과 삭제
xxxxxxxxxxCREATE VIEW 뷰명 AS SELECT 명령DROP VIEW 뷰명1. 뷰
xxxxxxxxxxSELECT *FROM ( SELECT * FROM sample_table) AS sq //AS 생략 가능FROM이나 JOIN문 에는 서브쿼리를 사용할 수 있다.
xxxxxxxxxxCREATE VIEW sample_viewAS SELECT * FROM sample_tableSELECT *FROM sample_view뷰를 생성하여 사용하면 위와 같이 내용을 간략하게 표현할 수 있다. sql문에 WHERE이나 GROUP BY같은 조건이 길어져 복잡해질 경우 뷰를 통해 간략히 표현할 수 있다.
가상 테이블
뷰는 테이블처럼 취급할 수 있지만 실체가 존재하지 않는다는 의미로 가상테이블이라고 불리기도 한다. SELECT 명령으로 이루어지는 뷰는 테이블처럼 데이터를 쓰거나 지울 수 있는 저장 공간을 가지지 않는다. 따라서 테이블처럼 취급할 수 있어도 SELECT 명령에만 사용하는 것을 권장한다. INSERT, UPDATE, DELETE명령에도 조건이 맞으면 사용 가능하지만 사용에 주의할 필요가 있다.
xxxxxxxxxx//SELECT 명령으로 이루어지기 때문에 수행시간이 짧았다.(인덱스 생성은 오래걸렸다)REATE VIEW sample_viewAS SELECT * FROM health_data;SELECT *FROM sample_viewWHERE id = 1;//단순 테이블이라 DELETE도 적용이 됬다.DELETE FROM sample_viewWHERE id = 1;//뷰에 삽입을 해도 원 테이블에 새로 추가가 되었다.INSERT INTO sample_view(id, gender, age_code, area_code, height, weight, waist, eye_left, eye_right, ear_left, ear_right) VALUES(1, 1, 10, 27, 175, 75, 86, 1, 1.2,1, 1 );2. 뷰 작성과 삭제
뷰 생성
xxxxxxxxxxCREATE VIEW 뷰명 AS SELECT문xxxxxxxxxxCREATE VIEW 뷰명(열명1, 열명2, ...) AS SELECT문위와 같이 뷰에서 사용할 열을 지정할 수 있다.
뷰 삭제
xxxxxxxxxxDROP VIEW 뷰명3. 뷰의 약점
뷰는 데이터벵스 객체로서 저장장치에 저장된다. 하지만 테이블과 달리 대량의 저장공간을 필요로 하지 않는다. 데이터베이스에 저장되는 것은 SELECT명령뿐이다. 저장공간을 소비하지 않는 대신 CPU 자원을 사용한다. SELECT 문은 데이터베이스의 테이블에서 행을 검색/정렬/집계하는 명령이다. 이러한 처리에는 CPU 연산이 필요하다. 뷰를 참조하면 뷰에 등록되어 있는 SELECT문이 실행된다. 실행결과는 일시적으로 보존되고, 뷰를 참조할때마다 SELECT명령이 실행된다.
Materialized View
일반적으로 뷰는 데이터를 일시적으로 저장했다가 쿼리가 실행 종료될 떄 함께 삭제된다. 그에 비해 머티리얼라이즈드 뷰는 데이터를 저장장치에 저장해 두고 사용한다. 머티리얼라이즈드 뷰는 처음 참조되었을 때 데이터를저장해 둔다. 이후 다시 참조할 때 이전에 저장해 두었던 데이터를 그대로 사용한다. 만일 뷰에 지정된 테이블의 데이터가 변경되면 SELECT문을 재실행하여 데이터를 다시 갱신한다. 이처럼 변경 유무를 확인하여 재실행하는 것은 RDBMS가 자동으로 실행한다. MySQL에서는 머티리얼라이즈드 뷰를 제공하지 않는다. 다만 TRIGGER나 프로시저를 이용해 비슷한 테이블을 만들어 구현할 수 있다.
'DB > MySQL' 카테고리의 다른 글
| [MySQL]8. 데이터베이스 설계 (0) | 2019.05.17 |
|---|---|
| [MySQL] 7. 복수의 테이블 다루기 (0) | 2019.05.15 |
| [MySQL] 5. 집계와 서브쿼리 (0) | 2019.05.04 |
| [MySQL] 4. 데이터의 추가, 삭제, 갱신 (0) | 2019.05.03 |
| [MySQL]3. 정렬과 연산 (0) | 2019.04.19 |
