MySQL

MySQL - это реляционная система управления базами данных (СУБД), которая была создана и разработана шведско-финской компанией MySQL AB, основанной Майклом Виденмейером, Давидом Акерстрендом и Алленом Ларссоном. Первая версия MySQL была выпущена в 1995 году.

В процессе разработки MySQL появилось несколько ответвлений кода, известных как "форки". Форки представляют собой независимые версии программного обеспечения, созданные на основе исходного кода MySQL, но с определенными изменениями или добавлениями. Вот несколько примеров известных форков MySQL:

• MariaDB:

  • Происхождение: MariaDB была создана после приобретения Sun Microsystems компанией Oracle. Одним из создателей MariaDB является Майкл Виденмейер, один из оригинальных создателей MySQL.
  • Цель: MariaDB была создана с целью предоставить альтернативу MySQL и продолжить развитие СУБД с уклоном в сторону открытого исходного кода.
  • Особенности: MariaDB включает дополнительные функции, улучшенную производительность и акцент на безопасность данных.

• Percona Server:

  • Происхождение: Percona Server - это другой форк MySQL, созданный компанией Percona, специализирующейся на решениях для управления базами данных.
  • Цель: Percona Server предназначен для улучшения производительности и масштабируемости MySQL, а также предоставляет дополнительные инструменты и функции для администрирования баз данных.
  • Особенности: Percona Server включает в себя оптимизации для работы с большими объемами данных и высокой нагрузкой.

• Drizzle:

  • Происхождение: Drizzle - это более радикальный форк MySQL, разработанный для легковесности и оптимизации производительности.
  • Цель: Drizzle был создан для простоты и масштабируемости, убрав множество функций, которые могли считаться избыточными или сложными в использовании.
  • Особенности: Drizzle имеет уменьшенный объем кода, более простую архитектуру и ориентирован на работу в распределенных средах.

Архитектура MySQL

Архитектура MySQL представляет собой многоуровневую клиент-серверную модель. Она разделена на три основных логических уровня: уровень клиентов, уровень сервера (обработки SQL) и уровень подключаемых механизмов хранения данных (storage engines).

Основные уровни архитектуры MySQL

• Client Layer
  • Этот уровень включает в себя приложения и инструменты (например, MySQL Workbench, командная строка, веб-приложения), которые взаимодействуют с сервером MySQL.
  • Клиенты отправляют SQL-запросы на сервер по сети (используя протокол TCP/IP) или через сокеты UNIX.
  • Для подключения используются специальные библиотеки, называемые коннекторами (Connectors/APIs), которые поддерживают различные языки программирования (Java, PHP, Python и т.д.).
• MySQL Server Layer. Ключевые компоненты этого уровня:
  • Управление соединениями (Connection Management): Обрабатывает клиентские подключения, аутентификацию, безопасность и управляет пулом потоков для каждого клиента.
  • Парсер (Parser) и Анализатор (Analyzer): Разбирает входящие SQL-запросы на составные части, проверяет синтаксис и корректность запроса.
  • Оптимизатор запросов (Query Optimizer): Определяет наиболее эффективный способ выполнения запроса, выбирая оптимальный план выполнения и используя индексы для минимизации времени доступа к данным.
  • Кэш запросов (Query Cache): (В последних версиях MySQL этот компонент был удален или заменен другими механизмами) Раньше использовался для хранения результатов часто выполняемых запросов для быстрого ответа без повторной обработки.
  • Встроенные функции (Built-in Functions): Предоставляет функциональность для обработки даты, времени, математических операций и т.д..
• Storage Engines Layer Это подключаемый уровень, который отвечает непосредственно за физическое хранение и извлечение данных. MySQL отличается тем, что позволяет выбирать различные механизмы хранения для разных таблиц в зависимости от конкретных требований к производительности и функциям.

В MySQL каждое клиентское соединение обрабатывается в рамках отдельного потока внутри серверного процесса. Это называется многопоточностью (multithreading) и представляет собой архитектурный подход, позволяющий обслуживать несколько клиентов одновременно, управляя каждым соединением в отдельном потоке. Сервер хранит потоки в кеше, их не нужно создавать или уничтожать для каждого соединения.

Преимущества многопоточности в MySQL:

• Эффективность ресурсов: Потоки предоставляют легковесный способ обслуживания множества клиентов с минимальным использованием ресурсов.
• Параллелизм: Многопоточность обеспечивает параллельное выполнение запросов, что может улучшить производительность и уменьшить временные задержки для пользователей.
• Отказоустойчивость: Использование отдельных потоков для каждого соединения обеспечивает изоляцию между клиентами, так что сбой в одном потоке не влияет на остальные.
• Масштабируемость: При увеличении числа клиентов сервер может создавать и управлять дополнительными потоками, что позволяет легко масштабировать систему.

