MySQL 고성능 테이블을 설계하는 방법

훌륭한 논리적 설계와 물리적 설계는 고성능의 초석입니다. 시스템이 실행할 쿼리문에 따라 스키마를 설계해야 하며, 이는 종종 다양한 요소를 고려해야 합니다.

1. 최적화된 데이터 유형을 선택하세요

MySQL은 다양한 데이터 유형을 지원합니다. 올바른 데이터 유형을 선택하는 것은 고성능을 달성하는 데 중요합니다.

일반적으로 작은 것이 더 좋습니다

데이터 유형이 작을수록 디스크, 메모리, CPU 캐시를 덜 차지하고 처리하는 데 필요한 CPU 주기가 더 적기 때문에 일반적으로 속도가 더 빠릅니다.

단순하게 유지하세요

단순 데이터 유형에 대한 작업에는 일반적으로 더 적은 CPU 주기가 필요합니다. 예를 들어, 문자 집합과 대조 규칙(조합)으로 인해 문자 비교가 정수 비교보다 더 복잡해지기 때문에 정수 연산은 문자 연산보다 저렴합니다.

NULL을 피하세요

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

물론 예외도 있습니다. 예를 들어 InnoDB는 NULL 값을 저장하기 위해 별도의 비트를 사용하므로 희소 데이터에 대한 공간 효율성이 좋습니다.

1. 정수형

숫자에는 정수와 실수의 두 가지 유형이 있습니다. 정수를 저장하는 경우 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT와 같은 정수 유형을 사용할 수 있습니다. 각각 8, 16, 24, 32, 64비트 저장 공간을 사용하세요.

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

Signed 타입과 unsigned 타입은 동일한 저장공간을 사용하고 동일한 성능을 가지므로 실제 상황에 따라 적절한 타입을 선택하시면 됩니다.

귀하의 선택에 따라 MySQL이 메모리와 디스크에 데이터를 저장하는 방법이 결정됩니다. 그러나 정수 계산에서는 32비트 환경에서도 일반적으로 64비트 BIGINT 정수를 사용합니다. (예외는 계산에 DECIMAL 또는 DOUBLE을 사용하는 일부 집계 함수입니다.)

MySQL은 INT(11)와 같은 정수 유형의 너비를 지정할 수 있습니다. 이는 대부분의 응용 프로그램에서는 의미가 없습니다. 이는 값의 합법적인 범위를 제한하지 않고 MySQL의 일부 대화형 도구(예: MySQL 명령줄 클라이언트)만 지정합니다. ) 문자 수를 표시하는데 사용됩니다. 저장 및 계산 목적에서는 INT(1)과 INT(20)이 동일합니다.

2.실수형

실수는 소수 부분이 있는 숫자입니다. 그러나 소수 부분만 저장하는 것이 아니라 DECIMAL을 사용하여 BIGINT보다 큰 정수를 저장할 수도 있습니다.

FLOAT 및 DOUBLE 유형은 표준 부동 소수점 연산을 사용한 대략적인 계산을 지원합니다.

DECIMAL 유형은 정확한 소수점을 저장하는 데 사용됩니다.

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

부동 소수점 열에 필요한 정밀도를 지정하는 방법은 다양하며, 이로 인해 MySQL이 다른 데이터 유형을 선택하거나 저장할 때 값을 반올림하게 됩니다. 이러한 정밀도 정의는 비표준이므로 정밀도가 아닌 데이터 유형만 지정하는 것이 좋습니다.

부동 소수점 유형은 일반적으로 동일한 범위에 값을 저장할 때 DECIMAL보다 적은 공간을 사용합니다. FLOAT는 4바이트의 저장 공간을 사용합니다. DOUBLE은 8바이트를 차지하며 FLOAT보다 정밀도가 높고 범위가 넓습니다. 정수 유형과 마찬가지로 선택할 수 있는 것은 저장 유형뿐입니다. MySQL은 내부 부동 소수점 계산을 위한 유형으로 DOUBLE을 사용합니다.

추가 공간과 계산 오버헤드가 필요하므로 소수에 대한 정확한 계산을 수행할 때는 DECIMAL만 사용하도록 노력해야 합니다. 하지만 데이터가 비교적 큰 경우 DECIMAL 대신 BIGINT를 사용하는 것을 고려해 볼 수 있습니다. 저장할 통화 단위에 소수 자릿수에 따라 해당 배수를 곱하면 됩니다.

