파티셔닝/샤딩이 필요한 이유

1)인덱스를 아무리관리해도 검색속도 관리가 안된다

2)하나의 시스템에서 일정부분의 저장공간을 초과하는 경우(예를 들어 20테라를 초과)

---->주로 WRITE 성능을 높이기 위해 사용

VLDB(Very Large DBMS) = 데이터들을 여러개의 시스템에 저장하는 방식

전체DB가 하나의 DBMS시스템에 다 들어가기 힘들어지는 경우(수억개, 수십억개가 넘어가게 되면)

-테이블 들을 여러개의 군으로 나눠 분산저장

-하나의 테이블이 방대한 경우에는 사전방식(a-m, n-r, s-z)와 같이 나눠 저장

파티셔닝

:DBMS 레벨 분할

샤딩(Sharding)

:DBMS 외부에서 분할(시스템이 분할됨) / 응용레벨에서 구별해야 함

:별도의 시스템이 필요하다

파티셔닝/샤딩 제약사항

-테이블 단위 연산이 힘들어짐(비용문제)

조인연산 어려움 -> 정규화(normallization)문제

역정규화(denormalization) -> 중복허용으로 해결

-외래키의 효용문제

레코드 추가시 참조무결성 조건 체크 -> 시스템 부담증가로 수동전환

CRUD시 위치를 인식해야 함(어디 위치에 저장했는지 확인해야함)

파티셔닝/샤딩 이점

-데이터 전체 검색 시 필요한 부분만 탐색해 성능 증가

-전체 데이터를 손실할 가능성이 줄어듦 -> 가용성(availabiltity) 향상

-파티션별 백업/복구 가능

-파티션 단위로 I/O 분산가능 -> 업데이트 성능 증가

파티셔닝/샤딩 방식

-범위(range) a-m / n-r / s-z

-해시(hash) 해시함수 파티션별로 크기를 비슷하게 나눔

-리스트(list) 특정한 컬럼을 기준(연도별)

-컴포지트(composite) - range-hash / range-list

MYSQL 파티셔닝

-MYSQL 5.x에서 지원

-최대 파티션은 1024개

-모든 파티션은 동일 스토리지 엔진사용 해야함

-파티션은 외래키 / Full Text 인덱스 지원하지 않음

-파티션 값은 정수

예제)

BusinessCard 테이블에 연도 컬럼을 추가하면서 생성하면서 파티셔닝하시오

Create table BusinessCard (ID INT NOT NULL, Name varchar(255), Address varchar(255). Telephone varchar(255), CreationTime Date)

PARTITION BY RANGE (YEAR(CreationTime))

(

   PARTITION p0 VALUES LESS THAN (2015),

   PARTITION p1 VALUES LESS THAN (2016),

   PARTITION p2 VALUES LESS THAN (2017),

   PARTITION p3 VALUES LESS THAN MAXVALUE

);

예제)

파티셔닝을 새로 추가

ALTER TABLE BusinessCard ADD PARTITION (PARATITION p4 VALUES LESS THAN(2005));

예제)

파티셔닝을 삭제

ALTER TABLE BusinessCard DROP PARTITION p4;

예제)

파티션 분할

ALTER TABLE BusinessCard REORGANIZE PARTITION p3 INTO (

   PARTITION p3 VALUES LESS THAN (2015),

   PARTITION p4 VALUES LESS THAN MAXVALUE

);

예제)

파티션 병합

ALTER TABLE  BusinessCard REORGANIZE PARTITION p2, p3 INTO (

   PARTITION p23 VALUES LESS THAN (2014)

);

데이터베이스 복제(Replication)

-데이터베이스의 내용을 복제해 동일한 DB내용을 유지

-두개 이상의 DBMS시스템을 마스터/슬레이브로 나눠 마스터DBMS->슬레이브DBMS로 SQL 쿼리 복제(SELECT 제외)

데이터 업데이트(CUD)는 마스터에서

읽기(R)은 슬레이브에서