Однако, следует отметить, что многопоточность требует правильного управления синхронизацией и обеспечения доступа к данным безопасным образом. В MySQL это часто реализуется с использованием механизмов блокировок и семафоров.

Движки MySQL

MySQL также предоставляет опцию использования различных хранилищ данных (storage engines), каждое из которых может использовать свой собственный подход к обработке многопоточности. Например, InnoDB, одно из популярных хранилищ данных MySQL, предоставляет механизм транзакций и управления блокировками для обеспечения целостности данных в многопоточной среде.

В контексте MySQL, подсистемы хранения данных называются "движками" (storage engines) или "хранилищами" (storage providers). Движок MySQL определяет, как данные будут храниться, организовываться и обрабатываться внутри базы данных. Каждый движок имеет свои особенности, преимущества и недостатки, позволяя пользователям выбирать подходящий для их конкретных требований.

Некоторые из наиболее известных и широко используемых движков MySQL:

• InnoDB:

  • Транзакции: Поддерживает транзакции для обеспечения целостности данных.
  • Блокировки: Использует механизмы блокировок для управления одновременным доступом к данным.
  • Ссылочная целостность: Поддерживает внешние ключи и обеспечивает ссылочную целостность данных.

• MyISAM:

  • Производительность: Обеспечивает высокую производительность для операций чтения.
  • Отсутствие транзакций: Не поддерживает транзакции и не обеспечивает такой уровень целостности данных, как InnoDB.
  • Полнотекстовый поиск: Имеет встроенную поддержку полнотекстового поиска.

• MEMORY (HEAP):

  • В памяти: Хранит все данные в памяти, что обеспечивает высокую скорость доступа.
  • Временные таблицы: Часто используется для создания временных таблиц или таблиц, содержащих данные, которые могут быть восстановлены после перезапуска сервера.

• TokuDB:

  • Сжатие данных: Использует эффективные методы сжатия данных, что может уменьшить объем хранимой информации.
  • Высокая производительность: Предназначен для работы с большими объемами данных и обеспечивает высокую производительность.

• ARCHIVE:

  • Сжатие данных: Оптимизирован для хранения архивных данных с использованием методов сжатия.
  • Низкая нагрузка: Эффективно работает при низкой активности и предназначен для хранения данных с низкой частотой обновления.

Выбор движка зависит от конкретных требований проекта, типа данных, требований к производительности, а также потребности в функциональности, такой как поддержка транзакций и ссылочной целостности. MySQL предоставляет возможность использовать различные движки в одной базе данных, что называется "смешанным движком" (mixed storage engine).

Транзакции в MySQL

Транзакция - это группа запросов SQL, обрабатываемых атомарно, как единое целое. Если БД не может выполнить какой-то запрос из группы, то ни один из запросов не будет выполнен. При использовании транзакций - на изменяемые данные в таблице накладывается блокировка, запрещающая двум транзакциям изменять данные в одной строке.

Очень важно следить за скоростью проходящих транзакций. С долгими транзакциями необходимо бороться, поскольку они очень сильно вредять производительности СУБД. Транзакции создают множество блокировок в БД, замедляют процес репликации.

Блокировки

MySQL поддерживает различные типы блокировок, и часть из них зависит от используемого движка (storage engine):

• READ (SHARED) LOCK. Это шаред-блокировка, которая предоставляет транзакции право на чтение данных. Другие транзакции также могут удерживать шаред-блокировки на тех же данных для чтения.

• WRITE (EXCLUSIVE) LOCK. Это эксклюзивная блокировка, предоставляющая транзакции право на запись или изменение данных. Другие транзакции не могут удерживать шаред-блокировки или эксклюзивные блокировки на эти данные.

• READ COMMITTED. Это уровень изоляции транзакций, при котором транзакция видит только те данные, которые были подтверждены (committed) другими транзакциями. Этот уровень изоляции может привести к "фантомным" чтениям (phantom reads), но предотвращает чтение несуществующих данных.

• REPEATABLE READ. Это уровень изоляции транзакций, при котором транзакция видит только те данные, которые были на момент ее начала. Этот уровень изоляции предотвращает "фантомные" чтения, но может привести к блокировкам.

