[MySQL] MySQL 데이터 유형 최적화

최적화된 데이터 유형 선택

MySQL은 다양한 데이터 유형을 지원하며, 고성능을 달성하려면 올바른 데이터 유형을 선택하는 것이 중요합니다. 저장하는 데이터 유형에 관계없이 더 나은 선택을 하는 데 도움이 되는 몇 가지 원칙은 다음과 같습니다.

일반적으로 작을수록 좋습니다

일반적으로 데이터를 올바르게 저장할 수 있는 가장 작은 데이터 유형을 사용하도록 노력해야 합니다(예를 들어 0-200만 저장하면 되며,tintinint unsigned가 더 좋습니다) ). 데이터 유형이 작을수록 일반적으로 더 적은 디스크, 메모리, CPU 캐시를 차지하고 처리하는 데 더 적은 CPU 주기가 필요하기 때문에 더 빠릅니다.

단순한 것이 좋다

단순 데이터 유형에 대한 작업에는 일반적으로 더 적은 CPU 주기가 필요합니다. 예를 들어, 문자 집합과 대조 규칙으로 인해 문자열 비교가 정수 비교보다 더 복잡해지기 때문에 정수 연산은 문자 연산보다 비용이 저렴합니다. 여기에 두 가지 예가 있습니다. 하나는 날짜와 시간을 저장하기 위해 문자열 대신 MySQL의 내장 유형(예: 날짜, 시간, 날짜/시간)을 사용해야 한다는 것이고, 다른 하나는 IP 주소를 저장하기 위해 정수를 사용해야 한다는 것입니다.

NULL 사용을 피하세요

애플리케이션이 NULL을 저장할 필요가 없더라도 NULL이 열의 기본 속성이기 때문에 많은 테이블에 NULL 가능 열이 포함되어 있습니다. 실제로 NULL 값을 저장해야 하는 경우가 아니면 일반적으로 열을 NOT NULL로 지정하는 것이 가장 좋습니다.

쿼리에 NULL 허용 열이 포함되어 있으면 MySQL이 최적화하기가 더 어렵습니다. NULL 허용 열은 인덱스, 인덱스 통계 및 값 비교를 더 복잡하게 만들기 때문입니다. NULL이 될 수 있는 열은 더 많은 저장 공간을 사용하고 MySQL에서 특별한 처리가 필요합니다. NULL 가능 열이 인덱싱되면 각 인덱스 레코드에는 추가 바이트가 필요하며 이로 인해 MyISAM에서는 고정 크기 인덱스(예: 정수 열이 하나만 있는 인덱스)가 가변 크기 인덱스가 될 수도 있습니다.

일반적으로 NULL 값을 NOT NULL로 변경하여 가져오는 성능 향상은 상대적으로 작기 때문에 (튜닝 시) 결정되지 않는 한 기존 스키마에서 이러한 상황을 먼저 찾아 수정할 필요가 없습니다. 문제를 일으킵니다. 그러나 열에 인덱스를 만들려는 경우 해당 열을 NULL이 가능하도록 설계하지 않아야 합니다.

물론 예외도 있습니다. 예를 들어 InnoDB는 NULL 값을 저장하기 위해 별도의 비트를 사용하므로 희소 데이터에 대한 성능이 좋습니다(대부분의 값은 NULL이고 일부만 해당). 행은 NULL이 아닌 값입니다). 그러나 이는 MyISAM에는 적용되지 않습니다.

---

정수 유형

정수를 저장하는 경우 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT와 같은 정수 유형을 사용할 수 있습니다. 각각 8, 16, 24, 32, 64비트 저장 공간을 사용하세요. 저장 범위는 -2의 (N-1) 거듭제곱, 2의 (N-1) 거듭제곱 -1까지이며, 여기서 N은 저장 공간의 비트 수입니다.

정수형에는 선택적 UNSIGNED 속성이 있는데, 이는 음수 값이 허용되지 않음을 의미하며, 이는 양수의 상한을 대략 두 배로 늘릴 수 있습니다. 예를 들어 TINYINT UNSIGNED가 저장할 수 있는 범위는 0-입니다. 255이고 TINYINT의 저장 범위는 -128~127입니다.

