Краткое содержание: в MySQL есть несколько движков хранения, у каждого есть свои преимущества и недостатки. Вы можете выбрать лучший вариант для использования: MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

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

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE.

MySQL поддерживает несколько движков хранения в качестве обработчиков для разных типов таблиц. Движки хранения MySQL включают движки, работающие с транзакционными таблицами, и движки, работающие с нетранзакционными таблицами:

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

• Движок хранения MEMORY предоставляет таблицы «в памяти». Движок хранения MERGE позволяет обрабатывать набор идентично обработанных таблиц MyISAM как одну таблицу. Как и MyISAM, движки хранения MEMORY и MERGE работают с нетранзакционными таблицами, и оба движка включены в MySQL по умолчанию.

  Примечание: движок хранения MEMORY ранее назывался движком HEAP.

• Движки хранения InnoDB и BDB предоставляют транзакционно-безопасные таблицы. BDB включён в бинарные дистрибутивы MySQL-Max, выпущенные для операционных систем, которые его поддерживают. InnoDB также по умолчанию включён во все бинарные дистрибутивы MySQL 5.1. Вы можете настроить MySQL, чтобы включать или отключать любой из этих движков по своему усмотрению.

• Механизм хранения EXAMPLE — это «заглушечный» механизм, который ничего не делает. С помощью этого механизма можно создавать таблицы, но данные в него не сохраняются и из него не извлекаются. Цель этого механизма — служить примером в исходном коде MySQL, демонстрируя, как начать написание нового механизма хранения. Также он представляет интерес в основном для разработчиков.

• NDB Cluster — это механизм хранения, используемый MySQL Cluster для реализации таблиц, секционированных между несколькими компьютерами. Он поставляется в бинарных дистрибутивах MySQL-Max 5.1. Этот механизм хранения в настоящее время поддерживается только в Linux, Solaris и Mac OS X. В будущих дистрибутивах MySQL мы планируем добавить поддержку этого механизма на других платформах, включая Windows.

• Механизм хранения ARCHIVE используется для хранения больших объёмов данных без индексов при очень малом объёме занимаемого места.

• Механизм хранения CSV хранит данные в текстовых файлах в формате с разделителями-запятыми.

• Механизм хранения BLACKHOLE принимает данные, но не сохраняет их, а выборка всегда возвращает пустой набор.

• Механизм хранения FEDERATED хранит данные в удалённой базе данных. В MySQL 5.1 он работает только с MySQL, используя MySQL C Client API. В будущих дистрибутивах мы планируем сделать возможным его подключение к другим источникам данных с помощью других драйверов или клиентских методов подключения.

Наиболее часто используемыми являются MyISAM и InnoDB

Как выбрать между движками хранения MySQL MyISAM и InnoDB?

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

Ниже мы рассмотрим различия между этими двумя движками хранения.

