Spec-Zone .ru
спецификации, руководства, описания, API
|
Если у Вас есть существующие таблицы, и приложения, которые используют их, в которых Вы хотите преобразовать
InnoDB
для лучшей надежности и масштабируемости, используйте следующие направляющие
линии и подсказки. Этот раздел предполагает, что большинство таких таблиц было первоначально MyISAM
, который был прежде значением по умолчанию.
MyISAM
, Использование Памяти увеличения для InnoDB
Поскольку Вы переходите далеко от MyISAM
таблицы, понизьте значение key_buffer_size
параметр конфигурации освободить память, больше необходимую для
того, чтобы кэшировать результаты. Увеличьте значение innodb_buffer_pool_size
параметр конфигурации, который выполняет подобную
роль выделения кэш-памяти для InnoDB
таблицы. InnoDB
кэши пула буферов и табличные
данные и индексируют данные, таким образом, это действительно удваивает режим работы в ускорении поисков для
запросов и хранения результатов запроса в памяти для повторного использования.
Выделите так много памяти этой опции, как можно предоставить, часто до 80 % физической памяти на сервере.
Если операционная система испытывает нехватку памяти для других процессов и
начинает подкачивать, уменьшать innodb_buffer_pool_size
значение. Свопинг является такой дорогой
работой, что это решительно уменьшает преимущество кэш-памяти.
Если innodb_buffer_pool_size
значение составляет несколько гигабайтов или
выше, рассмотрите увеличение значений innodb_buffer_pool_instances
. Выполнение так помогает на занятых
серверах, где много соединений читают данные в кэш одновременно.
На занятом сервере, выполненных сравнительных тестах с выключенным Кэшем Запроса.
InnoDB
пул буферов предоставляет подобные преимущества, таким образом, Кэш
Запроса мог бы связывать память излишне.
Поскольку MyISAM
таблицы не поддерживают транзакции, Вы,
возможно, не обратили много внимания на autocommit
параметр конфигурации и COMMIT
и ROLLBACK
операторы. Эти ключевые слова важны, чтобы позволить многократным
сеансам читать и писать InnoDB
таблицы одновременно, предоставляя существенные
преимущества масштабируемости в нагруженных записью рабочих нагрузках.
В то время как транзакция открыта, система сохраняет снимок данных как замечено в начале транзакции, которая может вызвать существенные издержки, если система вставляет, обновляет, и удаляет миллионы строк, в то время как беспризорная транзакция продолжает работать. Таким образом заботьтесь, чтобы избежать транзакций, которые работают слишком долго:
Если Вы используете mysql сеанс для интерактивных экспериментов, всегда COMMIT
(чтобы завершить изменения) или ROLLBACK
(чтобы отменить изменения) когда закончено. Закройте
интерактивные сеансы вместо того, чтобы оставить их открытыми в течение многих длительных периодов,
избежать сохранять транзакции открытыми в течение многих длительных периодов случайно.
Удостоверьтесь что любые обработчики ошибок в Вашем приложении также ROLLBACK
неполные изменения или COMMIT
завершенные изменения.
ROLLBACK
относительно дорогая работа, потому что INSERT
, UPDATE
, и DELETE
операции пишутся InnoDB
таблицы до COMMIT
, с ожиданием, что большинство изменений будет фиксироваться
успешно и откаты будут редки. Экспериментируя с большими объемами данных, избегите производить изменения
в больших количествах строк и затем откатывать те изменения.
Загружая большие объемы данных с последовательностью INSERT
операторы, периодически COMMIT
результаты избежать иметь транзакции это длится в течение многих
часов. В типичных операциях загрузки для организации хранилищ данных, если что-то идет не так, как надо,
Вы TRUNCATE TABLE
и запустите с начала вместо того, чтобы делать a ROLLBACK
.
Предыдущие подсказки сохраняют пространство памяти и дисковое пространство, которое может быть потрачено впустую
во время слишком длинных транзакций. Когда транзакции короче, чем они должны быть, проблемой является чрезмерный
ввод-вывод. С каждым COMMIT
,
MySQL удостоверяется, что каждое изменение безопасно записывается к диску, который включает некоторый
ввод-вывод.
Для большинства операций на InnoDB
таблицы, следует
использовать установку autocommit=0
. С точки зрения эффективности это избегает ненужного
ввода-вывода, когда Вы выпускаете большие количества последовательных INSERT
, UPDATE
,
или DELETE
операторы. С точки зрения безопасности это позволяет Вам проблеме a ROLLBACK
оператор, чтобы восстановить потерянные или искаженные
данные, если Вы делаете ошибку на mysql командной строке, или в обработчике исключений
в Вашем приложении.
Время, когда autocommit=1
является подходящим для InnoDB
таблицы, выполняя последовательность запросов для того, чтобы генерировать отчеты или проанализировать
статистику. В этой ситуации нет никакого штрафа ввода-вывода, связанного с COMMIT
или ROLLBACK
, и InnoDB
может автоматически
оптимизировать рабочую нагрузку только для чтения.
Если Вы делаете серию связанных изменений, завершаете все те изменения сразу с
синглом COMMIT
в конце. Например, если Вы вставляете связанные сведения в
несколько таблиц, сделайте сингл COMMIT
после произведения всех изменений. Или если Вы выполняете
многих последовательных INSERT
операторы, сделайте сингл COMMIT
после того, как все данные загружаются; если Вы делаете
миллионы INSERT
операторы, возможно разделяет огромную транзакцию, выходя a COMMIT
каждые десять тысяч или сто тысяч записей, таким образом,
транзакция не становится слишком большой.
Помните это даже a SELECT
оператор открывает транзакцию, таким образом, после выполнения
некоторого отчета или отладки запросов в интерактивном mysql сеансе, любой проблеме a COMMIT
или закройте mysql сеанс.
Вы могли бы видеть, что предупреждающие сообщения отнеслись к "мертвым блокировкам"
в журнале ошибок MySQL, или выводу SHOW ENGINE
INNODB STATUS
. Несмотря на страшно звучащее имя, мертвая блокировка не
является серьезной проблемой для InnoDB
таблицы, и часто не требуют никакого
корректирующего действия. Когда две транзакции начинают изменять многократные таблицы, получая доступ к таблицам
в различном порядке, они могут достигнуть состояния, где каждая транзакция ожидает другой, и ни один не может
продолжить. MySQL сразу обнаруживает это условие, и отмены (откатывает) "меньшую" транзакцию, позволяя другой
продолжиться.
Ваши приложения действительно нуждаются в логике обработки ошибок, чтобы перезапустить транзакцию, которая насильственно отменяется как это. Когда Вы переиздаете те же самые SQL-операторы как прежде, исходная проблема синхронизации больше не применяется: или другая транзакция уже закончилась, и Ваш может продолжиться, или другая транзакция все еще происходит, и Ваша транзакция ожидает, пока это не заканчивается.
Если предупреждения мертвой блокировки постоянно происходят, Вы могли бы рассмотреть код программы, чтобы
переупорядочить операции SQL непротиворечивым способом, или сократить транзакции. Можно протестировать с innodb_print_all_deadlocks
опция, позволенная видеть все предупреждения мертвой блокировки в журнале ошибок MySQL, а не только последнее
предупреждение в SHOW ENGINE INNODB STATUS
вывод.
Получить лучшую производительность от InnoDB
таблицы, можно скорректировать много
параметров, связанных с расположением хранения.
Когда Вы преобразовываете MyISAM
таблицы, которые являются большими, часто получали
доступ, и содержите жизненные данные, исследуйте и рассмотрите innodb_file_per_table
, innodb_file_format
, и innodb_page_size
параметры конфигурации, и ROW_FORMAT
и KEY_BLOCK_SIZE
пункты CREATE TABLE
оператор.
Во время Ваших начальных экспериментов самая важная установка innodb_file_per_table
. Включение этой опции прежде, чем создать новый InnoDB
таблицы гарантируют что InnoDB
системные
файлы табличной
области не выделяют дискового пространства постоянно для весь InnoDB
данные. С innodb_file_per_table
включенный, DROP TABLE
и TRUNCATE
TABLE
свободное пространство на диске, как Вы ожидали бы.
Преобразовать не -InnoDB
таблица, чтобы использовать InnoDB
использовать ALTER
TABLE
:
ALTER TABLE table_name
ENGINE=InnoDB;
Не преобразовывайте системные таблицы MySQL в mysql
база данных (такой
как user
или host
) к InnoDB
ввести. Это - недопустимая операция. Системные таблицы должны всегда
иметь MyISAM
ввести.
Вы могли бы сделать таблицу InnoDB, которая является клоном таблицы MyISAM, вместо того, чтобы делать ALTER
TABLE
преобразование, чтобы протестировать старую и новую таблицу бок о бок перед переключением.
Создайте пустое InnoDB
таблица с идентичным столбцом и индексирует определения.
Использовать show create table
видеть полное table_name
\GCREATE TABLE
оператор, чтобы использовать. Изменитесь ENGINE
пункт к ENGINE=INNODB
.
Передать большой объем данных в пустое InnoDB
таблица, составленная как показано в
предыдущем разделе, вставьте строки с INSERT INTO
.
innodb_table
SELECT * FROM myisam_table
ORDER BY primary_key_columns
Можно также создать индексирование для InnoDB
таблица после вставки данных.
Исторически, создание нового вторичного устройства индексирует, была медленная работа для InnoDB, но теперь
можно создать индексирование после того, как данные загружаются относительно небольшими издержками от шага
создания индекса.
Если Вы имеете UNIQUE
ограничения на вторичные ключи, можно ускорить табличный
импорт, выключая проверки уникальности временно во время работы импорта:
SET unique_checks=0;... import operation ...
SET unique_checks=1;
Для больших таблиц это сохраняет дисковый ввод-вывод потому что InnoDB
может
использовать его буфер вставки,
чтобы записать вторичный, индексируют записи как пакет. Будьте уверенны, что данные содержат, не делают
дубликаты ключа. unique_checks
разрешения, но не требуют, чтобы механизмы хранения, чтобы проигнорировать сделали дубликаты ключа.
Чтобы получить лучший контроль над процессом вставки, Вы могли бы вставить большие таблицы в части:
INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey >something
AND yourkey <=somethingelse
;
После того, как все записи были вставлены, можно переименовать таблицы.
Во время преобразования больших таблиц увеличьте размер InnoDB
пул буферов, чтобы
уменьшить дисковый ввод-вывод, к максимуму 80 % физической памяти. Можно также увеличить размеры InnoDB
файлы журнала.
Этой точкой, как уже упомянуто, Вы должны уже иметь innodb_file_per_table
включенная опция, так, чтобы, если Вы временно делаете
несколько копий своих данных в InnoDB
таблицы, можно восстановить все это дисковое
пространство, отбрасывая ненужные таблицы позже.
Преобразовываете ли Вы MyISAM
представьте в виде таблицы непосредственно или
создайте клонированный InnoDB
таблица, удостоверьтесь, что у Вас есть достаточное
дисковое пространство, чтобы содержать и старые и новые таблицы во время процесса. InnoDB
таблицы требуют большего дискового пространства чем MyISAM
таблицы. Если ALTER TABLE
работа исчерпывает пространство, она запускает откат, и это может
занять часы, если это ограничено диском. Для вставок, InnoDB
использует буфер
вставки, чтобы объединиться вторичный, индексируют записи на, индексирует в пакетах. Это сохраняет много
дискового ввода-вывода. Для отката не используется никакой такой механизм, и откат может взять в 30 раз дольше
чем вставка.
В случае безудержного отката, если у Вас нет ценных данных в Вашей базе данных, может быть желательно уничтожить
процесс базы данных, а не ожидать миллионов дисковых операций ввода-вывода, чтобы завершиться. Для полной
процедуры см. Раздел 14.2.4.6, "Запускаясь
InnoDB
на Поврежденной Базе данных".
PRIMARY KEY
для
Каждой ТаблицыPRIMARY KEY
пункт является критическим фактором, влияющим на производительность
запросов MySQL и использования пространства для таблиц, и индексирует. Возможно, Вы позвонили финансовому
учреждению, где Вас просят относительно номера счета. Если у Вас нет числа, Вас просят относительно дюжины
различных сведений "однозначно определить" себя.
Первичный ключ походит на тот уникальный номер счета, который позволяет Вам становиться прямыми вниз к бизнесу,
запрашивая или изменяя информацию в таблице. У каждой строки в таблице должно быть значение первичного ключа, и
ни у каких двух строк не может быть того же самого значения первичного ключа.
Вот некоторые направляющие линии для первичного ключа, сопровождаемого более подробными объяснениями.
Объявите a PRIMARY KEY
для каждой таблицы. Как
правило, это - самый важный столбец, к которому Вы обращаетесь в WHERE
пункты, ища единственную строку.
Объявите PRIMARY KEY
пункт в оригинале CREATE TABLE
оператор, вместо того, чтобы добавить это позже через ALTER TABLE
оператор.
Выберите столбец и его тип данных тщательно. Предпочтите числовые столбцы по символу или представьте в виде строки.
Рассмотрите использование столбца автоприращения, если нет другого устойчивого, уникального, ненулевого, числового столбца, чтобы использовать.
Столбец автоприращения является также хорошим выбором, если есть сомнение, могло ли бы значение столбца первичного ключа когда-либо изменяться. Изменение значения столбца первичного ключа является дорогой работой, возможно включая реконструкцию данных в пределах таблицы и в пределах каждого вторичного устройства индексирует.
Рассмотрите добавление первичного ключа к любой таблице, у которой уже нет того. Используйте самый маленький практический числовой тип, основанный на максимальном спроектированном размере таблицы. Это может сделать каждую строку немного более компактной, который может привести к существенным сбережениям пространства для больших таблиц. Сбережения пространства умножаются, если у таблицы есть какое-либо вторичное устройство, индексирует, потому что значение первичного ключа повторяется в каждом вторичном элементе индекса. В дополнение к сокращению размера данных на диске небольшой первичный ключ также позволяет большему количеству совпадения данных в пул буферов, ускоряя все виды операций и улучшая параллелизм.
Если у таблицы уже есть первичный ключ на некотором более длинном столбце, таком как a VARCHAR
,
рассмотрите добавление нового без знака AUTO_INCREMENT
столбец и переключение
первичного ключа к этому, даже если на тот столбец не ссылаются в запросах. Это изменение проекта может
произвести существенные сбережения пространства во вторичном устройстве, индексирует. Можно определять прежние
столбцы первичного ключа как UNIQUE NOT NULL
осуществлять те же самые ограничения
как PRIMARY KEY
пункт, то есть, чтобы предотвратить двойные или нулевые значения
через все те столбцы.
Если Вы распространяете соответствующую информацию через многократные таблицы, обычно каждая таблица использует тот же самый столбец для своего первичного ключа. Например, у базы данных персонала могло бы быть несколько таблиц, каждый с первичным ключом числа сотрудника. У базы данных о продажах могли бы быть некоторые таблицы с первичным ключом потребительского числа, и другие таблицы с первичным ключом порядкового номера. Поскольку поиски, используя первичный ключ очень быстры, можно создать эффективные запросы соединения для таких таблиц.
Если Вы уезжаете PRIMARY KEY
пункт полностью, MySQL создает невидимый для Вас. Это
- 6-байтовое значение, которое могло бы быть более длинным, чем Вы нуждаетесь, таким образом тратя впустую
пространство. Поскольку это скрывается, невозможно обратиться к этому в запросах.
Дополнительная надежность и функции масштабируемости InnoDB
действительно
потребуйте большей памяти на диске чем эквивалентный MyISAM
таблицы. Вы могли бы
изменить столбец и индексировать определения немного, для лучшего использования пространства, уменьшенного
ввода-вывода и потребления памяти, обрабатывая наборы результатов, и лучшие планы оптимизации запросов, делающие
эффективное использование, индексируют поиски.
Если Вы действительно устанавливаете числовой столбец ID для первичного ключа, используйте то значение, чтобы
перекрестно сослаться со связанными значениями в любых других таблицах, особенно для запросов соединения.
Например, вместо того, чтобы принять название страны как входной и сделать запросы, ищущие то же самое имя,
сделайте один поиск, чтобы определить ID страны, затем сделать другие запросы (или единственный запрос
соединения), чтобы искать релевантную информацию через несколько таблиц. Вместо того, чтобы хранить клиента или
номер изделия каталога как строка цифр, потенциально израсходовав несколько байтов, преобразуйте это в числовой
ID для хранения и запросов. 4 байта, без знака INT
столбец может индексировать более чем 4 миллиарда элементов (со значением
US миллиарда: 1000 миллионов). Для диапазонов различных целочисленных типов см. Раздел
11.2.1, "Целочисленные Типы (Точное Значение) - INTEGER
, INT
, SMALLINT
, TINYINT
, MEDIUMINT
, BIGINT
".
InnoDB
ТаблицыInnoDB
файлы требуют большей заботы и планирующий чем MyISAM
файлы делают:
Не следует удалить ibdata файлы, которые представляют
InnoDB
системная табличная область.
Копирование таблиц InnoDB от одного сервера до другого требует издания FLUSH TABLES ... FOR EXPORT
оператор сначала, и копирование
файл наряду с table_name
.cfg
файл.table_name
.ibd