MySQL은 INT(11)와 같은 정수 유형의 너비를 지정할 수 있습니다. 이는 대부분의 애플리케이션에서 의미가 없습니다. 이는 MySQL의 일부 대화형 도구(예: MySQL 명령줄 클라이언트) 문자 수를 표시하는 데 사용됩니다. 저장의 경우 INT(1)과 INT(20)은 동일합니다.

실수형

실수는 소수 부분이 있는 숫자입니다. 그러나 소수 부분만 저장하는 것이 아니라 DECIMAL을 사용하여 BIGINT보다 큰 정수를 저장할 수도 있습니다. MySQL은 정확한 유형과 부정확한 유형을 모두 지원합니다.

FLOAT 및 DOUBLE 유형은 표준 부동 소수점 연산을 사용한 대략적인 계산을 지원합니다. 부동 소수점 연산이 계산되는 방법을 알아야 하는 경우 사용 중인 플랫폼에서 부동 소수점 숫자의 특정 구현을 연구해야 합니다.

DECIMAL 유형은 정확한 소수를 저장하는 데 사용됩니다. 그러나 CPU는 DECIMAL의 직접 계산을 지원하지 않기 때문에 MySQL5.0 이상 버전에서는 MySQL 서버 자체가 DECIMAL의 고정밀 계산을 구현합니다. 상대적으로 말하면 이는 기본 부동 소수점 연산을 직접 지원하는 CPU보다 느립니다.

부동 소수점 유형과 DECIMAL 유형 모두 정밀도를 지정할 수 있습니다. DECIMAL 열의 경우 소수점 앞뒤에 허용되는 최대 자릿수를 지정할 수 있습니다. 이는 열의 공간 소비에 영향을 줍니다.

부동 소수점 유형은 동일한 범위의 값을 저장할 때 일반적으로 DECIMAL보다 적은 공간을 사용합니다. FLOAT는 4바이트의 저장 공간을 사용합니다. DOUBLE은 8바이트를 차지하며 FLOAT보다 정밀도가 높고 범위가 넓습니다.

추가 공간과 계산 오버헤드가 필요하므로 금융 데이터 저장과 같이 소수에 대한 정확한 계산을 수행할 때는 DECIMAL만 사용하도록 해야 합니다. 하지만 데이터의 양이 상대적으로 많은 경우에는 DECIMAL 대신 BIGINT를 사용하는 것을 고려해 볼 수 있으며, 저장할 통화 단위에 소수점 이하 자릿수에 따라 해당 배수를 곱하면 됩니다. 금융 데이터를 10,000분의 1까지 정확하게 저장하려고 한다고 가정하면 모든 금액에 1백만을 곱한 다음 결과를 BIGINT에 저장할 수 있습니다. 이렇게 하면 부정확한 부동 소수점 저장 계산 문제와 정확한 DECIMAL 계산에 드는 비용이 많이 드는 문제를 피할 수 있습니다.

문자열 유형

다음 설명에서는 사용되는 스토리지 엔진이 InnoDB/또는 MyISAM이라고 가정합니다. 이 두 스토리지 엔진에서 지원하지 않는 경우 사용되는 스토리지 엔진의 설명서를 참조하세요.

VARCHAR 및 CHAR

VARCHAR: 필요한 공간만 사용하므로 고정 길이 유형보다 공간을 더 절약합니다. VARCHAR은 공간을 절약하므로 성능에도 도움이 됩니다. 그러나 행의 길이가 가변적이므로 UPDATE 중에 행이 원본보다 길어질 수 있으므로 추가 작업이 필요합니다.

다음 상황에서는 VARCHAR을 사용하는 것이 적절합니다. 문자열의 최대 길이가 평균 길이보다 훨씬 길기 때문에 UTF와 같은 복잡한 문자 집합에서는 조각화가 문제가 되지 않습니다. -8을 사용하며, 각 문자마다 다른 바이트 수를 사용합니다.

버전 5.0 이상에서 MySQL은 저장 및 검색 시 후행 공백을 유지합니다. InnoDB는 더 유연합니다. 긴 VARCHAR을 BLOB

