인터뷰 답변: 각 MySQL 테이블에는 2천만 개 이하의 데이터가 있는 것이 가장 좋습니다. 그렇죠?

MySQL의 각 테이블은 얼마나 많은 데이터를 저장할 수 있나요? 실제 상황에서는 각 테이블이 차지하는 필드와 공간이 다르기 때문에 최적의 성능으로 저장할 수 있는 데이터의 양도 다르기 때문에 수동 계산이 필요합니다.

사건은 이렇습니다

다음은 내 친구의 인터뷰 기록입니다.

인터뷰어: 인턴 기간 동안 무엇을 했는지 말해주세요.

친구: 인턴 기간 동안 사용자 작업 기록을 저장하는 기능을 만들었습니다. 주로 MQ의 업스트림 서비스에서 전송된 사용자 작업 정보를 얻은 다음 이 정보를 동료가 데이터 웨어하우스에서 사용할 수 있도록 MySQL에 저장합니다.

친구: 데이터의 양이 상대적으로 많기 때문에 매일 4천만~5천만 건 정도의 항목이 발생하므로 이에 대한 하위 테이블 작업도 수행했습니다. 매일 정기적으로 세 개의 테이블이 생성되며, 테이블에 과도한 데이터가 쿼리 속도를 저하시키는 것을 방지하기 위해 이 세 개의 테이블에 각각 데이터를 모델링하여 저장합니다.

이 표현에는 문제가 없는 것 같죠? 걱정하지 마세요. 계속 읽어보세요.

인터뷰어: 그러면 왜 테이블 두 개로 나눌 수 없나요? 테이블 4개가 작동하지 않나요?

친구: 각 MySQL 테이블은 2천만 개의 데이터를 초과해서는 안 되기 때문에 그렇지 않으면 쿼리 속도가 감소하고 성능에 영향을 미칩니다. 우리의 일일 데이터는 약 5천만 개이므로 3개의 테이블로 나누는 것이 더 안전합니다.

인터뷰어: 더 없어요?

친구: 이제 그만...뭐해? 앗

인터뷰어: 그럼 돌아가서 알림을 기다려요.

이제 말을 마쳤는데, 제 친구의 대답에 문제가 있는 것 같나요?

머리말

많은 사람들이 각 MySQL 테이블의 데이터가 2천만 개를 초과하지 않는 것이 가장 좋다고 말합니다. 그렇지 않으면 성능 저하가 발생합니다. Alibaba의 Java 개발 매뉴얼에도 단일 테이블의 행 수가 500만 개를 초과하거나 단일 테이블의 용량이 2GB를 초과하는 경우에만 하위 데이터베이스 및 테이블을 구현하는 것이 좋습니다.
사실 이 2천만, 5백만은 대략적인 숫자일 뿐이고 모든 시나리오에 적용되는 것은 아닙니다. 테이블 데이터가 2천만을 넘지 않으면 문제가 없을 것이라고 맹목적으로 생각한다면 그렇습니다. 시스템 오류가 발생할 가능성이 높습니다.

실제 상황에서는 테이블마다 필드가 다르고 필드가 차지하는 공간 등이 다르기 때문에 최적의 성능에서 저장할 수 있는 데이터의 양도 다릅니다.

그렇다면 각 테이블에 적절한 데이터 양을 계산하는 방법은 무엇일까요? 걱정하지 말고 천천히 아래를 내려다보세요.

이 기사는 독자에게 적합합니다. 이 기사를 읽으려면 특정 MySQL 기초가 필요합니다. InnoDB 및 B+ 트리에 대해 어느 정도 이해하고 있어야 합니다. 1년간의 MySQL 학습 경험(약 1년?), "InnoDB의 B+ 트리 높이를 3단계 이내로 유지하는 것이 일반적으로 더 좋습니다"라는 이론적 지식을 알고 있습니다.

