Engine myisam что это

Tech blog by @dizballanze

Для того что-бы посмотреть какие типы поддерживает ваша инсталляция MySQL необходимо выполнить следующий SQL запрос:

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

В версии MySQL 5.5 поддерживается 9 различных типов таблиц.

Сравнительная таблица основных типов таблиц

Тестирование производительность InnoDB и MyIASM

Наибольший интерес для web-разработчика составляют innodb и myisam. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:

Напишем небольшой скрипт который будет выполнять 3 теста: запись данных (insert), выборка по ключу, выборка по не ключевому полю.

Для того что-бы выполнить тест, нужно раскоментить один соответствующий блок кода. И собственно, то что у меня получилось в результате тестирования:

Как мы видим myisam работает значительно быстрее, особенно это заметно при вставке данных. Хотя innodb и дает ряд новых возможностей и преимуществ, такая медлительность не позволяет ему конкурировать с myisam, особенно в web-приложениях.

Источник

Подсистемы (движки) хранения в базе данных MySQL 8

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

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

InnoDB

В MySQL 8 подсистема хранения данных InnoDB используется по умолчанию и яв­ляется наиболее широко применяемой из всех других доступных подсистем хра­нения. Подсистема InnoDB была выпущена вместе с MySQL 5.1 как плагин в 2008 году, и она рассматривается как подсистема хранения по умолчанию, начиная с версии 5.5 и выше. Поддержка подсистемы хранения InnoDB была перенята кор­порацией Oracle в октябре 2005 года у финской компании Innobase Oy.

Таблицы InnoDB поддерживают ACID-совместимые фиксации транзакций, от­кат и возможности аварийного восстановления для защиты пользовательских данных. InnoDB также поддерживает блокировку на уровне строк, что помогает улучшить параллелизм и производительность. InnoDB хранит данные в кластери­зованных индексах, чтобы уменьшить операции ввода-вывода для всех запросов SQL на выборку данных на основе первичного ключа. InnoDB также поддержи­вает ограничения внешнего ключа, которые обеспечивают лучшую целостность данных в базе данных. Максимальный размер таблицы InnoDB может масшта­бироваться до 256 Тб, что должно быть вполне достаточным во многих случаях использования больших данных.

Важные замечания по InnoDB

MyISAM

Подсистема хранения данных MyISAM использовалась по умолчанию для MySQL вплоть до версии 5.5 1. В отличие от InnoDB, таблицы подсистемы хранения данных MylSAM не поддерживают ACID-совместитмость. Таблицы MylSAM поддерживают только блокировку уровня таблицы, поэтому таблицы MyISAM небезопасны для транзакций. Таблицы MyISAM оптимизированы для сжатия и скорости. MyISAM обычно используется, когда вам нужно иметь в основном операции чтения с ми­нимальными транзакционными данными. Максимальный размер таблицы My- ISAM может достигать 256 Тб, что помогает в таких случаях, как анализ данных.

Важные примечания относительно таблиц MyISAM

Из-за низких накладных расходов MyISAM использует более простую струк­туру, которая обеспечивает хорошую производительность; однако это не сильно помогает для получения хорошей производительности, когда есть потребность в лучшем параллелизме и случаях использования, которые не нуждаются в тяже­лых операциях чтения. Наиболее распространенной проблемой производитель­ности MyISAM является блокировка таблицы, которая может задерживать ваши параллельные запросы в очереди. Это происходит, когда она блокирует таблицу для любой другой операции до тех пор, пока более ранняя операция не будет вы­полнена.

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

Memory

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

Ниже приведены важные примечания об оперативных таблицах Memory.

Archive

Blackhole

Эта подсистема хранения данных принимает данные, но их не сохраняет. Вместо сохранения данных она отбрасывает (уничтожает) их после каждой вставки.

В следующем ниже примере показана работа таблицы BLACKHOLE :

