Как происходит в MySQL создание таблицы и ее изменение в случае необходимости


Содержание материала:

MySQL — изменение полей, структуры таблицы

Mysql alter add/drop index (добавление и удаление индексов):

Редактирование структуры полей в mysql:

Удалить поля в mysql:

Добавление полей в mysql:

Можно комбинировать добавление и удаления полей одним alter запросом:

Добавить primary key (PK) в mysql таблицу

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

Запрос удаления первично ключа:

Пример добавления PK:

insert+select синтаксис запроса

Видно, что опускается слово VALUES и в скобках пишется sql-запрос.

Узнать структуру таблицы в mysql

Запрос SHOW CREATE TABLE показывает запрос для создания таблицы, пример

MySQL: Создание таблицы (Create Table)

​Таблицы создание команды требует: Имя таблицы, Имена полей, Определений для каждого поля. Вот универсальный синтаксис SQL для создания таблиц MySQL:

Таблицы создание команды требует:

  • Имя таблицы
  • Имена полей
  • Определений для каждого поля

Вот универсальный синтаксис SQL для создания таблиц MySQL:

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

Вот несколько пунктов, которые нуждаются в пояснении:

  • Поле атрибута не равно NULL , используется потому что мы не хотим, чтобы она была нулем. Поэтому если пользователь попытается создать запись со значением NULL, то MySQL будет вызвана ошибка.
  • Поле атрибута auto_increment в MySQL не говорит, чтобы идти вперед и добавить следующий доступный номер в поле ID.
  • Ключевое слово первичный ключ используется для определения столбца в качестве первичного ключа. Вы можете использовать несколько столбцов, разделенных запятыми, чтобы определить первичный ключ.

Создание таблиц из командной строки:

Это легко создать MySQL таблицу из MySQL> подсказка. Вы будете использовать команды SQL создать таблицу чтобы создать таблицу.

Пример:

Вот пример, который создает tutorials_tbl:

Создание таблиц с помощью PHP скрипта:

Чтобы создать новую таблицу в любой существующей базы данных необходимо использовать функции PHP функции mysql_query(). Вы будете проходить свой второй аргумент при правильной команды SQL для создания таблицы.

Пример:

Вот пример создания таблицы с помощью PHP скрипта:

Как сделать последовательность (sequence) в MySQL?

Всем известно, что такого объекта как «Последовательность» или «Sequence» в базе MySql нет, поэтому очень часто приходиться самим реализовывать последовательность. Сегодня я расскажу об одном способе, который использовал я, когда мне понадобился данный объект в MySql.

Начнем как всегда с теории, для того чтобы Вы понимали, о чем идет речь.

Последовательность (sequence) – это объект базы данных, при обращении к которому будет возвращаться в отсортированном виде уникальное число с тем инкрементом, который Вы сами зададите.

Вообще, в других СУБД, кроме инкремента, можно задавать много параметров, например минимальное, максимальное значение и так далее.

Как было сказано выше, в MySql отсутствует данный объект, и многие выходят из этой ситуации путем создания в таблице столбца с auto_increment, в этом случае добавление новой записи в таблицу будет всегда с уникальным значением в данном столбце. Но это не всегда спасает, например, мне как-то раз понадобилось возвращать в отсортированном виде уникальное значение, а записи велись в разных таблицах, поэтому auto_increment мне не подошел, в другой СУБД на помощь мне пришла бы «Последовательность», но так как реализация была сделана в MySql, пришлось придумать свою последовательность.

На самом деле все примитивно и просто, сначала расскажу сам алгоритм. Создается таблица с двумя полями, одно из них auto_increment, другое, статус значения (я его так назвал:)).

Затем пишется функция, которая будет добавлять новое строку в эту таблицу и удалять предыдущую, для того чтобы таблица не росла, а функция работала максимально быстро. Что именно возвращать, а что удалять разруливается как раз полем «Статус».

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

Создаем последовательность в MySQL

Теперь перейдем к практике, и для начала создадим саму таблицу, я ее назвал sequence (база данных у меня с названием test):

  • >Теперь напишем функцию, при обращении к которой нам будет возвращаться уникальное число. Я ее назвал next_id (), и везде, где нужно получить уникальное значение обращайтесь к этой функции и все.

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

Если и комментарии Вам непонятны, то прочитайте сначала про основы sql, например, вот эти статьи:

Воспользоваться этой функцией (или просто проверить) можно с помощью запроса:

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

Именно вот таким способом я вышел из своей ситуации, надеюсь, Вам это тоже поможет. Удачи!

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

17.08.2013, 19:09