이 글은 주로 "InnoDB에서 높이가 3인 B+ 트리에 얼마나 많은 데이터를 저장할 수 있는가?"라는 주제를 설명합니다. 게다가 이 기사의 데이터 계산은 상대적으로 엄격합니다(적어도 인터넷에 있는 관련 블로그 게시물의 95%보다 더 엄격함). 이러한 세부 사항에 관심이 있고 현재 명확하지 않은 경우 계속 읽으십시오.

이 글을 읽으시는데 10~20분 정도 소요됩니다. 읽으시면서 데이터를 확인하시면 30분정도 소요될 수 있습니다.

가 생기면 다시 읽어보는 것이 좋습니다. ??

데이터 테이블은 일반적으로 하나 이상의 트리 저장에 해당합니다. 트리 수는 인덱스 수와 관련이 있습니다.
클러스터형 인덱스와 비클러스터형 인덱스:

• 기본 키 인덱스도 클러스터형 인덱스이고, 기본 키가 아닌 인덱스는 비클러스터형 인덱스입니다.

  형식 정보를 제외하고 두 인덱스의 리프가 아닌 노드는 인덱스 데이터만 저장합니다

  . 예를 들어 인덱스가 id인 경우 리프가 아닌 노드는 id 데이터를 저장합니다. 리프 노드 간의 차이점은 다음과 같습니다.

  • 클러스터형 인덱스의 리프 노드는 일반적으로 이 데이터의 모든 필드 정보를 저장합니다. 그래서 우리가 select * from table where id = 1할 때 우리는 항상 데이터를 얻기 위해 리프 노드로 이동합니다.
  • 비클러스터형 인덱스의 리프 노드에는 이 데이터에 해당하는 기본 키 및 인덱스 열정보가 저장됩니다. 예를 들어, 이 비클러스터형 인덱스가 사용자 이름이고 테이블의 기본 키가 id인 경우 비클러스터형 인덱스의 리프 노드는 사용자 이름과 ID를 저장하지만 다른 필드는 저장하지 않습니다. 이는 먼저 비클러스터형 인덱스에서 기본 키 값을 찾은 다음 기본 키 인덱스를 기반으로 데이터 내용을 확인하는 것과 같습니다. 일반적으로 (인덱스가 포함되지 않는 한) 두 번 확인해야 합니다. table return이라고도 하는데 이는 약간 포인터를 저장하는 것과 유사하며 데이터가 저장된 실제 주소를 가리킵니다.

• B+ 트리 쿼리는 위에서 아래로 계층별로 쿼리됩니다. 일반적으로 B+ 트리의 높이를 3개 계층 이내로 유지하는 것이 좋다고 생각합니다. 즉, 상위 2개 계층은 인덱스이고, 마지막 레이어 데이터를 저장하므로 테이블을 조회할 때 디스크 IO를 세 번만 수행하면 됩니다(루트 노드가 메모리에 상주하기 때문에 실제로는 한 번 더 적음). 저장할 수 있는 데이터의 양도 많은.

  데이터 양이 너무 많아 B+ 숫자가 4레벨이 되면 각 쿼리에 4개의 디스크 IO가 필요하므로 성능이 저하됩니다. 이것이 우리가 InnoDB의 3계층 B+ 트리가 저장할 수 있는 최대 데이터 수를 계산하는 이유입니다.

• 각 MySQL 노드의 기본 크기는 16KB입니다. 즉, 각 노드는 최대 16KB의 데이터를 저장할 수 있으며 최대 64KB, 최소 4KB로 수정할 수 있습니다.

  확장: 특정 행의 데이터가 특히 크고 노드 크기를 초과하면 어떻게 되나요?

  MySQL5.7 문서 설명:

  • 4KB, 8KB, 16KB 및 32KB 설정의 경우 최대 행 길이는 데이터베이스 페이지의 절반보다 약간 작습니다. 예를 들어 기본 16KB 페이지 크기의 경우 최대 행 길이는 8KB보다 약간 작으며 기본 32KB 페이지 크기의 경우 최대 행 길이는 16KB보다 약간 작습니다.

  • 64KB 페이지의 경우 최대 행 길이는 16KB보다 약간 작습니다.

  • 행이 최대 행 길이를 초과하는 경우 행이 최대 행 길이 제한을 충족할 때까지 가변 길이 열이 외부 페이지에 저장됩니다. 즉, 이 행의 데이터 길이를 줄이기 위해 가변 길이의 varchar 및 텍스트가 외부 페이지에 저장됩니다.