-웹서버 시스템 성능확장에 적합

-주로 읽기 성능을 높이기 위해 사용

복제 방식

-로그기반 복제(Binary Log)

  Statement Based - SQL문장복제, SQL에 따라 결과가 달라지는 경우(시간, UUID, ...)  / 단 시간이나 id가 다를 수 있는 문제발생소지 있음 / 복제할 데이터양이 작음

  Row Based - SQL에 따라 변경된 라인만 기록하는 방식 / 데이터가 많이 변경된 경우 데이터 커지는 문제발생소지 있음 / 정확한 데이터를 복제가능함

  Mixed - 두 방식 복합

Mysql 복제 쉽게 지원함

-두개의 mysql서버 필요

-물리적 두개의 서버 혹은 가상화 시스템에서도 가능

전문검색(Full Text Search)의 필요성

:기존의 Like 검색의 단점을 개선하기 위함(like 여러개의 조건문을 And/or로 like 검색을 할 경우 심각한 성능 저하됨을 개선하기 위한 검색)

:결과는 동일하지만 DB서버에 부담을 주지 않는 방식

전문검색(Full Text Search)

-기본적으로 컬럼 내용 전체를 단순 문자열(Plain Text)로 생각하고 검색하는 방식(스키마를 무시하고 검색)

-문서편집기의 편집 찾기/바꾸기 메뉴의 동작방식과 유사

Mysql의 Full Text Search방식

-자연어 검색

-불린 검색(조건절 검색)

-쿼리 확장 검색

Full Text Search용 인덱스 생성

ALTER TABLE 테이블명 ADD FULLTEXT(컬럼명);

Full Text Search 설정

my.ini(my.cnf)파일에서

ft_min_word_len= 2

2자 이상의 단어에 대해서 적용하도록 제한(기본값은 4)

기존 full text search가 있었으면 인덱스 재구성 해야함(업데이트 적용)

REPAIR TABLE 테이블명 QUICK;

예시)

자연어검색

where match(컬럼명) against('검색어/검색문장');

결과는 검색의 정확도에 대한 내림차순 정렬

예시)

불린검색

검색의 정확도에 따른 정렬이 안되고 연산자 사용한 구문 검색 가능

필수단어(+), 예외단어(-), 부분단어(*)

where match(컬럼명) against('단어*' -제외단어 in boolean mode);

Bulk Insert(여러개의 건을 insert할때)

INSERT의 경우 레코드를 추가한 후 내부적으로 인덱스 재구성을 실행함

그런데 여러개의 건을 Insert할때마다 인덱스 재구성하니 오버헤드 발생

각각의 insert마다 인덱스를 재구성하는 것이 아니라

마지막에 한번 인덱스 재구성하는 것을 Bulk Insert라고 함

인덱스 작업을 정지

ALTER TABLE 테이블명 DISABLE KEYS;

인덱스 작업 재설정

ALTER TABLE 테이블명 ENABLE KEYS;

Bulk Insert 방식 - 파일로 덤프

-SQL을 사용한 방식

 기존의 source 명령어와 유사(백업/복원 참조)

-CSV(Comma Separated Value)파일을 사용한 방식

 <CSV파일을 테이블로 입력>

 LOAD DATA INFILE 'C:\PATH\FILE'

 INTO TABLE 테이블명

 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY'\\' 

 LINES STARTING BY '' TERMINATED BY '\n'

 <테이블 내용 CSV로 출력>

 SELECT * FROM 테이블명 INTO

 OUTFILE 'c:\PATH\FILE'

LOAD DATA/SELECT INTO OUTFILE의 경우 보안 문제로 특정 디렉토리에만 가능하도록 설정해야함

secure-file-priv=path 옵션

my.ini(my.cnf)파일

[mysqld]

secure-file-priv=c:\mysql\data

트랜잭션

-복수의 SQL문을 수행하는 도중(은행간의 이체)에 장애가 발생했을 때 장애에 대응할수 있도록 하는 기능

