Краткий обзор движков таблиц MySQL

    Цель этой статьи — дать краткий, очень сжатый обзор движков, для того, чтобы статьей можно было пользоваться при выборе движка на этапе проектирования \ создания \ оптимизации таблицы. Предполагается, что читатель знает суть вопроса по крайней мере поверхностно и способен сам отыскать всю дополнительную информацию (вопросы в комментах можно задавать всегда :) )

    MyISAM


    • транзакций нет
    • макс. диск: 256Тб
    • блокировка таблица
    • полнотекстовый поиск
    • работа в кластере: нет
    • поддержание целостности, внешние ключи: нет
    • репликация: да
    • макс. индексов: 64
    • макс. записей: 2^32
    • макс. длина ключа: 1000 байт
    • ключи занимают место на диске до (макс.): (key_length+4)/0.67
    • чувствительные к «падению» сервера, сложно восстанавливать
    • при отсутствии «дырок» (gaps) — вставки не конкурентные (блокировок не происходит)
    • возможно хранить файлы данных и индексов на разных устройствах
    • каждый столбец может иметь свою кодировку
    • макс. сумма длин VARCHAR и CHAR: 64к

    Static (Fixed-length) формат таблиц

    • автоматически, если нет VARCHAR, VARBINARY, BLOB, TEXT столбцов
    • быстрее, безопаснее (устойчивее), лучше кешируется, требует больше места на диске
    • если указать принудительно, VARCHAR и CHAR заполняются пробелами, VARBINARY — нулями

    Dynamic length формат таблиц

    • все строки длиной до 4 — VARCHAR
    • пустые строки и ноль (0) не занимают места на диске (NULL это не ноль)
    • запись (строка) фрагментируется автоматически при апдейтах (нужно запускать OPTIMIZE TABLE для дефрагментации)
    • сложнее восстановить при сбоях

    Compressed

    • создается утилитой myisampack
    • read-only
    • рекомендуется для очень медленных носителей
    • может быть и fixed-length и dynamic-length
    • посмотрите в сторону Archive table engine

    Советы:
    • говорят, что MyISAM таблицы обязательно «ломаются» рано или поздно, так что будте готовы ;)
    • не убивайте сервер во время записи
    • не изменяйте таблицы несколькими серверами одновременно
    • не изменяйте таблицы утилитой и сервером одновременно

    Рекомендации: справочники

    InnoDB


    • макс. диск: 64Тб
    • полная поддержка транзакций (4 уровня изоляции)
    • блокировка записи (не таблицы), два вида блокировок (SHARED, EXCLUSIVE)
    • полнотекстовый индекс: нет
    • безопасная для транзакций
    • индексы кластеризуются для «типичных запросов»
    • поддержка целостности (внешние ключи)
    • может использоваться на ОС с ограниченным размером файла
    • множество настроек для оптимизации
    • позволяет использовать Raw Disk для таблиц в обход ФС
    • по умолчанию включен AUTOCOMMIT (SET autocommit=1)
    • автоматически детектит дэдлоки (deadlocks)

    Движок был разработан специально для больших таблиц. Разработчики заявляют, что InnoDB — самый быстрый из всех известных движков для БД основанных на дисках (множественные тесты это подтверждают)

    Советы:
    • SELECT (*) FROM table работает гораздо медленнее, чем MyISAM — создавайте триггеры если нужно
    • бэкап простым копирование файлов невозможен
    • mysqldump работает медленно, для бэкапа используйте InnodDb Hot Backup
    • следите за индексами, выгода InnoDB теряется, если для запросов нет индексов

    Рекомендации: высоконагруженные сайты, финансовые транзакции

    MERGE


    • Используется для объединения одинаковых таблиц в одну
    • таблицы должны иметь идентичную структуру
    • порядок столбцов должен совпадать
    • DROP не удаляет исходных таблиц
    • таблицы могут быть в другой базе данных
    • можно использовать для алиасов (для одной таблицы)
    • нельзя пользоваться FULLTEXT search
    • нельзя смешивать временные и не временные таблицы
    • медленная при чтении по ключу
    • REPLACE не работает
    • не отслеживаются изменения в структуре исходных таблиц (таблица будет поломана)

    Рекомендации: «удобная» (ре)организация таблиц

    HEAP (MEMORY)


    • транзакций нет
    • блокировка таблицы
    • репликация: да
    • макс. длина ключа: 500 байт
    • все данные теряются при остановке сервера (сама таблица остаётся)
    • формат хранения: всегда fixed-length row
    • память не высвобождается при удалении записи (используется для вставки новых)

    Советы:

    большие таблицы «свопяться» на диск и выигрыш теряется

    Рекомендации: Локальные вычисления, временные данные

    ARCHIVE


    • макс. диск: нет ограничения
    • блокировка записи
    • не работает DELETE, REPLACE, UPDATE, ORDER BY, тип BLOB
    • INSERT буферизируется и «сливается» с большой задержкой
    • очень медленный SELECT

    Рекомендации: логирование операций (аудит, статистические данные, счетчики)

    CSV


    • хранит таблицы в CSV формате
    • позволяет редактировать таблицы внешними приложениями
    • плохо документирован, есть открытые баги


    FEDERATED



    Представляет собой «прозрачное» подключение к другому серверу (не репликация). Есть множество ограничений, планируется возможность подключения к отличным от MySQL серверам.

    BLACKHOLE


    • данные идут «вникуда»
    • двоичные логи пишуться

    Рекомендации: оптимизация репликации (мастер-сервер не пишет данные на диск)

    Тренды, MySQL 6.0


    • Maria — «улучшенный MyISAM»
    • Falcon — «улучшенный InnoDb», улучшенная производительность, для Web серверов


    Ссылки:

    MySQL Documentation, Chapter 13. Storage Engines
    MySQL Storage Engines by Mike Peters

    Комментарии 73

    • НЛО прилетело и опубликовало эту надпись здесь
        +6
        Нужная статья, таких надо больше. Коротко и по делу.
          –3
          Ничего полезного в статье нет. Это для тех что ли, кто не знает английский и не может прочитать сухие технические данные из документации?

          А нужные статьи с подходом «практика» и сравнением движков уже написаны.
            +8
            Все уже где-то когда-то написано. Мне полезно было ознакомиться с сегодняшним состоянием дел с MySQL. А в документацию лезть некогда. Кто знает — читает заголовок и пропускает.
              0
              Разве не удобно когда у тебя на одной странице описаны основные характеристики движков мускула? Или легче для этого перерыть десяток отдельных страниц документации?
                0
                Я согласен. Это неплохо, но в целом вместо перевода/пересказа/etc было бы гораздо интереснее прочитать личный опыт (это касается всех тем, не только MySQL).
                  0
                  Отличная шпаргалка, в закладки.
                  0
                  Очень содержательный комментарий, надо таких больше. Коротко и по делу.
                  0
                  А минусы-то забыли…
                    0
                    Дак и плюсов нет — они все в пуктах перечислены.
                    Есть рекомендации и советы.
                    +3
                    за подборку спасибо

                    Движек. реплекация, улучшеный
                    ДвижОк реплИкация, улучшенНый поправьте в статье, режет глаз.

                    Вообще с орфографией не очень :(, проверить бы текст хотя бы Вордом.
                      0
                      Присоединяюсь. Если переводите, то, пожалуйста, на русский.
                    • НЛО прилетело и опубликовало эту надпись здесь
                        0
                        Почему?
                        • НЛО прилетело и опубликовало эту надпись здесь
                            +1
                            Пустые заявления. Аргументируйте, или еще лучше напишите запись в блоге. Мне искренне интересно.
                            • НЛО прилетело и опубликовало эту надпись здесь
                              0
                              collations? кластер? partitioning? raw disk? memory engine?
                                0
                                По аналогии с этим топиком habrahabr.ru/blogs/linux/64957/ захотелось создать тему «Мифы и заблуждения, касающиеся MySQL» (спешл фор постгре юсерс)
                                0
                                Я люблю постгресс за возможность поиска по массивам и хэшам, хранящихся в ячейках таблиц.
                                Это позволяет гораздо удобнее работать с объектами — сохранять и восстанавливать их в/из БД.
                                  0
                                  Потому что нет проблем с ibdata.
                                  0
                                  Пользую обе базы.
                                  MY — простая база, есть на всех хостингах. Часто заказывается клиентом при разработках.
                                  PG — для серьезных проектов. И работать мне с ним на порядок приятней, один только CLI чего стоит по удобству. (работаю в шеле)
                                    0
                                    Юзайте DB2 Express-C! (он намного быстрее, чем MySQL и фишек больше).
                                      0
                                      Тогда уж Oracle, он удобней в администрировании, да и спецов на поддержку больше
                                        +2
                                        Хорошо что хоть не MSSQL предлагаете. И вообще, чего по реляционным базам всё, давайте переходить на объектные :)
                                          0
                                          Ну во первых MSSQL круче DB2 на порядки по простоте, удобству и кое-где по скорости (с Oracle не сравнивал).
                                          Я его не предлагаю только потому, что он Win-only (и лимита на размер бесплатной базы).
                                            0
                                            Что касается object и document DB — напишите статью, будет оч. интересно, особенно если с опытом на большом объёме.
                                            Выглядят они неплохо, но сам не пробовал.

                                            Помню статью «давайте откажемся от реляционных баз», которую писали замучанные MySQL авторы какого-то стартапа.
                                            У них индексы создавались несколько часов, локи, etc. Ну да, от такой базы мы и сами отказались.
                                              0
                                              Поищите здесь же на хабре «db4o» — должна быть статья про объектную базу данных.
                                            +2
                                            Хорошо что хоть не MSSQL предлагаете. И вообще, чего по реляционным базам всё, давайте переходить на объектные :)
                                              0
                                              4 Gb ограничение на размер бесплатной базы и 1 Gb на память.
                                                0
                                                Ах да, забыл что с ограничениями либеральней у DB2, sorry :)
                                          0
                                          медленная при чтение по ключу
                                          Исправьте пожалуйста.
                                            0
                                            Чего ж про falcon так мало? А federated? Руки устали копировать?
                                            Да и вообще практической ценности никакой, кто бы там что не говорил про «удобство», любой может это за 10 минут накопировать из мануала. Про механизмы надо читать подробно, понимать их сущность, как они работают, а не по этим столбикам выбирать «при проектировании». Ладно бы ещё полная таблица была, а тут и то, незаконченные огрызки.
                                              0
                                              webiteam.ru/2009/03/mysql-storage-engines/
                                                +2
                                                Я бы не сказал, что InnoDB — самый лучший для больших таблиц и самый быстрый.
                                                Особенность механизма хранения индексов в InnoDB может как ускорить работу с базой, так и подвесить намертво самоё мощное железо.
                                                Вот пример, как упал сервис Яндекса из-за InnoDB — softwaremaniacs.org/blog/2008/02/22/why-offline-crashed/

                                                Дело в том, что он перестраивает индексы по порядку, собственно это и даёт скорость. Но если у вас вставляется множество данных с случайным ключом, те же сессии, это подвешивает жёсткий диск намертво.
                                                И не говорите, что это ошибка конкретно этих программистов, многие из популярных скриптов, CMS, форумов и пр. тоже стартуют сессию на каждого посетителя.

                                                P.S. вообщем, к выбору движка надо подходить с умом и для каждого проекта решать этот вопрос индивидуально.
                                                У MyISAM есть уникальные преимущества (тот же полнотекстовый поиск), а дополнительные возможности более мощной InnoDB и тем более PostgreSQL, Microsoft Server, Oracle, DB2 далеко не всегда нужны.
                                                  0
                                                  Иногда имеет смысл некоторые таблицы переводить в MyISAM.
                                                    0
                                                    >> Вот пример, как упал сервис Яндекса из-за InnoDB — softwaremaniacs.org/blog/2008/02/22/why-offline-crashed/

                                                    Сервис упал не из-за ИнноДб, а из-за криворукости программистов.
                                                    1. Primary key — md5-хеш — иначе как долбоебизмом это я назвать не могу
                                                    2. На индексе была одна выборка с join'ом четырех таблиц, одна из которых — самая здоровенная таблица базы

                                                    Кстати, собственно Салагаев ни в чём ИнноДб не обвиняет, а пишет о своих косяках и к чему они привели.
                                                      0
                                                      1. Да все использует md5 или что-то подобное для генерации идентификатора сессии. А что Вы предлагаете? Автоинкрементный ключ? Так это потенциальная дыра в безопасности. Ключ обязательно должен быть случайным.
                                                      2. База упала не из-за сложной выборки, а из-за множественных вставок записей в БД.

                                                      И повторюсь это не ошибка конкретных программистов, например, вся тройка форумов рунета PHPbb, IPB, vBulletin стартует сессию на каждого гостя и их разработчики не считают это решение криворуким.
                                                        0
                                                        Стартовать сессии для гостей хранящиеся_в_БД — простите бред.

                                                        Тем более — на главной Яндекса. Насколько я понял — Салагаев просто не ожидал такого поведения (сохранения сессий в БД) от Джанго.

                                                        ИнноДб — здесь не причём абсолютно. Хватит уже распространять мифы. Для кластерных индексов в любой_бд — будет строиться дерево.

                                                        Как готовить сессии:

                                                        1. если необходимо хранить какую то временную информацию для гостей — целесообразно сохранять данные сессии в куки.

                                                        Зачем это нужно? Вот пример:

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

                                                        2. Сохранение данных о сессии в базу данных

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

                                                        Если данные сессии находятся в базе данных, то каждый раз, когда в cookies пользователя обнаруживается рабочая сессия, осуществляется запрос к базе данных — с целью сравнить ID сессий. Если ID сессии не совпадают, то сессия разрушается. ID сессии никогда не обновляется, он может быть лишь сгенерированным, когда сессия создается.
                                                          0
                                                          Другими словами хранение сессий в БД необходимо там, и только там, где необходима высокая безопасность. Для того, чтобы злоумышленник — укравший кукис пользователя не мог, например, списать средства со счета пользователя.

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

                                                          Более того, данную информацию целесообразно шифровать.

                                                          Рекомендую посмотреть на реализацию класса сессий в движке codeigniter (PHP): www.code-igniter.ru/user_guide/libraries/sessions.html

                                                          Там есть и шифрование, и опциональное сохранение в БД с синхронизацией и flashsdata для временных данных.

                                                          MD5 для ключа — кстати, вполне оправдано. Здесь я был не прав.
                                                            0
                                                            Миф говорите, прочтите документацию по InnoDB:
                                                            … and new rows are inserted in the ascending order of the primary key.
                                                            … и новые строки вставляются отсортированными по первичному ключу.

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

                                                            И это касается всех таблиц, где в качестве Primary Key используется случайное (неавтоинкрементное) число, а не только сессий, их просто для примера привел.
                                                              +1
                                                              Это абсолютно верное поведение кластерных индексов, соответствующее стандарту SQL.
                                                              Кластерные индексы медленнее на вставке и быстрее на селектах.
                                                              Если разработчик этого не знает — это не проблема движка, это проблема разработчика.

                                                              Если необходимо избежать данного поведения достаточно было сделать автоинкрементное поле integer — primary, а значения хэша хранить в отдельном поле, по которому построить уникальный secondary index.

                                                              Именно это написано в мануале — Clustered and Secondary Indexes: www.dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

                                                              Какая то неинтересная дискуссия. Предлагаю закрыть.
                                                            0
                                                            crc32(md5("..."))
                                                            0
                                                            пару лет назад таблицы с авторизацией\сессиями перевел с MyISAM на InnoDB
                                                            работать стали раза в два быстрее, так как убрались локи…

                                                            пару месяцев назад таблицы с авторизацией\сессиями перевел с и InnoDB на Memory
                                                            итого полумертвый сервер(load 600%/800%) стал почти что idle ( 100%/800% )
                                                            а полгига памяти мне не жалко
                                                          +1
                                                          Мой опыт:

                                                          MyISAM (~150Гб). 5 лет жёсткой эксплуатации, проблем — нет. Ничего не ломалось и не падало.
                                                            0
                                                            Вы понимаете, что Вы лишили аргументов всех фанатов InnoDB и PostgreSQL :)
                                                              0
                                                              Это только мой опыт.
                                                              Думаю, что у тех кто использует InnoDB, PostgreSQL, Microsoft Server, Oracle, DB2 и т.д. — свой :-)
                                                                0
                                                                Почему-то пишут что MyISAM упадёт обязательно, и что надо в кроне держать команду проверки целостности.
                                                                  0
                                                                  А у вас лично какой опыт?
                                                                  у вас лично, как он, myisam. Падает?
                                                                    0
                                                                    У меня опыт InnoDB, а об этой особенности MyISAM читал в интернете. Не хочется учиться на своих ошибках. Хотя MyISAM скорее всего придется использовать для полнотекстового поиска.
                                                                    0
                                                                    На заборе тоже написано…
                                                                      0
                                                                      Напишите статью по этому поводу, что мол MyISAM отличный движок. А то пока видел обратное — MyISAM не любит отключения питания, временами портится без видимых причин, восстановить не сложно, но требует ручного вмешательства. Не буду же я проверять на себе, лучше доверюсь чужому опыту. И поверю тем, кто написал об этом, а не кто промолчал :)
                                                                        0
                                                                        MyISAM — не лучше и не хуже :) там просто другие принципы.

                                                                        Это всёравно, что сравнивать BMW, Audi и т.д. у них есть что-то общее, но разные технологии. Где-то нужна скорость, а где-то проходимость… Высокий или низкий клиренс…

                                                                        Все стремятся к чему-то оптимальному, вот и технологии разные. Ещё нужно учитывать разные войны патентов и т.д.
                                                                          0
                                                                          Это понятно, иначе бы не существовало столько движков.
                                                                          0
                                                                          Прямо видели как портится без видимых причин? Вам повезло, мне за 10 лет работы с myisam не удалось это увидеть.

                                                                          Номер бага можете привести, я хочу посмотреть?

                                                                            0
                                                                            Я ничего не видел, прочитал об этом в интернете, когда выбирал движок для своей первой мускульной базы.
                                                                0
                                                                >макс. записей: 2^32

                                                                Из документации

                                                                There is a limit of 2^32 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (2^32)^2 (1.844E+19) rows. Binary distributions for Unix and Linux are built with this option.
                                                                  0
                                                                  Помню, что в версии 3.23 можно создавать таблицы до 8 миллионов терабайтов (2 ^ 63 bytes)!
                                                                  А, сейчас уже 5 версия!!!
                                                                    0
                                                                    ну так 2^32 это же не объем данных, а кол-во записей.
                                                                    Да и врядли где-то будет использоваться база с объемом в 8 миллионов террабайтов :-D
                                                                      0
                                                                      Когда-то и гигабайт казался фантастикой :) А сегодня терабайт уже повседненая реальность. Разработчики далеко вперед заглядывают (и правильно делают).
                                                                        0
                                                                        Ну… если грамотно заглядывать (как в Sun), то это конечно хорошо =)
                                                                        Но бывают случаи… когда незнаешь — то ли плакать, то ли смеяться :(
                                                                        0
                                                                        А информация о всех жителях и их телефонных разговорах, информация систем глобального фото-видеонаблюдения Вы думаете, где хранится? :-)
                                                                          0
                                                                          Ну я не думаю, что в базе MySQL ;)
                                                                            0
                                                                            почему нет?
                                                                            видео поток и фотки, конечно, не стоит пихать в БД, а вот всё остальное…
                                                                            0
                                                                            А может вообще все это дело пишется в один такой бАльшой файлик? Типа как squid =)
                                                                            * на правах шутки
                                                                      –1
                                                                      Статья ни о чем.
                                                                      Перевод документации, причем неполный, а в конце, так вообще абзац на каждый тип.
                                                                      А подводные камни? И вообще:

                                                                      Например тип MERGE:
                                                                      1. Только на основе таблиц MyISAM — про это — ни слова. Разве это не важно? А почему только на основе такого типа?
                                                                      2. цитирую: «не отслеживаются изменения в структуре исходных таблиц (таблица будет поломана)» — Что значит поломана? Что значит не отслеживаются? К чему это приводит? Как это исправить?
                                                                      3. цитирую: «Рекомендации: «удобная» (ре)организация таблиц» — если честно, данная рекомендация ставит в тупик.

                                                                      Тема «сисек» — не раскрыта.
                                                                        +1
                                                                        > InnoDB — самый быстрый из всех известных движков для БД
                                                                        > основанных на дисках
                                                                        Можно источник цитатов?

                                                                        Мои тесты НА ЗАПИСЬ показывают прямо противоположный результат. InnoDB отстает от того же PostgreSQL 8.3 при большом потоке параллельных записей (в том числе — проводимых пачками) в несколько раз. Кроме того, в InnoDB в зачаточном состоянии управление sync-ами, да и просто процессом сброса страниц на диск. Даже Percona Patches тут мало помогает.

                                                                        Что касается чтения, то, возможно, на чтение по первичному ключу InnoDB и правда несколько быстрее, чем тот же Постгрес (это я не проверял), однако мне кажется, что для подобных целей все же лучше использовать нереляционные СУБД. На сложных запросах (всякая там статистика и т.д.) планировщик MySQL попросту не справляется, тут уже движок таблиц мало влияет.
                                                                          0
                                                                          Ссылку на тесты дайте, я хочу посмотреть.
                                                                            0
                                                                            А вы заметили что innodb для соблюдения стандарта ACID при каждой фиксации транзакции записывает на диск и не возвращает ответ до завершения этой операции? Пробовали изменять innodb_flush_log_at_trx_commit?
                                                                            0
                                                                            # двоичные логи пишуться

                                                                            // исправьте, что-ли
                                                                              +1
                                                                              # SELECT (*) FROM table работает гораздо медленнее, чем MyISAM — создавайте триггеры если нужно

                                                                              имелся ввиду COUNT?

                                                                              # бэкап простым копирование файлов невозможен

                                                                              Буквально на днях восстанавливал из бэкапа такую БД: был полный бэкап каталога с данными mysql. Скопировал каталог на свою девелоперскую машину. Начал запускать mysql — ругается на размер чего-то (походу файлов innodb), поменял конфиг — запустился. Сделал дамп и восстановил на серваке.
                                                                                0
                                                                                Видимо, хотели предостеречь от копирования файлов в случае если каждая таблица innodb в отдельном файле. Некоторые по незнанию бекапят эти файлы, а толку не будет.
                                                                              • НЛО прилетело и опубликовало эту надпись здесь

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

                                                                                Самое читаемое