База данных

Введение: сбор и обобщение знаний о базах данных, связанных с транзакциями, индексами, блокировками и оптимизацией SQL!

[TOC]

Модели баз данных

Первая нормальная форма (1NF)

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

Если нет особых требований к провинции или городу, то каждая колонка в этой таблице пользователей является неделимой. Однако, если есть особые требования к провинции или городу, адрес в таблице можно разделить следующим образом:

Преимущества:

• Структура таблицы более чёткая.
• Легче выполнять запросы.

Вторая нормальная форма (2NF)

Все неключевые столбцы должны полностью зависеть от всего первичного ключа. Возможные сценарии включают:

Пример:

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

Таблица курсов:

Таблица связи между студентами и курсами:

Преимущества:

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

Третья нормальная форма (3NF)

При условии соблюдения 1NF и 2NF, между неключевыми столбцами не должно быть косвенной зависимости (устранить передаточную зависимость).

Пример:

Идентификатор студента является первичным ключом, удовлетворяющим 2NF. Через идентификатор студента →→ Идентификатор класса →→ Название класса существует зависимость, поэтому:

Таблица классов:

Преимущества:

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

Нормальная форма Бойса-Кодда (BCNF)

На основе 3NF, между основными атрибутами не должно существовать частичной или передаточной зависимости. Например:

Существуют следующие ограничения:

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

Таким образом, (складской номер, номер детали) используется в качестве первичного ключа, что удовлетворяет 3NF. Однако (складской номер, номер детали) →→ Идентификатор сотрудника →→ Складской номер приводит к передаточной функциональной зависимости, поэтому:

Таблица сотрудников:

Преимущества:

• Устраняются некоторые избыточности и аномалии.

Недостатки:

• Потеря некоторых функциональных зависимостей, таких как (складской номер, номер детали) →→ Идентификатор сотрудника.

Четвёртая нормальная форма (4NF)

Основываясь на BCNF, необходимо устранить многозначные зависимости.

Например:

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

Преимущества:

• Решаются некоторые аномалии, делая структуру таблицы более разумной.

Пятая нормальная форма (5NF)

Основанная на 4NF, устраняет передаточные зависимости.

Например:

Чтобы найти определённую запись, необходимо использовать (продавец, поставщик, продукт) в качестве первичного ключа:

Продавец-поставщик:

Продавец-продукт:

• UPDATE, DELETE проблемы с повторным чтением решаются с помощью Record Lock (блокировка записи);

• проблема повторного чтения при INSERT решается с помощью Next-Key Lock (блокировка следующего ключа).

Глобальная блокировка (Global-Level)

Глобальная блокировка — это блокировка всей базы данных. MySQL предоставляет метод добавления глобальной блокировки чтения с помощью команды Flush tables with read lock (FTWRL). Когда вам нужно перевести всю библиотеку в режим только для чтения, вы можете использовать эту команду, и последующие операторы из других потоков будут заблокированы:

• операторы обновления данных;

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

• команды фиксации транзакций.

Применение:

• типичное применение — логическое резервное копирование всей библиотеки (mysqldump).

Опасность режима только для чтения:

• если вы выполняете резервное копирование основной библиотеки, то во время резервного копирования нельзя выполнять обновления, и бизнес может быть остановлен;

• если вы выполняете резервное копирование из вторичной библиотеки, вторичная библиотека не сможет синхронизировать двоичный журнал из первичной библиотеки во время резервного копирования, что приведёт к задержке синхронизации между первичной и вторичной библиотеками.

Существует два способа установить глобальную блокировку:

1. FLUSH TABLES WRITE READ LOCK (FTWRL);
2. set global readonly=true.

Обычно рекомендуется использовать FTWRL, потому что:

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

• существуют различия в механизмах обработки исключений:

  • если после выполнения FTWRL клиент неожиданно отключается, MySQL автоматически освобождает глобальную блокировку, и библиотека возвращается в нормальное состояние обновления;
  • после установки библиотеки в режим readonly и возникновения исключения база данных останется в режиме readonly, что приведёт к длительной блокировке всей библиотеки;
  • режим readonly не влияет на права суперпользователя.

Глобальная блокировка чтения (FTWRL)

Зачем нужна глобальная блокировка чтения (FTWRL) ?

Когда mysqldump использует параметр --single-transaction, он запускает транзакцию перед получением данных, чтобы обеспечить согласованность моментального снимка. Поскольку MVCC поддерживается, данные могут быть обновлены во время этого процесса. Поэтому метод single-transaction применим только к таблицам, использующим транзакционный движок. Если некоторые таблицы используют механизм, который не поддерживает транзакции, резервное копирование должно выполняться с использованием метода FTWRL. Это часто является одной из причин, по которой администраторы баз данных требуют от разработчиков использовать InnoDB вместо MyISAM.

Чтобы использовать глобальную блокировку, выполните следующую команду:

flush tables with read lock

После выполнения этой команды вся база данных переходит в режим «только чтение», и все последующие операции из других потоков блокируются:

• операции обновления данных, такие как insert, delete, update и т.д.;

• операции изменения структуры таблицы, такие как alter table, drop table и т.д.

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

unlock tables

Конечно, когда сеанс завершается, глобальная блокировка снимается автоматически.

set global readonly=true

Блокировка на уровне таблицы (Table-Level)

Блокировка на уровне таблицы — это самый высокий уровень детализации блокировки в MySQL. Она представляет собой блокировку текущей операции над всей таблицей и имеет простую реализацию, низкие накладные расходы и поддерживается большинством движков MySQL. Наиболее распространённые движки MyISAM и InnoDB поддерживают блокировку на уровне таблицы. Блокировки на уровне таблиц делятся на:

• разделяемую блокировку чтения на уровне таблицы (разделяемая блокировка);

• исключающую блокировку записи на уровне таблицы (исключающая блокировка).

Особенности: низкие накладные расходы, быстрая блокировка, отсутствие взаимоблокировок, высокая вероятность конфликтов блокировок и низкая степень параллелизма.

Таблица блокировок

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

Например, если мы хотим заблокировать таблицу студентов (t_student), мы можем использовать следующую команду:

— разделяемая блокировка на уровне таблицы, также известная как блокировка чтения;
lock tables t_student read;
— исключительная блокировка на уровне таблицы, также называемая блокировкой записи;
lock tables t_stuent wirte;

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

unlock tables

Метаданные блокировки (MDL)

Цель MDL — предотвратить конфликты DDL и DML и обеспечить правильность чтения и записи. Метаданные блокируют, чтобы гарантировать, что когда пользователь выполняет операции CRUD для таблицы, другие потоки не изменяют структуру таблицы. Нет необходимости явно использовать MDL, потому что MDL автоматически добавляется при выполнении операций с базой данных.

При выполнении операций CRUD над таблицей добавляется MDL блокировка чтения;

при изменении структуры таблицы добавляется MDL блокировка записи.

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

Намеренная блокировка (Intention Lock)

Намеренная блокировка — это блокировка на уровне таблицы, активно добавляемая InnoDB без ручного управления. Её цель — быстро определить, заблокирована ли таблица.

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

С намеренной блокировкой при добавлении эксклюзивной блокировки таблицы сначала добавляется намеренная эксклюзивная блокировка на уровне таблицы. При добавлении эксклюзивной блокировки таблицы InnoDB проверяет, есть ли уже намеренная эксклюзивная блокировка, и если да, то она не сканирует все записи в таблице.

Для insert, update, delete InnoDB автоматически добавляет эксклюзивную блокировку (X) к соответствующим данным;

для обычных операторов select InnoDB не добавляет никаких блокировок, и транзакции могут добавлять разделяемые блокировки или эксклюзивные блокировки с помощью следующих операторов:

• разделяемая блокировка: SELECT ... LOCK IN SHARE MODE;

• эксклюзивная блокировка: SELECT ... FOR UPDATE.

Разделяемая намеренная блокировка (Intention Shared Lock, IS Lock)

Разделяемая намеренная блокировка (IS Lock) указывает на то, что транзакция собирается добавить разделяемую блокировку (S Lock) к определённой строке записи, и ей необходимо сначала добавить IS Lock на уровне таблицы.

Функция: уведомляет базу данных о том, какую блокировку следует добавить и заблокировать таблицу. Например, если требуется добавить разделяемую блокировку к записи A, InnoDB сначала найдёт таблицу, добавит IS Lock к таблице, а затем добавит S Lock к записи A.

Эксклюзивная намеренная блокировка (Intention Exclusive Lock, IX Lock)

Эксклюзивная намеренная блокировка (IX Lock) указывает, что транзакция собирается добавить эксклюзивную блокировку (X Lock) к определённой записи строки, и ей необходимо добавить IX Lock на уровне таблицы перед этим.

