MySQL Data Type 과 테이블 설계 방법

MySQL Data Type 과 테이블 설계 방법

MySQL Data Type 대해서 알아보도록 하겠습니다. ^^ (언제부턴가 강좌를 쓰는 기분으로 포스팅을 하네요 ^^ )

데이터 타입 
MySQL 은 다양한 데이터 타입을 제공합니다. 아래 표를 보시면 데이터 형 , 범위 그리고 데이터 크기를 보실 수 있습니다. 데이터 타입은 실제 입력하는 데이터 형에 맞추어서 최소한의 크기로 잡아 주어야 합니다. 아래 표를 보시면 데이터 형과 그 값의 범위 그리고 크기에 대해서 보실 수 있습니다. 

MySQL에서 지원하는 데이터 형(MySQL DataTypes)

형태	데이터형	범위	크기
숫자형	TINYINT	-128 ~ 128 , 0 ~ 255	1 Byte
	SMALLINT	-32768 ~ 32767 , 0 ~ 65535	2 Byte
	MEDIUMINT	-8388608 ~ 8388607, 0 ~ 16777215	3 Byte
	INT, INTEGER	-2147483648 ~ 2147483647, 0 ~ 4294967295	4 Byte
	BIGINT	-9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615	8 Byte
	FLOAT	-3.402823466E+38 ~ -1.175494351E-38 1.175494351E-38 ~ 3.402823466E+38	4 Byte
	DOUBLE [PRECISION], REAL	1.7976931348623157E+308 ~ -2.2250738585072014E-308 0 ~ 2.2250738585072014E-308	8 Byte
	DECIMAL(M,D), NUMERIC(M,D)	데이터 베이스 설정 및 시스템에 따라 다름	가변적 크기
날자형	DATE	'1000-01-01' ~ '9999-12-31'	3 Byte
	TIME	'-838:59:59' ~ '838:59:59'	3 Byte
	DATETIME	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	8 Byte
	TIMESTAMP	'1970-01-01 00:00:01' ~ '2038-01-19 03:14:07'	4 Byte
	YEAR	1901 ~ 2155	1 Byte
문자(열)형	CHAR(M)	1~ 255 개의 문자	M <= 255 Byte
	BINARY(M)	1 ~ 255 개의 문자	M Byte
	VARCHAR(M), VARBINARY(M)	1 ~ 255 개의 문자	M Byte
	TINYBLOB, TINYTEXT	최대 2^8	입력된 길이 만큼
	BLOB, TEXT	최대 2^16	입력된 길이 만큼
	MEDIUMBLOB, MEDIUMTEXT	최대 2^24	입력된 길이 만큼
	LONGBLOB, LONGTEXT	최대 2^32	입력된 길이 만큼
	ENUM	최대 65525 개	1 ~ 2 Byte
	SET	최대 64 개의 셋	1 ~ 8 Byte

테이블을 설계 시에 데이터에 맞는 데이터 를 선정해주는것이 매우 중요합니다. 물론 큰 데이터 형으로 잡아도 문제는 없지만 데이터의 효율성에서 많은 성능 저하가 있을 수 있습니다. 

예를 들어 현재 학교 학생수를 다루는 테이블이 있다고 가정합시다. (졸업, 입학 예정, 기타 학생은 제외 시킨 재학중인 학생) 학생들이 많아야 최대 65535 명은 안되고 255명은 넘을 것입니다. 그럼 PRIMARY KEY로 잡을 키 값은 SMALLINT 형의 데이터 형으로 해야 합니다. 

또 한가지 예를 들면 커뮤니티 포털 사이트의 회원이라고 생각을 해봅시다 (국내 회원만 받을, 중복, 탈퇴 기타 예외 비 포함) 그러면 전국민이 가입해도 5천만명입니다. 그럼 어떤 데이터 형이 맞을 까요? INT형으로 잡습니다. 

위 예처럼 데이터에 가장 효율적인 방법으로 데이터 형을 설정해야 합니다. 

NULL 을 최대한 배제 합니다. 테이블 설계시 NOT NULL 이 아닌 NULL을 허용하게 되면 해당 필드가 NULL 인지를 구분할수 있게 하는 플래그를 위한 공간이 따로 필요하고 , 데이터를 제어 할때 NULL인지를 한번더 확인하게 됩니다. (NULL과 ""는 다릅니다.)

ROW_FORMAT 이 가변적인 것 보다는 FIXED 인것이 좋습니다. 최대한 기준이 되는 테이블일 경우에는 FIXED 로 사용하고 그외 가변적인 요소 (TEXT, BLOB, VARCHAR)등은 따로 테이블을 떼어 놓으세요. 성능이 많이 향상됩니다. information_schema 데이터 베이스의 TABLES 테이블에서 확인 할 수 있습니다. 

MySQL 은 PRIMARY KEY가 AUTO_INCREMENT 에 최적화 되어 있습니다. 최대한 AUTO_INCREMENT를 사용하여 효율적으로 PRIMARY KEY를 사용하세요

모든 데이터 설계는 정규화 후 설계를 하세요 MySQL Workbench 를 이용하면 정규화 작업에 많이 도움이 됩니다.(머리 속에 있는것 보다는 눈으로 보고 그려보는게 좋겠죠~) 데이터 설계는 도식화 한 후에 항상 재 검토 하세요~ 안보이는것도 보일 수 있습니다. 

무리하게 인덱스를 사용하지 마세요 . WHERE 에 쓰인다고 해서 모든것을 인덱스 할 필요는 없습니다. 최대한 필요하고 가장 간단하게 범위를 잡아서 사용을 하세요, 무리한 인덱스는 전체적인 INSERT / UPDATE 성능을 떨어 뜨릴 수 있습니다. ANALYZE TABLE 과 OPTIMIZE TABLE 로 주기적으로 데이터 단편화를 제거해주세요. 2개 이상의 조건이 필수적으로 검색에 이용이 된다면 복합 인덱스를 사용하세요 . 두 개의 필드가 인덱스가 따로 따로 잡혀 있는것 보다 더욱 빠르게 결과를 보여줍니다. 

효율적인 테이블 설계 방법 요약 
1. 저장할 데이터에 맞게 최대한 작은 데이터 타입을 사용해야 합니다. 
2. 컬럼에서 DEFAULT NULL을 최대한 허용하지 마세요
3. 가변적인 데이터타입 보다 고정형의 데이터 타입을 선택하세요
4. PRIMARY KEY는 작은 데이터형으로 잡습니다. (AUTO_INCREMENT 최대한 사용)
5. 모든 데이터는 정규화를 꼭 해야 합니다. (정규화방법 참조)
6. 인덱스는 필요한 경우만 사용합니다. 

전체적인 테이블 설계에 대한 요약이었습니다. 테이블을 설계 할때 데이터 형에 알 맞는 형태의 데이터 형을 선택을 해서 효율적으로 설계를 해보세요 ^^

 

참조 - http://www.lovelgw.com/Blog/211