MySQL 퍼포먼스 향상 (2) 스키마 최적화

2. 스키마 최적화, 그리고 인덱스

데이터형 지정 관련

  - 데이터형은 사용가능한 가장작은 형을 사용해야 한다.

  - 문자보다는 숫자같이 단순한 형이 좋다.

  - 필드는 NOT NULL 로 지정하고 디폴트 값을 넣자. NULL 로 지정하면 MySQL 내부에서 처리를 해야하고 인덱스 붙이기에도 적절하지 않다.

날짜 관련

  - DATETIME, TIMESTAMP 의 차이. TIMESTAMP 는 반의 용량을 사용하며, 타임존에도 대응한다.

숫자형 관련

  - 정수형도 좀더 세분하게 나눠서 정의하자. 무조건 INT 는 피하자

  - 정수계산은 32 비트 아키텍처라 하도 64비트로 계산된다.

  - double, float, decimal 같은 실수형. decimal 은 소수점 계산이 가장 정확하다. 돈 계산같은 정확한 계산에 필요함

  - double, float 은 물론 decimal 형보다는 사이즈가 작다. 

문자열 관련

  - VARCHAR 와 가변형이기 때문에 필요한 만큼 사용한다. MyISAM ROW_FORMAT=FIXED 에서는 일정 사이즈 사용할수 있으니 주의

  - VARCHAR 가 가변형이어도 일단 데이터의 길이를 기억하는 공간이 필요한데, 255 까지는 1 바이트 , 256 부터 2 바이트를 사용한다.

  - VARCHAR 는 기억영역의 절약에는 도움이 되지만, 갱신시에는 크게 될 가능성도 있다.

  - CHAR 는 매우 짧은 문자열을 저장할 때만 사용하자. 문자열의 길이가 늘어날 일이 없는 경우가 좋다

  - CHAR 는 데이터 단편화가 발생하기 어렵다. 빈번히 변화하는 데이터에 VARCHAR 가 좋다.

바이너리 문자열 관련

  - 바이너리 문자열은 BINARY, VARBINARY 가 있음. 

  - 바이너리 문자열과 문자열의 차이점은 문자대신 바이트를 이용한다. 그러므로 바이트 단위로 비교해야 할때, 좋다. 대소문자 구분도 없다. 바이너리 문자열을 1 바이트씩 수치로써 비교하기 때문에 문자열 비교보다 단순하여 고속 비교가 가능하다.

  - 대용량 바이너리 BLOB , 대용량 문자열 TEXT 형들이 존재한다.

  - BLOB, TEXT 형의 값을 자체 ID 를 부여하여 오브젝트로서 관리한다.

  - BLOB, TEXT 데이터는 문자열전체에서 소트하는 것이 아닌, 최초 max_sort_length 만을 소트시킨다. 변경하고 싶은 경우에 max_sort_length 를 조정하거나, ORDER BY SUBSTRING 을 이용하자.

  - 바이너리, TEXT 에는 인덱스를 지정할 수 없다.

ENUM 관련

  - ENUM 형은 65535 개까지 저장가능한 문자열. 매우 컴팩트하다.

  - ENUM 소트는 문자열이 아닌 내부 숫자로 실행된다. FILED 함수를 사용하여 소트의 순서를 지정할 수 도 있으나 이 경우 인덱스 사용불가

  - ENUM 은 문자열 리스트가 고정이며, 문자열의 추가삭제에는 ALTER TABLE 을 사용해야만 한다.

  - ENUM 은 정수형으로 저장되어짐. 날짜, 시간 저장에는 비추천.

날짜시간 관련

  - DATETIME 은 타임존에 관계없이 YYYYMMDDHHMMSS 의 정수가 들어간다. 8 바이트 사용.

  - TIMESTAMP 는 유닉스 타임스탬프와 동일. 4 바이트 사용.

  - TIMESTAMP 는 디폴트로 NOT NULL 현재시각이 입력된다.

  - TIMESTAMP 는 DATETIME 보다 스토리지 효율이 좋아서 매우 추천.

식별자 관련

  - MySQL 에서 BIT 는 문자열이다. 바이너리 0 또는 1 로 저장되어 있다가 결과는 문자열로 변환되어 나타내므로 비추천

  - 식별자는 정수형이 빠르게 처리되며 AUTO INCREMENT 에도 대응된다.

  - ENUM, SET 은 상태플래그, 카테고리 등으로 사용하기에 적합하다.

  - 문자열을 식별자로 사용하면, MyISAM 에서 인덱스 지정시 퍼포먼스가 나빠진다. 문자열은 식별자 사용에 적합하지 않다.