Движок БД MySQL — что это такое?

База данных — это обертка вокруг движка хранения данных. Она занимается приемом запросов и управлением ими, кэшированием и прочими обслуживающими функциями, обеспечивая работу с низкоуровневым API движка. Последний, в свою очередь, собственно и хранит данные (на диске или в памяти), работает с операционной системой и обеспечивает выдачу нужных выборок по запросу от сервера. Если раньше СУБД (связка “сервер + движок”) была монолитная, то теперь во всех системах используется структура с плагинами. Движок в такой организации является просто модулем, а сам сервер не зависит от системы хранения данных. В последних редакциях классического MySQL также используется плагинная архитектура. Поэтому встроенный движок InnoDB (правда, обычно устаревшей версии) можно легко заменить на модуль другого проекта, который часто будет лучше. В альтернативных мускулу разработках, в том числе MariaDB или Drizzle, все движки изначально выполнены как плагины.

  • Вывести информацию о движках, которые поддерживает ваш сервер MySQL. Обратите внимание на столбец Support, если указано значение “NO” тип хранения данных не поддерживается (не включен).
# mysql -uroot -p
Server version: 5.5.29-0ubuntu0.12.04.2 (Ubuntu)

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | 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         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| 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         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

MySQL InnoDB, MyISAM etc.

Краткое описание современных движков хранения данных в MySQL- совместимых СУБД система управления базами данных.

  • InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table (Как включить MySQL innodb_file_per_table?).

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

  • MyISAM — не поддерживает транзакции и внешние связи (foreign key), но зато может похвастаться полнотекстовыми индексами и быстротой вставки данных. На Select запросах MyISAM медленнее, чем InnoDB. Долгое время был стандартным для всех версий MySQL, а потому до сих пор является самым популярным.
  • MERGE — MyISAM движок для разнесения данных в одной таблице на несколько разных.
  • CVS — специализированный движок на случай, когда требуется хранить и обрабатывать большие массивы строковых данных, разделяемых запятой. Данные можно править обычным текстовым редактором.
  • MEMORY — движок, использующийся для хранения данных не на диске, а в памяти. Информация из базы доступна только во время работы сервера, но это дает колоссальный прирост в производительности.
  • Federated/FederatedX — этот движок специализируется на прозрачном разнесении данных по нескольким серверам (физическим) на уровне таблицы.
  • PBXT — призванный заменить InnoDB новый движок, в котором реализованы полная поддержка транзакций, многоверсионность, автоматическая обработка дедлоков. Движок оптимизирован для большого количества одновременных транзакций.
  • Blackhole — служебный движок, представляющий собой, по сути, /dev/null для СУБД и фактически не производящий никаких записей на диск. Используется для репликации.
  • Archive — используется в тех случаях, когда необходимо хранить большие массивы данных без изменений. Для эффективности хранения используется сжатие, что приводит к медлительности во время выборок. Движок хорошо подходит для долговременного хранения логов и другой служебной информации.
  • XtraDB — расширенная и исправленная в некоторых проблемных местах InnoDB от компании Percona.
  • BlitzDB — еще одна замена для MyISAM с хорошей производительностью за счет встроенного построчного кэширования и автоматического восстановления после сбоев. Движок не поддерживает транзакции.
  • NDB — движок для кластера, обладающий, впрочем, кучей проблем и удручающе плохой производительностью.
  • Falcon — легендарный движок от компании MySQL AB, разрабатываемый еще со времен Sun, когда было принято решение заменить оракловский InnoDB.
  • SphinxSE — полнотекстовый движок от создателя поискового сервера Sphinx. Лучший вариант для полнотекстового поиска и индексации по правилам русского языка. Легко оперирует терабайтами данных, обеспечивая при этом все возможности современной БД.
  • Aria — замена для MyISAM с поддержкой транзакций и улучшенной работой с памятью. Движок гарантирует целостность данных и при этом не уступает в скорости MyISAM.
  • BDB(BerkeleyDB) - для совместимости с BerkeleyDB.

Клиенты администрирования MySQL

aptitude install emma

Команды MySQL

SHOW

  • Синтаксис команды SHOW

  • SHOW DATABASES; - список баз данных

  • SHOW TABLES [FROM db_name]; - список таблиц в базе
  • SHOW COLUMNS FROM таблица [FROM db_name]; - список столбцов в таблице
  • SHOW CREATE TABLE table_name; - показать структуру таблицы в формате “CREATE TABLE”
  • SHOW INDEX FROM tbl_name; - список индексов
  • SHOW GRANTS FOR user [FROM db_name]; - привилегии для пользователя.
  • SHOW VARIABLES; - значения системных переменных
  • SHOW FULL PROCESSLIST;
  • SHOW PROCESSLIST; - статистика по mysqld процессам. Удалить процесс: Синтаксис команды KILL
  • SHOW STATUS; - общая статистика
  • SHOW TABLE STATUS [FROM db_name]; - статистика по всем таблицам в базе

DESCRIBE

Команда DESCRIBE предоставляет информацию о столбцах таблицы.

  • DESCRIBE таблица; сокращенный вариант команды SHOW COLUMNS FROM

Транзакции и атомарные операции MySQL

Поддержка транзакций в сервере MySQL реализуется при помощи обработчиков транзакционных таблиц типов InnoDB и BDB. Таблицы InnoDB обеспечивают соответствие требованиям ACID.

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

Использование подготовленных запросов

Пример использования подготовленных запросов (Prepared Statements) для PHP

mysql_query("PREPARE myinsert FROM
'INSERT INTO
mytable (name, price)
VALUES (?, ?)'");
for ($i = 0; $i < 1000; $i++){
mysql_query("SET @name = 'Товар # $i'");
mysql_query("SET @price = " . ($i * 10));
mysql_query("EXECUTE myinsert USING @name, @price");
}

wiki.dieg.info