고성능 MySql의 진화(1): 데이터 유형의 최적화_1부

데이터베이스 성능 튜닝 과정에는 필드 속성 설정이 적절한지, 인덱스 설정이 적절한지, 테이블 구조가 적절한지, 데이터베이스/운영체제 설정이 올바른지 등 많은 지식이 필요합니다. .. 각 주제는 필드일 수 있습니다.

제 생각에는 데이터베이스 성능 향상을 위한 핵심 기술 중 필드 최적화가 상대적으로 어렵고 성능에 미치는 영향이 매우 크다고 생각합니다. MySQL은 다양한 데이터 유형을 지원하고 각 유형마다 고유한 특성이 있기 때문에 특정 데이터 유형을 선택할 때 유형이 최적인지 여부를 고려하지 않고 임의로 사용 가능한 유형을 선택하는 경우가 많습니다. 특정 유형을 설명하기 전에 먼저 데이터 유형을 선택하는 몇 가지 주요 원칙을 살펴보겠습니다.

a) 작은 유형은 디스크나 메모리에 있으므로 공간을 덜 차지하는 유형을 선택하세요.
공간 차지하는 공간은 작으며 쿼리 또는 정렬을 위해 임시 테이블에 필요한 공간도 상대적으로 작습니다. 데이터의 양이 상대적으로 적을 때는 느끼지 못할 수도 있지만, 데이터의 양이 상대적으로 많을 때 이 원리의 중요성이 분명해질 수 있습니다.

예를 들어, 2천만 개의 레코드가 있는 "제품 정보" 테이블이 있습니다. 이 테이블에는 일반적으로 SMALLINT(len :16) 필드가 있습니다. range:0-65535)이면 이 필드를 표현하기에 충분하지만 설계 과정에서 BIGINT(len:64 범위:0-18446744073709551615)를 사용하여 표현하면 프로그램이 제대로 실행될 수는 있지만 이 필드에 약 95M이 추가됩니다. 추가 디스크 저장 공간(64-16)/8*20,000,000바이트) 또한 데이터 선택 및 정렬을 수행할 때 위 동작의 영향을 기준으로 이 필드만 메모리 소비를 95M 증가시킵니다. 데이터베이스는 필연적으로 영향을 받습니다

가능한 한 작게 유지하는 전제는 선택하려는 유형이 향후 비즈니스 개발 요구를 충족할 수 있는지 확인하는 것입니다. 데이터의 양이 상대적으로 많기 때문에 속도가 매우 느리고 번거로운 일입니다.

b) 단순/적절한 유형을 선택해 보세요

테이블을 선택하고 정렬할 때 단순 유형은 CPU 클럭 주기를 덜 소모하는 경우가 많습니다. 예를 들어 MySql 서버의 경우 정수형 값의 비교가 문자열형 값의 비교보다 간단하고 빠른 경우가 많기 때문에 특정 테이블을 정렬해야 하는 경우에는 정수형을 정렬 기준으로 선택하는 것이 좋습니다.

c) 필드를 NOTNULL로 설정해 보세요.
일반적으로 필드를 명시적으로 NULL로 지정하지 않으면 데이터베이스 시스템에서 이 필드를 NULLABLE로 간주합니다. 다음 세 가지 문제가 발생합니다
(1) Mysql 서버 자체 쿼리 최적화 기능에 영향을 미칩니다
(2) Mysql에는 추가 저장 공간이 필요하고 null 값 필드에 대한 처리가 필요합니다
(3 ) null 값이 일부인 경우 인덱스의 경우 인덱스 효과도 영향을 받습니다

이 원칙은 데이터베이스 성능 향상에 큰 영향을 미치지 않기 때문에 기존 DB 스키마에는 NULLABLE 필드가 있거나 인덱스가 NULLABLE인 경우가 있습니다. 특별히 수정할 필요는 없으나 새로 디자인한 DB나 인덱스의 경우에는 이 원칙을 최대한 준수해야 한다.

데이터 타입 선택의 원칙을 소개한 후, MySQL에서 흔히 사용되는 데이터 타입과 성능 최적화 측면에서 주의해야 할 사항을 소개합니다.

· 정수
Mysql의 정수 계열에는 주로 TINYINT(8bit), SMALLINT(16bit), MEDIUMINT(24bit), INT(32bit) 또는 BIGINT(64bit)가 있습니다.

부호 있는 정수의 경우 이러한 유형의 저장 범위는 (-2(n-1), 2(n-1)-1)이고, 부호 없는 숫자의 경우 표현 범위는 (0, 2n- 1) 데이터베이스의 경우 부호 있는 숫자와 부호 없는 숫자가 동일한 저장 공간을 차지하므로 유형 선택 시 부호 있는 숫자인지 부호 없는 숫자인지 고려하지 않고 숫자의 범위만 고려하면 됩니다.

Mysql에서는 허용합니다. INT(10)과 같은 정수 유형을 정의할 때 너비를 지정합니다. Client/CMD Line에 대해서는 INT(10)의 출력에 차이가 있지만, Mysql Server 관점에서 보면 실제 저장공간/연산소모/사용면에서 INT(10)와 INT(32)의 차이는 없다. 숫자 범위.

· Decimal
Mysql에서 십진 계열의 데이터 유형은 주로 FLOAT(4Bytes), DOUBLE(8Bytes)이 있으며 이 두 가지 유형의 저장 공간에서 접근이 가능함을 알 수 있다. 소수는 정수보다 비용이 더 많이 듭니다. 따라서 필요한 경우가 아니면 소수 유형을 사용하지 않는 것이 좋습니다.