Как создать отчет (форму) в Access на основе хранимой процедуры с параметрами
Как создать отчет (форму) в Access на основе хранимой процедуры с параметрами. Без параметров -.

Как использовать временную таблицу в фильтре WHERE ?
SELECT fio, sum_price FROM (select fio, SUM(price) as sum_price FROM table GROUP BY fio) AS qq.

Как получить набор записей из хранимой процедуры?
Есть процедура (MS SQL) типа PROCEDURE vTest AS select * from Test возвращающая набор.

ASP, ADO, Access — как создать временную таблицу в памяти?
День добрый! Сильно не бейте, если вопрос совсем банальный, но поиски по гуглу в течение дня.

Как создать временную таблицу и как с ней работать?
Итак приступим у меня билдер 6, бд ацесс 2003. Для оптимизации многопользовательской работы по сети.

Представления (VIEW) в MySQL

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Эта статья является обзором представлений, появившихся в MySQL версии 5.0. В ней рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базы данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

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

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

Преимущества использования представлений:

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

Ограничения представлений в MySQL

В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).

  • нельзя повесить триггер на представление,
  • нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
  • в определении представления нельзя использовать подзапрос в части FROM,
  • в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
  • в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
  • таблицы и представления, присутствующие в определении представления должны существовать.
  • только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW , имеющий следующий синтаксис:

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

  1. OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
  2. ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
  3. column_list — задает имена полей представления.
  4. WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:

  1. Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:
    CREATE VIEW v AS SELECT a. >FROM a,b;
    Для избежания такой ситуации нужно явно указывать имена полей представления
    CREATE VIEW v ( a_ >) AS SELECT a. >FROM a,b;
    Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:
    CREATE VIEW v AS SELECT a. >FROM a,b;
  2. В случае если в определении представления получаемые данные преобразуются с помощью каких-то функций, то именем поля будет данное выражение, что не очень удобно для дальнейших ссылок на это поле. Напимер:

Для просмотра содержимого представления мы используем оператор SELECT (полностью аналогично как в случае простой таблицы), с другой строны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция — запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорированна и результат неопределен. Рассмотрим подробнее эти случаи:

  1. Если в обоих операторах встречается условие WHERE , то оба этих условия будут выполнены как если бы они были объединены оператором AND .
  2. Если в определении представления есть конструкция ORDER BY , то она будет работать только в случае отсутствия во внешнем операторе SELECT , обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
  3. При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY , результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE .

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

В случае алгоритма TEMPTABLE , MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ ALGORITHM = < UNDEFINED | MERGE | TEMPTABLE >]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

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

Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

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

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT : заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND . Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT , GROUP BY , то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

Найдем максимальное количество тем в форуме:

В этом случае MySQL использует алгоритм TEMPTABLE , т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX ( ) используя данные временной таблицы:

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

  1. В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
  2. Если вы явно указываете MERGE , а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND .

Обновляемость представлений

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

  1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
  2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обратите внимание: встречающиеся в русско-язычной литературе требования, чтобы обновляемое представление было основано на единственной таблице и присутствие в числе полей представления первичного ключа физичекой таблицы не являются необходимыми. Скорее всего требование единственной таблицы является ошибкой перевода. Дело в том, что через представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN , а не OUTER JOIN или UNION .

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

Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных ( INSERT ) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных ( DELETE ) для таких представлений не поддерживается.

При использовании в определении представления конструкции WITH [ CASCADED | LOCAL ] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

  • Изменение данных ( UPDATE ) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
  • Добавление данных ( INSERT ) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

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

Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

  • Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
  • Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED .

Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_ >AND num_views> 2000 WITH CHECK OPTION ;
Query OK, 0 rows affected ( 0.03 sec )

punbb >UPDATE v SET num_views= 2003 WHERE subject= ‘test’ ;
Query OK, 0 rows affected ( 0.03 sec )
Rows matched: 1 Changed: 0 Warnings : 0

punbb >SELECT subject, num_views FROM topics WHERE subject= ‘test’ ;
+ ———+————+
| subject | num_views |
+ ———+————+
| test | 2003 |
+ ———+————+
1 rows in set ( 0.01 sec )

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views> 2000 в определении представления и обновления не произойдет.

Не все обновляемые представления позволяют добавление данных:

Причина в том, что значением по умолчанию колонки forum_ > WHERE forum_ > в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:

С другой строны:

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

Как происходит в MySQL создание таблицы и ее изменение в случае необходимости

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

Просмотр списка поддерживаемых типов таблиц

Engine – содержит типы таблиц.

support — поддержка определенного типа таблиц.

Comment – содержит краткое описание.

