CodeNet / Приложения / Базы данных / MySQL / Справочное руководство по MySQL версии 5.0.0-alpha.

Типы таблиц MySQL

В MySQL версии 3.23.6 можно было выбирать из трех основных форматов таблиц (ISAM, HEAP и MyISAM). Более новые версии MySQL могут поддерживать дополнительные типы таблиц (InnoDB или BDB) - в зависимости от варианта установки.

При создании новой таблицы можно указать MySQL, какой тип таблицы для нее использовать.

Для таблицы и определений столбцов MySQL всегда создает файл `.frm'. Индекс и данные хранятся в других файлах, в зависимости от типа таблиц.

Обратите внимание: если необходимо использовать таблицы InnoDB, при запуске следует указать параметр innodb_data_file_path. See section 7.5.2 Параметры запуска InnoDB.

Если попытаться воспользоваться таблицей, которая не была активизирована или добавлена при компиляции, MySQL вместо нее создаст таблицу типа MyISAM. Это очень полезная функция, когда необходимо произвести копирование таблиц с одного SQL-сервера на другой, а серверы поддерживают различные типы таблиц (например, при копировании таблиц на подчиненный компьютер, который оптимизирован для быстрой работы без использования транзакционных таблиц).

Тем не менее, такое автоматическое изменение таблиц может сбить с толку новых пользователей MySQL. Мы планируем устранить эту проблему путем введения предупреждений в новом клиент-серверном протоколе в MySQL 4.1, которые будут выводиться при автоматическом изменении типов таблиц.

Преобразовывать таблицы из одного типа в другой можно при помощи оператора ALTER TABLE. See section 6.5.4 Синтаксис оператора ALTER TABLE.

Обратите внимание на то, что MySQL поддерживает два различных типа таблиц: транзакционные (InnoDB и BDB) и без поддержки транзакций (HEAP, ISAM, MERGE и MyISAM).

Преимущества транзакционных таблиц (Transaction-safe tables, TST):

Надежность. Даже если произойдет сбой в работе MySQL или возникнут проблемы с оборудованием, свои данные вы сможете восстановить - либо методом автоматического восстановления, либо при помощи резервной копии и журнала транзакций.
Можно сочетать несколько операторов и принимать все эти операторы одной командой COMMIT.
Можно запустить команду ROLLBACK, чтобы отменить внесенные изменения (если работа не производится в режиме автоматической фиксации).
Если произойдет сбой во время обновления, все изменения будут восстановлены (в нетранзакционных таблицах все внесенные изменения не могут быть отменены).
Лучше обеспечивает параллелизм при одновременных обновлениях таблицы и чтении.

Обратите внимание, что для использования таблиц InnoDB вам как минимум следует указать опцию innodb_data_file_path. See section 7.5.2 Параметры запуска InnoDB.

Преимущества нетранзакционных таблиц (non-transaction-safe tables, NTST):

Работать с ними намного быстрее, так как не выполняются дополнительные транзакции.
Для них требуется меньше дискового пространства, так как не применяются дополнительные транзакции.
Для обновлений используется меньше памяти.

В операторах можно сочетать таблицы TST и NTST, чтобы взять лучшее от каждого типа.

7.1 Таблицы `MyISAM`

Тип таблиц MyISAM принят по умолчанию в MySQL версии 3.23. Он основывается на коде ISAM и обладает в сравнении с ним большим количеством полезных дополнений.

Индекс хранится в файле с расширением `.MYI' (MYIndex), а данные - в файле с расширением `.MYD' (MYData). Таблицы MyISAM можно проверять/восстанавливать при помощи утилиты myisamchk. See section 4.4.6.7 Использование myisamchk для послеаварийного восстановления. Таблицы MyISAM можно сжимать при помощи команды myisampack, после чего они будут занимать намного меньше места. See section 4.7.4 myisampack, MySQL-генератор сжатых таблиц (только для чтения).

Новшества, которыми обладает тип MyISAM:

Флаг в файле MyISAM, указывающий, правильно была закрыта таблица или нет. В случае запуска mysqld с параметром --myisam-recover таблицы MyISAM будут автоматически проверяться и/или восстанавливаться при открытии, если таблица была закрыта неправильно.
При помощи команды INSERT можно вставлять новые строки в таблицу, в середине файла данных которой нет свободных блоков, в то время как другие потоки считывают из таблицы информацию (совмещенная вставка). Свободный блок может быть получен при обновлении строки с динамической длиной, когда большее количество данных заменяется меньшим количеством или при удалении строк. Когда свободных блоков не остается, все последующие блоки снова будут вставляться как совмещенные.
Поддержка больших файлов (63 бита) в файловых/операционных системах, которые поддерживают большие файлы.
Хранение всех данных осуществляется с первым младшим байтом. Это делает данные независимыми от операционной системы. Единственное требование - в компьютере должны применяться дополненные до двух байтов целые числа со знаком (как и во всех компьютерах в последние 20 лет) и формат с плавающей единичной запятой IEEE (также использующийся в подавляющем большинстве серийных компьютеров). Единственными компьютерами, которые могут не поддерживать бинарную совместимость, являются встроенные системы (поскольку в них иногда применяются специальные процессоры). При хранении данных с первым младшим байтом не происходит снижения скорости. Обычно байты в строке таблицы не выровнены и нет большой разницы в том, как прочитать невыровненный байт - в прямой последовательности или в обратной. Фактическое время извлечения значения столбца также не критично по сравнению со временем выполнения остального кода.
Все ключи номеров хранятся с первым старшим байтом, чтобы сжатие индексов было более эффективным.
Внутренняя обработка столбца AUTO_INCREMENT. MyISAM автоматически обновляет его при выполнении команд INSERT/UPDATE. Значение AUTO_INCREMENT может быть обнулено оператором myisamchk. После этого столбец AUTO_INCREMENT будет быстрее (по крайней мере на 10%) и старые номера не будут повторно использоваться, как со старым ISAM. Обратите внимание: когда AUTO_INCREMENT задан в конце составного ключа, старое поведение все еще сохраняется.
При вставке в отсортированном порядке (как при использовании столбца AUTO_INCREMENT) дерево ключей будет разделено таким образом, чтобы верхний узел содержал только один ключ. При этом сокращается расход пространства памяти в дереве ключей.
Столбцы BLOB и TEXT могут быть проиндексированы.
В индексных столбцах разрешены значения NULL. Они занимают 0-1 байта на ключ.
По умолчанию максимальная длина ключа составляет 500 байтов (это значение может быть изменено при повторной компиляции). В случаях, когда ключи больше 250 байтов, для них используются большие размеры блока ключа, чем предусмотренные по умолчанию 1024 байта.
По умолчанию в таблице может быть не более 32 ключей. Это значение можно увеличить до 64 без повторной компиляции myisamchk.
myisamchk будет отмечать таблицы как проверенные, если они запускаются с параметром --update-state. myisamchk --fast будет проверять только те таблицы, в которых отсутствует данная пометка.
myisamchk -a сохраняет статистические данные по частям ключа (не только для ключей целиком, как в ISAM).
Строки с динамическим размером будут менее фрагментированными, чем при смешивании удалений с обновлениями и вставками. Это осуществляется путем автоматического сочетания удаленных смежных блоков и расширением блоков, если следующий блок удален.
myisampack может упаковывать столбцы BLOB и VARCHAR.
Можно поместить файл данных и файл индексов в разные каталоги, чтобы увеличить скорость (с параметром DATA/INDEX DIRECTORY="path" для CREATE TABLE). See section 6.5.3 Синтаксис оператора CREATE TABLE.

MyISAM также поддерживает следующие функции, которые можно будет использовать в MySQL в ближайшем будущем:

Поддержка типа VARCHAR; столбец VARCHAR начинается с длины, которая хранится в 2 байтах.
Таблицы с VARCHAR могут иметь фиксированную или динамическую длину записей.
VARCHAR и CHAR могут быть до 64 Кб длиной. У всех ключевых сегментов есть свои собственные определения языка. Это позволяет задавать в MySQL различные определения языка для каждого столбца.
Для UNIQUE может использоваться вычисленный хэш-индекс. Это позволяет использовать UNIQUE с любым сочетанием столбцов в таблице (тем не менее, нельзя производить поиск по вычисленному UNIQUE индексу).

Обратите внимание, что индексные файлы при использовании MyISAM обычно намного меньше в сравнении с ISAM. Это означает, что для MyISAM обычно задействуется меньше системных ресурсов, чем для ISAM, но больше загружается процессор при вставке данных в сжатый индекс.

Приведенные ниже параметры mysqld могут использоваться для изменения поведения таблиц MyISAM. See section 4.5.6.4 SHOW VARIABLES.

Параметр	Описание
`--myisam-recover=#`	Автоматическое восстановление таблиц после сбоя.
`-O myisam_sort_buffer_size=#`	При восстановлении таблиц используется буфер.
`--delay-key-write=ALL`	Не сбрасывать на диск ключевые буферы между записями для любых таблиц `MyISAM`
`-O myisam_max_extra_sort_file_size=#`	Используется, чтобы помочь MySQL выбрать, когда использовать медленный, но надежный метод создания индекса кэша ключей. Обратите внимание на то, что этот параметр задается в мегабайтах!
`-O myisam_max_sort_file_size=#`	Не использовать метод быстрой сортировки индекса для созданных индексов, если временный файл превысит этот размер. Обратите внимание на то, что этот параметр задается в мегабайтах!
`-O bulk_insert_buffer_size=#` Размер кэша дерева, используемого при оптимизации групповых вставок. Обратите внимание: это ограничение на поток!

Автоматическое восстановление активизируется при запуске mysqld с параметром --myisam-recover=# (see section 4.1.1 Параметры командной строки mysqld). Когда таблица открывается, производится проверка, не помечена ли она как сбойная, не равна ли переменная счетчика открытий таблицы нулю (0) и не производится ли запуск с параметром --skip-external-locking. Если хотя бы одно из этих условий выполняется, произойдет следующее:

Будет произведена проверка таблицы на наличие ошибок;
Если обнаружится ошибка, будет произведена попытка быстрого восстановления (с сортировкой и без повторного создания файла данных) таблицы;
Если восстановление не удалось из-за ошибки в файле данных (например, ошибка дублирующегося ключа), будет произведена вторая попытка, но на этот раз с повторным созданием файла данных.
Если восстановление не удастся, будет произведена еще одна попытка с применением старого метода восстановления (запись по строкам без сортировки), который обеспечивает устранение ошибок любого типа с использованием незначительных ресурсов диска.

Если не удается восстановить все строки из предыдущего выполненного оператора, и не был указан параметр FORCE для myisam-recover, автоматическое восстановление будет отменено со следующей ошибкой в файле ошибок:

Error: Couldn't repair table: test.g00pages

Если в этом случае был указан параметр FORCE, вместо вышеуказанного сообщения в файле ошибок будет присутствовать следующее предупреждение:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Обратите внимание: если запустить автоматическое восстановление с параметром BACKUP, необходимо установить скрипт cron, который автоматически перемещает файлы с именами `tablename-datetime.BAK' из каталогов базы данных на носитель резервного копирования.

See section 4.1.1 Параметры командной строки mysqld.

7.1.1 Пространство, необходимое для ключей

В MySQL могут поддерживаться различные типы индексов, однако обычно это тип ISAM или MyISAM. Для обоих типов используется индекс B-дерева, так что приблизительно вычислить размер индексного файла можно по формуле (длина ключа+4)/0.67, просуммированной по всем ключам (приведено значение для самого худшего случая, когда все ключи вставлены в порядке сортировки и сжатые ключи отсутствуют).

В индексах строк сжаты пробелы. Если первая часть индекса является строкой, префикс также будет сжат. Сжатие пробелов позволяет уменьшить индексный файл в сравнении со значениями, вычисляемыми по приведенной выше формуле, если столбец строки содержит много пробелов в конце строки или является столбцом VARCHAR, который не всегда используется на полную длину. Сжатие префикса используется с ключами, которые начинаются со строки. Сжатие префикса полезно в случае, если имеется много строк с одинаковыми префиксами.

В таблицах MyISAM можно также сжимать числа в префиксах, указывая при создании таблицы PACK_KEYS=1. Это полезно в случае, когда имеется много целочисленных ключей с одинаковыми префиксами, а числа хранятся с первым старшим байтом.

7.1.2 Форматы таблиц `MyISAM`

В MyISAM поддерживается три различных типа таблиц. Два из них выбираются автоматически, в зависимости от типа используемых столбцов. Третий - сжатые таблицы - может быть создан только при помощи инструмента myisampack.

При использовании с таблицами команд CREATE или ALTER для таблиц, у которых нет форсированной настройки BLOB, можно задать формат DYNAMIC или FIXED с параметром таблицы ROW_FORMAT=#. В будущем можно будет сжимать/разжимать таблицы, указывая ROW_FORMAT=compressed | default для ALTER TABLE. See section 6.5.3 Синтаксис оператора CREATE TABLE.

7.1.2.1 Характеристики статических таблиц (с фиксированной длиной)

Это формат, принятый по умолчанию. Он используется, когда таблица не содержит столбцов VARCHAR, BLOB или TEXT.

Данный формат - самый простой и безопасный, а также наиболее быстрый при работе с дисками. Скорость достигается за счет простоты поиска информации на диске: в таблицах статического формата с индексом для этого достаточно всего лишь умножить номер строки на ее длину.

Кроме того, при сканировании таблицы очень просто считывать постоянное количество записей при каждом чтении с диска.

Если произойдет сбой во время записи в файл MyISAM фиксированного размера, myisamchk в любом случае сможет легко определить, где начинается и заканчивается любая строка. Поэтому обычно удается восстановить все записи, кроме тех, которые были частично перезаписаны. Отметим, что в MySQL все индексы могут быть восстановлены. Свойства статических таблиц следующие:

Все столбцы CHAR, NUMERIC и DECIMAL расширены пробелами до ширины столбца;
Очень быстрые;
Легко кэшируются;
Легко восстанавливаются после сбоя, так как записи расположены в фиксированных позициях;
Не нуждаются в реорганизации (при помощи myisamchk), кроме случаев, когда удаляется большое количество записей и необходимо вернуть дисковое пространство операционной системе.
Для них обычно используется больше дискового пространства, чем для динамических таблиц.

7.1.2.2 Характеристики динамических таблиц

Данный формат используется для таблиц, которые содержат столбцы VARCHAR, BLOB или TEXT, а также если таблица была создана с параметром ROW_FORMAT=dynamic.

Это несколько более сложный формат, так как у каждой строки есть заголовок, в котором указана ее длина. Одна запись может заканчиваться более чем в одном месте, если она была увеличена во время обновления.

Чтобы произвести дефрагментацию таблицы, можно воспользоваться командами OPTIMIZE table или myisamchk. Если у вас есть статические данные, которые часто считываются/изменяются в некоторых столбцах VARCHAR или BLOB одной и той же таблицы, во избежание фрагментации эти динамические столбцы лучше переместить в другие таблицы. Свойства динамических таблиц следующие:

Все столбцы со строками являются динамическими (кроме тех, у которых длина меньше 4).
Перед каждой записью помещается битовый массив, показывающий, какие столбцы пусты ('') для строковых столбцов, или ноль для числовых столбцов (это не то же самое, что столбцы, содержащие значение NULL). Если длина строкового столбца равна нулю после удаления пробелов в конце строки, или у числового столбца значение ноль, он отмечается в битовом массиве и не сохраняется на диск. Строки, содержащие значения, сохраняются в виде байта длины и строки содержимого.
Обычно такие таблицы занимают намного меньше дискового пространства, чем таблицы с фиксированной длиной.
Для всех записей используется ровно столько места, сколько необходимо. Если размер записи увеличивается, она разделяется на несколько частей - по мере необходимости. Это приводит к фрагментации записей.
Если в строку добавляется информация, превышающая длину строки, строка будет фрагментирована. В этом случае для увеличения производительности можно время от времени запускать команду myisamchk -r. Чтобы получить статистические данные, воспользуйтесь командой myisamchk -ei tbl_name.
Восстановление после сбоя для таких таблиц является более сложным процессом, так как запись может быть фрагментированной и состоять из нескольких частей, а ссылка (или фрагмент) могут отсутствовать.
Предполагаемая длина строки для динамических записей вычисляется следующим образом:
```
3
+ (число столбцов+ 7) / 8
+ (число столбцов char)
+ размер числовых столбцов в упакованном виде
+ длина строк
+ (число столбцов NULL + 7) / 8
```
На каждую ссылку добавляется по 6 байтов. Динамические записи связываются при каждом увеличении записи во время обновления. Каждая новая ссылка занимает по крайней мере 20 байтов, поэтому следующее увеличение может произойти либо по этой же ссылке; либо по другой, если не хватит места. Количество ссылок можно проверить при помощи команды myisamchk -ed. Все ссылки можно удалить при помощи команды myisamchk -r.

7.1.2.3 Характеристики сжатых таблиц

Таблицы этого тип предназначены только для чтения. Они генерируются при помощи дополнительного инструмента myisampack (pack_isam для таблиц ISAM):

Все дистрибутивы MySQL, даже выпущенные до предоставления общедоступной лицензии MySQL, могут читать таблицы, которые были сжаты при помощи myisampack.
Сжатые таблицы занимают очень мало дискового пространства; таким образом при применении данного типа значительно снижается использование дискового пространства. Это полезно при работе с медленными дисками (такими как компакт-диски).
Каждая запись сжимается отдельно (незначительные издержки при доступе). Заголовки у записей фиксированные (1-3 байта), в зависимости от самой большой записи в таблице. Все столбцы сжимаются по-разному. Ниже приведено описание некоторых типов сжатия:
- Обычно для каждого столбца используются разные таблицы Хаффмана.
- Сжимаются пробелы суффикса.
- Сжимаются пробелы префикса.
- Для хранения чисел со значением 0 отводится 1 бит.
- Если у значений в целочисленном столбце небольшой диапазон, столбец сохраняется с использованием минимального по размерам возможного типа. Например, столбец BIGINT (8 байт) может быть сохранен как столбец TINYINT (1 байт) если все значения находятся в диапазоне от 0 до 255.
- Если в столбце содержится небольшое множество возможных значений, тип столбца преобразовывается в ENUM.
- Столбец может содержать сочетание указанных выше сжатий.
Для таблиц этого типа возможна обработка записей с фиксированной или динамической длиной.
Таблицы данного типа могут быть распакованы при помощи команды myisamchk.

7.1.3 Проблемы с таблицами `MyISAM`.

Формат файлов, который используется для хранения данных в MySQL, тщательно тестировался, но всегда существуют обстоятельства, которые могут привести к повреждениям таблиц баз данных.

7.1.3.1 Повреждения таблиц `MyISAM`

Несмотря на то, что формат таблиц MyISAM очень надежен (все изменения в таблице записываются до возвращения значения оператора SQL), таблица, тем не менее, может быть повреждена. Такое происходит в следующих случаях:

Процесс mysqld уничтожен во время осуществления записи;
Неожиданное отключение компьютера (например, если выключилось электропитание);
Ошибка аппаратного обеспечения;
Использование внешней программы (например myisamchk) на открытой таблице.
Ошибка программного обеспечения в коде MySQL или MyISAM.

Типичные признаки поврежденной таблицы следующие:

Во время выбора данных из таблицы выдается ошибка Incorrect key file for table: '...'. Try to repair it.
Запросы не находят в таблице строки или выдают неполные данные.

Проверить состояние таблицы можно при помощи команды CHECK TABLE. См. раздел See section 4.4.4 Синтаксис CHECK TABLE.

Для восстановления поврежденного файла можно применить команду REPAIR TABLE. See section 4.4.5 Синтаксис REPAIR TABLE. Таблицу можно восстановить и в случае, когда не запущен mysqld, при помощи команды myisamchk. See section 4.4.6.1 Синтаксис запуска myisamchk.

Если таблицы повреждены значительно, необходимо выяснить причину произошедшего! See section A.4.1 Что делать, если работа MySQL сопровождается постоянными сбоями.

Сначала следует определить, послужил ли причиной повреждения таблицы сбой mysqld (это можно легко проверить, просмотрев последние строки restarted mysqld в файле ошибок mysqld). Если дело не в этом, то необходимо составить подробное описание произошедшего. See section D.1.6 Создание контрольного примера при повреждении таблиц.

7.1.3.2 Clients is using or hasn't closed the table properly

Клиенты неправильно используют таблицу или не закрыли ее надлежащим образом

В заголовке каждого файла MyISAM `.MYI' имеется счетчик, который может использоваться для проверки правильности закрытия таблицы.

