이코리아텍 실습과 함께하는 데이터베이스 MYSQL 강의 정리3/3

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

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-반정규형 데이터

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