Задачи на продвинутые sql запросы

Таблицы MyISAM

Использовались по-умолчанию в MySQL вплоть до выхода в свет версии 5.5.

Каждая MyISAM таблица хранится на диске в трёх файлах (в поддиректории каталога mysql/data), имена этих файлов совпадают с названием таблицы, а расширение может принимать одно из следующих значений: .frm — структура таблицы, этот файл содержит информацию об именах и типах столбцов и индексов; .MYD — в этом файле содержатся данные таблицы; .MYI — в этом файле содержатся индексы таблицы.

Таблицы MyISAM обладают рядом особенностей:
Данные хранятся в кроссплатформенном формате, что позволяет переносить базы с сервера непосредственным копированием файлов, минуя промежуточные формы.
Максимальное количество индексов — 64 (с версии 4.1.2). Каждый индекс может быть максимум из 16 столбцов.
С версии MySQL 4.1, для каждого текстового столбца может быть задана своя кодировка.
Допускается индексирование текстовых столбцов, в том числе и переменной длины.
Поддерживается полнотекстовый поиск.
Таблицы имеют специальный флаг, указывающий на правильность закрытия таблицы. Если сервер был остановлен аварийно, то при загрузке незакрытые флаги будут сигнализировать о возможных поврежденных таблицах и сервер попытается автоматически их проверить и восстановить.
Таблицы MyISAM можно проверять/восстанавливать при помощи утилиты myisamchk.
Таблицы MyISAM можно сжимать при помощи команды myisampack.

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

1.Cтатические таблицы (с фиксированной длиной)

Это формат, принятый по умолчанию. Он используется, когда таблица не содержит столбцов VARCHAR, BLOB или TEXT.
Данный формат — самый простой и безопасный, а также наиболее быстрый при работе с дисками. Скорость достигается за счет простоты поиска информации на диске: в таблицах статического формата с индексом для этого достаточно всего лишь умножить номер строки на ее длину.
Кроме того, при сканировании таблицы очень просто считывать постоянное количество записей при каждом чтении с диска.
Если произойдет сбой во время записи в файл MyISAM фиксированного размера, myisamchk в любом случае сможет легко определить, где начинается и заканчивается любая строка. Поэтому обычно удается восстановить все записи, кроме тех, которые были частично перезаписаны.

2.Динамические таблицы

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

3.Сжатые таблицы

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

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

Таблицы MEMORY

Следующий тип таблиц MEMORY (HEAP) хранится в оперативной памяти, из-за чего все запросы к таким таблицам выполняются очень быстро. Недостаток у таких таблиц один — полная потеря данных в случае сбоя работы сервера. В связи с этим в таких таблицах хранят в основном временные данные, которые можно легко восстановить заново.
При создании таблицы типа MEMORY, создаётся один файл с расширением frm, в котором определяется структура таблицы. При остановке или перезагрузке сервера, данные о структуре таблицы остаются, но вся информация содержащаяся в этой таблице теряется, поскольку хранится только в оперативной памяти. При каждой перезагрузке сервера, пересоздавать таблицу не нужно, её структура остаётся.
Таблицы типа MEMORY имеют ряд ограничений:

Индексы используются только в операциях сравнения с операторами «=» и «⇔», с другими операторами, такими как «>» или «<», индексирование столбцов не имеет смысла.
Как и с MERGE таблицами возможно использование только неуникальных индексов.
Не допустимы столбцы типов TEXT и BLOB.
До версии MySQL 4.1 в таблицах данного типа не поддерживаляс AUTO_INCREMENT.

Таблицы BDB (BerkeleyDB)

Таблицы BDB обслуживаются транзакционным обработчиком BerkeleyDB, который разработан компанией Sleepycat. При создании таблиц этого типа формируются два файла:
.frm — хранится структура таблицы.
.db — хранятся данные и индексы.

Особенности таблиц BDB:

Для таблиц ведётся журнал, что позволяет повысить устойчивость базы и увеличить вероятность успешного восстановления в случае сбоя.
Таблицы типа BDB хранятся в виде бинарных деревьев. Этот метод хранения замедляет сканирование таблицы (допустим для выборки всех строк таблицы) и увеличивает занимаемое таблицей место на диске. Однако поиск отдельных значений в таблице становится быстрее. Все остальные таблицы хранят в виде бинарных деревьев свои индексы.
Все таблицы BDB должны иметь первичный ключ, при отсутствии создаётся скрытый первичный ключ с атрибутом AUTO_INCREMENT.
Для данного типа таблиц поддерживаются транзакции на уровне страниц.
Подсчёт количества строк в таблице при помощи функции COUNT() происходит медленнее, из-за того что подсчёт строк для таблиц BDB (в отличие от MyISAM) на стороне сервера не поддерживается и полный пересчёт происходит при каждом обращении.
Ключи не являются упакованными, как в таблицах MyISAM и занимают больше места.
В случае, когда таблица BDB занимает всё свободное место на диске, происходит откат транзакции и вывод сообщения об ошибке. В отличие от BDB, таблицы MyISAM просто будут ждать появления свободного места, что приведёт к зависанию сервера.
Файлы таблиц BDB нельзя переносить между системами простым копированием, поскольку при их создании путь к файлу таблицы сохраняется. Для переноса базы необходимо использовать утилиту mysqldump.