Transactions — поддержка транзакций.

Типы движков таблиц

FEDERATED – используется для объединения нескольких MySQL серверов в одну логическую базу.

MRG_MYISAM (MERGE) – позволяет логически объединить MyISAM таблицы c идентичной структурой в одну, таблицы должны иметь идентичную структуру.

MyISAM – один из самых распространенных типов таблиц MySQL, используемый в Web-приложениях, поддерживается всеми дистрибутива MySQL, является не транзакционным типом таблиц и не поддерживает внешние ключи.

BLACKHOLE (черная дыра) – таблица не содержит никаких данных, все данные записанные в эту таблицу исчезают (/dev/null). Результатом запроса выборки всегда будут пустые строки.

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

MEMEORY (HEAP) – данные хранятся в оперативной памяти, что позволяет реализовать быстрый доступ к данным.

ARCHIVE – сжатые таблицы без индексов, подходит для хранения больших объёмов данных, к которым не нужен постоянный доступ, характерная особенность — медленное выполнение запросов и отсутствие поддержки запросов типа DELETE, REPLACE, UPDATE.

InnoDB – база данных поддерживает транзакций, возможность отмены транзакции, блокировку доступа на уровне строк, использование внешних ключей (FOREIGN KEY). Начиная c версии 5.5.5, этот тип используется по умолчанию.

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

Из примера видно, что многие из параметров необязательны, т.е. для создания таблицы достаточно указать имя таблицы (Test1) и имена и типы столбцов (Name1 VARCHAR(10)) .

Данный пример демонстрирует, что при создание таблицы командой ENGINE можно выбрать тип таблицы (InnoDB) и задать используемую кодировку в таблице (CHARACTER SET utf8). С помощью PRIMARY KEY (id) мы указали что столбец id будет первичным ключом, а тип NOT NULL AUTO_INCREMENT позволяет автоматически нумеровать строки таблицы столбца id.

Удаление таблиц

Переименование таблиц

Просмотр списка таблиц

Выводит список всех таблиц из выбранной базы данных с помощью команды USE.

Выводит список всех таблиц из указанной базы данных.

Выводит список всех таблиц из выбранной базы данных с помощью команды USE, и содержащих в название перечисленные символы.

Выводит список таблиц содержащих в название символы est.

Просмотр детальной информации о таблице, ее структуры.

Просмотр кода с помощью которого создавалась таблица.

Копирование таблиц.

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

Копирование таблиц из одной базы в другую.

имя_db – имя базы данных.

Добавление столбца к таблице

Команда FIRST добавляет столбец в начало, делает первым новый столбец. Команда AFTER добавляет столбец вслед за указанным. Если эти команды не используются, то столбец добавляется в конец таблицы.

Создаем таблицы MySQL c помощью phpMyAdmin, SQL-команд и в PHP

Здравствуйте уважаемый посетитель!

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

В данной статье будут рассмотрены три возможных способа создания таблиц MySQL, а именно: с использованием интерфейса phpMyAdmin, c помощью SQL-запросов, а также используя модуль MySQLi PHP.

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

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

При этом следует отметить, что наиболее часто из перечисленных способов используется первые два — с использованием интерфейса phpMyAdmin и c помощью SQL-запросов. Причем первый из них, на мой взгляд, более удобен для небольших таблиц. А использование SQL-запросов, наверное, можно оставить для создания более сложных, с большим количеством полей.

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

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

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

  • Определение основных параметров создаваемой таблицы
  • Создание таблицы с помощью интерфейса phpMyAdmin
  • Создание таблицы с помощью SQL-запросов
  • Создание таблицы MySQL в PHP
  • Исходные файлы сайта

Определение основных параметров создаваемой таблицы

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

  • количество столбцов (полей);
  • тип информации по каждому полю;
  • максимальное количество символов в каждом поле.

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

Дело в том, что в планах в ближайшем рассмотрении уделить внимание такому немаловажному вопросу, как ЧПУ (человеко-понятный урл).

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

Так вот, для того, чтобы заменять в url-страницах непонятные для человека get-параметры на понятные аналоги названия страниц, используя «транслит» (сокращенно от «транслитерация» — метод написания нелатинского текста латинскими символами), необходимо где-то иметь соответствие этих двух разных обозначений адресов. И при необходимости обращаться к этой информации.

Вот именно для этих целей мы и создадим первую таблицу нашей базы данных.

Следует отметить, рассмотрение вопроса об ЧПУ не является темой данной статьи. Здесь мы коснулись этого вопроса поверхностно, только для того, чтобы увязать наши действия по созданию таблицы БД с последующей работой над сайтом. Поэтому, более подробно о ЧПУ будем говорить позднее, в соответствующих статьях.