문서 주소: MySQL :: MySQL 5.7 참조 ​​설명서 :: 14.12.2 파일 공간 관리

• MySQL 쿼리 속도는 주로 디스크의 읽기 및 쓰기 속도에 따라 달라집니다. MySQL 쿼리는 한번에 하나의 노드만 메모리에 읽어들이고, 이 노드의 데이터를 통해 읽어야 할 다음 노드의 위치를 ​​찾아내고, 필요한 데이터를 쿼리하거나 쿼리가 완료될 때까지 다음 노드의 데이터를 다시 읽어온다. 데이터가 존재하지 않습니다.

  누군가는 각 노드의 데이터를 쿼리해야 하는 것이 아닌가요?라고 묻고 있을 것입니다. 여기서는 왜 소요시간이 계산되지 않나요?

  전체 노드 데이터를 읽은 후 메모리에 저장되기 때문입니다. 실제로 메모리에 있는 노드 데이터를 쿼리하는 데는 MySQL 쿼리 방법과 결합하면 시간 복잡도가 거의 $O(log_2N)$

MySQL InnoDB 노드 저장 내용

Innodb의 B+ 트리에서 우리가 자주 호출하는 노드를 page(페이지)라고 하며, 각 페이지는 사용자 데이터를 저장하고 모든 페이지가 합쳐져 ​​B+ 트리를 형성합니다( 물론 실제로는 훨씬 더 복잡하겠지만, 얼마나 많은 데이터를 저장할 수 있는지 계산하면 이렇게 이해할 수 있겠죠?)

Page는 InnoDB 스토리지 엔진이 데이터베이스를 관리하는 데 사용하는 가장 작은 디스크 단위입니다. 우리는 종종 각 노드가 16KB라고 말하며 이는 실제로 각 페이지의 크기가 16KB임을 의미합니다.

이 16KB 공간에는 페이지 형식 정보와 행 형식 정보가 저장되어야 합니다. 행 형식 정보에는 일부 메타데이터와 사용자 데이터도 포함되어 있습니다. 따라서 계산할 때 이러한 데이터를 모두 포함해야 합니다.

페이지 형식

각 페이지의 기본 형식, 즉 각 페이지에 포함될 일부 정보 요약 테이블은 다음과 같습니다.

구성도:

페이지 형식의 내용인데, 공식 홈페이지에서 한참 뒤져봤는데 그냥 못찾았나요?. . . . 제가 글을 작성하지 않아서인지, 아니면 제가 눈이 멀어서 그런 것인지 모르겠습니다. 혹시 찾으신 분이 계시다면 댓글란에 올려주시면 감사하겠습니다.

그래서 위 페이지 형식의 표 내용은 주로 일부 블로그에서 학습한 내용과 요약을 바탕으로 작성되었습니다.

또한 InnoDB 클러스터형 인덱스에 새 레코드가 삽입되면 InnoDB는 향후 인덱스 레코드 삽입 및 업데이트를 위해 페이지의 1/16을 비워 두려고 합니다. 색인 레코드를 순서대로(오름차순 또는 내림차순) 삽입하면 결과 페이지에는 사용 가능한 공간이 약 15/16이 됩니다. 레코드를 무작위 순서로 삽입하는 경우 페이지 공간의 약 1/2 ~ 15/16을 사용할 수 있습니다. 참조 문서: MySQL :: MySQL 5.7 참조 ​​설명서 :: 14.6.2.2 InnoDB 인덱스의 물리적 구조

