Подсистемы (движки) хранения в базе данных MySQL 8

Давайте посмотрим на различные подсистемы хранения базы данных MySQL. Прежде чем перейти к методам запроса данных, очень важно разобраться с ин­формацией этого раздела, поскольку подсистемы хранения данных играют важ­ную роль в методах запроса данных. MySQL хранит данные в базе данных как подкаталог. В каждой базе данных данные хранятся в таблицах, а каждое опреде­ление таблицы хранится в файле с расширением .frm с тем же именем, что и имя таблицы. Предположим, что если мы создадим новую таблицу admin_user, то она будет сохранять всю информацию, связанную с определением таблицы, в файл admin_user.frm.



Мы можем посмотреть информацию, связанную с таблицей, с помощью ко­манды SHOW TABLE STATUS. Попробуем выполнить эту команду для таблицы admin_user и вытащить информацию.

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
  Name: admin_user
  Engine: InnoDB
  Version: 10
  Row_format: Dynamic
  Rows: 2
  Avg_row_length: 8192
  Data_length: 16384
  Max_data_length: 0
  Index_length: 16384
  Data_free: 0
  Auto_increment: 3
  Create_time: 2017-06-19 14:46:49
  Update_time: 2017-06-19 15:15:08
  Check_time: NULL
  Collation: utf8_general_ci
  Checksum: NULL
  Create_options:
  Comment: Admin User Table
1 row in set (0.00 sec)

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

Подсистема хранения данных помогает обрабатывать различные операции SQL для различных типов таблиц. Каждая подсистема хранения имеет свои пре­имущества и недостатки. Выбор подсистемы хранения всегда будет зависеть от потребностей. Важно понимать особенности каждой подсистемы хранения и вы­бирать наиболее подходящую для ваших таблиц, чтобы максимизировать произ­водительность базы данных. В MySQL всякий раз, когда мы создаем новую табли­цу, подсистемой хранения данных по умолчанию является InnoDB.

Мы можем сказать, что сервер MySQL использует архитектуру, работающую в режиме самонастройки (plug and play), потому что мы можем легко загружать и выгружать подсистемы хранения данных с сервера MySQL. Все поддерживае­мые подсистемы хранения можно увидеть с помощью команды SHOW ENGINES. Она предоставит достаточно информации о том, поддерживается ли подсистема хра­нения данных сервером MySQL или нет, какая подсистема хранения данных ис­пользуется сервером MySQL по умолчанию. Давайте выполним эту команду и вы­тащим эту информацию.

mysql> SHOW ENGINES \G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)

InnoDB

В MySQL 8 подсистема хранения данных InnoDB используется по умолчанию и яв­ляется наиболее широко применяемой из всех других доступных подсистем хра­нения. Подсистема InnoDB была выпущена вместе с MySQL 5.1 как плагин в 2008 году, и она рассматривается как подсистема хранения по умолчанию, начиная с версии 5.5 и выше. Поддержка подсистемы хранения InnoDB была перенята кор­порацией Oracle в октябре 2005 года у финской компании Innobase Oy.

Таблицы InnoDB поддерживают ACID-совместимые фиксации транзакций, от­кат и возможности аварийного восстановления для защиты пользовательских данных. InnoDB также поддерживает блокировку на уровне строк, что помогает улучшить параллелизм и производительность. InnoDB хранит данные в кластери­зованных индексах, чтобы уменьшить операции ввода-вывода для всех запросов SQL на выборку данных на основе первичного ключа. InnoDB также поддержи­вает ограничения внешнего ключа, которые обеспечивают лучшую целостность данных в базе данных. Максимальный размер таблицы InnoDB может масшта­бироваться до 256 Тб, что должно быть вполне достаточным во многих случаях использования больших данных.

Важные замечания по InnoDB

