MySQL의 데이터 저장 구조에 대한 간략한 분석

이 글에서는 주로 InnoDB 데이터 저장 구조의 관점에서 어떤 상황에서 SQL 쿼리 효율성이 저하되는지 분석합니다. 인터넷에서 이에 대한 불만을 토로하는 기사를 종종 봅니다. 데이터의 양이 많으면 쿼리 효율성이 많이 떨어집니다. 관련 테이블이 많으면 쿼리 효율성이 떨어집니다. 단일 테이블의 데이터 양은 100만 개를 초과할 수 없습니다.

데이터베이스 버전: 8.0 엔진: InnoDB 참고 자료: Nuggets Booklet "Understanding MySQL from the Roots", 시간이 있으면 직접 읽어 보시기 바랍니다.

샘플 테이블:

CREATE TABLE `hospital_info` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `id` varchar(36)  NOT NULL COMMENT '外键',
  `hospital_code` varchar(36)  NOT NULL COMMENT '医院编码',
  `hospital_name` varchar(36)  NOT NULL COMMENT '医院名称',
  `is_deleted` tinyint DEFAULT NULL COMMENT '是否删除 0否 1是',
  `gmt_created` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '删除时间',
  PRIMARY KEY (`pk_id`),
  KEY `hospital_code` (`hospital_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='医院信息';

InnoDB 행 형식

한 행의 데이터부터 시작하여 먼저 한 행의 데이터 저장 형식을 이해해 보겠습니다. 현재 Compact, Redundant, Dynamic 및 Compressed 행 형식의 4가지 행 형식이 있습니다. 일반적으로 테이블을 생성할 때 의도적으로 지정할 필요가 없습니다. 버전 5.7 이상에서는 기본값이 Dynamic입니다. 각 행 형식은 유사합니다. 여기서는 각 데이터 행이 기록되는 방식을 간략하게 이해하기 위해 Compact를 예로 들어 보겠습니다.

위 사진과 같습니다. "추가 정보"와 "실제 데이터"의 두 부분으로 나뉩니다.

가변 길이 필드 목록

이것이 더 흥미롭습니다. 일반적으로 필드를 정의할 때 필드의 type 및 length을 지정해야 합니다.

예: hospital_codefield 정의VARCHAR (36). 실제 사용에서 hospital_code 필드 길이는 32비트만 사용합니다.

남은 4명의 캐릭터는 어떻게 될까요? 빈 글자를 억지로 채우면 4글자 메모리 낭비가 되지 않을까요? 채워지지 않은 경우 현재 필드에 몇 개의 문자가 저장되어 있는지 확인하는 방법은 무엇입니까? 얼마나 많은 메모리를 차지합니까?

이때 가변 길이 필드 목록은 각 가변 길이 필드의 실제 길이를 기록하기 위해 1~2바이트를 사용하여 필드별로 역순으로 정렬됩니다. 이를 통해 메모리 공간을 효과적으로 활용할 수 있습니다.

유사 필드: VARBINARY, 다양한 TEXT 유형, 다양한 BLOB 유형.

따라서 CHAR(10)과 같은 "고정 길이 필드"도 있습니다. 이 유형의 필드는 초기화 중에 기본적으로 지정된 문자 길이의 공간을 차지합니다. 빈 문자로 채워지기 때문에 공간이 상대적으로 낭비적이므로 일반적으로 필요에 따라 길이를 설정하는 것이 좋습니다.

물론, "가변 길이 필드 목록"이 반드시 존재하는 것은 아닙니다. 정의된 필드 유형에 "가변 길이 필드"가 없으면 존재하지 않습니다.

확장: TEXT 또는 BLOB 유형 필드의 경우 길이가 한 페이지에 저장되지 않을 수 있습니다. 이 경우 대부분의 데이터가 다른 페이지에 기록되고 다음 데이터 페이지의 주소가 유지됩니다. 현재 기록에서.

NULL 값 목록

실제로 데이터를 저장할 때 일부 열에는 NULL 값이 저장될 수 있습니다. 이러한 값이 실제 데이터에 기록되면 저장 공간이 낭비됩니다. Compact 형식에서는 NULL 값이 있는 이러한 열이 균일하게 관리되며 NULL 값 목록에 저장됩니다.

데이터 행에 NULL인 필드가 없으면 이 열이 생성되지 않습니다.

저장 방법도 꽤 흥미롭습니다. 바이너리 역순으로 기록됩니다.

샘플 테이블을 사용하여 분석하면 테이블에 is_deleted, gmt_created, gmt_modified라는 세 가지 필드가 있으며 비어 있을 수 있습니다. 레코드에서 gmt_created와 gmt_modified가 모두 비어 있다고 가정하면 해당 NULL 값 목록은 다음과 같아야 합니다.

펼치기: MySQL은 바이너리 데이터 저장을 지원하며, 전체 활용으로 많은 저장 공간을 줄일 수 있습니다.

레코드 헤더 정보

레코드 헤더 정보는 고정된 5자로 구성되며 길이는 40바이너리 비트입니다.

우선, 이해를 돕기 위해 여기에 더 흥미로운 로고가 있습니다: delete_mask Redis를 사용해 본 사람은 모두 Redis에서 삭제된 데이터가 즉시 지워지지 않는다는 것을 알고 있습니다. 동일한 mysql에서도 마찬가지입니다. 정리 프로세스는 효율성에 큰 영향을 미치는 IO 작업을 트리거하므로 데이터가 즉시 정리되지 않습니다. 삭제된 데이터는 링크드 리스트로 구성되어 재사용 가능한 공간으로 활용 가능합니다.

실제 데이터 데이터를 기록합니다

사실 이건 말할 것도 없고, NULL이 아닌 실제 데이터를 기록하는 것입니다.

인터넷에서 흔히 볼 수 있는 질문이 있습니다. 기본 키가 설정되지 않으면 어떻게 되나요?

InnoDB에서 기본 키는 레코드의 고유 식별자입니다. 사용자가 이를 지정하지 않으면 mysql은 Unique(고유) 키 중 하나를 기본 키로 선택합니다. row_idhide 열이라는 키를 기본 키로 추가합니다. 또한

transaction_id(트랜잭션 ID)

및 roll_pointer(롤백 포인터) 두 열이 추가됩니다.

요약

4개의 행 형식은 매우 유사하므로 "추가 정보"와 "실제 데이터" 두 부분으로 나누어서 하나씩 소개하지는 않겠습니다. 차이점은 주로 "추가 정보" 레코드의 내용과 가변 길이 필드의 저장에 있습니다.

InnoDB 데이터 페이지

데이터 페이지의 개념은 아마도 여러분에게 익숙할 것입니다. InnoDB가 저장 공간을 관리하는 기본 단위로, 한 페이지의 크기는 일반적으로

16KB

입니다. 테이블스페이스 헤더 정보를 저장하는 페이지, Insert Buffer 정보를 저장하는 페이지, INODE 정보를 저장하는 페이지, undo를 저장하는 페이지 등 다양한 유형의 페이지가 다양한 목적에 따라 설계됩니다. 로그 정보 등. 페이지 공간은 다음과 같이 구분됩니다.

총 7개의 구성 요소가 있습니다. 7개 부분을 대략적으로 설명하겠습니다.

파일 헤더

와 페이지 헤더에는 많은 속성이 있습니다. 이 두 곳이 page의 일부 속성을 기록한다는 점만 알면 여기서는 하나씩 소개하지 않겠습니다. : 페이지 번호, 이전 페이지 및 다음 페이지의 페이지 번호, 페이지 유형, 페이지의 메모리 사용량 등 여기서 이야기하자면, 페이지는 이중 연결 목록으로 연결되어 있습니다. 데이터 기록은 단일 목걸이입니다.

파일 트레일러

는 페이지 데이터의 무결성을 확인하는 데 사용됩니다. 메모리에서 디스크로 페이지 데이터를 다시 쓸 때 데이터 페이지 손상을 방지하기 위해 확인해야 합니다.

사용자 기록(사용된 공간)

과 여유 공간(남은 공간)에 집중하세요. 실제 저장된 데이터 기록은 다음과 같습니다. 또한

Infimum

과 Supremum은 각각 최소 기록과 최대 기록을 식별합니다. 즉, 페이지가 생성되면 기본적으로 이 두 레코드가 포함되지만 걱정하지 마세요. 이 두 레코드는 데이터 연결 목록의 헤드와 테일로만 사용되며 실제 데이터에는 영향을 미치지 않습니다. 요약하면 페이지에 기록이 저장되는 방식은 다음과 같습니다.

간단히 말하면 여유 공간을 사용자 기록으로

전환하는 것입니다. 여유 공간이 소진되면 데이터 페이지는 다음과 같이 간주됩니다. 가득한. 이 시점에서 데이터 페이지에 데이터가 기록되었습니다. 어떻게 꺼내나요? 위에서 데이터 레코드가 단일 연결 목록으로 구성되어 있다는 것을 알고 있습니다.

Infimum

(최소) 레코드에서 시작하여 연결 목록을 따라 이동해야 합니까? 분명히 MySQL의 개발 책임자는 그렇게 멍청할 수 없습니다. 그렇지 않으면 저는 할 수 있습니다. 하하.

여기서

페이지 디렉토리

(페이지 디렉토리)를 언급해야 합니다. 페이지에서는 데이터가 그룹화되어 있으며, 각 그룹의 마지막 레코드의 주소 오프셋이 별도로 추출되어 페이지 끝 부분에 있는 "페이지 디렉터리"에 순서대로 저장됩니다. 그리고 마지막 레코드 헤더(n_owned)에도 그룹에 몇 개의 레코드가 있는지 저장합니다. 페이지 디렉토리는 슬롯으로 구성되어 있습니다. 전체 구조 다이어그램은 다음과 같습니다.

디렉토리가 있으면 쿼리가 비교적 간단합니다.

이분법을 이용하시면 빠른 검색이 가능합니다. 위 그림에서 우리는 최소 슬롯이 0이고 최대 슬롯이 4임을 알 수 있습니다. 예:

기본 키 레코드가 6인 데이터를 쿼리한다고 가정합니다. 1) 중간 슬롯 위치를 계산합니다((0+4)/2 = 2). 추출된 슬롯에 해당하는 레코드의 기본키는 8>6이므로 8이다.

2) 같은 방법으로 가장 큰 슬롯을 2, 즉 (0+2)/2 =1로 설정합니다. 슬롯 1에 해당하는 기본 키는 4입니다. 4

이후 설명을 용이하게 하기 위해 페이지의 데이터 형식을 아래 그림과 같이 단순화했습니다.

B+Tree Index

앞서 언급한 것처럼 질문에 대해 생각해 보는 것이 좋습니다. 데이터 페이지는 대략 아래 그림과 같이 이중 연결 목록으로 연결됩니다. 위 그림에서 볼 수 있듯이 페이지 번호는 연속적이지 않으며

,

반드시 연속적인 메모리 공간은 아닙니다(다음 사항을 기억하세요). 문장) 그것에 대해 이야기하겠습니다).

각 페이지에 3개의 레코드를 저장할 수 있다고 가정하면, 이제 저장할 레코드가 100,000개가 되며, 이때 30,000개의 데이터 페이지가 필요하게 되며, 이때 한 페이지에 너무 많은 데이터가 있는 것과 동일한 쿼리 문제에 직면하게 됩니다. 하나씩 통과해 보세요. 이때 빠르게 조회할 수 있는 디렉터리도 필요합니다. 이 디렉터리가 "Index"입니다.

위 그림에 표시된 데이터 페이지를 기반으로 다음과 같은 인덱스 구조를 구성할 수 있습니다. 이것을 흔히 클러스터형 인덱스라고 부르며, 리프가 데이터입니다. 여기서 한 가지 주목할 점은 "Page 30"에는 기본 키와 해당 페이지 번호가 저장되어 있다는 점입니다. 단일 인덱스 페이지가 가득 차면 분할됩니다. 아래와 같이 트리 구조를 생성합니다. 단, 식별의 편의를 위해 위 사진은 완전히 정확하지는 않습니다. 루트 노드가 먼저 생성되어야 합니다. 루트 노드가 가득 차면 분할됩니다. 루트 노드는 분할 후 인덱스 페이지 정보를 기록합니다.

간단히 말하면 뿌리부터 시작하여 줄기, 가지, 잎 등으로 자라나는 나무의 성장과 같습니다.

보조 인덱스 개념은 클러스터형 인덱스와 동일합니다. 차이점은 보조 인덱스의 리프 노드가 실제 데이터가 아니라 데이터의 기본 키라는 것입니다. 실제 데이터를 얻으려면 return table 작업을 수행해야 합니다.

테이블 공간

지금까지 우리는 이미 단일 데이터의 저장 구조와 가장 작은 저장 데이터 단위 페이지를 알고 있었습니다. 데이터 페이지는 이중 연결 목록을 통해 연결되며 데이터 페이지가 반드시 연속적인 것은 아닙니다.

이때 문제가 발생합니다. 같은 테이블에 있는 레코드의 페이지가 메모리 주소에서 너무 멀리 떨어져 있으면 어떻게 되나요? 세 사람을 찾기 위해 각각 베이징, 뉴욕, 런던으로 간다고 상상해 보세요. 하나씩 찾아야 하고 여행 중에 많은 시간을 낭비해야 합니다. 한 나라나 심지어 도시에 모아두면 훨씬 더 빨라질 것이다.

그래서 District라는 개념이 탄생했습니다. 영역은 64개의 연속된 페이지로 구성됩니다. 기본적으로 하나의 영역은 1M의 메모리를 차지합니다. 메모리를 적용할 때 한번에 1M의 공간을 차지하고 데이터 페이지가 인접해 있어 random IO 문제를 어느 정도 해결해줍니다.

영역을 기준으로 쿼리 효율성을 보다 효과적으로 향상시키기 위해 B+ 트리의 리프 노드와 리프가 아닌 노드를 서로 다른 영역에 기록합니다. 이러한 영역의 집합을 "세그먼트(segment)"라고 합니다. ". 이 개념에 따라 첫 번째 레코드를 삽입하려면 2개의 영역 공간, 클러스터형 인덱스 루트 노드 및 데이터 페이지를 신청해야 합니다. 이번에는 2M의 공간을 신청해야 합니다! 아무것도 안했는데 2M 공간이 사라졌네요. 분명히 이것은 불합리합니다.

그래서 우리는 "fragment Area"라는 컨셉을 생각해냈습니다. 단편화된 영역은 테이블스페이스에 직접 속하며 어떤 세그먼트에도 속하지 않습니다. 메모리 할당 과정은 다음과 같이 변경됩니다.

1) 데이터가 처음 삽입되면 프래그먼트 영역에서 저장 공간이 단일 페이지로 할당됩니다.

2) 세그먼트가 32개의 조각 영역 페이지를 차지하면 공간이 완전한 영역으로 할당됩니다.

테이블 공간은 영역의 XDES Entry데이터 구조 외에도 시스템 테이블 공간과 독립 테이블 공간으로 구분됩니다. 내용이 너무 많고 복잡해서 더 알고 싶으시면 원서를 읽어보시면 됩니다.

Thinking

1) 인덱스가 많을수록 좋을까요? 더 많은 경우 어떤 영향이 있습니까?

위에서 볼 수 있듯이 인덱스 레코드에도 메모리 소비가 필요합니다. 각 인덱스는 B+ 트리에 해당하며 각 트리에는 리프 노드와 리프가 아닌 노드를 각각 기록하기 위해 두 개의 세그먼트가 필요합니다. 이로 인해 많은 메모리 낭비가 발생합니다. 결국 인덱스 자체의 의미는 공간과 시간을 교환하는 것입니다. 하지만 데이터의 추가, 삭제, 수정으로 인해 인덱스가 변경되므로 인덱스에서 노드를 재할당하고 페이지 메모리를 재활용 및 할당해야 한다는 점을 알아야 합니다. 이는 모두 IO 작업입니다. 인덱스가 너무 많으면 필연적으로 성능이 저하됩니다.

따라서 공동 인덱스를 합리적으로 사용하면 단일 인덱스가 너무 많은 문제를 해결할 수 있습니다. 또한 인덱스에는 길이 제한이 있으며, 너무 긴 필드는 인덱싱에 적합하지 않습니다.

2) 인덱스 쿼리 효율이 왜 이렇게 높나요?

이것은 실제로 알고리즘 문제입니다. 클러스터형 인덱스를 예로 들어 보겠습니다. 리프가 아닌 노드의 인덱스 페이지는 각각 1000개의 데이터를 기록할 수 있으며, 각 리프 노드는 3계층의 데이터를 기록할 수 있습니다. B+ 트리(루트 노드 계산 안 함)는 10001000500개의 레코드를 저장할 수 있습니다. 3계층 구조의 인덱스는 워낙 많은 레코드를 저장할 수 있기 때문에 매번 데이터를 찾는 데 몇 번의 쿼리만 필요하므로 당연히 효율성이 높습니다.

실제로 하나의 인덱스 페이지에 기록할 수 있는 데이터는 이보다 훨씬 더 많습니다.

마찬가지로 여기서 문제에 대해 생각해 볼 수 있습니다. 리프 노드의 단일 데이터 조각이 너무 커서 데이터 페이지가 3개의 레코드만 저장할 수 있으면 B+ 트리의 깊이가 증가하므로 테이블의 단일 레코드를 줄이는 것이 합리적입니다. 크기도 최적화됩니다.

3) 데이터 양이 많으면 SQL 실행이 느려지나요?

사실 이 문제에 대해 정말 불평하고 싶습니다. 수백만 개의 데이터에 대한 쿼리 효율성은 xx초로 너무 느립니다. mysql의 성능이 실제로 일부 데이터베이스에 비해 약한 것은 부인할 수 없지만 수백만 개의 데이터로 인해 속도가 느려질 것입니다. SQL 및 테이블 구조 설계가 합리적인지 생각해 보십시오. 수백만 수준은 말할 것도 없고, 수천만 수준이라도 밀리초 수준의 쿼리를 달성할 수 있습니다. 단지 수량에 대해 이야기하는 것은 말도 안되는 일입니다. 테이블에 수백 개의 필드가 있거나 매우 긴 문자가 있는 필드가 있는 경우 실제로 잠금이 차지하는 메모리 크기를 살펴봐야 합니다. 그러면 신들조차도 당신을 구원할 수 없습니다.

요약

이 글은 주로 MySql 데이터 구조의 개념을 소개하고 있습니다. 대부분의 내용은 "Understanding Mysql from the Root"라는 책에서 따왔습니다. 일부 개념을 이해하는 데 기초가 될 수 있도록 많은 단순화가 이루어졌습니다.

틀린 부분이나 누락된 부분이 있으면 지적해주시면 감사하겠습니다.

【관련 추천: mysql 비디오 튜토리얼】

위 내용은 MySQL의 데이터 저장 구조에 대한 간략한 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!