-전체수행(Commit)과 전체취소(Rollback) 두가지의 결과값만 가져야 함

-기본적으로 SQL수행모두는 AutoCommit모드(줄단위 커밋모드)

-트랜잭션을 지원하기 위해서는 AutoCommit모드를 오프시켜야 함

-InnoDB 스토리지엔진만 가능함

트랜잭션의 특징(ACID)

-원자성(Atomicity)

  All or Nothing, Commit/Rollback

-일관성(Consistency)

  트랜잭션 전후에 데이터가 손상을 받으면 안됨

  같은 데이터가 다른 값을 가지면 안됨

-고립성/격리수준(Isolation)

  여러 개의 트랜잭션이 수행될 때 성능과 데이터 안정성간의 trade-off

-지속성(Durability)

  트랜잭션이 종료된 이후에도 데이터에 문제가 없어야 됨(장시간)

예시)

트랜잭션 기본 설정 확인

select @@autocomit;

->1일경우 true / 0일경우 false

예시)

AutoCommit 설정 끄기(줄단위 커밋모드 취소)

set autocommit = false;

예시)

Autocommit 설정 끈 이후 커밋/롤백 둘중에 하나 수행

create database;

create table;

insert;

이후에

commit; or rollback;

락(Lock)

공유자원(리소스)에 대해 여러개의 트랜잭션이 접근하려고 경쟁할때 제어하는 방법

-프로그래밍에서는 동기화(synchronization)이라고 함

-동시성제어(Concurrency Control)는 보통 락(lock)으로 해결

락의 종류

-테이블단위 락(Table Lock)

  동일한 테이블을 다른 트랜잭션이 사용하고 있다면 접근 금지 - MyISAM 스토리지 엔진

-줄단위 락(Row Lock)

  동일한 줄(Row)만 접근 금지(테이블 락에 비해 높은 성능) - InnoDB 스토리지 엔진

기본 스토리지엔진 확인

select engine, support from information_schema.engines where support='DEFAULT';

기본 스토리지 엔진 변경(리셋하면 초기화되니 my.ini나 my.cnf에서 변경해야 함)

set default_storage_engine=InnoDB;

set default_storage_engine=MyISAM;

예시)city 테이블 스토리지엔진 확인

select engine from information_schema.TABLES where table_name='city' and table_schmema = 'world';

city테이블 스토리지 엔진 변경하기(이것은 안하는게 좋음 -> 처음부터 만들고 나서 데이터를 넣는게 좋다)

alter table 'city' engine=InnoDB;

격리(isolation)

여러개의 트랜잭션이 수행될때 안정성과 성능이 trade-off관계가 있다

4가지 종류의 단계가 있음

-Read Uncommitted

트랜잭션이 처리되는 도중에 다른 트랜잭션이 해당 데이터를 읽기를 허용

성능은 높지만 데이터의 안정성이 떨어짐(데이터가 바뀔 수 있기 때문)

-Read committed

트랜잭션이 끝난 이후에만 접근하도록 허용(일반적으로 많이 사용-오라클에서 기본설정)

-Serializable

트랜잭션이 동시에 수행되는 것이 금지되고 순차적으로 수행됨(데이터 안정성이 극단적으로 좋아짐)

-Repeatable Read

다른 트랜잭션이 업데이트한느 것은 금지하지만 레코드 추가하는 것은 허용하는 방식(MYSQL에서 기본설정)

트랜잭션이 중간에 값이 변경되더라도 최초의 값을 유지시켜서 처리함

예를 들어 복제나 백업을 할때 최초의 값을 가지고 진행함 중간에 값이 변경되더라도 반영안됨

기본 트랜잭션 격리수준 확인

select @@tx_isolation;

기본 트랜잭션 격리수준 변경

set tx_isolation='READ-COMMITED';

commit;

저장프로시저(stored procedure)

SQL을 함수형태로 저장하고 사용하는 방법(STATIC SQL->속도가 좀 빠르다)

