Вопросы и краткие ответы

MySQL

Архитектура

Компоненты: SQL-интерфейс, парсер, оптимизатор, кэш, движок хранения ! Схема архитектуры

• Connectors: взаимодействие с SQL на разных языках
• Management Serveices & Utilities: инструменты управления и контроля системы
• Connection Pool: пулы подключений
• SQL Interface: SQL-интерфейс
• Parser: парсер
• Optimizer: оптимизатор запросов
• Cache и Buffer: кэши запросов
• Engine: движок хранения Дополнительная литература [«Архитектура MySQL»](./02. MySQL архитектура.md)

Основные операции

Управление базами данных

Подключение к базе данных, просмотр всех баз, выбор базы, создание базы, удаление базы Просмотр всех таблиц, просмотр структуры таблицы, создание таблицы, удаление таблицы Добавление столбца, удаление столбца, изменение столбца

CRUD

INSERT, SELECT, UPDATE, DELETE

Одностраничный запрос

Все поля, указанные поля, WHERE, IN, BETWEEN AND, LIKE, AND, OR, DISTINCT, ORDER BY, GROUP BY, LIMIT

Многотабличный запрос

Объединение запросов, подзапросы, объединение результатов

Нормальные формы баз данных

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

Основные принципы проектирования баз данных

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

Различия между типами данных char и varchar

• char: хорошо подходит для хранения часто изменяемых значений или значений с фиксированной длиной, таких как type, IP-адрес или MD5. Не склонен к образованию фрагментов
• varchar: хорошо подходит для хранения значений с разной длиной, это самый часто используемый тип данных, экономит место на диске, хранит переменную длину строк. Это также рекомендуемый тип данных InnoDB

LEFT JOIN, RIGHT JOIN, INNER JOIN

• LEFT JOIN (левое соединение): получает все записи из левой таблицы, даже если в правой таблице нет соответствующих записей
• RIGHT JOIN (правое соединение): противоположно LEFT JOIN, используется для получения всех записей из правой таблицы, даже если в левой таблице нет соответствующих записей
• INNER JOIN (внутреннее соединение): получает записи, соответствующие полям из двух таблиц Дополнительная литература [«Использование соединений в MySQL»](. /01. MySQL соединения. md)

UNION, UNION ALL

• Оператор UNION используется для объединения результатов двух и более SELECT-запросов в один набор результатов. Множественные SELECT-запросы удаляют повторяющиеся данные.
• Оператор UNION ALL отображает все повторяющиеся данные без удаления дубликатов.

Часто используемые функции MySQL

Математические функции

• floor(x) Возвращает наибольшее целое число, не превышающее x.
• ceil/ceiling(x) Возвращает наименьшее целое число, не меньшее x.
• round(x) Округляет число.
• rand() Генерирует случайное число в диапазоне [0, 1).
• abs(x) Возвращает абсолютное значение x.

Строковые функции

• concat(str1, str2, . . . ) Объединяет параметры в строку.
• length(str) Возвращает длину строки.

Функции даты и времени

• now() Возвращает текущее время.
• curdate() Возвращает текущую дату.

SELECT UNIX_TIMESTAMP('2019-05-07 22:55:00'); #1557240900
SELECT FROM_UNIXTIME(1557240900); #2019-05-07 22:55:00

Системные функции

• VERSION() Возвращает версию базы данных.
• LAST_INSERT_ID() Возвращает последнее сгенерированное значение AUTO_INCREMENT.

Криптографические функции

• PASSWORD(str) Шифрует строку str.
• MD5(str) Шифрует строку str.

Форматирующие функции

• FORMAT(x, n) Форматирует число x, сохраняя n знаков после запятой, округляя до ближайшего значения.

SELECT FORMAT(2.7895, 2); #2.79

Закрытие

Цели Многие запросы, которые одновременно изменяют данные, могут вызвать проблемы с параллелизацией. Использование блокировок позволяет эффективно решать эту проблему.

Читательские и записные блокировки

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

Размер блокировки

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

Оптимистичная блокировка

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

Транзакции

Транзакция — это набор атомарных SQL-запросов или независимый рабочий блок. Запросы в транзакции либо все выполняются успешно, либо все завершаются ошибкой. Свойства ACID: атомарность (atomicity), согласованность (consistency), изолированность (isolation), устойчивость (durability).

Уровни изоляции

• Читать несогласованные (READ UNCOMMITTED): изменения в транзакции, которые еще не были подтверждены, видны другим транзакциям.

"Нечистая" (Dirty) чтение: транзакция читает несогласованные данные.

• Читать согласованные (READ COMMITTED): изменения, которые еще не были подтверждены, видны только текущей транзакции.Два одинаковых запроса могут вернуть разные результаты.
• Повторяемое чтение (REPEATABLE READ): повторное выполнение одного и того же запроса возвращает одинаковые результаты. Решает проблему "нечистого" чтения.

По умолчанию MySQL использует уровень изоляции REPEATABLE READ. - Сериализация (SERIALIZABLE): принудительно выполняет транзакции последовательно.

Дедлок

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

Транзакционный журнал

Транзакционный журнал помогает повысить эффективность транзакций.

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

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

Популярные движки хранения

InnoDB

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

MyISAM

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

Популярные индексы

Концепция индекса

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

Классификация индексов

! [Классификация индексов](. /assets/index. png)

Создание индекса

 ALTER TABLE `table_name` ADD INDEX index_name (`column`); #Обычный индекс

ALTER TABLE `table_name` ADD UNIQUE (`column`); #Уникальный индекс

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`); #Первичный ключ

ALTER TABLE `table_name` ADD FULLTEXT (`column`); #Полнотекстовый индекс

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`); #Комбинированный индекс

Различия индексов

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

Разница между кластеризованными и некластеризованными индексами

• По физическому хранению: кластеризованный индекс (clustered index), некластеризованный индекс (non-clustered index).
• Листовые узлы кластеризованного индекса содержат данные, в то время как листовые узлы некластеризованного индекса содержат указатели на соответствующие блоки данных.

Принципы работы BTree и BTree-/BTree+ индексов

• BTree Двоичное дерево имеет очень высокую высоту, поэтому логически близкие узлы могут быть очень далеко физически, что затрудняет использование локальности, увеличивает количество операций ввода-вывода и снижает скорость поиска.
• BTree- Каждый узел представляет собой двоичный массив [key, data], все узлы могут хранить данные, key является индексом, а data — данными вне индекса. Вставка или удаление данных нарушает свойства BTree, поэтому при вставке данных требуется их разделение, объединение и перемещение для поддержания свойств BTree, что приводит к частым операциям ввода-вывода.
• BTree+ Нелистовые узлы не содержат data, а только ключи индекса, только листовые узлы содержат data.
• BTree+ в MySQL На основе классического BTree+ были внесены оптимизации, добавлены указатели последовательного доступа. В каждом листовом узле BTree+ добавлен указатель на соседний листовой узел, что приводит к улучшению производительности доступа к диапазону.

Количество таблиц для разделения

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

Формат вывода EXPLAIN

Конфигурация my.cnf

Медленные запросы