CHAR: 고정 길이로 저장할 수 있습니다. CHAR 값을 저장할 때 MySQL은 모든 후행 공백을 삭제합니다. 고정 길이 CHAR 유형은 단편화가 덜 발생하며 매우 짧은 열의 경우 CHAR는 레코드 길이가 1~2바이트 추가되는 VARCHAR보다 저장 공간에서 더 효율적입니다. CHAR는 매우 짧은 문자열을 저장하거나 모든 값이 동일한 길이에 가까울 때 적합합니다. 예: CHAR은 고정 길이 값이기 때문에 비밀번호의 MD5 값을 저장하는 데 매우 적합합니다. CHAR에는 비교를 용이하게 하기 위해 필요에 따라 공백이 채워집니다.

CHAR 및 VARCHAR과 유사한 유형으로는 바이너리 문자열을 저장하는 BINARY 및 VARBINARY가 있습니다. 바이너리 문자열은 문자가 아닌 바이트코드를 저장합니다.

이진 비교의 장점은 대소문자 구분에만 있는 것이 아닙니다. MySQL은 BINARY 문자열을 한 번에 한 바이트씩 비교하고 바이트 값을 기준으로 비교합니다. 따라서 바이너리는 문자보다 훨씬 간단하므로 속도가 더 빠릅니다.

BLOB 및 TEXT 유형

BLOB 및 TEXT 유형: BLOB 및 TEXT는 모두 대용량 데이터를 저장하도록 설계된 문자열 데이터 유형이며 각각 바이너리 및 문자 모드로 저장됩니다. BLOB 및 TEXT 값이 너무 크면 InnoDB는 전용 "외부" 저장 영역을 사용하여 저장합니다. 원래 테이블 필드 저장 포인터는 외부 저장 영역을 가리킵니다.

MySQL은 BLOB 및 TEXT 열을 다른 유형과 다르게 정렬합니다. 전체 문자열 대신 열의 첫 번째 max_sort_length 바이트만 정렬합니다. 첫 번째 문자의 작은 부분만 정렬해야 하는 경우 max_sort_length 구성을 줄이거나 ORDER BY SUSTRING(column, length)을 사용할 수 있습니다.

MySQL은 BLOB 및 TEXT 열의 전체 길이 문자열을 색인화할 수 없으며 이러한 색인을 사용하여 정렬을 제거할 수도 없습니다.

문자열 형식 대신 열거형(ENUM)을 사용하세요

문자열 형식 대신 열거형(ENUM)을 사용할 수 있습니다. 일반적으로 사용되는 문자열 유형 대신 열거형 열을 사용하는 것이 권장되는 경우가 많습니다.

(1) 열거형 열은 일부 고유 문자열을 사전 정의된 컬렉션에 저장할 수 있습니다.
(2) Mysql은 열거형을 저장할 때 매우 컴팩트하며 목록 값의 수에 따라 1바이트 또는 2바이트로 압축됩니다.
(3) MySQL은 내부적으로 목록의 각 값의 위치를 ​​정수로 저장하고, "숫자-문자열" 매핑 관계의 "조회 테이블"을 테이블의 .frm 파일에 저장합니다.

참고: 한 가지 놀라운 점은 열거형 필드가 정의된 문자열이 아닌 내부에 저장된 정수를 기준으로 정렬된다는 것입니다.

참고: 열거형의 가장 나쁜 점은 문자열 목록이 고정되어 있다는 것입니다. 문자열을 추가하거나 삭제하려면 ALTER TABLE을 사용해야 하므로 나중에 변경될 수 있는 일련의 문자열에 대해서는 열거형을 사용하고 좋지 않습니다. 목록 끝에만 요소를 추가할 수 있다는 점을 동의하지 않는 한.

참고: MySQL은 각 열거형 값을 정수로 저장하고 문자열로 변환하려면 조회를 수행해야 하기 때문에 열거형 열에 약간의 오버헤드가 있습니다.

날짜 및 시간 유형

데이터 유형 및 사용에 대한 자세한 내용은 http://www.php.cn/을 참조하세요.

Mysql에는 저장할 수 있는 유형이 많이 있습니다. YEAR 및 DATE와 같은 날짜 및 시간 값입니다.

