На облачных сайтах, выделенном сервере обычно используется Innodb. Innodb предлагает лучшую производительность на лучшем оборудовании. Это вариант создания таблиц баз данных для вашего сайта, причём очень популярный, имеющий ряд своих преймуществ.
Первый и самый важный момент заключается в том, что я не гуру базы данных. MySQL настолько специализирован, и когда речь идет о MySQL Engine, честно говоря, мне довольно сложно это объяснить. Поскольку вопрос был задан читателем сайта, я пытаюсь ответить.
MyISAM против Innodb MySQL Engine для WordPress
Поскольку нам нужно определенное оборудование, чтобы предлагать MySQL Engine для WordPress, на практике это типичная война MyISAM против Innodb.
WordPress предназначен для работы только на базе данных MySQL. По этой причине оптимизация MySQL является очень важной проблемой, если вы хотите очень быстро запускать WordPress на используемом оборудовании. Но это может быть не так просто, как вы думаете, учитывая, что MySQL может предложить вам множество механизмов хранения.
Начиная с MySQL 5.0, в MySQL есть 10 (да, десять) механизмов хранения. До выпуска MySQL 5.
5 MyISAM был механизмом хранения по умолчанию, и когда вы создаете новую таблицу без указания механизма, таблица будет использовать механизм MyISAM. После обновления до MySQL 5.
5, механизм по умолчанию теперь InnoDB. Самое замечательное в MySQL – то, что вы можете использовать разные механизмы хранения для каждой таблицы.
MyISAM
Это старый (est) механизм хранения в MySQL и наиболее часто используемый. Это простой в настройке движок (нет связей между внешними ключами между таблицами или проблем проектирования) Он очень хорош в операциях, связанных с чтением, и поддерживает полнотекстовое индексирование.
Но у него много недостатков: нет поддержки транзакций, нет проверки целостности данных (нет строгих отношений таблиц) и поддерживается только полная блокировка таблиц, что замедляет работу при обновлении или вставке данных, поскольку для каждого обновления или вставки вся таблица будет быть заблокирован, делая его недоступным для других запросов.
Из-за этого MyISAM в среднем хорош для большинства таблиц WordPress. MyISAM хорошо работает “из коробки” и не требует слишком большой оптимизации. Даже с оптимизацией вы не сможете значительно повысить производительность по сравнению с настройками по умолчанию.
MyISAM не очень надежен в случае аппаратного сбоя, остановки процесса или какого-либо другого сбоя. Это почти всегда вызывает повреждение данных в зависимости от последнего запуска операций.
InnoDB
Это относительно новый механизм, и он поддерживает транзакции, он очень быстр в операциях вставки или обновления, потому что он поддерживает блокировку строк, позволяющую выполнять несколько операций над одной таблицей, и поддерживает внешние ключи для отношений таблиц. И все это делает InnoDB великолепным, когда целостность данных является важной проблемой.
Но разработка таблиц с ограничениями внешнего ключа не всегда проста, она не поддерживает полнотекстовое индексирование и требует больше ресурсов (памяти), чем MyISAM. Также вам нужно потратить некоторое время на оптимизацию этого движка.
В зависимости от уровня оптимизации и используемого оборудования, InnoDB может быть настроен на очень быструю работу, некоторые даже в 20 раз быстрее, чем настройки по умолчанию.
InnoDB не имеет постоянного размера для таблицы. Даже когда вы получаете размеры таблиц, вы должны знать, что возвращаемые значения являются только оценочными. Это происходит из-за того, что InnoDB обрабатывает данные, и поэтому ему требуется больше места для данных, чем MyISAM.
InnoDB очень надежен из-за транзакционного характера операций с данными и делает его очень хорошим выбором для систем, где операции резервного копирования необходимы и часто необходимы. Таблицы InnoDB надежны и имеют много мер безопасности, чтобы обеспечить безопасность данных.
Какой вид таблиц выбрать?
Ну, это не легко ответить. Поскольку InnoDB теперь является механизмом по умолчанию (MySQL 5.5), при установке нового WordPress на сервер, на котором установлена последняя версия MySQL, вы, скорее всего, получите все таблицы InnoDB.
Если у вас мощный сервер с большим объемом памяти, вы не заметите никаких замедлений (если InnoDB настроен правильно), и для многих операций вы можете заметить повышение скорости.
Если у вас есть все таблицы подсистем MyISAM в базе данных, вы можете рассмотреть возможность переключения некоторых из них на подсистему InnoDB. Здесь нет правильного ответа, и все зависит от того, что вам нужно от вашей базы данных. По умолчанию WordPress не использует полнотекстовую индексацию, поэтому он вполне может использовать InnoDB.
Если вам нужно больше надежности данных, InnoDB это путь в лучшее для сайта. Поскольку WordPress не использует внешние ключи для табличных отношений, выбор движка также не важен, оба будут работать хорошо.
Для обычных сайтов без большого трафика движок не так важен. Проблемы со скоростью вступают в игру, если у вас много трафика на вашем сайте. В этом случае InnoDB для некоторых таблиц является хорошей идеей, если эти таблицы нуждаются в большом обновлении.
Лучше всего протестировать оба механизма для разных таблиц в течение определенного периода (опять же, учтите ресурсы вашего сервера) и найти баланс, устанавливающий некоторые таблицы с InnoDB, а некоторые с MyISAM.
Кроме того, не забудьте проверить настройки MySQL для обоих типов движков и проконсультироваться с системным администратором, чтобы убедиться, что оба настроены на лучшую производительность (это очень важно для InnoDB).
Нахождение правильного баланса между этими двумя двигателями может дать вам дополнительную скорость. Рекомендуется использовать только один движок, если у вас есть среда репликации, в которой разные движки могут влиять на производительность репликации. В таком случае InnoDB зарекомендовал себя как лучший выбор.
Я думаю, что WordPress в следующей крупной ревизии (возможно, для 3.4 или 3.5) должен рассмотреть вопрос о настройке движков для каждой таблицы на основе основной роли таблицы. Это потребовало бы тщательного тестирования, но стоило бы иметь лучший выбор двигателей с самого начала.
Лучшая производительность и стабильность
Из моего исследования о MyISAM против InnoDB я узнал, что InnoDB способен использовать преимущества нескольких ядер, тогда как MyISAM может использовать только одно ядро. Это означает, что загружается в 4-ядерный сервер, как мой Linode. InnoDB также имеет другие функции, такие как способность лучше восстанавливаться после сбоя и в целом более стабильный характер.
После того как я переключил формат всех таблиц базы данных на InnoDB, моя общая загрузка ЦП на сервере снизилась, сайты стали значительно быстрее, и ни одного сбоя еще не было. Я все еще в поиске, но сейчас я принял, что InnoDB – лучший формат для баз данных WordPress.
Имейте в виду, что WordPress не решает, какой формат будут использовать базы данных. По умолчанию ваш сервер определяет формат таблицы базы данных.
Как конвертировать WP таблицы из MyISAM в InnoDB
Не забывайте выполнять резервное копирование таблиц и базы данных так часто, как это практически возможно, в том числе и особенно перед тем, как сделать что-то подобное, что может повредить вашу базу данных, испортить все в таблице и, как правило, сломать материал.
Шаг 1 – Узнайте, какая у вас версия MySQL
Это важно. Если вы используете MySQL версии 5.5 или выше, вам не придется беспокоиться о втором шаге, связанном с полнотекстовыми индексами .
До MySQL 5.5 InnoDB не поддерживал полнотекстовые индексы, поэтому все, что у вас есть в ваших таблицах, не будет работать, если вы преобразуете их. Это не значит, что не нужно конвертировать, но это означает, что сначала удалите эти полнотекстовые индексы.
Шаг 2 – Найти и удалить полнотекстовые индексы
Зайдите в MySQL или выполните следующее в PHPMyAdmin для каждой таблицы, которая использует MyISAM – я использую wp_posts в качестве примера:
SHOW INDEX FROM wp_posts;
Это покажет вам индексы. Пока ни один из них не находится в полном тексте, вы можете преобразовать эту таблицу.
Если существует полнотекстовый индекс, удалите его, введя следующую команду:
DROP INDEX index_name ON wp_posts
Вставьте имя вашего полнотекстового индекса и опустите его. Тогда вам будет хорошо преобразовать эту таблицу.
Шаг 3 – преобразовать механизм хранения таблицы
ALTER TABLE wp_posts ENGINE = InnoDB;
Это будет завершено, и вы успешно завершите изменение для этой таблицы. Теперь, чтобы сделать другие старые таблицы тоже!
Повторить на всех таблицах
Повторите это на всех таблицах вашей базы сайта. Я имею тенденцию начинать с самых больших, и работать вниз, но это не действительно необходимо. Просто большие таблицы – это, скорее, исправление, когда что-то идет не так, поэтому сначала я возьму их.
После того, как вы получите их все, вы сможете настроить свой файл My.cnf и получить эту настройку для таблиц InnoDB, чтобы добиться успеха.
Наличие таблиц как в InnoDB, так и в MyISAM, что часто случается с сайтами WP, которые существуют уже давно, означает, что вы должны поддерживать оба в файлах my.cnf.
На мой взгляд, оптимально и лучше масштабировать, чтобы все ваши таблицы были в одном механизме хранения, и чтобы этот механизм хранения был InnoDB. Затем подайте столько же мощности на сервер (ы) базы данных и убедитесь, что он настроен правильно для ваших запросов.
Дополнительно, если вы чувствуете себя достаточно смелым, вы можете выполнить следующие действия, чтобы обновить все свои базы данных:
Настройка механизма хранения по умолчанию
Установите механизм хранения по умолчанию на InnoDB, добавив default_storage_engine = InnoDB в раздел [mysqld] файла конфигурации системы, расположенный по адресу: /etc/my.cnf. Перезапуск службы MySQL необходим серверу для обнаружения изменений в файле.
~ $ cat /etc/my.cnf
[mysqld]
log-error=/var/lib/mysql/mysql.err
innodb_file_per_table=1
default-storage-engine=innodb
innodb_buffer_pool_size=128M
Преобразование всех таблиц между MyISAM и InnoDB
К сожалению, MySQL по своей природе не имеет возможности конвертировать таблицы, оставляя каждую таблицу для индивидуального изменения. Мы составили простой план обслуживания для этого процесса. Сценарий, который вы можете запустить на необходимом сервере через оболочку доступа (SSH), преобразует все таблицы между механизмами хранения.
Планируйте соответственно при выполнении пакетных операций такого рода на случай простоя. Рекомендуется сделать резервную копию всех ваших баз данных MySQL, прежде чем вносить изменения такого масштаба, что обеспечивает простую точку восстановления для предотвращения потери данных.
Планируйте начинать в то время суток, когда простои будут иметь минимальные последствия. Этот процесс сам по себе не требует простоев, однако для восстановления после непредвиденных обстоятельств может потребоваться время простоя.
Резервное копирование всех баз данных в файл
Приведенная ниже команда создает резервную копию одного файла для всех баз данных с именем all-database-backup.sqld и может быть удалена после успешного завершения конвертации и отсутствия явных проблем.
mysqldump —all-database> all-database-backup.sql
Записать существующие движки таблиц в файл
Запустите следующий скрипт, чтобы записать существующие механизмы таблиц в файл с именем table-engine-backup.sql . Затем вы можете «импортировать» или «запустить» этот файл позже, чтобы преобразовать их в исходные движки, если это необходимо.
mysql
-Bse 'SELECT CONCAT(«ALTER TABLE «,table_schema,».»,table_name,»
ENGINE=»,Engine,»;») FROM information_schema.tables WHERE table_schema
NOT IN(«mysql»,»information_schema»,»performance_schema»);' | tee
table-engine-backup.sql
Если по какой-либо причине вам нужно вернуть таблицы движков обратно, выполните:
mysql
Источник: https://ok2web.ru/myisam-protiv-innodb-mysql-engine-dlja-wordpress-chto-luchshe/
Как перевести таблицу из InnoDB в MyISAM
Меняем формат хранения табличных данных. Переводим таблицы базы данных для WordPress из InnoDB в MyISAM.
Зачем может понадобиться смена формата таблиц?
У каждой из таблиц есть свои преимущества и недостатки. В целом считается, что InnoDB более надежная база для больших структур, чем MyISAM.
Однако на деле, в привычной жизни рядового вебмастера таблицы с InnoDB приносят больше проблем, чем MyISAM. Потому что последние чинить гораздо проще.
- Ниже ошибка в базе данных InnoDB, которая не чинится встроенными инструментами SQL, phpMyAdmin или WordPress.
- Вы будете получать уведомления:
the storage engine for the table doesn't support repair wordpress
Также в логах можно увидеть ошибки от InnoDB такого рода:
171027 15:11:53 InnoDB: The InnoDB memory heap is disabled 171027 15:11:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins 171027 15:11:53 InnoDB: Compressed tables use zlib 1.2.7 171027 15:11:53 InnoDB: Using Linux native AIO 171027 15:11:53 InnoDB: Initializing buffer pool, size = 128.0M 171027 15:11:53 InnoDB: Completed initialization of buffer pool 171027 15:11:53 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 171027 15:11:53 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer… 171027 15:11:54 InnoDB: Waiting for the background threads to start
171027 15:11:55 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 402087117
Для починки таких ошибок нужно заходить через SHH, создавать DUMP InnoDB и затем его восстанавливать. А это несколько замороченее, чем починка таблиц MyISAM.
Переводим таблицы из InnoDB в MyISAM
К тому же таблицах в таблицах InnoDB полноценные текстовой поиск не работает (хотя в последних версиях вроде как заявлена). Это приводит к тому, что некоторые плагины поиска на WordPress либо просят перевести таблицу в другой формат либо ищут некачественно.
Команда перевода основных таблиц WordPress из InnoDB в MyISAM:
ALTER TABLE wp_commentmeta ENGINE=myisam; ALTER TABLE wp_comments ENGINE=myisam; ALTER TABLE wp_links ENGINE=myisam; ALTER TABLE wp_options ENGINE=myisam; ALTER TABLE wp_postmeta ENGINE=myisam; ALTER TABLE wp_posts ENGINE=myisam; ALTER TABLE wp_termmeta ENGINE=myisam; ALTER TABLE wp_terms ENGINE=myisam; ALTER TABLE wp_term_relationships ENGINE=myisam; ALTER TABLE wp_term_taxonomy ENGINE=myisam; ALTER TABLE wp_usermeta ENGINE=myisam;
ALTER TABLE wp_users ENGINE=myisam;
Команды нужно вводить в phpMyAdmin или в программах работающих с базами данных удалённо, например, Sequel Pro.
Привет. Ты находишься на моём сайте. Я разработчик. Здесь я делюсь своими наработками и знаниями. Спрашивай в х, если тебе что-то не понятно или пиши, если есть что добавить.
Если вам пригодилась информация, вы можете поблагодарить автора сайта символическим пожертвованием:
Источник: https://ploshadka.net/innodb-to-myisam/
Внешний ключ и целостность данных. Базы данных InnoDB и MyIsam
Определение 1
Внешним ключом называется ограничение, поддерживающее согласованное состояние данных между двумя таблицами, которое обеспечивает ссылочную целостность.
Ссылочная целостность позволяет получить полную информацию о необходимом объекте, которая расположена в нескольких таблицах.
Связь между таблицами не равноправна. В связи существует главная и подчиненная таблица. Самые распространенные связи «один-к-одному» и «один-ко-многим». При связи «один-к-одному» строке главной таблицы отвечает одна или ни одной строки подчиненной таблицы. В связи «один-ко-многим» одной строке главной таблицы соответствует любое количество строк подчиненной таблицы.
Связь устанавливается при равенстве значений определенных столбцов главной и подчиненной таблицы. При этом столбец (или их набор при составном ключе) подчиненной таблицы, который отвечает столбцу (или их набору) главной таблицы, и называют внешним ключом.
Так как главная таблица всегда располагается со стороны «один», то у столбца, который участвует в связи по внешнему ключу, должно быть ограничение UNIQUE или PRIMARY KEY. Внешний ключ задают в процессе создания или изменения структуры подчиненной таблицы с помощью спецификации FOREIGN KEY:
Ничего непонятно?
Попробуй обратиться за помощью к преподавателям
Типы данных столбцов 1 и 2 должны быть попарно совместимыми, а списки столбцов 1 и 2 должны содержать одинаковое количество столбцов.
Например, внешний ключ в таблице Т1 можно создать следующим образом:
Если столбец является первичным ключом главной таблицы, то его можно в скобках не указывать, поскольку он лишь один. Тогда последняя строка может быть записана следующим образом:
Базы данных InnoDB и MyIsam
Изначально SQL был разработан в качестве записной книжки для компьютера. С течением времени требования к хранению и обработке информации росли и СУБД перестали соответствовать потребностям пользователей.
Учитывая недостатки существующих СУБД в 2005 г. было разработано ядро СУБД InnoDB, которое стало поддерживаться MySQL.
Важнейшее отличие InnoDB – отношение к целостности данных. InnoDB поддерживал внешние ключи, транзакции, блокировку на уровне строк, что повысило надежность хранения и обработки информации, но, кроме всего прочего, повлияло на скорость и занимаемое место.
Рассмотрим механизм хранения данных в ядрах СУБД MyISAM и InnoDB .
В InnoDB и в MyISAM данные хранятся в файлах. Различаются они тем, что MyISAM для каждой таблицы создает отдельный файл, InnoDB все хранит в общем по умолчанию.
Также InnoDB поддерживает MVCC («мультиверсионность») – каждый пользователь, не мешая другим, работает с отдельным экземпляром базы данных.
Все вышеназванное приводит к увеличению размера хранилища, что сказывается на производительности InnoDB.
В вопросах надежности MyISAM уступает InnoDB. Так, в MyISAM таблица блокируется полностью, в то время, как в InnoDB таблица блокируется на уровне рядов таблицы. К тому же при правильной расстановке индексов и архитектуре и расстановке индексов снижение скорости InnoDB практически сводится к нулю, а возможно даже ее увеличение.
В отличие от MyISAM, InnoDB ранее не поддерживал индекс FULLTEXT, что приводило к сложной организации полнотекстового поиска. С версии MySQL 5.6.4 индекс был включен в InnoDB.
MyISAM удобно использовать, если таблицы изменяются довольно редко или не изменяются, т.е. когда СУБД используется не для изменения информации, а для чтения. Например, при хранении книг, индексации контента, в справочниках и т.д. в таком случае скорость выборки и размер хранилища может явиться существенным критерием. Целостность данных не находится под угрозой.
Источник: https://spravochnick.ru/bazy_dannyh/yazyk_sql_osnovy_raboty_s_relyacionnymi_subd_osnovy_yazyka_sql/vneshniy_klyuch_i_celostnost_dannyh_bazy_dannyh_innodb_i_myisam/
MySQL: отличия между MyISAM и InnoDB
Самые популярные типы хранение в базе MySQL – это MyISAM и InnoDB.
Неправильный выбор типа хранения приводит к тем же последствиям, что и неправильная структура таблиц, неправильные индексы и неправильные запросы. Другими словами – к падению производительности.
MyISAM
Интересен тем, что дает просто безумную скорость на select-ах и insert-ах.
С другой стороны, он не поддерживает транзакционность и блокировку на уровне строк, что в свою очередь приводит к страшным тормозам при использовании deleteupdate.
Проще говоря, на таблицу допускается только одна одновременная delete или update операция, и остальные вынуждены ждать завершения текущей операции, что на больших объемах данных приводит к серьезным проблемам.
К преимуществам движка можно отнести поддержку полнотекстовый поиск, компрессию и GIS функции. Под хранение каждой таблицы отводятся два файла – имя_таблицы.MYD ( данные ) и имя_таблицы.MYI ( индексы ). Формат данных платформенно независимый, что позволяет переносить данные с сервера на сервер простым копированием таблиц – это еще один плюс.
InnoDB
Был спроектирован для обработки транзакций, в частности для большого количества короткоживущих транзакций, которые чаще комитятся чем откатываются. Это не единственный движок, поддерживающий транзакционность, но на сегодня он является самым популярным для этой цели.
Кроме транзакционности, к преимуществам этого движка можно отнести row-level блокировки, правда тут стоит учесть тот факт, что table-level блокировки тоже имеют место быть, например при использовании автоинкрементных полей. С этим борятся и время от времени выходят фиксы, уменьшающие количество table-level блокировок, но никто не отменял регулярный просмотр логов на предмет проблем с блокировками.
Таблицы по умолчанию хранятся в одном innodb файле, но при желании их можно разместить в разных файлах, причем по одному на таблицу, если указать это явно.
Что может повлиять на выбор тиха хранения данных? Ну во-первых необходимость транзакционности, ибо если пишется серьезный финансовый софт, например тот же биллинг или процессинг, то тут майсам использовать никак нельзя.
Кроме того, специфика работы с данными, накладывает свой отпечаток на выбор движка. Если речь идет о какой-то статистике, которую все просто напросто просматривают, то лучше майсам с этим никто не справится.
Про конкурентность забывать тоже не стоит. Если работа с данными идет всего в несколько потоков, то это вполне может нивелировать недостатки myisam в плане updatedelete в пользу быстрых select.
При выборе движка есть еще ряд нюансов, которые не так важны как главные критерии, но которые тоже стоит учитывать.
| Описание | MyISAM | InnoDB |
| Транзакционный движек?Транзакция (Transaction) — блок операторов SQL , который в случае ошибки в одном запросе, возвращается к предыдущему состоянию (Rollback), и только в случае выполнения всех запросов подтверждается (Commit) | Нет | Да |
| Поддержка внешних ключейВнешние ключи — это способ связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит определенное изменение поля в дочерней (дочернюю и родительскую выбираешь при создании ключа; точнее, создаешь ключ в дочерней, который ссылается на родительскую). | Нет | Да |
| Блокировка.Блокировка на уровне строк, т.е. если процессу нужно обновить строку в таблице, то он блокирует только эту строку, позволяя другим обновлять другие строки параллельно | Блокировка на уровне таблиц | Блокировка на уровне строк |
| Одновременные запросы к разным частям таблицы. | Медленнее | Быстрее |
| При смешанной нагрузке в таблице (select/update/delete/insert) | Медленнее | Быстрее |
| Операция Insert | Быстрее | Медленнее, ибо есть оверхед на транзакцию, но это цена надежности |
| Если преобладают операции чтения (SELECT) | Работает быстрее | Работает медленнее |
| DeadlockDeadlock — ситуация в многозадачной среде или СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими процессами. | Не возникают | Возможны. |
| Поддержка полнотекстового поиска | Да | Нет (доступен начиная с версии MySQL 5.6.4) |
| Запрос Count(*) | Быстрее | Медленнее |
| Поддержка mysqlhotcopyУтилита mysqlhotcopy представляет собой Perl-сценарий, использующий SQL-команды LOCK TABLES, FLUSH TABLES и Unix-утилиты cp или scp для быстрого получения резервной копии базы данных. | Да | Нет |
| Файловое хранение таблиц | Каждой таблице отдельный файл | Данные при настройках по умолчанию хранятся в больших совместно используемых файлах |
| Бинарное копировании таблиц?Табличные файлы можно перемещать между компьютерами разных архитектур и разными операционными системами без всякого преобразования. | Да | Нет |
| Размер таблиц в БД | Меньше | Больше |
| Поведение в случае сбоя | Крашится вся таблица | По логам можно все восстановить |
| В случае хранения «логов» и подобного | Лучше | Хуже |
Выводы:
- Использовать MyISAM лучше в таблицах, которых преобладает один вид доступа: чтение (новостной сайт) или запись (например, логирование) ;
- Использование InnoDB имеет смысл во всех остальных случаях и случаях повышенных требований по сохранности данных.
- Многие жалуются на частые поломки MyISAM. Лично мне ни разу не доводилось с этим сталкиваться, потому ничего не могу сказать по этому поводу. Но надежность данных – это еще один аргумент в пользу innodb, который и крешит реже и восстанавливается быстрее.
- Каждый движок требует свой “кусок пирога”. Мало перекинуть данные в myisam – нужно чтобы сервер был сконфигурирован так, чтобы этому движку было выделено достаточно ресурсов, иначе поимеем те же самые тормоза. Впрочем, сводных данных для отчетов по статистике не обязательно будет много!
- Если данных немного, например той же статистики, то можно использовать тот же движок что и вся остальная база. По крайней мере лишите себя гемора поддержки двух движков.
Источник: https://w1c.ru/sql-mysql_otlichiya_mezhdu_myisam_i_innodb.html
Правильная миграция с MyISAM на InnoDB
Давайте я отвлеку вас от котиков и расскажу, основываясь на своём опыте, какие подводные камни появляются при переходе с MyISAM на InnoDB, и как их избежать. Код приложения будет на PHP.
Этот пост я решил написать, прочитав огромное количество неправильных ответов на запрос из сабжа в интернете.
По всему интернету разбросаны неграмотные или не полные ответы, в результате чего складывается впечатление о том, что смигрировать вашу базу данных на InnoDB — это очень просто. Нет, это не просто! Итак, начнем!
Зачем переходить на InnoDB
С этим вопросом, я думаю, всем всё ясно. Объяснять не буду — преимуществам InnoDB посвящены куча статей в интернете. Если ты читаешь эти строки, то значит ты осознанно пришел к этой мысли о переводе своего хозяйства на InnoDB, и ты, хабраюзер, гуглишь) Надеюсь, эта статья — то, что тебе надо.
Подготовительный этап
1. Из банального — это обеспечить необходимое количество свободного места на диске, где у нас развернута база. InnoDB занимает примерно в 1,5 раза больше места, чем MyISAM. 2. Очень важный момент — он вам пригодится в будущем при траблшутинге перформанс ишшусов в базе.
Нужно прокомментировать каждый SQL запрос в вашем приложении с использованием уникального идентификатора, например, порядкового номера.
Если у вас сотни или тысячи SQL запросов, то как вы жили до сих пор без этого? SELECT /*017*/ client_id, money, lastname FROM clients WHERE money > 100;
Если так сделать, то запросы вида SHOW PROCESSLIST, а также дампы запросов в slow лог файлы будут содержать подсказку для вас — номер SQL запроса, и потом вы мгновенно сможете найти этот запрос в коде и оптимизировать его. 3. Прописываем в конфиг-файле my.cnf:[mysqld]
innodb_file_per_table=1
Этот флаг позволит каждую таблицу хранить в отдельном тэблспейсе (в отдельном файле на диске), чтобы не захламлять системный тэблспейс. 4. Настройка размера кэшей для InnoDB — в том же my.cnf файле:# (уменьшаем это значение, оно для MyISAM и данный вид буфера нам больше не нужен)
key_buffer_size = 8M
# этот размер выставляем в 50-80% от размера всей оперативной памяти у сервера БД.
innodb_buffer_pool_size = 512M
5. Настройка способа работы базы с транзакциямиtransaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=1
binlog-format = MIXED
innodb_log_file_size = 200M
Я на своем приложении выставил уровень изоляции транзакций READ-COMMITTED, вместо выставляющегося по умолчанию REPEATABLE-READ, поскольку в противном случае в базе было бы чрезмерное количество дедлоков. Я для себя решил, что мое приложение может прочитать не самые свежие данные, ценой более быстрой работы, вместо абсолютно актуальных данных, но отягощенных множеством блокировок. Впрочем, для mission-критикал транзакции в коде можно повысить её уровень изоляции — этот эффект будет действовать только на одну транзакцию:mysqli_query($link, «SET TRANSACTION ISOLATION LEVEL SERIALIZABLE»);
Следующий параметр — таймаут, который я специально снизил с 50 до 5 секунд, чтобы он не подвешивал клиентские сессии на очень долго при наличии блокировок.
innodb_rollback_on_timeout очень важен относительно того, как именно ваш код обрабатывает ошибки. С этим моментом я не встречал ясности, поэтому расскажу.
— если этого флага нет, то InnoDB, при наступлении таймаута (Error code 1205) будет откатывать только один этот затаймаутившийся стейтмент в вашей транзакции. То есть, вам нужно будет повторить только его, а не всю транзакцию с начала. Для меня этот вариант показался сложнее в реализации. — если флаг выставлен, то откатывается вся транзакция, точно так же, как это делается при выявлении дедлока (Error code 1213). Я выбрал именно этот вариант, потому что это позволяет сделать код обработки ошибок унифицированным, т.е. повторять транзакцию с первого стейтмента, с начала, при получении любой из этих двух ошибок.
innodb_log_file_size придется увеличить из-за подводного камня №3 (ниже), поскольку этот лог должен быть достаточным для хранения как минимум нескольких записей, а при наличии записей типа MEDIUMTEXT их размер может превысить несколько мб, поэтому дефолтное значение в 5мб крайне мало. После изменения этого параметра базу нужно остановить, старые файлы ib_logfile0 и ib_logfile1 нужно удалить, и только потом поднимать базу.
Чего бояться в InnoDB
Собственно, в InnoDB нужно внимательно смотреть только за этими двумя кодами ошибок: 1205 (таймаут) и 1213 (дедлок), которых не было в MyISAM. При настройке сервера, приведенной выше, он будет сам откатывать ваши транзакции в обоих случаях. Вам надо будет их повторить сначала.
При этом ваш прикладной код может состоять как из отдельных стейтментов — транзакций (при autocommit=1), так и из транзакций, состоящих из нескольких SQL стейтментов — в этом случае транзакция начинается с mysqli_query($link, «START TRANSACTION»); и завершается mysqli_commit($link); (Про mysqli_begin_transaction() знаю, но он только для MySQL >= 5.6, а не везде такие новые MySQL сервера).
Если у вас какой-то вызов mysqli_query() не обернут в for($i=0;$i 2)) {
tologfile(«[$msctime sec.] SUCCESS (from attempt #$attempts)
$query
«);
}
break;
}
$error_code = mysqli_errno($link);
if(($error_code != 1205) && ($error_code != 1213)) {
tologfile(«[$msctime sec.] IGNORING Code: $error_code
$query
«);
break;
}
tologfile(«[$msctime sec.] FOR RETRY; Code: $error_code (attempt $attempts)
$query
«);
}
if(!$result) {
tologfile(«[$msctime sec.] FAILED after $attempts attempts; Code: $error_code
$query
«);
}
return $result;
}
Этот код и повторяет откаченные из-за таймаутов или дедлоков одно-стейтментные транзакции, и также логгирует странности, что мне позволило выловить довольно редкие баги. Также обратите внимание, что код фактически является аналогом конфигурационной опции log_slow_queries, только сделан своими силами и более гибок. Например, я логгирую запросы с длительностью более 2 секунд.
Подводный камень №1
Видел распространенное заблуждение насчет того, как люди обрабатывают ошибки:for ($attempts = 0; $attempts < 5; $attempts++) { $result = mysqli_query($link, $Query); if($result) { # транзакция успешна, выходим из цикла, иначе - повтор break; } } mysqli_commit($link); Вроде бы всё правильно… Но только на первый взгляд. На самом деле, этот код смешной. Например, при синтаксической ошибке в SQL запросе (Error code 1064), или если в столбец не уберутся все данные (Data truncated, Error code 1265) — этот код будет 5 раз повторять очевидно избыточные вещи.
Поэтому вывод — уходим на следующую итерацию цикла только если код ошибки равен 1205 или 1213. В остальных случаях нужно логгировать ошибочный запрос, и разбираться с ним потом. Тут-то вам и пригодятся комменты в теле SQL запроса с его порядковым номером.
Подводный камень №2
Тут всё просто, просто нужно это помнить: код, который вы будете повторять при возникновении ошибок 1205 и 1213, должен быть идемпотентным. То есть, результаты работы этого кода один раз и несколько раз не должны отличаться.
Например, если внутри цикла for вас есть перекладывания результатов запроса в массив: array_push($clients_array, $Row['client_id']);
То в начале этого цикла for должен быть код очистки массива:$clients_array = array(); иначе при повторе транзакции у вас будет уже в два раза больший массив результатов.
Подводный камень №3
А этот подводный камень — просто ахтунг.
Помните, что я, руководствуясь благими намерениями, выставил уровень изоляции транзакций в READ-COMMITTED? Так вот, в этом случае, и если у вас включена репликация, то бинарные логи сервера будут расти как на дрожжах! При данном уровне изоляции транзакций MySQL уже не верит данным, которые вы модифицируете с помощью SQL запросов, поэтому в бинарные логи, и, соответственно, на слейвы передаются логи не в формате STATEMENT, как раньше, а в формате MIXED (binlog-format =MIXED в конфиг-файле, иначе не взлетит!), то есть в данном случае — целиком вся строка, в которой изменен хотя бы один столбец, кидается в лог. Теперь представим, что у нас есть таблица в базе, в которой хранится какой-то большой MEDIUMTEXT, например какие-то логи хранятся в базе, наряду с другими столбцами:CREATE TABLE `processing_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`isfinished` int(11) NOT NULL,
`text` mediumtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
И в коде мы делаемmysqli_query($link, «UPDATE /*041*/ processing_logs set isfinished=1 where id=102»);
В этом случае в mysql-bin.00001 лог будет добавлена вся строка, вместе с неизменившимся text, потому что гладиолус READ-COMMITTED, что приведет к тому, что 100-мегабайтный бинарный лог переполнится буквально через несколько минут работы на продакшен нагрузке. Отсюда вывод — нужно отступать от классической теории реляционных баз данных, и выделять большие по размеру столбцы (MEDIUMTEXT например) в отдельные таблицы, если данные в них меняются реже, чем остальные атрибуты в этой сущности.
Note: это справедливо для MySQL 5.5. В более новой версии базы есть опция binlog-row-image, которую можно выставить в minimal — это должно решить проблему с сильным ростом бинарных логов при каждом апдейте типа показанного выше. Но я не тестировал.
Собственно переход на InnoDB
Переходить на InnoDB мы будем путем создания новой базы данных, в которую будем копировать все таблицы старой БД. Вот этот кусок кода, кстати, делает правильный дамп базы без её остановки, и одновременно узнает master status, что нужно для запуска репликации на слейве.
select now()G
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only=ON;
show master statusG
! mysqldump —add-drop-database -u root -pmypassword myclientsdb > /root/myclientsdb.sql
SET GLOBAL read_only=OFF;
select now()G
exit
В нашем случае нужен просто SQL дамп, который мы будем загонять в новую InnoDB базу.
Чтобы импорт в InnoDB прошел без проблем с превышением размера max_allowed_packet, надо выполнить эти две команды в mysql:set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
Далее создаем новую базу и пользователя в ней:create database newclientsdb;
use newclientsdb;
create user 'newclientsdb'@'localhost' identified by 'passworddb';
grant delete,insert,update,select on newclientsdb.* to 'newclientsdb'@'localhost';
И загоняем всю старую базу в новую. Люблю такую конвейеризацию, где конвертация движка базы делается на лету:cat myclientsdb.sql | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' | mysql -u root -pmypassword newclientsdb
Меняем username/password и имя базы данных в строке коннекта к БД на новую базу:$link = mysqli_connect ($Host, $User, $Password, $DBName);
Тестируем, и если все ОК, то старую MyISAM базу можно дропать.
Вот, вроде бы, и всё.
Источник: https://habr.com/post/269121/
Отличия движков InnoDB от MyISAM в MySQL
InnoDB и MyISAM являются двумя движками с которыми работает MySQL. Таблицы на одном сервере баз данных могут быть как одного типа, так и обоих. Рассмотрим основные отличия InnoDB от MyISAM.
Поскольку некоторые проекты развиваются в течение длительного времени — типы таблиц часто смешиваются и продолжают оставаться в таком виде.
Отличия InnoDB от MyISAM
Главное преимущество InnoDB в скорости работы — при выполнении запроса к базе InnoDB происходит блокировка строки, при выполнении же запроса к базе MyISAM блокируется таблица, это означает, что пока запрос выполнен не будет никакие другие обращения к таблице/строке будут невозможны. Поскольку строки значительно меньше InnoDB обрабатывается быстрее.
InnoDB в отличии от MyISAM поддерживает транзакции, а MyISAM имеет полнотекстовый поиск для всех версий Mysql (для InnoDB такая поддержка есть только для версий старше 5.6.4)
MyISAM таблицы можно без всяких трудностей конвертировать в InnoDB (как и выполнять преобразование в обратном направлении). Это делается при помощи ALTER TABLE или скриптом если таблиц много.
При ковертации стоит иметь в виду, что начиная с версии MySQL 5.6 и эквивалентной ей MariaDB 10 InnoDB является движком по умолчанию. Для ранних версий по умолчанию таблицы создавались в MyISAM.
В настоящее время InnoDB используется значительно чаще, но есть два важных момента:
Недостатки InnoDB:
- InnoDB также сложнее восстанавливать после сбоя в работе сервера, для MyISAM восстановление заключается в применении утилиты myisamchk
- Изначально сами данные как составляющая таблиц хранятся в одном файле ibdata1. Информация из этого файла не удаляется. Т.е. если таблица добавлена, в нее загружен дамп, потом таблица удалена — в ibdata1 содержимое останется и будет накапливаться занимая дисковое пространство. Также хранение данных по всем таблицам в одном файле означает, что с таблицами сложнее работать, нельзя перемещать их и восстанавливать из резервных копий по отдельности.
Второй вопрос решается добавлением в конфигурационный файл директивы innodb_file_per_table = 1; (подробнее о хранении данных InnoDB)
mcedit /etc/mysql/my.cnf
- innodb_file_per_table = 1;
- Отличия InnoDB от MyISAM, таким образом, весьма значительные и какой движок использовать стоит решать в каждом конкретном случае, но почти всегда перевешивают плюсы InnoDB.
- Читайте про то как преобразовать большое количество MyISAM таблиц в InnoDB.
Источник: https://server-gu.ru/innodb-myisam/
MySql: MyISAM против Inno DB!
InnoDB и MyISAM
Сравнение характеристик и производительности:
- InnoDB является более новым, а MyISAM старше.
- InnoDB более сложный, тогда как MyISAM проще.
- InnoDB более строг в целостности данных, в то время как MyISAM свободен.
- InnoDB реализует блокировку на уровне строк для вставки и обновления, в то время как MyISAM реализует блокировку на уровне таблиц.
- В InnoDB есть транзакции, в то время как MyISAM этого не делает.
- У InnoDB есть внешние ключи и отношения, в то время как MyISAM этого не делает.
- У InnoDB есть лучшее восстановление после сбоя, в то время как MyISAM плохо восстанавливает целостность данных при сбоях системы.
- MyISAM имеет полнотекстовый индекс поиска, в то время как InnoDB не имеет.
В свете этих различий InnoDB и MyISAM имеют свои уникальные преимущества и недостатки друг против друга. Каждый из них более подходит в некоторых сценариях, чем другой.
Преимущества InnoDB
- InnoDB следует использовать там, где целостность данных является приоритетом, поскольку она по своей сути заботится о них с помощью ограничений связей и транзакций.
- Быстрее в таблицах с интенсивной записью (вставляет, обновляет), потому что он использует блокировку на уровне строк и только удерживает изменения в той же строке, которая вставлена или обновлена.
Недостатки InnoDB
- Поскольку InnoDB должен заботиться о разных отношениях между таблицами, администратор базы данных и разработчики схем должны уделять больше времени разработке моделей данных, которые более сложны, чем модели MyISAM.
- Потребляет больше системных ресурсов, таких как оперативная память. На самом деле, многие из них рекомендуют отключить движок InnoDB, если они не нуждаются в нем после установки MySQL.
- Нет полнотекстового индексирования.
Преимущества MyISAM
- Проще создавать и создавать, тем лучше для новичков. Не беспокойтесь о внешних отношениях между таблицами.
- Быстрее, чем InnoDB в целом в результате более простой структуры, что значительно снижает затраты на серверные ресурсы.
- Полнотекстовое индексирование.
- Особенно полезно для таблиц с интенсивным чтением (выберите).
Недостатки MyISAM
- Проверка целостности данных (например, ограничений отношений), которая затем несет ответственность и накладные расходы администраторов баз данных и разработчиков приложений.
- Не поддерживает транзакции, которые необходимы для критически важных приложений данных, таких как банкинг.
- Медленнее, чем InnoDB для таблиц, которые часто вставляются или обновляются, потому что вся таблица заблокирована для любой вставки или обновления.
Сравнение довольно простое. InnoDB более подходит для критически важных ситуаций, требующих частых вставок и обновлений. С другой стороны, MyISAM лучше работает с приложениями, которые не полностью зависят от целостности данных и в основном просто выбирают и отображают данные.
- Справка:
Сравнение InnoDB и MyISAM - Вы также можете ознакомиться здесь для получения дополнительной информации:
MyISAM или движок InnoDB MySQL? - Надеюсь, что это поможет.
Источник: https://qarchive.ru/60482_mysql__myisam_protiv_inno_db_
MySQL | Внешние ключи FOREIGN KEY
Последнее обновление: 27.04.2019
Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне таблицы:
[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец1, столбец2, … столбецN)
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, … столбец_главной_таблицыN)
[ON DELETE действие]
[ON UPDATE действие]
Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ.
А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ.
После выражения REFERENCES идут выражения ON DELETE и ON UPDATE, которые задают действие при удалении и обновлении строки из главной таблицы соответственно.
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers
(
Id INT PRIMARY KEY AUTO_INCREMENT,
Age INT,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE Orders
(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);
В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:
CREATE TABLE Orders
(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
CONSTRAINT orders_custonmers_fk
FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:
- CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
- SET NULL: при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL. (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
- RESTRICT: отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
- NO ACTION: то же самое, что и RESTRICT.
- SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.
Каскадное удаление
Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE:
CREATE TABLE Orders
(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);
Подобным образом работает и выражение ON UPDATE CASCADE. При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
Источник: https://metanit.com/sql/mysql/2.5.php
Загрузка...
