☰

분할된 MySQL InnoDB 테이블이 분할되지 않은 테이블보다 두 배 큰 이유는 무엇입니까?

tag-icon • tag-icon mysql innodb table-partitioning
분할된 MySQL InnoDB 테이블이 분할되지 않은 테이블보다 두 배 큰 이유는 무엇입니까?

하나는 분할되고 다른 하나는 그렇지 않다는 점을 제외하면 모든 측면에서 동일한 두 개의 InnoDB 테이블이 있습니다.

DROP TABLE IF EXISTS `simple_table`;
CREATE TABLE `simple_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `partitioned_table`;
CREATE TABLE `partitioned_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY RANGE ( TO_DAYS(`date`))
(PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = InnoDB,
 PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = InnoDB);

두 가지 모두에 동일한 데이터를 삽입합니다.

INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);

결과적으로 분할된 데이터는 두 배 더 커집니다. 결과는 다음과 같습니다 SHOW TABLE STATUS.

*************************** 1. row ***************************
        Name: partitioned_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 5461
    Data_length: 32768
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: partitioned
        Comment: 
*************************** 2. row ***************************
        Name: simple_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: 
        Comment:

(값 확인 Data_length: 16384 대 32768)

조금 더 복잡하고 날짜당 수백만 개의 항목을 포함하는 테이블에서도 동일한 일이 발생합니다. 파티셔닝을 적용한 후에는 두 배 더 커집니다. 이는 테이블 상태와 실제 파일 크기에서 확인할 수 있습니다.

이와 같은 문제에 대한 정보를 찾을 수 없습니다. 왜 그런 일이 일어나고 있습니까?

UPD: 파티션 수를 10으로 변경하고 각 날짜에 대해 100개의 항목(두 테이블 각각에 대해 총 10000개)을 삽입하면 다음과 같은 결과가 나옵니다.

           Name: partitioned_table
    Data_length: 655360
...
           Name: simple_table
    Data_length: 344064

(삽입 후 몇 분 동안 값이 계속 변경되지만 결국 안정화됩니다.) 그리고 파일을 나열하면 다음과 같은 결과를 얻습니다.

-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180201.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180202.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180203.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180204.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180205.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180206.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180207.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180208.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180209.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180210.ibd
-rw-rw----  1 mysql mysql 409600 Feb 20 15:50 simple_table.ibd
-rw-rw----  1 mysql mysql    128 Feb 20 15:50 partitioned_table.par
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 partitioned_table.frm
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 simple_table.frm

답변1

문제의 원인을 찾았습니다. 이는 MySQL이 테이블 파일 증가를 위해 디스크 공간을 할당하는 방식으로 인해 발생합니다.

이제 여기에 있는 내 예가 내가 설명하려는 사례에 비해 그다지 좋지 않다는 것을 깨달았습니다. 그러나 원래 문제는 대규모 데이터베이스(수백만 개의 작은 행 포함)에 있었습니다.

나는 이것을 찾았다기사그 얘기는innodb_space이 도구를 사용하여 내 테이블이 대부분 빈 할당 페이지로 구성되어 있다는 사실을 발견했습니다. 이로 인해 이 MySQL이 생겼습니다.문서 페이지. 그것은 말한다:

페이지는 최대 16KB 크기의 페이지에 대해 1MB 크기의 범위로 그룹화됩니다.

그리고

테이블스페이스 내에서 세그먼트가 커지면 InnoDB는 처음 32페이지를 한 번에 하나씩 할당합니다. 그 후 InnoDB는 전체 익스텐트를 세그먼트에 할당하기 시작합니다. InnoDB는 추가할 수 있습니다최대 4개 범위데이터의 양호한 연속성을 보장하기 위해 한 번에 큰 세그먼트로 이동합니다.

이것은 내 테이블의 빈 페이지를 설명합니다. 결과적으로 .ibd파일은 실제 데이터보다 최대 몇 배 더 커질 수 있습니다.

근본 원인은 실제로 파티셔닝과 관련이 없지만 파티션 파일이 단일 파일 테이블보다 훨씬 작고 포함된 행 수가 적기 때문에 파티셔닝은 효과를 증폭시킵니다(특히 파일 크기가 여전히 꽤 큰 거의 비어 있는 파티션이 많은 경우). )

관련 정보