Давайте посмотрим на различные подсистемы хранения базы данных 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?
Следующая ниже схема поможет вам понять, какую подсистему хранения данных вам нужно использовать для ваших потребностей:
Каждая подсистема хранения данных имеет свое преимущество и удобство использования:
- поисковый механизм: NDBCluster;
- транзакционные данные: InnoDB;
- сеансовые данные: MyISAM или NDBCluster;
- локализованные вычисления: Memory;
- словарь: MyISAM.
Теперь у вас есть более четкое представление о различных подсистемах хранения данных вместе с различными случаями использования, которые помогут вам выбрать свою подсистему в зависимости от ваших потребностей. Давайте рассмотрим операторы обработки данных, используемые для извлечения, сохранения и обновления данных.