Если при выполнении команд CHECK TABLE или myisamchk выдается следующая ошибка:

# clients is using or hasn't closed the table properly

значит, нарушена синхронность счетчика. Это не означает, что таблица повреждена, но необходимо произвести проверку и убедиться, что все в порядке.

Счетчик работает следующим образом:

Во время первого обновления таблицы в MySQL значение счетчика в заголовках индексных файлов увеличивается.
Во время следующих обновлений значение счетчика не изменяется.
После закрытия последней записи таблицы (после применения команды FLUSH или из-за отсутствия места в кэше таблицы) значение счетчика уменьшается, если в таблицу были внесены изменения.
Если производится проверка таблицы, или проверка показывает, что все в порядке, счетчик устанавливается в значение 0.
Чтобы избежать пересечения с другими процессами, которые могут проверять таблицу, при закрытии значение счетчика не уменьшается, если счетчик установлен в значение 0.

Иначе говоря, синхронность может быть нарушена следующим образом:

Таблицы MyISAM копируются без команд LOCK и FLUSH TABLES.
Между обновлением и последним закрытием произошел сбой MySQL (обратите внимание: с таблицей все может быть в порядке, так как MySQL документирует все изменения между выполнением каждого из операторов).
Кто-то применил команду myisamchk --recover или myisamchk --update-state к таблице, которая в данный момент использовалась mysqld.

Таблицу используют несколько серверов mysqld, и один из них выполнил команду REPAIR или CHECK по отношению к таблице, с которой работал другой сервер. В этом случае можно выполнить команду CHECK (даже если другие серверы выдают предупреждения), но команды REPAIR следует избегать, так как она заменяет файл данных новым, информация о котором не передается другим серверам.

7.2 Таблицы `MERGE`

Таблицы MERGE (объединение) являются новшеством версии MySQL 3.23.25. В настоящее время код находится еще на стадии разработки, но, тем не менее, должен быть достаточно стабилен.

Таблица MERGE (или таблица MRG_MyISAM) представляет собой совокупность идентичных таблиц MyISAM, которые могут использоваться как одна таблица. К совокупности таблиц можно применять только команды SELECT, DELETE и UPDATE. Если же попытаться применить к таблице MERGE команду DROP, она подействует только на определение MERGE.

Обратите внимание на то, что команда DELETE FROM merge_table без параметра WHERE очищает только распределение для таблицы, но ничего не удаляет из распределенных таблиц (мы планируем исправить это в версии 4.1).

Под идентичными таблицами подразумеваются таблицы, созданные с одинаковой структурой и ключами. Нельзя объединять таблицы, в которых столбцы сжаты разными методами или не совпадают, либо ключи расположены в другом порядке. Тем не менее, некоторые таблицы можно сжимать при помощи команды myisampack. See section 4.7.4 myisampack, MySQL-генератор сжатых таблиц (только для чтения).

При создании таблицы MERGE будут образованы файлы определений таблиц `.frm' и списка таблиц `.MRG'. Файл `.MRG' содержит список индексных файлов (файлы `.MYI'), работа с которыми должна осуществляться как с единым файлом. Все используемые таблицы должны размещаться в той же базе данных, что и таблица MERGE.

На данный момент по отношению к таблицам, которые необходимо преобразовать в таблицу MERGE,необходимо обладать привилегиями SELECT, UPDATE и DELETE.

Ниже перечислены возможности, которые обеспечивают таблицы MERGE:

Простое управление набором файлов журналов. Например, можно поместить данные за различные месяцы в отдельные файлы, сжать некоторые из них при помощи myisampack, а затем создать таблицу MERGE, чтобы использовать их как одну таблицу.
Увеличение скорости работы. Большую таблицу можно разделить по некоторому критерию, а затем поместить различные части таблицы на разные диски. В этом случае таблица MERGE может обрабатываться намного быстрее, чем обычная большая таблица (можно, конечно, воспользоваться дисковым массивом RAID, чтобы получить те же преимущества).
Более эффективный поиск. Если точно известно, что вы ищете, можно производить поиск по определенным запросам только в одной из составляющих таблицу MERGE таблиц, одновременно используя таблицу MERGE для других запросов. Можно даже иметь несколько активных таблиц MERGE (возможно, с перекрывающимися файлами).
Более простое восстановление. Гораздо легче восстановить отдельные файлы, которые преобразованы в файл MERGE, чем пытаться восстановить действительно большой файл.
Быстрая обработка большого количества файлов как одного. Для таблицы MERGE используются индексы отдельных таблиц; поддерживать для нее один большой индекс нет необходимости. Благодаря этому создание или изменение таблиц MERGE осуществляется ОЧЕНЬ быстро. Обратите внимание на то, что при создании таблицы MERGE необходимо указывать определения ключей!
Если требуется объединить несколько таблиц в одну большую таблицу по требованию или при формировании, лучше создать для них по требованию таблицу MERGE. Это намного быстрее и позволит сэкономить дисковое пространство.
Таблицы MERGE позволяют обходить ограничения на размер файлов в операционных системах.
Можно создать псевдоним/синоним для таблицы - для этого нужно просто применить MERGE к одной таблице. Заметного падения производительности при этом наблюдаться не будет (только пара непрямых вызовов и вызовы memcpy() при каждом чтении).

Недостатки таблиц MERGE:

Для создания таблицы MERGE можно использовать только идентичные таблицы MyISAM.
Не работает команда REPLACE.
Для таблиц MERGE используется больше дескрипторов файлов. Если применяется таблица MERGE, преобразованная из более чем 10 таблиц, к которым получают доступ 10 пользователей, то используется 10*10 + 10 дескрипторов файлов (10 файлов данных для 10 пользователей и 10 общих индексных файлов).
Ключи считываются медленнее. При чтении ключа обработчику MERGE необходимо прочитать все базовые таблицы, чтобы выяснить, какая из них больше всего соответствует указанному ключу. Если после этого выполнить команду ``читать следующий'', то обработчик объединенной таблицы должен будет просмотреть буферы чтения, чтобы найти следующий ключ. Только по завершении использования одного буфера ключей обработчику понадобится прочитать следующий блок ключей. В связи с этим ключи MERGE дают большое замедление при поиске eq_ref, однако не такое значительное при поиске ref. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Нельзя выполнять команды DROP TABLE, ALTER TABLE, DELETE FROM table_name без оператора WHERE REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, или ANALYZE TABLE по отношению к таблицам, которые размещены в таблице MERGE и открыты. Если это сделать, в таблице MERGE останутся ссылки на исходную таблицу, и полученные результаты будут совершенно непредсказуемыми. Самый легкий путь обойти эти трудности - выполнить комманду FLUSH TABLES. Это удостоверит, что ни одна таблица MERGE не будет открытой.

При создании таблицы MERGE необходимо указать при помощи UNION(list-of-tables), какие таблицы требуется использовать как одну. В случае необходимости, если требуется производить вставку в таблицу MERGE в первую или в последнюю таблицу в списке UNION, можно задать INSERT_METHOD. Если не указать INSERT_METHOD или выбрать NO, то все команды INSERT для таблицы MERGE будут выдавать ошибку.

В приведенном ниже примере показано, как использовать таблицы MERGE:

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20))
TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Кроме того, можно управлять файлом `.MRG', находясь за пределами сервера MySQL:

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables

Теперь можно выполнять следующие действия:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Обратите внимание на то, что столбец a, хотя и объявлен как PRIMARY KEY, не является уникальным, так как таблица MERGE не может обеспечивать уникальность для всех таблиц MyISAM.

Чтобы повторно преобразовать таблицу MERGE, можно выбрать один из следующих вариантов:

Применить к таблице команду DROP и создать ее повторно
Воспользоваться командой ALTER TABLE table_name UNION(...)
Изменить файл `.MRG' и выполнить команду FLUSH TABLE над таблицей MERGE и всеми базовыми таблицами, чтобы обработчик прочитал новый файл определения.

7.2.1 Проблемы при работе с таблицами `MERGE`

При работе с таблицами MERGE могут возникать следующие проблемы:

Для таблицы MERGE не могут поддерживаться ограничения UNIQUE по всей таблице. При выполнении команды INSERT данные помещаются в первую или последнюю таблицу (в соответствии с INSERT_METHOD=xxx) и для этой таблицы MyISAM обеспечивается однозначность данных, но ей ничего не известно об остальных таблицах MyISAM.
Команда DELETE FROM merge_table без оператора WHERE очищает только распределение для таблицы, ничего не удаляя из преобразованных таблиц.
Использование команды RENAME TABLE над активной таблицей MERGE может привести к повреждению таблицы. Эта ошибка будет исправлена в MySQL 4.0.x.
При создании таблицы типа MERGE не проверяется совместимость типов базовых таблиц. Создав таблицу MERGE на основе несовместимых типов, вы можете столкнуться с непредсказуемыми проблемами.
Если для первого добавления индекса UNIQUE в таблицу, преобразованную в MERGE, используется команда ALTER TABLE, а затем командой ALTER TABLE в таблицу MERGE добавляется нормальный индекс, порядок ключей для таблиц будет разным, если в таблице был старый не однозначный ключ. Это происходит потому, что команда ALTER TABLE помещает ключи UNIQUE перед нормальными ключами, чтобы как можно раньше обнаружить дублирующиеся ключи.
Оптимизатор диапазона пока не может эффективно использовать таблицу MERGE, в связи с чем иногда возникают неоптимальные соединения. Это будет исправлено в MySQL 4.0.x.

Команда DROP TABLE над таблицей, преобразованной в таблицу MERGE, не будет работать под Windows, так как обработчик MERGE скрывает распределение таблиц от верхнего уровня MySQL. Поскольку в Windows не разрешается удалять открытые файлы, сначала необходимо сбросить на диск все таблицы MERGE (при помощи команды FLUSH TABLES) или удалить таблицу MERGE перед тем, как удалить таблицу. Эту ошибку мы планируем исправить одновременно с введением VIEW.

7.3 Таблицы `ISAM`

В MySQL пока еще можно применять и устаревший тип таблиц ISAM. В ближайшем времени этот тип будет исключен (возможно, в MySQL 5.0), так как MyISAM является улучшенной реализацией тех же возможностей. В таблицах ISAM используется индекс B-tree. Индекс хранится в файле с расширением `.ISM', а данные - в файле с расширением `.ISD'. Таблицы ISAM можно проверять/восстанавливать при помощи утилиты isamchk (see section 7.1 Таблицы MyISAM).

Ниже перечислены свойства таблиц ISAM:

Ключи со сжатой и фиксированной длиной
Фиксированная и динамическая длина записи
16 ключей с 16 частями ключей/ключами
Максимальная длина ключа 256 (по умолчанию)
Данные хранятся в машинном формате; благодаря этому обеспечивается скорость, но возникает зависимость от компьютера/ОС.

Большинство параметров таблиц MyISAM также соответствуют таблицам ISAM. See section 7.1 Таблицы MyISAM. Ниже перечислены основные отличия таблиц ISAM от MyISAM:

Таблицы ISAM не являются переносимыми в двоичном виде с одной ОС/платформы на другую;
Невозможна работа с таблицами > 4Гб.
В строках поддерживается только сжатие префикса.
Ограничения по маленьким ключам.
Динамические таблицы больше фрагментируются.
Таблицы сжимаются при помощи pack_isam, а не при помощи myisampack.

Если вы хотите преобразовать таблицу ISAM в таблицу MyISAM, чтобы иметь возможность работать с такими утилитами, как mysqlcheck, воспользуйтесь оператором ALTER TABLE:

mysql> ALTER TABLE tbl_name TYPE = MYISAM;

Встроенные версии MySQL не поддерживают таблицы ISAM.

7.4 Таблицы `HEAP`

Для HEAP-таблиц используются хэш-индексы; эти таблицы хранятся в памяти. Благодаря этому обработка их осуществляется очень быстро, однако в случае сбоя MySQL будут утрачены все данные, которые в них хранились. Тип HEAP очень хорошо подходит для временных таблиц!

Для внутренних HEAP-таблиц в MySQL используется 100%-ное динамическое хэширование без областей переполнения; дополнительное пространство для свободных списков не требуется. Отсутствуют при использовании HEAP-таблиц и проблемы с командами удаления и вставки, которые часто применяются в хэшированных таблицах:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
    -> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

При использовании HEAP-таблиц необходимо обращать внимание на следующие моменты:

Необходимо всегда указывать параметр MAX_ROWS в операторе CREATE, чтобы случайным образом не занять всю память.
Индексы будут использоваться только с = и <=> (но ОЧЕНЬ быстрые).
В HEAP-таблицах для поиска строки могут использоваться только полные ключи, в то время как для таблиц MyISAM при поиске строк может применяться любой префикс ключа.
Для HEAP-таблиц используется формат с фиксированной длиной записи.
Для HEAP-таблиц не поддерживаются столбцы формата BLOB/TEXT.
Для HEAP-таблиц не поддерживаются столбцы формата AUTO_INCREMENT.
До версии 4.0.2 для HEAP-таблиц не поддерживаются индексы в столбцах формата NULL.
В HEAP-таблицах могут встречаться совпадающие ключи (что не является нормой для хэшированных таблиц).
HEAP-таблицы используются совместно всеми клиентами (как и все другие таблицы).
Нельзя производить поиск следующей записи в порядке следования (т.е. использовать индекс в команде ORDER BY).
Данные HEAP-таблиц расположены в маленьких блоках. Таблицы на 100% являются динамическими (при вставке). Нет необходимости ни в областях переполнения, ни в дополнительных ключах. Удаленные строки помещаются в связанный список и используются при вставке в таблицу новых данных.
Следует позаботиться о том, чтобы имелось достаточное количество дополнительной памяти для всех HEAP-таблиц, которые будут использоваться одновременно,.
Чтобы освободить память, необходимо запустить команду DELETE FROM heap_table, TRUNCATE heap_table или DROP TABLE heap_table.
MySQL не может подсчитать, сколько строк находится между двумя значениями (используется оптимизатором диапазонов для выбора используемого индекса). Это может повлиять на некоторые запросы, если преобразовать таблицу MyISAM в формат HEAP.
При создании размер таблицы HEAP не может превышать max_heap_table_size; это сделано для того, чтобы обеспечить защиту от случайных неквалифицированных действий.

Количество памяти, необходимой для одной строки в HEAP-таблице, вычисляется следующим образом:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) составляет 4 на 32-разрядных компьютерах и 8 - на 64-разрядных.