Эта подсистема хранения полезна для репликации с большим количеством сер­веров. Подсистема хранения данных Blackhole работает в качестве фильтрующе­го сервера между ведущим и ведомым серверами, который не хранит никаких данных, но который применяет только правила replicate-do-* и replicate-ignore-* и пишет двоичные журналы. Эти двоичные журналы используются для выполне­ния репликации на ведомых серверах. Мы обсудим это подробно в главе 6 «Репли­кация для построения высокодоступных решений».

Merge

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

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

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

С помощью подсистемы MERGE трудно выполнять разделение, следовательно, таблицами MERGE оно не поддерживается, и мы не можем реализовать раздел на таблице MERGE или любой таблице MyISAM.

Federated

Давайте создадим таблицу FEDERATED.

В поле CONNECTION содержится следующая ниже информация для вашей справки:

NDB Cluster

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

Кластерная часть NDB Cluster настроена независимо от серверов MySQL. В NDB Cluster каждая часть кластера считается узлом.

Как выбрать движок (подсистему хранения) MySQL?

Следующая ниже схема поможет вам понять, какую подсистему хранения дан­ных вам нужно использовать для ваших потребностей:

Каждая подсистема хранения данных имеет свое преимущество и удобство ис­пользования:

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

Источник

MyISAM

MyISAM — одна из основных (наряду с InnoDB) систем хранения данных в СУБД MySQL. Она основывается на коде ISAM и обладает в сравнении с ним рядом полезных дополнений.

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

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

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

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

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

Недостатки

Данные недостатки проявляются в заметной степени при высокой нагрузке: порядка 400 клиентов, исполняющих сложные запросы по базе данных размером 2-3 ГБ.

Источник

Различия индексов MySql, кластеризация, хранение данных в MyIsam и InnoDb

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

Что представляет из себя индекс в MySql

Скорость чтения из индекса

Отличия в индексах MyISAM и InnoDb

Первичные и «вторичные» индексы в чем отличия

Вводная информация

Что представляет из себя индекс в MySql

На рисунке изобразил схематично как устроен индекс. Имеются узловые элементы (квадраты) и листья (круги). Предположим у нас есть таблица с колонками «Val» и «ID» как на рисунке. В этой таблице индекс построен по числовому полю «ID». Тогда получается что в узловых элементах находятся значения индекса и ссылки на другой более нижний узел или лист. В листовых же элементах точно так же лежат значения индекса которые уже ссылаются непосредственно на данные из таблицы.

Процесс поиска происходит примерно следующим образом. Например нужно найти строку с индексом 11.

начинаем просмотр корневого (верхнего) узла

первое значение в нем 10

идем к следующему 19, оно уже больше чем нам нужно

по ссылке слева от 19 переходим к следующему нижнему узлу

там первое значение 13, оно больше чем нам нужно

опять по ссылке слева переходим к более нижнему элементу

это уже будет листовой элемент, в нем уже лежат непосредственно данные

просматриваем данные по порядку

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

Скорость чтения из индекса

Такое устройство индекса позволяет обеспечить логарифмическую скорость поиска O(log n). Это очень быстро. Вот таблица где для наглядности посчитал сколько сравнений нужно сделать для поиска записи в таблице с разным количеством данных:

Количество элементов в таблице

Количество сравнений

Отличия в индексах MyISAM и InnoDb

MyIsam это более старый движок чем InnoDb и все описанное выше хорошо описывает устройство индекса именно в MyIsam. Более того для MyIsam можно сказать что первичный индекс и просто обычный индекс ни чем между собой не отличаются. В целом таблицы построенные на движке MyIsam вполне себе могут существовать даже без первичного ключа и без всякого индекса в целом. А вот InnoDb уже более свежий и продвинутый движок, и тут как раз есть отличия первичного ключа и просто индекса. Создать таблицу InnoDb тоже можно не указав первичный ключ, но в этом случае первичный ключ все равно создастся. Это называется суррогатный первичный ключ. InnoDb сам выберет поле по которому нужно этот ключ создать, если ни одно поле не подходит, то создаст новое числовое поле, которое конечно же будет скрыто и в структуре его не увидеть. Для разбора индексов InnoDb первым делом нужно начать с кластеризации.