저장프로시저 정의

CREATE PROCEDURE 프로시저명(인자 인자형,....)

BEGIN

      ....SQL 문장들...

END

저장프로시저 호출

CALL 프로시저명;

저장 프로시저 삭제

DROP PROCEDURE 프로시저명;

저장함수(stored function)

SQL을 함수형태로 저장하고 사용하는 방법 + stored procedure에 return 값이 있는 형태

저장함수 정의

CREATE FUNCTION 함수명(인자 인자형,....) RETURNS 타입

BEGIN

      ....SQL 문장들...

END

저장프로시저 호출

함수명(인자);

저장 프로시저 삭제

DROP FUNCTION 함수명;

예시-정의)

DELIMITER //       DELIMITER를//로 변경

CREATE FUNCTION CountLines() RETURNS INTEGER

BEGIN

     DECLARE LINE_COUNT INTEGER;

     SELECT COUNT(*) INTO LINE_COUNT FROM BusinessCard;

     RETURN LINE_COUNT;

END

//

DELIMITER ;    DELIMITER를 ;로 변경

예시-실행)

SELECT countLines() from BusinessCard;

트리거

특수한 저장프로시저(stored procedure)라고 생각하면 됨

특정한 조건이 되면 자동으로 호출(콜백)되는 저장 프로시저

ex)레코드를 삭제하면 자동으로 참조무결성을 체크하는 트리거

트리거 정의

CREATE TRIGGER 트리거명 BEFORE(또는 AFTER) CRUD ON 테이블명 FOR EACH ROW

BEGIN

      변경전(OLD.컬럼명) 또는 변경후(NEW.컬럼명)을 이용한 처리

END

트리거 삭제

DROP PROCEDURE 트리거명;

예시)

BusinessCard 테이블에 레코드가 추가되면 BusinessCard2테이블에 레코드를 추가하는 트리거를 작성

DELIMITER //       DELIMITER를//로 변경

CREATE TRIGGER  InsertBusinessCard2 after insert on BusinessCard for each row

BEGIN

      insert into BusinessCard2 values('zzz', 'zzzzzz'. '000-0000');

END

//

DELIMITER ;    DELIMITER를 ;로 변경

대형 데이터베이스 구축기술

데이터는 계속 늘어남 -> 한 대의 DBMS서버로는 처리능력의 한계

성능 업그레이드(HDD->SSD-> 인메모리 머신)

스케일업

-보통 말하는 업그레이드를 말함

-CPU 클럭속도증가, 코어수증가, 메모리 증가

-보통 성능증가에 비해 가격증가가 더 빠름 -> 비용부담

-병렬컴퓨팅/전용 네트워크 -> 비용부담

-Tiglely-copled system

스케일아웃

-동일한 서버/DBMS를 병렬로 구축

-분산컴퓨팅 -> 비용이 매우 저렴

-loosely coupled system -> 상대적으로 저렴

-노드 수 추가하여 계속 성능 향상 가능 / 효율은 상대적으로 떨어짐

디비에서는 VLDB(Very Larege DBMS)로 성능 항샹 시킴

파티셔닝은 한대의 서버에서 나눔(DB자체적으로 나눔)

1)샤딩(Sharding) -> 여러대의 서버에 나눔

DBMS 내용 분할(DB/테이블)

쓰기성능 향상

2)복제(Replication)

동일한 DBMS를 여러 개 유지(마스터/슬레이브)

읽기성능 향상

NOSQL(Not Only SQL)

-일반 RDBMS가 주로 읽기/검색성능에 최적화

-쓰기 성능이 중요한 경우 좋은 성능을 보이는 경우가 많음

-로그 머신, SNS메신저(밴드, 카카오톡, ...등)

-기존 RDBMS를 완전히 대체하는 것이라기 보다는 보완재의 역할

-특정 기술을 말하는 것이 아니라 일련의 제품군(10가지)을 가리킴

-제품군

MongoDB가 시초