На данный момент наш сайт имеет пять страниц. На рис.1 показана таблица соответствия заголовков страниц к их url-адресам (именам файлов).


пп
Заголовок страницы Адрес страницы (имя файла)
1 Главная index.php
2 Статистика угонов article.php
3 Способы маркировки action.php
4 Получить скидку righting.php
5 Контакты contacts.php

А, теперь составим с помощью транслита человеко-понятные урлы (ЧПУ), соответствующие заголовкам страниц и поместим их в таблицу соответствия ЧПУ.

Кроме того, дополним заголовки наименованиями будущих полей таблицы БД с использованием латинского алфавита: «id», «title», «file» и «url», соответственно.

id
(№
пп)
title (Заголовок страницы) file (имя файла) url (ЧПУ)
1 Главная index.php (см. примечание *)
2 Статистика угонов article.php statistika-ugonov
3 Способы маркировки action.php sposobi-markirovki
4 Получить скидку righting.php poluchity-skidku
5 Контакты contacts.php kontakti

* — url главной страницы обычно имеет вид доменного имени без добавления каких-либо символов. В нашем случае — это «avtobezugona.ru».

Вот такую таблицу БД мы и будем сейчас создавать, в которой будут использоваться следующие параметры:

  • количество столбцов — 4;
  • тип информации:
    • поле «id» — целое положительное число (в данном поле значения будут являться уникальным идентификатором для каждой строки (записи));
    • поля «title», «file» и «url» — строки переменной длины;
  • максимальное количество символов:
    • поле «id» — 2 знака, что соответствует максимальному значению 99 (в данном случае количество знаков ориентировочно определено из расчета максимального числа параметров url1 адресов страниц сайта, на данный момент их всего 5). Но это не принципиально, тем более, что в дальнейшем, в случае нехватки в этом поле знаков, в любое время можно будет увеличить этот параметр. А вообще, на практике, обычно для поля «id» устанавливают значение по умолчанию, равное 10 символам;
    • поля «title», «file» и «url» — 100 знаков (из расчета, что значения этих полей не превысят данное значение). Ведь, нет смысла задавать чрезмерный размер этих полей, который заведомо не будет востребован.

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

Создание таблицы с помощью интерфейса phpMyAdmin

Перед тем, как начать создавать новую таблицу, необходимо войти в приложение phpMyAdmin, аналогично тому, как было показано в статье » Создаем базу данных MySQL «. Для варианта с локальным веб-сервером, это можно найти в разделе Создаем базу данных на локальном веб-сервере Denwer, а в случае работы с базой данных на хостинге — в разделе Создаем базу данных на хостинге.

Выбрав в главном меню phpMyAdmin нужную базу данных (в нашем случае, это «avtobezugona»), для создания таблицы введем ее наименование (назовем ее «url»), количество столбцов (ранее было определено в количестве 4) и нажмем «OK», как показано на рис.3.

После чего мы попадем на новую вкладку панели phpMyAdmin, в которой требуется ввести параметры по заданным полям. Ниже показан скриншот с параметрами полей, которые заполнены в соответствии с таблицей, изображенной на рис.2.

Здесь, что касается параметров полей, более подробно следует остановиться на поле «id», а именно:

  • параметр «Тип» установлен в значение «INT», означающее любое целое число;
  • для параметра длины задано максимальное значение 10 (в случае, если поле не заполнено, по умолчанию оно будет установлено в это же значение, равное 10-ти символам);
  • в параметре «Атрибуты» задано беззнаковое значение «UNSIGNED, при котором будут использоваться только положительные числа;
  • в параметре «Индекс» установлено «PRIMARY», означающее первичный ключ, при котором одинаковых значений в этом поле быть не может. Таким образом будет обеспечиваться уникальность индексов всех записей;
  • в параметре «AI» (autoincrement) отмечено использование автоматического приращения, при котором «id» будет увеличиваться на единицу при каждом создании новой записи;

Таким образом поле идентификатора «id» определено таким образом, что ее значения будут принимать только уникальные положительные целые числа, автоматически увеличиваясь на единицу при каждом создании новой записи.

С остальными полями, несколько проще. Достаточно для них лишь указать тип данных «VARCHAR», означающее строку переменной длины и выбрать нужную кодировку «ult8_general_ci».

По вопросу же длины полей «title», «file» и «url» стоит отметить, что начиная с версии MySQL 5.0.3 максимальная длина полей типа «VARCHAR» увеличена с 255 до 65535 символов, и будет не логично для заведомо небольшой длины записей назначать такой максимальный размер полей. Поэтому, их длина здесь назначена в соответствии с ранее определенным значением, равным 100 символов.