십진 유형 필드를 생성할 때 FLOAT(10,3)을 사용하여 정밀도를 지정할 수 있습니다. 소수점, >= MySQL 5.0 버전의 최대 정밀도는 소수점 이하 65자리를 지원합니다.

데이터베이스는 소수점 이하의 숫자를 저장하기 위해 이진 배열 문자열을 사용하므로 요구되는 정밀도가 높을수록 저장 공간/계산 CPU 클럭이 더 많이 소모될 수 있습니다.

소수를 사용하면 저장 공간과 CPU 리소스가 더 많이 소모될 수 있으며 초기 MySQL 버전의 경우 계산에 소수 두 자리가 포함되면 정밀도가 손실되지만 예를 들어 금융 분야에서는 필요한 경우가 많습니다. 금액 저장. 많은 경우 저장 오버헤드를 줄이고 정확성을 보장하기 위해 소수는 정수로 확장되어 데이터베이스에 저장되는 경우가 많습니다. 그리고 소수는 애플리케이션에서 변환되어 계산됩니다. 예를 들어 사용자의 계정 잔액은 999.35위안으로 유지됩니다. 데이터에 저장된 금액은 99935포인트입니다. 은행의 처리 프로그램이 99935포인트를 얻은 후 먼저 999.35위안으로 변환한 다음 해당 처리를 수행합니다.

·문자열

어떤 언어이든 문자열은 상대적으로 중요하고 복잡한 유형입니다. 이 규칙은 MYSQL에도 적용됩니다.
MYSQL에는 VARCHAR과 CHAR라는 두 가지 주요 문자열 유형이 있습니다. 디스크와 메모리의 크기는 스토리지 엔진에 의해 결정되며, 스토리지 엔진마다 저장 방법이 다를 수 있습니다. 일반적으로 스토리지 엔진의 경우 디스크와 메모리의 저장 방식도 다릅니다. 디스크와 메모리 간에 데이터가 전송될 때 스토리지 엔진은
VARCHAR
우선 데이터를 변환하는 역할을 담당하게 됩니다. Mysql은 가변 길이를 사용하여 VARCHAR을 저장한다는 점을 지적할 필요가 있습니다. 고정 길이에 비해 이 방법은 "필요한 만큼 사용"하는 저장 공간 전략을 채택하므로 상대적으로 공간 절약형 저장 솔루션을 사용할 수 있습니다. 특별한 요구 사항 없이 기본 유형으로

VARCHAR이 고정 길이를 구현할 수 있는 이유는 예를 들어 " "를 저장해야 할 때 각 VARCHAR 값에 1~2byte의 길이 표시자가 추가되기 때문입니다. Java", 기본 스토리지 콘텐츠는 "11I Love Java"이며, 여기서 11(1바이트)은 길이를 나타냅니다. 저장할 콘텐츠의 길이가 1000인 경우 길이 표시에는 2바이트가 필요합니다. 2바이트의 최대값은 216이므로, 저장된 문자열이 이 길이를 초과하면 예측할 수 없는 예외가 발생하게 됩니다. 이 경우 매우 긴 문자열을 저장하려면 CLOB를 사용해야 합니다.

다른 버전의 MYSQL에서는 VARCHAR 필드의 후행 공백 처리도 다릅니다.
Version>=5.0 후행 공백 유지
Version
을 MYSQL로 유지하세요. 5.6은 예입니다.

'hello'를 저장하기 위해 VARCHAR(5) 및 VARCHAR(200)을 사용하는 데 따른 공간 오버헤드는 같은. 그렇다면 더 짧은 열을 사용하면 어떤 이점이 있습니까?

큰 장점으로 작용합니다. MySQL은 일반적으로 내부 값을 보관하기 위해 고정 크기의 메모리 블록을 할당하기 때문에 열이 클수록 더 많은 메모리를 사용합니다. 이는 정렬이나 작업을 위해 메모리 내 임시 테이블을 사용할 때 특히 나쁩니다. 디스크 임시 테이블을 사용하여 정렬할 때도 마찬가지로 나쁩니다.

그래서 가장 좋은 전략은 꼭 필요한 공간만 할당하는 것입니다.

CHAR
CHAR 유형과 VARCHAR 유형의 가장 큰 차이점은 고정 길이라는 점입니다. 동시에 VARCHAR과 비교하면 주로 다음과 같은 특징을 가지고 있습니다
1) 모든 MYSQL 버전에서는 후행 공백이 잘립니다

2) 일부의 경우 MD5, ID 번호와 같이 짧고 기본적으로 길이가 동일한 필드는 좋은 선택입니다
3) 자주 변경해야 하는 필드의 경우 CHAR 유형이 더 효율적입니다
4) 일부 매우 짧은 필드의 경우 필드에서도 매우 공간 절약적입니다. 예를 들어 "Y"나 "N"을 저장하면 CHAR를 사용하면 1바이트만 필요하고, VARCHAR을 사용하면 2바이트(길이 1바이트+값 1바이트)가 필요합니다.

고정 길이 CHAR의 경우, Mysql 서버 정의된 길이에 따라 공간을 채워 충분한 저장 공간을 할당합니다. 한 가지 주의할 점은 VARCHAR/CHAR에 대한 "공백 채우기" 및 "후행 공백 제거" 작업은 Mysql 서버에 의해 구현되며 스토리지 엔진과는 아무런 관련이 없다는 것입니다.

위는 진화론입니다. 고성능 MySql의 (1) :데이터 유형 최적화_내용에 관한 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!