Функция: уведомить базу данных о том, какая блокировка должна быть добавлена, и заблокировать таблицу. Например, если необходимо добавить эксклюзивную блокировку к записи A, InnoDB сначала находит таблицу, добавляет IX Lock к таблице, а затем добавляет X Lock к записи A.

Самоувеличивающаяся блокировка (AUTO-INC Lock)

Самоувеличивающаяся блокировка — это особый тип блокировки на уровне таблицы, используемый для обеспечения уникальности значений столбцов с атрибутом AUTO_INCREMENT.

Самоувеличивающаяся блокировка не удерживается до завершения транзакции, а освобождается сразу после выполнения оператора вставки. Самоувеличивающаяся блокировка добавляется на уровне таблицы при вставке данных и немедленно освобождается после присвоения значения столбцу с атрибутом AUTO_INCREMENT.

В версиях MySQL до 5.1.22 самоувеличивающаяся блокировка влияла на производительность вставки, поскольку вставка из другого сеанса блокировалась. Начиная с версии 5.1.22, InnoDB предоставляет облегчённую блокировку для реализации самоувеличивающейся блокировки. Облегчённая блокировка также добавляется к столбцу с атрибутом AUTO_INCREMNET при вставке и немедленно освобождается после назначения значения.

InnoDB предоставляет системную переменную innodb_autoinc_lock_mode для управления выбором самоувеличивающейся блокировки или облегчённой блокировки:

• innodb_autoinc_lock_mode = 0: использовать самоувеличивающуюся блокировку;

• innodb_autoinc_lock_mode = 2: использовать облегчённую блокировку. Эта блокировка обеспечивает наилучшую производительность, но значения автоинкремента могут быть не последовательными, что небезопасно в сценариях репликации главный-подчиненный;

• innodb_autoinc_lock_mode = 1 (по умолчанию): использовать оба типа блокировок. Если количество вставляемых записей известно, используется облегчённая блокировка; в противном случае используется самоувеличивающаяся блокировка.

Блокировка уровня страницы (Page-Level)

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

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

• разделяемая блокировка;
• эксклюзивная блокировка.

Особенности: высокие накладные расходы, медленное получение блокировки, возможность возникновения взаимоблокировок, наименьшая вероятность конфликта блокировок, наивысшая степень параллелизма.

При использовании транзакций в InnoDB блокировка на уровне строк реализуется путём блокировки индекса. Разделяемая и эксклюзивная блокировки применяются только после получения соответствующей «намеренной» блокировки. Разделяемые и эксклюзивные блокировки на уровне строк также бывают двух видов:

• общие (S-блокировки);
• эксклюзивные (X-блокировки).

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

Общие блокировки (Shared Locks)

Общие блокировки (S-блокировки или блокировки чтения) позволяют нескольким операциям чтения выполняться одновременно без взаимного влияния. Если транзакция T применяет общую блокировку к данным A, то транзакция T может только читать данные A. Другие транзакции могут применять только общие блокировки к данным A до тех пор, пока транзакция T не снимет блокировку. Для применения общей блокировки используется команда:

— select ... lock in share mode.

Примечание: все общие блокировки являются блокировками строк.

Эксклюзивные блокировки (Exclusive Locks)

Эксклюзивные блокировки (X-блокировки или блокировки записи) предотвращают любые другие операции чтения и записи до завершения текущей операции записи. Если транзакция T применяет эксклюзивную блокировку к данным A, только транзакция T сможет читать и изменять данные A, а другие транзакции не смогут применять никакие типы блокировок к данным A до снятия блокировки транзакцией T. Для применения эксклюзивной блокировки используются команды:

• insert;
• update;
• delete;
• select ... for update.

Примечание: в операциях обновления (INSERT, UPDATE или DELETE) всегда применяются эксклюзивные блокировки. Эксклюзивные блокировки могут быть как блокировками строк, так и блокировками таблиц.

— когда username является первичным ключом, блокировка применяется к строке; когда username не является первичным ключом, это блокировка таблицы.
SELECT count(*) as total FROM test WHERE username = "zhangsan" FOR UPDATE

Реализация блокировки

Блокировка записей (Record Lock)

Блокировка записей применяется к отдельной строке индекса.

— id является первичным ключом или уникальным индексом
SELECT * FROM t_user WHERE id = 1 FOR UPDATE;

Строка с id, равным 1, будет заблокирована. Обратите внимание:

• столбец id должен быть первичным ключом или уникальным индексом, иначе блокировка станет блокировкой следующего ключа;
• запрос должен быть точным соответствием (=`), иначе он также станет блокировкой следующего ключа.

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

— id является первичным ключом
UPDATE t_user SET age = 50 WHERE id = 1;

Примечание: в MySQL блокировка строк фактически применяется к индексу. Индексы делятся на первичные ключи и непервичные ключи:

• если запрос работает с первичным ключом, MySQL блокирует этот первичный ключ;
• если запрос работает с непервичным ключом, сначала блокируется непервичный ключ, затем блокируется соответствующий первичный ключ.

Блокировка промежутка (Gap Lock)

Промежуток — это причина фантомного чтения. Блокировка промежутка решает эту проблему.

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

SELECT * FROM t_user WHERE id BETWEEN 1 AND 10 FOR UPDATE;
— или
SELECT * FROM t_user WHERE id > 1 AND id < 10 FOR UPDATE;

Все строки в диапазоне (1, 10) будут заблокированы. Все строки с id 2, 3, 4, 5, 6, 7, 8, 9 будут заблокированы от вставки, но строки с id, равными 1 и 10, не будут заблокированы. Помимо ручной установки блокировки, после выполнения некоторых SQL-запросов InnoDB автоматически устанавливает блокировку промежутка.

Причина фантомного чтения заключается в том, что блокировка строк может применяться только к строкам, но не к промежуткам между ними. Поэтому вводится блокировка промежутков.

Цель блокировки промежутков:

1. предотвратить фантомное чтение, чтобы удовлетворить требования соответствующего уровня изоляции транзакций;
2. удовлетворить потребности восстановления и репликации.

Условия возникновения блокировки промежутка (при уровне изоляции READ COMMITTED):

1. использование обычной блокировки для первичного ключа;
2. использование многостолбцового уникального индекса;
3. использование уникального индекса для блокировки нескольких строк.

Блокировка следующего ключа (Next-Key Lock)

Блокировка следующего ключа — это особый вид блокировки промежутка, который также можно рассматривать как особый алгоритм. Блокировка следующего ключа решает проблему фантомного чтения. Каждый непервичный индекс имеет блокировку следующего ключа для каждой строки. Когда транзакция удерживает блокировку следующего ключа строки данных, она блокирует диапазон слева направо. Следует подчеркнуть, что в InnoDB блокировки строк основаны на индексах, и блокировка следующего ключа существует только для непервичных индексов. Первичные индексы не имеют блокировки следующего ключа.

Например, таблица t_user содержит информацию (id — первичный ключ, age — уникальный индекс, name):

Таблица содержит следующие потенциальные блокировки следующего ключа:


Транзакция A выполняет следующую команду:

— обновление по непервичному индексу
UPDATE t_user SET name = Vladimir WHERE age = 24;
— или блокировка одной строки по непервичному индексу
SELECT * FROM t_user WHERE age = 24 FOR UPDATE;

Независимо от того, какая из команд выполняется в транзакции A, если транзакция B затем пытается выполнить следующую команду, транзакция B будет заблокирована:

INSERT INTO t_user VALUES(100, 26, 'tian');

Очевидно, что транзакция A при изменении возраста 24 блокирует промежуток (24, 32].

Кроме того, при выполнении следующей команды транзакция B также будет заблокирована:

INSERT INTO table VALUES(100, 30, 'zhang');

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

При чтении данных значение поля version считывается вместе с данными. Каждый раз, когда данные обновляются, к значению version прибавляется единица.

- Использование временных меток (timestamp) для сравнения

Во время обновления проверяется текущая временная метка в базе данных и временная метка перед обновлением. Если они совпадают, то всё в порядке, иначе возникает конфликт версий.

Пессимистическая блокировка (Pessimistic Lock)

Пессимистическая блокировка («один замок — две проверки — три обновления») характеризуется тем, что сначала получают блокировку, а затем выполняют операцию. Это называется «пессимистическим» подходом, поскольку предполагается, что получение блокировки может завершиться неудачей. Поэтому необходимо сначала убедиться в успешном получении блокировки, а потом выполнять операцию. В базах данных пессимистическую блокировку нужно поддерживать на уровне базы данных, используя команду select … for update.

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

Проблемы блокировок

Тупик (Deadlock)

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

В InnoDB блокировки получаются постепенно, поэтому могут возникнуть тупиковые ситуации. Распространённая ошибка: «Deadlock found when trying to get lock...».

Как избежать тупиковой ситуации

Есть три способа избежать тупиковых ситуаций:

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

Предотвращение тупиковых ситуаций

• innodb_lock_wait_timeout — ожидание тайм-аута блокировки и откат транзакции.

Простой способ — когда две транзакции ожидают друг друга, если одна из них ждёт слишком долго, происходит откат одной из транзакций, и другая может продолжить работу.

• Алгоритм ожидания-для-графа (wait-for graph) для активного обнаружения тупиковых ситуаций. Алгоритм требует, чтобы база данных сохраняла следующую информацию:
  • Список блокировок. — Список транзакций в ожидании.

Решение тупиковых ситуаций

• Ожидание тайм-аута транзакции и активный откат.
• Активное обнаружение тупиковых ситуаций и откат одной из транзакций, чтобы позволить другой транзакции продолжить работу.

Вот метод решения тупиковых ситуаций:

-- Просмотр текущих блокировок транзакций
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;


— Значение trx_mysql_thread_id из рисунка выше
kill trx_mysql_thread_id;

Грязное чтение (Dirty Read)

Грязное чтение означает, что в одной транзакции можно прочитать данные, которые ещё не были зафиксированы в другой транзакции.

Например, транзакция T1 изменяет данные, а транзакция T2 читает эти данные позже. Если T1 откатывает изменение, данные, прочитанные T2, являются грязными.


Неповторяющееся чтение (Non-Repeatable Read)

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

Например, T2 считывает данные, затем T1 изменяет эти данные. Если T2 снова считывает эти данные, результат будет отличаться от первого раза.

В механизме хранения InnoDB:

• SELECT — проблема неповторяющегося чтения решается с помощью MVCC.
• UPDATE/DELETE — проблема неповторяющегося чтения решается с помощью Record Lock.
• INSERT — проблема неповторяющегося чтения решается с помощью Next-Key Lock (Record Lock + Gap Lock).

Фантомное чтение (Phantom Read)

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

Потерянное обновление (Lost Update)

Потерянное обновление означает, что обновление одной транзакции перезаписывается обновлением другой транзакции.

Например, транзакции T1 и T2 одновременно обновляют одни и те же данные. Обновление T1 перезаписывается T2.

Это проблему можно решить, добавив исключительную блокировку к операции SELECT, но это может привести к снижению производительности, и решение зависит от конкретной ситуации.

Транзакции базы данных

Что такое транзакция?

Транзакция — это серия операций над данными в системе, которая рассматривается как единая логическая единица работы.

Свойства транзакций (ACID)

Атомарность (Atomicity)

Атомарность означает, что транзакция представляет собой неделимую единицу работы, и все операции внутри неё должны быть выполнены полностью или не выполнены вообще. Если какая-либо операция внутри транзакции завершается неудачно, все предыдущие операции также должны быть отменены, и база данных возвращается в состояние до начала транзакции.

Как это работает

① Журнал отмены (undo log)

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

• Для каждой операции insert журнал отмены содержит операцию delete.
• Для каждой операции delete журнал отмены содержит операцию insert.
• Для каждой операции update журнал отмены содержит противоположную операцию update, которая отменяет изменения.

Согласованность (Consistency)

Согласованность означает, что после завершения транзакции база данных остаётся в согласованном состоянии, и никакие ограничения целостности не нарушаются. Ограничения целостности включают, но не ограничиваются:

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

Если A переводит 10 единиц на счёт B, независимо от результата, общий баланс A и B не должен измениться.

Как это работает

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

① Обеспечение атомарности, долговечности и изоляции. Без этих свойств невозможно обеспечить согласованность.

② Обеспечение согласованности самой базой данных. Например, предотвращение вставки строковых значений в целочисленные столбцы или ограничение длины строк в соответствии с требованиями столбца.

③ Обеспечение согласованности на уровне приложения. Например, при переводе средств только уменьшать баланс отправителя и не увеличивать баланс получателя.

Изоляция (Isolation)

Изоляция означает, что операции внутри одной транзакции не влияют на другие параллельные транзакции. Существует несколько уровней изоляции транзакций:

• Самый строгий уровень изоляции — сериализуемый (Serializable), который обеспечивает полную изоляцию между транзакциями. Однако этот уровень редко используется из-за его влияния на производительность.

Как это работает

Цель изоляции — предотвратить взаимное влияние транзакций друг на друга. Есть два аспекта:

① Блокировка для обеспечения изоляции: блокировка записи одной транзакции от чтения другой транзакцией. После получения блокировки транзакция может изменять данные, и другие транзакции должны ждать освобождения блокировки.

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

• Скрытые столбцы: каждая строка данных имеет два скрытых столбца (на самом деле три):
  • Идентификатор транзакции (data_trx_id), который указывает на последнюю транзакцию, изменившую эту строку.
  • Указатель отката (data_roll_pointer), указывающий на соответствующую запись журнала отмены.
• На основе журнала отмены формируется цепочка версий, где каждая запись отмены указывает на предыдущую запись.
• ReadView: при выполнении запроса создаётся снимок активных транзакций (ReadView), который определяет, какие данные видны текущей транзакции. ReadView содержит список идентификаторов транзакций и нижнюю границу версии. В памяти при отправке транзакции в базу данных происходит кэширование состояния транзакции до её успешного завершения или отката.

Если транзакция завершается успешно, то состояние кэша становится неактуальным и его можно удалить. За удаление неактуальных состояний отвечает поток Purge. По умолчанию существует 4 таких потока.

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

В MySQL версии 5.7 и выше можно настроить количество потоков для очистки грязных страниц. Это позволяет повысить эффективность обработки грязных страниц:

SET GLOBAL innodb_page_cleaner = 3

Данные хранятся на страницах. Страница — это область памяти, которая содержит записи таблицы. На диске страницы связаны двунаправленной цепью для быстрого поиска. Размер одной страницы по умолчанию составляет 16 килобайт. Его можно изменить с помощью параметра innodb_page_size.

Каждая страница состоит из следующих частей:

• Заголовок файла (File Header) — содержит информацию о странице, например, номер страницы, тип страницы и ссылки на предыдущую и следующую страницу. Размер заголовка фиксированный и составляет 38 байт.
• Заголовок страницы (Page Header) — хранит информацию о состоянии страницы и сводные данные, такие как количество записей на странице. Размер заголовка также фиксированный — 56 байт.
• Записи Infimum и Supremum — используются для быстрого нахождения минимальной и максимальной записи на странице. При создании пустой страницы автоматически создаются две дополнительные записи: одна для минимальной записи (Infimum), а другая для максимальной (Supremum).
• Пользовательские записи (User Records) — содержат данные, которые пользователь сохраняет в базе данных. Каждая запись состоит из трёх частей: дополнительной информации о записи, скрытых столбцов и реальных данных пользователя.
• Свободное пространство (Free Space) — оставшееся пространство на странице, которое может быть использовано для новых записей.
• Каталог страниц (Page Directory) — используется для быстрого поиска записей на странице с помощью бинарного поиска. Каталог содержит ссылки на максимальные записи каждой группы записей на странице.
• Хвостовая часть файла (File Trailer) — проверяет целостность страницы. Состоит из двух частей: контрольной суммы (4 байта) и позиции журнала (4 байта), соответствующей последней модификации страницы.

Также в тексте упоминаются различные форматы строк: компактный, избыточный, динамический и сжатый. Они определяют способ хранения данных в записях. Пул буферов

В пуле буферов все кэшированные страницы записываются на диск, поскольку накладные расходы ввода-вывода слишком велики. Следует записывать грязные страницы на диск (обновлённые кэшированные страницы). Какие страницы являются грязными?

• Обратитесь к схеме проектирования свободной цепочки, чтобы добавить описание данных в цепочку сброса. Схема проектирования цепочки сброса:
  • Добавьте базовый узел сброса;
  • Описание данных добавляет указатель узла сброса.

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

Цепочка LRU

С течением времени база данных MySQL будет непрерывно загружать данные с диска в свободные кэшированные страницы, поэтому количество свободных кэшированных страниц в цепочке свободных будет постепенно уменьшаться, пока их не останется совсем, и тогда данные на диске больше не смогут быть загружены. Чтобы решить эту проблему, необходимо исключить кэшированные страницы и освободить место для свободных кэшированных страниц. Здесь используется идея алгоритма LRU для исключения наименее используемых кэшированных страниц и предоставления цепочки LRU. Схема проектирования LRU:

• Добавить базовый узел LRU;
• Описание данных добавляет указатель узла LRU.

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

Оптимизация цепочки LRU

При исключении кэшированных страниц всё ещё существует проблема: если использовать только механизм цепочки LRU, два сценария могут привести к исключению горячих точек данных:

1. Предварительное чтение

InnoDB использует два алгоритма предварительного чтения для повышения производительности ввода-вывода: линейное предварительное чтение (linear read-ahead) и случайное предварительное чтение (random read-ahead).

Предварительное чтение означает, что когда MySQL загружает данные, он может загрузить соседние данные вместе (принцип локальности). Это приводит к проблеме: данные предварительного чтения фактически не были доступны, но они были помещены впереди. Обычно при исключении кэшированных страниц следует исключить предварительно прочитанные данные, но в итоге исключаются данные в конце очереди. Это неразумно.

2. Полное сканирование таблицы

Если объём данных таблицы велик, большое количество кэшированных страниц будет использовано. В конечном итоге все кэшированные страницы в начале цепочки LRU — это данные полного сканирования таблицы, а ранее часто используемые горячие точки данных находятся в конце очереди, и они будут исключены при исключении кэшированных страниц.

Решение

Чтобы решить вышеуказанные проблемы, нам нужно разделить горячие и холодные данные в цепочке LRU и разделить цепочку LRU на холодную и горячую области в определённом соотношении. Горячая область называется областью young, холодная область называется областью old. Например, 7:3, область young составляет 70%, область old составляет 30%.

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

Область young также может быть немного оптимизирована, чтобы предотвратить частое перемещение данных области young в начало очереди. Область young перемещается в начало очереди только тогда, когда 1/4 данных была доступна, в противном случае она остаётся в конце очереди.

Следует отметить, что InnoDB вводит параметр midpoint в список LRU. Новые прочитанные страницы не помещаются непосредственно в начало списка LRU, а помещаются в позицию midpoint, которая определяется параметром innodb_old_blocks_pct. По умолчанию это 37%, минимум 5, максимум 95. Если память большая, этот параметр можно уменьшить, обычно до 20, то есть 20% — это холодные блоки данных. Цель состоит в том, чтобы защитить горячие данные от вытеснения из памяти.

Также InnoDB ввёл параметр innodb_old_blocks_time, который контролирует время, необходимое для того, чтобы стать горячими данными, по умолчанию 1000 мс, то есть 1 секунда, то есть данные не вытесняются из памяти в течение 1 секунды.

Изменённый буфер

Изменённый буфер (Change Buffer) находится в памяти и используется, когда вторичный индекс (secondary index) подвергается DML-операциям, а соответствующие страницы в буферном пуле отсутствуют. Страницы в изменённом буфере объединяются в буферный пул при чтении. Когда количество грязных страниц превышает определённый процент, они сбрасываются на диск. Изменённый буфер занимает 25% буферного пула по умолчанию.

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

Сценарии применения

1. Подходит для сценариев с большим количеством записей и небольшим количеством чтений

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

2. Не подходит для сценариев, где запись сразу же запрашивается

Запись выполняется, и доступ к данным осуществляется немедленно. Даже если условие выполнено, обновление сначала записывается в изменённый буфер, но впоследствии, поскольку доступ к этим данным должен быть выполнен немедленно, это вызовет процесс слияния. Количество случайных операций ввода-вывода не уменьшается, а стоимость обслуживания изменённого буфера увеличивается. Поэтому для такого сценария использования изменённый буфер имеет противоположный эффект.

Адаптивный хэш-индекс

Адаптивный хеш-индекс (Adaptive Hash Index) значительно оптимизирует запросы к буферному пулу. Для горячих страниц данных в буферном пуле создаётся индекс (обычно используется префикс ключа для создания хеш-индекса). Поскольку эффективность поиска по равнозначным значениям в хеш-индексе намного выше, чем в B+Tree, результаты могут быть быстро возвращены при совпадении хэша, и нет необходимости проходить через B+Tree.

Журнал буфера

Журнал буфера (Log Buffer) используется для хранения журналов, которые вот-вот будут записаны на диск (например, RedoLog). По умолчанию размер составляет 16 МБ. Параметры настройки следующие:

• innodb_log_buffer_size: настройка размера;
• innodb_flush_log_at_trx_commit: управление тем, как содержимое журнала буфера записывается и сбрасывается на диск;
  • 0: журнал сбрасывается на диск каждую секунду. Транзакции без сброса журнала будут потеряны при сбое MySQL;
  • 1: значение по умолчанию. Журнал записывается при каждой фиксации транзакции и сбрасывается на диск при каждой фиксации;
  • 2: журнал записывается после каждой фиксации транзакции, и сбрасывается каждую секунду; транзакции без сброса журнала могут быть потеряны при сбое MySQL.
• innodb_flush_log_at_timeout: управляет частотой сброса журнала. Сбрасывает журнал каждые несколько секунд, диапазон значений [1,2700] (секунд).

Структура диска

Табличное пространство

Системное табличное пространство (The System Tablespace) — это область хранения словаря данных, двойной буфер записи (Doublewrite Buffer), изменённого буфера (Change buffer) и журнала отмены (Undo Logs). Файл данных управляется параметром innodb_data_file_path, значение по умолчанию — ibdata1:12M:autoextend (имя файла — ibdata1, размер чуть больше 12 МБ, автоматическое расширение). Начиная с версии 8.0, словарь данных InnoDB хранится в системном табличном пространстве.

Пространство таблиц с исключительным правом владения

Пространства таблиц с исключительным правом владения (File-Per-Table Tablespaces) включены по умолчанию, и каждый файл таблицы имеет свой собственный файл .ibd. Параметр innodb_file_per_tabl может отключить его, и в этом случае все данные таблицы будут храниться в системном табличном пространстве ibdata.

Общее табличное пространство

Общие табличные пространства (General Tablespaces) создаются с помощью CREATE TABLESPACE. Общие табличные пространства могут быть созданы вне каталога данных MySQL, и одно табличное пространство может содержать несколько таблиц и поддерживать все форматы строк.

Табличное пространство отмены

Табличные пространства отмены (Undo Log Tablespaces) хранят журнал отмены, используемый для отката транзакций. Журнал отмены (undo log) записывает состояние данных до транзакции (исключая select). Он используется для обеспечения возможности отката при необходимости и для восстановления исходных данных при выполнении других транзакций, требующих согласованного чтения. Если другой транзакции требуется просмотреть исходные данные во время согласованного чтения, она извлекает данные из журнала отмены вместо физической страницы, что является логическим форматом журнала, используемым для реализации MVCC. При выполнении отката просто восстанавливается логическое состояние данных до состояния транзакции, а не физическое состояние страницы. Табличные пространства) хранят данные временных таблиц, включая созданные пользователем временные таблицы и внутренние временные таблицы диска. Соответствующий каталог данных — ibtmp1 файл. Когда сервер данных нормально закрывается, табличное пространство удаляется и создаётся заново при следующем использовании.

Словарь данных (Data Dictionary)

Словарь данных (DD, Data Dictionary) — это совокупность информации о базе данных, например, таблицы, представления, индексы и т. д., которые можно рассматривать как метаданные базы данных. Другими словами, словарь данных хранит информацию о структуре таблиц, столбцах каждой таблицы и индексах.

В InnoDB словарь данных состоит из внутренних системных таблиц, содержащих метаданные для поиска таблиц, индексов и полей таблиц. Физически метаданные находятся в табличном пространстве InnoDB. По историческим причинам метаданные словаря данных частично совпадают с информацией метаданных табличных файлов (.frm файлов).

Буфер двойной записи (Doublewrite Buffer)

Механизм буфера двойной записи значительно повышает безопасность данных Innodb. Даже если происходит сбой диска или сбой страницы, можно восстановить данные из буфера двойной записи.

Буфер двойной записи — это область хранения, в которую InnoDB записывает страницу перед её записью в соответствующее место в файле данных InnoDB. Если во время процесса записи происходит сбой системы, сбой подсистемы хранения или сбой процесса mysqld, InnoDB может восстановить страницу из буфера двойной записи во время восстановления после сбоя.

До MySQL 8.0.20 буфер двойной записи хранился в табличном пространстве InnoDB. Начиная с MySQL 8.0.20, буфер двойной записи хранится в отдельном файле двойной записи. По умолчанию создаются два файла буфера двойной записи начиная с MySQL 8.0.20.

Почему существует двойная запись, несмотря на наличие журнала повторов? В чём преимущество двойной записи?

Основная цель двойной записи — обеспечить надёжность страниц данных. Основное назначение — решить проблему частичной потери данных. Например, страница размером 16 КБ, но записано только первые 4 КБ, а затем происходит сбой. Такая ситуация называется частичной потерей данных. Журнал повторов не может решить эту проблему.

Журнал повтора (Redo Log)

Журнал повторов — это структура данных на диске, которая используется для исправления неполных записей данных во время восстановления после сбоев. Во время нормальной работы журнал повторов регистрирует запросы на изменение данных таблицы, вызванные операторами SQL или низкоуровневыми вызовами API. Перед инициализацией и до принятия соединения автоматически воспроизводятся незавершённые обновления данных файла, которые не были завершены до неожиданного закрытия. По умолчанию журнал повторов автоматически создаёт два файла: ib_logfile0 и ib_logfile1.

WAL механизм

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

Назначение WAL

Чтобы гарантировать долговечность данных в MySQL, каждый раз, когда транзакция фиксируется, журнал обновляется на диске. Это серьёзно влияет на производительность и поэтому используется механизм опережающей записи.

Принцип работы опережающей записи

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

Отменяющий журнал (Undo Logs)

Отменяющий журнал в основном предназначен для поддержки функции отката транзакций. По умолчанию он создаёт два отменяющих журнала, хранящихся в табличных пространствах отмены, по умолчанию в каталоге данных: undo_001 и undo_002.

Одна транзакция может выделить максимум четыре отменяющих журнала:

• Для выполнения операций вставки в пользовательские таблицы;
• Для выполнения операций удаления и обновления в пользовательских таблицах;
• Для выполнения операций вставки в пользовательские временные таблицы;
• Для выполнения операций удаления и обновления во временных таблицах пользователя.

Хранение индексов

InnoDB поддерживает три распространённых типа индексов:

• B+ дерево;
• Полнотекстовый индекс;
• Хеш-индекс.

B+дерево

B+дерево — это приближённое сбалансированное дерево поиска для дисков или других устройств прямого доступа. В InnoDB B+деревья индексируются по типу ключа. Индексы B+дерева можно разделить на кластеризованные и некластеризованные.

Кластеризованный индекс (первичный ключ)

Кластеризованный индекс, также известный как индекс B+дерева, использует нелистовые узлы для хранения индексных страниц, а листовые узлы используются для хранения страниц данных, причём все строки записей на одной и той же листовой странице упорядочены по возрастанию ключа. Листовые страницы связаны двунаправленным списком.

На рисунке ниже показана структура кластеризованного индекса InnoDB:

При создании таблицы InnoDB автоматически строит кластеризованный индекс на основе первичного ключа. Высота дерева обычно составляет от 2 до 4 уровней, поэтому требуется от 2 до 4 запросов для доступа к соответствующей странице данных. При поиске по первичному ключу поиск выполняется последовательно в дереве B+до тех пор, пока не будет найдена листовая страница, а затем выполняется двоичный поиск в листовой странице для определения местоположения целевой строки. Поэтому скорость поиска по первичному ключу очень высока.

Некластеризованный индекс

Некластеризованный индекс использует нелистовые узлы для хранения соответствующих ключевых значений, а листовые узлы не содержат строк данных, но содержат первичный ключ. Поиск по непервичному ключу должен сначала найти соответствующую страницу в некластеризованном индексе B+дерева через первичный ключ, а затем перейти к кластеризованному индексу B+дерева для поиска целевой строки данных. Высота дерева также обычно составляет от 2 до 4 уровней.

На рисунке ниже представлена структура некластеризованного индекса:

Полнотекстовый индекс (FULLTEXT)

Особенностью индекса B+дерева является то, что он может выполнять поиск по равным значениям (например, where a = "xxx") или префиксный поиск (например, where a like "xxx%"). Однако, если данные, хранящиеся в базе данных, представляют собой длинные текстовые документы, такие как блоги или Weibo, пользователи обычно хотят выполнять поиск с использованием неточного соответствия (например, где a like "%xxx%"), поскольку условия не имеют фиксированных префиксов и, следовательно, не могут использовать преимущества индекса B+дерева. Чтобы решить эту проблему, был представлен полнотекстовый индекс.

Полнотекстовый индекс — это технология быстрого поиска текста в базе данных. Его основная идея заключается в создании вспомогательной таблицы, которая сопоставляет слова или фразы с их местоположением в документе. В отличие от индекса B+дерева, где ключом является значение, а значением является местоположение, здесь ключ — это слово или фраза, а значение — местоположение. Этот тип индекса также называется инвертированным индексом.

Эта вспомогательная таблица также сохраняется на диске для повышения производительности. InnoDB генерирует шесть вспомогательных таблиц для ускорения полнотекстового поиска и сохраняет их в памяти. Эти таблицы хранятся в кэше в виде красно-чёрных деревьев, отсортированных по (текст, документ). Этот кэш также имеет яркое название — кэш полнотекстового индекса.

Параметр innodb_ft_cache_size управляет размером кэша полнотекстового индекса, и его значение по умолчанию обычно составляет 32 МБ. Когда кэш заполнен, информация о сопоставлении (текст, документ) сохраняется на диск. Если размер кэша установлен слишком маленьким, это может привести к частой подкачке и снижению эффективности поиска.

Кроме того, стоит отметить, что полнотекстовый поиск InnoDB поддерживает только языки с разделителями слов, такие как английский, и не поддерживает языки без разделителей слов, такие как китайский.

Адаптивный хеш-индекс

Высота дерева B+обычно составляет от 2 до 4 уровней, поэтому для доступа к данным требуется от 2 до 4 поисков. Хеш-индексы обеспечивают чрезвычайно быстрый поиск, обычно с временной сложностью O(1). InnoDB обычно включает адаптивный хеш-индекс по умолчанию, который автоматически отслеживает доступ к страницам индекса таблицы и строит хеш-индексы, если это приводит к повышению производительности. Таким образом, он называется адаптивным хеш-индексом (Adaptive Hash Index). AHI строится из страниц индекса B+дерева в буферном пуле, без необходимости доступа к диску, поэтому скорость построения очень высокая.

Поскольку он адаптивен, мы не можем активно создавать AHI, но можем контролировать его включение или выключение:

set global innodb_adaptive_hash_index=off/on

Хранилище InnoDB использует AHI для горячих страниц на основе следующих критериев:

• Запрос должен указывать конкретное значение, а не диапазон (например, where a=xxx and b=xxx);
• Запрос обращается к странице более 100 раз или достигает 1/16 от общего количества записей на странице.

На следующем рисунке показана структура адаптивного хеш-индекса:

Согласно официальной статистике производительности InnoDB, включение AHI может повысить производительность чтения и записи в два раза, а производительность соединений с некластеризованным индексом — в пять раз. Обычно рекомендуется включать AHI. Кроме того, мы можем использовать команду для просмотра текущего состояния использования AHI:

show engine innodb status \G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
------------------------------------- **Слияние**

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

**MySQL-лог**

*Логи MySQL включают:*
— Redo log (журнал повтора);
— Bin log (двоичный журнал);
— Undo log (журнал отката);
— Error log (журнал ошибок);
— Slow query log (медленный журнал запросов);
— General log (общий журнал);
— Relay log (журнал ретрансляции).

### Двоичный журнал (bin log)

Binlog используется для записи операций записи в базу данных (исключая запросы) в двоичной форме на диск. Binlog — это логический журнал MySQL (SQL-запросы), который записывается сервером с использованием любого механизма хранения MySQL. Записи в binlog добавляются, а размер каждого файла binlog можно настроить с помощью параметра max_binlog_size. Когда размер файла достигает указанного значения, создаётся новый файл для сохранения логов.

#### Применение binlog

В реальных сценариях использования binlog имеет два основных применения:
1. **Мастер-реплика:** на главном сервере (Master) включается binlog, затем binlog отправляется на каждый подчинённый сервер (Slave), где он воспроизводится для синхронизации данных между главным и подчинёнными серверами.
2. **Восстановление данных:** с помощью инструмента mysqlbinlog можно восстановить базу данных до определённого момента времени.

#### Запись на диск

Для InnoDB, записи в binlog происходят только после фиксации транзакции. В MySQL 5.7.7 и более поздних версиях значение по умолчанию для параметра sync_binlog равно 1, что означает, что каждая фиксация транзакции приводит к немедленной записи binlog на диск. Однако для повышения производительности базы данных можно увеличить значение этого параметра, жертвуя некоторой согласованностью ради улучшения производительности.

#### Формат журнала

Существует три формата binlog: STATEMENT, ROW и MIXED. До MySQL 5.7.7 по умолчанию использовался формат STATEMENT. В MySQL 5.7.7 и выше по умолчанию используется формат ROW.

*STATEMENT:* основан на копировании SQL-операторов (statement-based replication, SBR). В этом формате каждая изменяющая данные SQL-команда записывается в binlog. Преимущества этого подхода включают уменьшение размера binlog и повышение производительности за счёт уменьшения количества записей. Недостатком является возможность возникновения несогласованности данных в некоторых ситуациях, например, при выполнении функций sysdate(), sleep() и других.

*ROW:* основан на репликации строк (row-based replication, RBR). Этот формат не записывает контекстную информацию о каждой SQL-команде, а только фиксирует изменённые строки. Преимущество заключается в том, что он предотвращает проблемы с копированием определённых хранимых процедур, функций и триггеров. Недостаток — увеличение объёма логов, особенно при изменении структуры таблицы.

*MIXED:* смешанный режим репликации (mixed-based replication, MBR), который использует STATEMENT для обычных операций и ROW для операций, которые не могут быть корректно скопированы в режиме STATEMENT.

### Журнал повтора (redo log)

Журнал повтора InnoDB используется для регистрации изменений данных, вызванных транзакциями. Он состоит из двух частей: буфера журнала повтора в памяти и файлов журнала повтора на диске. Каждый раз, когда выполняется DML-оператор, запись об операции сначала помещается в буфер журнала повтора, и затем несколько операций одновременно записываются в файл журнала повтора. Эта техника, называемая WAL (Write-Ahead Logging), гарантирует сохранность данных. Файлы журнала повтора имеют фиксированный размер, например, могут состоять из четырёх файлов по 1 ГБ каждый.

#### WAL (Write-Ahead Logging)

WAL (Write-Ahead Logging, предварительное журналирование) — это механизм безопасности данных, который сначала записывает изменения в журнал, а затем сохраняет их на диск. Сначала транзакция фиксируется в памяти, затем изменения записываются в журнал повтора (Redo Log в InnoDB), и наконец, данные асинхронно сохраняются на диск фоновым процессом.

#### Функции

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

#### Процесс записи

Чтобы контролировать стратегию записи журнала повтора, параметр innodb_flush_log_at_trx_commit может иметь следующие значения:
0: фиксация транзакции только в буфере журнала повтора;
1: фиксация транзакции с одновременной записью на диск;
2: фиксация транзакции с записью в кэш страниц файловой системы.

#### Записывание на диск

Фактическая операция fsync, которая записывает данные на диск, происходит в следующих случаях:
* каждые 1 секунду фоновый поток проверяет наличие необходимости записи;
* при значении innodb_flush_log_at_trx_commit равном 1 запись происходит при каждой фиксации транзакции;
* размер буфера журнала повтора достигает половины значения innodb_log_buffer_size, также инициируется запись.

### Журнал отката (undo log)

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

#### Функции

* Поддержка атомарности транзакций путём сохранения версии данных перед изменениями.
* Предоставление многоверсионного управления параллелизмом (MVCC) для чтения без блокировки.

### Индексы

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

#### Значение индексов

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

#### Преимущества

* Значительное сокращение объёма данных, сканируемых сервером.
* Повышение эффективности поиска данных за счёт преобразования случайного ввода-вывода в последовательный.
* Возможность избежать использования временных таблиц при сортировке и группировке данных, что снижает нагрузку на процессор.

#### Недостатки

* Индексы занимают дисковое пространство.
* Хотя индексы ускоряют поиск данных, они замедляют операции обновления таблицы.

#### Типы структур данных

##### Hash-индекс

Принцип работы hash-индекса заключается в использовании хэш-функции для получения хеш-значения индекса, которое затем сохраняется в хэш-таблице. Во время поиска значение индекса хэшируется, и полученный хеш сравнивается с данными в хэш-таблице. Благодаря указателю на файл, можно быстро найти нужную запись, затрачивая примерно одно обращение к диску. **B-Tree индекс**

**Предыстория**: время сложности поиска двоичного дерева поиска составляет O(logN), скорость поиска самая высокая, а количество сравнений наименьшее. Однако для использования в качестве индекса базы данных, когда объём данных слишком велик, невозможно загрузить все индексы в память, использование бинарного дерева может привести к слишком частому дисковому вводу-выводу, и в худшем случае частота ввода-вывода диска определяется высотой дерева.

**B-дерево (сбалансированное многопутевое дерево поиска)** расширяет бинарное дерево по горизонтали, что позволяет эффективно решать проблему высоты, оставшуюся от красно-чёрного дерева, делая структуру дерева более «приземистой» и позволяя выполнять больше операций ввода-вывода за один раз в памяти, уменьшая количество операций ввода-вывода на диск и делая его более подходящим для больших баз данных. Однако, чтобы поддерживать баланс, вставка или удаление элементов могут вызвать расщепление узлов, что иногда может быть очень хлопотно.

*Рисунок 1: Структура B-дерева*

**Проблемы**:

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

**Анализ случая**: имитация процесса поиска ключа 10 данных.

*Рисунок 2: Анализ случая B-дерева*

1. Согласно указателю корневого узла, прочитайте корневой блок диска 1 файла каталога. [Операция ввода-вывода с диском 1]
2. Блок диска 1 хранит 15, 45 и три указателя данных. Мы обнаружили, что 10 < 15, поэтому мы нашли указатель p1.
3. В соответствии с указателем p1, мы позиционируем и считываем блок диска 2. [Операция ввода-вывода с диском 2]
4. Блок диска 2 хранит 7, 12 и три указателя данных. Мы обнаруживаем, что 7 < 10 < 12, поэтому находим указатель p2.
5. В соответствии с указателем p2, мы позиционируем и считываем блок диска 6. [Операция ввода-вывода с диском 3]
6. Блок диска 6 хранит 8, 10. Мы находим 10 и получаем данные, соответствующие 10.

### B+дерево

B+дерево — это разновидность многопутевого дерева поиска, которое также является разновидностью B-дерева. Его определение в основном такое же, как у B-дерева, но по сравнению с B-деревом, B+дерево имеет следующие отличия:

— Количество нелистовых узлов равно количеству указателей на дочерние узлы.
— Указатели на нелистовые узлы указывают на диапазон значений ключей [K[i], K[i+1]).
— Для всех листовых узлов добавляется один указатель цепочки.
— Все ключевые слова появляются в листовых узлах.
— Внутренние узлы не хранят данные, только ключи.

*Рисунок 3: Структура B+дерева*

**Преимущества**:

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

**Недостатки**:

Основная проблема производительности B+дерева заключается в том, что оно генерирует большое количество случайных операций ввода-вывода, которые возникают в следующих двух случаях:

— Первичный ключ не является упорядоченным и возрастающим, что приводит к большому количеству перемещений данных и фрагментации пространства при каждой вставке данных.
— Даже если первичный ключ упорядочен и возрастает, большое количество запросов на запись всё ещё являются случайными.

**Случай анализа: запрос равных значений**

Предположим, мы запрашиваем данные со значением 9. Путь запроса: блок диска 1 → блок диска 2 → блок диска 6.

*Рисунок 4: B+дерево, запрос равных значений*

1. Первая операция ввода-вывода: загрузите блок диска 1 в память и выполните сравнение в памяти с начала. 9 < 15, перейдите налево, до блока диска 2 адреса диска.
2. Вторая операция ввода-вывода: загрузите блок диска 2 в память и сравните в памяти с начала. 7 < 9 < 12, перейдите к блоку диска 6 адреса диска.
3. Третья операция ввода-вывода: загрузите блок диска 6 в память и найдите 9 в третьем индексе. Получите данные и завершите запрос. Если данные хранятся в виде адреса диска, вам также необходимо найти данные в соответствии с адресом диска, и запрос завершится. (Здесь необходимо различать, что в InnoDB данные хранятся в строках, а в MyIsam хранятся адреса дисков.)

**Пример анализа: запрос диапазона**

Если мы хотим запросить данные между 9 и 26, путь запроса будет следующим: блок диска 1 → блок диска 2 → блок диска 6 → блок диска 7.

*Рисунок 5: Пример анализа B+дерева: запрос диапазона*

1. Сначала найдите данные со значением 9, сохраните их в наборе результатов. Этот процесс такой же, как и предыдущий запрос равных значений, и происходит три операции ввода-вывода.
2. После нахождения 15 нижний листовой узел представляет собой упорядоченный список, начиная с блока диска 6, ключа значения 9, и выполняет поиск в обратном направлении, чтобы отфильтровать все данные, удовлетворяющие условиям фильтрации.
3. Четвёртая операция ввода-вывода: согласно адресу диска после блока диска 6, перейдите к блоку диска 7 адреса диска и загрузите его в память. Сравните в памяти с начала, 9 < 25 < 26, 9 < 26 ≤ 26, сохраните данные в наборе результатов.
4. Первичный ключ уникален (после этого не будет данных ≤ 26), нет необходимости продолжать поиск, завершить запрос. Вернуть набор результатов пользователю.

### B*дерево

Это разновидность B+дерева, которая добавляет указатели на соседние узлы к нелистовым и некорневым узлам.

*Рисунок 6: Структура индекса B*дерева*

В B+дереве количество ключевых слов в нелистовом узле должно быть не менее половины M, то есть минимальная степень использования узла составляет 1/2.

**Разделение B+дерева**

Когда узел заполнен, выделите новый узел, скопируйте половину данных исходного узла в новый узел и добавьте указатель нового узла к родительскому узлу; разделение B+дерева влияет только на исходный узел и родительский узел, не затрагивая соседние узлы, поэтому ему не нужно добавлять указатели соседних узлов.

**Разделение B*дерева**

Когда узел заполняется, если следующий соседний узел не заполнен, переместите часть данных в соседний узел, вставьте ключ в исходный узел, а затем измените ключ соседнего узла в родительском узле (поскольку диапазон ключа соседнего узла изменился); если соседний узел также заполнен, добавьте новый узел между исходным узлом и соседним узлом, скопируйте одну треть данных в каждый из них и добавьте указатели нового узла в родительский узел; таким образом, вероятность выделения нового узла в B*дереве ниже, чем в B+дереве, и коэффициент использования пространства выше.

### R-дерево индекса

Индекс пространственных данных (R-дерево), MyISAM поддерживает пространственные индексы, которые можно использовать для хранения географических данных. **Полнотекстовый индекс** — это индекс, который позволяет выполнять поиск по содержимому текстовых полей в таблице. Полнотекстовые индексы используются для ускорения поиска по текстовым данным и могут быть созданы для столбцов типа `varchar`, `char` или `text`.

Создать или изменить полнотекстовый индекс можно с помощью команд `ALTER TABLE` или `CREATE INDEX`. Для больших наборов данных создание полнотекстового индекса через `ALTER TABLE` (или `CREATE INDEX`) может быть быстрее, чем вставка записей в таблицу с уже существующим полнотекстовым индексом.

В MySQL поддержка полнотекстовых индексов обеспечивается типом таблиц `MyISAM` и начиная с версии 5.6 типом `InnoDB`. Однако следует учитывать, что **полнотекстовые индексы не поддерживают китайский язык**. Для обработки китайского языка необходимо использовать Sphinx (Coreseek) или «Синьсюэ» (`迅搜`).

Пример создания таблицы с полнотекстовым индексом:
```mysql
-- Создание таблицы, подходящей для добавления полнотекстового индекса
CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
);

