FAQ MySQL
Краткое описание современных движков хранения данных в MySQL — совместимых СУБД.
Клиенты администрирования MySQL
Команды MySQL
Пример вывода значения переменных MySQL:
DESCRIBE
Команда DESCRIBE предоставляет информацию о столбцах таблицы.
Транзакции и атомарные операции MySQL
Поддержка транзакций в сервере MySQL реализуется при помощи обработчиков транзакционных таблиц типов InnoDB и BDB. Таблицы InnoDB обеспечивают соответствие требованиям ACID.
Однако для таблиц нетранзакционных типов, таких как MyISAM, в MySQL используется иная парадигма обеспечения целостности данных, получившая название атомарные операции. Атомарные операции в сравнении с транзакциями часто обеспечивают такую же или даже лучшую целостность при более высокой производительности. Поскольку сервер MySQL поддерживает обе парадигмы, пользователь может выбирать между скоростью, которую обеспечивают атомарные операции, и транзакционными возможностями для своих приложений. Такой выбор может быть сделан для каждой таблицы отдельно.
Использование подготовленных запросов
Пример использования подготовленных запросов (Prepared Statements) для PHP
Читайте также
Friendhosting — Разумные цены на хостинг
VDS/VPS сервер от 3.49€ в месяц. Много ресурсов. Высокая надежность. Гибкое управление. Удобная оплата. Настройка под вас!
Антидетект браузер Dolphin бесплатно до 10 профилей
Dolphin разработан для работы с такими сложными ресурсов, как Google, Facebook и Coinlist.
Английский для IT‑специалистов по Skype
Персональные занятия по разумным ценам. 80% разговорной практики. Персональный график!
Tech blog by @dizballanze
Здравствуйте, сегодня мы поговорим о типах таблиц в MySQL. Архитектура MySQL позволяет подключать разные движки таблиц. На данный момент MySQL поддерживает множество различных типов таблиц, каждый из которых имеет свои преимущества и недостатки. Я перечислю и коротко опишу основные типы таблиц, а затем проведу небольшой тест производительности наиболее часто используемых типов — myisam и innodb.
Для того что-бы посмотреть какие типы поддерживает ваша инсталляция MySQL необходимо выполнить следующий SQL запрос:
В результате вы получаете таблицу содержащую информацию о том какие типы таблиц установлены в вашей системе и краткое описание их возможностей.
| Engine | Support | Comment | Transactions | XA | Savepoints |
|---|---|---|---|---|---|
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL CSV |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
Нас в основном будет интересовать столбец support, который содержит информацию о поддержке типа таблицы и может принимать значения: NO — не поддерживается, YES — поддерживается, DEFAULT -используется по-умолчанию. Начиная с версии 5.5.5 по-умолчанию выбран тип innodb, ранее стандартным типом был myisam.
В версии MySQL 5.5 поддерживается 9 различных типов таблиц.
- InnoDB — движок с поддержкой транзакций, откатов и защитой от потери данных. В данном типе таблиц используются блокировки на уровне записи и не блокирующее чтение, что позволило улучшить производительность при многопользовательском режиме работы. InnoDB сохраняет пользовательские данные в кластерных индексах, что позволяет компенсировать в/в для простых запросов основанных на первичных ключах.
- MyISAM — движок таблиц MySQL используемый в основном в Web-приложениях, хранилищах данных и других программных средах. Данный тип таблиц поддерживается всеми инсталляциями MySQL.
- Memory — хранит данные в оперативной памяти для очень быстрого доступа. Также известен как HEAP (куча).
- Merge — используется для логического объединения одинаковых MyISAM таблиц и обращение к ним, как к единому объекту. Хорошо подойдет для очень больших хранилищ данных.
- Archive — идеальное решение для хранения больших объёмов информации, к которой не требуется частый доступ.
- Federated — предоставляет возможность объединять различные MySQL сервера для создания одной логической базы данных из нескольких физических машин. Идеально подойдет для архитектур, которые поддерживают распределенное хранение данных.
- CSV — хранит пользовательские данные в текстовых файлах разделяя значения запятыми. Используется если необходим простой обмен с приложениями, которые умеют экспортировать/импортировать данные из CSV формата.
- Blackhole — принимает, но не возвращает никаких данных. Результатами любых запросов из таких хранилищ будут пустые выборки.
- Example — тестовый движок, не выполняет никаких функций, будет полезен только разработчикам, которые собираются писать свой движок, в качестве примера.
Сравнительная таблица основных типов таблиц
| Функция | MyISAM | Memory | InnoDB | Archive |
|---|---|---|---|---|
| Максимальный объём хранимых данных | 256TB | RAM | 64TB | Нет |
| Транзакции | Нет | Нет | Да | Нет |
| Блокировки | Таблица | Таблица | Запись | Запись |
| MVCC | Нет | Нет | Да | Нет |
| B-деревья | Да | Да | Да | Нет |
| Хэш индексы | Нет | Да | Нет | Нет |
| Индексы полнотекстового поиска | Да | Нет | Нет | Нет |
| Кластерные индексы | Нет | Нет | Да | Нет |
| Кэширование данных | Нет | Н/д | Да | Нет |
| Кэширование индексов | Да | Н/д | Да | Нет |
| Сжатие данных | Да | Нет | Да | Да |
| Шифрование данных | Да | Да | Да | Да |
| Поддержка кластерных БД | Нет | Нет | Нет | Нет |
| Репликация | Да | Да | Да | Да |
| Внешние ключи | Нет | Нет | Да | Нет |
| Бэкап | Да | Да | Да | Да |
| Кэширование запросов | Да | Да | Да | Да |
Тестирование производительность InnoDB и MyIASM
Наибольший интерес для web-разработчика составляют innodb и myisam. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:
Напишем небольшой скрипт который будет выполнять 3 теста: запись данных (insert), выборка по ключу, выборка по не ключевому полю.
Для того что-бы выполнить тест, нужно раскоментить один соответствующий блок кода. И собственно, то что у меня получилось в результате тестирования:
| Тест | InnoDB | MyISAM |
|---|---|---|
| Вставка данных(insert) | 15.697 с | 1.591 с |
| Выборка по ключу | 1.678 с | 1.603 с |
| Выборка по не ключевому полю | 149.961 c | 95.984 c |
Как мы видим myisam работает значительно быстрее, особенно это заметно при вставке данных. Хотя innodb и дает ряд новых возможностей и преимуществ, такая медлительность не позволяет ему конкурировать с myisam, особенно в web-приложениях.
What are MySQL database engines? [closed]
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 4 years ago .
I looked around and found some of the MySQL engines are innodb and MyISAM. Perhaps there are few more. My question is what are these database engines?
What are the differences between different MySQL engines? And more importantly, How do I decide which one to use?
4 Answers 4
I personally always use InnoDB if I have to use MySQL. It supports transaction and foreign keys while MyISAM doesn’t.
MyISAM and InnoDB are the most commonly used engines.
MyISAM is slightly faster than InnoDB, and implements the FULLTEXT index which is quite useful for integrating search capabilities. MyISAM is not transacted and doesn’t implement foreign key constraints, which is a major drawback.
But you can use the best of both and create tables with different storage engines. Some software (WordPress, I think) use Inno for most data, like relations between pages, versions etc. Records for the posts contain an ID that links to a record in a separate content table that uses MyISAM. That way, the content is stored in the table that has the best search capabilities, while most other data is stored in tables that enforce data integrity.
If I were you, I’d pick Inno, because it is the most reliable. Only use MyISAM for specific purposes if you need to.
You can configure your database to use InnoDB by default when creating new tables.
1. Что такое ядро базы данных
Ядро базы данных — это программа или программный сегмент, который управляет базой данных и является основной службой для хранения, обработки и защиты данных. Ядро базы данных может использоваться для управления правами доступа и быстрой обработки транзакций, тем самым удовлетворяя требованиям большинства приложений, которым необходимо обрабатывать большие объемы данных на предприятии. Проект приложения базы данных связан с базой данных через ядро базы данных. Что такое ядро базы данных? Короче говоря, ядро базы данных — это программа, которая управляет различными базами данных, и она отвечает за обработку всей основной части работы, связанной с базами данных. Точно так же операционные инструкции проекта приложения базы данных будут обрабатываться ядром базы данных в базе данных.
MySQL — одно из наиболее часто используемых нами программ для баз данных. Он имеет множество преимуществ, таких как открытый исходный код, бесплатный и так далее. На самом деле, у него также есть очень хорошая функция, то есть есть несколько движков, из которых вы можете выбирать. Если гонщики могут легко заменить наиболее подходящий двигатель в соответствии с различными дорожными условиями и рельефом местности, они будут творить чудеса. Однако они не смогли так легко заменить двигатель, а мы можем. Механизм базы данных MySQL зависит от того, как MySQL был скомпилирован при установке. Чтобы добавить новый движок, вы должны перекомпилировать MYSQL. По умолчанию MYSQL поддерживает три движка:ISAM、MYISAMсHEAP. Два других типаINNODBсBERKLEY（BDB）, Может также использоваться часто. Если технология превосходна, вы также можете использовать MySQL + API для самостоятельного создания движка. Вот несколько движков баз данных.
2、ISAM
ISAM — это четко определенный и проверенный временем метод управления таблицами данных, который при разработке учитывает, что количество запросов к базе данных намного превышает количество обновлений. Таким образом, ISAM выполняет операции чтения очень быстро и не занимает много памяти и ресурсов хранения. Два основных недостатка ISAM заключаются в том, что он не поддерживает обработку транзакций и не является отказоустойчивым: если ваш жесткий диск выходит из строя, файлы данных не могут быть восстановлены. Если вы используете ISAM в критически важных приложениях, вы всегда должны выполнять резервное копирование всех ваших данных в реальном времени.С помощью функции репликации MYSQL может поддерживать такие приложения резервного копирования.
3、MyISAM
MyISAM — это расширенный формат MySQL ISAM и ядро базы данных по умолчанию. Помимо предоставления большого количества функций для управления индексами и полями, которые недоступны в ISAM, MyISAM также использует механизм блокировки таблиц для оптимизации нескольких одновременных операций чтения и записи. Стоимость состоит в том, что вам нужно часто запускать команду OPTIMIZE TABLE, чтобы восстановить обновляемое Пространство, потраченное зря механизмом. MyISAM также имеет несколько полезных расширений, таких как инструмент MyISAMCHK для восстановления файлов базы данных и инструмент MyISAMPACK для восстановления потерянного пространства. MYISAM делает упор на быстрые операции чтения, что может быть основной причиной того, почему MySQL так предпочитается веб-разработкой: большое количество операций с данными, которые вы выполняете при веб-разработке, являются операциями чтения. Поэтому большинство провайдеров виртуального хостинга и провайдеров интернет-платформы разрешают использование только формата MYISAM. Важный недостаток формата MyISAM состоит в том, что данные не могут быть восстановлены после повреждения таблицы.
MyIASM — это движок MySQL по умолчанию, но онНе поддерживает транзакции базы данных и не поддерживает блокировки на уровне строк и внешние ключи., Поэтому, когда данные INSERT (вставка) или UPDATE (обновление), то есть операции записи должны блокировать всю таблицу, эффективность будет ниже. Однако, в отличие от Innodb, MyIASM хранит количество строк в таблице, поэтому при выборе SELECT COUNT (*) FROM TABLE вам нужно только напрямую прочитать сохраненные значения без выполнения полного сканирования таблицы.Если в таблице больше операций чтения, чем операций записи, и для нее не требуется поддержка транзакций базы данных, тогда MyIASM также является хорошим выбором.
Механизм MyISAM использует B + Tree в качестве структуры индекса, а поле данных конечного узла хранит адрес записи данных. Метод индекса MyISAM также называется"Неагрегированные", Причина, по которой это называется, состоит в том, чтобы общаться с InnoDBКластерный индексотличать. На следующем рисунке представлена схематическая диаграмма индекса первичного ключа MyISAM:
4、InnoDB
Движок Innodb обеспечиваетПоддержка транзакций ACID, И внедрить стандарт SQLЧетыре уровня изоляции. Двигатель также обеспечиваетБлокировки на уровне строк и ограничения внешнего ключа, Его целью проектирования является обработка систем баз данных большой емкости. Сама по себе это полная система баз данных, основанная на серверной части MySQL. Когда MySQL работает, Innodb создает буферный пул в памяти для буферизации данных и индексов. Но движок не поддерживает индексы типа FULLTEXT и не сохраняет количество строк в таблице.Когда SELECT COUNT (*) FROM TABLE, вам нужно просканировать всю таблицу. Этот механизм, конечно, является первым выбором, когда вам нужно использовать транзакции базы данных. Поскольку степень детализации блокировки меньше, операция записи не будет блокировать всю таблицу, поэтому при высоком уровне параллелизма использование механизма Innodb повысит эффективность. Однако использование блокировок на уровне строк не является абсолютным.Если MySQL не может определить диапазон для сканирования при выполнении оператора SQL, таблица InnoDB также заблокирует всю таблицу.
Структура индекса механизма Innodb также является B + Tree, но индексный файл самого Innodb является файлом данных, то есть поле данных B + Tree хранит фактические данные, и этот индексКластерный индекс. Ключ этого индекса является первичным ключом таблицы данных, поэтому сам файл данных таблицы InnoDB является первичным индексом. Поскольку сам файл данных InnoDB должен быть агрегирован по первичному ключу, InnoDB требует, чтобы таблица имела первичный ключ (MyISAM может не иметь). Если он не указан явно, система MySQL автоматически выберет столбец, который может однозначно идентифицировать запись данных как первичный ключ. Если он не существует Для этого типа столбца MySQL автоматически создает скрытое поле для таблицы InnoDB в качестве первичного ключа.Длина поля составляет 6 байтов, а тип — длинное целое число. И в отличие от MyISAM,Поле данных вторичного индекса InnoDB также хранит значение первичного ключа соответствующей записи вместо адреса, поэтому при поиске по вторичному индексу первичный ключ будет найден сначала согласно вторичному индексу, а затем фактические данные будут найдены согласно индексу первичного ключа.Поэтому Innodb не рекомендует использовать слишком длинный первичный ключ, иначе вторичный индекс станет слишком большим. Рекомендуется использовать автоматически увеличивающееся поле в качестве первичного ключа, чтобы каждый узел дерева B + заполнялся по порядку без частых корректировок разделения, что эффективно повысит эффективность вставки данных. Индекс первичного ключа и вспомогательный индекс Innodb показаны на следующем рисунке:
Таблица InnoDB основана наКластерный индексУстановлено. Следовательно, индекс InnoDB может обеспечить очень быстрое выполнение поиска по первичному ключу. Однако его вторичный индекс (вторичный индекс, то есть индекс непервичного ключа) также будет содержать столбец первичного ключа, поэтому, если определение первичного ключа относительно велико, другие индексы также будут большими. Если вы хотите определить много индексов в таблице, попробуйте определить первичный ключ как можно меньше. InnoDB не сжимает индексы. Чтобы
5. Разница между MyISAM и InnoDB
InnoDB и MyISAM — два наиболее часто используемых типа таблиц, когда многие люди используют MySQL.Эти два типа таблиц имеют свои преимущества и недостатки в зависимости от конкретного приложения. Основные отличия:Тип MyISAM не поддерживает расширенную обработку, такую как обработка транзакций, в то время как тип InnoDB поддерживает.Таблица типов MyISAM подчеркивает производительность, и ее выполнение происходит быстрее, чем тип InnoDB, но она не обеспечивает поддержку транзакций, в то время как InnoDB обеспечивает поддержку транзакций и расширенные функции базы данных, такие как внешние ключи. Ниже приведены некоторые детали и конкретные различия в реализации:
(1) InnoDB не поддерживает индексы типа FULLTEXT;
(2) InnoDB не сохраняет определенное количество строк в таблице, то есть при выполнении select count (*) fromtable InnoDB должен сканировать всю таблицу, чтобы вычислить, сколько там строк, но MyISAM Просто прочтите количество сохраненных строк. Обратите внимание, что когда оператор count (*) содержит условие where, операции двух таблиц одинаковы;
(3) Для полей типа AUTO_INCREMENT InnoDB должен содержать индекс только с этим полем, но в таблицах MyISAM можно создать объединенный индекс с другими полями.
(4) При УДАЛЕНИИ ИЗ таблицы InnoDB не воссоздает таблицу заново, а удаляет строку за строкой;
(5) Операция LOAD TABLE FROMMASTER не работает для InnoDB. Решение состоит в том, чтобы сначала изменить таблицу InnoDB на таблицу MyISAM, а затем изменить ее на таблицу InnoDB после импорта данных, но для используемых Таблицы с дополнительными функциями InnoDB (такими как внешние ключи) не применимы.
(5) Разница между основным индексом, сам файл данных InnoDB является индексным файлом, а индекс MyISAM и данные разделены.
(6) Разница во вспомогательном индексе: поле данных вспомогательного индекса InnoDB хранит значение первичного ключа соответствующей записи вместо адреса. И вторичный индекс MyISAM мало чем отличается от основного индекса.
MyISAM подходит для:
(1) Проведите много подсчетов;
(2) Вставки выполняются не часто, а запросы выполняются очень часто;
(3) Нет транзакции.
InnoDB подходит для:
(1) Требования к надежности относительно высоки, или требуются дела;
(2) Обновления таблиц и запросы происходят довольно часто, а вероятность блокировки таблицы относительно велика, укажите создание механизма данных;
(3) Наборы данных большого размера обычно выбирают движок InnoDB, потому что он поддерживает обработку транзакций и восстановление после сбоев. Размер базы данных определяет продолжительность восстановления после сбоя.InnoDB может использовать журналы транзакций для восстановления данных, что будет быстрее.