MySQL/MariaDB InnoDB 스토리지 엔진 특성

 

  • Version : Mariadb 5.5.4.2-WinX64

 

InnoDB 스토리지 엔진은 MySQL/MariaDB에서 레코드 기반의 잠금을 제공하고 있으며 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

 

 

InnoDB의 구조는 크게 메모리 영역과 CPU 연산 영역, 디스크 스토리지 영역으로 구분할 수 있다. 메모리 영역에는 버퍼풀과 로그 버퍼가 있으며 CPU 연산에는 인서트 버퍼 머지 스레드, Write 스레드, 로그 스레드, 그외 기타 스레드가 있다. 디스크 영역에는 시스템 테이블 스페이스와 사용자 테이블 스페이스, 리두로그가 존재한다.

 

[InnoDB 스토리지 엔진 특성]

  • 프라이머리 키에 의한 클러스터링 : 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다. 즉 키값 순서대로 디스크에 저장된다.
  • 잠금이 필요 없는 일관된 읽기(Non-Locking consistent read) : MVCC(Multi Version Concurrency Control) 기술을 이용해 락을 걸지 않고 읽기 작업을 수행하여 다른 트랜잭션이 가지고 있는 락을 기다리지 않아도 된다.
  • 외래키 지원 : InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 여러 가지 제약사항 탓에 실무에서는 잘 사용하지 않는다. 외래키는 부모와 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고 그로 인해 데드락이 발생하기 쉽다.
  • 지동 데드락 감지 : 그래프 기반의 데드락 체크 방식을 사용하기 때문에 데드락이 발생함과 동시에 바로 감지되고 감지된 데드락은 관련 트랜잭션 중에서 ROLLBACK이 가장 용이한 트랜잭션을 자동적으로 강제 종료한다. 따라서 데드락 때문에 쿼리가 타임아웃 또는 슬로우 쿼리로 기록되는 경우는 많지 않다.
  • 자동화된 장애 복구 : 손실이나 장애로부터 데이터를 보호하기 위해 여러 메커니즘이 있으며 MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 트랜잭션(Partial write)등에 대한 일련의 복구 작업이 자동으로 진행 된다.

 

[InnoDB 버퍼풀]

스토리지 엔진의 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 변경된 데이터를 모아서 처리하기 때문에 랜덤한 디스크 작업의 횟수를 줄일 수 있다. 더티페이지는 주기적으로 또는 어떤 조건이 되면 체크포인트가 발생하는데 이때 Write 스레드가 필요한 만큼의 데이터페이지만 디스크로 기록한다. 체크포인트가 발생한다고 해서 버퍼풀의 모든 더티페이지가 디스크로 기록되는 것은 아니다.

 

[언두(Undo)로그]

언두 영역은 update 문장이나 delete와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전데이터)를 보관하는 곳이다. 언두 데이터는 크게 두 가지 용도로 사용되는데 롤백을 위한 용도와 높은 동시성을 제공하는데 사용된다.

 

[인서트버퍼(Insert Buffer)]

레코드가 INSERT되거나 UPDATE될 때 데이터 파일 변경 및 인덱스 업데이트 작업이 발생하는데인덱스 업데이트 작업은 디스크의 많은 자원을 소모한다. 그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 된데 이러한 임시 메모리 공간을 인서트 버퍼라 한다. 인서트 버퍼에 임시로 저장되어 있는 인덱스 레코드 조각은 이후 백그라운드 인서트 버퍼 머지 스레드(Merge thread)에 의해 병합된다.

 

[리두(Redo) 로그 및 로그 버퍼]

쿼리 요청에 의에 데이터 변경작업을 진행 할 때 순차적으로 변경된 내용을 기록하는 로그 파일을 리두 로그라 한다. 대용량 작업에서는 리두 로그 기록 작업이 큰 문제가 되는데 이러한 부분을 보완하기 위해 최대한 ACID 속성을 보장하는 수준에서 버퍼링하게 된다. 로그 버퍼는 이러한 리두 로그 버퍼링에 사용되는 공간이다. 로그 버퍼는 일반적으로 1~8MB 수준에서 설정하는 것이 적합한데 만약 BLOB, TEXT와 같이 큰 데이터를 자주 변경하거나 하는 경우에는 더 크게 설정 하는것이 좋다.

 