Mysql이 저장할 수 있는 최소 시간 단위는 초입니다(MariaDB는 마이크로초 수준의 이벤트 유형을 지원합니다). 그러나 MySQL은 마이크로초 수준의 세분성으로 임시 작업을 수행할 수도 있습니다.

대부분의 경우 유형에 대한 대안이 없기 때문에 무엇이 최선의 선택인지 의문의 여지가 없습니다.

그럼 유일한 질문은 날짜와 시간을 저장할 때 어떻게 해야 하느냐는 것입니다.

DATETIME

(1) 이 타입은 1001부터 9999까지의 넓은 범위의 값을 초 단위의 정밀도로 저장할 수 있습니다. (2) DATETIME은 시간대에 관계없이 시간과 날짜를 YYYYMMDDHHMMSS 형식의 정수로 캡슐화합니다. (3) DATETIME은 8바이트의 저장공간을 사용한다.

TIMESTAMP

(1) TIMESTAMP 유형은 1970년 1월 1일 자정 이후의 초 수를 저장하며 이는 UNIX의 타임스탬프와 동일합니다. (2) TIMESTAMP는 저장 공간을 4바이트만 사용하므로 범위가 DATETIME보다 훨씬 작습니다. (3) TIMESTAMP에 표시되는 값은 시간대에 따라 다릅니다.

DATETIME과 TIMESTAMP 비교:

(1) 기본적으로 삽입 시 첫 번째 TIMESTAMP 열의 값을 지정하지 않으면 Mysql은 이 열의 값을 현재 시간으로 설정합니다. (DATETIME에는 없는 기능입니다.) (2) MySQL은 레코드 행을 삽입할 때 기본적으로 첫 번째 TIMESTAMP 열의 값도 업데이트합니다. (3) TIMESTAMP 컬럼의 기본값은 NOT NULL로 다른 데이터 유형과 다릅니다.

요약

(1) 특별한 동작 외에도 TIMESTAMP는 DATETIME보다 공간 효율적이므로 가능할 때마다 일반적으로 사용해야 합니다. (2) 일반적으로 UNIX 타임스탬프를 정수 값으로 저장하는 것은 권장되지 않습니다. 이는 정수로 타임스탬프 형식을 저장하는 것은 일반적으로 처리하기 불편합니다. (3) 초보다 작은 단위로 날짜 및 시간 값을 저장해야 하는 경우 BIGINT 유형을 사용하여 마이크로초 수준의 타임스탬프를 저장하거나 DOUBLE을 사용하여 초 이후의 소수 부분을 저장하는 방법도 있습니다. MySQL 대신.

비트 데이터 유형

MySQL에는 컴팩트 비트를 사용하여 데이터를 저장하는 몇 가지 저장 유형이 있습니다. 기본 저장 형식 및 처리에 관계없이 이러한 모든 비트 유형은 기술적으로 문자열 유형입니다.

BIT

BIT 열은 하나 이상의 참/거짓 값을 열에 저장하는 데 사용할 수 있습니다. BIT(1)은 단일 비트를 포함하는 필드를 정의하고, BIT(2)는 2비트를 저장하는 식입니다. BIT 열의 최대 길이는 64비트입니다.

참/거짓 값을 약간의 저장 공간에 저장하고 싶다면 null이 될 수 있는 CHAR(0) 열을 생성하는 또 다른 방법이 있습니다. 이 열은 Null 값(NULL) 또는 길이가 0인 문자열(빈 문자열)을 보유할 수 있습니다.

SET

많은 참/거짓 값을 저장해야 하는 경우 이러한 열을 SET 데이터 유형으로 병합하는 것을 고려할 수 있습니다. 이 유형은 MySQL에서 내부적으로 압축된 비트 세트로 표시됩니다. 이는 저장 공간을 효과적으로 활용하며, MySQL에는 쿼리에 쉽게 사용할 수 있도록 FIND_IN_SET(), FIELD()와 같은 기능이 있습니다. 주요 단점은 열 정의를 변경하는 데 비용이 많이 든다는 것입니다. ALTER TABLE이 필요하며 이는 대형 테이블의 경우 매우 비용이 많이 드는 작업입니다. 일반적으로 SET 열의 인덱스를 통한 검색도 불가능합니다.