• Во-первых, InnoDB поддерживает транзакции, а MyISAM — нет. Это очень важно. Транзакция — это продвинутый метод обработки. Например, в серии вставок, удалений и изменений, если возникает какая-либо ошибка, можно выполнить откат и восстановить данные, но MyISAM этого не может.
• Во-вторых, MyISAM подходит для приложений, ориентированных на запросы и вставки, тогда как InnoDB подходит для приложений с частыми изменениями и более высокими требованиями к безопасности.
• В-третьих, InnoDB поддерживает внешние ключи, а MyISAM — нет.
• В-четвёртых, движком хранения по умолчанию для версий MySQL до 5.1 является MyISAM, а для версий после 5.1 — InnoDB.
• В-пятых, InnoDB не поддерживает индексы типа FULLTEXT.
• Шестое: InnoDB не сохраняет количество строк таблицы. Например, при выполнении select count(*) from table InnoDB приходится сканировать всю таблицу, чтобы вычислить, сколько в ней строк, тогда как MyISAM может просто прочитать сохранённое количество строк. Обратите внимание: когда оператор count(*) содержит условие WHERE, MyISAM также приходится сканировать всю таблицу.
• Седьмое: для автоинкрементных полей InnoDB должен содержать индекс, включающий только это поле, но в таблицах MyISAM можно создать составной индекс вместе с другими полями.
• Восьмое: при очистке всей таблицы InnoDB удаляет строки по одной, что очень неэффективно. MyISAM вместо этого пересоздаёт таблицу.
• Девятое: InnoDB поддерживает блокировку на уровне строк (в некоторых случаях она всё ещё блокирует всю таблицу, напримерupdate table set a=1 where user like '%lee%'

Основываясь на указанных девяти различиях и особенностях персональных блогов, рекомендуется использовать MyISAM для систем персональных блогов, потому что в блоге основные операции — это чтение и запись, а связанных операций мало. Поэтому выбор движка MyISAM делает эффективность открытия страниц вашего блога выше, чем у блогов, использующих движок InnoDB. Конечно, это лишь личная рекомендация; большинству блогов всё же следует выбирать осторожно, исходя из фактических обстоятельств.

Некоторые замечания по выбору между MyISAM и InnoDB:

MYISAM и InnoDB — это два движка хранения, предоставляемых базой данных MySQL. У каждого есть свои преимущества и недостатки. InnoDB поддерживает расширенные возможности реляционных баз данных, такие как транзакции и блокировка на уровне строк, которые MyISAM не поддерживает. MyISAM имеет более высокую производительность и занимает меньше места для хранения. Поэтому выбор движка хранения зависит от конкретного приложения.

Если вашему приложению обязательно требуются транзакции, вам, несомненно, следует выбрать движок InnoDB. Однако учтите, что блокировка на уровне строк в InnoDB условна. Когда в условии WHERE не используется первичный ключ, вся таблица всё равно будет заблокирована. Например, оператор DELETE, такой как DELETE FROM mytable.

Если ваше приложение предъявляет высокие требования к производительности запросов, следует использовать MyISAM. MyISAM отделяет индексы от данных, а его индексы сжаты, что позволяет лучше использовать память. Поэтому производительность запросов у него значительно выше, чем у InnoDB. Сжатые индексы также экономят немного дискового пространства. MyISAM поддерживает полнотекстовое индексирование, что может значительно оптимизировать эффективность запросов LIKE.

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

Некоторые другие утверждения:

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

Что касается движка хранения по умолчанию в базе данных MySQL:

MyISAM и InnoDB — это два движка хранения MySQL. Если это установка по умолчанию, то должен быть InnoDB. Вы можете найти default-storage-engine=INNODB в файле my.ini. Конечно, при создании таблицы можно указать соответствующий движок хранения. Соответствующую информацию можно увидеть, выполнив show create table xx.

Сравнение InnoDB и MyISAM в MySQL

MyISAM：

Каждая таблица MyISAM хранится на диске в виде трёх файлов. Имя первого файла начинается с имени таблицы, а расширение указывает тип файла. Файл .frm хранит определение таблицы. Файл данных имеет расширение .MYD (MYData).

Таблицы MyISAM можно сжимать, и они поддерживают полнотекстовый поиск. Они не поддерживают транзакции и внешние ключи. Если транзакция откатывается, это приведёт к неполному откату и отсутствию атомарности. При выполнении UPDATE используется блокировка таблицы, поэтому конкурентность относительно невысокая. Если вы выполняете большое количество операторов SELECT, MyISAM — лучший выбор.

MyISAM отделяет индексы от данных, и индексы сжимаются, что значительно повышает эффективность использования памяти. Можно загрузить больше индексов. InnoDB, напротив, тесно связывает индексы и данные вместе без сжатия, из-за чего InnoDB значительно больше, чем MyISAM.

MyISAM кэширует индексы в памяти, а не данные. InnoDB кэширует данные в памяти. Относительно говоря, чем больше памяти на сервере, тем больше преимуществ может извлечь InnoDB.

Преимущества:Запрос данных выполняется относительно быстро, подходит для большого количества операторов SELECT и поддерживает полнотекстовое индексирование.

Недостатки:Не поддерживает транзакции, не поддерживает внешние ключи, имеет низкую конкурентность и не подходит для большого количества операторов UPDATE.

InnoDB：

Этот тип безопасен для транзакций. Он обладает теми же характеристиками, что и тип BDB, и они также поддерживают внешние ключи. Таблицы InnoDB очень быстры. У них даже больше возможностей, чем у BDB, поэтому, если вам нужен безопасный для транзакций механизм хранения, рекомендуется использовать его. При UPDATE используется блокировка на уровне строк, поэтому конкурентность относительно высокая. Если ваши данные включают большое количество операций INSERT или UPDATE, по соображениям производительности следует использовать таблицы InnoDB.

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

Недостатки:Запрос данных относительно быстр, но не подходит для большого количества инструкций SELECT.

Для таблиц InnoDB, поддерживающих транзакции, основная причина, влияющая на скорость, заключается в том, что включена настройка AUTOCOMMIT по умолчанию, а программа явно не вызывает BEGIN для начала транзакции, из-за чего каждая вставленная строка автоматически фиксируется, что серьёзно влияет на скорость. Можно вызвать BEGIN перед выполнением SQL, и несколько инструкций SQL образуют одну транзакцию (даже если autocommit включён), что значительно повысит производительность.

Основные различия таковы:Тип MyISAM не поддерживает такую продвинутую обработку, как обработка транзакций, тогда как тип InnoDB поддерживает её.

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

Другие сравнения:

MyIASM — это новая версия таблицы ISAM со следующими расширениями:

• Переносимость на двоичном уровне.
• Индексация столбцов NULL.
• Меньшая фрагментация для строк переменной длины, чем у таблиц ISAM.
• Поддерживает большие файлы.
• Лучшее сжатие индексов.
• Лучшее статистическое распределение ключей.
• Улучшенная и более быстрая обработка auto_increment.

Ниже приведены некоторые различия в деталях и конкретной реализации:

• 1. InnoDB не поддерживает индексы типа FULLTEXT.
• 2. InnoDB не хранит точное количество строк в таблице. То есть при выполнении select count(*) from table InnoDB приходится сканировать всю таблицу для вычисления количества строк, тогда как MyISAM достаточно прочитать сохранённое количество строк. Заметьте, что когда выражение count(*) включает условие WHERE, операция для обеих таблиц одинакова.
• 3. Для полей AUTO_INCREMENT InnoDB должен включать индекс, содержащий только это поле, но в таблицах MyISAM составной индекс можно создать вместе с другими полями.
• 4. При выполнении DELETE FROM table InnoDB не перестраивает таблицу; он удаляет строки по одной.
• 5. Операция LOAD TABLE FROM MASTER не работает с InnoDB. Решение состоит в том, чтобы сначала преобразовать таблицу InnoDB в таблицу MyISAM, импортировать данные, а затем преобразовать её обратно в таблицу InnoDB. Однако это неприменимо к таблицам, использующим дополнительные возможности InnoDB (например, внешние ключи).

Кроме того, блокировка на уровне строк в InnoDB не абсолютна. Если MySQL не может определить диапазон для сканирования при выполнении SQL-инструкции, таблицы InnoDB также заблокируют всю таблицу, например: update table set num=1 where name like "%aaa%".

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

Сравнение обновлений между InnoDB и MyISAM:

Организация данных InnoDB — это B+-дерево, построенное по первичному ключу. Если первичный ключ явно не определён, InnoDB выберет ключ NOT NULL UNIQUE в качестве первичного ключа. Если его всё ещё нет, InnoDB создаст 6-байтовый первичный ключ. Индекс первичного ключа указывает на страницу, а не на конкретную позицию строки.

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

Большая таблица не страшна; страшно — это COUNT или LIMIT с большим смещением. Можно заменить большой LIMIT big на LIMIT max_id, xxxxx.

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

Для InnoDB партиционирование таблицы по определённому столбцу и надежда повысить производительность на одном сервере не имеет смысла.

Скорость вставки и скорость запросов иногда являются непримиримыми противоречиями.

Утверждение, что InnoDB не подходит для COUNT, неверно. MyISAM столь же медленный. Просто MyISAM кэширует количество строк всей таблицы, поэтому подсчёт по всей таблице быстр. Если есть условие запроса и это не запрос по первичному ключу, разницы нет. Причина, по которой COUNT по первичному ключу медленный, в том, что InnoDB организована по первичному ключу. При подсчёте по первичному ключу загружаются данные.

Страничное хранение InnoDB упрощает полное кэширование таблицы и горячее резервное копирование.

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

InnoDB не хранит точное количество строк в таблице. То есть при выполнении select count(*) from table InnoDB приходится сканировать всю таблицу, чтобы вычислить количество строк, тогда как MyISAM достаточно прочитать сохранённое количество строк. Обратите внимание, что когда в операторе count(*) есть условие WHERE, работа с обеими таблицами одинакова.

Исходный адрес: https://my.oschina.net/junn/blog/183341