Далее, после определения всех параметров, для завершения создания таблицы осталось только установить ее кодировку в «ult8_general_ci» и сохранить введенные параметры с помощью кнопки «Сохранить.

В результате, наша первая таблица базы данных с именем «url» создана. О чем свидетельствуют соответствующие обозначения в главном меню phpMyAdmin и в перечне таблиц раздела «Структура» базы данных, как показано на следующем скриншоте.

А теперь, если кликом мышки выберем в меню phpMyAdmin эту таблицу, то сможем увидеть ее структуру, отражающую в полной мере те настройки, которые мы сделали при ее создании.

При этом, как видно на рис.6, с помощью действий «Изменить» или «Удалить» мы в любое время можем вносить изменения в параметры существующих полей, либо при необходимости удалять их. И что немаловажно, используя соответствующий набор инструментов, можем также в любое место таблицы добавлять новые поля. Не правда ли, очень удобно для дальнейшей работы с созданными таблицами MySQL.

Создание таблицы с помощью SQL-запросов

Теперь будем создавать таблицу в phpMyAdmin не в ручную, как это делали мы в предыдущем случае, а использовать для этого SQL-запросы. И воспользуемся имеющейся в SQL командой «CREATE TABLE», которая и предназначена для создания и описания структуры таблицы.

На рис.7 показан SQL-запрос, который соответствует созданию аналогичной таблицы, созданной в предыдущем случае. Только этим запросом создадим ее не под существующим именем «url», а с изменённым «url_sql», так как при наличие существующей таблицы, создания другой, с таким же именем невозможно.

CREATE TABLE url_sql

id int(10) unsigned auto_increment primary key,

title varchar(100) not null,

file varchar(100) not null,

url varchar(100) not null

Как видно, для того, чтобы создать таблицу достаточно сделать лишь один запрос к базе данных с использованием команды «CREATE TABLE».

При этом, все параметры, которые мы вводили вручную, здесь можно назначить в параметрах этой команды, а именно:

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

А, теперь выполним данный запрос.

Для этого, в phpMyAdmin откроем поле для ввода SQL-запросов, выбрав раздел SQL, и запишем в него соответствующую команду, как показано на скриншоте.

После нажатия на кнопку «OK» заданная таблица должна быть создана. В этом можно убедиться в появившимся обозначении в меню phpMyAdmin и указанием в разделе SQL созданных полей.

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

Как видим, результат этих двух вариантов одинаков. И теперь осталось проверить третий способ, с использованием модуля MySQLi PHP.

Создание таблицы MySQL в PHP

По существу, этот вариант, как и предыдущий, основывается на выполнении SQL-запроса «CREATE TABLE». Но, в отличие от него, выполнение этой команды будет происходить не в ручном режиме, а программно, с использованием функции выполнения запроса к базе данных query модуля MySQLi.

Таким образом, для создания таблицы в PHP, откроем файл «connect.php», полученный в предыдущей статье Подключаем базу данных MySQL с использованием процедурного и объектно-ориентированного стиля MySQLi, и добавим в него при использовании объектно-ориентированного стиля следующий код:

if ( $mysqli -> query ( «CREATE TABLE url_php (id int(10) unsigned auto_increment primary key, title varchar(100) not null, file varchar(100) not null, url varchar(100) not null)» )) <

echo «Таблица url_php c заданными параметрами успешно создана» ;

Для тех, кто использует процедурный стиль, функция query в этом скрипте примет несколько другой вид:

if ( mysqli_query ( $link, «CREATE TABLE url_php (id int(10) unsigned auto_increment primary key, title varchar(100) not null, file varchar(100) not null, url varchar(100) not null)» )) <

echo «Таблица url_php c заданными параметрами успешно создана» ;

Если внимательно посмотреть, то можно увидеть, что синтаксис и все параметры команды «CREATE TABLE» в строгом соответствии повторяют тот запрос, который мы делали в предыдущем варианте.

Отличие заключается лишь в том, сейчас эта команда сама является параметром функции query модуля MySQLi и сформирована она в данном скрипте в одну строку (написание параметров функции в одну строку необязательное условия, просто такое оформление кода PHP более компактно и удобно).

При этом, имя создаваемой таблицы здесь заменено на «url_php». Это обусловлено тем, что как и в случае с SQL-запросами, при имеющейся в базе данных существующей таблицы, создания другой, с таким же именем выполнено не будет.

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