• SERIALIZABLE. Это наивысший уровень изоляции транзакций, при котором транзакция видит данные в одном и том же состоянии на протяжении всей ее длительности. Этот уровень изоляции предотвращает "фантомные" чтения и гарантирует полную изоляцию, но может привести к серьезным блокировкам.

Операции LOCK TABLES и UNLOCK TABLES

Команды LOCK TABLES и UNLOCK TABLES используются для управления блокировками на уровне таблиц. Они могут быть использованы для ручного управления блокировками.

SQL

--- Чтобы узнать текущий уровень изоляции, используется следующая команда:
SHOW VARIABLES LIKE '%transaction_isolation%';

--- Для установки уровня изоляции транзакций используется следующая команда:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level;
--- GLOBAL - во всех последующих сессиях,
---SESSION - ко всем последующим транзакциям, выполняемых в текущей сессии.
---level - это уровень применяемой транзакции.

Конфигурирование сервера MySQL

Производительность MySQL обеспечивается выбором “правильного движка” и тонкой настройкой сервера БД.

В общем виде настройка сервера заключается в изменении параметров конфигурационного файла my.cnf

SQL

-- Для того чтобы посмотреть текущие значения переменным, можно использовать конструкцию типа:
SHOW GLOBAL VARIABLES
   LIKE 'max_connections';

innodb_buffer_pool_size

Размер буферного пула InnoDB, который определяет объем памяти, выделенный для кэширования данных и индексов. Обычно устанавливается в 50-75% от доступной оперативной памяти.

innodb_log_file_size

Размер файла-лога операций. Данный файл требуется для восстановления работоспособности сервера БД после сбоя. Файлов логов всегда 2, таким образом - занятое место на диске будет: total_disk_space = innodb_log_file_size * 2

Чем больше выделено пространства для данного файла, тем быстрее будут производится io операции, но тем медленнее будет восстанавливаться сервер БД.

innodb_log_buffer_size

Размер буффера, в который помещаются транзакции в незакомиченном состоянии. После коммита транзакции из буффера попадают в log_file. В большинстве случаев достаточно эту величину выставлять 1 Mb.

innodb_file_per_table

По умолчанию InnoDB сохраняет все таблицы в один файл. При включении данной опции - таблицы хранятся по разным файлам. Включение данного параметра требуется в случаях необходимости:

• освобождения места на диске при удалении таблиц (общий файл может только увеличиваться)
• компрессии таблиц для экономии места на диске

innodb_flush_method

Данный параметр определяет логику сброса данных на диск. На текущий момент оптимальные, возможные для установки, значения данной настройки:

• O_DIRECT - обеспечивает большую надежность процесса записи.
• O_DSYNC - работает быстрее

innodb_flush_log_at_trx_commit

Данный параметр определяет поведение сброса операций в лог файл на диск.

• innodb_flush_log_at_trx_commit = 1 Сохранность данных важнее скорости IO
• innodb_flush_log_at_trx_commit = 2 Скорость IO важнее сохранности данных

query_cache_size

Данный параметр определяет объем памяти, выделенный под кеш запросов. Является неэффективным и чаще его выставляют 0. Неправильно выставленные параметр может замедлить сервер БД.

max_connections

Данный параметр определяет количество одновременных соединений с сервером БД. Изменять его следует только в том случае, если вы уверены в нехватке текущего значения. Например в логах сервера БД видите ошибку“Too many connections”.

slow_query_log

Для активации журнала медленных запросов MySQL нужно запустить команды, которые определят системные переменные:

slow_query_log

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 2;

Slow Query Log Overview

wait_timeout

Этот параметр определяет максимальное время ожидания (в секундах), в течение которого неактивное соединение останется открытым. Если клиент не отправляет запросы в течение этого времени, сервер разрывает соединение. Этот параметр применяется ко всем типам соединений, включая неинтерактивные и интерактивные.

interactive_timeout

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

skip-name-resolve

Отключение поиска DNS-имени для адресов, с которых осуществляются подключения к БД. Подключаться можно только с ip-адресов. У всех пользователей следует заменить хостнеймы на ip-адреса, напр: root@127.0.0.1 Ускоряет работу БД

max_connections

MariaDB/MySQL сервер будет ограничивать количество одновременных соединений. Это позволит оптимизировать использование ресурсов сервера и предотвратить исчерпание доступных соединений.

query_cache_size

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

Безопасность

Безопасность MySQL основана на использовании ACL (access control list) для всех пользовательских операций.

Управление доступом сервером MySQL осуществляется в два шага:

1. Сервер принимает или отклоняет соединение, основываясь на Вашей личности и том, может ли проверить Вашу личность, поставляя правильный пароль.
2. Предположив, что вы можете соединиться, сервер проверяет каждый запрос, который Вы делаете, чтобы определить, есть ли у Вас достаточные привилегии, чтобы выполнить это.

Систему привилегий MySQL можно разделить на 3 типа:

1. Административные привилегии. Позволяют пользователям управлять работой сервера MySQL
2. Привилегии базы данных. Относятся к базе данных и всем объектам в ее пределах.
3. Привилегии для объектов базы данных. Для конкретных целей в пределах БД, для всех объектов данного типа в пределах БД или глобально для всех объектов данного типа во всех БД.

Бэкап и восстановление

Файлы бэкапов в MySQL могут быть использованы:

1. В качестве резервной копии для восстановления данных
2. Как источник данных для настройки реплик
3. В качестве источника данных для экспериментов:
   • Сделать копию базы данных, которую можно использовать без изменения исходных данных.
   • Чтобы проверить возможные несовместимости обновлений

В MySQL для создания бэкапов используется утилита mysqldump. Эта утилита позволяет вам создавать текстовые файлы, содержащие SQL-запросы, которые могут быть использованы для восстановления базы данных в будущем.

mysqldump

mysqldump -u [username] -p[password] [database_name] > dump.sql

# Бэкап всех БД на сервере
mysqldump --all-databases > dump.sql

# Бэкап выбранных БД
mysql --databases db1 db2 > dump.sql

Если вы хотите восстановить базу данных из дампа, вы можете использовать команду mysql

Bash

mysql -u [username] -p[password] [database_name] < dump.sql

# Для восстановления изменений над БД после бэкапа - можно воспользоваться записями из бинарных лог-файлов:
mysqlbinlog /path/to/binary/log/file | mysql -u [username] -p[password]

Масштабирование

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

Масштабирование делится на:

• шардирование - разделение таблиц на куски по какому-либо принципу.
• репликацию - дублирование данных на разных экземплярах СУБД и формирование правил выполнения io операций.

Каждый из видов масштабирования также имеет деление на подтипы. Эффективная работа с БД достигается путем правильного комбинирования типов шардирования и репликации.

Шардирование можно разделить на:

• вертикальное - разделение таблиц на куски по какому-либо условию в рамках одного экземпляра СУБД.
• горизонтальное - разделение таблиц на куски по какому-либо условию в рамках нескольких экземпляров СУБД.

В MySQL шардирование может быть настроено через AutoSharding, либо выполнено на стороне клиентского приложения.

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

• master-slave репликация обеспечивает передачу данных на запись с ведущего узла (master) на ведомые узлы (slave).

  Ведомые узлы работают в режиме “только для чтения”. При остановке ведущего узла - все запросы на модификацию данных не будут выполняться

• multi-master репликация похожа на master-slave репликацию, за исключением наличия нескольких ведущих узлов.

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

• two masters, many slaves репликация представляет цепочку из 2х master серверов, которые имеют равные количества slave серверов.

  Оба master сервера выполняют запросы на модификацию данных. При выходе из строя одного master сервера, приложение продолжит работу со вторым.

Режим репликации (синхрон/асинхрон) может изменяться, в зависимости от версии MySQL. Также возможна репликация на дисках и лог-файлах.

Как устроена репликация в MySQL

Репликация в MySQL состоит из трех основных шагов:

1. Master-сервер записывает изменения данных (события) в двоичный журнал (binary log).
2. Slave копирует изменения двоичного журнала в свой, который называется журналом ретрансляции (relay log).
3. Slave воспроизводит изменения из журнала ретрансляции, применяя их к своим данным.

Существует два принципиально разных подхода к репликации: покомандная и построчная. В первом случае в журнал мастера протоколируются запросы изменения данных (INSERT, UPDATE, DELETE), а slave в точности воспроизводит команды у себя. При построчной же репликации в журнале окажутся непосредственно изменения строк в таблицах, и эти же фактические изменения применятся затем на слейве.

В MySQL поддерживаются оба способа репликации, а дефолтный изменялся в зависимости от версии. В современных версиях, например MySQL 8, по умолчанию используется построчная репликация.

Второй принцип разделения подходов к репликации — количество master-серверов. Наличие одного master-сервера подразумевает, что только он принимает изменения данных и является образцом, с которого далее распространяются изменения на прочие slave. Так, например, можно давать удаленным клиентам одинаково быструю возможность вносить изменения в базу.