SET의 대안은 일련의 비트를 정수로 래핑하는 것입니다. 예를 들어, 8비트는 TINYINT로 압축되어 비트 연산에 사용될 수 있습니다. 애플리케이션의 각 비트에 대해 명명된 상수를 정의하여 이를 단순화할 수 있습니다.

SET과 비교할 때 이 방법의 가장 큰 장점은 ALTER TABLE을 사용하지 않고도 필드가 나타내는 "열거" 값을 변경할 수 있다는 것입니다. 단점은 쿼리 문을 작성하기가 더 어렵다는 것입니다. 이해하다(5번째 비트일 때 비트가 설정되었다는 것은 무엇을 의미하는가?). 어떤 사람들은 이 접근 방식에 매우 익숙하고 어떤 사람들은 그렇지 않습니다. 따라서 이 기술을 채택할지 여부는 개인 취향에 달려 있습니다.

식별자 선택

식별자(ID 열)에 적합한 데이터 유형을 선택하는 것이 매우 중요합니다.

일반적으로 다른 값과 비교하기 위해 식별 열을 사용하거나, 식별 열을 통해 다른 열을 찾을 가능성이 높습니다.

ID 열 유형을 선택할 때 저장 유형뿐만 아니라 Mysql이 이 유형에 대해 계산 및 비교를 수행하는 방법도 고려해야 합니다.

유형을 선택한 후에는 모든 관련 테이블에서 동일한 유형을 사용해야 합니다.

값의 범위 요구 사항을 충족할 수 있고 향후 성장 여지가 있다는 전제 하에 가장 작은 데이터 유형을 선택해야 합니다.

• 정수는 빠르고 사용할 수 있기 때문에 일반적으로 ID 열에 가장 적합합니다 AUTO_INCREMENT.

• ENUM 및 SET은 최악의 선택입니다.

• 가능한 경우 문자열을 ID 열로 사용하지 마십시오. 공간을 많이 소모하고 일반적으로 숫자 클래스보다 느립니다.

특수 유형 데이터

일부 유형의 데이터는 내장 유형과 직접적으로 일치하지 않습니다. 1초 미만의 정밀도를 갖는 타임스탬프가 한 예입니다.

또 다른 예는 사람들이 IP 주소를 저장하기 위해 종종 VARCHAR(15)을 사용한다는 것입니다. 그러나 실제로는 문자열이 아닌 32비트 부호 없는 정수입니다. 소수점은 읽기 쉽도록 필드를 4개의 세그먼트로 나누는 데 사용됩니다. 따라서 IP 주소는 부호 없는 정수로 저장되어야 합니다. MySQL은 이 두 가지 표현 방법을 변환하는 INET_ATON() 및 INET_NTOA() 함수를 제공합니다.

최적화된 데이터 유형 선택

MySQL은 다양한 데이터 유형을 지원하며, 올바른 데이터 유형을 선택하는 것은 고성능을 달성하는 데 중요합니다. 저장하는 데이터 유형에 관계없이 더 나은 선택을 하는 데 도움이 되는 몇 가지 원칙은 다음과 같습니다.

일반적으로 작을수록 좋습니다

일반적으로 데이터를 올바르게 저장할 수 있는 가장 작은 데이터 유형을 사용하도록 노력해야 합니다(예를 들어 0-200만 저장하면 되며,tintinint unsigned가 더 좋습니다) ). 데이터 유형이 작을수록 일반적으로 더 적은 디스크, 메모리, CPU 캐시를 차지하고 처리하는 데 더 적은 CPU 주기가 필요하기 때문에 더 빠릅니다.

단순한 것이 좋다

단순 데이터 유형에 대한 작업에는 일반적으로 더 적은 CPU 주기가 필요합니다. 예를 들어, 문자 집합과 대조 규칙으로 인해 문자열 비교가 정수 비교보다 더 복잡해지기 때문에 정수 연산은 문자 연산보다 비용이 저렴합니다. 여기에 두 가지 예가 있습니다. 하나는 날짜와 시간을 저장하기 위해 문자열 대신 MySQL의 내장 유형(예: 날짜, 시간, 날짜/시간)을 사용해야 한다는 것이고, 다른 하나는 IP 주소를 저장하기 위해 정수를 사용해야 한다는 것입니다.

