Типы и структура таблиц MySQL

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

В этой лекции мы разберем основные типы таблиц, которые поддерживаются в MySQL 5.6.

Фактически доступные способы хранения зависят от конфигурации и версии сервера СУБД.

Вывести список доступных типов таблиц можно выполнив команду

SHOW STORAGE ENGINES;

или просто

SHOW ENGINES;

Тип таблицы задается при выполнении запроса CREATE TABLE после всех остальных частей указанием ENGINE=.... Например,

CREATE TABLE tbl (i INT) ENGINE=MEMORY;

MyISAM

Этот тип таблиц появился в MySQL еще до первых публичных выпусков и долгое время считался главной “фишкой” данной СУБД.

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

frm

содержит описание схемы таблицы – названия столбцов, их типы и др.
myd

содержит данные таблицы
myi

содержит индексы

MyISAM обладает рядом особенностей, некоторые из которых могут быть нежелательны:

Данные хранятся в платформонезависимом формате, что позволяет переносить БД простым копированием файлов
Максимальное число индексов в таблице ограничено 64-мя. Каждый индекс может содержать не более 16 столбцов
Для каждого текстового столбца может быть назначена своя кодировка
Допускается индексирование текстовых столбцов, в том числе переменной длины (это не всегда возможно в других форматах)
Поддерживается полнотекстовый поиск
При сбоях сервера, неправильно закрытые таблицы автоматически восстанавливаются

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

Данный тип таблиц так же не поддерживает обеспечение ссылочной целостности по внешним ключам.

MEMORY

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

Как следствие, этот тип таблицы хорошо подходит для хранения не являющихся критичными (легко восстановимых) данных, доступ к которым осуществляется часто.

Структура таблицы при перезапуске сервера сохраняется, и хранится в файле *.frm.

Существуют некоторые ограничения:

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

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

Данный тип таблиц так же не поддерживает обеспечение ссылочной целостности по внешним ключам.

BDB

Таблицы типа Berkley Database разработаны компанией Sleepyсat в 1996 году. Это был первый и долгое время единственный тип таблиц, поддерживающий транзакции. Начиная с версии 5.1, более не поддерживается в MySQL. Вместо него рекомендуется использовать InnoDB.

InnoDB

Данный тип таблиц разработан компанией Innobase Oy, которую в последствии поглотила компания Oracle. Этот тип таблиц обладает высокой устойчивостью даже на больших (до 1 ТБайта) таблицах и при достаточно больших нагрузках (до 800 вставок/обновлений в секунду).

Начиная с версии 5.5 является установленным по умолчанию (и “основным”) типом таблиц.

Этот тип таблиц, в отличие от прочих, не требует создания отдельных файлов для хранения данных. Вместо этого, все таблицы (даже принадлежащие разным схемам БД) хранятся в оптимизированном файловом хранилище вместе.

Обеспечивает поддержку транзакций.

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

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

Начиная с версии 5.6.4 поддерживает полнотекстовые индексы.

CSV

Этот тип таблиц позволяет хранить данные в простых текстовых файлах с разделителем “запятая”. Любая программа, поддерживающая этот формат (например, Excel) способна работать с этими таблицами минуя сервер СУБД.

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

BLACKHOLE

Этот тип таблиц не сохраняет данные. Любые данные, которые в него записываются, “бесследно исчезают”, как в черной дыре.

Понятно, что при такой постановке задачи, ни транзакции, ни индексы, ни внешние ключи не поддерживаются. К тому же, запросы UPDATE и DELETE не имеют смысла.

Этот тип таблицы может быть удобен для отладки и оптимизации, а так же для проверки синтаксиса дампов данных (данных БД, сохраненных в виде SQL-запросов на вставку).

Типы индексов

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

Мы уже поверхностно познакомились с обычным индексом INDEX и уникальным индексом UNIQUE. Кроме того, существуют полнотекстные индексы FULLTEXT и специальный тип уникального индекса PRIMARY для первичного ключа.

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

INDEX

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

UNIQUE

Уникальный индекс. Подразумевает ограничение уникальности на значения в столбцах (NULL ≠ NULL, поэтому он может встречаться несколько раз). Кроме того, ускоряет работу запросов.

PRIMARY

Работает похоже наUNIQUE, но индекс этого типа всегда называется PRIMARY, запрещает значения NULL и может быть только один на таблицу. Соответствует первичному ключу.

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

FULLTEXT

Полнотекстовые индексы разительно отличаются от всех вышеприведенных. Они используются исключительно для полнотекстового поиска по текстовым полям.