7.5 Таблицы `InnoDB`

7.5.1 Обзор таблиц InnoDB

Таблицы InnoDB в MySQL снабжены обработчиком таблиц, обеспечивающим безопасные транзакции (уровня ACID) с возможностями фиксации транзакции, отката и восстановления после сбоя. Для таблиц InnoDB осуществляется блокировка на уровне строки, а также используется метод чтения без блокировок в команде SELECT (наподобие применяющегося в Oracle). Перечисленные функции позволяют улучшить взаимную совместимость и повысить производительность в многопользовательском режиме. В InnoDB нет необходимости в расширении блокировки, так как блоки строк в InnoDB занимают очень мало места. Для таблиц InnoDB поддерживаются ограничивающие условия FOREIGN KEY.

InnoDB предназначается для получения максимальной производительности при обработке больших объемов данных. По эффективности использования процессора этот тип намного превосходит другие модели реляционных баз данных с памятью на дисках.

Технически InnoDB является завершенной системой управления базой данных в рамках MySQL. В InnoDB есть свой собственный буферный пул для кэширования данных и индексов в основной памяти. Таблицы и индексы InnoDB хранятся в специальном пространстве памяти, которое может состоять из нескольких файлов. В этом заключается отличие InnoDB от, например, таблиц MyISAM: каждая таблица MyISAM хранится в отдельном файле. Таблицы InnoDB могут быть любого размера даже в тех операционных системах, где установлено ограничение файла в 2 Гб.

Свежую информацию по InnoDB можно найти на http://www.innodb.com/. Здесь же находится последняя версия руководства по InnoDB. Кроме того, можно заказать коммерческие лицензии и поддержку для InnoDB.

В настоящий момент (октябрь 2001 года) таблицы InnoDB применяются на нескольких больших сайтах баз данных, для которых важна высокая производительность. Так, таблицы InnoDB используются на популярном сайте новостей Slashdot.org. Формат InnoDB применяется для хранения более 1Тб данных компании Mytrix, Inc; можно привести пример еще одного сайта, где при помощи при помощи InnoDB обрабатывается средняя нагрузка объемом в 800 вставок/обновлений в секунду.

Таблицы InnoDB входят в дистрибутив исходных текстов MySQL, начиная с версии 3.23.34a; они активизированы в исполняемом коде MySQL -Max. Для Windows исполняемые коды -Max находятся в стандартном дистрибутиве.

Если вы загрузили исполняемую версию MySQL, которая включает поддержку InnoDB, следует просто выполнить инструкции руководства MySQL по установке исполняемой версии MySQL. В случае, если у вас уже установлен MySQL-3.23, проще всего установить MySQL -Max, чтобы заменить исполняемый файл `mysqld' соответствующим файлом из дистрибутива -Max. Различными в MySQL и MySQL -Max являются только исполняемые файлы сервера. См. разделы section 2.2.10 Установка бинарного дистрибутива MySQL и See section 4.7.5 mysqld-max, расширенный сервер mysqld.

Чтобы произвести компиляцию MySQL с поддержкой InnoDB, загрузите MySQL-3.23.34a или более новую версию с http://www.mysql.com/ и настройте MySQL при помощи параметра --with-innodb. См. раздел руководства MySQL по установке дистрибутива исходного кода MySQL, See section 2.3 Установка исходного дистрибутива MySQL.

cd /path/to/source/of/mysql-3.23.37
./configure --with-innodb

Чтобы использовать InnoDB, необходимо указать параметры запуска InnoDB в своем файле `my.cnf' или `my.ini'. Самый простой способ внести изменения - добавить в раздел [mysqld] строку

innodb_data_file_path=ibdata:30M

Однако чтобы добиться высокой скорости работы, лучше указать рекомендуемые параметры. See section 7.5.2 Параметры запуска InnoDB.

InnoDB распространяется на условиях общедоступной лицензии версии 2 (от июня 1991 года). В дистрибутиве исходного кода MySQL InnoDB находится в подкаталоге `innobase'.

7.5.2 Параметры запуска InnoDB

Чтобы использовать таблицы InnoDB в MySQL-Max-3.23, НЕОБХОДИМО задать параметры конфигурации в разделе [mysqld] файла конфигурации `my.cnf' или в файле параметров Windows `my.ini'.

В версии 3.23 как минимум необходимо указать имя и размер файлов данных в innodb_data_file_path. Если вы не указали innodb_data_home_dir в `my.cnf' по умолчанию эти файлы создаются в директории данных MySQL. Если вы указали innodb_data_home_dir как пустую строку, то вы должны указать полный путь к вашим файлам данным в innodb_data_file_path. В MySQL 4.0 не требуется задавать даже innodb_data_file_path: по умолчанию для него создается автоматически увеличивающийся файл размером в 10 Мб с именем `ibdata1' в каталоге `datadir' MySQL. (в MySQL-4.0.0 и 4.0.1 размер файла данных составляет 64 Мб и он не является автоматически увеличивающимся).

Если вы не хотите использовать InnoDB таблицы, вы можете добавить опцию skip-innodb в конфигурационный файл MySQL.

Однако для того, чтобы получить высокую производительность, НЕОБХОДИМО явно задать параметры InnoDB, перечисленные в следующих примерах.

Начиная с версий 3.23.50 и 4.0.2 для InnoDB имеется возможность задавать последний файл данных в innodb_data_file_path как автоматически увеличивающийся. В этом случае для innodb_data_file_path используется следующий синтаксис:

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
... ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

Если последний файл данных указан с параметром автоматического увеличения, то в случае нехватки места для табличной области InnoDB будет увеличивать последний файл данных; приращение файла каждый раз составляет 8 Мб. Например, синтаксис:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

указывает InnoDB создать один файл данных с начальным размером 100 Мб, который будет увеличиваться на 8 Мб каждый раз, когда не будет хватать места. Если текущий диск окажется заполненным, можно, к примеру, добавить еще один файл данных на другой диск. При задании размера автоматически увеличивающегося файла `ibdata1' следует округлить его текущий размер до ближайшего числа, кратного 1024 * 1024 байтам (= 1 Мб), и явно указать округленный размер `ibdata1' в innodb_data_file_path. После этой записи можно добавить еще один файл данных:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Следует соблюдать осторожность при работе в файловых системах, в которых установлено ограничение на размер файла в 2 Гб! Максимальный для данной операционной системы размер файла InnoDB не известен. В таком случае желательно указать максимальный размер файла данных:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