Apache Cassandra(페이스북에 적용), Apache HBASE, Redis ...

CAP 이론을 이용한 NoSQL 시스템 분류

1)C:일관성:Consistency

어떤 노드를 접근하더라도 데이터 값이 동일해야 한다

2)A:가용성:Availability

노드 일부가 Fail되더라도 서비스가 중단이 안 되어야 한다

3)P:파티션 내성:Partition tolerance

노드간 통신에 장애가 생겨도 문제가 없어야 한다

어떤 시스템도 3가지 모두를 만족시킬 수 없다(CA, AP, CP)(2가지만 가능)

CA(일반 RDBMS)

CP(Hbase, MongoDB, Redis)

AP(Cassandra)

저장형태에 따른 NoSQL 시스템 분류

1)키/밸류

키값을 주면 밸류를 리턴하는 형태

JSON과 유사한 형태

Redis

2)정렬된 키/밸류

키값으로 정렬되는 형태

Hadoop, Cassandra, HBase

3)도큐먼트 형태

밸류값이 JSON/XML 문서(파일)인 형태

MongoDB

NoSQL의 특징

-스키마리스

스키마 DDL 기반이 아니라 필요하면 새로운 컬럼(키)를 추가하면 된다

전체적으로 동일한 구조가 아닐 수 있음

데이터 구성방법에 따라 나뉨

RDBMS-정규형 데이터

NoSQL-반정규형 데이터

검색엔진-비정규형 데이터

스키마수정

ALTER TABLE ADD COLUMN 컬럼명 컬럼타입

ALTER TABLE DROP COLUMN 컬럼명

ALTER TABLE CHANGE 원컬럼명 새로운컬럼명 컬럼타입

ALTER TABLE MODIFY 컬럼명 컬럼타입

ALTER TABLE ADD PRIMARY KEY (컬렴명)

ALTER TABLE DROP PRIMARY KEY 컬럼명

ALTER TABLE ADD UNIQUE (컬럼명)

ALTER TABLE ADD CONSTRAINT 제약명 UNIQUE (컬럼명1, 컬럴럼명2)

ALTER TABLE DROP UNIQUE

ALTER TABLE ADD FOREIGHN KEY 컬럼명 REFERENCES 원컬럼명

ALTER TABLE DROP FOREIGHN KEY

ALTER TABLE 테이블명 RENAME 새로운 테이블명

ALTER TABLE ADD CHECK 조건

ALTER TABLE ADD CONTRAINT 조건명 CHECK 조건

ALTER TABLE DROP CHECK

ALTER TABLE 테이블명 ALTER 컬럼명 SET DEFAULT 기본값

ALTER TABLE 테이블명 ALTER 컬럼명 DROP DEFAULT

스키마 삭제

DROP DATABASE 데이터베이스명

테이블삭제

1)DROP TABLE 테이블명 

-> 전체(스키마와 내용) 삭제

2)DELETE * FROM 테이블명 

-> 스키마는 유지 내용은 삭제(레코드를 하나씩 지운다 = 범용명령어)

3)TRUNCATE TABLE 테이블명

-> 내용만 지음 스키마는 유지(전용명령어=빠름)

DCL

-권한 및 역할 설정하는 언어

-GRANT(부여)와 REVOKE(해제)

MYSQL의 유저관련 정보는

USE MYSQL

SELECT USER, HOST FROM USER;

에서 정보가 있다

MYSQL 유저등록하는 방법

CREATE USER  아이디@호스트주소 IDENTIFIED BY 비번

MYSQL 사용자 삭제

DROP USER 아이디@호스트주소

사용자 삭제 반영하기

FLUSH PRIVILEGES;

권한 부여 해제관련

예시)

특정유저의 모든 권한을 보기

SHOW GRANT FOR 아이디@LOCALHOST;

LOCALHOST에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

로컬랜(공유기내 방화벽내)에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@공유기주소 IDENTIFIED BY 비밀번호;

