>데이터 베이스 >MySQL 튜토리얼 >MySQL 데이터 행 오버플로에 대한 심층적인 이해

MySQL 데이터 행 오버플로에 대한 심층적인 이해

不言
不言앞으로
2018-12-30 09:29:305995검색

이 기사는 MySQL 데이터 행 오버플로에 대한 심층적인 이해를 제공합니다. 이는 특정 참조 가치가 있습니다. 도움이 필요한 친구가 도움이 되기를 바랍니다.

1. 일반적인 오류부터 시작해 보겠습니다.

일반적인 SQL 오류 메시지부터 시작하겠습니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

특히 OMG와 같은 BG의 경우 이러한 유형의 오류가 여러 번 발생했을 것입니다. 콘텐츠 생산이 주요 핵심이기 때문에 콘텐츠 라인의 저장에서 대용량 데이터는 피할 수 없는 주제입니다. 여기서 데이터의 "크기"는 차지하는 큰 저장 공간을 훨씬 뛰어넘는 단일(테이블) 필드의 큰 저장 공간, 긴 데이터 보존 시간, 큰 데이터 중복성, 불일치로 인한 대용량도 포함됩니다. 핫스팟에 따라 피크값이 크게 변화하고, 복잡한 논리적 처리로 인해 데이터 저장 부담이 증폭되는 등의 문제가 발생합니다. 오류 보고 문제로 돌아가서 먼저 이 테이블의 구조를 살펴보겠습니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

이를 보면 모든 사람이 이를 처리하는 방법이 다를 것이라고 생각하므로 장단점에 대해서는 논의하지 않겠습니다. 여기에서는 비교를 위해 더 자주 사용되는 두 가지 처리 방법만 설명합니다.

  • 오류 지침에 따라 두 개의 큰 varchar(22288)를 text와 blob으로 변경합니다

  • 업무 특성에 따라 varchar의 저장 길이를 줄이거나 여러 개의 작은 vachar와 char로 분할합니다. 규칙

이 두 가지 처리 방법에도 각각의 장점과 단점이 있습니다. 필드를 텍스트 또는 blob으로 변경하면 데이터 저장 용량이 늘어날 뿐만 아니라 이 필드의 인덱스 페이지는 접두사 또는 전체 텍스트 인덱스만 사용할 수 있습니다. 비즈니스 측에서 데이터를 json 형식으로 저장하는 경우 5.7이 단일 하위 클래스에 대해 쿼리하고 출력할 수 있는 json 데이터 유형을 지원하는 것이 좋습니다. 마찬가지로 축소 및 분할하는 경우 비즈니스 시나리오 및 논리적 요구 사항에 따라 더 많이 달라집니다. 비즈니스에서 사용하는 논리를 수정해야 하며 엔지니어링 볼륨도 평가해야 합니다.

2. 심층 탐구

그럼 제한 크기 “65535”에 대해 혼란스러운 몇 가지 개념을 심층적으로 분석해 보겠습니다.

1. "65535"는 단일 varchar(N)의 최대 제한이 아니라 전체 테이블의 비대형 필드 유형 필드의 총 바이트 수입니다.

-------------------------------------------------- --- ---------------------------------- ---

모든 테이블(스토리지 엔진에 관계없이)의 최대 행 크기는 65,535바이트입니다. 스토리지 엔진은 이 제한에 추가 제약을 적용하여 효과적인 최대 행 크기를 줄일 수 있습니다.

--------- ------- ----------------- ------- -------------

2. 다른 문자 세트는 최대 저장 용량에 영향을 미칩니다. 예를 들어 UTF8 문자에는 3바이트의 저장 공간이 필요하고 VARCHAR(255) CHARACTER SET UTF8 열의 경우 255×3 =765바이트를 차지합니다. 따라서 테이블에는 65,535/765=85개 이상의 열을 포함할 수 없습니다. GBK는 더블바이트 등입니다.