Простой пример файла `my.cnf'. Предположим, что у вас есть компьютер с 128 Мб ОЗУ и одним жестким диском. Ниже приведены примеры возможных параметров конфигурации в `my.cnf' или `my.ini' для InnoDB. Мы предполагаем что у вас запущен MySQL-Max-3.23.50 и выше или MySQL-4.0.2 и выше. Этот пример подходит для большинства пользователей работающих под Unix и Windows, которые не хотят располагать файлы данных и журнальные файлы на различных дисках. В этом примере создается автоматически увеличивающийся файл `ibdata1' и два журнальных файла `ib_logfile0' и `ib_logfile1' в в директории данных MySQL (обычно `/mysql/data'). Небольшой архивный журнальный файл InnoDB `ib_arch_log_0000000000' также располагается в каталоге datadir:

[mysqld]
# Сюда  можно  добавить  другие  опции MySQL
# ...
#
# Файлы  данных  должны  иметь  достаточно
# места  для  сохранения  ваших  данных  и
# индексов. Убедитесь что у вас достаточно 
# свободного места на диске.
innodb_data_file_path = ibdata1:10M:autoextend
# Размер  буферного  пула  следует  задавать
# как 50 - 80% памяти  компьютера
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Размер  файла  журналов  должен  составлять
# около 25% от  размера  буферного  пула
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
# Если  допустима  потеря  некоторых
# последних  транзакций, установите
# flush_log_в_trx_commit в 0
innodb_flush_log_at_trx_commit=1

Убедитесь, что MySQL server имеет права создавать файлы в datadir.

Не забывайте, что в некоторых файловых системах существует ограничение в 2 Гб на размер файла данных! Общий размер файлов журналов должен быть меньше 4 Гб, а общий размер файлов данных - больше или равен 10Мб.

При первом создании базы данных InnoDB лучше всего запустить сервер MySQL из командной строки. Тогда на экран будет выводиться информация о создании базы данных и вы сможете увидеть, что происходит. Смотрите следующий раздел, в котором описано, на что должна быть похожа выводимая информация. Например, в Windows можно запустить `mysqld-max.exe' с параметрами:

your-path-to-mysqld>mysqld-max --console

Где поместить файл `my.cnf' или `my.ini' в Windows? Для Windows существуют следующие правила:

Должен быть создан только один файл `my.cnf' или `my.ini'.
Файл `my.cnf' должен находиться в корневом каталоге диска `C:'.
Файл `my.ini' должен находиться в каталоге `WINDIR', например в `C:\WINDOWS' или `C:\WINNT'. Чтобы вывести значение `WINDIR', можно воспользоваться командой SET операционной системы MS-DOS.
Если на вашем компьютере применяется загрузчик операционной системы, в котором диск `C:' не является загрузочным, то необходимо использовать файл `my.ini'.

Где указываются параметры в Unix?

В Unix mysqld считывает параметры из следующих файлов, если они существуют, в таком порядке:

`/etc/my.cnf' - общие параметры.
`COMPILATION_DATADIR/my.cnf' -- параметры для сервера.
`defaults-extra-file' -- файл, указанный при помощи --defaults-extra-file=....
`~/.my.cnf' - параметры для пользователя.

COMPILATION_DATADIR представляет собой каталог данных MySQL, который был указан как параметр ./configure при компиляции mysqld (обычно `/usr/local/mysql/data' для установки исполняемых файлов или `/usr/local/var' для установки исходного кода).

Если точно не известно, откуда mysqld считывает свои файлы `my.cnf' или `my.ini', можно задать путь как первый параметр командной строки для сервера: mysqld --defaults-file=your_path_to_my_cnf.

InnoDB формирует полный путь к файлу данных путем соединения innodb_data_home_dir и имени файла данных или пути в innodb_data_file_path, добавляя при необходимости косую черту. Если ключевое слово innodb_data_home_dir не упоминается в `my.cnf' совсем, по умолчанию используется директория данных MySQL.

Пример расширенного файла `my.cnf'

Предположим, что у вас есть компьютер Linux с 2 Гб ОЗУ и тремя жесткими дисками по 60 Гб (с путями каталогов ``/'', ``/dr2'' and ``/dr3''). Ниже приведен пример возможных параметров конфигурации в `my.cnf' для InnoDB:

Учтите, что InnoDB не создает директории: вы должны создать их самостоятельно. Используйте комманду Unix или MS-DOS mkdir для создания домашней директории для InnoDB и домашней директории для группы журнальных файлов.

[mysqld]
# Здесь  вы  можете  задать  другие  опции  сервера MySQL
# ...
#
innodb_data_home_dir = 
# Файлы  данных  должны  иметь  достаточно
# места  для  сохранения  ваших  данных  и
# индексов
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
# Размер  буферного  пула  следует  задавать
# как 50 - 80% памяти  компьютера, но
# для Linux x86 следует  убедиться, что
# общий  расход  памяти  не  превышает 2 Гб
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
# .._log_arch_dir должен  быть  такой
# же, как _log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=3
# Размер  файла  журналов  должен  составлять
# около 15 % от  размера  буферного  пула
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
# Если  допустима  потеря  некоторых
# последних  транзакций, установите
# flush_log_в_trx_commit в 0
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Обратите внимание на то, что два файла данных размещены на разных дисках. InnoDB будет заполнять табличное пространство, образованное файлами данных, снизу вверх. В некоторых случаях это позволяет увеличить производительность баз данных, если не все данные размещены на одном физическом диске. Размещение файлов журнала на другом диске очень часто позволяет повысить производительность. Для файлов данных можно также использовать разделы реального диска (реальные устройства): в некоторых версиях Unix это ускоряет операции ввода/вывода. Информацию по указанию параметров в `my.cnf' вы найдете в разделе руководства по управлению пространством файлов InnoDB.

Предупреждение: в Linux x86 необходимо соблюдать осторожность, чтобы не установить слишком высокое значение использования памяти. glibc может дать процессу разрастись и превысить стеки потоков, что приведет к сбою сервера. Степень риска значительно повышается, если значение

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB

приближается к значению 2 Гб или превышает его. Каждый поток будет использовать стек (обычно 2 Мб, но в бинарной поставке MySQL AB только 256 Кб) и в худшем случае также дополнительную память sort_buffer + read_buffer_size.

Как настроить другие параметры сервера mysqld? Для большинства пользователей подходят следующие стандартные значения:

skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
# Размер key_buffer следует  задавать  как
# 5%-50% от  вашего  ОЗУ, в  зависимости
# от  того, как  часто  у  вас  используются
# таблицы MyISAM, но key_buffer +
# размер  буферного  пула InnoDB не  должен
# превышать 80% вашего  ОЗУ
set-variable = key_buffer=...

Обратите внимание на то, что некоторые параметры представлены при помощи формата числового параметра `my.cnf': set-variable = innodb... = 123, а остальные (строчные и логические параметры) - при помощи другого формата: innodb_... = ....

Параметры настройки имеют следующие значения:

Параметр	Описание
`innodb_data_home_dir`	Общая часть пути к каталогу всех файлов данных InnoDB. Если вы не указали этот параметр в `my.cnf', то по умолчанию для этого параметра принято значение `datadir` MySQL. Если вы указали как пустою строку, то в этом случае вы можете использовать полный путь в `innodb_data_file_path`.
`innodb_data_file_path`	Пути к отдельным файлам данных и их размеры. Полный путь к каталогу каждого файла данных получается путем объединения `innodb_data_home_dir` с путем, который задается данным параметром. Размеры файлов указаны в мегабайтах. InnoDB также ``понимает'' сокращение 'G', 1 G означает 1024 М. Начиная с версии 3.23.44 в операционных системах, где поддерживаются большие файлы, можно задавать размер файла, превышающий 4 Гб. В некоторых операционных системах файлы должны быть < 2 Гб. Общий объем файлов должен быть как минимум 10 Мб.
`innodb_mirrored_log_groups`	Количество идентичных копий групп журналов, которые хранятся для базы данных. На данный момент этому параметру должно быть присвоено значение 1.
`innodb_log_group_home_dir`	Путь к каталогу файлов журналов InnoDB.
`innodb_log_files_in_group`	Количество файлов журналов в группе журналов. InnoDB производит запись в файлы по круговому способу. Для этого параметра рекомендуется установить значение "3".
`innodb_log_file_size`	Размер каждого файла журнала в группе журналов (указывается в мегабайтах). Разумный диапазон значений составляет от 1М до 1/N от размера буферного пула, приведенного ниже, где N - количество файлов журналов в группе. Чем больше это значение, тем меньше требуется сбросов на диск информации из буферного пула, что сокращает количество дисковых операций ввода/вывода. Однако в случае сбоя восстановление при больших размерах файлов журналов займет больше времени. Общий размер файлов журналов на 32-разрядных компьютерах должен быть < 4 Гб.
`innodb_log_buffer_size`	Размер буфера, который в InnoDB используется для записи информации файлов журналов на диск. Разумный диапазон значений составляет от 1М до 8М. Большой буфер журналов позволяет осуществлять объемные транзакции без записи журнала на диск до завершения транзакции. Поэтому если ваши транзакции отличаются значительными объемами, увеличение буфера журналов сократит количество операций ввода/вывода диска.
`innodb_flush_log_at_trx_commit`	Обычно этому параметру присваивается значение 1; при этом значении после завершения транзакции информация журнала записывается на диск и фиксируются изменения, внесенные транзакцией, благодаря чему данные сохраняются в случае сбоя базы данных. Если у вас выполняется большое количество маленьких транзакций и вы готовы пожертвовать такой возможностью, можно установить значение этого параметра в 0, чтобы снизить количество обращений к диску.
`innodb_log_arch_dir`	Каталог, в котором будут храниться заполненные файлы журналов, если включено архивирование журналов. Значение этого параметра на настоящий момент должно задаваться таким же, как и для `innodb_log_group_home_dir`.
`innodb_log_archive`	На данный момент значение этого параметра должно устанавливаться в 0. Поскольку восстановление из резервной копии MySQL осуществляет при помощи своих собственных файлов журналов, архивировать файлы журналов InnoDB нет необходимости.
`innodb_buffer_pool_size`	Размер буфера памяти, который InnoDB использует для кэширования данных и индексов своих таблиц. Чем больше это значение, тем меньше обращений к диску осуществляется при получении доступа к данным таблиц. На специально выделенном сервере баз данных этот параметр можно установить в значение до 80% физической памяти компьютера. Однако для этого параметра не следует задавать слишком большое значение, так как при недостатке физической памяти операционная система будет вынуждена сбрасывать часть информации на диск.
`innodb_additional_mem_pool_size`	Размер пула памяти, который InnoDB использует для хранения информации словаря данных и других внутренних структур данных. Разумным значением для этого параметра может быть 2М, но чем больше таблиц в вашем приложении, тем больше информации нужно будет разместить в этом пуле. Если памяти в этом пуле будет недостаточно для InnoDB, то будет выделятся память операционной системы, а в файл журнала MySQL будут записываться предупреждающие сообщения.
`innodb_file_io_threads`	Количество потоков ввода/вывода файлов в InnoDB. Обычно этому параметру присваивается значение 4, но в Windows при помощи увеличения данного значения можно сократить количество обращений к диску.
`innodb_lock_wait_timeout`	Время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать блокировки прежде, чем будет произведен откат. InnoDB автоматически обнаруживает зависшие транзакции в своей таблице блокировок и производит откат транзакций. Если в той же самой транзакции используется команда `LOCK TABLES`, или другие обработчики таблиц с безопасными транзакциями, отличными от InnoDB, то может возникнуть зависание, которое не будет обнаружено InnoDB. В таких ситуациях параметр времени простоя помогает устранить проблему.
`innodb_flush_method` (Доступен, начиная с версий 3.23.40 и выше). По умолчанию для этого параметра принято значение `fdatasync`. Другой возможный вариант - `O_DSYNC`.

7.5.3 Создание табличной области InnoDB

Предположим, что вы установили MySQL и внесли в файл `my.cnf' необходимые параметры настройки InnoDB. Прежде чем запустить MySQL, необходимо убедиться, что указанные каталоги для файлов данных и журналов InnoDB существуют, и что у вас есть право доступа к этим каталогам. InnoDB может создавать только файлы, но не каталоги. Проверьте также, достаточно ли у вас свободного дискового пространства для файлов данных и журналов.

Теперь при запуске MySQL InnoDB начнет создавать ваши файлы данных и файлы журналов. При этом будет выводиться примерно такая информация:

~/mysqlm/sql > mysqld
InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to
5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to
5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to
5242880
InnoDB: Started
mysqld: ready for connections

Сейчас была создана новая база данных InnoDB. К серверу MySQL вы можете подключиться при помощи обычных клиентских программ MySQL, таких как mysql. Если работа сервера MySQL завершается при помощи команды mysqladmin shutdown, InnoDB выведет примерно следующее:

010321 18:33:34 mysqld: Normal shutdown
010321 18:33:34 mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed

Теперь можно просмотреть каталоги файлов данных и журналов, чтобы увидеть, какие файлы были созданы. В каталоге журналов будет также находиться небольшой файл ib_arch_log_0000000000. Этот файл появляется в результате создания базы данных, после чего InnoDB отключает архивирование журналов. При новом запуске MySQL будет выведена примерно следующая информация:

~/mysqlm/sql > mysqld
InnoDB: Started
mysqld: ready for connections

7.5.3.1 Если во время создания базы данных что-то происходит не так

Если InnoDB выдает ошибку операционной системы во время операции с файлом, то причиной возникшей проблемы, как правило, является одна из следующих:

У mysqld нет прав на создание файлов в этих каталогах.
mysqld не считал нужный файл `my.cnf' или `my.ini' и, соответственно, не получил указанных вами параметров.
Диск переполнен или превышена квота использования диска.
Вы создали подкаталог, имя которого совпадает с указанным файлом данных.
Синтаксическая ошибка в innodb_data_home_dir или innodb_data_file_path.

Если что-то происходит не так во время создания базы данных InnoDB, необходимо удалить все файлы, созданные InnoDB. В их число входят все файлы данных, все файлы журналов, небольшой архивный файл журнала; если вы уже создали какие-либо таблицы InnoDB, то следует также удалить соответствующие им файлы `.frm', которые находятся в каталогах баз данных MySQL. После этого можно попробовать создать базу данные InnoDB еще раз.

7.5.4 Создание таблиц InnoDB

Предположим, что у вас запущен клиент MySQL при помощи команды mysql test. Чтобы создать таблицу в формате InnoDB, необходимо в команде создания таблицы SQL указать TYPE = InnoDB:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

Эта команда SQL создаст таблицу и индекс в столбце A табличной области InnoDB. Кроме того, MySQL создаст файл `CUSTOMER.frm' каталоге баз данных MySQL с именем `test'. В свой собственный словарь данных InnoDB добавит запись для таблицы test/CUSTOMER. Таким образом, можно создать таблицу с таким же именем CUSTOMER в другой базе данных MySQL, и это не приведет к конфликту имен таблиц в рамках InnoDB.

Для любой таблицы, которая была создана с параметром TYPE = InnoDB, можно запросить количество свободного пространства в табличной области InnoDB. Для этого нужно выполнить команду запроса состояния таблицы. Количество свободного пространства будет выводиться в разделе примечаний к таблице в выходной информации команды SHOW.

Например:

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Обратите внимание на то, что статистические данные, которые команда SHOW выдает по таблицам InnoDB, являются приблизительными: они используются для оптимизации SQL. Точными являются зарезервированные размеры таблицы и индекса, значения которых выдаются в байтах.

7.5.4.1 Преобразование таблиц MyISAM в формат InnoDB

В InnoDB отсутствует специальная оптимизация создания отдельных индексов. Таким образом, этот формат не обеспечивает экспорта и импорта таблиц с последующим созданием индексов. Самый быстрый способ преобразовать таблицу в формат InnoDB - напрямую вставить данные в таблицу InnoDB, воспользовавшись командой ALTER TABLE ... TYPE=INNODB, или создать пустую таблицу InnoDB с такой же структурой и вставить строки при помощи команды INSERT INTO ... SELECT * FROM ....

Чтобы лучше контролировать процесс вставки, большие таблицы желательно вставлять по частям:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

После того, как все данные будут вставлены, таблицы можно будет переименовать.

Во время преобразования больших таблиц необходимо задать достаточно большой размер динамического буфера InnoDB, чтобы снизить количество дисковых операций ввода/вывода. Однако размер буфера не должен превышать 80% физической памяти компьютера. Следует установить большие размеры для файлов журналов InnoDB, а также большой размер буфера журналов.

Убедитесь, что у вас достаточно свободного пространства для табличной области: таблицы InnoDB занимают намного больше места, чем таблицы MyISAM. Если во время выполнения команды ALTER TABLE будет исчерпано свободное дисковое пространство, начнется выполнение отката, и это может занять несколько часов, если диск заполнен. Во время вставок для таблицы InnoDB используется буфер вставки, чтобы произвести объединение вторичных индексных записей с индексными таблицами при помощи групповых операций. Это позволяет значительно снизить интенсивность дисковых операций ввода/вывода. При откате такой механизм не используется, поэтому откат может занять в 30 раз больше времени, чем вставка.

В случае, если началось выполнение отката и база данных не содержит ценной информации, лучше прервать этот процесс и удалить все данные InnoDB, файлы журналов, а также все таблицы InnoDB (файлы с расширением `.frm'), и начать свою работу сначала, а не ждать завершения выполнения миллионов операций ввода/вывода диска.

7.5.4.2 Ограничения внешнего ключа

Начиная с версии 3.23.43b, в InnoDB включены ограничения внешних ключей. InnoDB - первый формат таблиц MySQL, который обеспечивает возможность задавать ограничения внешнего ключа, чтобы обеспечить целостность данных.

Синтаксис задания ограничения внешнего ключа в InnoDB следующий:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
                    REFERENCES table_name (index_col_name, ...)
                    [ON DELETE {CASCADE | SET NULL | NO ACTION
                                | RESTRICT}]
                    [ON UPDATE {CASCADE | SET NULL | NO ACTION
                                | RESTRICT}]

Обе таблицы должны быть InnoDB-типа; обязательно также наличие индекса, в котором внешний ключ и ссылочный ключ должны находиться в ПЕРВЫХ столбцах. Для таблиц InnoDB индексы по внешним ключам или ссылочным ключам не создаются автоматически: их создание требуется задавать явно.

Соответствующие столбцы внешнего и ссылочного ключей в таблице InnoDB должны содержать одинаковые типы данных, чтобы их можно было сравнивать без преобразования типов. Размер и знак целочисленных типов должны быть одинаковыми. Длины для строковых типов могут не совпадать.

Если вы указали действие SET NULL, убедитесь что вы не объявили столбец в дочерней таблице как NOT NULL.

Если оператор MySQL CREATE TABLE выдает ошибку с номером 1005, и в строке сообщения об ошибке присутствует ссылка на ошибку с номером 150, то произошел сбой создания таблицы из-за того, что ограничения внешнего ключа не были сформированы надлежащим образом. Аналогично и для оператора ALTER TABLE: если происходит ошибка при выполнении оператора и в сообщении присутствует ссылка на ошибку с номером 150, то определение внешнего ключа для преобразовываемой таблицы сформировано неправильно.

Начиная с версии 3.23.50 с ограничением внешнего ключа можно также связывать выражения ON DELETE CASCADE или ON DELETE SET NULL. Начиная с версии 4.0.8 вы можете это же использовать с ON UPDATE.

Если указано выражение ON DELETE CASCADE и строка в родительской таблице удалена, то в формате InnoDB все эти строки автоматически удаляются также и из дочерней таблицы, значения внешнего ключа которой равны значениям ссылочного ключа в строке родительской таблицы. Если указано выражение ON DELETE SET NULL, строки дочерней таблицы автоматически обновляются, поэтому столбцам во внешнем ключе также присваивается значение SQL NULL.

Начиная с версии 3.23.50 в InnoDB не осуществляется проверка ограничений внешних ключей на наличие значений внешних или родительских ключей, которые содержат столбец NULL.

Начиная с версии 3.23.50 синтаксический анализатор InnoDB обеспечивает возможность использовать обратные кавычки (`), ограничивающие имена таблиц и столбцов в FOREIGN KEY ... REFERENCES ..., однако синтаксический анализатор InnoDB еще ``не знает'' об опции lower_case_table_names, которая может быть задана в файле `my.cnf'.

Пример:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
	     FOREIGN KEY (parent_id) REFERENCES parent(id)
	     ON DELETE SET NULL
) TYPE=INNODB;

Начиная с версии 3.23.50, для таблиц InnoDB обеспечивается возможность добавлять новые ограничения внешних ключей для таблиц при помощи

ALTER TABLE yourtablename
  ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)

Однако не следует забывать предварительно создавать необходимые индексы. В InnoDB версий < 3.23.50 команды ALTER TABLE или CREATE INDEX не должны использоваться совместно с таблицами, для которых установлены ограничения внешнего ключа или на которые есть ссылки в ограничениях внешних ключей: Команда ALTER TABLE удаляет все ограничения внешних ключей, определенные в таблице. Не следует использовать команду ALTER TABLE для таблиц, на которые есть ссылки; вместо этого необходимо применять команды DROP TABLE и CREATE TABLE, чтобы изменить логическую структуру. При выполнении команды ALTER TABLE MySQL может использовать команду RENAME TABLE, что нарушит ограничения внешнего ключа, относящиеся к таблице. Оператор CREATE INDEX в MySQL обрабатывается таким же образом, как и ALTER TABLE, поэтому приведенные выше ограничения распространяются и на этот оператор.

При проверке внешних ключей для таблиц InnoDB устанавливается совместно используемая блокировка строк на подлежащих просмотру родительских или дочерних записях. Проверка ограничений внешнего ключа для таблиц InnoDB производится немедленно и не откладывается до принятия транзакции.

Формат InnoDB обеспечивает возможность удалить любую таблицу, даже если это нарушит ограничения внешнего ключа, ссылающегося на таблицу. При удалении таблицы также удаляются ограничения, определенные оператором ее создания.

Если удаленная таблица создается повторно, ее определение должно быть согласовано с ограничениями внешнего ключа, который на нее ссылается. В этой таблице необходимо правильно задать имена и типы столбцов; в ней также должны присутствовать индексы ключей, на которые производится ссылка, как указано выше. Если эти условия не будут выполнены, MySQL выдаст ошибку с номером 1005 и ссылку на ошибку с номером 150 в строке сообщения об ошибке.

Начиная с версии 3.23.50 InnoDB возвращает определения внешних ключей таблицы, если вызвать

SHOW CREATE TABLE yourtablename

Помимо этого, mysqldump выводит корректные определения таблиц в файл дампа, ``не забывая'' о внешних ключах.

Список ограничений внешнего ключа таблицы T можно также вывести при помощи команды

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

Ограничения внешнего ключа выводятся в комментариях к таблице.

7.5.5 Добавление и удаление файлов данных и журналов InnoDB

Начиная с версий 3.23.50 и 4.0.2, можно указать последний файл данных InnoDB как autoextend. Можно также увеличить табличную область, указав дополнительные файлы данных. Для этого необходимо остановить сервер MySQL, внести изменения в файл `my.cnf', добавив новый файл данных к innodb_data_file_path, а затем запустить сервер MySQL снова.

На данный момент нельзя удалить файл данных из InnoDB. Чтобы уменьшить размер своей базы данных, необходимо воспользоваться mysqldump, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.

Если необходимо изменить количество или размер файлов журналов InnoDB, необходимо остановить MySQL и убедиться, что работа была завершена без ошибок. После этого нужно скопировать старые файлы журналов в безопасное место - на случай, если завершение работы было произведено с ошибками и потребуется восстановление базы данных. Затем следует удалить старые файлы журналов из каталога файлов журналов, внести изменения в `my.cnf' и снова запустить MySQL. InnoDB при запуске сообщит о создании новых файлов журналов.

7.5.6 Создание резервных копий и восстановление баз данных InnoDB

Чтобы обеспечить безопасное управление базами данных, необходимо регулярно создавать резервные копии.

Существует интерактивный инструмент, который можно использовать для создания резервных копий своих баз данных InnoDB, когда они открыты, - InnoDB Hot Backup. Для своей работы InnoDB Hot Backup не требует закрытия базы данных, блокировки данных или нарушения обычного хода обработки базы данных. InnoDB Hot Backup является платным дополнительным инструментом, не входящим в стандартный дистрибутив MySQL. Чтобы получить дополнительную информацию о нем и просмотреть копии экрана, см. домашнюю страницу InnoDB Hot Backup http://www.innodb.com/hotbackup.php.

Если у вас есть возможность остановить сервер MySQL, а затем создать двоичную резервную копию своей базы данных, необходимо выполнить следующие действия:

Закройте свою базу данных MySQL и убедитесь, что закрытие было произведено без ошибок.
Скопируйте все свои файлы данных в безопасное место.
Скопируйте все свои файлы журналов InnoDB в безопасное место.
Скопируйте свой файл конфигурации `my.cnf' в безопасное место.
Скопируйте все файлы `.frm' своих таблиц InnoDB в безопасное место.

В дополнение к двоичным резервным копиям, описанным выше, необходимо также регулярно создавать дампы своих таблиц при помощи mysqldump. Дело в том, что повреждение двоичного файла человеку заметить сложно. Дампы таблиц сохраняются в текстовых файлах, которые могут прочитать люди и структура которых намного проще двоичных файлов базы данных. Увидеть повреждение таблицы в файле дампа легче, и благодаря простоте этого формата вероятность серьезного повреждения данных меньше.

Дампы лучше всего создавать одновременно с созданием двоичной резервной копии своей базы данных. Чтобы получить согласованную копию всех своих таблиц в дампах, необходимо запретить всем клиентам доступ к базе данных. Затем можно создать двоичную резервную копию и получить согласованные копии своей базы данных в двух форматах.

Чтобы восстановить исходное состояние своей базы данных InnoDB из описанной выше двоичной резервной копии, необходимо запустить свою базу данных MySQL с включенными общим журналом и архивацией журналов MySQL (здесь под общим журналом подразумевается механизм занесения записей в журнал сервера MySQL, независимый от журналов InnoDB).

Единственное, что нужно сделать для восстановления процесса MySQL после сбоя, - перезапустить его. InnoDB автоматически произведет проверку журналов и выполнит восстановление базы данных, а также автоматически произведет откат по незавершенным транзакциям, которые проводились на момент сбоя. Во время восстановления InnoDB будет выводить примерно следующую информацию:

~/mysqlm/sql > mysqld
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

Если ваша база данных повреждена или произошел сбой диска, необходимо произвести восстановление из резервной копии. В случае повреждения сначала необходимо найти целую резервную копию, а из резервной копии произвести восстановление по файлам общего журнала MySQL в соответствии с инструкциями руководства MySQL.

7.5.6.1 Контрольные точки

В InnoDB реализован механизм контрольных точек, который получил название нечеткой контрольной точки. В InnoDB измененные страницы базы данных сбрасываются из буфера на диск небольшими частями. Сбрасывать содержимое буфера одним большим пакетом нет необходимости, так как это приведет к временной остановке обработки операторов пользователей.

В случае восстановления после сбоя InnoDB производит поиск меток контрольных точек, записанных в файлы журналов. Известно, что все изменения базы данных, внесенные перед меткой, уже записаны в образ базы данных на диске. Затем InnoDB производит сканирование файлов журналов начиная от места контрольной точки, и вносит зафиксированные изменения в базу данных.

Запись в файлы журналов в InnoDB осуществляется по круговому методу. Все внесенные изменения, после которых страницы базы данных в буфере начинают отличаться от образа на диске, должны быть записаны в файлы журналов, на случай, если InnoDB понадобится произвести восстановление. Это означает, что когда InnoDB начинает повторно использовать файл журнала по круговому методу, производится проверка на наличие в образах страниц базы данных на диске изменений, зафиксированных в файле журнала, который InnoDB собирается повторно использовать. Иначе говоря, необходимость поставить контрольную точку зачастую приводит к тому, что InnoDB сбрасывает измененные страницы базы данных на диск.

Из сказанного выше становится понятно, почему при больших файлах журналов сокращается количество дисковых операций ввода/вывода при создании контрольных точек. Иногда имеет смысл задавать общий размер файлов журналов равным буферному пулу или даже больше. Недостатком больших файлов журналов является то, что восстановление после сбоя может длиться дольше, так как к базе данных придется применить больше информации из файла журнала.

7.5.7 Перенесение базы данных InnoDB на другой компьютер

Файлы данных и журналов InnoDB на двоичном уровне совместимы на всех платформах, если на компьютерах совпадает формат чисел с плавающей десятичной запятой. Базу данных InnoDB можно перенести, просто скопировав все относящиеся к ней файлы (список которых был приведен в предыдущем разделе, посвященном созданию резервных копий базы данных). Если компьютеры имеют различные форматы чисел с плавающей десятичной запятой, но типы данных FLOAT или DOUBLE в ваших таблицах не задействованы, последовательность действий остается точно такой же: нужно просто скопировать все относящиеся к базе данных файлы. Если же при наличии различных форматов в ваших таблицах содержатся данные с плавающей десятичной запятой, то для перемещения таких таблиц необходимо воспользоваться командами mysqldump и mysqlimport.

Чтобы увеличить скорость обработки, можно отключить автоматическую фиксацию транзакций при импортировании в свою базу данных, исходя из предположения, что ваша табличная область содержит достаточно пространства для отката большого сегмента на случай генерации большой транзакции импортирования. Фиксация производится только после импорта всей таблицы или сегмента таблицы.

7.5.8 Транзакционная модель InnoDB

Назначение транзакционной модели InnoDB заключается в том, чтобы совместить лучшие свойства многовариантной базы данных и традиционной двухфазной блокировки. Для таблиц InnoDB осуществляется блокировка на уровне строки и запросы по умолчанию запускаются как целостное считывание без блокировок, подобно тому, как это реализовано в Oracle. Хранение таблицы блокировок InnoDB организовано настолько экономично, что нет необходимости в расширении блокировки: обычно несколько пользователей могут блокировать любую строку или любой набор строк в базе данных, не занимая всю память, доступную для InnoDB.

В таблицах InnoDB все действия пользователей осуществляются при помощи транзакций. Если в MySQL используется режим автоматической фиксации, то для каждого оператора SQL будет создаваться отдельная транзакция. MySQL всегда открывает новое соединение с включенным режимом автоматической фиксации.

Если режим автоматической фиксации отключен при помощи SET AUTOCOMMIT = 0, то мы предполагаем, что у пользователя постоянно имеется открытая транзакция. Если он выполняет оператор SQL COMMIT или ROLLBACK, которые завершают текущую транзакцию, сразу же запускается новая транзакция. Оба упомянутых оператора снимают все блокировки InnoDB, которые были установлены во время выполнения текущей транзакции. Оператор COMMIT означает, что изменения, внесенные во время выполнения текущей транзакции, принимаются и становятся видимыми для других пользователей. Оператор ROLLBACK отменяет все изменения, внесенные текущей транзакцией.

Если в соединении установлено AUTOCOMMIT = 1, то пользователь, тем не менее, может использовать транзакции, начиная их с BEGIN и заканчивая при помощи COMMIT или ROLLBACK.

В терминах описания уровней изоляции транзакций (SQL-1992), InnoDB по умолчанию использует REPEATABLE READ. Начиная с версии 4.0.5, InnoDB предлагает все 4 уровня изоляции описанные в стандарте SQL-1992. Вы можете установить уровень изоляции по умолчанию для всех соединений в секции [mysqld] файла `my.cnf':

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

Пользователь может изменить уровень изоляции для отдельно взятой сессии или нового соединения таким образом:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Обратите внимания что названия уровней изоляции пишется без дефиса в SQL-выражении. Если вы указали ключевое слово GLOBAL в указанном выше выражении, оно будет определять уровень изоляции для новых соединений, но не будет иметь эффекта для старых соединений. Любой пользователь может изменить уровень изоляции для своей сессии, даже внутри самой транзакции. В версиях старше 3.23.50 SET TRANSACTION не оказывает эффекта на таблицы InnoDB. В версиях старше 4.0.5 вы можете использовать только REPEATABLE READ и SERIALIZABLE.

Вы можете получить информацию об уровне изоляции, глобальном или для текущего соединения:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

В блокировке уровня строки InnoDB использует так называемую блокировку следующего ключа. Это означает, что кроме индексных записей InnoDB может также блокирует "интервал" перед индексной записью для блокировки вставок другими пользователями непосредственно перед индексной записью. Блокировка следующего ключа означает блокировку, которая ставится на индексную запись и интервал перед ней. Блокировка интервала означает только блокировку интервала перед некоторыми индексными записями.

Подробное описание каждого уровня изоляции в InnoDB:

READ UNCOMMITTED Также называется "грязным чтением": неблокирующиеся выборки (SELECT) выполняются таким образом, что мы не видим возможные ранние версии записи; таким образом они "несогласованно" читаются в этом уровне изоляции; в остальных случаях этот уровень работает также как и READ COMMITTED.
READ COMMITTED Нечто похожее на уровень изоляции Oracle. Все выражения SELECT ... FOR UPDATE и SELECT ... LOCK IN SHARE MODE блокируют только индексные записи и не блокируют интервал перед ними. Поэтому они позволяют свободно добавлять новые записи после заблокированных. UPDATE и DELETE, которые используют уникальный индекс и уникальные условия поиска, блокируют только найденную индексную запись, и не блокируют интервал перед ней. Но в UPDATE и DELETE диапазонного типа в InnoDB должны установить блокировку следующего ключа или интервальную блокировку и блокировать добавления другими пользователями в интервал, покрытый диапазоном. Это необходимо, т.к. "фантомные строки" должны быть блокированы для успешной работы репликации и восстановления в MySQL. Согласованное чтение работает как и в Oracle: каждое согласованное чтение, даже внутри одной транзакции, устанавливает и читает свой собственный снимок.
REPEATABLE READ Этот уровень изоляции используется в InnoDB по умолчанию. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, и DELETE, которые используют уникальные индексы и уникальное условие поиска блокируют только найденную индексную запись и не блокируют интервал перед ней. В остальных случаях эта операция использует блокировку следующего ключа, блокирует диапазон индексов, просканированных блокировкой следующего ключа или интервальной, и блокирует новые добавления другими пользователями. В согласованном чтении есть важное отличие от предыдущего уровня изоляции: на этом уровне все согласованные чтения внутри той же самой транзакции читают снимок, сделанный для первого чтения. Это соглашение означает, что если вы выполните несколько простых выборок (SELECT) внутри той же самой транзакции, эти выборки будут целостными по отношению к друг другу.
SERIALIZABLE Этот уровень похож на предыдущий, но простые SELECT преобразовываются в SELECT ... LOCK IN SHARE MODE.

7.5.8.1 Согласованное чтение

Согласованное чтение означает, что для того, чтобы предоставить запросу копию базы данных на текущий момент времени, используется многовариантность таблиц InnoDB. Для запроса доступны лишь те изменения, которые были внесены транзакциями, зафиксированными на этот момент времени, и не доступны изменения, сделанные незафиксированными или проведенными позже транзакциями. Исключением из данного правила могут стать только изменения, внесенные транзакцией, направляющей текущий запрос.

Если вы используете уровень изоляции по умолчанию REPEATABLE READ, то все выборки читают снимок, сделанный первым чтением в этой транзакции. Вы можете получить более свежую копию для своих запросов - для этого следует зафиксировать текущую транзакцию и направить новые запросы.

Согласованное чтение является режимом по умолчанию, в котором в InnoDB обрабатываются операторы SELECT при READ COMMITTED или REPEATABLE READ уровнях изоляции. При согласованном чтении не устанавливаются блокировки на таблицы, к которым обращается запрос, и, таким образом, остальные пользователи могут вносить изменения в эти таблицы одновременно с согласованным чтением таблиц.

7.5.8.2 Чтение с блокировкой

В некоторых случаях использовать согласованное чтение нецелесообразно. Приведем пример. Допустим, что необходимо добавить новую строку в таблицу CHILD, предварительно убедившись, что для нее имеется родительская строка в таблице PARENT.

Предположим, что для чтения таблицы PARENT было использовано согласованное чтение, и в таблице была обнаружена родительская строка. Можно ли теперь безопасно добавить дочернюю строку в таблицу CHILD? Нет, потому что в это время другой пользователь мог без вашего ведома удалить родительскую строку из таблицы PARENT.

В данной ситуации необходимо выполнить операцию SELECT в режиме блокировки, LOCK IN SHARE MODE.

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Выполнение чтения в режиме совместного использования (share mode) означает, что считываются самые новые доступные данные и производится блокировка строк, чтение которых осуществляется. Если последние данные принадлежат еще не зафиксированной транзакции, мы ждем, пока транзакция не будет зафиксирована. Блокировка в режиме совместного использования не позволяет другим пользователям обновить или удалить читаемую строку. После того, как указанный выше запрос вернет родительскую строку 'Jones', мы можем безопасно добавить дочернюю строку в таблицу CHILD и зафиксировать транзакцию. В этом примере показано, как использовать целостность ссылочных данных в своей программе.

Рассмотрим еще один пример. Пусть у нас есть поле целочисленного счетчика в таблице CHILD_CODES, которое мы используем для назначения уникального идентификатора каждой дочерней записи, добавляемой к таблице CHILD. Очевидно, что использование согласованного чтения или чтения в режиме совместного доступа для получения текущего значения счетчика не подходит, так как два пользователя базы данных могут получить одно и то же значение счетчика и создать дублирующиеся ключи при добавлении двух дочерних записей в таблицу.

Для этого случая возможны два способа произвести чтение и увеличить значение счетчика: (1) сначала обновить значение счетчика, увеличив его на 1, и только после этого прочитать его или (2) сначала прочитать счетчик в режиме блокировки FOR UPDATE, а после этого увеличить его значение:

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

Оператор SELECT ... FOR UPDATE прочитает последние доступные данные с установкой отдельной блокировки на каждую считываемую строку. Таким образом, блокировка на строки устанавливается точно так же, как и в случае поиска по UPDATE.

7.5.8.3 Блокировка следующего ключа: устранение проблемы с фантомом

При блокировке на уровне строк в InnoDB используется алгоритм, который получил название блокировки следующего ключа. В InnoDB осуществляется блокировка на уровне строк, поэтому на время поиска или сканирования индекса таблицы устанавливается совместно используемая или эксклюзивная блокировка записей обрабатываемых индексов. Таким образом, более точно блокировку на уровне строк можно определить как блокировку индексных записей.

Блокировка, которая в InnoDB устанавливается на индексные записи, влияет также на интервал перед этой индексной записью. Если у пользователя имеется совместная или эксклюзивная блокировка записи R в индексе, то другой пользователь не может вставить новую индексную запись перед R в порядке следования индексов. Такая блокировка интервалов производится для предотвращения так называемой проблемы с фантомом. Предположим, что необходимо прочитать и заблокировать все дочерние записи с идентификатором, превышающим 100, из таблицы CHILD, и обновить некоторые поля в выбранных строках.

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Допустим, что создан индекс таблицы CHILD по столбцу ID. Наш запрос произведет сканирование, начиная с первой записи, в которой ID больше 100. Теперь, если установленная на записи индекса блокировка не заблокирует вставки в интервалы, за это время в таблицу может быть вставлена новая дочерняя запись. Если теперь в транзакции запустить

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

еще раз, то в результате запроса будет выдана новая дочерняя запись. Это противоречит принципу изоляции транзакции: транзакция должна запускаться таким образом, чтобы считываемые ею данные не изменялись на протяжении выполнения транзакции. Если мы рассматриваем набор строк как элемент данных, то новая дочерняя ``запись-фантом'' нарушит этот принцип изоляции.

Когда InnoDB сканирует индекс, то возможна также блокировка интервалов после последних записей в индексе. Именно это иллюстрируется в предыдущем примере: блокировка, установленная InnoDB, предотвратит вставку в таблицу, если ID будет больше 100.

Блокировку следующего ключа можно использовать для того, чтобы провести проверку уникальности значений в своей программе. Если данные считываются в режиме совместного доступа и отсутствует дубликат строки, которую необходимо вставить, то можно безопасно вставлять свою строку и быть уверенным, что благодаря блокировке следующего ключа, установленной на предшествующей строке во время чтения, будет предотвращена вставка дублирующейся строки. Таким образом, блокировка следующего ключа позволяет ``заблокировать'' отсутствие чего-либо в таблице.

7.5.8.4 Блокировка, осуществляемая различными операторами SQL в InnoDB

SELECT ... FROM ...: согласованное чтение, которое производится из образа базы данных без блокировки.
SELECT ... FROM ... LOCK IN SHARE MODE: устанавливает совместно используемую блокировку следующего ключа на все считываемые индексные записи.
SELECT ... FROM ... FOR UPDATE: устанавливает эксклюзивную блокировку следующего ключа на все считываемые индексные записи.
INSERT INTO ... VALUES (...): устанавливает эксклюзивную блокировку на вставленную строку. Обратите внимание, что эта блокировка не является блокировкой следующего ключа и не предотвращает вставку другими пользователями записей в интервал перед вставленной строкой. Если произойдет ошибка дублирующегося ключа, оператор устанавливает блокировку совместного доступа на запись дублирующегося индекса.
INSERT INTO T SELECT ... FROM S WHERE ... устанавливает эксклюзивную (не следующего ключа) блокировку на каждую вставляемую в T строку. Осуществляет поиск по S как согласованное чтение, но устанавливает блокировки совместного доступа к следующему ключу на S, если включено ведение журнала MySQL. InnoDB в последнем случае должен устанавливать блокировки, так как при восстановлении работоспособности системы с повтором всех завершенных транзакций из резервной копии все операторы SQL должны запускаться точно таким же образом, как и изначально.
CREATE TABLE ... SELECT ... выполняет операцию SELECT как согласованное чтение или совместную блокировку, как и в предыдущем пункте.
REPLACE осуществляется так же, как и вставка, если нет конфликтов уникальных ключей. В противном случае эксклюзивная блокировка следующего ключа будет установлена на строку, которая должна быть обновлена.
UPDATE ... SET ... WHERE ...: устанавливает эксклюзивную блокировку следующего ключа для каждой записи, по которой производится поиск.
DELETE FROM ... WHERE ...: устанавливает эксклюзивную блокировку следующего ключа для каждой записи, по которой производится поиск.
Если для таблицы определены ограничения FOREIGN KEY, для любой вставки, обновления или удаления, для которых требуется проверка условий ограничения, устанавливается совместная блокировка на уровне записей, которые просматриваются для проверки ограничения. В InnoDB эти блокировки устанавливаются также в случае нарушения ограничения.
LOCK TABLES ...: устанавливает блокировку таблицы. Эта блокировка производится кодом уровня MySQL. Механизм автоматического обнаружения взаимоблокировок (deadlock) InnoDB не может детектировать взаимоблокировки, в которых участвуют такие блокировки таблиц (см. следующий раздел). Кроме того, поскольку MySQL ``знает'' о блокировке на уровне строки, возможно установление блокировки таблицы, в которой другой пользователь заблокировал строки. Но это не опасно для целостности транзакции. See section 7.5.13 Ограничения для таблиц InnoDB.

7.5.8.5 Обнаружение и откат взаимоблокировки (deadlock)

InnoDB автоматически обнаруживает взаимоблокировку транзакций и производит откат транзакции или транзакций для предотвращения взаимоблокировок. Начиная с версии 4.0.5 InnoDB будет пытаться выбрать меньшую транзакцию для отката. Размер транзакции определяется количеством строк, которые должны быть добавлены, обновлены или удалены. До версии 4.0.5 InnoDB всегда откатывал транзакцию, запрос на блокировку которой вызвал возникновение взаимоблокировки, то есть замкнутого цикла в графике ожиданий транзакций.

InnoDB не может обнаружить взаимоблокировку, установленную оператором MySQL LOCK TABLES, или блокировку, установленную отличным от InnoDB обработчиком таблиц. Такие ситуации необходимо исправлять при помощи параметра innodb_lock_wait_timeout, который задается в `my.cnf'.

Когда InnoDB выполняет полный откат транзакции, все блокировки, установленные транзакцией, снимаются. Тем не менее, если в результате ошибки производится откат только одного оператора SQL, некоторые блокировки, установленные оператором, могут остаться в силе. Это происходит потому, что InnoDB хранит блокировку строк в формате, по которому впоследствии нельзя определить, каким оператором SQL была установлена блокировка.

7.5.8.6 Пример работы согласованного чтения в InnoDB

Допустим, вы используете уровень изоляции, установленый по умолчанию - REPEATABLE READ. При выполнении согласованного чтения (т.е. обычного оператора SELECT) InnoDB определяет для транзакции момент времени, по состоянию на который запросу будет предоставляться информация из базы данных. Таким образом, если транзакция удаляет строку и фиксирует это изменение после назначенного момента времени, то вы не увидите, что строка была удалена. Это справедливо также для вставок и обновлений.

Чтобы такой момент времени ``передвинуть вперед'', нужно зафиксировать транзакцию, а затем выполнить новую команду SELECT.

Это называется многовариантным контролем совпадений.

		 Пользователь A 	  Пользователь B

		SET AUTOCOMMIT=0;	SET AUTOCOMMIT=0;

время
| 		SELECT * FROM t;
|		пустой набор данных
|					INSERT INTO t VALUES (1, 2);
|
v 		SELECT * FROM t;
		пустой набор данных
					COMMIT;
		SELECT * FROM t;
		пустой набор данных;

		COMMIT;

		SELECT * FROM t;
		---------------------
		| 1 | 2 |
		---------------------

Таким образом, пользователь A увидит строку, вставленную пользователем B только после того, как B зафиксирует вставку, и A зафиксирует свою собственную транзакцию, чтобы момент времени передвинулся на позицию, находящуюся после фиксации, произведенной пользователем B.

Чтобы увидеть ``самое свежее'' состояние базы данных, необходимо использовать чтение с блокировкой:

SELECT * FROM t LOCK IN SHARE MODE;

7.5.8.7 Каким образом избежать взаимоблокировок (deadlock)?

Взаимоблокировки - классическая проблема транзакционных баз данных. Они не опасны до тех пор пока не становятся настолько частыми, что вы вообще не можете запустить некоторые транзакции. Обычно вы можете написать свои приложения таким образом, что они всегда будут подготавливать перезапуск транзакции, если произошел откат из-за взаимоблокировок.

InnoDB использует автоматическую блокировку уровня строки. Вы можете создать взаимоблокировку даже в случае транзакций, которые всего лишь добавляют или удаляют единичную строку. Это происходит из-за того, что в действительности эти операции не являются "атомарными": они автоматически устанавливают блокировку на индексные записи добавляемых/удаляемых строк (или на несколько записей).

Вы можете избежать взаимоблокировок или уменьшить их количество, следуя следующим приемам:

Используйте SHOW INNODB STATUS в MySQL начиная с 3.23.52 и 4.0.3 для определения причины последней взаимоблокировки. Это поможет вам настроить ваше приложение, что бы избежать взаимоблокировок.
Всегда подготавливайте перезапуск транзакции, если произошел откат из-за взаимоблокировки. Взаимоблокировка не опасна: всего лишь попробуйте еще раз.
Чаще фиксируйте свои транзакии. Маленькие транзакции меньше склонны к противоречиям.
Если вы используете чтение с блокировкой SELECT ... FOR UPDATE или ... LOCK IN SHARE MODE, попробуйте использовать более низкий уровень изоляции READ COMMITTED.
Производите операции с вашими таблицам и строками в фиксированном порядке. Тогда транзакции будут формировать очередь и не будет происходить взаимоблокировка.
Добавьте хорошие индексы на ваши таблицы. Тогда ваши запросы будут сканировать меньше индексных записей и, соответственно, будут устанавливать меньше блокировок. Используйте EXPLAIN SELECT для того, чтобы узнать, выбирает ли MySQL соответствующий индекс для ваших запросов.
Используйте меньше блокировок: если вы можете допустить, чтобы SELECT возвращал данные из старого снимка, не добавляйте к выражению FOR UPDATE или LOCK IN SHARE MODE. Используйте уровень изоляции READ COMMITTED, который больше всего подходит для данной ситуации, так как каждое согласованное чтение внутри одной и той же транзакции читает свой собственный свежий снимок.
Если ничего не помогло, сериализируйте свои транзакции с блокировкой уровня таблиц: LOCK TABLES t1 WRITE, t2 READ, ... ; [здесь можете развлекаться с таблицами t1 и t2]; UNLOCK TABLES. Блокировка на уровне таблиц выстраивает ваши транзакции в очередь, и позволяет избежать взаимоблокировки. Заметьте, что LOCK TABLES неявным образом начинает транзакцию наподобие BEGIN, и UNLOCK TABLES неявным образом завершает ее в COMMIT.
Другое решение для сериализации транзакций - это создание вспомагательного "семафора" таблицы, где есть всего лишь одна строка. Каждая транзакция обновляет эту строку перед доступом к другой таблице. В этом случае все транзакции выполняются в виде очереди. Отметим что таким же образом в настоящий момент работает и алгоритм определения взаимоблокировок в InnoDB, так как блокировка сериализации - это блокировка уровня строки. При блокировке на уровне таблицы в MySQL мы используем метод таймаута для разрешения взаимоблокировки.

7.5.8.8 Рекомендации по увеличению производительности

1. Если top операционной системы Unix или Task Manager Windows показывают процент рабочей нагрузки процессора меньше 70%, это значит, что объем рабочей нагрузки в основном сводится к обращениям к диску. Возможно, слишком часто производится фиксация транзакций, или буферный пул слишком мал. Здесь может помочь увеличение размера буферного пула, но не следует устанавливать его значение большим, чем 80% физической памяти.

2. Несколько изменений следует вносить за одну транзакцию. InnoDB должен сбрасывать журнал на диск после каждой фиксации транзакции, если эта транзакция вносит изменения в базу данных. Поскольку скорость вращения диска обычно не превышает 167 оборотов в секунду, то количество фиксаций ограничено 167 фиксациями в секунду, если, конечно, диск не обманывает операционную систему.

3. Если вы можете позволить себе потерять последние зафиксированные транзакции, установите параметр innodb_flush_log_at_trx_commit в файле `my.cnf' в нулевое значение. Так или иначе InnoDB пытается сохранить журнал ежесекундно, и в этом случае сохранение не гарантируется.

4. Увеличьте размеры файлов журналов, доведите их даже до размера буферного пула. Когда InnoDB заполняет файлы журналов, он должен сохранить измененное содержимое буферного пула на диск в виде моментального снимка базы. Маленькие журналы будут вызывать множество ненужных записей на диск. Есть и оборотная сторона медали - если файлы журналов большие, то время восстановления транзакций (в случае сбоя) будет больше.

5. Кроме того, буфер журнала должен быть достаточно большим, например 8 Мб.

6. (Актуально для версии 3.23.39 и выше.) В некоторых версиях операционных систем Linux и Unix запись файлов на диск при помощи команды Unix fdatasync и других подобных методов производится на удивление медленно. Принятый по умолчанию метод InnoDB использует функцию fdatasync. Если скорость записи базы данных вас не устраивает, можно попробовать для параметра innodb_flush_method в файле `my.cnf' задать значение O_DSYNC, хотя на многих системах O_DSYNC обычно работает медленнее.

7. При импортировании данных в InnoDB убедитесь что в MySQL не установлено значение autocommit=1. Если оно установлено, то каждая вставка требует сохранения журналов на диске. Поместите прямо в начале вашего файла с данными:

SET AUTOCOMMIT=0;

и в конце

COMMIT;

Если используется параметр mysqldump --opt, то вы получите файлы, которые достаточно быстро импортируются в InnoDB, даже если их не окружить вышеуказанными командами SET AUTOCOMMIT=0; ... COMMIT;.

8. Осторожно относитесь к значительным откатам больших вставок InnoDB использует буфер вставок для того, чтобы меньше ``дергать'' диск на вставках, однако для соответствующего отката транзакции такой механизм не предусмотрен.. Ограниченный производительностью диска откат может занять в 30 раз больше времени, чем вставка. Удаление процесса базы данных не поможет, так как откат начнется снова после запуска базы данных. Единственный способ избежать такого отката - это увеличить буферный пул настолько, что откат станет зависеть только от производительности процессора, перестанет ``равняться'' по диску и отработается быстро. Есть еще один способ - это удаление базы данных InnoDB целиком.

9. Следует также осторожно относиться к операциям со значительными объемами данных, зависящим от производительности диска. Чтобы очистить таблицу, используйте команды DROP TABLE или TRUNCATE (начиная с версии MySQL-4.0 и выше), а не DELETE FROM yourtable.

10. Используйте множественные вставки для уменьшения нагрузки на коммуникации между клиентом и сервером, если вам нужно вставить множество записей:

INSERT INTO yourtable VALUES (1, 2), (5, 5);

Эта рекомендация подходит для вставок в таблицы любого типа, а не только InnoDB.

7.5.8.9 InnoDB Monitor

Начиная с версии 3.23.41 в состав InnoDB входит InnoDB Monitor, который выводит информацию по внутреннему состоянию InnoDB. Когда InnoDB Monitor включен, сервер MySQL mysqld выводит стандартный набор данных (обратите внимание: клиент MySQL ничего не выводит) примерно каждые 15 секунд. Эти данные могут пригодиться при настройке производительности. В операционной системе Windows необходимо запустить mysqld-max из командной строки MS-DOS с параметрами --standalone --console, чтобы направить выводимые данные в окно MS-DOS.

Существует отдельная функция innodb_lock_monitor, которая выводит такую же информацию как innodb_monitor, а также данные по блокировкам, установленным каждой транзакцией.

Выводящаяся информация включает следующие данные:

по блокировкам, ожидающим транзакций;
по семафорам, ожидающим потоков;
по файлам, ожидающим ответа на запрос ввода/вывода;
статистику буферного пула;
по активности буферов удаления и вставок в основном потоке InnoDB.

InnoDB Monitor можно запустить при помощи следующей команды SQL:

CREATE TABLE innodb_monitor(a int) type = innodb;

а остановить его при помощи:

DROP TABLE innodb_monitor;

Вызов команды CREATE TABLE является только способом передачи команды в InnoDB через программу синтаксического анализа SQL. Факт создания таблицы не играет никакой роли для InnoDB Monitor. Если вы останавливаете сервер, когда монитор работает, и хотите запустить монитор заново, следует уничтожить таблицу прежде, чем снова вызвать CREATE TABLE для запуска монитора. Синтаксис может измениться в будущих версиях.

Пример информации, выводимой InnoDB Monitor:

================================
010809 18:45:06 INNODB MONITOR OUTPUT
================================
--------------------------
LOCKS HELD BY TRANSACTIONS
--------------------------
LOCK INFO:
Number of locks in the record hash table 1294
LOCKS FOR TRANSACTION ID 0 579342744
TABLE LOCK table test/mytable trx id 0 582333343 lock_mode IX
RECORD LOCKS space id 0 page no 12758 n bits 104 table test/mytable index
PRIMARY trx id 0 582333343 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 74; 1-byte offs FALSE;
info bits 0
0: len 4; hex 0001a801; asc ;; 1: len 6; hex 000022b5b39f; asc ";;
2: len 7; hex 000002001e03ec; asc ;; 3: len 4; hex 00000001;
...
-----------------------------------------------
CURRENT SEMAPHORES RESERVED AND SEMAPHORE WAITS
-----------------------------------------------
SYNC INFO:
Sorry, cannot give mutex list info in non-debug version!
Sorry, cannot give rw-lock list info in non-debug version!
-----------------------------------------------------
SYNC ARRAY INFO: reservation count 6041054, signal count 2913432
4a239430 waited for by thread 49627477 op. S-LOCK file NOT KNOWN line 0
Mut ex 0 sp 5530989 r 62038708 sys 2155035;
rws 0 8257574 8025336; rwx 0 1121090 1848344
-----------------------------------------------------
CURRENT PENDING FILE I/O'S
--------------------------
Pending normal aio reads:
Reserved slot, messages 40157658 4a4a40b8
Reserved slot, messages 40157658 4a477e28
...
Reserved slot, messages 40157658 4a4424a8
Reserved slot, messages 40157658 4a39ea38
Total of 36 reserved aio slots
Pending aio writes:
Total of 0 reserved aio slots
Pending insert buffer aio reads:
Total of 0 reserved aio slots
Pending log writes or reads:
Reserved slot, messages 40158c98 40157f98
Total of 1 reserved aio slots
Pending synchronous reads or writes:
Total of 0 reserved aio slots
-----------
BUFFER POOL
-----------
LRU list length 8034
Free list length 0
Flush list length 999
Buffer pool size in pages 8192
Pending reads 39
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31383918, created 51310, written 2985115
----------------------------
END OF INNODB MONITOR OUTPUT
============================
010809 18:45:22 InnoDB starts purge
010809 18:45:22 InnoDB purged 0 pages

Некоторые примечания по выводу:

Если раздел LOCKS HELD BY TRANSACTIONS содержит информацию по ожидаемым блокировкам, то у вашей программы может быть конфликт блокировок. Выводимая информация также может оказать помощь в отслеживании причин возникновения взаимных блокировок.
Если InnoDB скомпилировать при помощи UNIV_SYNC_DEBUG в `univ.i', то раздел SYNC INFO будет содержать информацию по зарезервированным семафорам.
Раздел SYNC ARRAY INFO содержит информацию по потокам, ожидающим семафора, а также статистические данные по количеству повторных циклов или ожиданий, выполненных потоками для семафоров или блокировок чтения/записи. Большое количество потоков, ожидающих семафоров, может возникнуть в результате частого выполнения операций ввода/вывода диска, оно может быть также обусловлено конфликтами внутри самого InnoDB. Конфликты могут возникать при большом количестве параллельных запросов или в случае проблем операционной системы с планированием потоков.
В разделе CURRENT PENDING FILE I/O'S выводится список файлов, ожидающих ответа на запрос ввода/вывода. Большое количество таких файлов говорит о том, что рабочая нагрузка ограничена операциями ввода/вывода диска.
Раздел BUFFER POOL содержит статистическую информацию по записываемым и считываемым страницам. По этим данным можно вычислить, сколько запросов ввода/вывода по файлам данных выполняется на данный момент.

7.5.9 Реализация многовариантности

Поскольку InnoDB является многовариантной базой данных, информация по старым версиям строк в ней хранится в табличной области. Эта информация содержится в структуре данных, которую мы по аналогии со структурой данных в Oracle называем сегментом отката.

К каждому внутреннему представлению строки таблицы, хранящейся в базе данных InnoDB, добавляется по два поля. В 6-байтовом поле хранится идентификатор последней транзакции, которая производила вставку или обновление строки. Удаление рассматривается как обновление, при котором специальный бит удаления строки помечается соответствующим образом. Помимо этого, каждая строка содержит также 7-байтовое поле, которое называется указателем отката. Указатель отката указывает на запись журнала отмены, занесенную в сегмент отката. Если строка была обновлена, запись журнала отмены содержит необходимую информацию для восстановления содержимого строки до обновления.

Информация из сегмента отката в базе данных InnoDB используется для того, чтобы произвести отмену, необходимую для отката транзакции, а также для создания предыдущих версий строки для согласованного чтения.

Журналы отмены в сегменте отката разделяются на журналы вставки и журналы обновления. Журналы отмены вставки необходимы только для отката транзакций и могут быть удалены сразу после фиксации транзакции. Журналы отмены обновления используются для согласованного чтения, и их можно удалять только после того, как не останется транзакций, для которых в InnoDB определена копия, создающая при согласованном чтении раннюю версию строки по информации из журнала отмены обновления.

Не забывайте регулярно фиксировать свои транзакции, включая транзакции, использующие согласованное чтение. В противном случае InnoDB не сможет удалить данные из журналов отмены обновления, что приведет к разрастанию сегмента отката, который может занять всю вашу табличную область.

Физический размер записи журнала отмены в сегменте отката обычно меньше, чем соответствующая вставка или обновленная строка. Эту информацию можно использовать для вычисления размера пространства, необходимого для сегмента отката.

В нашей многовариантной схеме строка физически не удаляется из базы данных немедленно после удаления ее при помощи оператора SQL. Только после того, как InnoDB сможет удалить запись журнала отмены обновления, занесенную для удаления, соответствующая строка и ее индексная запись из базы данных могут быть физически удалены. Эта операция удаления называется чисткой. Она производится достаточно быстро - на нее уходит столько же времени, как и на выполнение оператора удаления SQL.

7.5.10 Структуры таблиц и индексов

В MySQL информация словаря данных таблиц хранится в файлах `.frm', расположенных в каталогах баз данных. Но для каждой таблицы InnoDB имеются также свои записи во внутренних словарях данных InnoDB в табличной области. Когда MySQL удаляет таблицу или базу данных, необходимо удалить как файлы `.frm', так и соответствующие записи в словаре данных InnoDB. Именно поэтому нельзя перемещать таблицы InnoDB между базами данных путем простого перемещения файлов `.frm'. По этой же причине DROP DATABASE не работал для таблиц InnoDB в MySQL версий <= 3.23.43.

Для всех таблиц InnoDB есть специальный индекс, в котором хранятся данные строк - он называется кластеризованным индексом. Если в таблице определить PRIMARY KEY, то индекс первичного ключа будет кластеризированным индексом.

Если первичный ключ для таблицы не определен, то InnoDB самостоятельно создаст кластеризированный индекс; строки в этом индексе будут упорядочены по идентификатору строки, который InnoDB назначил строкам этой таблицы. Идентификатор строки представляет собой 6-байтовое поле, значение которого постоянно увеличивается при вставке новых строк. Таким образом, сортировка по идентификатору строки фактически представляет собой сортировку по последовательности вставки.

Доступ к строке через кластеризированный индекс осуществляется достаточно быстро, поскольку данные строки находятся на той же странице, к которой приводит поиск по индексу. Во многих базах данных информация и индексная запись традиционно хранятся на разных страницах. При больших размерах таблицы архитектура кластеризированных индексов часто позволяет сократить количество дисковых операций ввода/вывода по сравнению с традиционными решениями.

Записи в некластеризированных индексах (мы называем их также вторичными индексами), в InnoDB содержат значение первичного ключа для строки. InnoDB использует этот значение первичного ключа для поиска строки в кластеризированном индексе. Следует учитывать, что если первичный ключ достаточно велик, вторичные индексы будут занимать больше места.

7.5.10.1 Физическая структура индекса

Все индексы в InnoDB представляют собой B-деревья, в которых записи индексов хранятся в страницах ответвления дерева. По умолчанию размер индексной страницы составляет 16 Кб. При вставке новых записей InnoDB старается оставить 1 / 16 страницы свободной - для будущих вставок и обновлений индексных записей.

Если записи индекса вставлены в последовательном порядке (в порядке возрастания или убывания), то получившиеся индексные страницы будут заполнены примерно на 15/16. Для записей, которые вставляются в случайном порядке, эти значения составят от 1/2 до 15/16. Если коэффициент заполнения индексной страницы уменьшится и станет ниже 1/2, InnoDB попытается объединить записи индексного дерева, чтобы освободить страницу.

7.5.10.2 Буферизация вставок

Нередко в программах для работы с базами данных первичный ключ является уникальным идентификатором и новые строки вставляются в порядке возрастания первичного ключа. Таким образом, вставки в кластеризированный индекс не требуют проведения случайных считываний с диска.

Что же касается вторичных индексов, то они, напротив, обычно не являются уникальными, так что вставки во вторичные индексы производятся в относительно случайном порядке. Это приводит к выполнению большого количества случайных дисковых операций ввода/вывода диска, если не используется специальный механизм, применяемый в InnoDB.

Если требуется вставить запись индекса во вторичный индекс, который не является уникальным, InnoDB проверяет, находится ли страница вторичного индекса в буферном пуле. Если она там есть, InnoDB произведет вставку непосредственно в страницу индекса. Но если страница индекса не найдена в буферном пуле, InnoDB вставляет запись в специальную структуру буфера вставок. Буфер вставок настолько мал, что полностью помещается в буферный пул, и вставки в него могут производиться очень быстро.

Буфер вставок периодически объединяется с деревьями вторичных индексов в базе данных. Часто, объединив несколько вставок на одной странице индексного дерева, можно за счет этого сократить количество операций ввода/вывода диска. Использование буфера вставки может ускорить вставку в таблицу в 15 раз.

7.5.10.3 Адаптивный хешированный индекс

Если база данных почти полностью помещается в основной памяти, то самым быстрым способом выполнения запросов по этой базе данных является использование хешированных индексов. В InnoDB существует автоматический механизм, который отслеживает поиск по индексу, осуществляемый по индексам, определенным для таблицы, и если InnoDB посчитает, что запросы выиграют от создания хешированного индекса, такой индекс будет создан автоматически.

Но следует учитывать, что хешированный индекс всегда создается на основе существующего индекса B-дерева таблицы. InnoDB может создать хешированный индекс на префиксах любой длины ключа, определенного для B-дерева, в зависимости от того, по какой схеме поиска InnoDB производит обзор индекса the B-дерева. Хешированный индекс может быть частичным: не обязательно кэшировать в буферном пуле весь индекс B-дерева. InnoDB будет создавать хешированные индексы по запросу для тех страниц индекса, к которым часто производится доступ.

Хотя механизм адаптивного хешированного индекса InnoDB приспосабливается к большому количеству основной памяти, он больше подходит для архитектуры баз данных основной памяти.

7.5.10.4 Физическая структура записи

У всех записей индекса в InnoDB есть заголовок, состоящий из 6 байтов. Заголовок используется для связывания вместе последовательных записей, а также при блокировке на уровне строк.
Записи в кластеризированном индексе содержат поля для всех столбцов, определенных пользователем. Кроме того, имеется 6-байтовое поле для идентификатора транзакции и 7-байтовое поле для указателя строки.
Если пользователь не определил для таблицы первичный ключ, то в каждой записи кластеризированного индекса также содержится 6-байтовое поле идентификатора строки.
Все записи вторичного индекса содержат также все поля, определенные для ключа кластеризированного индекса.
Запись также содержит указатель на каждое поле записи. Если общая длина полей в записи меньше 128 байтов, то размер указателя будет 1 байт, в противном случае - 2 байта.

7.5.10.5 Как работают автоинкрементные столбцы в InnoDB

Когда пользователь после запуска базы данных осуществляет первую вставку в таблицу T, где определен автоинкрементный столбец, и пользователь не предоставляет конкретного значения для этого столбца, InnoDB выполняет SELECT MAX(auto-inc-column) FROM T, затем присваивает это значение, увеличенное на единицу, столбцу, и автоматически увеличивает счетчик таблицы. Эту последовательность действий мы называем инициализацией счетчика автоматического увеличения для таблицы T.

Ту же последовательность действий InnoDB выполняет и для инициализации автоинкрементного счетчика вновь созданной таблицы.

Обратите внимание: если пользователь указывает при вставке значение автоинкрементного столбца 0, то InnoDB обрабатывает строку так, как будто значение не было указано.

Если после инициализации автоматического увеличения счетчика пользователь вставляет строку, в которой он явно указывает значение столбца, и это значение превышает текущее значение счетчика, то счетчик устанавливается в указанное значение столбца. Если пользователь явно не указывает значение, то InnoDB увеличивает счетчик на единицу и присваивает столбцу это новое значение.

При присвоении значений из счетчика механизм автоматического увеличения обходит блокировку и управление транзакциями. Вследствие этого могут возникнуть пропуски в последовательности чисел в случае, если производится откат транзакций, которые получили номера из счетчика.

Для случаев, когда пользователь присваивает столбцу отрицательное значение или если значение превысит максимальное целое число, которое может храниться в переменной целочисленного типа, поведение механизма механического увеличения не определено.

7.5.11 Управление файловым пространством и дисковый ввод/вывод

7.5.11.1 Дисковый ввод/вывод

В операциях дискового ввода/вывода для таблиц InnoDB используется асинхронный ввод/вывод. В Windows NT применяется собственный асинхронный ввод/вывод, обеспечиваемый операционной системой, а в Unix - эмуляция асинхронного ввода/вывода, встроенная в InnoDB (InnoDB создает определенное количество потоков ввода/вывода, чтобы обеспечить операции ввода/вывода, такие как опережающее считывание). В будущей версии мы добавим поддержку эмуляции асинхронного ввода/вывода в Windows NT и собственного асинхронного ввода/вывода в тех версиях Unix, в которых он есть.

В Windows NT для таблиц InnoDB используется ввод/вывод без буферизации. Это означает, что страницы на диске, которые записывает или считывает InnoDB, не заносятся в файловый кэш операционной системы. При этом экономится некоторое количество памяти.

Начиная с версии 3.23.41 в InnoDB используется новая техника сбрасывания файлов на диск, которая получила название двойной записи. Она обеспечивает большую безопасность при восстановлении после сбоев (таких как, например, зависание операционной системы или отключение питания) и повышение производительности на большинстве версий Unix, так как снижается необходимость в операциях fsync.

``Двойная запись'' означает, что InnoDB перед записью страниц в файл данных сначала записывает их в смежный участок табличной области, который называется буфером двойной записи. Запись страниц в предназначенные для них места файла данных осуществляется только после завершения записи и сброса буфера двойной записи на диск. В случае сбоя системы во время записи страницы InnoDB во время восстановления найдет в буфере двойной записи пригодную копию страницы.

Начиная с версии 3.23.41 в качестве файла данных можно также использовать раздел реального диска, хотя тестирование этой возможности еще не проводилось. При создании нового файла данных в innodb_data_file_path сразу после размера файла данных необходимо ввести ключевое слово newraw. Раздел диска должен быть больше указанного размера или равен ему. Обратите внимание: 1 Мб в InnoDB -это 1024 x 1024 байт, тогда как в характеристиках диска 1 Мб обычно соответствует 1000 000 байт.

innodb_data_file_path=hdd1:5Gnewraw;hdd2:2Gnewraw

При новом запуске базы данных Вы должны изменить ключевое слово на raw. В противном случае InnoDB перезапишет ваш раздел!

innodb_data_file_path=hdd1:5Graw;hdd2:2Graw

Используя реальный диск, в некоторых версиях Unix можно производить небуферизованные операции ввода/вывода.

В InnoDB существует два эвристических метода опережающего считывания: последовательное опережающее считывание и случайное опережающее считывание. Метод последовательного опережающего считывания предусматривает, что InnoDB, определив, что схема доступа к сегменту в табличной области является последовательной, будет заранее направлять системе ввода/вывода пакет считываний страниц базы данных. Метод случайного опережающего считывания предполагает, что InnoDB, определив, что некоторые части табличной области полностью считываются в буферный пул, направляет оставшиеся считывания системе ввода/вывода.

7.5.11.2 Управление файловым пространством

Табличную область InnoDB составляют файлы данных, определенные в файле конфигурации. Файлы используются последовательно, распределения данных (striping ) по ним не производится. На данный момент вы не можете непосредственно указать, где должны быть размещены таблицы. Можно только воспользоваться знанием того факта, что для вновь созданной табличной области InnoDB будет распределяться место с начала памяти.

Табличная область состоит из страниц базы данных, принятый по умолчанию размер которых составляет 16 Кб. Эти страницы сгруппированы в блоки по 64 последовательных страницы. 'Файлы' внутри табличной области в InnoDB называются сегментами. Название 'сегмент отката' несколько не соответствует действительности, так как фактически в нем содержится много сегментов табличной области.

Для каждого индекса в InnoDB выделяется два сегмента: один - для конечных узлов B-дерева, а другой - для остальных узлов. Идея заключается в том, чтобы получить лучшее координирование конечных узлов, в которых содержатся данные.

Когда сегмент внутри табличной области возрастает, InnoDB выделяет первые 32 специально для этого сегмента. После этого InnoDB начинает выделять целые области для этого сегмента. Чтобы обеспечить хорошее координирование данных, InnoDB может единовременно добавить к большому сегменту до 4 областей,.

Некоторые страницы табличной области содержат битовые образы других страниц, поэтому несколько областей в табличной области InnoDB могут быть выделены не для целого сегмента, а только для отдельных страниц.

Когда вы запускаете запрос SHOW TABLE STATUS FROM ... LIKE ... для получения информации по доступному свободному пространству табличной области, InnoDB предоставит данные по свободным областям табличной области. InnoDB всегда резервирует области для очистки и других внутренних операций. Зарезервированные области не включаются в объем свободного пространства.

Если из таблицы удаляются данные, InnoDB объединяет соответствующие индексы B-дерева. В зависимости от схемы удалений, когда освобождаются отдельные страницы или области табличной области, это пространство становится доступным для других пользователей. Удаление таблицы или удаление всех ее строк гарантированно освободит пространство для других пользователей, но не следует забывать, что физически строки удаляются только после проведения чистки, после чего они больше не нужны при откате транзакций или согласованном чтении.

7.5.11.3 Дефрагментация таблицы

Если в индексной таблице производились случайные вставки или удаления, индекс может стать фрагментированным. Под фрагментацией мы подразумеваем то, что физическое расположение индексных страниц на диске значительно отличается от алфавитного порядка страниц, или что в 64-страничных блоках много пустых страниц, которые занесены в индекс.

Скорость сканирования индекса может возрасти, если периодически использовать команду mysqldump для копирования дампа таблицы в текстовый файл, записи диска на диск и повторного считывания таблицы из дампа. Есть еще один способ произвести дефрагментацию - преобразовать таблицу при помощи команды ALTER в тип MyISAM, а затем обратно в тип InnoDB. Обратите внимание на то, что таблица типа MyISAM должна помещаться в один файл в вашей операционной системе.

Если вставки в индекс всегда производятся последовательно, а удаления - только с конца, то алгоритм управления файловым пространством InnoDB гарантирует, что фрагментации индекса не возникнет.

7.5.12 Обработка ошибок

Обработка ошибок в InnoDB не всегда соответствует спецификациям, указанным в стандарте ANSI SQL. В соответствии со стандартом ANSI любая ошибка, произошедшая во время выполнения оператора SQL должна привести к откату оператора. InnoDB иногда осуществляет откат только части оператора или целой транзакции. Особенности обработки ошибок в InnoDB указаны в приведенном ниже списке.

Если закончилось свободное место в табличной области, будет выдано сообщение об ошибке MySQL 'Table is full' и InnoDB произведет откат оператора SQL.
Взаимоблокировка транзакции или истечение времени ожидания при блокировке приводят к откату целой транзакции в InnoDB.
Ошибка дублирующегося ключа приводит к откату вставки только этой определенной строки, даже в операторе INSERT INTO ... SELECT .... Этот алгоритм мы, возможно, изменим, с тем чтобы производился откат всего оператора SQL, если для него не указан параметр IGNORE.
Ошибка 'row too long' приводит к откату оператора SQL.
Большинство остальных ошибок обнаруживается на уровне кода MySQL, и производится откат соответствующего оператора SQL.

7.5.13 Ограничения для таблиц InnoDB

Предупреждение: НЕЛЬЗЯ преобразовывать системные таблицы MySQL из формата MyISAM в формат InnoDB! Эта операция не поддерживается, и если попытаться ее осуществить, MySQL не перезапустится, пока не будут восстановлены старые системные таблицы из резервной копии, или пока не будут созданы новые таблицы при помощи скрипта mysql_install_db.
Команда SHOW TABLE STATUS не выдает точных статистических данных по таблицам InnoDB, за исключением размера физического пространства, зарезервированного для таблицы. Подсчет строк производится приблизительно так, как в оптимизации SQL.
Если попытаться создать уникальный индекс на префиксе столбца, то будет выдана ошибка:
```
CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;
```
Если на префиксе столбца создать неуникальный индекс, InnoDB создаст индекс по всему столбцу.
Для таблиц InnoDB не поддерживается команда INSERT DELAYED.
Операция MySQL LOCK TABLES не знает про блокировки InnoDB на уровне строк в уже выполненном операторе SQL: это означает, что можно установить блокировку на таблицу, даже если существуют транзакции других пользователей, которые установили блокировку этой же таблицы на уровне строк. Таким образом, может оказаться, что ваши операции над таблицей будут вынуждены ожидать, если такая блокировка будет установлена другими пользователями: возможна также и взаимоблокировка. Тем не менее, это не угрожает целостности транзакций, так как при установке блокировки на таблицы InnoDB всегда соблюдается целостность. Кроме того, блокировка таблицы не позволяет другим транзакциям установить на таблицу дополнительные блокировки на уровне строки (в режиме несовместимости блокировок).
Нельзя установить ключ для столбцов типа BLOB или TEXT.
Таблица не может содержать больше 1000 столбцов.
Команда DELETE FROM TABLE не пересоздает таблицу, она удаляет все строки по одной, что осуществляется не очень быстро. В будущих версиях MySQL можно будет использовать команду TRUNCATE, которая намного быстрее.
Принятый по умолчанию размер страницы в InnoDB составляет 16 Кб. Повторно скомпилировав код, можно установить значение от 8 Кб до 64 Кб. В версиях <= 3.23.40 InnoDB максимальная длина строки несколько меньше половины размера страницы базы данных. Начиная с релиза исходного кода версии 3.23.41 столбцы типов BLOB и TEXT могут достигать 4 Гб, общая длина строк также не должна превышать 4 Гб. Поля с размером меньше или равным 128 байтам в InnoDB не хранятся на отдельных страницах. После того как InnoDB изменит строку, сохранив длинные поля на отдельных страницах, оставшаяся длина строки должна быть меньше половины страницы базы данных. Максимальная длина ключа - 7000 байтов.
В некоторых операционных системах файлы данных не должны превышать 2 Гб. Общий размер файлов журналов должен быть меньше 4 Гб.
Максимальный размер табличной области составляет 4 миллиарда страниц базы данных. Это также максимальный размер таблицы. Минимальный размер табличной области составляет 10 Мб.

7.5.14 История изменений InnoDB

Раздел в переводе.

7.5.15 Контактная информация для получения данных по InnoDB

Контактная информация компании Innobase Oy, которая создала модель InnoDB: веб-сайт: http://www.innodb.com/, e-mail: Heikki.Tuuri@innodb.com

Телефон: 358-9-6969 3250 (офис) 358-40-5617367 (мобильный)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland

7.6 Таблицы `BDB` или BerkeleyDB

7.6.1 Обзор таблиц `BDB`

Поддержка таблиц BDB включена в дистрибутив исходного кода MySQL начиная с версии 3.23.34 и в бинарную версию MySQL-Max.

BerkeleyDB, доступный на веб-сайте http://www.sleepycat.com/, обеспечивает транзакционный обработчик таблиц для MySQL.

Использование BerkeleyDB повышает для ваших таблиц шансы уцелеть после сбоев, а также предоставляет возможность осуществлять операции COMMIT и ROLLBACK для транзакций. Дистрибутив исходного кода MySQL поставляется с дистрибутивом BDB, содержащим несколько небольших исправлений, которые позволяют устранить определенные проблемы при работе с MySQL. Неисправленные версии BDB при работе с MySQL использовать нельзя.

В целях поддержания высокого уровня и качества интерфейса MySQL/BDB компания MySQL AB тесно сотрудничает с компанией Sleepycat.

Что касается поддержки таблиц BDB, то мы взяли на себя обязательство оказывать помощь нашим пользователям в выявлении проблем и создании воспроизводимых контрольных примеров для любых ошибок, возникающих при использовании таблиц BDB. Все такие контрольные примеры направляются в компанию Sleepycat, которая, в свою очередь, помогает нам выявлять и исправлять ошибки. Поскольку эта операция состоит из двух этапов, решение проблем с таблицами BDB может отнять у нас больше времени, чем устранение ошибок других обработчиков таблиц. Тем не менее, поскольку помимо MySQL код BerkeleyDB использовался с большим количеством других приложений, мы не думаем, что с ним возникнут серьезные проблемы (see section 1.5.1.1 Поддержка).

7.6.2 Установка `BDB`

Если вы загрузили бинарную версию MySQL, которая включает поддержку BerkeleyDB, просто выполните инструкции по установке бинарной версии MySQL (см. разделы section 2.2.10 Установка бинарного дистрибутива MySQL и see section 4.7.5 mysqld-max, расширенный сервер mysqld).

Чтобы произвести компиляцию MySQL с поддержкой Berkeley DB, загрузите MySQL версии 3.23.34 или выше и выполните настройку MySQL при помощи параметра --with-berkeley-db (see section 2.3 Установка исходного дистрибутива MySQL).

cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db

Чтобы получить самую последнюю информацию, обращайтесь к руководству, которое поставляется с дистрибутивом BDB.

Хотя Berkeley DB детально протестирован и надежен, BDB-интерфейс MySQL пока еще является бета-версией. Мы совершенствуем и оптимизируем его, чтобы в скором времени добиться стабильной работы.

7.6.3 Параметры запуска `BDB`

Если запуск производился с параметром AUTOCOMMIT=0, то изменения, сделанные в в таблицах BDB, не вносятся, пока не будет выполнена команда COMMIT. Кроме операции фиксации, можно запустить команду ROLLBACK, чтобы отменить изменения (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK).

Если вы работаете с параметром AUTOCOMMIT=1 (значение по умолчанию), внесенные изменения будут фиксироваться немедленно. Можно выполнить расширенную транзакцию при помощи команды SQL BEGIN WORK, после которой изменения не будут зафиксированы до запуска команды COMMIT (или будут отменены при помощи команды ROLLBACK).

Чтобы изменить параметры таблиц BDB, можно воспользоваться следующими опциями mysqld:

Параметр	Описание
`--bdb-home=directory`	Базовый каталог для таблиц `BDB`. Это должен быть тот же каталог, что и для `--datadir`.
`--bdb-lock-detect=#`	Обнаружение блокировки Berkeley; одно из значений: `DEFAULT`, `OLDEST`, `RANDOM` или `YOUNGEST`
`--bdb-logdir=directory`	Каталог файла журнала Berkeley DB
`--bdb-no-sync`	Отмена синхронной записи журналов на диск
`--bdb-no-recover`	Отмена запуска Berkeley DB в режиме восстановления
`--bdb-shared-data`	Запуск Berkeley DB в режиме параллельной обработки (при инициализации Berkeley DB не следует использовать `DB_PRIVATE`)
`--bdb-tmpdir=directory`	Имя временной директории Berkeley DB
`--skip-bdb`	Отмена использования таблиц `BDB`
`-O bdb_max_lock=1000`	Задает максимальное количество возможных блокировок (see section 4.5.6.4 `SHOW VARIABLES`).

Если используется параметр --skip-bdb, MySQL не будет инициализировать библиотеку Berkeley DB, что позволит сэкономить большое количество памяти. Разумеется, после включения этого параметра нельзя пользоваться таблицами BDB. если вы попытаетесь создать таблицу BDB, то в этом случае MySQL будет создавать таблицу MyISAM.

Обычно если предполагается использовать таблицы BDB, следует запускать mysqld без параметра --bdb-no-recover. Однако если файлы журналов BDB повреждены, то при попытке запуска mysqld могут возникнуть проблемы (see section 2.4.2 Проблемы при запуске сервера MySQL).

При помощи параметра bdb_max_lock задается максимальное количество блокировок (10000 по умолчанию), которые могут быть установлены на таблицу BDB. Это значение необходимо увеличить, если возникают ошибки bdb: Lock table is out of available locks или Got error 12 from ... при проведении длинных транзакций или когда mysqld должен просмотреть много строк, чтобы произвести необходимые вычисления для запроса.

Можно также изменить binlog_cache_size и max_binlog_cache_size, если используются большие многострочные транзакции (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK).

7.6.4 Характеристики таблиц `BDB`

Чтобы обеспечить возможность отката транзакций, для таблиц BDB поддерживается ведение файлов журналов. Для достижения максимальной производительности эти файлы необходимо разместить на разных с базой данных дисках, воспользовавшись параметром --bdb-logdir.
Каждый раз, когда создается новый файл журнала BDB, MySQL устанавливает контрольные точки и удаляет все файлы журналов, которые не нужны для текущих транзакций. Можно также в любое время запустить команду FLUSH LOGS, чтобы установить контрольную точку для таблиц Berkeley DB. Чтобы произвести восстановление после сбоя, необходимо воспользоваться резервными копиями таблицы, а также бинарным журналом MySQL (see section 4.4.1 Резервное копирование баз данных). Предупреждение: если удалить используемые старые файлы журналов, BDB не сможет осуществить восстановление, и в случае сбоя вы можете потерять данные.
В MySQL все таблицы BDB должны иметь первичные ключи, чтобы обеспечить возможность обращаться к ранее считанным строкам. Если первичный ключ не создан, MySQL создаст его и будет поддерживать скрытый первичный ключ. Длина скрытого ключа составляет 5 байтов, а его значение увеличивается при каждой попытке вставки.
Если все столбцы, к которым производится обращение в таблице BDB, являются частью одного индекса или одного первичного ключа, то MySQL может выполнить запрос, не обращаясь к самой строке. Для таблиц MyISAM это справедливо только если столбцы являются частью одного индекса.
Первичный ключ обеспечивает более быструю обработку, чем любой другой ключ, так как он хранится вместе с данными строки. Поскольку остальные ключи хранятся как данные ключа + PRIMARY KEY, очень важно иметь как можно более короткие первичные ключи, чтобы сэкономить дисковое пространство и увеличить производительность.
Команда LOCK TABLES работает с таблицами BDB точно так же, как и с другими таблицами. Если команда LOCK TABLE не используется, MySQL устанавливает на таблицу внутреннюю множественную блокировку записи, чтобы обеспечить правильную блокировку, если другой поток установит блокировку таблицы.
Внутренняя блокировка в таблицах BDB осуществляется на уровне страниц.
Команда SELECT COUNT(*) FROM table_name выполняется медленно, так как для таблиц BDB не поддерживается подсчет количества строк в таблице.
Сканирование осуществляется медленнее, чем в таблицах MyISAM, так как данные в таблицах BDB хранятся в B-деревьях, а не в отдельных файлах данных.
Приложение всегда должно быть готово к обработке ситуаций, в которых любые изменения таблицы BDB могут привести к автоматическому откату и любое считывание может вызвать сбой из-за возникновения взаимоблокировки.
Ключи не являются пакованными как в MyISAM. Иначе говоря, информация по ключам в таблицах BDB займет несколько больше места по сравнению с таблицами MyISAM.
В таблице BDB всегда имеются промежутки, благодаря чему можно вставлять новые строки в середину дерева ключа. Из-за этого таблицы BDB несколько больше, чем таблицы MyISAM.
Оптимизатору необходимо знать приблизительное количество строк в таблице. В MySQL этот вопрос решается путем подсчета количества вставок и поддержки этой информации в отдельном сегменте каждой таблицы BDB. Если операторов DELETE или ROLLBACK выполнялось не слишком много, это количество должно быть достаточно точным для оптимизатора MySQL, но MySQL сохраняет это число только при закрытии, и оно в случае аварийного завершения работы MySQL может оказаться неверным. Если число не соответствует действительности на 100% - ничего страшного в этом нет. Количество строк можно обновить, запустив команду ANALYZE TABLE или OPTIMIZE TABLE (см. разделы section 4.5.2 Синтаксис команды ANALYZE TABLE и see section 4.5.1 Синтаксис команды OPTIMIZE TABLE).
Если таблица BDB займет все пространство на диске, то будет выведено сообщение об ошибке (возможно, ошибка 28) и выполнен откат транзакции. В отличие от BDB, таблицы MyISAM и ISAM в mysqld будут ожидать, пока не появится свободное место, а потом продолжат работу.

7.6.5 Что нам нужно исправить в `BDB` в ближайшем будущем:

Процесс одновременного открытия многих таблиц BDB производится очень медленно. Если вы собираетесь применять таблицы BDB, не следует создавать очень большой кэш таблицы (например, больше 256 Кб) и необходимо использовать параметр --no-auto-rehash для клиента mysql. Мы планируем частично исправить это в версии 4.0.
Команда SHOW TABLE STATUS еще не предоставляет достаточного количества информации по таблицам BDB.
Оптимизация производительности.
Переход на запрет использования блокировок при сканировании таблиц.

7.6.6 Операционные системы, поддерживаемые `BDB`

На данный момент нам известно, что таблицы BDB работают со следующими операционными системами.

Linux 2.x Intel
Solaris SPARC
Caldera (SCO) OpenServer
Caldera (SCO) UnixWare 7.0.1

И не работают со следующими:

Linux 2.x Alpha
Max OS X

Этот список неполон. Мы будем обновлять его по мере поступления свежей информации.

Если вы собираете MySQL с поддержкой таблиц BDB и получаете вот такую ошибку в файле журнала при запуске mysqld:

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init dtabases

То это означает, что таблицы BDB не поддерживаются на вашей платформе. В этом случае вам следует пересобрать MySQL без поддержки таблиц BDB.

7.6.7 Ограничения таблиц `BDB`

Ниже приведены ограничения при использовании таблиц BDB:

Таблицы BDB хранятся в файле `.db', который находится в том же каталоге, где был создан (это сделано для того, чтобы была возможность обнаруживать блокировки в многопользовательской среде с поддержкой символических ссылок). Но вследствие этого таблицы BDB нельзя перемещать между каталогами!
При создании резервных копий таблиц BDB необходимо использовать mysqldump или создать резервные копии всех файлов `table_name.db' и файлов журналов BDB. Файлы журналов BDB - это файлы в базовом каталоге донных с именами `log.XXXXXX' (6 цифр). Обработчик таблицы BDB хранит незавершенные транзакции в файлах журналов; их наличие требуется при запуске mysqld.

7.6.8 Ошибки, которые могут возникнуть при использовании таблиц `BDB`

Если в `hostname.err log' при запуске mysqld возникла следующая ошибка:
```
bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version #
```
это означает, что новая версия BDB не поддерживает старый формат файлов журналов. В этом случае необходимо удалить все файлы журналов BDB из каталога своей базы данных (файлы формата `log.XXXXXXXXXX' ) и перезапустить mysqld. Мы также рекомендуем сохранить содержимое BDB-баз данных в файл путем вызова mysqldump --opt, удалить старые файлы таблиц и восстановить базы данных из сохраненного файла.
Если запуск производится не в режиме auto_commit и происходит удаление таблицы, которая используется другим потоком, в файле ошибок MySQL могут появится следующие записи:
```
001119 23:43:56 bdb: Missing log fileid entry
001119 23:43:56 bdb: txn_abort: Log undo failed for LSN:
1 3644744: Invalid
```
Это не смертельно, но мы не рекомендуем удалять таблицы, если вы не находитесь в режиме auto_commit, пока эта проблема не будет решена (а решить ее вовсе не просто).

Go to the first, previous, next, last section, table of contents.

Комментарий: можно использовать BB-коды Максимальная длина комментария - 4000 символов.	CodeNet ВКонтакте Facebook Twitter Google Яндекс Чтобы оставить комментарий, необходимо авторизоваться. Можно ввести логин и пароль, или авторизоваться через социальные сети.

Ваш аккаунт

Последние темы форума

Почтовая рассылка

Типы таблиц MySQL

Оставить комментарий