User Records和Free Space을 제외하고 점유된 메모리는 $38 + 56 + 26 + 8 = 128$= 1523216회 분수{15} {16}1024 - 128 = 1523216 1 6 ㅋㅋㅋㅋㅋㅋㅋㅋㅋ 1 024 −128= 15 2 32 바이트 (1/16 예약됨). 물론 페이지 디렉토리를 고려하지 않기 때문에 이는 최소값입니다. 페이지 디렉토리는 나중에 고려되며 이는 테이블 필드를 기반으로 계산되어야 합니다. 행 형식우선 MySQL5.6의 기본 행 형식은 COMPACT(컴팩트)이고, 5.7 이상에서는 기본 행 형식이 DYNAMIC(동적)이라는 점을 언급할 필요가 있다고 생각합니다. . 다른 행 형식 저장 방법도 다르며 다른 두 가지 행 형식이 있습니다. 이 기사의 이후 내용은 주로 DYNAMIC을 기반으로 설명됩니다. 공식 문서 링크: MySQL :: MySQL 5.7 참조 ​​매뉴얼:: 14.11 InnoDB 행 형식 (아래 행 형식 내용의 대부분은 여기에서 찾을 수 있습니다) 각 레코드 행에는 다음이 포함됩니다. 정보, 대부분은 공식 문서에서 찾을 수 있습니다. 제가 여기에 쓴 내용은 그다지 상세하지 않으며, 공간 계산에 도움이 되는 몇 가지 지식만 작성했습니다. 자세한 내용은 온라인에서 "MySQL 행 형식"을 검색해 보세요.

개략도:

몇 가지 참고 사항이 더 있습니다.

오버플로 페이지 저장(외부 페이지)

참고: 이는 DYNAMIC의 기능입니다.

DYNAMIC을 사용하여 테이블을 생성할 때 InnoDB는 더 긴 가변 길이 열(예: VARCHAR, VARBINARY, BLOB 및 TEXT 유형)의 값을 제거하여 오버플로 페이지에만 저장합니다. 컬럼 오버플로 페이지에 대한 20바이트 포인터를 예약합니다.

COMPACT 행 형식(MySQL5.6 기본 형식)은 B+ 트리 노드의 레코드에 처음 768바이트와 20바이트 포인터를 저장하고 나머지는 오버플로 페이지에 저장됩니다.

열이 페이지 외부에 저장되는지 여부는 페이지 크기와 행의 전체 크기에 따라 다릅니다. 행이 너무 길면 클러스터형 인덱스 레코드가 B+ 트리 페이지에 맞을 때까지 오프 페이지 저장을 위해 가장 긴 열이 선택됩니다(문서에는 몇 개가 나와 있지 않습니까?). 40바이트 이하의 TEXT 및 BLOB는 행 내에 직접 저장되며 페이징되지 않습니다.

장점

동적 행 형식은 B+ 트리 노드를 많은 양의 데이터로 채워 긴 열이 발생하는 문제를 방지합니다.

동적 행 형식의 아이디어는 긴 데이터 값의 일부가 페이지 외부에 저장되는 경우 일반적으로 전체 값을 페이지 외부에 저장하는 것이 가장 효율적이라는 것입니다.

DYNAMIC 형식을 사용하면 가능한 경우 더 짧은 열이 B+ 트리 노드에 유지되어 특정 행에 필요한 오버플로 페이지 수를 최소화합니다.

다른 문자 인코딩으로 저장

char, varchar, text 등은 문자 인코딩 유형을 설정해야 합니다. 점유 공간을 계산할 때 다른 인코딩이 차지하는 공간을 고려해야 합니다.

Varchar, text 및 기타 유형에는 차지하는 길이를 기록하기 위한 길이 필드 목록이 있지만 char은 고정 길이 유형이므로 상황이 특별합니다. 그러면 필드 이름 유형이 char(10)이라고 가정합니다. 다음과 같은 상황이 발생합니다.