Индекс MyISAM

Данные и индексы в файлах MyISAM хранятся отдельно. При построении индекса B+-дерева в MyISAM значения ключей в листовых узлах представляют собой значения индексированных столбцов, а данные — адреса строк на диске.

• Первичный ключ

На рисунке ниже показан пример первичного ключа в таблице user. Индекс хранится в файле user.MYI, а данные — в файле user.MYD. Анализ запроса при поиске по первичному ключу:

1. Поиск начинается с корневого узла первичного ключа. Корневой узел загружается в память, и выполняется сравнение 28 < 75. Происходит переход влево.

2. Левый дочерний узел также загружается в память. Выполняется сравнение 16 < 28 < 47. Происходит спуск вниз.

3. Достигается листовой узел. Узел загружается в память и перебирается. Выполняются сравнения 16 < 28, 18 < 28 и 28 = 28. Находит значение, равное 30, в элементе индекса.

4. Из элемента индекса извлекается адрес на диске, после чего из файла данных user.MYD извлекается соответствующая целая запись.

5. Запись возвращается клиенту.

Количество операций ввода-вывода: 3 раза для поиска индекса и 1 раз для получения записи.

• Вторичный ключ

Анализ запроса при поиске в диапазоне по вторичному ключу:

1. Начинается с корневого узла вторичного ключа. Корневой узел загружается в память, и выполняется сравнение 28 < 75, происходит переход влево.