GRANT ALL ON 데이터베이스명.* TO 아이디@192.168.0.% IDENTIFIED BY 비밀번호;

인터넷전체에서 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@% IDENTIFIED BY 비밀번호;

검색/추가 권한만 부여

GRANT SELECT, INSERT ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

특정테이블의 특정컬럼의 업데이트 권한만 부여

GRANT UPDATE(특정컬럼) ON 데이터베이스명.테이블명 TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

모든 권한을 삭제

REVOKE ALL ON *.* FROM 아이디@LOCALHOST;

역할관련

MYSQL은 ROLE(역할) 관련된 내용이 없음

GRANT의 문제점을 해결하기 나온 개념이 ROLE(역할)이다

GRANT의 문제점

테이블수*사용자수의 조합*CRUD 만큼의 갯수만큼 종류가 굉장히 많아진다

즉, 관리 포인트가 많다

ROLE(역할)의 개념

역할별로 권한을 설정하고 사용자에게 역할을 부여하는 형태로 사용

사용자가 여러개의 롤을 가지는 것이 가능함

예시)

CREATE ROLE 역할명

GRANT INSERT, DELETE ON 테이블명 TO 역할명

GRANT 역할 TO 사용자명

MYSQL 원격접속 설정

1)사용자를 원격사용자로 등록

GRANT ALL PRIVILEGES ON 데이터베이스명.테이블명 TO 아이디@% IDENTIFIED BY 비밀번호

2)MY.INI 또는 MY.CNF파일 수정(BIND-ADDRESS부분 주석처리)

3)MYSQL 서버 재시작

4)방화벽 3306 포트 열기

검색성능개선을 위한 INDEX

인덱스개념

-검색을 빠르게 하기 위한 자료구조(주로 B트리 계열)

레코드 추가/수정/삭제시 해당 컬럼으로 다시 정렬한 후 검색에 필요한 값들을 미리 계산해 검색속도를 높임

-기본키는 자동으로 인덱스 설정

-조인에도 인덱스가 영향을 줌

빠른검색을 위해서는 정렬(쿽정렬, 힙정렬 주로 사용)이 되어있어야 함

인덱스 종류

-이진탐색

-B트리(상용DBMS에서 가장 일반적으로 많이 사용됨)

인덱스 설정

예시)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2,...)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명)

ALTER TABLE 테이블명 DROP INDEX 인덱스명

메타데이터

:DB, 테이블의 스키마에 대한 정보를 저장하는 테이블

메타데이터의 종류

1)데이터 사전(INFORMATION_SCHEMA)

일반적으로 읽기전용

데이터베이스의 정보저장

데이터의 데이터(DB스키마)가 저장되는 데이터 베이스

2)데이터 디렉토리

DBMS의 모든 데이터가 저장되는 디렉토리

DB저장, 상태 및 로그저장

****my.ini에서 디렉토리를 변경할 수 있음

예시)

SHOW DATABASES;

SHOW TABLES;

SHOW TABLE STATUS;

SHOW COULUMNS FROM 테이블명;

SHOW INDEX FROM 테이블명;

SHOW TABLES IN INFORMATION_SCHEMA;

DESCRIBE INFORMATION_SCHEMA.CHARACTER_SET

USE INFROMATION_SCHEMA;

SHOW TABLES;

1)CHARACTER_SETS

2)COLLATION

3)COLLATION_CHARATER_SET_APPLICABILITY

4)COLUMNS

5)COLUMN_PRIVILEGES

6)KEY_COLUMN_USAGE

7)REFERENTIAL_CONSTRAINTS(외래키)

8)ROUTINES

9)SCHEMATA

10)STATISTICS

11)TABLES

12)TABLE_CONSTRAINTS

13)TRIGGERS

14)USER_PRIVILEGES

15)VIEWS

캐릭터셋

:문자인코딩 정보/메타데이터의 일종

:데이터베이스/테이블별로 별도 설정 가능

예시)

ASCII/ISO-8859-1   아스키계열

