MySQL 데이터 행 오버플로에 대한 심층적인 이해
- 不言앞으로
- 2018-12-30 09:29:305955검색
이 기사는 MySQL 데이터 행 오버플로에 대한 심층적인 이해를 제공합니다. 이는 특정 참조 가치가 있습니다. 도움이 필요한 친구가 도움이 되기를 바랍니다.
1. 일반적인 오류부터 시작해 보겠습니다.
일반적인 SQL 오류 메시지부터 시작하겠습니다.
특히 OMG와 같은 BG의 경우 이러한 유형의 오류가 여러 번 발생했을 것입니다. 콘텐츠 생산이 주요 핵심이기 때문에 콘텐츠 라인의 저장에서 대용량 데이터는 피할 수 없는 주제입니다. 여기서 데이터의 "크기"는 차지하는 큰 저장 공간을 훨씬 뛰어넘는 단일(테이블) 필드의 큰 저장 공간, 긴 데이터 보존 시간, 큰 데이터 중복성, 불일치로 인한 대용량도 포함됩니다. 핫스팟에 따라 피크값이 크게 변화하고, 복잡한 논리적 처리로 인해 데이터 저장 부담이 증폭되는 등의 문제가 발생합니다. 오류 보고 문제로 돌아가서 먼저 이 테이블의 구조를 살펴보겠습니다.
이를 보면 모든 사람이 이를 처리하는 방법이 다를 것이라고 생각하므로 장단점에 대해서는 논의하지 않겠습니다. 여기에서는 비교를 위해 더 자주 사용되는 두 가지 처리 방법만 설명합니다.
오류 지침에 따라 두 개의 큰 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. 실제 오류
온라인 업계에서 다음과 같은 오류가 많이 발생하여 프로그램에서 데이터를 쓸 수 없게 되었습니다.
프롬프트 및 정상 이것을 생각하면 우리의 첫 번째 반응은 비즈니스에 다음과 같은 문제가 있다는 것입니다.
설정된 테이블 구조의 필드가 제한을 초과했습니다.
특정 필드에 삽입된 데이터의 길이가 최대값을 초과했습니다. 변경된 필드에 설정
그런 다음 비즈니스의 데이터베이스 테이블 구조를 다음과 같이 확인했습니다.
첫 번째 이유는 우선 비즈니스 오류가 발생하지 않았을 때 발생하지 않았기 때문에 빨리 제거되었습니다. 테이블이 생성된 경우 테이블 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의 데이터 길이는 그다지 크지 않으므로 이러한 이유는 배제됩니다.
이를 확인한 후 기본적으로 몇 가지 일반적인 문제를 제거했습니다. 그런 다음 다른 매개변수인 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을 다시 빌드하는 것입니다.
다음으로 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바이트는 여전히 데이터 페이지에 있고 나머지 바이트는 아래와 같이 오버플로 페이지(오프페이지)에 배치됩니다.
위에서 언급한 가변 대형 필드 유형에는 blob, text, varchar가 포함됩니다. varchar 열 값의 길이가 특정 숫자 N보다 큰 경우 오버플로 페이지도 latin1 문자 집합에 저장됩니다. innodb의 기본 블록 크기는 16kb입니다. innodb 스토리지 엔진 테이블은 인덱스 구성 테이블이므로 트리 하단의 리프 노드는 양방향 연결 목록이므로 최소한 두 행이 있어야 합니다. 이는 innodb가 다른 열 값이 차지하는 바이트 수(N과 거의 같음)를 뺀 8,000개 이상의 데이터 행을 저장할 수 없음을 결정합니다.
②압축 또는 동적
BLOB에 전체 행 오버플로를 사용합니다. 즉, 클러스터형 인덱스 레코드(데이터 페이지)는 실제로 저장된 오버플로 세그먼트 주소를 가리키는 20바이트 포인터만 유지합니다.
동적 행 형식, 열 저장소가 오프 페이지 페이지에 배치되는지 여부는 주로 행 크기에 따라 다릅니다. 데이터 페이지가 다음 두 개를 저장할 수 있을 때까지 오프 페이지의 행에 가장 긴 열을 배치합니다. 행. 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!