NULL 사용을 피하세요

애플리케이션이 NULL을 저장할 필요가 없더라도 많은 테이블에 NULL 허용 열이 포함되어 있습니다. 왜냐하면 NULL 허용 여부는 열의 기본 속성이기 때문입니다. 실제로 NULL 값을 저장해야 하는 경우가 아니면 일반적으로 열을 NOT NULL로 지정하는 것이 가장 좋습니다.

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM中甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的值改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有的schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建立索引，就应该避免设计成可为NULL的列。

当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（大部分值为NULL，只有少数行为非NULL的值）有良好的空间效率。但这一点不适用于MyISAM。

---

整数类型

如果存储整数，可以使用这几种整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16,24,32,64位存储空间。它们的存储范围从 -2的(N-1)次方 到 2的(N-1)次方-1，其中N为存储空间的位数。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如TINYINT UNSIGNED可以存储的范围是0-255，而TINYINT 的存储范围是-128~127。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：他不会限制值得合法范围，知识规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的，则需要研究所使用的平台的浮点数的具体实现。

DECIMAL 类型用于存储精确的小数。但因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，这比CPU直接支持原生浮点数运算要慢。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以100W，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

下面的描述假设使用的存储引擎是InnoDB/或者MyISAM。如果不是这两种存储引擎的，请参考所使用的存储引擎的文档。

VARCHAR 및 CHAR

VARCHAR: 필요한 공간만 사용하므로 고정 길이 유형보다 공간을 더 절약합니다. VARCHAR은 공간을 절약하므로 성능에도 도움이 됩니다. 그러나 행의 길이가 가변적이므로 UPDATE 중에 행이 원본보다 길어질 수 있으므로 추가 작업이 필요합니다.

다음 상황에서는 VARCHAR을 사용하는 것이 적절합니다. 문자열의 최대 길이가 평균 길이보다 훨씬 길기 때문에 UTF와 같은 복잡한 문자 집합에서는 조각화가 문제가 되지 않습니다. -8을 사용하며, 각 문자마다 다른 바이트 수를 사용합니다.

버전 5.0 이상에서 MySQL은 저장 및 검색 시 후행 공백을 유지합니다. InnoDB는 더 유연합니다. 긴 VARCHAR을 BLOB

CHAR: 고정 길이로 저장할 수 있습니다. CHAR 값을 저장할 때 MySQL은 모든 후행 공백을 삭제합니다. 고정 길이 CHAR 유형은 단편화가 덜 발생하며 매우 짧은 열의 경우 CHAR는 레코드 길이가 1~2바이트 추가되는 VARCHAR보다 저장 공간에서 더 효율적입니다. CHAR는 매우 짧은 문자열을 저장하거나 모든 값이 동일한 길이에 가까울 때 적합합니다. 예: CHAR은 고정 길이 값이기 때문에 비밀번호의 MD5 값을 저장하는 데 매우 적합합니다. CHAR에는 비교를 용이하게 하기 위해 필요에 따라 공백이 채워집니다.

CHAR 및 VARCHAR과 유사한 유형으로는 바이너리 문자열을 저장하는 BINARY 및 VARBINARY가 있습니다. 바이너리 문자열은 문자가 아닌 바이트코드를 저장합니다.

이진 비교의 장점은 대소문자 구분에만 있는 것이 아닙니다. MySQL은 BINARY 문자열을 한 번에 한 바이트씩 비교하고 바이트 값을 기준으로 비교합니다. 따라서 바이너리는 문자보다 훨씬 간단하므로 속도가 더 빠릅니다.

BLOB 및 TEXT 유형

BLOB 및 TEXT 유형: BLOB 및 TEXT는 모두 대용량 데이터를 저장하도록 설계된 문자열 데이터 유형이며 각각 바이너리 및 문자 모드로 저장됩니다. BLOB 및 TEXT 값이 너무 크면 InnoDB는 전용 "외부" 저장 영역을 사용하여 저장합니다. 원래 테이블 필드 저장 포인터는 외부 저장 영역을 가리킵니다.