Выполнение простого запроса, такого как SELECT count(*) FROM [имя таблицы], без наличия индексов будет очень медленным, поскольку, для того чтобы получить данные, он выполняет полное сканирование таблицы. Если вы хотите часто при­менять запрос на количество данных к таблице InnoDB, предлагается создавать триггеры на операции вставки и удаления, после чего можно увеличивать или уменьшать счетчики, когда записи вставляются или удаляются, что может помочь вам достигнуть лучшей производительности.

Дамп содержимого MySQL, который используется для создания резервной копии, работает с InnoDB слишком медленно. Во время выполнения команды mysqldump можно включить флаги --opt--compress, которые фактически сжимают данные, прежде чем делать дамп со­держимого вашей базы данных/таблицы MySQL.

InnoDB - это подсистема хранения данных с мультиверсионным управле­нием параллелизмом (MVCC), которая хранит информацию о старых версиях измененных строк, чтобы поддержать функционал транзакций и отката, что ока­зывается весьма кстати в целях поддержания целостности данных или в случаях аварийного прекращения работы.

Для оптимизации производительности таблицы InnoDB ниже приведено не­сколько параметров, которые мы можем использовать в настройках my.cnf. Одна­ко они зависят от вашей среды и баз данных.

  • innodb_open_files = 300: определяет максимальное количество файлов, которые она может держать открытыми при работе с режимом innodb_file_per_table.
  • innodb_buffer_pool_size = 128M: задает размер пула в памяти, который может использоваться для кеширования индексов и табличных данных. Это один из важных аспектов настройки таблиц InnoDB. Это значение можно увели­чить в зависимости от размера оперативной памяти на сервере.
  • innodb_thread_concurrency = 8: этот параметр используется для нескольких па­раллельных потоков, которые будут использоваться для обработки запроса и зависят от числа доступных ЦП.

MyISAM

Подсистема хранения данных MyISAM использовалась по умолчанию для MySQL вплоть до версии 5.5 1. В отличие от InnoDB, таблицы подсистемы хранения данных MylSAM не поддерживают ACID-совместитмость. Таблицы MylSAM поддерживают только блокировку уровня таблицы, поэтому таблицы MyISAM небезопасны для транзакций. Таблицы MyISAM оптимизированы для сжатия и скорости. MyISAM обычно используется, когда вам нужно иметь в основном операции чтения с ми­нимальными транзакционными данными. Максимальный размер таблицы My- ISAM может достигать 256 Тб, что помогает в таких случаях, как анализ данных.

Важные примечания относительно таблиц MyISAM

Подсистема хранения данных MyISAM поддерживает полнотекстовое индексиро­вание, которое может помочь в сложных операциях поиска. С помощью полно­текстовых индексов можно индексировать данные, хранящиеся в типах данных BLOB и TEXT. Мы подробно рассмотрим полнотекстовое индексирование в следующих статьях.

Из-за низких накладных расходов MyISAM использует более простую струк­туру, которая обеспечивает хорошую производительность; однако это не сильно помогает для получения хорошей производительности, когда есть потребность в лучшем параллелизме и случаях использования, которые не нуждаются в тяже­лых операциях чтения. Наиболее распространенной проблемой производитель­ности MyISAM является блокировка таблицы, которая может задерживать ваши параллельные запросы в очереди. Это происходит, когда она блокирует таблицу для любой другой операции до тех пор, пока более ранняя операция не будет вы­полнена.

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

Memory

Подсистема хранения в памяти (подсистема оперативного хранения данных) обычно называется подсистемой хранения данных на основе кучи. Она исполь­зуется для чрезвычайно быстрого доступа к данным. Эта подсистема хранения содержит данные в оперативной памяти, поэтому ей не нужны операции ввода- вывода. Поскольку она хранит данные в оперативной памяти, все данные теря­ются при перезапуске сервера. Такая подсистема в основном используется для временных таблиц или таблицы подстановки. Эта подсистема поддерживает бло­кировку на уровне таблицы, которая ограничивает параллелизм с высокой часто­той записи.

