데이터베이스 13주차

구조 최적화 - 테이블의 크기

테이블 별 행의 개수, 평균 바이트, 인덱스 크기 등 보기

SHOW TABLE STATUS FROM [스키마 명];

열 개수 보기

SHOW CULUMNS FROM [테이블 명];

각 열의 최대값, 최소값 최대길이, 최소길이, 등 보기

# 제안하는 테이블 구조
SELECT * FROM [테이블 명] PROCEDURE ANALYSE();

추가설명

ENUM 데이터 타입은 변하지 않는 작은 범위의 값을 저장하는 용도이다.

하지만 데이터 변경/재사용이 어렵고, DB호환성, 최적화 성능이 낮음 등의 이유로 인해 대부분의 테이블에서 ENUM을 찾아볼 수 없다.

대신 INT, CHAR 등 다양한 데이터 타입을 사용함으로써 데이터베이스의 구조를 최적화한다.

최적의 데이터타입은 숫자형 > 문자 및 문자열 > BLOB 순으로 좋다.

---

페이지 압축

내부 엔진 수준에서 동작

zlib/LZ4 등의 압축 방식 지원

운영체제 별 파일 시스템 지원이 필요하기 때문에 활용도가 떨어진다.

ALTER TABLE [테이블 명] COMPRESSION="zlib";
OPTIMIZE TABLE [테이블 명]

 

테이블 압축

페이지 압축보다 활용도가 높다.

쿼리 처리 성능이 저하될 수 있다.

데이터 변경이 많은 경우 압축률이 떨어진다.

ALTER TABLE [테이블 명] ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
OPTIMIZE TABLE [테이블 명];

 

OPTIMIZE TABLE 명령문을 사용하여 사용하지 않는 공간을 회수한다.

 

---

테이블 크기를 줄이는 방법

인덱스

짧은 이름을 사용하게 한다.

NULL값이 있으면 안된다.

삽입/업데이트 쿼리에 사용하지 않는다.

# innodb_large_prefix가 활성화 되어 있는지 확인한다.
# 활성화 되어 있다면 인덱스의 최대 크기는 3072 바이트
# 활성화 되어 있지 않다면 인덱스의 최대 크기는 767바이트이다.
SHOW VARIABLES LIKE 'innodb_large_prefix%';

 

조인

짧은 이름을 사용하게 한다.

동일한 데이터 타입끼리 조인한다.

---

구조 최적화 - 다중 테이블

SELECT문을 돌리면 캐싱이 일어나며 캐시가 쌓이게 된다. 이 캐시들을 지움으로써 최적화를 한다.

 

캐시 설정 확인

SHOW VARIABLES LIKE 'have_query_cache';

캐시 용량 확인(바이트 단위)

SHOW VARIABLES LIKE 'query_cache_size';

캐시 제한 용량 확인

SHOW VARIABLES LIKE 'query_cache_limit';

캐시 상태 확인

SHOW STATUS LIKE 'Qcache%';

캐시 삭제

RESET QUERY CACHE;

 

INSERT, UPDATE, DELETE가 발생하면 기존의 캐시를 삭제한다.

---

구조 최적화 - 내부 임시 테이블

UNION, 서브쿼리, 조인, INSERT, UPDATE에서 사용할 경우 효과적이다.

세션 혹은 연결이 종료되면 임시 테이블은 자동으로 삭제된다.

하지만 삭제가 안 될 수 도 있기 때문에 명시적으로 삭제가 필요하다.

임시 테이블 확인

SHOW STATUS WHERE VARIABLE_NAME like '%tmp%';

임시 테이블 크기 확인(max_heap_table_size - tmp_table_size)

SHOW VARIABLES WHERE VARIABLE_NAME IN('tmp_table_size', 'max_heap_table_size');

임시 테이블 생성

CREATE TEMPORARY TABLE [테이블 명](열 데이터타입, 열 데이터타입, ...)

임시 테이블 삭제

DROP TABLE [테이블 명];