• 고정 길이 문자 인코딩(예: ASCII 코드)의 경우 필드 이름은 ASCII 코드의 각 문자가 1바이트를 차지하므로 이름이 차지합니다. 10바이트.

• 가변 길이 문자 인코딩(예: utf8mb4)의 경우 이름에 최소 10바이트가 예약됩니다. 가능하다면 InnoDB는 후행 공백을 잘라서 10바이트로 저장합니다.

  자르기 후 공간을 저장할 수 없는 경우 후행 공백을 열 값의 최소 바이트 길이(보통 1바이트)로 자릅니다. 열의 최대 길이는 다음과 같습니다.

  단어

  코드$\text{가 가장 큽니다}\mathrm{문자}$degree ×$\mathrm{}10.$

솔직히 저는 char의 디자인을 잘 이해하지 못합니다. 비록 공식 문서와 일부 블로그를 포함하여 오랫동안 읽었지만 이해하는 학생들이 댓글 영역에서 자신의 의심을 명확히 할 수 있기를 바랍니다.

가변 길이를 갖는 문자의 경우 인코딩 측면에서 char은 가변 길이 유형과 약간 비슷하지 않나요? 일반적으로 사용되는 utf8mb4는 1~4바이트를 차지하므로 char(10)이 차지하는 공간은 10~40바이트이다. 이 변화는 꽤 크지만 이를 위한 충분한 공간을 남겨주지도 않고, char 필드의 공간 사용량을 기록하는 가변 길이 필드 목록이 있습니까?

계산 시작

자, 우리는 이미 각 페이지에 무엇이 저장되어 있는지 알고 있고 이제 컴퓨팅 능력도 갖추게 되었습니다.

위의 페이지 형식에서 페이지의 남은 공간을 이미 계산했으므로 각 페이지에 사용할 수 있는 공간은 15232바이트입니다. 아래에서 직접 행을 계산해 보겠습니다.

비리프 노드 계산

단일 노드 계산

인덱스 페이지는 인덱스가 저장되는 노드, 즉 비리프 노드입니다.

각 인덱스 레코드에는 데이터 페이지의 다음 레이어에 대한 포인터를 가리키는 데 사용되는 현재 인덱스의 값, 6바이트 포인터 정보, 5바이트 행 헤더가 포함되어 있습니다.

공식 문서의 인덱스 레코드에서 포인터가 차지하는 공간을 찾지 못했다면? 다른 블로그 게시물을 참조하면 이 6바이트라고 하는데 소스 코드에서는 6바이트라고 합니다. 소스 코드의 어느 부분인지 모르시나요?

더 많은 것을 알고 있는 학생들이 댓글 영역에서 자신의 의심을 명확히 할 수 있기를 바랍니다.

기본 키 ID가 bigint 유형(8바이트)이라고 가정하면 인덱스 페이지의 각 데이터 행이 차지하는 공간은 $8 + 6 + 5 = 19$바이트. 각 페이지는 $15232 div 19 약 801$을 저장할 수 있습니다. 인덱스 데이터.

페이지 디렉토리까지 포함하면 슬롯당 6개 데이터의 평균을 계산하면 최소 $801개가 됩니다. div 6 약 134$

처음 두 레이어의 리프가 아닌 노드 계산

B+ 트리에서 노드 인덱스 레코드가 $N$ 바, $N$ 노드. 3레벨 B+ 트리의 처음 두 레벨은 인덱스 레코드이므로 첫 번째 레벨 루트 노드에는 $N$이 있습니다. 색인 레코드, 두 번째 레이어는 $n$ 노드를 가지게됩니다. 각 노드 데이터 유형은 루트 노드와 일치합니다. 더 많은 $N$ 레코드을 저장할 수 있습니다. 세 번째 레이어의 노드 수는 와 같습니다. $N^2$.

다음은 다음과 같습니다.

