MySQL Study Notes — 1 — Базовая архитектура и двухфазная фиксация журналов

Базовая архитектура MySQL

MySQL можно условно разделить на уровень сервера и уровень механизма хранения.

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

Соединитель

Отвечает за установление соединений с клиентами, получение разрешений, поддержание и управление соединениями.
После прохождения проверки имени пользователя и пароля коннектор перейдет к таблице разрешений, чтобы запросить разрешения пользователя, а затем логика оценки разрешений в этой ссылке зависит от разрешения, прочитанного в это время; даже если разрешения пользователя изменены с использованием учетной записи администратора, не влияет на разрешения для существующих подключений
Параметр wait_timeout управляет временем, в течение которого сервер поддерживает соединение с клиентом в режиме ожидания, значение по умолчанию — 8 часов.
Накопление длинных соединений приводит к резкому увеличению использования памяти MySQL.
- Рассмотрите возможность периодического отключения длинных соединений
- После MySQL 5.7 выполните mysql_reset_connection для повторной инициализации ресурсов соединения (этот процесс не требует повторного подключения и повторной аутентификации, но восстановит соединение в только что созданное состояние)

MySQL :: MySQL 5.7 Reference Manual :: 27.8.7.60 mysql_reset_connection() Dev.MySQL.com/doc/Furious/…

кэш запросов

Если запрос попадет в кеш запросов, запрос не будет проанализирован, план выполнения не будет сгенерирован, и MySQL проверит разрешения пользователя, прежде чем возвращать результаты запроса.
Обновления таблицы приведут к очистке кеша запросов этой таблицы (кэш запросов часто становится недействительным, что приносит больше вреда, чем пользы).
Рекомендуется использовать кэш запросов по запросу.
- Задайте для параметра query_cache_type значение DEMAND.По умолчанию SQL не использует кеш запросов, а кеш запросов указывается в отображении SQL_CACHE.

select SQL_CACHE * from T where ID = 10;

MySQL 8.0 удалил всю функцию кэширования запросов

Парсер (что делает)

Лексический анализ, определяющий, что такое строка в SQL.
Разбор
- На основе лексического анализа в соответствии с правилами грамматики судят, соответствует ли SQL грамматике MySQL, и, наконец, получают дерево грамматики.
- Для SQL с разными параметрами, но теми же другими, их время выполнения различно, но время жесткого разбора одинаково; в то время как для одного и того же SQL по мере изменения данных запроса время выполнения нескольких запросов может быть разным, но время жесткого разбора неизменен.

Лексический анализ -> синтаксический анализ можно назвать жестким разбором

препроцессор

В соответствии с правилами MySQL дополнительно проверьте, является ли синтаксическое дерево допустимым.

Например, проверьте, существуют ли таблицы и столбцы данных, не являются ли псевдонимы неоднозначными и т. д.

Оптимизатор (как это сделать)

Оптимизатор преобразует синтаксическое дерево в план выполнения (можно создать несколько планов выполнения), а затем находит наиболее подходящий план выполнения (с наименьшими затратами).
Узнать стоимость запроса можно через Last_query_cost

На приведенном выше рисунке показано, что оптимизатор считает, что этот SQL должен выполнить случайный поиск 1,399 страниц данных для завершения запроса.

Оптимизатор может быть запрошен для разбора различных факторов процесса оптимизации (объясните sql)
Если в таблице несколько индексов, решите, какой индекс использовать.
С помощью специальных подсказок по ключевым словам оптимизатор может влиять на процесс принятия оптимизатором решений (например, для индекса).
После завершения фазы оптимизатора план выполнения дорабатывается.
Оптимизатор иногда выбирает неправильный план выполнения
- Неточная статистика индекса
- Оптимальный план выполнения, понятный оптимизатору, не обязательно является самым быстрым планом выполнения.

уровень механизма хранения

Отвечает за хранение и извлечение данных, режим подключаемой архитектуры, поддержку InnoDB, MyISAM и других механизмов хранения.
Различные механизмы хранения используют один и тот же уровень сервера

redo log

Уникальный журнал механизма InnoDB, журнал уровня механизма хранения

Когда запись необходимо обновить, механизм InnoDB обновит память, запишет запись в журнал повторов, а затем при необходимости обновит запись операции на диск.
Размер журнала повторов фиксирован.
- write pos — позиция текущей записи, двигаться назад при записи, писать в конец и начинать с начала
- Контрольная точка – это текущая позиция, которую нужно стереть. Она также перемещается назад и зацикливается, стирает записи и сначала обновляет журнал на диск.

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

bin log

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

Более подробная информация будет представлена позже

Различия между журналом повторов и бинлогом

журнал повторов уникален для InnoDB; binlog реализуется на уровне сервера и может использоваться всеми механизмами хранения.
журнал повторов — это физический журнал, в котором записываются «изменения на странице данных»; бинлог — это логический журнал, в котором записывается исходная логика оператора.
Журнал повторов записывается циклически, а место на нем фиксировано; бинлог можно записывать дополнительно, и он не перезапишет предыдущий лог.

Поток выполнения оператора обновления

Благодаря двухэтапной отправке журнала можно гарантировать согласованность логики журнала повторов и журнала корзины.

Если транзакция завершается сбоем в ①, транзакция будет отменена.
Если транзакция завершается сбоем на этапе ②, поскольку журнал повторов находится в стадии подготовки, а бинлог также был записан, он будет автоматически зафиксирован при восстановлении транзакции.

Write-Ahead Logging

Используя журнал, механизм хранения может обновить данные в памяти, а затем сохранить обновление в файле журнала на диске без необходимости каждый раз сбрасывать обновленные данные на диск (случайный ввод-вывод).
В журнале используется метод добавления, а операция записи журнала представляет собой последовательный ввод-вывод (намного быстрее, чем случайный ввод-вывод) небольшой области на диске.
После сохранения журнала на диск измененные данные (грязные страницы) в памяти можно медленно сбросить на диск в фоновом режиме.