По умолчанию MySQL InnoDB хранит все таблицы всех БД в одном глобальном файле. Вы можете изменить это, установив innodb_file_per_table в конфигурации, которая затем создаст один файл данных для каждой таблицы.
Мне интересно почему innodb_file_per_table не включен по умолчанию. Есть ли недостатки в его использовании?
У меня есть полный ответ на этот вопрос.
однажды innodb_file_per_table установлен, и новые таблицы InnoDB могут быть уменьшены с помощью ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; Это сократит новые .ibd файлы ГАРАНТИРОВАННЫЕ.
Если ты бежишь ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; в таблице InnoDB, созданной до того, как вы использовали innodb_file_per_table, он извлечет данные и индексы для этой таблицы из файла ibdata1 и сохранит их в .ibd file, в результате в ibdata1 останется постоянный голубь, который нельзя будет использовать повторно.
В ibdata1 файл обычно содержит четыре типа информации
Pictorial Representation of ibdata1Вот гарантированный способ навсегда сжать файл ibdata1 ...
ШАГ 01) MySQL: Дамп всех баз данных в текстовый файл SQL (назовите его SQLData.sql)
ШАГ 02) Удалите все базы данных (кроме схем mysql, information_schema и performance_schema)
ШАГ 03) Завершение работы mysql
ШАГ 04) Добавьте следующие строки в /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
Примечание: независимо от того, какой у вас установлен innodb_buffer_pool_size, убедитесь, что innodb_log_file_size составляет 25% от innodb_buffer_pool_size.
На данный момент в / var / lib / mysql должна быть только схема mysql.
Это воссоздает ibdata1 на 10 МБ (не настраивайте этот параметр), ib_logfile0 и ib_logfile1 на 1 ГБ каждый
ibdata1 будет расти, но содержать только метаданные таблицы и прерывистые данные MVCC.
Каждая таблица InnoDB будет существовать вне ibdata1
Предположим, у вас есть таблица InnoDB с именем mydb.mytable. Если вы войдете в /var/lib/mysql/mydb, вы увидите два файла, представляющих таблицу
mytable.frm (Заголовок механизма хранения)mytable.ibd (Домашняя страница табличных данных и табличных индексов для mydb.mytable)ibdata1 никогда больше не будет содержать данные и индексы InnoDB.
С innodb_file_per_table вариант в /etc/my.cnf, Вы можете запустить OPTIMIZE TABLE mydb.mytable ИЛИ ALTER TABLE mydb.mytable ENGINE=InnoDB; и файл /var/lib/mysql/mydb/mytable.ibd фактически сжимается.
Я делал это много раз за свою карьеру администратора баз данных MySQL без единой проблемы после этого. Фактически, в первый раз, когда я сделал это, я свернул файл ibdata1 размером 50 ГБ в 50 МБ.
Попробуйте. Если у вас есть дополнительные вопросы по этому поводу, напишите мне. Доверьтесь мне. Это будет работать как в краткосрочной, так и в долгосрочной перспективе.
У меня есть предостережение в этом отношении, которое я обновил в других своих сообщениях, но я пропустил это: я немного обновляю свой ответ с помощью innodb_fast_shutdown потому что я для этого перезапускал mysql и останавливал mysql. Теперь этот одноэтапный шаг жизненно важен, потому что каждая незафиксированная транзакция может иметь другие движущиеся части внутри и за пределами журналов транзакций InnoDB (См. Инфраструктуру InnoDB).
Обратите внимание, что настройка innodb_fast_shutdown to 2 также очистит журналы, но все еще существует больше движущихся частей, которые выбираются при Crash Recovery во время запуска mysqld. Лучше всего установить 0.
Видеть ошибка.
Есть ли недостатки в его использовании?
Я всегда использую innodb_file_per_table для больших баз данных.
innodb_file_per_table по умолчанию включен в MariaDB.
Просто чтобы добавить немного информации
Начиная с mysql 5.6.6 он включен по умолчанию
Причина, по которой я решил не использовать innodb_file_per_table, потому что каждая таблица помещается в свой собственный файл, а это означает, что каждая таблица получает свои собственные, отдельные накладные расходы (подписи файлов и т. д.), что приводит к общему размеру MySQL каталог должен быть больше, чем при использовании общего табличного пространства. Кроме того, при наличии нескольких небольших файлов вместо одного большого из-за нехватки кластера возникает больше ненужного пространства.
Конечно, дополнительные накладные расходы не являются массивный по большому счету, особенно если вы используете большой диск или гигантскую базу данных, но для меня (и, вероятно, многих «домашних пользователей») все это складывалось и все равно было слишком большим для маленького диска с большие кластеры, в которых я держал хранилище MySQL.
Например, мое хранилище базы данных с моими базами данных WordPress и несколькими другими небольшими базами данных (phpBB, dev, некоторые тесты AMP и т. Д.), Преобразование в таблицу изменило его с 32 МБ на 50 МБ, и это даже не включая ibdata1 который все еще требует а минимум 10 МБ, в общей сложности по крайней мере 60 МБ.
Как я уже сказал, это может быть не слишком большой проблемой для некоторых людей, особенно для предприятий, но если вы домашний пользователь, который размещает только свой сайт, блог и т. Д., То это действительно может быть фактором в таких вещах, как выбор хост-провайдер, потому что многие хосты ограничивают размер вашей базы данных в дополнение к общему использованию диска.
с innodb_file_per_table = 1 таблица перетаскивания может замедляться Вот