내맘대로 정리하는 Real MySQL #14 #15장) 데이터 모델링과 데이터 타입

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

14장

데이터를 모델링할 때 각 개체가 어디서부터 어디까지를 포함할 것인지를 명확히 하는 것은 매우 중요하다.

Attribute는 반드시 독자적인 성질을 가지는 하나의 값만을 저장해야 한다

• 식별 관계 : 부모의 식별자가 자식 엔티티의 레코드를 식별하는데 꼭 필요하다면 그 관계는 식별 관계다.
• 비식별 관계 : 부모 엔티티의 식별자가 없어도 자식 엔티티의 레코드가 생성될 수 있을 때 비식별 관계를 사용한다.

순환관계를 계층형관계로 고친다고 해서 나아지는 것은 아무것도 없다.

15장

칼럼의 데이터 타입과 길이를 선정할 때 가장 주의해야 할 사항은 다음과 같다.

• 저장되는 값의 성격에 맞는 최적의 타입을 선정
• 가변 길이 칼럼은 최적의 길이를 지정
• 조인 조건으로 사용되는 칼럼은 똑같은 데이터 타입을 선정

문자열

CHAR와 VARCHAR의 가장 큰 공통점은 문자열을 저장할 수 있는 데이터 타입이라는 점이고, 가장 큰 차이는 고정 길이인지 가변길이인지 여부다.

• CHAR 타입의 문자열을 변경하는 경우 공간이 미리 준비되어 있으므로 그냥 변겨오디는 칼럼의 값을 업데이트 하면 된다
• VARCHAR 타입을 변경하는 경우 길이가 더 큰 값으로 변경될 때는 레코드 자체를 다른 공간으로 옮기거나(Row migration) 칼럼 값의 나머지 부분을 다른 공간에 저장(Row chaining)해야 한다

때문에 값이 2~3 바이트씩 차이가 나더라도 자주 변경될 수 있는 부서 번호나 게시물의 상태 값 등은 CHAR 타입을 사용하는 것이 좋다.

MySQL에서 CHAR나 VARCHAR 뒤에 지정하는 숫자는 그 칼럼의 바이트 크기가 아니라 문자의 수를 의미한다.

MySQL에서는 문자집합을 설정하는 시스템 변수가 여러 가지가 있는데, 모두 제각기 목적이 다르므로 주의해야 한다.

• character_set_system : MySQL 서버가 식별자(테이블이나 칼럼명)를 저장할때 사용하는 문자집합. 항상 utf8로 설정되며 사용자가 설정하거나 변경할 필요 없다
• character_set_server : MySQL 서버의 기본 문자집합
• character_set_database : MySQL DB의 기본 문자집합
• character_set_filesystem : 파일에 읽고 쓰는 쿼리에서 '파일 이름'을 해석할 때 사용되는 문자집합
• character_set_client : MySQL 클라이언트가 보낸 SQL 문장은 character_set_client에 설정된 문자집합으로 인코딩해서 MySQL 서버로 전송한다. 이 값은 각 커넥션에서 임의의 문자집합으로 변경해서 사용할 수 있다
• character_set_connection : MySQL 서버가 클라이언트로부터 전달받은 SQL 문장을 처리하기 위해 character_set_connection의 문자집합으로 변환한다.
• character_set_results : MySQL 서버가 쿼리의 처리 결과를 클라이언트로 보낼 때 사용하는 문자 집합을 설정하는 시스템 변수

SQL 문장에서 별도로 문자집합을 설정하는 지정자를 '인트로듀서'라고 하며, _문자셋이름과 같이 문자열 리티럴 앞에 언더스코어 기호와 문자집합의 이름을 붙여서 표현한다.

콜레이션은 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다.

콜레이션의 이름은 3개 또는 2개의 파트로 구성되어 있다.

3개로 구성된 경우

• 첫 번째 파트 : 문자집합의 이름
• 두 번째 파트 : 해당 문자집합의 하위 분류
• 세 번째 파트 : ci이면 대소문자를 구분하지 않고, cs이면 대소문자를 별도의 문자로 구분한다

2개로 구성된 경우

• 첫 번째 파트 : 문자집합의 이름
• 두 번째 파트 : bin이라는 키워드를 사용하는데 이는 이진 데이터를 뜻하며, 문자 데이터의 바이트 값을 기준으로 수행한다

숫자

숫자를 저장하는 타입은 크게 값의 정확도에 따라 참값과 근사값 타입으로 나눌 수 있다

• 참값 : 소수점 이하 값의 유무에 관계없이 정확히 그 값을 그대로 유지하는 것을 의미한다.
• 근사값 : 부동소수점

MySQL에서 FLOAT이나 DOUBLE과 같은 부동소수점 타입은 잘 사용하지 않는다. 또한 십진 표기법을 사용하는 DECIMAL 타입은 이진 표기법을 사용하는 타입보다 저장 공간을 2배 이상 필요로 한다. 매우 큰 숫자 값이나 고정 소수점을 저장해야 하는 것이 아니라면 일반적으로 INTEGER나 BIGINT 타입을 자주 사용하는 편이다.

DECIMAL(20, 5)라고 정의하면 정수부를 15자리까지, 소수부를 5자리까지 저장할 수 있는 DECIMAL 타입을 생성한다.

모든 정수 타입은 고정형 데이터 타입이며, 정수 뒤에 명시되는 괄호는 화면에 표시할 자리 수를 의미할 뿐 저장 가능한 값을 제한하는 용도가 아니다. 여기서 자리 수라는 것도 숫자의 앞을 0으로 얼마나 채울지 의미할 뿐이다.

날짜와 시간

MySQL 5.6.4 버전부터 마이크로초 단위의 값을 관리할 수 있는 기능이 추가됐다.

DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존에 관계없이 클라이언트로부터 입력된 값을 그대로 저장하고 조회할 때도 변환 없이 그대로 출력한다. 하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.

system_time_zone 시스템 변수는 MySQL 서버의 타임존을 의미하며, 일반적으로 이 값은 운영체제의 타임존을 그대로 상속받는다. 시스템 타임존은 MySQL을 기동하는 OS 계정의 환경 변수를 변경하거나 mysqld_safe를 시작할때 --timezone 옵션을 이용해 변경하는 것이 가능하다.

유닉스 계열의 운영체제나 맥 OS X 운영체제는 자체적인 타임존 정보를 가지고 있는데, 이러한 타임존 정보를 mysql_tzinfo_to_sql이라는 MySQL 유틸리티를 이용해 MySQL의 타임존으로 등록할 수 있다.

TEXT, BLOB

MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 하는데, 이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 작동한다. TEXT 타입과 BLOB 타입의 유일한 차이점은 TEXT 타입은 문자열을 저장하는 대용량 칼럼이라 문자집합이나 콜레이션을 가진다는 것이고, BLOB 타입은 이진 데이터 타입이라서 별도의 문자집합이나 콜레이션을 가지지 않는다는 것이다.

TEXT나 BLOB과 같은 칼럼이 포함된 테이블에서는 SELECT * FROM ... 과 같은 쿼리 보다는 꼭 필요한 칼럼만 조회하는 쿼리를 사용하길 권장한다.