2. Левый дочерний узел загружается в память. Выполняется сравнение 16 < 28 < 47, происходит спуск вниз.

3. Достигается листовой узел. Узел загружается в память и перебирается. Выполняются сравнения 16 < 28, 18 < 28 и 28 = 28 < 47. Находится значение, равное 28, в элементе индекса.

4. По адресу на диске из файла данных извлекается строка.

5. Поскольку запрос ищет диапазон, необходимо пройти по нижнему уровню листового списка до тех пор, пока не будет найдено значение, не удовлетворяющее условию фильтра. В данном случае происходит переход к следующему узлу.

6. Следующий узел загружается в память. Выполняется сравнение 28 < 47 = 47. По адресу на диске извлекается вторая строка.

7. Получены две строки, удовлетворяющие условиям фильтра. Результат запроса возвращается клиенту.

Количество операций ввода-вывода: 4 раза для поиска индекса и 2 раза для получения записей.

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

• Вторичные ключи

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

Индекс InnoDB

Каждая таблица InnoDB имеет первичный ключ, который используется для построения B+-дерева. Первичный ключ обычно является кластеризованным индексом, создаваемым автоматически при определении первичного ключа или выборе уникального непустого столбца для его формирования. Если первичный ключ не определён, InnoDB выбирает первый непустой уникальный столбец для создания кластеризованного индекса или использует скрытый 6-байтовый целочисленный столбец ROWID для формирования кластеризованного индекса. Этот столбец автоматически увеличивается при вставке новых строк.