3. 가변 길이 열의 경우 필드 크기를 평가할 때 열의 실제 길이를 저장하는 바이트 수도 고려해야 합니다. 예를 들어 VARCHAR (255) CHARACTER SET UTF8 열의 경우 값 길이 정보를 저장하려면 추가로 2바이트가 필요하므로 해당 열은 실제로 최대 65533바이트까지 저장할 수 있으며 나머지 2바이트도 저장할 수 있습니다. 바이트는 길이 정보를 저장합니다.

4. BLOB, TEXT 및 JSON 열은 varchar 및 char과 같은 필드와 다릅니다. 열 길이 정보는 행 길이와 독립적으로 저장되며 65535바이트에 도달할 수 있습니다.

5. 허용되는 최대 열 수입니다.

  • InnoDB 테이블, NULL 및 NOT NULL 열 저장 크기는 동일합니다.

  • MyISAM 테이블, NULL 열은 해당 값이 NULL인지 여부를 기록하기 위해 추가 공간이 필요합니다. 각 NULL에는 하나의 추가 비트가 필요합니다(가장 가까운 바이트로 반올림됨). 최대 행 길이는 다음과 같이 계산됩니다.

행 길이 = 1 + (열 길이의 합) + (NULL 열 수 + delete_flag + 7)/8 + (가변 길이 열 수)

  • 정적 테이블, delete_flag = 1, 정적 테이블은 행의 비트를 기록하여 행이 삭제되었는지 여부를 식별합니다.

  • 동적 테이블, delete_flag = 0, 플래그는 동적 행의 시작 부분에 저장됩니다. 동적 테이블은

6에 따라 지정할 수 있습니다. InnoDB 테이블의 경우 NULL 및 NOT NULL 열의 저장 크기입니다. 동일합니다

7. InnoDB는 단일 테이블을 허용합니다. 테이블은 최대 1000개의 열을 가질 수 있습니다

8. varchar 기본 키는 767바이트 또는 768/2=384 더블 바이트 또는 767/3=255 3바이트만 지원합니다. GBK는 2바이트이고 UTF8은 3바이트입니다

9. 엔진마다 인덱스에 대한 제한이 다릅니다

  • innodb 각 열의 길이는 모든 인덱스 길이의 합보다 클 수 없습니다. 열은 3072바이트를 초과할 수 없습니다

  • myisam per 각 열의 길이는 1000바이트를 초과할 수 없으며 모든 인덱스 열의 길이의 합은 1000바이트를 초과할 수 없습니다

3. 실제 오류

온라인 업계에서 다음과 같은 오류가 많이 발생하여 프로그램에서 데이터를 쓸 수 없게 되었습니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

프롬프트 및 정상 이것을 생각하면 우리의 첫 번째 반응은 비즈니스에 다음과 같은 문제가 있다는 것입니다.

  • 설정된 테이블 구조의 필드가 제한을 초과했습니다.

  • 특정 필드에 삽입된 데이터의 길이가 최대값을 초과했습니다. 변경된 필드에 설정

그런 다음 비즈니스의 데이터베이스 테이블 구조를 다음과 같이 확인했습니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

첫 번째 이유는 우선 비즈니스 오류가 발생하지 않았을 때 발생하지 않았기 때문에 빨리 제거되었습니다. 테이블이 생성된 경우 테이블 65535의 크기가 크지 않은 필드의 합인 경우 테이블 생성 시 오류가 발생하며 비즈니스에서는 쓰기 시에만 오류를 보고합니다. 또한 라이브러리 테이블 구조를 통해 많은 수의 중간 blob 유형 필드가 있고, 크지 않은 필드의 합계가 65535보다 훨씬 적다는 것을 알 수 있습니다.

그러면 업체에서 제공하는 특정 SQL에 따라 appversion, datadata, elt_stamp, id 등의 비대형 필드는 최대 16M까지 저장할 수 있습니다. 이 수준에 도달하는 것부터. 오류 메시지에 따라 appversion, datadata, elt_stamp, id의 비대형 필드를 blob 유형으로 변경했지만 여전히 해결되지 않습니다. (이전 분석에 따르면 문제의 원인이 아니어야 합니다.)

