최적의 데이터 타입 선택
1.한계값을 넘지 않는 가장 작은 데이터 타입을 선택해라
2.간단한 데이터 타입을 사용해라
스트링 보다는 int가 빠르다
날짜 기록에는 date type을 사용해라
ip를 저장하려면integer를 사용해라
*스트링 타입은 character set, collations 이 있다(정렬 룰)
3.널 값을 가능하면 피해라
널이 들어가면 쿼리를 옵티마이직 할때 더많은 코스트가 필요하다
널값을 의도 하지 않는 다면 not null을 사용해라
*이노디비는 널을 위해 추가 공간을 사용하지 않음
마이이삼은 함
데이터 타입
넘버
INTEGER, BOOL, NUMERIC 그냥 INT 다 ALIASES 일뿐
정수 bit
TINYINT 8
SMALLINT 16
MEDIUMNTINT 24
INT 32
BIGINT 64
UNSIGNED 를 사용하면 양수 범위 넒어짐(성능 차이는 없음)
INT(1) 과 INT (20)은 완벽하게 동일하다 (화면에 표시되는것만 다름)
실수
DECIMAL 실수를 위한거지만 BIGINT로 범위가 안되면 쓰면된다.
*정확한 소수점을 위해 사용 연산이 크기때문에 꼭 필요할 경우 사용
FLOAT
DOUBLE
DECIMAL(18,9)
전체 18 자리 = 실수 9 + 소수 9
최대 전제자리 (65숫자까지)
연산은 내부적으로 DOUBLE로 바꿔서 처리
*가능하다면 0.0001 * 1000 = 1등 INT 로처리할수있으면 그렇게 하라
스트링 타입
VARCHAR
실제 벨류 만큼만 저장해 저장공간 효율에 좋음
예외 :MyISAM table created with ROW_FORMAT=FIXED,
VARCHAR(10) 을 하게 되면 마지막에 1~2바이트의 길이 데이터가 들어가
실제적으로 11~12의 공간에 저장
여유공간이 이상을 UPDATE 하게 되면
MYISAM row fregmention
InnoDB page split 일어남!
*실제 최대 벨류보다 평균 벨류가 훨씬 작을때 사용하는게 좋다.
*업데이트가 적어야 좋다.
CHAR
CHAR VARCHAR 는 로직적으로 동일하나 저장포멧이 다른 뿐이다.
아주 작은 길이거나 평균 길이가 거의 비슷할때 좋다.
*CHAR(1) 한바이트사용 , VARCHAR(1) 길이때문에 2바이트 사용
*업데이트가 많은 칼럼으 경우는 CHAR 를 쓰자
바이너리 스트링을 저장할때
BINARY and VARBINARY
*비교할때 스트링이 아닌 바이너리로 비교하면 좋다.
*만약 비교하면 1바이트씩 비교하며 스트링 비교 보다 많이 빠르다
VARCHAR(5) VS VARCHAR(20)
메모리에 저장할때는 무조건 VARCHAR(20)의 메모리를 할당 받음으로 메모리 낭비가 생길 수 있다
그럼으로 가장 적절한 숫자를 쓴다.
BLOB
TEXT
이노디비는 테이블이 아닌 다른 부분에 저장
*다른 점은 blob은 저장 될때 CHARACTER SET, COLLATION이 없다는것
TEXT TYPE은 둘다 있음
*max_sort_length 솔트시 특정 길이로만 솔팅
쿼리에서 BLOB(or TEXT)을 사용다면 메모리디비에서 해당 타입을 지원하지 않기때문에
무조건 디스크에 임시테이블을 만들어야 된다.
(피하는 방법SUBSTRING(column, length) 을 하면 스트링으로 변해 메모리 사용가)
단 varchar 나 위의 트릭을 사용해도
max_heap_table_size or tmp_table_size 보다 사이즈가 크면 디스크에 쓴다.
ENUM
CREATE TABLE enum_test(
e ENUM('fish', 'apple', 'dog') NOT NULL
);
INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple'),(1);
SELECT e FROM enum_test;
result ->
e
fish
dog
apple
fish
실제로는 숫자를 저장하지만 외부에는 스트링으로 노출된다.
* 하지만 정렬하게 되면 숫자 자체로 정렬된다.(스트링으로 정렬안됨)
필요하다면 이렇게 가능
SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
이늄에 값을 뒤에추가 할수는 있으나 거의 고정적인 값에만쓰자
varchar랑 enum 랑 조인 할 수는 있으나 enum이 스트링으로 형변환을 해야한다. 조금 느리다.
Date and Time Types
DATETIME
1001 year ~ 9999year 까지 표현가능
저장 될시 인티저로 YYYYMMDDHHMMSS로 저장됨 (8바이트)
TIMESTAMP
4바이트
1970 year ~2038 year 까지 표현가능
타임존을 사용하기 때문에 여러 나라에서 사용하기 좋음
*null 값으로 설정하면 mysql에서 현재 시간 집어 넣는게 디폴트 not null임
가능하다면 TIMESTAMP를 사용해라
만약 마일 세컨드를 저장하고 싶다면 BIGINT 나 DOUBLE을 사용해라
BIT-Packed Data Type
BIT(1)
5.0이상에서는 실제 1비트임 ture/false로 좋음
MyISAM에서는 실제 1비트(널값 허용안할시)
InnoDB, Memory는 TINYINT로 취급
mysql에서는 비트를 숫자로 취급
mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
a | a + 0 |
+------+-------+
| 9 | 57
ASC2코드에서 57은 "9"임
*사용하지 말자 TRUE/FALSE 할꺼면 nullable CHAR(0)사용
""면 TRUE NULL이면 폴스
SET 도있지만 사용하지 말자
키값 컬럼
Integer
빠르다 A_I쓸수있어 좋다
String
가능하면 피해라
MyISAM은 스트링을 packed index로 디폴트로 만들어 느리다
MD5,UUID등은 좋지않다 P130참조
프로그램에서 ORM 쓰면 디비가 개판 날 가능성이 매우 많다.
MySql에서 스키마 짤때 조심해야 되는거!
TooManyColumn
Mysql은 엔진에서 던져준 컬럼을 디코딩해서 사용한다.
즉 컬럼이 100개 이상 되면 엄청 느려질수 있다.
TooManyJoin
조인 이 더 좋다고 하지만 Mysql에서 조인의 한계는 61개며
조인이 많으면 느려진다!
높은 동시성과 빠른 속도를 원하면 12개 정도 조인으로 결과값이 나오게하자
The all-powerful ENUM
mysql에서 이늄으로 오바하지 말자
Null 왠만하면 쓰지말자
꼭 필요하면 쓰고 아니면 대처하자
MySql에서index 만들면 null값도 인덱싱 된다(오라클과 다르게)
Normalization and Denormalization
정규화 장점
업데이트가 많으면 정규화 하는게 좋다
정규화 테이블이 일반적으로 조금하다 즉 메모리에 올라감으로 빠른경우가 많다.
정규화 테이블이 DISTINCT 나 GROUP BY를 사용할때 훨빠르다
정규화 단점
조인 해서 느리다.
하나의 인덱스로 커버 될수 있는 데이터를 여러개의 테이블로
쪼갤 수있다
비정규화 장점
조인 하는 테이블이 커서 메모리에 못올라올경우 full table 리드가 훨빠르다
(랜덤 IO가 없으니까)
인덱스 사용 계획에서 훨 빠를수 있다.
P136
프리미엄 유저가 쓴글 선택 LIMIT 0,10
정규화 되어 테이블이 두개면
A.날짜로 긁고 유저 조인해서 프리미엄인지 확인
또는 B.프리미엄 유저 같고와서 글과 조인
비정규화로 글테이블에 유저의 프리미엄 여부가 있으면
걍 읽으면됨
어떻게 할꺼냐?
select 와 upsert의 비율을 보고 필요한대로
믹싱해서 써라
조인에 꼭 필요한 컬럼이 있으면 디 노말 라이징 해라
derived 컬럼도 필요하면 컬럼으로 만들어라
eX)유저당 게시글수
Cache and Summary Tables
필요한 데이터만 뽑아서 다른 엔진에 캐쉬 테이블을 만들수있다
ex) fulltext search
캐쉬 또는 서머리 테이블 갱신할때 atomic으로 할수있다.
mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
-- populate my_summary_new as desired
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
Materialized Views
mysql에서 지원하지 않지만 http://code.google.com/p/flexviews/
라는 오픈 소스가 있음 추가하면됨
데이터 변경, 그룹바이 지원함
*원본테이블에 로우 추가되면 통계뷰에서 바이너리 로그에서
가져와 +1한다고함
카운팅 테이블
여러개 열로 분리해 mutex 줄이고 나중에 섬
mysql> CREATE TABLE hit_counter (
-> slot tinyint unsigned not null primary key,
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;
mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
mysql> SELECT SUM(cnt) FROM hit_counter;
ALTER TABLE
*걍 slave에서 만들고 마스터로 교체
mysql은 alter 하면 새로운 테이블 만들고 예전데이터를 전부 밀어 넣는다.
(https://launchpad.net/mysqlatfacebook),
Shlomi Noach’s openark toolkit (http://code.openark.org/),
and Percona Toolkit (http://www.percona.com/software/).
Flexviews (discussed in “Materialized Views” on page 138)
를 사용하면 non block 으로 할수있다
아님 shadow copy를 사용하던가
MODIFY 쓰면 리빌딩한다.
ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
ALTER COLUMN 하면 그냥 .frm 파일만 건드려 빠르다
ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;
* 비공식적이라서 위험함
백업뜨고 인덱스 빠르게 만들어야 될때
P144 확인