Все остальные индексы, кроме кластеризованных, называются вторичными. В InnoDB вторичные индексы содержат значения первичных ключей. Во время поиска InnoDB использует эти значения для поиска соответствующих строк в кластеризованном индексе.

Листовые узлы кластеризованного индекса содержат полные строки данных, тогда как листовые узлы вторичного индекса содержат только значения первичного ключа.

При поиске равных значений:

1. Запрос начинается с корневого узла кластеризованного индекса. Корневой узел загружается в память, выполняется сравнение 28 < 75 и происходит переход влево.

2. Левый дочерний узел загружается в память. Выполняется сравнение 16 < 28 < 47 и происходит спуск вниз.

3. Достигается листовой узел. Узел загружается в память и перебирается. Выполняются сравнения 16 < 28, 18 < 28 и 28 = 28. Находится значение 28 в элементе кластера.

4. Целая запись извлекается напрямую. Запись возвращается клиенту.

Количество операций ввода-вывода: 3 раза.

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

Например, если взять таблицу user_innodb и рассмотреть индекс по столбцу age, то структура индекса будет выглядеть следующим образом:

Листовые узлы вторичного индекса отсортированы по возрастанию значений age и дополнительно по id в случае равенства age.

Поиск равных значений:

1. Используя вторичный индекс, находится значение первичного ключа (в данном случае id) по заданному значению age.