Теперь, если откроем главную страницу сайта на локальном веб-сервере, в которой у нас подключен файл «connect.php», то в верхней части страницы должно появиться сообщение «Таблица url_php c заданными параметрами успешно создана», как показано на следующем скриншоте.

Это означает, что таблицы создана. В этом можно будет убедиться используя панель phpMyAdmin.

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

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

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

Исходные файлы сайта

Исходные файлы сайта с обновлениями, которые были сделаны в данной статье, можно скачать из прилагаемых дополнительных материалов:

  • Файлы каталога www
  • Таблицы базы данных MySQL

Следует отметить, что в файле «connect.php», созданные в этой статье фрагменты кода по созданию таблицы MySQL в объектно-ориентированном и процедурном стиле закомментированы. При их использовании следует раскомментировать соответствующую часть кода.

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

В данном случае в базе данных находятся все три таблицы MySQL, которые были созданы по ходу рассмотрения данного материала. Причем, две их них: «url_sql» и «url_php» созданы временно для демонстрации способов работы с БД MySQL. В последствии, по мере ненадобности они будут удалены.

Дополнительные материалы бесплатно предоставляются только зарегистрированным пользователям.

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

Для тех кто не зарегистрирован, можно это сделать на вкладке Регистрация.

Linux.yaroslavl.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В 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 section 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 .
  • Восстановление после сбоя для таких таблиц является более сложным процессом, так как запись может быть фрагментированной и состоять из нескольких частей, а ссылка (или фрагмент) могут отсутствовать.
  • Предполагаемая длина строки для динамических записей вычисляется следующим образом: На каждую ссылку добавляется по 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 section 4.4.4 Синтаксис CHECK TABLE .

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

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

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

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

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


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

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

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

  • Во время первого обновления таблицы в 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 section 4.7.4 myisampack , MySQL-генератор сжатых таблиц (только для чтения).

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

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

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

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

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

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

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

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

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

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

Обратите внимание на то, что столбец 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 section 7.1 Таблицы MyISAM ).

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

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

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

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

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

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

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

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

При использовании 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 -таблице, вычисляется следующим образом:

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

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

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

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

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

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

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

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

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

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

Однако чтобы добиться высокой скорости работы, лучше указать рекомендуемые параметры. See section 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 используется следующий синтаксис:

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

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

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

Простой пример файла `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 :

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

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

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

Где поместить файл `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 и домашней директории для группы журнальных файлов.

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

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

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

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