Полнотекстовый поиск осуществляется при помощи оператора полнотекстового сравнения MATCH (...) AGAINST (...) в условии поиска, например:

SELECT * FROM posts WHERE MATCH (title, text) AGAINST ('test');

Полнотекстовый индекс составляется по отдельным словам, и поиск, соответственно, осуществляется так же.

Общий синтаксис MATCH (...) AGAINST (...) имеет вид:

MATCH (col1,col2,...) AGAINST (expr
  [
     IN NATURAL LANGUAGE MODE
   | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
   | IN BOOLEAN MODE
   | WITH QUERY EXPANSION
  ])

Имеются, соответственно, три стратегии поиска: NATURAL LANGUAGE, BOOLEAN и QUERY EXPANSION.

NATURAL LANGUAGE интерпретирует выражение поиска expr как некую “человеко-читаемую строку запроса”. По сути, СУБД ищет все записи, содержащие хотя бы одно слово из строки expr. Например, стока поиска “MySQL это система управления базой данных” вернет записи, в которых содержатся хотя бы одно из слов: “MySQL”, “система”, “управления”, “базой”, “данных”, “это”.

Слово “это” может быть проигнорировано, поскольку слишком коротко. Вообще, по умолчанию, слова короче 3 символов в таблицах InnoDB и 4 символов в таблицах MyISAM игнорируются. Так же, таблицы MyISAM игнорируют слова, встречающиеся в более чем половине всей коллекции данных (как слишком часто встречающиеся чтобы иметь смысл).

BOOLEAN позволяет указать слова, которые должны быть в выдаче, и которых быть в выдаче не должно, добавляя префикс + или - соответственно. Например, “+MySQL -Microsoft база данных” вернет записи, содержащие слово “MySQL”, не содержащие слова “Microsoft”, и возможно содержащие слова “база”, “данных”. Можно интерпретировать + как конъюнкцию, - как конъюнкцию с инверсией и отсутствие их как дизъюнкцию.

QUERY EXPANSION использует стратегию раскрытия запроса первого уровня. При поиске, например, строки “СУБД”, сначала найдутся все вхождения, содержащие это слово, затем несколько наиболее подходящих документов будут добавлены к строке поиска и поиск будет осуществлен по этой строке. Таким образом, найдутся не только документы, содержащие “СУБД”, но и документы, содержащие связанные понятия. Увы, найдется так же целая куча несвязанных документов.

Алгоритмы построения индексов

В основном, MySQL поддерживает построение индексов по алгоритму сбалансированного двоичного дерева, B-Tree. Таблицы MEMORY кроме того поддерживают построение индексов по hash-таблицам.

B-Tree-индексы могут использоваться для поиска с различными опреаторами сравнения, включая =, >, >=, <, <=, BETWEEN. Так же работает для оператора LIKE с постоянной строкой, не начинающейся на %.

Hash-индексы могут использоваться только с операторами = и <=>, но эта операция гораздо быстрее, чем в случае с B-Tree-индексами.

B-Tree индексы могут использоваться для ускорения работы ORDER BY, в то время как hash-индексы – нет.

Hash-индексы крайне удобно использовать для организации временных Key-Value хранилищ, например, для хранения промежуточных результатов. По сути, это позволяет использовать MySQL в качестве нереляционной СУБД типа Redis.

Использование индексов для ускорения выборки

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

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

Так же напоминаю, что префикс любого индекса – сам по себе индекс. То есть, если есть индекс по колонкам (col1, col2, col3, ...) это соответствует существованию индексов по (col1), (col1, col2), (col1, col2, col3) и т.д.

Например:

    /* Используется индекс по (index_part_1, index_part_2) */
... WHERE index_part_1=1 AND index_part_2=2 AND other_column=3

    /* Используется индекс по (index) */
... WHERE index=1 OR A=10 AND index=2

    /* Используется индекс по index_part_1 */
... WHERE index_part_1='hello' AND index_part_3=5

    /* Используется индекс по index_1,
    но не по index_2 или index_3 */
... WHERE index_1=1 AND index_2=2 OR index_1=3 AND index_3=3;

    /* Индекс по index_part_1 не используется,
    соответственно никакой не используется */
... WHERE index_part_2=1 AND index_part_3=2

    /* index присутствует только в одной конъюнкции,
    и поэтому не может быть использован */
... WHERE index=1 OR A=10

    /* нет индекса по index_part_2,
    поэтому никакой индекс не используется */
... WHERE index_part_1=1 OR index_part_2=10

Подсистемы хранения данных. Индексирование данных.