• 기본 키 bigint가 있는 테이블은 $787 ^ 2 = 6193을 저장할 수 있습니다. 69$ 기본 키 int가 있는 테이블은
• $\mathrm{}32^{\mathrm{}}986049을\; 저장할\; 수\; 있습니다.\mathrm{}993\; ^\; 2\; =\; 986049$
최소 저장 레코드 수

최대 행 길이가 데이터베이스 페이지의 절반보다 약간 작다고 언급한 이유. 절반 미만은 각 페이지에 페이지 형식에 다른 콘텐츠를 위한 일부 공간이 남아 있기 때문에 각 페이지가 최소한 두 개의 데이터를 담을 수 있고 각 데이터 조각이 8KB보다 약간 적다고 생각할 수 있습니다. 행의 데이터 길이가 이 값을 초과하면 InnoDB는 일부 데이터를 overflow 페이지

각 데이터 조각이 8KB인 경우 각 리프 노드는 2개의 데이터만 저장할 수 있습니다. 기본 키가 bigint인 경우 이러한 테이블은 $2회$

-- 这是一张非常普通的课程安排表，除id外，仅包含了课程id和老师id两个字段
-- 且这几个字段均为 int 型（当然实际生产中不会这么设计表，这里只是举例）。

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

먼저 이 테이블의 행 데이터를 분석해 보겠습니다. null 값 목록도 없고, 가변 길이 필드 목록도 없으며, 트랜잭션 ID와 포인터 필드를 계산해야 하고, 행 레코드 헤더를 계산한 다음 공간을 차지해야 합니다. 데이터의 각 행은 $4+4+4+6+7+5 = 30$+

算上页目录的槽位所占空间，每个叶子节点可以存放 502 条数据，那么三层B+树可以存放的最大数据量就是 $502 \times 986049 = 494,996,598$，将近5亿条数据！没想到吧??。

常规表的存放记录数

大部分情况下我们的表字段都不是上面那样的，所以我选择了一场比较常规的表来进行分析，看看能存放多少数据。表情况如下：

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `author_id` bigint unsigned NOT NULL COMMENT '作者id',
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
  `description` varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述',
  `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码',
  `cover_image` char(32) DEFAULT NULL COMMENT '封面图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `release_time` datetime DEFAULT NULL COMMENT '首次发表时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint unsigned NOT NULL COMMENT '发表状态',
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

这是我的开源项目“校园博客”（GitHub地址：github.com/stick-i/scb…） 中的博客表，用于存放博客的基本数据。

分析一下这张表的行记录：

• 行记录头信息：肯定得有，占用5字节。

• 可变长度字段列表：表中 title占用1字节，description占用2字节，共3字节。

• null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。

• 事务ID和指针字段：两个都得有，占用13字节。

• 字段内容信息：

  • id、author_id、school_code 均为bigint型，各占用8字节，共24字节。

  • create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。

  • status、is_delete 为tinyint类型，各占用1字节，共2字节。

  • cover_image 为char(32)，字符编码为表默认值utf8，由于该字段实际存的内容仅为英文字母（存url的），结合前面讲的字符编码不同情况下的存储 ，故仅占用32字节。

  • title、description 이 두 가지는 각각 오버플로 페이지를 생성해서는 안 됩니다(확실하지 않음). 실제 제작에서는 70% 이상이 중국어로 저장됩니다. 바이트), 25%는 영어(1바이트), 5%는 4바이트 이모티콘? 저장공간이 꽉 차면 $(50+250을\; 차지하게\; 됩니다.)\; )\times (0.7\times 3+0.25\times 1+0.4)\mathrm{}=765\mathrm{(50\; +\; 250)\; 곱하기\; (0.7\; 곱하기\; 3\; +\; 0.25\; 곱하기\; 1\; +\; 0.05\; 곱하기\; 4\; )\; =\; 765}$

위의 모든 분석 통계는 총 869바이트를 차지하며, 각 리프 노드는 $15232div 869 대략 페이지 목차를 포함한 17$6

19$36\mathrm{}9\mathrm{}=\mathrm{}\mathrm{}10\mathrm{},5$2