정신을 차리고 보니 사실 또 하나 무시한 사항이 있다는 걸 발견했습니다. 사업 실패율이 100%가 아니라는 건, 아직 성공한 요청이 있다는 뜻이고, 성공한 SQL과 실패한 SQL을 비교해보면, 데이터 볼륨의 차이는 Mediumblob 유형 필드입니다. 그래서 지금 가장 먼저 생각나는 것은 max_allowed_packet 매개변수가 조정되었는지 여부입니다. 예, 단일 요청이 크기를 초과하여 거부되었습니다. 구성된 값은 1G입니다. SQL의 데이터 길이는 그다지 크지 않으므로 이러한 이유는 배제됩니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

이를 확인한 후 기본적으로 몇 가지 일반적인 문제를 제거했습니다. 그런 다음 다른 매개변수인 innodb_page_size의 기본값을 살펴보겠습니다. 각 페이지에는 2개의 데이터 행이 있습니다. 행은 최대 8k 데이터입니다.

데이터 테이블 Row_format이 Compact인지 확인한 후 문제의 원인을 추론할 수 있습니다. innodb의 기본 접근 방식 저장 형식은 페이지에 있는 각 blob 필드의 처음 864바이트를 저장하므로 blob이 초과합니다. 특정 숫자 그렇다면 단일 라인 크기가 8k를 초과하므로 오류가 보고됩니다. 비즈니스 글쓰기의 성공 SQL과 실패 SQL을 비교함으로써 이러한 추론도 적용되었습니다. 그렇다면 이제 이 문제를 어떻게 해결해야 할까요?

  • 비즈니스 분할 테이블, 큰 필드가 별도의 테이블에 저장됨

  • Row_format 저장 방법을 해결하여 문제를 해결하세요

    단일 비즈니스 테이블의 저장 항목 수가 많지 않고 비즈니스 로직이 다음과 같기 때문입니다. 분할에는 적합하지 않으므로 Row_format에서 이 문제를 해결해야 합니다.

Barracuda 파일 형식에는 압축 및 동적이라는 두 가지 새로운 라인 레코드 형식이 있습니다. 새로운 두 가지 형식은 완전한 라인 오버플로 방법을 사용하여 BLOB 데이터를 저장하고 데이터 페이지에 20단어만 저장합니다. BLOB 페이지에 저장됩니다. 압축된 행 레코드 형식의 또 다른 특징은 저장된 데이터가 zlib 알고리즘을 사용하여 압축된다는 것입니다.

관련 변경 작업은 비교적 간단합니다.

1. MySQL 전역 변수 수정:

SET GLOBAL innodb_file_format='Barracuda';

2.

ROW_FORMAT=COMPRESSED

Four. Continue to learn

이 사례를 통해 우리는 깊이 연구할 가치가 있는 두 가지 점을 추출할 수 있습니다:

1 innodb_page_size에 대하여

MySQL 5.6부터 Innodb_page_size를 설정할 수 있습니다. 데이터 페이지는 8K, 4K이며 기본값은 16K입니다. 이 매개변수는 초기 초기화 시 my.cnf에 추가되어야 합니다. 테이블을 생성한 후 수정한 경우 MySQL을 시작할 때 오류가 보고됩니다.

그렇다면 5.6 버전 이전에 이 값을 수정해야 한다면 어떻게 해야 할까요? 유일한 방법은 소스 코드에 대한 작업을 수행한 다음 MySQL을 다시 빌드하는 것입니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

UNIV_PAGE_SIZE는 데이터 페이지 크기이며 기본값은 16K이며 이 값은 2의 거듭제곱으로 설정할 수 있습니다. 이 값은 4k, 8k, 16k, 32K, 64K로 설정할 수 있습니다. UNIV_PAGE_SIZE를 동시에 변경한 후에는 UNIV_PAGE_SIZE_SHIFT를 변경해야 합니다. 값은 UNIV_PAGE_SIZE의 2승이므로 데이터 페이지의 설정은 다음과 같습니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

