Виды таблиц и способ их хранения

Heap

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

Резидентные таблицы не располагают многими возможностями обычных таблиц. Они не могут иметь столбцы типа BLOB или TEXT. Нельзя использовать флаг AUTO_INCREMENT. Можно создавать индексы, но нельзя индексировать столбцы, допускающие значения NULL. Индексы используются только в операциях = и <=>.

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

Для этого предназначена опция max_rows инструкции CREATE TABLE. Серверная переменная max_heap_table_size задает максимальный объем памяти, занимаемой всеми резидентными таблицами.

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

InnoDB

СУБД InnoDB была разработана Хейкки Туури (Heikki Tuuri) из компании Innobase — финского производителя программного обеспечения, специализирующегося на технологии реляционных баз данных. InnoDB представляет собой результат исследований, проводимых Хейкки в университете Хельсинки. Поддержка InnoDB появилась в MySQL версии 3.23. Сама СУБД доступна на условиях открытой лицензии.

На Web-узле InnoDB можно найти массу информации о деталях работы ядра этой СУБД. Ядро не существует само по себе, а является дополнением к MySQL. С Web-узла MySQL можно загрузить скомпилированную версию программы, в которую встроена поддержка InnoDB. Если впоследствии потребуется отключить эту поддержку, достаточно будет запустить демон mysql с опцией –skip-innodb. На этапе компиляции MySQL поддержка InnoDB включается с помощью опции –with-innodb. Исходные коды InnoDB входят в исходный дистрибутив MySQL.

В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB хранятся в больших совместно используемых файлах. Можно создать произвольное число файлов данных, но их нельзя будет удалить. Размер файлов определяется в конфигурационном файле. Если нужно уменьшить объем дискового пространства, занимаемого таблицами InnoDB, создайте резервные копии таблиц, после чего удалите все файлы InnoDB и позвольте программе MySQL восстановить их в соответствии с новыми установками конфигурационного файла. Журнальные файлы InnoDB можно безопасно удалить после остановки сервера. При повторном запуске сервера программа MySQL создаст журнальные файлы заново.

В листинге 6.1 приведен пример опций, которые необходимо добавить в конфигурационный файл в группу [mysql] чтобы активизировать таблицы InnoDB. Размер файлов здесь задан относительно небольшим, что вполне подходит для целей эксперимента. На практике используются файлы гораздо большего размера.

innodb_data_home_dir = /usr/local/var/innodb/
innodb_data_file_path = ibdata1/ibdata1:100M; ibdata2/ibdata2:100M
set-variable = innodb_mirrored_log_groups = 1
innodb_log_group_home_dir = /disk2/innodb/log
set-variable = innodb_log_files_in_group = 3
set-variable = innodb_log_file_size = 16M
set-variable = innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_log_arch_dir = /disk2/innodb/log
innodb_log_archive = 0
set_variable = innodb_buffer_pool_size = 25M
set_variable = innodb_additional_mem_pool_size = 5M
set_variable = innodb_file_io_threads = 4
set_variable = innodb_lock_wait_timeout = 50

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

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

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

На момент создания этого курса существовало несколько ограничений таблиц InnoDB. Самое существенное из них заключалось в способе отслеживания таблиц. В InnoDB ведётся каталог таблиц, который не поддерживается инструкцией DROP TABLE, поэтому каждую таблицу приходится удалять отдельно. Не разрешается индексировать префикс столбца, а также индексировать столбцы типа BLOB и TEXT. Максимальное количество столбцов в таблице — 1000. Флаг DELAYED в инструкции INSERT не поддерживается.

ISAM

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

Merge

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

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

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

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