[잠금 없는 일관된 읽기(Non-locking consistent read)]

격리 수준이 SERIALIZABLE이 아닌 READ-UNCOMMITTED나 READ-COMMITTED, REPEATABLE-READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다. 특정 사용자가 레코드를 변경하고 커밋을 수행하지 않았다 하더라도 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를 "잠금없는 일관된 읽기"라 표현하고 변경되기 전의 데이터를 읽기 위해 언두(Undo) 로그를 사용한다. 가끔 오랜 시간 활성상태의 트랜잭션은 언두 로그를 삭제하지 못하고 계속 유지해야하기 때문에 서버가 느려지는 경우가 발생한다. 때문에 최대한 빨리 롤백이나 커밋을 통해 완료하는 것이 좋다.

 

[참고자료]

 

 

2015-07-27 / 강성욱 / http://sqlmvp.kr

 

 

MySQL, MariaDB, InnoDB, InnoDB Storage, Storage Architecture, 마리아DB, 마이에스큐엘, 스토리지 엔진

데이터 타입

 

  • Version : Mariadb 5.5.4.2-WinX64

 

MySQL / MariaDB 데이터 타입에 대해서 알아본다.

 

[숫자형 데이터 타입]

데이터 타입

의미

크기

설명

TINYINT

매우 작은 정수

1 byte

-128 ~ 127 (부호없이 0 ~ 255)

SMALLINT

작은 정수

2 byte

-32768 ~ 32767

MEDIUMINT

중간 크기의 정수

3 byte

-(-8388608) ~ -1(8388607)

INT

표준 정수

4 byte

~ -1

BIGINT

큰 정수

8 byte

- ~ -1

FLOAT

단정도 부동 소수

4 byte

-3.40E+45 ~ 3.40E+45 (no unsigned)

DOUBLE

배정도 부동 소수

8 byte

-1.7976E+320 ~ 1.7976E+320

(no unsigned)

DECIMAL(m,n)

고정 소수

m과 n에 따라 다르다

숫자 데이터지만 내부적으로 String형태로 저장됨. 최대 65자.

BIT(n)

비트 필드

m에 따라 다르다

 

 

[날짜형 데이터 타입]

데이터 타입

의미

크기

설명

DATE

CCYY-MM-DD

3 byte

1000-01-01 ~ 9999-12-31

DATETIME

CCYY-MM-DD hh:mm:ss

8 byte

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

TIMESTAMP

CCYY-MM-DD hh:mm:ss

4 byte

1970-01-01 00:00:00 ~ 2037

TIME

hh:mm:ss

3 byte

-839:59:59 ~ 839:59:59

YEAR

CCYY 또는 YY

1 byte

1901 ~ 2155

 

TIMESTAMP는 현재 시간이 자동으로 들어가는 데이터 타입니다.

create table TS_Test(

Num int PRIMARY KEY,

TS TIMESTAMP);

 

insert into TS_TEST (NUM) values (1);

select * from TS_TEST;

 

 

 

[문자형 데이터 타입]

데이터 타입

의미

크기

설명

CHAR(n)

고정길이 비이진(문자) 문자열

n byte

 

VARCHAR(n)

가변 길이 비이진 문자열

Length + 1 byte

 

BINARY(n)

고정길이 이진 문자열

n byte

 

VARBINARY(n)

가변 길이 이진 문자열

Length + 1 byte or 2 byte

 

TINYBLOB

매우작은 BLOB(Binary Large Object)

Length + 1 byte

 

BLOB

작은 BLOB

Length + 2 byte

최대크기 64KB

MEDIUMBLOB,

중간 크기 BLOB

Length + 3 byte

최대크기 16MB

LONGBLOB

큰 BLOB

Length + 4 byte