다음으로 innodb_page_size를 다른 값으로 설정하는 것이 mysql 성능에 미치는 영향에 대해 이야기해 보겠습니다. 테스트된 테이블에는 1억 개의 레코드가 포함되어 있으며 파일 크기는 30G입니다.

1읽기 및 쓰기 시나리오(읽기 50%, 쓰기 50%)

16K, CPU에 대한 압력이 적고, 평균은 20%

8K, CPU 압력은 30%~40%이지만 선택 처리량 16K보다 높습니다

②읽기 시나리오(100% 읽기)

16K와 8K의 차이가 명확하지 않습니다

InnoDB 버퍼 풀 관리 페이지 자체에도 비용이 듭니다. 페이지가 많을수록 동일한 크기의 관리 목록이 길어집니다. 따라서 데이터 행 자체가 상대적으로 긴 경우(큰 블록 삽입), 한 페이지에 더 많은 행을 넣을 수 있고 각 IO 쓰기 크기가 더 크고 IOPS가 적을수록 더 많은 데이터를 쓰기 때문에 페이지가 클수록 속도 향상에 더 도움이 됩니다. . 줄 길이가 8K를 초과하는 경우, 16K 페이지인 경우 일부 문자열 유형은 강제로 TEXT로 변환되고 문자열의 본문은 확장 페이지로 전송되므로 해당 페이지를 읽으려면 IO가 한 번 더 필요합니다. 더 큰 행 길이가 지원되며 64K 페이지는 확장 페이지를 사용하지 않고도 약 32K의 행 길이를 지원할 수 있습니다. 그러나 짧은 줄 길이를 무작위로 읽고 쓰는 경우에는 이렇게 큰 페이지를 사용하는 것이 적합하지 않아 IO 효율성이 떨어지고 큰 IO의 일부만 읽을 수 있습니다.

2. Row_format 소개

Innodb 스토리지 엔진은 레코드를 행 형태로 저장합니다. InnoDB 버전 1.0.x 이전에는 InnoDB 스토리지 엔진이 행 레코드 데이터를 저장하기 위해 Compact 및 Redundant라는 두 가지 형식을 제공했습니다. MySQL 5.1의 innodb_plugin에는 새로운 파일 형식인 Barracuda가 도입되었습니다. Barracuda에는 압축 및 동적이라는 두 가지 새로운 행 형식이 있습니다. 그리고 Compact하고 Redundant를 합쳐서 Antelope라고 부릅니다. SHOW TABLE STATUS LIKE 'table_name' 명령을 사용하면 현재 테이블에서 사용되는 행 형식을 볼 수 있습니다. 여기서 row_format 열은 현재 사용되는 행 레코드 구조의 유형을 나타냅니다.