MySQL은 BLOB 및 TEXT 열을 다른 유형과 다르게 정렬합니다. 전체 문자열 대신 열의 첫 번째 max_sort_length 바이트만 정렬합니다. 첫 번째 문자의 작은 부분만 정렬해야 하는 경우 max_sort_length 구성을 줄이거나 ORDER BY SUSTRING(column, length)을 사용할 수 있습니다.

MySQL은 BLOB 및 TEXT 열의 전체 길이 문자열을 색인화할 수 없으며 이러한 색인을 사용하여 정렬을 제거할 수도 없습니다.

문자열 형식 대신 열거형(ENUM)을 사용하세요

문자열 형식 대신 열거형(ENUM)을 사용할 수 있습니다. 일반적으로 사용되는 문자열 유형 대신 열거형 열을 사용하는 것이 권장되는 경우가 많습니다.

(1) 열거형 열은 일부 고유 문자열을 사전 정의된 컬렉션에 저장할 수 있습니다.
(2) Mysql은 열거형을 저장할 때 매우 컴팩트하며 목록 값의 수에 따라 1바이트 또는 2바이트로 압축됩니다.
(3) MySQL은 내부적으로 목록의 각 값의 위치를 ​​정수로 저장하고, "숫자-문자열" 매핑 관계의 "조회 테이블"을 테이블의 .frm 파일에 저장합니다.

참고: 한 가지 놀라운 점은 열거형 필드가 정의된 문자열이 아닌 내부에 저장된 정수를 기준으로 정렬된다는 것입니다.

참고: 열거형의 가장 나쁜 점은 문자열 목록이 고정되어 있다는 것입니다. 문자열을 추가하거나 삭제하려면 ALTER TABLE을 사용해야 하므로 나중에 변경될 수 있는 일련의 문자열에 대해서는 열거형을 사용하고 좋지 않습니다. 목록 끝에만 요소를 추가할 수 있다는 점을 동의하지 않는 한.

참고: MySQL은 각 열거형 값을 정수로 저장하고 문자열로 변환하려면 조회를 수행해야 하기 때문에 열거형 열에 약간의 오버헤드가 있습니다.

날짜 및 시간 유형

데이터 유형 및 사용에 대한 자세한 내용은 http://www.php.cn/을 참조하세요.

Mysql에는 저장할 수 있는 유형이 많이 있습니다. YEAR 및 DATE와 같은 날짜 및 시간 값입니다.

Mysql이 저장할 수 있는 최소 시간 단위는 초입니다(MariaDB는 마이크로초 수준의 이벤트 유형을 지원합니다). 그러나 MySQL은 마이크로초 수준의 세분성으로 임시 작업을 수행할 수도 있습니다.

대부분의 경우 유형에 대한 대안이 없기 때문에 무엇이 최선의 선택인지 의문의 여지가 없습니다.

그럼 유일한 질문은 날짜와 시간을 저장할 때 어떻게 해야 하느냐는 것입니다.

DATETIME

(1) 이 타입은 1001부터 9999까지의 넓은 범위의 값을 초 단위의 정밀도로 저장할 수 있습니다. (2) DATETIME은 시간대에 관계없이 시간과 날짜를 YYYYMMDDHHMMSS 형식의 정수로 캡슐화합니다. (3) DATETIME은 8바이트의 저장공간을 사용한다.

TIMESTAMP

(1) TIMESTAMP 유형은 1970년 1월 1일 자정 이후의 초 수를 저장하며 이는 UNIX의 타임스탬프와 동일합니다. (2) TIMESTAMP는 저장 공간을 4바이트만 사용하므로 범위가 DATETIME보다 훨씬 작습니다. (3) TIMESTAMP에 표시되는 값은 시간대에 따라 다릅니다.

DATETIME과 TIMESTAMP 비교:

(1) 기본적으로 삽입 시 첫 번째 TIMESTAMP 열의 값을 지정하지 않으면 Mysql은 이 열의 값을 현재 시간으로 설정합니다. (DATETIME에는 없는 기능입니다.) (2) MySQL은 레코드 행을 삽입할 때 기본적으로 첫 번째 TIMESTAMP 열의 값도 업데이트합니다. (3) TIMESTAMP 컬럼의 기본값은 NOT NULL로 다른 데이터 유형과 다릅니다.

