Loading...
MySQL 9.5 Reference Manual 9.5의 10.4.1 Optimizing Data Size의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
디스크 상에서 사용할 공간을 최소화하도록 테이블을 설계하십시오. 이렇게 하면 디스크에 기록·읽기되는 데이터 양을 줄여서 큰 폭의 개선을 얻을 수 있습니다. 더 작은 테이블은 일반적으로 쿼리 실행 중에 그 내용이 적극적으로 처리될 때 필요한 메인 메모리도 더 적게 듭니다. 테이블 데이터에 대한 어떤 공간 절감도 더 작고 더 빠르게 처리 가능한 인덱스로 이어집니다.
MySQL은 많은 스토리지 엔진(테이블 타입)과 행 포맷을 지원합니다. 각 테이블마다 어떤 스토리지 및 인덱싱 방식을 사용할지 결정할 수 있습니다. 애플리케이션에 적합한 테이블 포맷을 선택하면 큰 성능 향상을 얻을 수 있습니다. Chapter 17, The InnoDB Storage Engine 및 Chapter 18, Alternative Storage Engines를 참조하십시오.
다음에 열거된 기법들을 사용하면 테이블 성능을 더 좋게 하고 스토리지 공간을 최소화할 수 있습니다:
가능한 한 가장 효율적인(가장 작은) 데이터 타입을 사용하십시오.
MySQL에는 디스크 공간과 메모리를 절약하는 특화된 타입이 많이 있습니다. 예를 들어, 더 작은 정수 타입을 사용할 수 있다면 더 작은 테이블을 얻을 수 있습니다. MEDIUMINT는 INT보다 나은 선택인 경우가 자주 있는데, MEDIUMINT 컬럼이 25% 더 적은 공간을 사용하기 때문입니다.
가능하다면 컬럼을 NOT NULL로 선언하십시오.
이렇게 하면 각 값이 NULL인지 여부를 검사하는 오버헤드를 제거하고 인덱스를 더 잘 사용할 수 있어 SQL 연산이 더 빨라집니다. 또한 컬럼당 1비트의 스토리지 공간을 절약합니다. 테이블에 정말로 NULL 값이 필요하다면 사용하십시오. 다만 모든 컬럼에 NULL 값을 허용하는 기본 설정은 피하십시오.
InnoDB 테이블은 기본적으로 DYNAMIC 행 포맷을 사용하여 생성됩니다.DYNAMIC이 아닌 다른 행 포맷을 사용하려면 innodb_default_row_format을 설정하거나, CREATE TABLE 또는 ALTER TABLE 문에서 ROW_FORMAT 옵션을 명시적으로 지정하십시오.compact 계열 행 포맷(COMPACT, DYNAMIC, COMPRESSED 포함)은 일부 연산에서 CPU 사용량을 증가시키는 대가로 행 스토리지 공간을 줄여 줍니다. 워크로드가 일반적인 경우, 즉 캐시 히트율과 디스크 속도에 의해 제한되는 경우 더 빠를 가능성이 큽니다. CPU 속도에 의해 제한되는 드문 경우에는 더 느릴 수 있습니다.
compact 계열 행 포맷은 또한 utf8mb3 또는 utf8mb4와 같은 가변 길이 문자 집합을 사용할 때 CHAR 컬럼 스토리지를 최적화합니다.
ROW_FORMAT=REDUNDANT를 사용할 때 CHAR(N)은 문자 집합의 최대 바이트 길이에 _N_을 곱한 만큼의 공간을 차지합니다. 많은 언어는 주로 단일 바이트의 utf8mb3 또는 utf8mb4 문자를 사용해 작성할 수 있으므로, 고정 스토리지 길이는 종종 공간을 낭비하게 됩니다. compact 계열 행 포맷에서는 InnoDB가 이러한 컬럼에 대해 뒤따르는 공백을 제거함으로써 스토리지를 _N_에서 N × 문자 집합의 최대 바이트 길이까지의 범위에서 가변적으로 할당합니다. 일반적인 경우 인플레이스 업데이트를 용이하게 하기 위해 최소 스토리지 길이는 N 바이트입니다. 자세한 내용은 Section 17.10, “InnoDB Row Formats”를 참조하십시오.
테이블 데이터를 압축된 형태로 저장해 공간을 더 줄이려면, InnoDB 테이블을 생성할 때 ROW_FORMAT=COMPRESSED를 지정하거나, 기존 MyISAM 테이블에 대해 myisampack 명령을 실행하십시오.
(InnoDB 압축 테이블은 읽기와 쓰기가 가능하지만, MyISAM 압축 테이블은 읽기 전용입니다.)
MyISAM 테이블의 경우, 가변 길이 컬럼(VARCHAR, TEXT, BLOB 컬럼)이 하나도 없다면 고정 크기 행 포맷이 사용됩니다. 이는 더 빠르지만 약간의 공간을 낭비할 수 있습니다. Section 18.2.3, “MyISAM Table Storage Formats”를 참조하십시오.
VARCHAR 컬럼이 있더라도 ROW_FORMAT=FIXED라는 CREATE TABLE 옵션을 사용하여 고정 길이 행을 사용하도록 힌트를 줄 수 있습니다.
테이블의 프라이머리 인덱스는 가능한 한 짧게 유지해야 합니다. 이렇게 하면 각 행의 식별을 쉽고 효율적으로 할 수 있습니다. InnoDB 테이블의 경우, 프라이머리 키 컬럼이 각 세컨더리 인덱스 항목에 중복 저장되므로, 세컨더리 인덱스가 많다면 짧은 프라이머리 키가 상당한 공간을 절약해 줍니다.
쿼리 성능을 향상하는 데 필요한 인덱스만 생성하십시오. 인덱스는 데이터 검색에는 좋지만 insert 및 update 연산을 느리게 합니다. 테이블에 접근할 때 대부분 여러 컬럼의 조합으로 검색한다면, 각 컬럼에 대해 별도의 인덱스를 만드는 대신 이들 컬럼에 대한 단일 복합 인덱스를 생성하십시오. 인덱스의 첫 부분은 가장 많이 사용되는 컬럼이어야 합니다. 테이블에서 select할 때 항상 여러 컬럼을 사용하는 경우, 인덱스의 첫 컬럼은 인덱스 압축을 더 좋게 하기 위해 가장 중복이 많은 컬럼이어야 합니다.
긴 문자열 컬럼이 처음 몇 문자에서 고유한 프리픽스를 가질 가능성이 매우 높다면, MySQL이 컬럼의 왼쪽 부분에 대한 인덱스 생성을 지원한다는 점을 활용하여 이 프리픽스만 인덱싱하는 것이 낫습니다( Section 15.1.18, “CREATE INDEX Statement” 참조).
더 짧은 인덱스는 디스크 공간을 더 적게 사용할 뿐만 아니라 인덱스 캐시에서 더 많은 히트를 제공하므로 디스크 시크 횟수도 줄어들어 더 빠릅니다. Section 7.1.1, “Configuring the Server”를 참조하십시오.
어떤 상황에서는 매우 자주 스캔되는 테이블을 둘로 나누는 것이 도움이 될 수 있습니다. 특히 동적 포맷 테이블이고, 더 작은 정적 포맷 테이블을 사용해 테이블을 스캔할 때 관련 행을 찾을 수 있다면 그렇습니다.
서로 다른 테이블에서 동일한 정보를 담는 컬럼은 조인을 빠르게 하기 위해 동일한 데이터 타입으로 선언하십시오. 이렇게 하면 해당 컬럼을 기반으로 하는 조인이 빨라집니다.
컬럼 이름을 단순하게 유지하여 서로 다른 테이블에서 동일한 이름을 사용할 수 있게 하고 조인 쿼리를 단순화하십시오. 예를 들어, customer라는 이름의 테이블에서는 컬럼 이름을 customer_name 대신 name으로 사용하십시오. 다른 SQL 서버로의 이식성을 높이려면 이름 길이를 18자 미만으로 유지하는 것을 고려하십시오.
일반적으로, 모든 데이터를 중복 없이 유지하려고 노력하십시오(데이터베이스 이론에서 말하는 third normal form을 따름).
이름, 주소 같은 긴 값을 반복해서 저장하는 대신, 이들에게 고유한 ID를 부여하고 여러 더 작은 테이블에 이 ID를 필요에 따라 반복 저장한 뒤, join 절에서 ID를 참조하여 테이블을 조인하는 방식으로 쿼리를 구성하십시오.
디스크 공간보다 속도가 더 중요하고, 데이터의 여러 사본을 유지하는 유지보수 비용을 감수할 수 있다면(예: 큰 테이블의 모든 데이터를 분석하는 비즈니스 인텔리전스 시나리오), 정규화 규칙을 완화하여 정보를 중복 저장하거나 요약 테이블을 생성해 더 높은 속도를 얻을 수 있습니다.
10.4 Optimizing Database Structure
10.4.2 Optimizing MySQL Data Types