Кластерный индекс

Кластерный индекс отличается тем, что в отличии от предыдущей картинки, где от листьев шли ссылки непосредственно на строки в таблице, тут все данные строк хранятся непосредственно в самом индексе. Проиллюстрировал это на примере листьев 10, 11, 12. Это хорошо тем что позволяет избежать лишнего чтения диска при переходе по ссылке от листа на данные в строке. Тут непосредственно вся строка лежит в индексе. То есть получается что в InnoDb при создании таблицы и указании первичного ключа будет построено такое дерево, в котором все данные таблицы будут продублированы в листья индекса. Если первичный ключ не задать то колонка для него будет выбрана или создана автоматически и все равно по ней будет построен кластерный индекс.

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

Первичные и «вторичные» индексы в чем отличия

Выше было оговорено что для MyIsam нет разницы между первичными и «вторичными» ключами.

Первичный и вторичный индекс в MyIsam

На картинке нарисован первичный и вторичный ключ в MyIsam. Первичный ключ построен по полю «ID», вторичный по полю «Val». Видно что их структура одинакова. И в том и в другом в листьях расположены значения индекса и ссылки на строки в таблице.

В InnoDb это устроено немного по другому.

Первичный и вторичный индекс в InnoDb

Как уже говорил, таблица тут просто для наглядности. Все ее данные хранятся в первичном (кластерном ключе). Тут первичный ключ построен по полю «Id», вторичный по полю «Val». Видно что в листьях первичного ключа лежат значения индекса + все данные из строк таблицы. Во вторичном же ключе, в листьях лежат значения ключа + первичный ключ.

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

Покрывающие индексы

Смысл покрывающих индексов в том, что MySql может вытаскивать данные непосредственно из самого индекса, не читая при этом всю строку и вовсе не читая строку. Для такой оптимизации нужно чтобы все поля указанные в SELECT имелись в индексе. То есть например у нас имеется таблица с полями «id», «name», «surname», «age», «address». И мы проиндексировали ее по полю «id». В запросе мы хотим получить например «id» и «name». При таком условии MySql найдет по первичному ключу нужную строку, прочитает ее и отбросит все поля не указанные в SELECT. Если же мы немного оптимизируем этот запрос и построим индкес по двум полям «id» и «name», то в таком случае MySql найдя нужную строку по этому индексу не пойдет читать всю эту строку, а просто возьмет данные, которые нужны непосредственно из индекса. Правда есть обратная сторона такого подхода, а именно размер индекса в этом случае будет больше, по этому нужно грамотно подходить к построению покрывающих индексов.

Более подробно можно почитать в очень хорошей книге «MySQL по максимуму» Бэрон Шварц, Петр Зайцев, Вадим Ткаченко

Источник

Читаем (и пишем) MyISAM напрямую

Какого типа данные мы будем читать из MyISAM?

Формат MyISAM — это, на самом деле, несколько форматов (а именно — несколько форматов хранения индексов и несколько типов хранения данных, для данных наиболее распространенные — это fixed и dynamic). Мне было интересно рассмотреть самый-самый простой случай: когда таблица имеет фиксированную длину записи, не содержит NULL полей и тот тип запросов, который мы собираемся тестировать — это простой SELECT * FROM tbl WHERE (условие) без участия индексов и других таблиц (то, что называется full scan).

Зачем?

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

Как будем читать?

Изначально я рассматривал довольно много вариантов, как можно читать данные из MyISAM, в том числе и с использованием индексов. Для чтения напрямую из индексных файлов есть довольно хорошее описание в файле myisam.txt, который раньше распространялся с исходными кодами MySQL, а теперь его можно найти, например, в MariaDB. Для программы, которая хочет читать MyISAM вместе с индексными структурами, вероятно, потребуется собирать ощутимую часть MySQL. Хорошо, что это нужно сделать только один раз :).