Ниже приведены важные примечания об оперативных таблицах Memory.

  • Оперативная таблица хранит данные в оперативной памяти, которая имеет очень ограниченный объем; если вы попытаетесь записать слиш­ком много данных в оперативную таблицу, она начнет свопить данные на диск, и тогда вы потеряете преимущества подсистемы хранения данных в памяти.
  • Оперативные таблицы не поддерживают типы данных TEXT и BLOB; такие типы данных могут не потребоваться, так как таблицы имеют ограничен­ную емкость.
  • Эта подсистема хранения может использоваться для кеширования ре­зультатов; таблицы поиска, например, или почтовые индексы и названия штатов.
  • Оперативные таблицы поддерживают индексы на основе B-дерева и хеш-индексы.

Archive

Эта подсистема хранения данных используется для хранения больших объемов исторических данных без каких-либо индексов. Архивные таблицы не имеют ограничений по объему хранимых данных. Архивная подсистема хранения дан­ных оптимизирована для операций с высокой частотой вставки, а также под­держивает блокировку на уровне строк. Такие таблицы хранят данные в сжатом и малом форматах. Архивная подсистема не поддерживает операции DELETE или UPDATE; она разрешает только операции INSERT, REPLACE и SELECT.

Blackhole

Эта подсистема хранения данных принимает данные, но их не сохраняет. Вместо сохранения данных она отбрасывает (уничтожает) их после каждой вставки.

В следующем ниже примере показана работа таблицы BLACKHOLE:

