Содержание
- 7.1. Таблицы
MyISAM
- 7.2. Таблицы
MERGE
- 7.3. Таблицы
ISAM
- 7.4. Таблицы
HEAP
- 7.5. Таблицы
InnoDB
- 7.5.1. Обзор таблиц InnoDB
- 7.5.2. Параметры запуска InnoDB
- 7.5.3. Создание табличной области InnoDB
- 7.5.4. Создание таблиц InnoDB
- 7.5.5. Добавление и удаление файлов данных и журналов InnoDB
- 7.5.6. Создание резервных копий и восстановление баз данных InnoDB
- 7.5.7. Перенесение базы данных InnoDB на другой компьютер
- 7.5.8. Транзакционная модель InnoDB
- 7.5.9. Реализация многовариантности
- 7.5.10. Структуры таблиц и индексов
- 7.5.11. Управление файловым пространством и дисковый ввод/вывод
- 7.5.12. Обработка ошибок
- 7.5.13. Ограничения для таблиц InnoDB
- 7.5.14. История изменений InnoDB
- 7.5.15. Контактная информация для получения данных по InnoDB
- 7.6. Таблицы
BDB
или BerkeleyDB - 7.6.1. Обзор таблиц
BDB
- 7.6.2. Установка
BDB
- 7.6.3. Параметры запуска
BDB
- 7.6.4. Характеристики таблиц
BDB
- 7.6.5. Что нам нужно исправить в
BDB
в ближайшем будущем: - 7.6.6. Операционные системы, поддерживаемые
BDB
- 7.6.7. Ограничения таблиц
BDB
- 7.6.8. Ошибки, которые могут возникнуть при
использовании таблиц
BDB
- 7.6.1. Обзор таблиц
В MySQL версии 3.23.6 можно было выбирать
из трех основных форматов таблиц
(ISAM
, HEAP
и
MyISAM
). Более новые версии MySQL
могут поддерживать дополнительные
типы таблиц (InnoDB
или
BDB
) - в зависимости от
варианта установки.
При создании новой таблицы можно указать MySQL, какой тип таблицы для нее использовать.
Для таблицы и определений столбцов
MySQL всегда создает файл
.frm
. Индекс и данные
хранятся в других файлах, в
зависимости от типа таблиц.
Обратите внимание: если необходимо
использовать таблицы InnoDB
,
при запуске следует указать параметр
innodb_data_file_path
. See
Раздел 7.5.2, «Параметры запуска InnoDB».
Если попытаться воспользоваться
таблицей, которая не была
активизирована или добавлена при
компиляции, MySQL вместо нее создаст
таблицу типа MyISAM
. Это очень
полезная функция, когда необходимо
произвести копирование таблиц с
одного SQL-сервера на другой, а серверы
поддерживают различные типы таблиц
(например, при копировании таблиц на
подчиненный компьютер, который
оптимизирован для быстрой работы без
использования транзакционных
таблиц).
Тем не менее, такое автоматическое изменение таблиц может сбить с толку новых пользователей MySQL. Мы планируем устранить эту проблему путем введения предупреждений в новом клиент-серверном протоколе в MySQL 4.1, которые будут выводиться при автоматическом изменении типов таблиц.
Преобразовывать таблицы из одного
типа в другой можно при помощи
оператора ALTER TABLE
. See
Раздел 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
Раздел 7.5.2, «Параметры запуска InnoDB».
Преимущества нетранзакционных таблиц (non-transaction-safe tables, NTST):
Работать с ними намного быстрее, так как не выполняются дополнительные транзакции.
Для них требуется меньше дискового пространства, так как не применяются дополнительные транзакции.
Для обновлений используется меньше памяти.
В операторах можно сочетать таблицы TST и NTST, чтобы взять лучшее от каждого типа.
Тип таблиц MyISAM
принят по
умолчанию в MySQL версии 3.23. Он
основывается на коде ISAM
и
обладает в сравнении с ним большим
количеством полезных дополнений.
Индекс хранится в файле с
расширением .MYI
(MYIndex), а
данные - в файле с расширением
.MYD
(MYData). Таблицы
MyISAM
можно
проверять/восстанавливать при
помощи утилиты myisamchk
. See
Раздел 4.4.6.7, «Использование myisamchk для
послеаварийного восстановления». Таблицы
MyISAM
можно сжимать при
помощи команды myisampack
,
после чего они будут занимать
намного меньше места. See
Раздел 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 Раздел 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 Раздел 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
Раздел 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 Раздел 4.1.1, «Параметры командной строки
mysqld
».
В MySQL могут поддерживаться
различные типы индексов, однако
обычно это тип ISAM
или
MyISAM
. Для обоих типов
используется индекс B-дерева, так
что приблизительно вычислить
размер индексного файла можно по
формуле (длина
ключа+4)/0.67
, просуммированной
по всем ключам (приведено значение
для самого худшего случая, когда
все ключи вставлены в порядке
сортировки и сжатые ключи
отсутствуют).
В индексах строк сжаты пробелы.
Если первая часть индекса является
строкой, префикс также будет сжат.
Сжатие пробелов позволяет
уменьшить индексный файл в
сравнении со значениями,
вычисляемыми по приведенной выше
формуле, если столбец строки
содержит много пробелов в конце
строки или является столбцом
VARCHAR
, который не всегда
используется на полную длину.
Сжатие префикса используется с
ключами, которые начинаются со
строки. Сжатие префикса полезно в
случае, если имеется много строк с
одинаковыми префиксами.
В таблицах MyISAM
можно
также сжимать числа в префиксах,
указывая при создании таблицы
PACK_KEYS=1
. Это полезно в
случае, когда имеется много
целочисленных ключей с
одинаковыми префиксами, а числа
хранятся с первым старшим байтом.
В MyISAM
поддерживается три
различных типа таблиц. Два из них
выбираются автоматически, в
зависимости от типа используемых
столбцов. Третий - сжатые таблицы -
может быть создан только при
помощи инструмента
myisampack
.
При использовании с таблицами
команд CREATE
или
ALTER
для таблиц, у которых
нет форсированной настройки
BLOB
, можно задать формат
DYNAMIC
или FIXED
с
параметром таблицы
ROW_FORMAT=#
. В будущем можно
будет сжимать/разжимать таблицы,
указывая ROW_FORMAT=compressed |
default
для ALTER TABLE
. See
Раздел 6.5.3, «Синтаксис оператора CREATE TABLE
».
Это формат, принятый по умолчанию.
Он используется, когда таблица не
содержит столбцов VARCHAR
,
BLOB
или TEXT
.
Данный формат - самый простой и безопасный, а также наиболее быстрый при работе с дисками. Скорость достигается за счет простоты поиска информации на диске: в таблицах статического формата с индексом для этого достаточно всего лишь умножить номер строки на ее длину.
Кроме того, при сканировании таблицы очень просто считывать постоянное количество записей при каждом чтении с диска.
Если произойдет сбой во время
записи в файл MyISAM
фиксированного размера,
myisamchk
в любом случае
сможет легко определить, где
начинается и заканчивается любая
строка. Поэтому обычно удается
восстановить все записи, кроме
тех, которые были частично
перезаписаны. Отметим, что в MySQL
все индексы могут быть
восстановлены. Свойства
статических таблиц следующие:
Все столбцы
CHAR
,NUMERIC
иDECIMAL
расширены пробелами до ширины столбца;Очень быстрые;
Легко кэшируются;
Легко восстанавливаются после сбоя, так как записи расположены в фиксированных позициях;
Не нуждаются в реорганизации (при помощи
myisamchk
), кроме случаев, когда удаляется большое количество записей и необходимо вернуть дисковое пространство операционной системе.Для них обычно используется больше дискового пространства, чем для динамических таблиц.
Данный формат используется для
таблиц, которые содержат столбцы
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
.
Таблицы этого тип предназначены
только для чтения. Они
генерируются при помощи
дополнительного инструмента
myisampack
(pack_isam
для таблиц ISAM
):
Все дистрибутивы MySQL, даже выпущенные до предоставления общедоступной лицензии MySQL, могут читать таблицы, которые были сжаты при помощи
myisampack
.Сжатые таблицы занимают очень мало дискового пространства; таким образом при применении данного типа значительно снижается использование дискового пространства. Это полезно при работе с медленными дисками (такими как компакт-диски).
Каждая запись сжимается отдельно (незначительные издержки при доступе). Заголовки у записей фиксированные (1-3 байта), в зависимости от самой большой записи в таблице. Все столбцы сжимаются по-разному. Ниже приведено описание некоторых типов сжатия:
Обычно для каждого столбца используются разные таблицы Хаффмана.
Сжимаются пробелы суффикса.
Сжимаются пробелы префикса.
Для хранения чисел со значением 0 отводится 1 бит.
Если у значений в целочисленном столбце небольшой диапазон, столбец сохраняется с использованием минимального по размерам возможного типа. Например, столбец
BIGINT
(8 байт) может быть сохранен как столбецTINYINT
(1 байт) если все значения находятся в диапазоне от0
до255
.Если в столбце содержится небольшое множество возможных значений, тип столбца преобразовывается в
ENUM
.Столбец может содержать сочетание указанных выше сжатий.
Для таблиц этого типа возможна обработка записей с фиксированной или динамической длиной.
Таблицы данного типа могут быть распакованы при помощи команды myisamchk.
Формат файлов, который используется для хранения данных в MySQL, тщательно тестировался, но всегда существуют обстоятельства, которые могут привести к повреждениям таблиц баз данных.
Несмотря на то, что формат таблиц
MyISAM
очень надежен (все
изменения в таблице записываются
до возвращения значения
оператора SQL), таблица, тем не
менее, может быть повреждена.
Такое происходит в следующих
случаях:
Процесс
mysqld
уничтожен во время осуществления записи;Неожиданное отключение компьютера (например, если выключилось электропитание);
Ошибка аппаратного обеспечения;
Использование внешней программы (например
myisamchk
) на открытой таблице.Ошибка программного обеспечения в коде MySQL или
MyISAM
.
Типичные признаки поврежденной таблицы следующие:
Во время выбора данных из таблицы выдается ошибка
Incorrect key file for table: '...'. Try to repair it
.Запросы не находят в таблице строки или выдают неполные данные.
Проверить состояние таблицы
можно при помощи команды CHECK
TABLE
. См. раздел See
Раздел 4.4.4, «Синтаксис CHECK TABLE
».
Для восстановления поврежденного
файла можно применить команду
REPAIR TABLE
. See
Раздел 4.4.5, «Синтаксис REPAIR TABLE
». Таблицу можно
восстановить и в случае, когда не
запущен mysqld
, при помощи
команды myisamchk
. See
Раздел 4.4.6.1, «Синтаксис запуска myisamchk
».
Если таблицы повреждены значительно, необходимо выяснить причину произошедшего! See Раздел A.4.1, «Что делать, если работа MySQL сопровождается постоянными сбоями».
Сначала следует определить,
послужил ли причиной повреждения
таблицы сбой mysqld
(это
можно легко проверить, просмотрев
последние строки restarted
mysqld
в файле ошибок
mysqld
). Если дело не в
этом, то необходимо составить
подробное описание
произошедшего. See
Раздел E.1.6, «Создание контрольного примера при
повреждении таблиц».
Клиенты неправильно используют таблицу или не закрыли ее надлежащим образом
В заголовке каждого файла 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
следует
избегать, так как она заменяет
файл данных новым, информация о
котором не передается другим
серверам.
Таблицы MERGE
(объединение)
являются новшеством версии MySQL 3.23.25.
В настоящее время код находится еще
на стадии разработки, но, тем не
менее, должен быть достаточно
стабилен.
Таблица MERGE
(или таблица
MRG_MyISAM
) представляет собой
совокупность идентичных таблиц
MyISAM
, которые могут
использоваться как одна таблица. К
совокупности таблиц можно
применять только команды
SELECT
, DELETE
и
UPDATE
. Если же попытаться
применить к таблице MERGE
команду DROP
, она
подействует только на определение
MERGE
.
Обратите внимание на то, что команда
DELETE FROM merge_table
без
параметра WHERE
очищает
только распределение для таблицы,
но ничего не удаляет из
распределенных таблиц (мы планируем
исправить это в версии 4.1).
Под идентичными таблицами
подразумеваются таблицы, созданные
с одинаковой структурой и ключами.
Нельзя объединять таблицы, в
которых столбцы сжаты разными
методами или не совпадают, либо
ключи расположены в другом порядке.
Тем не менее, некоторые таблицы
можно сжимать при помощи команды
myisampack
. See Раздел 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 Раздел 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
и всеми базовыми таблицами, чтобы обработчик прочитал новый файл определения.
При работе с таблицами
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
.
В MySQL пока еще можно применять и
устаревший тип таблиц ISAM
.
В ближайшем времени этот тип будет
исключен (возможно, в MySQL 5.0), так как
MyISAM является улучшенной
реализацией тех же возможностей. В
таблицах ISAM
используется
индекс B-tree. Индекс хранится в файле
с расширением .ISM
, а
данные - в файле с расширением
.ISD
. Таблицы ISAM
можно проверять/восстанавливать
при помощи утилиты isamchk
(see
Раздел 7.1, «Таблицы MyISAM
»).
Ниже перечислены свойства таблиц
ISAM
:
Ключи со сжатой и фиксированной длиной
Фиксированная и динамическая длина записи
16 ключей с 16 частями ключей/ключами
Максимальная длина ключа 256 (по умолчанию)
Данные хранятся в машинном формате; благодаря этому обеспечивается скорость, но возникает зависимость от компьютера/ОС.
Большинство параметров таблиц
MyISAM
также соответствуют
таблицам ISAM
. See
Раздел 7.1, «Таблицы MyISAM
». Ниже перечислены
основные отличия таблиц
ISAM
от MyISAM
:
Таблицы
ISAM
не являются переносимыми в двоичном виде с одной ОС/платформы на другую;Невозможна работа с таблицами > 4Гб.
В строках поддерживается только сжатие префикса.
Ограничения по маленьким ключам.
Динамические таблицы больше фрагментируются.
Таблицы сжимаются при помощи
pack_isam
, а не при помощиmyisampack
.
Если вы хотите преобразовать
таблицу ISAM
в таблицу
MyISAM
, чтобы иметь
возможность работать с такими
утилитами, как mysqlcheck
,
воспользуйтесь оператором ALTER
TABLE
:
mysql> ALTER TABLE tbl_name TYPE = MYISAM;
Встроенные версии MySQL не
поддерживают таблицы ISAM
.
Для 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.1. Обзор таблиц InnoDB
- 7.5.2. Параметры запуска InnoDB
- 7.5.3. Создание табличной области InnoDB
- 7.5.4. Создание таблиц InnoDB
- 7.5.5. Добавление и удаление файлов данных и журналов InnoDB
- 7.5.6. Создание резервных копий и восстановление баз данных InnoDB
- 7.5.7. Перенесение базы данных InnoDB на другой компьютер
- 7.5.8. Транзакционная модель InnoDB
- 7.5.9. Реализация многовариантности
- 7.5.10. Структуры таблиц и индексов
- 7.5.11. Управление файловым пространством и дисковый ввод/вывод
- 7.5.12. Обработка ошибок
- 7.5.13. Ограничения для таблиц InnoDB
- 7.5.14. История изменений InnoDB
- 7.5.15. Контактная информация для получения данных по 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 являются только исполняемые
файлы сервера. См. разделы
Раздел 2.2.8, «Установка бинарного дистрибутива MySQL» и See
Раздел 4.7.5, «mysqld-max
, расширенный сервер
mysqld
».
Чтобы произвести компиляцию MySQL с
поддержкой InnoDB, загрузите MySQL-3.23.34a
или более новую версию с
http://www.mysql.com/ и настройте MySQL при
помощи параметра --with-innodb
.
См. раздел руководства MySQL по
установке дистрибутива исходного
кода MySQL, See Раздел 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 Раздел 7.5.2, «Параметры запуска InnoDB».
InnoDB распространяется на условиях
общедоступной лицензии версии 2 (от
июня 1991 года). В дистрибутиве
исходного кода MySQL InnoDB находится в
подкаталоге innobase
.
Чтобы использовать таблицы 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 . |
Предположим, что вы установили 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
Если InnoDB выдает ошибку операционной системы во время операции с файлом, то причиной возникшей проблемы, как правило, является одна из следующих:
Вы не создали каталоги для файлов данных или журналов InnoDB.
У
mysqld
нет прав на создание файлов в этих каталогах.mysqld
не считал нужный файлmy.cnf
илиmy.ini
и, соответственно, не получил указанных вами параметров.Диск переполнен или превышена квота использования диска.
Вы создали подкаталог, имя которого совпадает с указанным файлом данных.
Синтаксическая ошибка в
innodb_data_home_dir
илиinnodb_data_file_path
.
Если что-то происходит не так во
время создания базы данных InnoDB,
необходимо удалить все файлы,
созданные InnoDB. В их число входят
все файлы данных, все файлы
журналов, небольшой архивный файл
журнала; если вы уже создали
какие-либо таблицы InnoDB, то следует
также удалить соответствующие им
файлы .frm
, которые
находятся в каталогах баз данных
MySQL. После этого можно попробовать
создать базу данные 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.
Точными являются
зарезервированные размеры таблицы
и индекса, значения которых
выдаются в байтах.
В 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
), и начать свою
работу сначала, а не ждать
завершения выполнения миллионов
операций ввода/вывода диска.
Начиная с версии 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'
Ограничения внешнего ключа выводятся в комментариях к таблице.
Начиная с версий 3.23.50 и 4.0.2, можно
указать последний файл данных InnoDB
как autoextend
. Можно также
увеличить табличную область,
указав дополнительные файлы
данных. Для этого необходимо
остановить сервер MySQL, внести
изменения в файл my.cnf
,
добавив новый файл данных к
innodb_data_file_path
, а затем
запустить сервер MySQL снова.
На данный момент нельзя удалить
файл данных из InnoDB. Чтобы уменьшить
размер своей базы данных,
необходимо воспользоваться
mysqldump
, чтобы сделать дамп
всех своих таблиц, создать новую
базу данных и импортировать
таблицы в новую базу данных.
Если необходимо изменить
количество или размер файлов
журналов InnoDB, необходимо
остановить MySQL и убедиться, что
работа была завершена без ошибок.
После этого нужно скопировать
старые файлы журналов в безопасное
место - на случай, если завершение
работы было произведено с ошибками
и потребуется восстановление базы
данных. Затем следует удалить
старые файлы журналов из каталога
файлов журналов, внести изменения
в my.cnf
и снова запустить
MySQL. InnoDB при запуске сообщит о
создании новых файлов журналов.
Чтобы обеспечить безопасное управление базами данных, необходимо регулярно создавать резервные копии.
Существует интерактивный инструмент, который можно использовать для создания резервных копий своих баз данных InnoDB, когда они открыты, - InnoDB Hot Backup. Для своей работы InnoDB Hot Backup не требует закрытия базы данных, блокировки данных или нарушения обычного хода обработки базы данных. InnoDB Hot Backup является платным дополнительным инструментом, не входящим в стандартный дистрибутив MySQL. Чтобы получить дополнительную информацию о нем и просмотреть копии экрана, см. домашнюю страницу InnoDB Hot Backup http://www.innodb.com/hotbackup.html.
Если у вас есть возможность остановить сервер 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.
В InnoDB реализован механизм контрольных точек, который получил название нечеткой контрольной точки. В InnoDB измененные страницы базы данных сбрасываются из буфера на диск небольшими частями. Сбрасывать содержимое буфера одним большим пакетом нет необходимости, так как это приведет к временной остановке обработки операторов пользователей.
В случае восстановления после сбоя InnoDB производит поиск меток контрольных точек, записанных в файлы журналов. Известно, что все изменения базы данных, внесенные перед меткой, уже записаны в образ базы данных на диске. Затем InnoDB производит сканирование файлов журналов начиная от места контрольной точки, и вносит зафиксированные изменения в базу данных.
Запись в файлы журналов в InnoDB осуществляется по круговому методу. Все внесенные изменения, после которых страницы базы данных в буфере начинают отличаться от образа на диске, должны быть записаны в файлы журналов, на случай, если InnoDB понадобится произвести восстановление. Это означает, что когда InnoDB начинает повторно использовать файл журнала по круговому методу, производится проверка на наличие в образах страниц базы данных на диске изменений, зафиксированных в файле журнала, который InnoDB собирается повторно использовать. Иначе говоря, необходимость поставить контрольную точку зачастую приводит к тому, что InnoDB сбрасывает измененные страницы базы данных на диск.
Из сказанного выше становится понятно, почему при больших файлах журналов сокращается количество дисковых операций ввода/вывода при создании контрольных точек. Иногда имеет смысл задавать общий размер файлов журналов равным буферному пулу или даже больше. Недостатком больших файлов журналов является то, что восстановление после сбоя может длиться дольше, так как к базе данных придется применить больше информации из файла журнала.
Файлы данных и журналов InnoDB на
двоичном уровне совместимы на всех
платформах, если на компьютерах
совпадает формат чисел с плавающей
десятичной запятой. Базу данных
InnoDB можно перенести, просто
скопировав все относящиеся к ней
файлы (список которых был приведен
в предыдущем разделе, посвященном
созданию резервных копий базы
данных). Если компьютеры имеют
различные форматы чисел с
плавающей десятичной запятой, но
типы данных FLOAT
или
DOUBLE
в ваших таблицах не
задействованы, последовательность
действий остается точно такой же:
нужно просто скопировать все
относящиеся к базе данных файлы.
Если же при наличии различных
форматов в ваших таблицах
содержатся данные с плавающей
десятичной запятой, то для
перемещения таких таблиц
необходимо воспользоваться
командами mysqldump
и
mysqlimport
.
Чтобы увеличить скорость обработки, можно отключить автоматическую фиксацию транзакций при импортировании в свою базу данных, исходя из предположения, что ваша табличная область содержит достаточно пространства для отката большого сегмента на случай генерации большой транзакции импортирования. Фиксация производится только после импорта всей таблицы или сегмента таблицы.
- 7.5.8.1. Согласованное чтение
- 7.5.8.2. Чтение с блокировкой
- 7.5.8.3. Блокировка следующего ключа: устранение проблемы с фантомом
- 7.5.8.4. Блокировка, осуществляемая различными операторами SQL в InnoDB
- 7.5.8.5. Обнаружение и откат взаимоблокировки (deadlock)
- 7.5.8.6. Пример работы согласованного чтения в InnoDB
- 7.5.8.7. Каким образом избежать взаимоблокировок (deadlock)?
- 7.5.8.8. Рекомендации по увеличению производительности
- 7.5.8.9. InnoDB Monitor
Назначение транзакционной модели 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
.
Согласованное чтение означает, что для того, чтобы предоставить запросу копию базы данных на текущий момент времени, используется многовариантность таблиц InnoDB. Для запроса доступны лишь те изменения, которые были внесены транзакциями, зафиксированными на этот момент времени, и не доступны изменения, сделанные незафиксированными или проведенными позже транзакциями. Исключением из данного правила могут стать только изменения, внесенные транзакцией, направляющей текущий запрос.
Если вы используете уровень
изоляции по умолчанию REPEATABLE
READ
, то все выборки читают
снимок, сделанный первым чтением
в этой транзакции. Вы можете
получить более свежую копию для
своих запросов - для этого следует
зафиксировать текущую транзакцию
и направить новые запросы.
Согласованное чтение является
режимом по умолчанию, в котором в
InnoDB обрабатываются операторы
SELECT
при READ
COMMITTED
или REPEATABLE READ
уровнях изоляции. При
согласованном чтении не
устанавливаются блокировки на
таблицы, к которым обращается
запрос, и, таким образом,
остальные пользователи могут
вносить изменения в эти таблицы
одновременно с согласованным
чтением таблиц.
В некоторых случаях использовать
согласованное чтение
нецелесообразно. Приведем пример.
Допустим, что необходимо добавить
новую строку в таблицу
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
.
При блокировке на уровне строк в 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.
Блокировку следующего ключа можно использовать для того, чтобы провести проверку уникальности значений в своей программе. Если данные считываются в режиме совместного доступа и отсутствует дубликат строки, которую необходимо вставить, то можно безопасно вставлять свою строку и быть уверенным, что благодаря блокировке следующего ключа, установленной на предшествующей строке во время чтения, будет предотвращена вставка дублирующейся строки. Таким образом, блокировка следующего ключа позволяет ``заблокировать'' отсутствие чего-либо в таблице.
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 Раздел 7.5.13, «Ограничения для таблиц InnoDB».
InnoDB автоматически обнаруживает взаимоблокировку транзакций и производит откат транзакции или транзакций для предотвращения взаимоблокировок. Начиная с версии 4.0.5 InnoDB будет пытаться выбрать меньшую транзакцию для отката. Размер транзакции определяется количеством строк, которые должны быть добавлены, обновлены или удалены. До версии 4.0.5 InnoDB всегда откатывал транзакцию, запрос на блокировку которой вызвал возникновение взаимоблокировки, то есть замкнутого цикла в графике ожиданий транзакций.
InnoDB не может обнаружить
взаимоблокировку, установленную
оператором MySQL LOCK TABLES
,
или блокировку, установленную
отличным от InnoDB обработчиком
таблиц. Такие ситуации необходимо
исправлять при помощи параметра
innodb_lock_wait_timeout
, который
задается в my.cnf
.
Когда InnoDB выполняет полный откат транзакции, все блокировки, установленные транзакцией, снимаются. Тем не менее, если в результате ошибки производится откат только одного оператора SQL, некоторые блокировки, установленные оператором, могут остаться в силе. Это происходит потому, что InnoDB хранит блокировку строк в формате, по которому впоследствии нельзя определить, каким оператором SQL была установлена блокировка.
Допустим, вы используете уровень
изоляции, установленый по
умолчанию - 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;
Взаимоблокировки - классическая проблема транзакционных баз данных. Они не опасны до тех пор пока не становятся настолько частыми, что вы вообще не можете запустить некоторые транзакции. Обычно вы можете написать свои приложения таким образом, что они всегда будут подготавливать перезапуск транзакции, если произошел откат из-за взаимоблокировок.
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 мы используем метод таймаута для разрешения взаимоблокировки.
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.
Начиная с версии 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
содержит статистическую информацию по записываемым и считываемым страницам. По этим данным можно вычислить, сколько запросов ввода/вывода по файлам данных выполняется на данный момент.
Поскольку InnoDB является многовариантной базой данных, информация по старым версиям строк в ней хранится в табличной области. Эта информация содержится в структуре данных, которую мы по аналогии со структурой данных в Oracle называем сегментом отката.
К каждому внутреннему представлению строки таблицы, хранящейся в базе данных InnoDB, добавляется по два поля. В 6-байтовом поле хранится идентификатор последней транзакции, которая производила вставку или обновление строки. Удаление рассматривается как обновление, при котором специальный бит удаления строки помечается соответствующим образом. Помимо этого, каждая строка содержит также 7-байтовое поле, которое называется указателем отката. Указатель отката указывает на запись журнала отмены, занесенную в сегмент отката. Если строка была обновлена, запись журнала отмены содержит необходимую информацию для восстановления содержимого строки до обновления.
Информация из сегмента отката в базе данных InnoDB используется для того, чтобы произвести отмену, необходимую для отката транзакции, а также для создания предыдущих версий строки для согласованного чтения.
Журналы отмены в сегменте отката разделяются на журналы вставки и журналы обновления. Журналы отмены вставки необходимы только для отката транзакций и могут быть удалены сразу после фиксации транзакции. Журналы отмены обновления используются для согласованного чтения, и их можно удалять только после того, как не останется транзакций, для которых в InnoDB определена копия, создающая при согласованном чтении раннюю версию строки по информации из журнала отмены обновления.
Не забывайте регулярно фиксировать свои транзакции, включая транзакции, использующие согласованное чтение. В противном случае InnoDB не сможет удалить данные из журналов отмены обновления, что приведет к разрастанию сегмента отката, который может занять всю вашу табличную область.
Физический размер записи журнала отмены в сегменте отката обычно меньше, чем соответствующая вставка или обновленная строка. Эту информацию можно использовать для вычисления размера пространства, необходимого для сегмента отката.
В нашей многовариантной схеме строка физически не удаляется из базы данных немедленно после удаления ее при помощи оператора SQL. Только после того, как InnoDB сможет удалить запись журнала отмены обновления, занесенную для удаления, соответствующая строка и ее индексная запись из базы данных могут быть физически удалены. Эта операция удаления называется чисткой. Она производится достаточно быстро - на нее уходит столько же времени, как и на выполнение оператора удаления SQL.
В MySQL информация словаря данных
таблиц хранится в файлах
.frm
, расположенных в
каталогах баз данных. Но для каждой
таблицы InnoDB имеются также свои
записи во внутренних словарях
данных InnoDB в табличной области.
Когда MySQL удаляет таблицу или базу
данных, необходимо удалить как
файлы .frm
, так и
соответствующие записи в словаре
данных InnoDB. Именно поэтому нельзя
перемещать таблицы InnoDB между
базами данных путем простого
перемещения файлов .frm
.
По этой же причине DROP
DATABASE
не работал для таблиц
InnoDB в MySQL версий <= 3.23.43.
Для всех таблиц InnoDB есть
специальный индекс, в котором
хранятся данные строк - он
называется кластеризованным
индексом. Если в таблице
определить PRIMARY KEY
, то
индекс первичного ключа будет
кластеризированным индексом.
Если первичный ключ для таблицы не определен, то InnoDB самостоятельно создаст кластеризированный индекс; строки в этом индексе будут упорядочены по идентификатору строки, который InnoDB назначил строкам этой таблицы. Идентификатор строки представляет собой 6-байтовое поле, значение которого постоянно увеличивается при вставке новых строк. Таким образом, сортировка по идентификатору строки фактически представляет собой сортировку по последовательности вставки.
Доступ к строке через кластеризированный индекс осуществляется достаточно быстро, поскольку данные строки находятся на той же странице, к которой приводит поиск по индексу. Во многих базах данных информация и индексная запись традиционно хранятся на разных страницах. При больших размерах таблицы архитектура кластеризированных индексов часто позволяет сократить количество дисковых операций ввода/вывода по сравнению с традиционными решениями.
Записи в некластеризированных индексах (мы называем их также вторичными индексами), в InnoDB содержат значение первичного ключа для строки. InnoDB использует этот значение первичного ключа для поиска строки в кластеризированном индексе. Следует учитывать, что если первичный ключ достаточно велик, вторичные индексы будут занимать больше места.
Все индексы в InnoDB представляют собой B-деревья, в которых записи индексов хранятся в страницах ответвления дерева. По умолчанию размер индексной страницы составляет 16 Кб. При вставке новых записей InnoDB старается оставить 1 / 16 страницы свободной - для будущих вставок и обновлений индексных записей.
Если записи индекса вставлены в последовательном порядке (в порядке возрастания или убывания), то получившиеся индексные страницы будут заполнены примерно на 15/16. Для записей, которые вставляются в случайном порядке, эти значения составят от 1/2 до 15/16. Если коэффициент заполнения индексной страницы уменьшится и станет ниже 1/2, InnoDB попытается объединить записи индексного дерева, чтобы освободить страницу.
Нередко в программах для работы с базами данных первичный ключ является уникальным идентификатором и новые строки вставляются в порядке возрастания первичного ключа. Таким образом, вставки в кластеризированный индекс не требуют проведения случайных считываний с диска.
Что же касается вторичных индексов, то они, напротив, обычно не являются уникальными, так что вставки во вторичные индексы производятся в относительно случайном порядке. Это приводит к выполнению большого количества случайных дисковых операций ввода/вывода диска, если не используется специальный механизм, применяемый в InnoDB.
Если требуется вставить запись индекса во вторичный индекс, который не является уникальным, InnoDB проверяет, находится ли страница вторичного индекса в буферном пуле. Если она там есть, InnoDB произведет вставку непосредственно в страницу индекса. Но если страница индекса не найдена в буферном пуле, InnoDB вставляет запись в специальную структуру буфера вставок. Буфер вставок настолько мал, что полностью помещается в буферный пул, и вставки в него могут производиться очень быстро.
Буфер вставок периодически объединяется с деревьями вторичных индексов в базе данных. Часто, объединив несколько вставок на одной странице индексного дерева, можно за счет этого сократить количество операций ввода/вывода диска. Использование буфера вставки может ускорить вставку в таблицу в 15 раз.
Если база данных почти полностью помещается в основной памяти, то самым быстрым способом выполнения запросов по этой базе данных является использование хешированных индексов. В InnoDB существует автоматический механизм, который отслеживает поиск по индексу, осуществляемый по индексам, определенным для таблицы, и если InnoDB посчитает, что запросы выиграют от создания хешированного индекса, такой индекс будет создан автоматически.
Но следует учитывать, что хешированный индекс всегда создается на основе существующего индекса B-дерева таблицы. InnoDB может создать хешированный индекс на префиксах любой длины ключа, определенного для B-дерева, в зависимости от того, по какой схеме поиска InnoDB производит обзор индекса the B-дерева. Хешированный индекс может быть частичным: не обязательно кэшировать в буферном пуле весь индекс B-дерева. InnoDB будет создавать хешированные индексы по запросу для тех страниц индекса, к которым часто производится доступ.
Хотя механизм адаптивного хешированного индекса InnoDB приспосабливается к большому количеству основной памяти, он больше подходит для архитектуры баз данных основной памяти.
У всех записей индекса в InnoDB есть заголовок, состоящий из 6 байтов. Заголовок используется для связывания вместе последовательных записей, а также при блокировке на уровне строк.
Записи в кластеризированном индексе содержат поля для всех столбцов, определенных пользователем. Кроме того, имеется 6-байтовое поле для идентификатора транзакции и 7-байтовое поле для указателя строки.
Если пользователь не определил для таблицы первичный ключ, то в каждой записи кластеризированного индекса также содержится 6-байтовое поле идентификатора строки.
Все записи вторичного индекса содержат также все поля, определенные для ключа кластеризированного индекса.
Запись также содержит указатель на каждое поле записи. Если общая длина полей в записи меньше 128 байтов, то размер указателя будет 1 байт, в противном случае - 2 байта.
Когда пользователь после запуска
базы данных осуществляет первую
вставку в таблицу T, где определен
автоинкрементный столбец, и
пользователь не предоставляет
конкретного значения для этого
столбца, InnoDB выполняет SELECT
MAX(auto-inc-column) FROM T
, затем
присваивает это значение,
увеличенное на единицу, столбцу, и
автоматически увеличивает
счетчик таблицы. Эту
последовательность действий мы
называем инициализацией счетчика
автоматического увеличения для
таблицы T
.
Ту же последовательность действий InnoDB выполняет и для инициализации автоинкрементного счетчика вновь созданной таблицы.
Обратите внимание: если пользователь указывает при вставке значение автоинкрементного столбца 0, то InnoDB обрабатывает строку так, как будто значение не было указано.
Если после инициализации автоматического увеличения счетчика пользователь вставляет строку, в которой он явно указывает значение столбца, и это значение превышает текущее значение счетчика, то счетчик устанавливается в указанное значение столбца. Если пользователь явно не указывает значение, то InnoDB увеличивает счетчик на единицу и присваивает столбцу это новое значение.
При присвоении значений из счетчика механизм автоматического увеличения обходит блокировку и управление транзакциями. Вследствие этого могут возникнуть пропуски в последовательности чисел в случае, если производится откат транзакций, которые получили номера из счетчика.
Для случаев, когда пользователь присваивает столбцу отрицательное значение или если значение превысит максимальное целое число, которое может храниться в переменной целочисленного типа, поведение механизма механического увеличения не определено.
В операциях дискового ввода/вывода для таблиц 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, определив, что некоторые части табличной области полностью считываются в буферный пул, направляет оставшиеся считывания системе ввода/вывода.
Табличную область InnoDB составляют файлы данных, определенные в файле конфигурации. Файлы используются последовательно, распределения данных (striping ) по ним не производится. На данный момент вы не можете непосредственно указать, где должны быть размещены таблицы. Можно только воспользоваться знанием того факта, что для вновь созданной табличной области InnoDB будет распределяться место с начала памяти.
Табличная область состоит из страниц базы данных, принятый по умолчанию размер которых составляет 16 Кб. Эти страницы сгруппированы в блоки по 64 последовательных страницы. 'Файлы' внутри табличной области в InnoDB называются сегментами. Название 'сегмент отката' несколько не соответствует действительности, так как фактически в нем содержится много сегментов табличной области.
Для каждого индекса в InnoDB выделяется два сегмента: один - для конечных узлов B-дерева, а другой - для остальных узлов. Идея заключается в том, чтобы получить лучшее координирование конечных узлов, в которых содержатся данные.
Когда сегмент внутри табличной области возрастает, InnoDB выделяет первые 32 специально для этого сегмента. После этого InnoDB начинает выделять целые области для этого сегмента. Чтобы обеспечить хорошее координирование данных, InnoDB может единовременно добавить к большому сегменту до 4 областей,.
Некоторые страницы табличной области содержат битовые образы других страниц, поэтому несколько областей в табличной области InnoDB могут быть выделены не для целого сегмента, а только для отдельных страниц.
Когда вы запускаете запрос
SHOW TABLE STATUS FROM ... LIKE ...
для
получения информации по
доступному свободному
пространству табличной области,
InnoDB предоставит данные по
свободным областям табличной
области. InnoDB всегда резервирует
области для очистки и других
внутренних операций.
Зарезервированные области не
включаются в объем свободного
пространства.
Если из таблицы удаляются данные, InnoDB объединяет соответствующие индексы B-дерева. В зависимости от схемы удалений, когда освобождаются отдельные страницы или области табличной области, это пространство становится доступным для других пользователей. Удаление таблицы или удаление всех ее строк гарантированно освободит пространство для других пользователей, но не следует забывать, что физически строки удаляются только после проведения чистки, после чего они больше не нужны при откате транзакций или согласованном чтении.
Если в индексной таблице производились случайные вставки или удаления, индекс может стать фрагментированным. Под фрагментацией мы подразумеваем то, что физическое расположение индексных страниц на диске значительно отличается от алфавитного порядка страниц, или что в 64-страничных блоках много пустых страниц, которые занесены в индекс.
Скорость сканирования индекса
может возрасти, если периодически
использовать команду mysqldump для
копирования дампа таблицы в
текстовый файл, записи диска на
диск и повторного считывания
таблицы из дампа. Есть еще один
способ произвести дефрагментацию
- преобразовать таблицу при
помощи команды ALTER
в тип
MyISAM
, а затем обратно в
тип InnoDB. Обратите внимание на то,
что таблица типа MyISAM
должна помещаться в один файл в
вашей операционной системе.
Если вставки в индекс всегда производятся последовательно, а удаления - только с конца, то алгоритм управления файловым пространством InnoDB гарантирует, что фрагментации индекса не возникнет.
Обработка ошибок в 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.
Предупреждение: НЕЛЬЗЯ преобразовывать системные таблицы 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 Мб.
Контактная информация компании
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.1. Обзор таблиц
BDB
- 7.6.2. Установка
BDB
- 7.6.3. Параметры запуска
BDB
- 7.6.4. Характеристики таблиц
BDB
- 7.6.5. Что нам нужно исправить в
BDB
в ближайшем будущем: - 7.6.6. Операционные системы, поддерживаемые
BDB
- 7.6.7. Ограничения таблиц
BDB
- 7.6.8. Ошибки, которые могут возникнуть при
использовании таблиц
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
Раздел 1.5.1.1, «Поддержка»).
Если вы загрузили бинарную версию
MySQL, которая включает поддержку
BerkeleyDB, просто выполните инструкции
по установке бинарной версии MySQL
(см. разделы Раздел 2.2.8, «Установка бинарного дистрибутива MySQL» и see
Раздел 4.7.5, «mysqld-max
, расширенный сервер
mysqld
»).
Чтобы произвести компиляцию MySQL с
поддержкой Berkeley DB, загрузите MySQL
версии 3.23.34 или выше и выполните
настройку MySQL при помощи параметра
--with-berkeley-db
(see
Раздел 2.3, «Установка исходного дистрибутива MySQL»).
cd /path/to/source/of/mysql-3.23.34 ./configure --with-berkeley-db
Чтобы получить самую последнюю информацию, обращайтесь к руководству, которое поставляется с дистрибутивом BDB.
Хотя Berkeley DB детально протестирован и надежен, BDB-интерфейс MySQL пока еще является бета-версией. Мы совершенствуем и оптимизируем его, чтобы в скором времени добиться стабильной работы.
Если запуск производился с
параметром AUTOCOMMIT=0
, то
изменения, сделанные в в таблицах
BDB, не вносятся, пока не будет
выполнена команда COMMIT
.
Кроме операции фиксации, можно
запустить команду ROLLBACK
,
чтобы отменить изменения (see
Раздел 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
Раздел 4.5.6.4, «SHOW VARIABLES »). |
Если используется параметр
--skip-bdb
, MySQL не будет
инициализировать библиотеку Berkeley
DB, что позволит сэкономить большое
количество памяти. Разумеется,
после включения этого параметра
нельзя пользоваться таблицами BDB.
если вы попытаетесь создать
таблицу BDB
, то в этом
случае MySQL будет создавать таблицу
MyISAM
.
Обычно если предполагается
использовать таблицы BDB
,
следует запускать mysqld
без параметра --bdb-no-recover
.
Однако если файлы журналов
BDB
повреждены, то при
попытке запуска mysqld могут
возникнуть проблемы (see
Раздел 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
Раздел 6.7.1, «Синтаксис команд BEGIN/COMMIT/ROLLBACK
»).
Чтобы обеспечить возможность отката транзакций, для таблиц BDB поддерживается ведение файлов журналов. Для достижения максимальной производительности эти файлы необходимо разместить на разных с базой данных дисках, воспользовавшись параметром
--bdb-logdir
.Каждый раз, когда создается новый файл журнала
BDB
, MySQL устанавливает контрольные точки и удаляет все файлы журналов, которые не нужны для текущих транзакций. Можно также в любое время запустить командуFLUSH LOGS
, чтобы установить контрольную точку для таблиц Berkeley DB. Чтобы произвести восстановление после сбоя, необходимо воспользоваться резервными копиями таблицы, а также бинарным журналом MySQL (see Раздел 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
(см. разделы Раздел 4.5.2, «Синтаксис командыANALYZE TABLE
» и see Раздел 4.5.1, «Синтаксис командыOPTIMIZE TABLE
»).Если таблица
BDB
займет все пространство на диске, то будет выведено сообщение об ошибке (возможно, ошибка 28) и выполнен откат транзакции. В отличие отBDB
, таблицыMyISAM
иISAM
вmysqld
будут ожидать, пока не появится свободное место, а потом продолжат работу.
Процесс одновременного открытия многих таблиц
BDB
производится очень медленно. Если вы собираетесь применять таблицыBDB
, не следует создавать очень большой кэш таблицы (например, больше 256 Кб) и необходимо использовать параметр--no-auto-rehash
для клиентаmysql
. Мы планируем частично исправить это в версии 4.0.Команда
SHOW TABLE STATUS
еще не предоставляет достаточного количества информации по таблицам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.
Ниже приведены ограничения при
использовании таблиц BDB
:
Таблицы
BDB
хранятся в файле.db
, который находится в том же каталоге, где был создан (это сделано для того, чтобы была возможность обнаруживать блокировки в многопользовательской среде с поддержкой символических ссылок).Но вследствие этого таблицы
BDB
нельзя перемещать между каталогами!При создании резервных копий таблиц
BDB
необходимо использоватьmysqldump
или создать резервные копии всех файловtable_name.db
и файлов журналовBDB
. Файлы журналовBDB
- это файлы в базовом каталоге донных с именамиlog.XXXXXX
(6 цифр). Обработчик таблицыBDB
хранит незавершенные транзакции в файлах журналов; их наличие требуется при запускеmysqld
.
Если в
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
, пока эта проблема не будет решена (а решить ее вовсе не просто).
This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.