MySQL : 스키마,테이블 만드는 방법, 테이블 엔진 종류 , db설계 방법

포트폴리오 기록

MySQL : 스키마,테이블 만드는 방법, 테이블 엔진 종류 , db설계 방법

흰색기린 2021. 8. 24. 22:34

MySql workbench 사용법에 대해서 알아본다.

UTF-8 설정을 먼저 해주도록 한다.

먼저 스키마를 생성한다.

스키마가 생성된 것을 확인할 수 있음.

스키마는 ?

1. 스키마는 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술한 메타데이터의 집합.

2. 스키마는 데이터베이스를 구성하는 데이터 개체(Entity), 속성(Attribute), 관계(Realtionship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.

3. 스키마는 사용자의 관점에 따라 외부 스키마,개념 스키마,내부 스키마로 나눠짐.

스키마를 생성한 후 테이블을 생성

MySql db에는 엔진이 있다.. 엔진은 무엇인가?

스토리지엔진은 DB에서 데이터를 어떠한 방식으로 저장하고 접근할 것인지에 대한 기능을 제공함.

스토리지엔진의 특성에 따라 데이터 접근이 얼마나 빠른지, 얼마나 안정적인지, 트랜잭션 등의 기능을 제공하는지 등의 차이점이 발생한다.

하나씩 알아보자.

1. MyISAM

MySQL의 기본 스토리지 엔진이다.

데이터 저장에 실제적인 제한이 없고 매우 효율적으로 저장.

Full-Text 인덱스를 지원하며 특정 인덱스에 대해 메모리 캐쉬를 지원함.

트랜잭션은 미지원(?) 테이블 레벨의 락을 지원 잦은 변경 및 삭제에는 좋은 성능이 나오지 못하나 데드락 발생은 예방가능

테이블작업시 특정행을 수정하려고 하면 테이블 전체에 락이 걸려서 다른 사람 작업 X (주로 select 작업이 많은 경우에 사용 된다.)

Table-level Lock이 걸림

2. InnoDB

ACID 트랜잭션을 지원하며, MyISAM보다 데이터 저장비율이 낮고, 데이터 로드 속도가 느리다.

특정 데이터와 인덱스에 대해서 메모리 캐쉬를 지원하며 외부티를 지원한다. 데이터 압축이 불가능하고 자동 에러 복구 기능이 있다. 테이블 레벨이 아닌 ROW 레벨의 락을 지원한다.

테이블작업시 작업 시작하면 해당 열만 잠기게 되며 나머지 부분은 다른 사용자가 수정 가능

Row-level Lock이 걸림

row-level 이다보니깐 insert,update,delete에 대한 속도가 빠르다.

주로 데이터 입력 및 수정이 빈번한 높은 퍼포먼스를 요구하는 대용량 사이트에서 적합

3. Cluster (NDB)

트랜잭션을 지원하고 모든 데이터와 인덱스가 메모리에 존재하여 매우 빠른 데이터 로드 속도를 자랑하며 PK 사용시 최상의 속도를 나타낸다.

4. Archive

MySQL 5.0부터 새롭게 도입된 엔진으로 자동적으로 데이터 압축을 지원하며 다른 엔진에 비해 80% 저장공간 절약 효과를 자랑한다.

그리고 가장 빠른 데이터 로드 속도 또한 자랑하지만, INSERT와 SELECT만이 가능하다.

5. Federated

MySQL 5.0부터 새롭게 도입된 엔진으로 물리적 데이터베이스에 대한 논리적 데이터베이스를 생성하여 원격 데이터를 컨트롤 할 수 있따. 실행속도는 네트워크 요소에 따라 좌우되며 테이블 정의를 통한 SSL 보안 처리를 한다.

분산 데이터베이스 환경에 사용한다.

회원을 위한 테이블이므로 중요하기 때문에 복구능력이 좋은것은 Innodb이므로 채택했고, insert,update,delete가 많이 일어나면 속도가 빠른 면에서도 Innodb가 좋아서 선택했다

create table tab_myisam(
	mem_age int not null,
    primary key(mem_age)
)engine=MyISAM;

create table tab_innodb(
	mem_age int not null,
    primary key(mem_age)
)engine=InnoDB;

insert into tab_myisam (mem_age) value (15);
insert into tab_innodb (mem_age) value (15);

insert into tab_myisam (mem_age) values(13), (14), (15);
insert into tab_innodb (mem_age) values(13), (14), (15);

처음에는 MyISAM 엔진을 선택하려고 했으나, 복구 능력이 좋지 않고, 갈아타는 경우도 많다고 해서 InnoDB를 선택했다.

그리고 테스트를 해봤는데 (MyISAM 테이블 VS InnoDB 테이블)

같은 테이블로 pk를 지정해서 데이터를 각각 1,2,3 1,2,3을 넣었는데 MyISAM테이블은 트랜잭션을 지원하지 않아서 1,2,3이 모두 들어갔다.. unique 제약 조건에 걸려서 들어가지 않아야 하는데 !

InnoDB 테이블은 3 외에는 없는걸로 나온다.

InnoDB로 멤버 테이블을 만들었다.