mysql> CREATE TABLE user(id INT, name CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO USER VALUES(1,'Kandarp'),(2,'Chintan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM USER;
Empty set (0.00 sec)

И какой тогда прок от такой подсистемы хранения данных? Зачем кому-то ее использовать? Зачем запускать запрос INSERT, который ничего в таблицу не встав­ляет?

Эта подсистема хранения полезна для репликации с большим количеством сер­веров. Подсистема хранения данных Blackhole работает в качестве фильтрующе­го сервера между ведущим и ведомым серверами, который не хранит никаких данных, но который применяет только правила replicate-do-* и replicate-ignore-* и пишет двоичные журналы. Эти двоичные журналы используются для выполне­ния репликации на ведомых серверах. Мы обсудим это подробно в главе 6 «Репли­кация для построения высокодоступных решений».

CSV

Подсистема хранения данных CSV хранит данные в файлах в формате .csv, ис­пользуя формат с разделением значений запятыми. Эта подсистема извлека­ет данные из базы данных и копирует их в .csv. Если вы создаете CSV-файл из электронной таблицы и копируете его на сервер папок данных MYSQL, она мо­жет читать данные с помощью запроса SELECT на выборку данных. Аналогичным образом, если вы записываете данные в таблицу, внешняя программа может их прочитать из CSV-файла. Эта подсистема хранения данных используется для об­мена данными между программным обеспечением или приложениями. Таблица CSV не поддерживает индексирование и разделение. Чтобы избежать ошибок при создании таблицы, все столбцы в подсистеме хранения данных CSV должны быть определены с атрибутом NOT NULL.

Merge

Эта подсистема хранения данных также называется подсистемой хранения MRG_Myisam. Эта подсистема хранения объединяет все данные в одну таблицу MyISAM и использует ее для ссылки на единственное представление. В таблицах объединения/слияния все столбцы перечисляются в том же порядке. Эти таблицы хоро­ши для сред объединения баз данных.

В следующем ниже примере показано, как создавать таблицы MERGE:

mysql> CREATE TABLE user1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name
CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE user2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name
CHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO user1 (name) VALUES ('abc'),('xyz');

mysql> INSERT INTO user2 (name) VALUES ('def'),('pqr');

mysql> CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name CHAR(20),
INDEX(id))ENGINE=MERGE UNION=(user1,user2);

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

Таблицы MyISAM имеют ограничение по объему хранения для операционной системы, но коллекция таблиц MyISAM (MERGE) не имеет таких ограничений. Та­ким образом, использование подсистемы MERGE позволит вам разделять данные на многочисленные таблицы MyISAM, что может помочь в преодолении ограни­чений по объему хранения.

С помощью подсистемы MERGE трудно выполнять разделение, следовательно, таблицами MERGE оно не поддерживается, и мы не можем реализовать раздел на таблице MERGE или любой таблице MyISAM.

Federated

Подсистема интегрированного хранения данных FEDERATED позволяет создавать одну базу данных на нескольких физических серверах. Она открывает клиентское соединение с другим сервером и выполняет запросы к таблице, получая и от­правляя строки по мере необходимости. Первоначально она рекламировалась как конкурентная функциональность, которая поддерживала многие корпоративные проприетарные серверы баз данных, такие как Microsoft SQL Server и Oracle, но это всегда было натяжкой, мягко говоря. Хотя казалось, что в ней задействовалось много гибкости и хитрых приемов, она оказалась источником многих проблем и по умолчанию деактивирована. Однако мы можем ее активировать, запустив двоичный файл сервера MySQL с параметром --federated.

Давайте создадим таблицу FEDERATED.

CREATE TABLE user_federated (
   id INT(20) NOT NULL AUTO_INCREMENT,
   name VARCHAR(32) NOT NULL DEFAULT '',
   PRIMARY KEY (id),
   INDEX name (name))
ENGINE=FEDERATED DEFAULT CHARSET=latin1
CONNECTION='mysql://remote_user:[password]@remote_host:port/federated/table
';

 В поле CONNECTION содержится следующая ниже информация для вашей справки:

  • remote_user: имя пользователя удаленного сервера MySQL;
  • password: пароль удаленного сервера MySQL;
  • remote_host: имя хоста удаленного сервера;
  • port: номер порта удаленного сервера;
  • federated: имя базы данных удаленного сервера;
  • table: имя таблицы базы данных удаленного сервера.

NDB Cluster

NDB Cluster (или просто NDB) - это подсистема хранения прямо в оперативной памяти, обеспечивающая высокую доступность и сохраняемость данных.

Кластерная подсистема хранения данных NDB Cluster может конфигуриро­ваться с помощью ряда параметров аварийного переключения и балансиров­ки нагрузки, но проще всего начать с подсистемы хранения на уровне кла­стера. NDB Cluster использует подсистему хранения NDB и содержит полный набор данных, который зависит только от других наборов данных, доступных в кластере.

Кластерная часть NDB Cluster настроена независимо от серверов MySQL. В NDB Cluster каждая часть кластера считается узлом.

Как выбрать движок (подсистему хранения) MySQL?

Следующая ниже схема поможет вам понять, какую подсистему хранения дан­ных вам нужно использовать для ваших потребностей:

 какую подсистему хранения дан­ных использовать в MySQL

Каждая подсистема хранения данных имеет свое преимущество и удобство ис­пользования:

  • поисковый механизм: NDBCluster;
  • транзакционные данные: InnoDB;
  • сеансовые данные: MyISAM или NDBCluster;
  • локализованные вычисления: Memory;
  • словарь: MyISAM.

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

Вас заинтересует / Intresting for you:

Модель развития базы данных My...
Модель развития базы данных My... 914 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03
MySQL: подсистемы хранения сто...
MySQL: подсистемы хранения сто... 814 просмотров Ирина Светлова Sat, 07 Aug 2021, 10:28:39
MySQL: специфические движки дл...
MySQL: специфические движки дл... 1035 просмотров Ирина Светлова Mon, 07 Jan 2019, 12:57:57
Обзор версий MySQL - какой рел...
Обзор версий MySQL - какой рел... 6196 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41
Войдите чтобы комментировать

apv аватар
apv ответил в теме #10108 07 авг 2021 10:39
Великолепный обзор движков СУБД MySQL 8. Большая вам благодарность! + мой лайк!)