최대크기 4GB

TINYTEXT

매우 작은 비이진 문자열

Length + 1 byte

 

TEXT

작은 비이진 문자열

Length + 2 byte

최대크기 64KB

MEDIUMTEXT

중간 크기 비이진 문자열

Length + 3 byte

최대크기 16MB

LONGTEXT

큰 비이진 문자열

Length + 4 byte

최대크기 4GB

 

BLOB의 경우는 바이너리 데이터이므로 이미지 파일 등도 저장기 가능하다. 처리성능이 높지 않으않으 사용할 때 주의한다.

create table img_table(

NUM int primary key auto_increment,

filename varchar(64),

image MEDIUMBLOB NOT NULL

);

 

insert into img_table(image,filename) values (load_file("C:\\sql_data\\Daily_report.JPEG"), 'Daily_report.JPEG');

 

select * from img_table;

 

select image into DUMPFILE 'c:\\sql_data\\dump.jpg' from img_table where filename = 'Daily_report.JPEG';

 

 

 

 

[공간형 데이터 타입]

데이터 타입

의미

GEOMETRY

공간 값

POINT

포인트 값(한 쌍의 x,y 좌표)

LINESTRING

커브 값(하나 이상의 POINT값)

POLYGON

다각형

GEOMETRYCOLLECTION

기하학(GEOMETRY) 값의 집합

MULTILINESTRING

LINESTRING 값의 집합

MULTIPOINT

POINT 값의 집합

MULTIPOLYGON

다각형 값의 집합

 

 

2015-04-24 / 강성욱 / http://sqlmvp.kr

 

 

MySQL, MariaDB, 마리아DB, 마이에스큐엘, 데이터 타입, MYSQL 데이터 타입, MariaDB 데이터 타입

'MySQL MariaDB' 카테고리의 다른 글

ALTER, FOREIGN KEY, JOIN  (0) 2015.07.16
외부접속 허용하기  (0) 2015.07.16
데이터 타입  (0) 2015.07.16
테이블 생성 삭제 / 데이터 입력 삭제  (0) 2015.07.16
데이터베이스 생성 삭제  (0) 2015.07.16
HeidiSQL 툴 살펴보기  (0) 2015.07.16

테이블 생성 삭제 / 데이터 입력 삭제

 

  • Version : Mariadb 5.5.4.2-WinX64

 

테이블 생성 삭제에 대해서 알아본다.

 

[테이블 생성]

  • 기본 문법

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...) [table_options ]... [partition_options]

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)] [table_options ]... [partition_options]

select_statement

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_table_name | (LIKE old_table_name) }

select_statement:

[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

 

create table tbl_a (

num int,

name nvarchar(50)

)

 

 

 

[테이블 삭제]

drop table tbl_a

 

 

[자동증가 값이 있는 테이블 생성]

AUTO_INCREAMENT 옵션으로 컬럼에 자동증가 값을 부여 할 수 있다. (MS SQL Server에서는 Identity 명령어를 사용한다.)

 

create table tbl_a (

num int AUTO_INCREMENT,

name nvarchar(50),

primary key (num)

);

 

insert into tbl_a (name) values ('kang');

insert into tbl_a (name) values ('sung');

 

select * from tbl_a

 

 

SET INSERT_ID = ? 명령을 사용하여 자동 증가값의 시작 값을 변경 할 수 있다.

set insert_id = 10;

 

insert into tbl_a (name) values ('wook');

 

select * from tbl_a;

 

 

INSERT_ID 값을 이미 존재하는 값으로 변경하고 데이터를 입력하면 옵션은 오류 없이 적용 되지만 데이터를 입력 할 때 PRIMARY 오류가 발생한다.

set insert_id = 2;

 

insert into tbl_a (name) values ('kang1');

 

 

위 예제에서 INSERT_ID = 3을 입력하고 데이터를 입력하면 정상적으로 입력되며 (현재 3 값이 없기 때문에) 다음 값은 마지막 INCREMENT 값 + 1인 11로 입력 된다.

 

 

[데이터 삭제]

기본 문법

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

[RETURNING select_expr [, select_expr ...]]

 

delete from tbl_a

 

 

[기타]

AUTO_INCREAMENT에 관한 정보를 찾다 보니 InnoDB에서 AUTO_INCREAMENT를 맹신하지 말라는 글이 있다.

 

글 내용을 요약하면 MyISAM테이블을 성능 및 안정성 이슈로 InnoDB로 전환 후 서버 재시작 시 매번 Primary Key 중복 오류가 발생한 사례가 있다. Auto_Increment는 스토리지 엔진 별로 다르게 동작하기 때문에 예기치 못한 문제가 발생 할 수 있다고 한다. MyISAM경우 파일 기반이며 InnoDB는 메모리 기반의 스토리지이기 때문에 다른 방식으로 관리된다고 한다. 따라서 DB가 처음 구동이 되면 Auto_Increament 속성을 가진 테이블은 초기화 된다고 한다. 마지막 값이 NULL이면 1로 초기화 된다고 한다.

 

자세한 내용은 원문을 참고 한다.(http://gywn.net/2013/02/mysql-innodb-auto-increment/)

 

 

2015-04-10 / 강성욱 / http://sqlmvp.kr

 

 

MySQL, MariaDB, 마리아DB, 마이에스큐엘, MariaDB 설치, MySQL 생성, 데이터베이스, MariaDB 생성, 데이터베이스 생성, MariaDB 인코딩 변경, MySQL인코딩 변경

'MySQL MariaDB' 카테고리의 다른 글

외부접속 허용하기  (0) 2015.07.16
데이터 타입  (0) 2015.07.16
테이블 생성 삭제 / 데이터 입력 삭제  (0) 2015.07.16
데이터베이스 생성 삭제  (0) 2015.07.16
HeidiSQL 툴 살펴보기  (0) 2015.07.16
MariaDB 설치  (0) 2015.07.16

데이터베이스 생성 삭제

 

  • Version : Mariadb 5.5.4.2-WinX64

 

데이터베이스를 생성 및 삭제에 대해서 알아본다.

 

[데이터베이스 생성]

  • 기본 문법

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

 

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

 

CREATE DATABASE SW_TEST

 

 

SW_TEST 데이터베이스가 생성된 것을 확인 할 수 있다. 생성된 데이터베이스의 문자셋을 보면 latin1_swedisk_ci로 생성된 것을 확인 할 수 있다. 이는 현재 MariaDB의 기본 값으로 생성된다.

 

한글을 사용하기 위해서는 인코딩을 euc-kr 이나 UTF8를 사용해야한다. 문자셋을 사용자가 지정하여 데이터베이스를 생성하려면 다음과 같은 쿼리를 사용한다.

CREATE DATABASE SW_TEST

CHARACTER SET = 'UTF8'

COLLATE = 'utf8_general_ci';

 

 

 

[데이터베이스 삭제]

drop database SW_TEST

 

 

현재 데이터베이스의 캐릭터셋 상태를 보는 명렁어는 아래 스크립트를 실행 한다.

show variables like 'c%';

 

 

 

[참고자료]

CREATE DATABASE : https://mariadb.com/kb/en/mariadb/create-database/

 

 

2015-04-09 / 강성욱 / http://sqlmvp.kr

 

MySQL, MariaDB, 마리아DB, 마이에스큐엘, MariaDB 설치, MySQL 생성, 데이터베이스, MariaDB 생성, 데이터베이스 생성, MariaDB 인코딩 변경, MySQL인코딩 변경

'MySQL MariaDB' 카테고리의 다른 글

외부접속 허용하기  (0) 2015.07.16
데이터 타입  (0) 2015.07.16
테이블 생성 삭제 / 데이터 입력 삭제  (0) 2015.07.16
데이터베이스 생성 삭제  (0) 2015.07.16
HeidiSQL 툴 살펴보기  (0) 2015.07.16
MariaDB 설치  (0) 2015.07.16

+ Recent posts