EUC-KR/KSC_5601    한글 완성형 계열

UTF-8/UNICODE      유니코드계열

UTF-8(기본)

콜레이션

:데이터를 정렬(문자간의 비교)할때 사용하는 정보

:정렬시에 대소문자를 구분(CASE SENSITIVE)/비구분여부(CASE INSENSITIVE) 설정

예시)

UTF8-GENERAL-CI(기본값)

여기서 CI가 CASE INSENSITIVE임

예시)

MYSQL 기본 캐릭터셋 확인

STATUS;

OR

SHOW VARIABLES LIKE CHARACTER_SET_SYSTEM;

예시)

WORLD/CITY 캐릭터셋/콜레이션을 각각 UTF8/UTF8_GENERAL_CI로 설정

ALTER DATABASE WORLD CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

ALTER TABLE CITY CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

예시)

SAMPETESTDB를 UTF8/UTF8_GENERAL_CI로 지정하고 생성하시오

CREATE DATABASE SAMPLETESTDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

스토리지 엔진

:인덱스와 유사

MYSQL의 여러엔진중에 스토리지엔진이라는 것이 있음

외래키를 이용하려면 엔진의 기능중에 외래키 지원하는 엔진을 써야함

대표적인 엔진은 MyISAM과 InnoDB가 있음

데이터 접근속도/안정성/트랜잭션의 지원 여부 등의 차이가 있음

기본값은 InnoDB 임

InnoDB - 트랜잭션 지원 / 업데이트 위주 / 줄단위락 / 복구용이 / 동시처리성 높음

MyISAM - 상대적으로 높은 성능 / 읽기 위주 / 테이블단위 락

예시)

MySQL 기본 스토리지 엔진 확인

SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE SUPPORT='DEFAULT';

예시)

기본 스토리지 엔진을 변경

SET DEFAULT_STORAGE_ENGINE=MyISAM;(리부팅시 리셋)

OR

my.ini변경(리부팅시에도 계속 적용)

[mysqld]default-storate-engine=MyISAM

데이터베이스 백업(콘솔창에서)

1)전체 데이터베이스 백업

mysqldump -u아이디 -p --all-databases > 덤프파일명.sql

2)특정 데이터베이스 백업

mysqldump -u아이디 -p --databases DB명 > 덤프파일명.sql

3)특정 테이블 백업(데이터포함)

mysqldump -u아이디 -p DB명 테이블명 > 덤프파일명.sql

4)스키마만 백업

mysqldump -u아이디 -p --no-data world(DB명) country(테이블명) > country_schema.sql

5)데이터만 백업

mysqldump -u아이디 -p --no-create-info world country > country_data.sql

데이터베이스 백업(워크벤치에서)

1)Data Export -> Export Options -> Export to Self-Contained File

2)Tables to Export

3)테이블 선택

4)Dump Structure and Data/Structure Only/Data Only

5)Export 버튼

데이터베이스 복원(콘솔)

mysql -u아이디 -p DB명 < 파일명

mysql -u아이디 -p < 파일명

mysql -u아이디 -p DB명 테이블명 < 파일명

데이터베이스 복원(prompt)

SQL>source 파일명

데이터베이스 복업(워크벤치)

1)Data Import -> Import from Self-Contained File

2)Default Schema to be imported to DB명

3)Data Structure and Data/Dump Data Only/Dump Structure only

4)Import 버튼

데이터베이스 로그(데이터베이스 운영 상황을 별도의 파일에 저장)

1)에러로그(일반적으로 사용하는 로그)

Mysql 구동과 모니터링, Query 에러에 관련된 메시지를 포함

2)일반로그(일반적으로 사용하는 로그)

전체 쿼리에 대하여 General log를 활성화 시켜서 저장 가능

일반적으로 성능상의 문제로 남기지 않는다

3)슬로우 쿼리 로그(일반적으로 사용하는 로그)

long_query_time에 설정된 시간 이상을 소요한 쿼리를 기록

