У меня есть две таблицы 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
Обнаружена причина проблемы - она вызвана тем, как MySQL выделяет дисковое пространство для растущих файлов таблиц.
Теперь я понимаю, что мой пример здесь был не очень хорош для случая, который я пытался проиллюстрировать, но моя первоначальная проблема была с большими базами данных (с миллионами крошечных строк)
я нашел это статья это говорит о innodb_space инструмент, с помощью которого я обнаружил, что мои таблицы в основном состоят из пустых выделенных страниц, что привело меня к этому MySQL страница документа. Он говорит:
Страницы сгруппированы в экстенты размером 1 МБ для страниц размером до 16 КБ.
и
Когда сегмент растет внутри табличного пространства, InnoDB выделяет ему первые 32 страницы по одной. После этого InnoDB начинает выделять сегменту целые экстенты. InnoDB может добавить до 4-х экстентов за один раз в большой сегмент, чтобы обеспечить хорошую последовательность данных.
Это объясняет пустые страницы в моих таблицах. В результате .ibd
файлы могут быть в несколько раз больше фактических данных.
Основная причина не имеет ничего общего с разбиением на разделы на самом деле, но разбиение на разделы усиливает эффект, потому что файлы разделов намного меньше и содержат меньше строк, чем однофайловые таблицы (особенно, если есть много почти пустых разделов, которые все еще имеют довольно большие размеры файлов. )