2. С помощью найденного значения id осуществляется поиск в кластеризованном индексе и извлекается полная запись.

Количество операций ввода-вывода: поиск по вторичному индексу — 3 раза, поиск по кластеризованному индексу — 3 раза.

Также существует возможность создания комбинированных индексов. Например, для таблицы abc_innodb:

CREATE TABLE abc_innodb (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
  c varchar(10) DEFAULT NULL,
  d varchar(10) DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE,
  KEY idx_abc (a, b, c)
) ENGINE = InnoDB;

Комбинированный индекс имеет следующую структуру:

Процесс поиска с использованием комбинированного индекса:

Запрос: select * from abc_innodb where a = 13 and b = 16 and c = 4;

1. Сравнение значения a определяет направление поиска в комбинированном индексе: налево или направо.

2. Если значение a совпадает, сравниваются значения b.

3. Если значения a и b совпадают, сравниваются значения c.

Если в запросе отсутствует значение a, комбинированный индекс не сможет определить, с какого узла начать поиск.

Можно сказать, что созданный индекс idx_abc(a,b,c) эквивалентен трём отдельным индексам: (a), (a,b), (a,b,c), и поиск будет осуществляться в соответствии с принципом самого левого префикса.

Принцип самого левого префикса означает, что MySQL будет продолжать сопоставление элементов индекса справа до тех пор, пока не встретит условие диапазона (>,<,between,like), после чего сопоставление прекратится. Когда вставляются данные, при возникновении ошибки, например, при повторении данных, не будет возвращаться ошибка, а только предупреждение. Поэтому, используя ignore, убедитесь, что сам оператор не имеет проблем, иначе он также будет проигнорирован. Например:

INSERT IGNORE INTO user (name) VALUES ('telami') 

Этот метод очень удобен, но есть вероятность того, что ошибка будет проигнорирована не из-за повторения данных, а по другой причине.

Решение 2: on duplicate key update

Если primary или unique ключ повторяется, то выполняется update, если update является бесполезным оператором, таким как id = id, то результат будет таким же, как и при использовании 1. В бэкэнде публичного аккаунта архитектора после ответа «архитектура чистая» можно получить подарочный пакет. Например, чтобы реализовать вставку данных без ошибок при повторении name, можно использовать следующий оператор:

INSERT INTO user (name) VALUES ('telami') ON duplicate KEY UPDATE id = id 

У этого метода есть предварительное условие: вставляемые ограничения должны быть первичными ключами или уникальными ограничениями (в вашем бизнесе те, которые должны использоваться в качестве уникальных критериев, должны быть установлены как уникальные ключи, т. е. unique key).

Решение 3: insert … select … where not exist

Можно определить, следует ли вставлять данные на основе условия select, и можно использовать не только primary и unique, но и другие условия. Например:

INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1) 

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

Решение 4: replace into

Сначала удаляются существующие записи с одинаковыми первичными или уникальными значениями, затем вставляется новая запись.

REPLACE INTO user SELECT 1, 'telami' FROM books 

В этом методе независимо от того, существуют ли одинаковые записи, они сначала удаляются, а затем вставляются. Выбран второй способ:

<insert id="batchSaveUser" parameterType="list">
    insert into user (id,username,mobile_number)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (
            #{item.id},
            #{item.username},
            #{item.mobileNumber}
        )
    </foreach>
    ON duplicate KEY UPDATE id = id