3. 문자열 유형

VARCHAR

• 변수 문자열을 저장하는데 사용되며, 길이는 65535까지 지원됩니다
• 문자열 길이를 기록하려면 1~2바이트의 추가 바이트가 필요합니다
• 적합 대상: 문자열의 최대 길이가 평균 길이보다 훨씬 깁니다. 업데이트는 거의 없습니다.

CHAR

고정 길이, 길이 범위는 1~255
적합: 매우 짧은 문자열을 저장하거나 자주 변경되는 동일한 길이에 가까운 모든 값을 저장합니다.

관대함은 현명하지 못하다

VARCHAR(5)와 VARCHAR(200)을 사용하여 'hello'를 저장하는 공간 오버헤드는 동일합니다. 그렇다면 더 짧은 열을 사용하면 어떤 이점이 있습니까?

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

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

4.BLOB 및 TEXT 유형

BLOB 및 TEXT는 모두 대용량 데이터를 저장하도록 설계된 문자열 데이터 유형이며 각각 바이너리 및 문자 모드로 저장됩니다.

다른 유형과 달리 MySQL은 각 BLOB 및 TEXT 값을 독립적인 개체로 처리합니다. 스토리지 엔진은 일반적으로 저장할 때 특수 처리를 수행합니다. BLOB 및 TEXT 값이 너무 크면 InnoDB는 저장을 위해 전용 "외부" 저장 영역을 사용합니다. 이때 각 값은 행에 저장되기 위해 1~4바이트가 필요합니다. .

BLOB와 TEXT의 유일한 차이점은 BLOB 유형은 이진 데이터를 저장하고 대조나 문자 집합이 없는 반면, TEXT 유형은 문자 집합과 대조가 있다는 것입니다

5.날짜 및 시간 유형

대부분의 경우 유형에 대한 대안이 없으므로 무엇이 최선의 선택인지 의문의 여지가 없습니다. 유일한 문제는 날짜와 시간을 저장할 때 무엇을 해야 하느냐는 것입니다. MySQL은 DATE TIME과 TIMESTAMP라는 두 가지 유사한 날짜 유형을 제공합니다.

하지만 현재는 타임스탬프를 저장하는 방식을 선호하므로 여기서는 DATE TIME과 TIMESTAMP에 대해 자세히 설명하지 않겠습니다.

6.기타 유형

6.1 식별자 선택

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

• 정수형

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

• ENUM 및 SET 유형

EMUM 및 SET 유형은 고정된 상태나 유형만 포함하는 일부 정적 "정의 테이블"에는 적합할 수 있지만 일반적으로 ID 열에는 적합하지 않습니다. ENUM, SET 컬럼은 주문현황, 제품종류, 성별 등 고정정보를 저장하는데 적합하다.

• 문자열 유형

가능한 경우 문자열 유형은 공간을 많이 소비하고 일반적으로 숫자 유형보다 느리기 때문에 ID 열로 사용하지 않는 것이 좋습니다.

MDS(), SHAl() 또는 UUID()에 의해 생성된 문자열과 같이 완전히 "임의" 문자열에 더 많은 주의를 기울여야 합니다. 이러한 함수에 의해 생성된 새로운 값은 넓은 공간에 임의로 분산되므로 INSERT 및 일부 SELECT 문이 매우 느려질 수 있습니다. UUID 값이 저장되어 있는 경우 "-" 기호를 제거해야 합니다.

6.2 특수 유형 데이터

일부 유형의 데이터 웰은 기본 제공 유형과 직접적으로 일치하지 않습니다. 킬로초 정밀도가 낮은 타임스탬프가 한 예입니다. 또 다른 예는 1Pv4 주소입니다. 사람들은 종종 VARCHAR(15) 열을 사용하여 IP 주소를 저장합니다. 그러나 실제로는 문자열이 아닌 32비트 부호 없는 정수입니다. 소수점을 사용하여 주소를 4개 부분으로 나누어 표현한 것은 사람들이 읽기 쉽도록 하기 위한 것입니다. 따라서 IP 주소는 부호 없는 정수로 저장되어야 합니다. MySQL은 이 두 가지 표현 방법 간 변환을 위해 INET_ATON() 및 INET_NTOA() 함수를 제공합니다.

