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

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

MyISAM

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

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

MySQL хранит счетчик подключений к таблице MylSAM. Когда таблица закрывается, счетчик сбрасывается в нуль. Если сервер неожиданно завершает работу, счетчик остается положительным числом. В таком случае в процессе перезапуска сервер обнаружит проблему. Это не означает, что таблица непременно повреждена, но подобная возможность существует. Следует немедленно выполнить инструкцию CHECK TABLE или вызвать утилиту myisamchk. Можно также запустить демон mysql с опцией –myisam-recover, чтобы заставить его восстанавливать все таблицы MyISAM с ненулевым значением счетчика.

Для таблиц MyISAM разрешены одновременные операции вставки и выборки, если только в таблице нет пустых участков. Такие участки создаются инструкциями DELETE и могут быть заполнены последующими инструкциями INSERT. MySQL блокирует таблицу MyISAM, пока инструкция INSERT заполняет пустой участок.

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

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

По умолчанию в каждой таблице может быть не более тридцати двух индексов, но это значение можно повысить до шестидесяти четырех. Индексы создаются в виде двоичных деревьев. Разрешается индексировать столбцы типа BLOB и TEXT, а также столбцы, допускающие значения NULL.

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

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

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

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

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

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

Столбцы

В табл. 6.1 указаны размерности стандартных типов данных MySQL. Значения некоторых типов всегда занимают фиксированный объем памяти. Например, размерность столбцов типа INTEGER всегда составляет 4 байта. Столбцы типа CHAR могут иметь размерность от 0 до 255, но в момент создания таблицы под них отводится фиксированный объем памяти. Существуют также столбцы переменной размерности. Например, столбцы типа VARCHAR и BLOB интерпретируются в соответствии с их содержимым.

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

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

Блокировки таблиц

В MySQL разрешается явно блокировать таблицы с помощью инструкции LOCK TABLES. Тем не менее, не рекомендуется делать это для таблиц тех типов, которые поддерживают транзакции. Блокировки и транзакции — это два разных способа решения проблемы одновременного доступа к таблице, поэтому нужно сделать выбор в пользу одного из них.

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

Можно заблокировать таблицу таким образом, чтобы разрешить другим потокам обращаться к ней для чтения. Это называется блокировкой чтения. Блокировка записи гарантирует текущему потоку монопольный доступ к таблице. Запросы на чтение откладываются до тех пор, пока не будут сняты все блокировки записи. Эту установку можно изменить с помощью флагов инструкций либо путем задания специальных серверных переменных. Для SQL-инструкций создаются две очереди. Чтобы программа MySQL начала извлекать инструкции из очереди на чтение, очередь на запись должна быть пуста. При наличии флага LOW_PRIORITY инструкции DELETE, INSERT и UPDATE помещаются в очередь на чтение, т.е. они получают такой же приоритет, что и инструкции SELECT. Флаг HIGH_PRIORITY переводит инструкцию SELECT в очередь на запись.

Индексы

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

Определения индексов хранятся в frm-файле а сами индексируемые значения — в файле с расширением .MYI. Если индексный файл отсутствует на момент запуска сервера, он будет автоматически воссоздан. Таким образом, при создании резервных копий можно не заботиться об индексах в целях экономии места. Позднее, в процессе восстановления базы данных, программа MySQL создаст индексы заново на основании схемы таблицы.

Индексы способны повысить производительность инструкций, связанных с поиском записей. Они ускоряют процесс сравнения столбцов при выполнении операций объединения. Кроме того, они помогают находить минимальное и максимальное значения столбца и ускоряют выполнение инструкций SELECT с предложением ORDER BY.

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

CREATE TABLE car (
Make CHAR(32) NOT NULL,
Model CHAR(32) NOT NULL,
Introduced YEAR,
PRIMARY KEY (MAKE,Model)
);

У таблицы car имеется составной первичный ключ. В запросе, который показан в листинге 6.3, индекс будет использован, так как столбец Make является самым левым компонентом индекса.

SELECT * FROM car WHERE Make='Ford';

А вот в следующем запросе (листинг 6.4) этого не произойдет, поскольку правило очередности столбцов не соблюдается.

SELECT * FROM car WHERE Model='Pinto';

В листинге 6.5 индекс также не используется, из-за того, что самый левый компонент индекса нельзя применить к каждой записи. Если бы в предложении WHERE стоял оператор AND, а не OR, все было бы наоборот.

SELECT * FROM car WHERE Make='Ford' OR Model='Impala';

Следующий запрос (листинг 6.6) является правильным с точки зрения использования индекса. В данном случае просмотр значений столбца осуществляется слева направо.

SELECT * FROM car WHERE Make LIKE 'F%';

В листинге 6.7 индекс не используется, потому что просмотр значений столбца осуществляется справа налево (метасимвол % стоит вначале).

SELECT * FROM car WHERE Make LIKE '%d';

Дескрипторы файлов

Сервер MySQL представляет собой один процесс со множеством потоков. Для каждого сеанса подключения к серверу создается свой поток. Каждому потоку требуется один или несколько дескрипторов файлов, чтобы он мог осуществлять чтение и запись таблиц. Операционная система ограничивает количество файловых дескрипторов, доступных процессу. Это число может быть самым разным. Например, в AIX оно равно 2000 по умолчанию, а в Solaris — всего лишь 64. В Linux лимит по умолчанию составляет 1024 дескриптора. В Windows NT и 2000 видимый предел отсутствует.

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

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

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

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

Системная память

В MySQL специальные буферы и кэши применяются для самых разных целей. Их размеры можно задавать в конфигурационном файле или в командной строке запуска сервера. У каждого потока есть свой стек, буфер приема входных данных от клиента и буфер результатов запроса. Размер стека задается серверной переменной thread_stack, а размеры обоих буферов — переменной net_buffer_length. Последняя определяет начальные размеры буферов, так как они могут увеличиваться в случае необходимости, например, при обработке столбцов типа BLOB или TEXT.

Все потоки совместно используют индексный буфер. Его размер определяется переменной key_buffer_size. В операциях объединения, проходящих без участия индексных столбцов, используется отдельный буфер (переменная join_buffer_size ), как и в операциях сканирования таблиц (переменная record_buffer ).

Если для выполнения операции объединения требуется временная таблица, она создается как резидентная (тип Heap ). Максимальный размер таких таблиц определяется переменной tmp_table_size. После превышения этого предела таблица преобразуется в формат MyISAM. В любом случае временные таблицы удаляются по окончании операции.