Формат хранения для разных структур MyISAM очень неплохо описан здесь: forge.mysql.com/wiki/MySQL_Internals_MyISAM. Формат же для fixed строк настолько прост, что его можно привести прямо в тексте статьи:

Для разных типов MySQL двоичное представление разное, и оно прекрасно описано по ссылке выше

1) если длина одной строки невелика (скажем, 10 байт), то даже при использовании fread() стоит читать не по одной строке, а, скажем, по 100 — для моей программы это ускорило чтение из файла где-то в 2 раза
2) скорее всего, доступ к отдельным полям будет невыровненным, поэтому, если ваша архитектура отличается от x86 (например, PPC), то не стоит использовать следующую простую конструкцию для того, чтобы вытащить какое-то конкретное поле: *(int*)(ptr) (ptr — указатель на начало поля, само поле, при этом, имеет тип int)

Помимо этого, необходимо озаботиться вопросами блокировки таблицы при начале чтения (это можно сделать с помощью LOCK TABLES tbl READ). Если этого не сделать, то есть шанс нарваться на «грязные» данные при чтении (вплоть до перезаписанных наполовину строк, если не повезло). С другой стороны, именно при таком сценарии чтения из MyISAM можно вполне безопасно игнорировать блокировки и читать из таблицы в тот момент, когда туда кто-то пишет, имея минимум побочных эффектов.

Вы что-то говорили про запись?

Да, MyISAM с fixed форматом строк настолько прост, что мы можем сами создавать таблицы для MySQL и наполнять их данными. Разве что, без индексов. Но они нужны далеко не всегда, а если они очень нужны, то можно впоследствии попросить MySQL создать их через ALTER TABLE.

Что нам потребуется

При возникновении проблем с чтением «новоиспеченных» таблиц стоит сначала проверить, что вы не переписываете существующую таблицу, а создаете новую. Если вы хотите записать таблицу вместо старой — сначала нужно сделать DROP TABLE этой таблицы непосредственно из MySQL, и только потом создавать эту таблицу заново (чтобы MySQL случайно не заюзал кеш открытых файлов). Другой вариант — сделать FLUSH TABLE для этой таблицы и после этого записывать туда новые данные (пригодится, если вы делаете APPEND к таблице).

150 строк кода на Си.

Так что, если вам вдруг очень нужна супер-высокая производительность full scan, то попробуйте MyISAM + свою программу на С для чтения из таблицы. Вы будете приятно удивлены, насколько просто это реализовать, и какой малой кровью вы сможете поднять производительность чтения в несколько раз.

UPD
Вы просили цифры? Вот вам немножко:

1. Цифры будут очень сильно зависеть от реализации. Я уже написал, что у меня получилось ускорить нужный мне запрос в 4 раза. Итого, скорость чтения составила 1 Гб/сек при размере записи в 25 байт
2. Различия при FULL SCAN при использовании HANDLER или SELECT я не обнаружил.
FULL SCAN в MyISAM дает скорость чтения, по моим оценкам, как минимум в 3-4 раза больше, чем FULL SCAN в InnoDB.
3. Сам по себе FULL SCAN в MyISAM работает в 5-10 раз быстрее, чем скан по индексу с таким же числом записей (при некотором желании, отставание можно сократить до где-то 2-3 раз).
4. В InnoDB скан по PRIMARY KEY работает также быстро, как FULL SCAN, скан по любому другому индексу работает где-то в 3-4 раза медленней.

Сравнения с NoSQL не имеют особого смысла, если делается FULL SCAN для большой таблицы:

1. Memcached не умеет делать FULL SCAN памяти.
2. HandlerSocket хоть и умеет FULL SCAN, но по скорости от SELECT/HANDLER не отличается (если много строк). К тому же, в MySQL 5.1 он не поддерживается
3. Mongo, Redis, etc занимают на порядок больше памяти/стораджа, что во многих случаях абсолютно неприемлимо. Они работают быстро, спору нет. Но предназначение у них различается

Источник