2. 테이블 구조 디자인

1. 패러다임과 안티 패러다임

완전 정규화된 것부터 완전히 비정규화된 것까지, 그리고 둘 사이의 절충안까지 주어진 데이터를 표현하는 방법에는 일반적으로 여러 가지가 있습니다. 정규화된 데이터베이스에서는 각 사실이 정확히 한 번 나타납니다. 대조적으로, 비정규화된 데이터베이스에서는 정보가 중복되어 여러 위치에 저장될 수 있습니다.

패러다임의 장점과 단점

성능 향상을 고려할 때 특히 쓰기 집약적인 시나리오에서는 스키마 디자인을 정규화하는 것이 권장되는 경우가 많습니다.

• 정규화된 업데이트 작업은 일반적으로 비정규화된 업데이트 작업보다 빠릅니다.
• 데이터가 잘 정규화되면 중복된 데이터가 거의 또는 전혀 없으므로 수정해야 하는 데이터가 줄어듭니다.
• 정규화된 테이블은 일반적으로 더 작고 메모리에 더 적합하므로 작업이 더 빠르게 수행됩니다.
• 중복 데이터가 적다는 것은 목록 데이터를 검색할 때 필요한 DISTINCT 또는 GROUP BY 문이 적다는 것을 의미합니다.

안티 패러다임의 장점과 단점

관련 테이블이 필요하지 않으므로 테이블이 인덱스를 사용하지 않더라도 대부분의 쿼리에 대한 최악의 시나리오는 전체 테이블 스캔입니다. 이는 임의 I/0이 방지되므로 데이터가 메모리보다 큰 경우 연관보다 훨씬 빠를 수 있습니다.

개별 테이블에서도 보다 효율적인 인덱싱 전략을 사용할 수 있습니다.

정규화와 비정규화 혼합

실제 애플리케이션에서는 혼합이 필요한 경우가 많으며 부분적으로 정규화된 스키마, 캐시 테이블 및 기타 기술이 사용될 수 있습니다.

성능 우선순위와 같은 중복 필드를 테이블에 적절하게 추가하지만 복잡성이 증가합니다. 테이블 조인 쿼리를 피할 수 있습니다.

데이터베이스 패러다임에 쉽고 친숙함

<br> 첫 번째 정규형(1NF): 필드 값은 원자적이며 분할될 수 없습니다(모든 관계형 데이터베이스 시스템은 첫 번째 정규형을 충족함).<br> 예: 이름 필드에서 성과 이름이 전체인 경우 성과 이름이 구별되는 경우 두 개의 독립적인 필드를 설정해야 합니다.

두 번째 정규형(2NF): 테이블에는 기본 키가 있어야 합니다. 즉, 각 데이터 행을 고유하게 구별할 수 있습니다.
참고: 첫 번째 정규 형식이 먼저 충족되어야 합니다.

제3정규형(3NF): 테이블은 다른 관련 테이블의 키가 아닌 필드에 대한 정보를 포함할 수 없습니다. 즉, 데이터 테이블은 중복 필드를 가질 수 없습니다.
참고: 두 번째 정규형이 먼저 충족되어야 합니다.

2. 테이블 필드가 덜 정교해졌습니다.

• I/O 효율성
• 필드를 쉽게 분리하고 유지 관리할 수 있습니다
• 싱글 테이블 1G 볼륨 500W 행 평가
• 한 줄은 200Byte를 초과할 수 없습니다
• 단일 테이블에 INT 필드는 50개 이하입니다
• 단일 테이블에는 20개 이상의 CHAR(10) 필드를 사용할 수 없습니다
• 한 테이블의 필드 수는 20개 이내로 조절하는 것이 좋습니다
• TEXT/BLOB 분할, TEXT 유형의 처리 성능은 VARCHAR보다 훨씬 낮으므로 하드 디스크 임시 테이블 생성으로 인해 더 많은 공간이 낭비됩니다.

위 내용은 MySQL 고성능 테이블을 설계하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!