Обратите внимание на то, что некоторые параметры представлены при помощи формата числового параметра `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 Гб. В некоторых операционных системах файлы должны быть 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-разрядных компьютерах должен быть 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 начнет создавать ваши файлы данных и файлы журналов. При этом будет выводиться примерно такая информация:

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

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

Если 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 :

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

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

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

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

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

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

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

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

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

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

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

Обе таблицы должны быть 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'.

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

Однако не следует забывать предварительно создавать необходимые индексы. В InnoDB версий 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 возвращает определения внешних ключей таблицы, если вызвать

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

Список ограничений внешнего ключа таблицы 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 будет выводить примерно следующую информацию:

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

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

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

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

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

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

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

Назначение транзакционной модели 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':

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

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

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

В блокировке уровня строки 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 .

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

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

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

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

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

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

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

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

Когда 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 section 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 только после того, как B зафиксирует вставку, и A зафиксирует свою собственную транзакцию, чтобы момент времени передвинулся на позицию, находящуюся после фиксации, произведенной пользователем B.

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

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

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 . Если оно установлено, то каждая вставка требует сохранения журналов на диске. Поместите прямо в начале вашего файла с данными:

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

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

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

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

Эта рекомендация подходит для вставок в таблицы любого типа, а не только 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 через программу синтаксического анализа SQL. Факт создания таблицы не играет никакой роли для InnoDB Monitor. Если вы останавливаете сервер, когда монитор работает, и хотите запустить монитор заново, следует уничтожить таблицу прежде, чем снова вызвать CREATE TABLE для запуска монитора. Синтаксис может измениться в будущих версиях.

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

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

  • Если раздел 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 версий 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 байт.

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

Используя реальный диск, в некоторых версиях 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.
  • Если попытаться создать уникальный индекс на префиксе столбца, то будет выдана ошибка: Если на префиксе столбца создать неуникальный индекс, InnoDB создаст индекс по всему столбцу.
  • Для таблиц InnoDB не поддерживается команда INSERT DELAYED .
  • Операция MySQL LOCK TABLES не знает про блокировки InnoDB на уровне строк в уже выполненном операторе SQL: это означает, что можно установить блокировку на таблицу, даже если существуют транзакции других пользователей, которые установили блокировку этой же таблицы на уровне строк. Таким образом, может оказаться, что ваши операции над таблицей будут вынуждены ожидать, если такая блокировка будет установлена другими пользователями: возможна также и взаимоблокировка. Тем не менее, это не угрожает целостности транзакций, так как при установке блокировки на таблицы InnoDB всегда соблюдается целостность. Кроме того, блокировка таблицы не позволяет другим транзакциям установить на таблицу дополнительные блокировки на уровне строки (в режиме несовместимости блокировок).
  • Нельзя установить ключ для столбцов типа BLOB или TEXT .
  • Таблица не может содержать больше 1000 столбцов.
  • Команда DELETE FROM TABLE не пересоздает таблицу, она удаляет все строки по одной, что осуществляется не очень быстро. В будущих версиях MySQL можно будет использовать команду TRUNCATE , которая намного быстрее.
  • Принятый по умолчанию размер страницы в InnoDB составляет 16 Кб. Повторно скомпилировав код, можно установить значение от 8 Кб до 64 Кб. В версиях BLOB и TEXT могут достигать 4 Гб, общая длина строк также не должна превышать 4 Гб. Поля с размером меньше или равным 128 байтам в InnoDB не хранятся на отдельных страницах. После того как InnoDB изменит строку, сохранив длинные поля на отдельных страницах, оставшаяся длина строки должна быть меньше половины страницы базы данных. Максимальная длина ключа — 7000 байтов.
  • В некоторых операционных системах файлы данных не должны превышать 2 Гб. Общий размер файлов журналов должен быть меньше 4 Гб.
  • Максимальный размер табличной области составляет 4 миллиарда страниц базы данных. Это также максимальный размер таблицы. Минимальный размер табличной области составляет 10 Мб.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Процесс одновременного открытия многих таблиц 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

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

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

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

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

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

MySQL — изменение полей, структуры таблицы

Mysql alter add/drop index (добавление и удаление индексов):

Редактирование структуры полей в mysql:

Удалить поля в mysql:

Добавление полей в mysql:

Можно комбинировать добавление и удаления полей одним alter запросом:

Добавить primary key (PK) в mysql таблицу

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

Запрос удаления первично ключа:

Пример добавления PK:

insert+select синтаксис запроса

Видно, что опускается слово VALUES и в скобках пишется sql-запрос.

Узнать структуру таблицы в mysql

Запрос SHOW CREATE TABLE показывает запрос для создания таблицы, пример

Оптимизация JOIN с помощью временных таблиц

Когда тормозит база данных

Многие разработчики рано или поздно при работе с MySQL сталкиваются с проблемами производительности. Одна из частых причин — много запросов с блокировкой ресурсов в очереди с долгой отработкой запросов, либо из-за deadlocks.

Обычно проблем с простыми SELECT-запросами не возникает. Они выплоняются довольно быстро, а если не быстро — то оптимизируются с помощью добавления правильных индексов или переопределения логики. А вот запросы с использованием JOIN довольно часто начинают необъяснимо тормозить, даже если использовать исключительное присоединение таблиц по Primary Key . Давайте посмотрим подробнее как происходит выполнение JOIN-запроса в MySQL.

Как выполняется JOIN

Любой SELECT-запрос начинает выполняться именно с открытия учавствующих в запросе таблиц и их соединения по JOIN и WHERE условиям. При соединении учавствующих таблиц MySQL создает новую TEMPORARY таблицу, подходящую под все условия. После соединения к TEMPORARY таблице применяются остальные части запроса — GROUP BY , ORDER BY , HAVING , LIMIT , SELECT (выборка определеных столбцов).

Теперь графически на примере.

Имеется две таблицы — таблица image со столбцами id , src , type_id и таблица type со столбцами id , name , wiki_info . Столбцы id (в обеих таблицах), type_ >src и src имеют тип varchar(64) и занимают в среднем по 40 байт. А столбец wiki_info имеет тип varchar(1024) и занимает в среднем 500 байт. В таблице image 1 миллион строк, а в таблице type 3 строки. Нетрудно посчитать что image занимает на диске порядка 50 Мб пространства, а type около полутора килобайт.

Теперь предположим что мы хотим выгрузить список источников ( image.src ) изображений и рядом с каждым — его название типа ( type.name ). Любой разработчик сделает это с легкостью одним запросом:

Как обработает такой запрос MySQL? Как и было описано выше — первым выплоняется JOIN по условиям и создает новую таблицу склеивая две другие по условию t. >. Получится такая таблица:

Таким образом после соединения у MySQL будет таблица длиной в 1 миллион строк и шириной в 6 столбцов. Нетрудно посчитать что в среднем каждая строчка имеет длину в 550 байт. А значит суммарный объем таблицы составит

670 Мб. После этого из таблицы будут выбраны столбцы i.src и t.name и информация объемом в

100 Мб отправлена клиенту. В том случае если значение tmp_table_size будет больше чем размер временной таблицы — запрос отработает достаточно быстро, но если же значение tmp_table_size будет недостаточным — MySQL эту же таблицу будет создавать на диске. А запись такого количества информации на диск — весьма медленная операция, получим долгий запрос, к тому же бесполезно нагружающий дисковую систему. Согласитесь, было бы правильно сначала выделить нужные столбцы, а потом уже соединить таблицы.

Оптимизация JOIN путем уменьшения потребления памяти

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

Созданная во время выполнения SELECT вспомогательная таблица будет иметь теперь всего 5 столбцов, а вся таблица будет иметь размер около 110 Мб, из которых 100 Мб будет отправлено клиентскому приложению. При таком выполнении запроса мы сильно снизили вероятность выхода за рамки tmp_table_size, но если даже и вышли — работа с диском отнимет у MySQL в 6 раз меньше времени чем в случае неоптимального варианта.

Проверка на реальных данных

Для проверки возьмем аналог приведенную выше таблицу, которая используется в реальном проекте. Количество строк — 48 млн. Вторая таблица будет аналогичная той что в рассматриваемом выше примере. Добавим инструкцию LIMIT чтоб увеличить сложность довольно простого запроса и показать какой потенциальный выигрыш можно получить.

19,3 сек. При том что в это время у нас залочено 2 таблицы одновременно. Итого 38,6 условных «таблицо-секунд» блокировки.

Выигрыш очевиден, хоть и условия не выглядят как реальные. Но на реальных задачах, на более широких (множество столбцов) таблицах с множеством JOIN-ов можно достичь многократного роста скорости выполнения запросов и превратить те запросы, которые выполняются по несколько секунд в те, которые выполняются почти мгновенно. Кроме того, несомненным плюсом будет и отсутствие каскадных блокировок таблиц, т.к. они не будут участвовать одновременно в одном запросе, а будут блокироваться последовательно на маленькие промежутки времени.

Оптимизация JOIN с группировкой

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

На тестовом стенде из нескольких миллионов user и несколько десятков миллионов product запрос выполняется

50 сек. Оптимизируем с помощью временной таблицы, чтоб MySQL проводил группировку до JOIN с использованием более узкой таблицы:

Суммарный итог: 10 сек. Выигрыш по скорости в 5 раз.

Оптимизация JOIN с подзапросами

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

Типичный запрос который составит разработчик будет выглядеть вот так:

MySQL при выполнении такого запроса сперва создаст две временные таблицы из подзапросов, потом создаст третью временную таблицу из JOIN-ов. Потом вернет результат клиенту. Проблема в том что таблицы из подзапросов не имеют индексов, поэтому для присоединения каждой таблицы движку придется выполнить очень много сравнений. Например если в таблице user около 1000 записей из них 400 человек привлечены другими, а заказы имеют 500 человек, то MySQL сделает 1000*400*500 = 200млн сравнений прежде чем будет готова итоговая таблица. Так же на всех учавствующих в запросе таблицах будет висать read lock всё время выплонения запроса.

Между тем, можно сделать вот так:

В этом случае все JOIN будут проходить по уникальным ключам и достаточно быстро. Кроме того таблицы будут получать read lock на более короткие промежутки времени и только по одной.

Проверка на реальных данных

На тестовом стенде была сгенерирована таблица user со 100000 записей, внешний ключ user_id есть у

70000 записей и ссылается (с неравномерным распределением) на

30000 записей из user . Таблица product имееет 300000 записей и ее внешний ключ user_id ссылается (с неравномерным распределением) на

60000 записей из user .

Тесты на SSD дисках, ненагруженной MySQL версии 5.5 и идеальных только что созданных таблицах показали 10% рост производительности. На HDD дисках оптимизация более заметна — 9сек на варианте с временнными таблицами и 14 секунд без них. Немного если смотреть с точки зрения скорости, но отстуствие длогих lock-операций может дать приличный суммарный выигрыш в случае большого количества парралельных запросов. Еще одним неоспоримым приемуществом будет возможность в рамках одной MySQL-сессии использовать эти данные несколько раз.

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

Топ-пост этого месяца:  OpenCart – модуль дополнительного описания категорий
Добавить комментарий