Я недавно изменил огромное MyISAM
таблица (80 миллионов записей) путем добавления нового значения в перечислимом поле. После того, как я сделал это, я проверил состояние таблицы на 2 ведомых серверах, которые используются для тиражирования главного сервера. Я заметил это MYI
файл намного меньше на одном из ведомых серверов. Я восстановил таблицу с myisamchk
и REPAIR
но размер индекса не изменился.
Посмотрите ниже результатов, возвращенных путем выполнения myisamchk -dvv
на этих 3 серверах для "Длины файла ключей":
Master Server
Auto increment key: 1 Last value: 80098340
Data records: 79375556 Deleted blocks: 0
Datafile parts: 79375556 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9635014668 **Keyfile length: 18945252352**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 1110
Slave Server 1
Auto increment key: 1 Last value: 80097611
Data records: 79374828 Deleted blocks: 0
Datafile parts: 79394418 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9635788652 **Keyfile length: 18024821760**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 1110
Slave Server 2 - Here the size of Keyfile is much smaller
Auto increment key: 1 Last value: 80098312
Data records: 79375002 Deleted blocks: 0
Datafile parts: 79375002 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 9634942908 **Keyfile length: 11092404224**
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Что могло возможно вызвать такую значительную разницу?
Если вы выполнили ремонт на MyISAM, он должен уменьшить размер MYI
. Почему?
Когда mysqldump создает и загружает таблицу MyISAM, подумайте о механических шагах для создания mytable
:
CREATE TABLE mytable ...
LOCK TABLE mytable ...
ALTER TABLE mytable DISABLE KEYS; (shuts off updates to non-unique indexes)
INSERT INTO ...
INSERT INTO ...
.
.
.
ALTER TABLE mytable ENABLE KEYS; (rebuild all indexes)
UNLOCK TABLES;
Во время ALTER TABLE ... ENABLE KEYS
вы запустите SHOW PROCESSLIST;
Вы увидите информацию об этом процессе, например, Ремонт путем сортировки
Когда закончите, у вас должен быть MYI с 95% дополнительных узлов BTREE, заполненных до отказа.
Почему так много места исчезает, когда вы его ремонтируете? Посмотрите на противоположный случай.
Вы загружаете таблицу в числовом порядке по некоторому идентификатору auto_increment. Загрузка данных в некотором порядке приводит к однобокому распределению ключей в индексах BTREE. В некоторых случаях все узлы могут быть фрагментированы до 45%.
ПРИМЕР: Если у вас есть двоичное дерево (худшее BTREE), загруженное по порядку, вы получите двоичное дерево, наклоненное до упора вправо, которое должно выглядеть как связанный список с отрицательным наклоном.
Я однажды упомянул это сумасшедшее явление в моих сообщениях DBA StackExchange
3 августа 2011 г.
: Когда мне следует перестраивать индексы? 28 июня 2012 г.
: Преимущества BTREE в MySQL 26 октября 2012 г.
: Насколько сильно innodb фрагментируется перед лицом некорректных вставок? Запуск REPAIR TABLE
в клиенте mysql, запуск myisamchk -r
или перезагрузка mysqldump MyISAM всегда должна приводить к уменьшению файла индекса MyISAM.