MySQL 5.6 버전인 기본 Compact, msyql 5.7.9 이상 버전에서는 기본 행 형식이 innodb_default_row_format 변수에 의해 결정되며, 기본값은 DYNAMIC이며, 테이블을 생성할 때 ROW_FORMAT=DYNAMIC을 지정할 수도 있습니다(이를 통해 테이블 저장 형식을 동적으로 조정할 수 있습니다. 기존 테이블의 행 모드를 압축 또는 동적으로 수정하려면 먼저 파일 형식을 Barracuda로 설정해야 합니다(set global innodb_file_format=Barracuda;). 그런 다음 ALTER TABLE 테이블 이름 ROW_FORMAT=COMPRESSED;를 사용하여 수정하여 적용합니다. 그렇지 않으면 수정 사항이 유효하지 않으며 프롬프트가 표시되지 않습니다.

①compact

blob 열 값 길이 > 768바이트이면 처음 768바이트는 여전히 데이터 페이지에 있고 나머지 바이트는 아래와 같이 오버플로 페이지(오프페이지)에 배치됩니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

위에서 언급한 가변 대형 필드 유형에는 blob, text, varchar가 포함됩니다. varchar 열 값의 길이가 특정 숫자 N보다 큰 경우 오버플로 페이지도 latin1 문자 집합에 저장됩니다. innodb의 기본 블록 크기는 16kb입니다. innodb 스토리지 엔진 테이블은 인덱스 구성 테이블이므로 트리 하단의 리프 노드는 양방향 연결 목록이므로 최소한 두 행이 있어야 합니다. 이는 innodb가 다른 열 값이 차지하는 바이트 수(N과 거의 같음)를 뺀 8,000개 이상의 데이터 행을 저장할 수 없음을 결정합니다.

②압축 또는 동적

BLOB에 전체 행 오버플로를 사용합니다. 즉, 클러스터형 인덱스 레코드(데이터 페이지)는 실제로 저장된 오버플로 세그먼트 주소를 가리키는 20바이트 포인터만 유지합니다.

MySQL 데이터 행 오버플로에 대한 심층적인 이해

동적 행 형식, 열 저장소가 오프 페이지 페이지에 배치되는지 여부는 주로 행 크기에 따라 다릅니다. 데이터 페이지가 다음 두 개를 저장할 수 있을 때까지 오프 페이지의 행에 가장 긴 열을 배치합니다. 행. TEXT/BLOB 열은

compressed는 물리적 구조에서 동적과 유사하지만 테이블의 데이터 행은 zlib 알고리즘을 사용하여 압축되어 저장됩니다. 긴 Blob 컬럼 유형이 많을 때 사용하면 오프 페이지 사용을 줄이고 저장 공간을 줄일 수 있습니다. (약 50%, 이전 "[데이터베이스 평가 보고서] Issue 3: InnoDB, TokuDB 압축 성능" 보고서 테스트 참조) 결과), 그러나 더 높은 CPU가 필요합니다. 버퍼 풀은 압축된 버전과 압축되지 않은 버전의 데이터를 모두 저장할 수 있으므로 더 많은 메모리를 차지합니다.

마지막으로 "고성능 MySQL"을 언급하고 BLOB와 같은 가변 Long 필드 유형 사용에 대한 몇 가지 제안을 했습니다.

① 큰 필드는 InnoDB에서 많은 공간을 낭비할 수 있습니다. 예를 들어 저장된 필드 값이 행에 필요한 것보다 1바이트만 더 큰 경우 전체 페이지를 사용하여 나머지 바이트를 저장하므로 페이지 공간의 대부분이 낭비됩니다. 마찬가지로 32페이지 크기를 약간 초과하는 값이 있으면 실제로는 96페이지가 사용됩니다.

②값이 너무 길면 인덱스가 쿼리에서 WHERE 조건으로 사용되지 않아 실행 속도가 느려질 수 있습니다. MySQL은 WHERE 조건을 적용하기 전에 모든 열을 읽어야 하므로 MySQL이 InnoDB에 많은 확장 스토리지를 읽도록 요청한 다음 WHERE 조건을 확인하고 불필요한 데이터를 모두 삭제할 수 있습니다.

3 한 테이블에 큰 필드가 여러 개 있으면 이를 모아서 하나의 열에 저장하는 것이 가장 좋습니다. 각 필드에 자체 페이지를 두는 것보다 모든 큰 필드가 확장된 저장 공간을 공유하는 것이 더 좋습니다.

4 COMPRESS()를 사용하여 큰 필드를 압축한 다음 BLOB으로 저장하거나 MySQL로 보내기 전에 애플리케이션에서 압축하여 상당한 공간 이점과 성능 이점을 얻을 수 있습니다.

⑤ 확장 스토리지는 적응형 해싱을 비활성화합니다. 데이터가 올바른지 확인하려면 열의 전체 길이를 완전히 비교해야 하기 때문입니다.


위 내용은 MySQL 데이터 행 오버플로에 대한 심층적인 이해의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 segmentfault.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

관련 기사

더보기