</insert>

Здесь используется Mybatis для выполнения операции пакетной вставки, mobile_number уже имеет уникальное ограничение. Таким образом, при пакетной вставке, если существует тот же номер мобильного телефона, он больше не будет вставлен.

Анализ сценариев

Случай 1: Самое левое соответствие

Индекс:

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL-оператор:

select * from _t where orderno='xxx';

Запрос соответствует слева направо, необходимо использовать order_no для доступа к индексу, либо индекс (shop_id, order_no) должен быть переупорядочен.

Случай 2: Неявное преобразование

Индекс:

KEY `idx_mobile` (`mobile`)

SQL-оператор:

select * from _user where mobile=12345678901;

Неявное преобразование аналогично выполнению операций над индексом, что приводит к его неработоспособности. mobile — это символьный тип, использующий числовые значения, следует использовать строковое сопоставление, в противном случае MySQL будет использовать неявную замену, что приведёт к неработоспособности индекса.

Случай 3: Большая страница

Индекс:

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL-оператор:

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

Для больших страниц можно сначала оптимизировать требования продукта. Если нет оптимизации, есть два способа оптимизации:

• Передать предыдущую последнюю запись, а затем выполнить обработку «c < xxx», но обычно требуется изменить протокол интерфейса, и это не всегда возможно.
• Использовать отложенное связывание для обработки, уменьшая количество SQL-запросов, но необходимо помнить, что индекс должен полностью покрывать его, и SQL должен быть изменён следующим образом:

SELECT t1.* FROM _t t1, (SELECT id FROM _t WHERE a=1 AND b=2 ORDER BY c DESC LIMIT 10000,10) t2  WHERE t1.id=t2.id;

Случай 4: in + order by

Индекс:

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL-оператор:

SELECT * FROM _order WHERE shop_id = 1 AND order_status IN ( 1, 2, 3 ) ORDER BY created_at DESC LIMIT 10

in-запрос в MySQL на самом деле реализуется через n * m, аналогично union, но эффективность выше. in-запросы участвуют в вычислении стоимости (стоимость = количество кортежей * среднее значение IO), путём разделения in на содержащиеся значения и поиска количества кортежей один за другим, поэтому этот процесс вычисления относительно медленный, поэтому MySQL устанавливает пороговое значение (eq_range_index_dive_limit), которое превышает 200. После превышения этого порога стоимость in не участвует в расчёте.

Это может привести к неправильному выбору плана выполнения. По умолчанию это 200, то есть, когда in содержит более 200 значений, расчёт стоимости in может быть проблематичным, что может привести к выбору неправильного индекса MySQL. Способ решения проблемы заключается в изменении порядка (order_status, created_at), а также в настройке SQL для отложенного связывания.

Случай 5: Блокировка диапазона запросов

Блокировка диапазона запросов, последующие поля не могут использовать индекс.

Индекс:

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL-оператор:

SELECT * FROM _order WHERE shop_id=1 AND created_at > '2021-01-01 00:00:00' AND order_status=10;

Диапазон запросов также включает «IN, between».

Случай 6: Избегайте использования небыстрого индекса

Не равно, не содержит, нельзя использовать быстрый поиск по индексу.

select * from _order where shop_id=1 and order_status not in (1,2);
select * from _order where shop_id=1 and order_status != 1;

При использовании индексов избегайте использования NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE и других.

Случай 7: Оптимизатор выбирает неработающий индекс

Если требуемый объём данных невелик, оптимизатор всё равно выберет вспомогательный индекс, но когда объём данных составляет большую часть таблицы (обычно около 20%), оптимизатор выберет использование агрегатного индекса для поиска данных.

select * from _order where  order_status = 1

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

Случай 8: Сложные запросы

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

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

Случай 9: Смешивание asc и desc

select * from _t where a=1 order by b desc, c asc

Смешивание desc и asc может привести к неработоспособности индекса.

Случай 10: Большие данные

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

В тексте рассказывается о группе для обмена знаниями по архитектуре, где можно найти видео с анализом исходного кода Spring, MyBatis, Netty, а также информацию о микросервисной архитектуре, высокопроизводительных и распределённых системах, оптимизации JVM и других важных аспектах архитектуры. Также в тексте говорится о том, что участники группы могут получить бесплатные ресурсы для обучения.

Далее в тексте описывается роль кластера MySQL Master-Slave в повышении производительности, доступности и отказоустойчивости системы. Описывается процесс работы кластера:

• выполнение операций чтения и записи на главной базе данных (Master);
• выполнение только операций чтения на подчинённой базе данных (Slave).

Также в тексте приводится информация о binlog — журнале операций записи, который используется для репликации данных между главным и подчинённым серверами MySQL. В тексте описываются три формата binlog: STATEMENT, ROW и MIXED.

Затем в тексте рассматривается процесс репликации данных в MySQL, который включает в себя три потока:

1. Binlog dump — поток, который записывает операции в binlog на главном сервере.
2. I/O thread — поток на подчинённом сервере, который запрашивает данные из binlog и записывает их в локальный журнал.
3. SQL thread — поток на подчинённом сервере, который считывает данные из локального журнала и выполняет их на локальной базе данных.

Кроме того, в тексте упоминается о параллельной репликации, которая позволяет ускорить процесс копирования данных за счёт одновременного выполнения операций на нескольких потоках.

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

Согласно обычной стратегии, разделение чтения и записи реализуется так: основная база данных обеспечивает возможность записи, а дополнительная — возможность чтения. Большое количество запросов выполняется на дополнительной базе данных, что приводит к значительному использованию ресурсов процессора и, как следствие, влияет на скорость синхронизации и вызывает задержку между основной и дополнительной базами данных. В этой ситуации можно распределить нагрузку чтения между несколькими дополнительными базами данных или вывести binlog во внешнюю систему, такую как Hadoop, чтобы внешняя система обеспечивала возможность запроса.

Выполнение больших транзакций

Если выполняется большая транзакция, основная база данных должна дождаться завершения транзакции, прежде чем записывать данные в binlog. Например, если в основной базе данных выполняется операция вставки с большим объёмом данных, которая генерирует binlog-файл размером в несколько сотен гигабайт, передаваемый на узел только для чтения, это может привести к задержке обработки binlog на узле только для чтения. Поэтому администраторы баз данных часто напоминают разработчикам о том, что не следует использовать оператор delete для удаления большого объёма данных за один раз, а лучше контролировать количество удаляемых данных и выполнять операцию удаления порциями.

DDL-операции в основной базе данных

Синхронизация DDL-операций (alter, drop, create) между узлом только для чтения и основной базой данных происходит последовательно. Если DDL-операция занимает много времени в основной базе данных, то же самое время будет затрачено и на узле только для чтения. Например, добавление поля в таблицу с 500 000 записей в основной базе данных заняло 10 минут, то же время потребуется и на узле только для чтения.

Если на узле только для чтения выполняется запрос с длительным временем выполнения, этот запрос заблокирует DDL-запросы из основной базы данных до своего завершения, что приведёт к задержке данных на узле только для чтения.

Конфликты блокировок

Проблемы с конфликтами блокировок также могут замедлить выполнение SQL-потоков на узлах только для чтения. Это может произойти, например, при выполнении запросов select ... for update или при использовании движка MyISAM.

Скорость репликации на узлах только для чтения

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

До версии MySQL 5.6, обновление логики SQL-потока на узле только для чтения поддерживало только однопоточное выполнение. При высокой параллельности и большом количестве транзакций в секунду в основной базе данных это могло привести к значительной задержке между основной и дополнительной базой данных. Начиная с версии MySQL 5.7, поддерживается параллельная репликация. На сервере узла только для чтения можно установить значение slave_parallel_workers больше нуля и настроить параметр slave_parallel_type на LOGICAL_CLOCK.

mysql> show variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+

Как уменьшить задержку между основной и дополнительной базой данных?

Проблема синхронизации между основной и дополнительной базой данных всегда является компромиссом между согласованностью и производительностью. Решение зависит от конкретной ситуации использования. Чтобы уменьшить задержку, можно предпринять следующие шаги:

• Уменьшить вероятность одновременного выполнения больших транзакций, оптимизировать бизнес-логику.
• Оптимизировать SQL, избегать медленных запросов, уменьшить объём пакетных операций, использовать скрипты для обновления данных с паузами между операциями.
• Улучшить конфигурацию сервера узла только для чтения, чтобы уменьшить разницу в эффективности записи binlog в основную базу данных и чтения binlog узлом только для чтения.
• Использовать короткие каналы связи между основной и дополнительной базой данных, улучшить пропускную способность портов, чтобы сократить задержки передачи binlog по сети.
• Для критически важных операций чтения использовать основную базу данных, а дополнительную базу данных оставить только для резервного копирования и аварийного восстановления.