요약

（1）除了特殊行为之外，通常也应该尽可能使用TIMESTAMP，因为它比DATETIME空间效率更高。 （2）一般来讲不建议把UNIX时间戳保存为整数值，这不会带来任何收益，用整数保存时间戳格式通常不方便处理。 （3）如果需呀存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分，也可以用MariaDB替代Mysql。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型的。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义了一个包含单个位的字段，BIT(2)存储2个位，依次类推。BIT列的最大长度是64位。

如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

많은 참/거짓 값을 저장해야 하는 경우 이러한 열을 SET 데이터 유형으로 병합하는 것을 고려하십시오. 이는 MySQL 내부적으로 압축된 비트 세트로 표시됩니다. 이는 저장 공간을 효과적으로 활용하며, MySQL에는 쿼리에 쉽게 사용할 수 있도록 FIND_IN_SET(), FIELD()와 같은 기능이 있습니다. 주요 단점은 열 정의를 변경하는 데 비용이 많이 든다는 것입니다. ALTER TABLE이 필요하며 이는 대형 테이블의 경우 매우 비용이 많이 드는 작업입니다. 일반적으로 SET 열의 인덱스를 통한 검색도 불가능합니다.

SET의 대안은 일련의 비트를 정수로 래핑하는 것입니다. 예를 들어, 8비트는 TINYINT로 압축되어 비트 연산에 사용될 수 있습니다. 애플리케이션의 각 비트에 대해 명명된 상수를 정의하여 이를 단순화할 수 있습니다.

SET과 비교할 때 이 방법의 가장 큰 장점은 ALTER TABLE을 사용하지 않고도 필드가 나타내는 "열거" 값을 변경할 수 있다는 것입니다. 단점은 쿼리 문을 작성하기가 더 어렵다는 것입니다. 이해하다(5번째 비트일 때 비트가 설정되었다는 것은 무엇을 의미하는가?). 어떤 사람들은 이 접근 방식에 매우 익숙하고 어떤 사람들은 그렇지 않습니다. 따라서 이 기술을 채택할지 여부는 개인 취향에 달려 있습니다.

식별자 선택

식별자(ID 열)에 적합한 데이터 유형을 선택하는 것이 매우 중요합니다.

일반적으로 다른 값과 비교하기 위해 식별 열을 사용하거나, 식별 열을 통해 다른 열을 찾을 가능성이 높습니다.

ID 열 유형을 선택할 때 저장 유형뿐만 아니라 Mysql이 이 유형에 대해 계산 및 비교를 수행하는 방법도 고려해야 합니다.

유형을 선택한 후에는 모든 관련 테이블에서 동일한 유형을 사용해야 합니다.

값의 범위 요구 사항을 충족할 수 있고 향후 성장 여지가 있다는 전제 하에 가장 작은 데이터 유형을 선택해야 합니다.

• 정수는 빠르고 사용할 수 있기 때문에 일반적으로 ID 열에 가장 적합합니다 AUTO_INCREMENT.

• ENUM 및 SET은 최악의 선택입니다.

• 가능한 경우 문자열을 ID 열로 사용하지 마십시오. 공간을 많이 소모하고 일반적으로 숫자 클래스보다 느립니다.

특수 유형 데이터

일부 유형의 데이터는 내장 유형과 직접적으로 일치하지 않습니다. 1초 미만의 정밀도를 갖는 타임스탬프가 한 예입니다.

또 다른 예는 사람들이 IP 주소를 저장하기 위해 종종 VARCHAR(15)을 사용한다는 것입니다. 그러나 실제로는 문자열이 아닌 32비트 부호 없는 정수입니다. 소수점은 읽기 쉽도록 필드를 4개의 세그먼트로 나누는 데 사용됩니다. 따라서 IP 주소는 부호 없는 정수로 저장되어야 합니다. MySQL은 이 두 가지 표현 방법을 변환하는 INET_ATON() 및 INET_NTOA() 함수를 제공합니다.

위 내용은 [MySQL] MySQL 데이터형 최적화 내용입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!