4)이진 로그/릴레이 로그(리플리케이션 복제에서 사용하는 특정 로그임)

-mysql쿼리를 수행하면서 쌓는 로그, 시점 복구 등을 수행하는 역할

-replication에서 사용

-바이너리 로그(마스터-CUD를 남기게 된다)/릴레이 로그(슬레이브)에서 바이너리 로그를 사용해서 입력한다(내용동일하게)

2018년 9월 

이코라이텍

실습과 함께하는 데이터베이스 MYSQL편

검색에 최적화된 시스템은 RDBMS계열

쓰기에 최적화된 시스템은 Nosql(Not only SQL)계열

모델링

-정규화:중복정보를 최소화시킴

이진검색은 비교하는 값이 하나라서 작다 크다만 가능

B-Tree(비트리)는 이진검색에서 비교하는 값이 2개라서 작다 크다 중간에 있다까지 가능

상용디비에서는 비트리를 쓴다고 생각하면 됨

DML(CRUD)

DDL(스카마 정의 수정) - CREATE / DROP / 

DCL(접근권한 정의) - GRANT(권한부여) / REVOKE(권한취소)

MYSQL은

INNER JOIN

LEFT JOIN

RIGHT JOIN

까지만 지원하며

FULL JOIN은 지원하지 않는다 ->각각 JOIN해서 UNION으로 합쳐야 함

VIEW는 내부 컬럼의 조건에 따라 변하기도 하고 변하지 않기도 한다

컬럼이 바로 맵핑된 경우는 원테이블이 변경되면 변경되고

가공을 거쳤다면 변경되지 않는다

LIKE검색 %와 _가 있다

NULL함수

IFNULL(MYSQL)

NVM(오라클)

집합함수

SUM

COUNT

등등

다중행 서브쿼리(다중행 연산자)

ALL -> 가장 큰값보다 큰(maximum효과)

ANY -> 가장 작은 값보다 큰(minimum효과)

IN - OR효과

EXISTS - if문효과

MYSQL은 합집합만 지원함

교집합과 차집합은 지원하지 않음

UNION은

두 쿼리의 결과 형식이 동일해야 함

다른테이블이라도 결과값의 형식만 잁치하면 됨

UINION ALL->중복허용

MYSQL 자료형

1)정수형

TINYINT(1BYTE)

INT(4BYTE)

BIGINT

2)실수형

FLOAT(4BYTE)

DOUBLE(8BYTE)

DECIMAL(십진법)

3)문자열

CHAR 고정길이 문자열(최대 255자)

VARCHAR 가변길이 문자열(최대 65,535자)

4)TEXT문자열

TEXT(2BYTE)

MEDIUMTEXT(3BYTE)

LONGTEXT(4BYTE)

5)BLOB(이미지 및 파일을관리)

BLOB(2GBYTE)

MEDIUMBLOB(3GBYTE)

LARGEBLOB(4GBYTE)

6)시간관련

DATE(YYYY-MM-DD)

TIME(HH:MI:SS)

DATETIME(YYYY-MM-DD HH:MI:SS)

TIMESTAMP(YYYY-MM-DD HH:MI:SS)

입력데이터 제약조건

NOT NULL

UNIQUE(테이블에 동일한 값 받아들여지지않음)

PRIMARY KEY(UNIQUE + NOT NULL)

FOREIGN KEY

CHECK(해당 조건일때만 입력 가능 - MYSQL지원안함)

DEFAULT(컬럼값이 입력되지 않으면 기본값 입력)

AUTO INCREMENT(MYSQL만 있음)

정규화(중복성 제거-무결성 유지)

->그리고 조인을 한다

정규형(중복을 제거하기 위한 테이블 정의 규칙)

1정규형:나눌 수 있을만큼 쪼개라

2정규형:테이블의 컬럼들이 기본키와 직접 연관되는 컬럼만으로 구성하라

3정규형:컬럼들간의 종속관계가 있으면 안됨