ISAM, HEAP, MyISAM, InnoDB
| Описание | MyISAM | InnoDB |
|---|---|---|
| Транзакции | Нет | Да |
| Внешние ключи | Нет | Да |
| Блокировки | На уровне таблиц | На уровне строк |
| Одновременные запросы к разным частям таблицы | Медленнее | Быстрее |
| Cмешанная нагрузка при (SELECT/UPDATE/DELETE/INSERT) | Медленнее | Быстрее |
| Операция INSERT | Быстрее | Медленнее |
| Если преобладают операции чтения (SELECT) | Работает быстрее | Работает медленнее |
| Deadlock | Не возникают | Возможны |
| Запрос вида SELECT count(*) | Быстрее | Медленнее |
| Файловое хранение таблиц | Каждая таблица в отдельном файле | По умолчанию данные хранятся в больших совместно используемых файлах, но возможно хранение каждой таблицы в отдельном файле |
| Размер занимаемого места на диске | Меньше | Больше (примерно в 1,5 раза) |
| Поведение в случае сбоя | Ломается вся таблица | Можно восстановить по логам транзакций |
| Блокировка при записи | Таблица | Строка |
| Где применять | Аналитики Веб-сайты с большим количеством запросов на чтение |
Электронная коммерция Финансовые системы Системы управления заказами. |
Поле в таблице, которое однозначно идентифицирует каждую строку/запись в таблице базы данных. Содержит уникальные значения и не может иметь значения NULL.
Позволяет установить связи между таблицами, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
5. Для чего создаются индексы в базе данных, как работают индексы, какие бывают, чем отличаются, как влияют на производительность?
Индексы отсортированный набор значений, создаются с целью повышения производительности поиска данных.
Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы, таким образом позволяет искать строки, удовлетворяющие критерию поиска.
Типы индексов:
B-tree (Balanced Tree) Index - Наиболее распространенный тип индекса, поддерживающий упорядоченные данные.
Когда использовать: Для поиска по диапазону, сортировки, а также для большинства общих запросов.
Примеры: Индексы по первичным ключам и уникальным полям.
Hash Index - Основан на хеш-таблицах, обеспечивает быстрый доступ к данным по точным значениям ключа.
Когда использовать: Для поиска по точным значениям, но не поддерживает диапазонные запросы или сортировку.
Примеры: Кэширование, поиска по точным совпадениям.
Bitmap Index - Использует битовые карты для индексирования данных.
Когда использовать: Для столбцов с низкой кардинальностью, таких как булевы или категорийные поля.
Примеры: Аналитические запросы в хранилищах данных.
Full-Text Index - Специализированный индекс для полнотекстового поиска.
Когда использовать: Для поиска слов или фраз в текстовых данных.
Примеры: Системы поиска по текстам, блоги, новостные сайты.
Spatial Index - Используется для индексирования геопространственных данных.
Когда использовать: Для работы с географическими координатами и запросами, такими как ближайшие соседи или пересечения.
Примеры: Геоинформационные системы (ГИС), приложения для карт и навигации.
Unique Index - Обеспечивает уникальность значений в индексируемом столбце.
Когда использовать: Для полей, которые должны содержать только уникальные значения.
Примеры: Поля с идентификаторами пользователей, адреса электронной почты.
Composite (Multi-Column) Index - Индекс, созданный на основе нескольких столбцов.
Когда использовать: Для оптимизации запросов, которые фильтруются или сортируются по нескольким столбцам.
Примеры: Запросы, которые часто фильтруются по комбинации нескольких полей.
Partial (Filtered) Index - Индекс, созданный на подмножестве строк таблицы, удовлетворяющих определенному условию
Когда использовать: Для улучшения производительности запросов, которые обращаются к частому подмножеству данных.
Примеры: Индексирование только активных пользователей или записей с ненулевым значением.
Влияние на производительность:
Положительное влияние:
- Ускорение операций чтения.
- Улучшение производительности соединений.
- Ускорение агрегатных функций.
Отрицательное влияние:
- Замедление операций записи.
- Увеличение использования дискового пространства.
- Сложность управления.
Выбор оптимальных индексов зависит от структуры и запросов к конкретной таблице базы данных.
Виды индексов: для поиска, сортировки, уникальные, для полнотекстового поиска.
Общие правила построение индексов:
-
Индексировать столбцы, используемые в предикатах WHERE, JOIN и ORDER BY
-
Избегать излишнего индексирования: Не стоит индексировать каждый столбец в таблице, особенно если они не используются в запросах или имеют низкую селективность (т.е. мало уникальных значений). Индексы требуют дополнительного пространства и влияют на производительность операций вставки, обновления и удаления данных, поэтому лучше выбирать индексы осознанно и оптимизировать их использование.
-
Учитывайте размер и объем данных: Если таблица очень маленькая или содержит небольшое количество записей, может не быть необходимости в индексах.
-
Анализируйте производительность запросов: Используйте профилирование запросов и инструменты анализа производительности для идентификации узких мест и определения, где необходимо добавить индексы. Проверяйте планы выполнения запросов и ищите места, где полные сканирования таблиц можно заменить использованием индексов.
-
Учитывайте изменяемость данных: Если данные в таблице часто изменяются (вставка, обновление, удаление), то индексы также должны обновляться, что может замедлить операции записи. В таких случаях необходимо тщательно балансировать потребности в индексировании и производительности операций изменения данных.
При использовании составных индексов порядок полей в индексе имеет определяющее значение.
Один из основных параметров, характеризующий индекс — селективность(selectivity) - определяется количеством записей в таблице с одинаковыми значениями. Когда записей c уникальными значениями много(с одинаковым значением мало) – селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.
- INNER JOIN: возвращает строки, когда есть совпадение в обеих таблицах.
- LEFT JOIN: возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.
- RIGHT JOIN: возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.
WHERE - используется для фильтрации строк перед выполнением группировки
HAVING - используется для фильтрации результатов после выполнения группировки и агрегатных функций
Основное отличие между HAVING и WHERE заключается в том, что WHERE применяется к отдельным строкам данных до группировки, а HAVING применяется к группам данных после группировки.
Блок операторов SQL, который в случае ошибки в одном запросе, возвращается к предыдущему состоянию (Rollback), и только в случае выполнения всех запросов подтверждается (Commit);
Ситуация в многозадачной среде или СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими процессами
READ UNCOMMITTED (неподтвержденное чтение): Это самый низкий уровень изоляции, при котором транзакции могут видеть изменения внесенные другими транзакциями, которые еще не были подтверждены. Этот уровень предоставляет самую низкую степень изоляции данных, но может привести к проблемам "грязного чтения" и "неповторяющегося чтения".
READ COMMITTED (подтвержденное чтение): Это уровень, при котором транзакции видят только изменения, подтвержденные другими транзакциями. Это предотвращает проблему "грязного чтения", но может все еще приводить к проблеме" неповторяющегося чтения".
REPEATABLE READ (повторяемое чтение): Это уровень, при котором транзакции видят согласованное состояние данных в пределах одной транзакции - это означает, что другие транзакции не могут изменять данные, которые были прочитаны текущей транзакцией. Если транзакция выполняет несколько запросов к одним и тем же данным, то она всегда будет видеть одни и те же значения этих данных. Это предотвращает проблемы "грязного чтения" и "неповторяющегося чтения", но может все еще возникать проблема "фантомного чтения".
SERIALIZABLE (сериализуемость): Это самый высокий уровень изоляции, при котором транзакции видят данные, как если бы они выполнялись последовательно одна за другой. Это предотвращает все проблемы изоляции данных, но может приводить к потере параллельности и снижению производительности при работе с большим количеством одновременных транзакций.
EXPLAIN может в точности рассказать, что происходит, когда вы выполняете запрос. Эта информация позволит обнаружить медленные запросы и сократить время, затрачиваемое на обработку запроса, что впоследствии может значительно ускорить работу вашего приложения
Команда GROUP BY группирует результаты при выборке из базы данных.
- Лексический анализ (Lexical analysis): Лексический анализатор считывает входной текст SQL запроса и разбивает его на лексемы (токены). Лексемы могут быть ключевыми словами (SELECT, FROM, WHERE), идентификаторами (названия таблиц, столбцов), операторами, значениями и другими элементами языка SQL.
- Синтаксический анализ (Syntax analysis): Анализатор проверяет синтаксическую корректность запроса, основываясь на грамматике SQL. Он строит синтаксическое дерево (парсерное дерево), которое представляет структуру запроса и его взаимосвязи между элементами. При этом проверяются правильность расстановки ключевых слов, операторов, скобок и других синтаксических правил.
- Семантический анализ (Semantic analysis): Анализатор проводит семантическую проверку запроса, основываясь на семантике SQL. Он проверяет правильность ссылок на таблицы и столбцы, типы данных, права доступа и другие семантические правила. Если обнаружены ошибки, генерируется сообщение об ошибке.
- Построение внутреннего представления (Internal representation): После успешного анализа, синтаксический анализатор строит внутреннее представление запроса, которое может быть использовано другими компонентами СУБД для выполнения запроса. Это может быть абстрактное синтаксическое дерево, план выполнения запроса или другая форма представления.
- Оптимизация запроса (Query optimization): Некоторые СУБД имеют шаг оптимизации запроса, который анализирует внутреннее представление и пытается найти оптимальный способ выполнения запроса, используя различные алгоритмы и эвристики. Это может включать выбор индексов, определение порядка выполнения операций и другие оптимизации.
Приоритеты операторов по убыванию:
- круглые скобки
- умножение (*), деление (/)
- сложение (+), вычитание (-)
- операторы сравнения (=, >, <, >=, <=, <>)
- NOT
- AND
- OR
Приоритеты операций по убыванию:
- FROM (выбор таблицы)
- JOIN (комбинация с подходящими по условию данными из других таблиц)
- WHERE (фильтрация строк)
- GROUP BY (агрегирование данных)
- HAVING (фильтрация агрегированных данных)
- SELECT (возврат результирующего датасета)
- DISTINCT (Убираются дубликаты из результатов)
- ORDER BY (сортировка).
- LIMIT/OFFSET (Ограничивается количество возвращаемых строк)
B-дерево (B-tree):
B-дерево является сбалансированным деревом поиска, где каждый узел может содержать несколько ключей и ссылок на дочерние узлы. Использует принцип бинарного поиска и используется для индексации данных. Индексы обычно используются для поиска диапазонов значений, сортировки данных и поддержки операций JOIN.
Хеш-индексы (hash map indexes):
Хеш-индекс использует хеш-функции для преобразования ключей данных в адреса в хеш-таблице. Хеш-таблица представляет собой структуру данных, где каждому ключу соответствует значение. Хеш-индексы обычно используются для точного поиска по ключу, а не для поиска диапазонов значений или сортировки данных. Однако, в отличие от B-дерева, хеш-индексы не поддерживают операции сортировки и JOIN.
Оператор LIKE в SQL обычно используется для поиска шаблонов в строковых значениях. Он позволяет искать значения, соответствующие заданному шаблону, который может содержать символы-маски. Оператор LIKE не является прямым сопоставлением ключа, а требует выполнения поиска с использованием регулярных выражений или подобных механизмов.
SELECT DISTINCT
ALTER TABLE table_name MODIFY COLUMN column_name data_type DEFAULT default_value constraint_name;
20. У нас есть одно поле id на нем есть индекс, одна запись, как выбрать поле чтобы индекс не отработал?
Использовать преобразование к типу varchar или использовать string в запросе.
В данном случае, когда условие WHERE сравнивает поле с большим количеством записей, использование индекса может быть неэффективным.
- Проверьте селективность условия: Убедитесь, что условие WHERE на самом деле ограничивает количество возвращаемых строк. Если условие выбирает большую часть или все записи, то использование индекса может не принести значительной выгоды.
- Оптимизируйте запрос: Проверьте план выполнения запроса и убедитесь, что он оптимальный.
- Разбейте запрос на части: Если условие WHERE неэффективно из-за большого количества данных, можно попробовать разбить запрос на несколько более мелких запросов с более узкими условиями, а затем объединить результаты.
- Используйте другие индексы: Проверьте, имеются ли другие поля, которые могут быть использованы для создания индекса, чтобы улучшить производительность запроса. Иногда изменение индекса или добавление дополнительных индексов может быть полезным.
Необходимо использовать выражение field_name > id Использование LIMIT OFFSET не стоит использовать на большие выборки из-за того что происходит сдвиг и индексы перестают работать.
В простом случае простым деление на количество серверов и брать остаток от деления. В более сложном, использовать консистентное хеширование
24. Есть таблица с 90 миллионов записей из неё нужно удалять данные которые старше 60 дней, какие подводные камни могут быть.
Будет происходить блокировка таблицы на время удаления необходимо ограничить запрос LIMIT 1
Есть огромная таблица заказов и таблица с одной ячейкой, в которой указана дата. Нужно посчитать сумму оплат по заказам, которые были позже даты, указанной во второй таблице.
SELECT SUM(price) FROM orders WHERE orders.date > (SELECT date FROM time)
SELECT SUM(price) FROM orders JOIN time ON orders.date > time.date
Добавить индекс на date в таблице orders и time по убыванию. Пакетная обработка данных, кэширование результатов, оптимизация аппаратного обеспечения
26. Расскажите об операторе JOIN, приведите примеры и варианты использования. Когда JOIN использовать не стоит?
Оператор JOIN служит для объединения нескольких таблиц и включения этих данных в выходной набор.
- Небольшой объем данных и простые запросы
- Индексы отсутствуют или неоптимальны: Если таблицы, которые вы хотите объединить, не имеют соответствующих индексов или индексы не оптимизированы для выполнения операции JOIN, это может привести к плохой производительности.
- Сложные или многоступенчатые JOIN операции: Если вам требуется выполнить сложные или многоступенчатые операции JOIN с большим количеством таблиц, это может привести к сложному плану выполнения запроса и снижению производительности.
- Использование полных соединений (FULL JOIN): Полные соединения объединяют данные из всех таблиц, включая строки, которые не имеют совпадений. Это может привести к большому объему данных и медленной производительности.
Репликация
Создание полного дубликата базы данных. Так, вместо одного сервера их будет несколько, обычно используется схема master-slaveШардинг
Техника масштабирования работы с данными. Суть его в разделении (партиционирование) базы данных на отдельные части так, чтобы каждую из них можно было вынести на отдельный сервер. Этот процесс зависит от структуры базы данных и выполняется прямо в приложении в отличие от репликации существуют два вида шардирования:- Вертикальный шардинг — это выделение таблицы или группы таблиц на отдельный сервер.
- Горизонтальный шардинг — это разделение одной таблицы на разные сервера. Это необходимо использовать для огромных таблиц, которые не умещаются на одном сервере.
Когда речь идет о выборе подходящих индексов для оптимизации запросов, есть несколько факторов, которые стоит учитывать:
Анализ запросов
Изучите типы запросов, которые выполняются на вашей таблице. Определите, какие столбцы часто используются в условиях WHERE, JOIN или ORDER BY. Это поможет вам определить, на какие столбцы следует создать индексы.Селективность столбцов
Оцените уникальность значений в столбцах, на которые вы планируете создать индексы. Если столбец имеет высокую Селективность (много уникальных значений), то индексирование этого столбца может быть более эффективным.Размер данных
Учтите размер данных в столбцах, которые вы планируете индексировать.Обновление данных
Рассмотрите частоту обновления данных в таблице. Индексы улучшают производительность чтения, но при обновлении или вставке данных индексы должны быть обновлены, что может замедлить процесс.Использование объединенных индексов
Иногда создание комбинированных индексов на несколько столбцов может быть полезным.Избегайте создания слишком большого количества индексов, так как это может увеличить размер базы данных и замедлить операции записи.
В основном используются те же правила создания индексов, что и для обычных запросов – индекс для колонок в WHERE. В сложных выборках выбирайте индексы (перебором), которые позволят уменьшить число "rows" в EXPLAIN запроса.
В особо сложных случаях – денормализация и кеширование. (Следует создать таблицу, оптимизированную под запрос и синхронно её обновлять)
Кэширование результатов запросов (Query Result Caching)
Этот метод заключается в кэшировании результатов выполнения запросов, чтобы избежать повторного выполнения запросов с теми же параметрами. Результаты запроса сохраняются в кэше и возвращаются при повторном запросе с теми же параметрами.Кэширование данных (Data Caching)
Этот метод включает кэширование конкретных данных, которые часто запрашиваются из базы данных. Например, можно кэшировать отдельные строки или целые таблицы, чтобы избежать выполнения запросов к базе данных при каждом запросе данных.Кэширование запросов (Query Caching)
Этот метод кэширует сами запросы, а не только их результаты. Если запрос уже был выполнен ранее, то результаты запроса извлекаются из кэша, вместо выполнения запроса повторно.Кэширование планов выполнения (Execution Plan Caching)
При выполнении запросов база данных генерирует план выполнения, определяющий оптимальный способ получения данных. Планы выполнения могут быть кэшированы для повторного использования, чтобы избежать повторного анализа и планирования запроса.Кэширование метаданных (Metadata Caching)
Этот метод кэширует метаданные, такие как структура таблиц, схемы базы данных и другие связанные схематические данные. Кэширование метаданных может помочь ускорить выполнение запросов, связанных с анализом структуры базы данных.31. Как вы оптимизируете работу с транзакциями в SQL? Какие подходы используете для улучшения производительности?
Минимизация длительности транзакций
Старайтесь ограничивать объем работы, выполняемой внутри транзакции, чтобы уменьшить время блокировки ресурсов и конкуренцию с другими транзакциями. Разбейте большие задачи на более мелкие транзакции, если это возможно.Правильное использование индексов
Убедитесь, что таблицы, используемые в транзакциях, имеют подходящие индексы. Индексы могут значительно ускорить выполнение запросов внутри транзакций и снизить блокировки.Использование оптимистической блокировки (Optimistic Locking)
Вместо блокировки ресурсов на протяжении всей транзакции, можно использовать оптимистическую блокировку, где проверяется, изменились ли данные с момента начала транзакции. Если данные не были изменены, транзакция может продолжиться без блокировки.Использование транзакционных изоляционных уровней
Выбор правильного уровня изоляции транзакций может повлиять на производительность и конкурентность. Оцените требования вашего приложения и выберите подходящий уровень изоляции для достижения нужной согласованности данных и производительности.Пакетная обработка (Batch Processing)
Если вам не требуется немедленное применение изменений в базе данных, рассмотрите возможность пакетной обработки транзакций, где несколько операций объединяются и выполняются одновременно. Это может сократить накладные расходы на установку и снятие блокировок.Использование хранимых процедур и представлений
Хранимые процедуры и представления могут уменьшить накладные расходы на сетевое взаимодействие и улучшить производительность, особенно при выполнении повторяющихся операций или сложных запросов.Оптимизация и профилирование запросов
Анализируйте и оптимизируйте запросы, выполняемые внутри транзакций. Используйте профилирование для выявления узких мест и оптимизации запросов для более эффективной работы с базой данных.Оптимистическая блокировка
Оптимистическая блокировка предполагает, что конфликты между транзакциями происходят редко, поэтому каждая транзакция не блокирует доступ к данным для других транзакций. Вместо этого используются механизмы контроля версий данных, чтобы обнаруживать конфликты и разрешать их. Это обычно достигается с помощью использования меток времени или хеш-значений для проверки, были ли данные изменены другими транзакциями после начала текущей транзакции. Если данные были изменены, оптимистическая блокировка может прервать или откатить текущую транзакцию и позволить другой транзакции применить свои изменения.Пессимистическая блокировка
Пессимистическая блокировка используется, когда ожидается возникновение конфликтов между транзакциями. При пессимистической блокировке транзакция блокирует доступ к данным, чтобы предотвратить изменение или чтение этих данных другими транзакциями. Это гарантирует, что только одна транзакция имеет доступ к данным в определенное время, но может привести к проблемам с конкурентностью и производительностью, особенно если блокировки длительные или часто возникают.Атомарность
Все команды в транзакции либо полностью выполняются, и соответственно, фиксируются все изменения данных, либо ничего не выполняется и ничего не фиксируетсяСогласованность
Данные, в случае успешного выполнения транзакции, должны соблюдать все установленные правила в части различных ограничений, первичных и внешних ключей, определенных в базе данныхИзоляция
Механизм предоставления доступа к данным. Транзакция изолирует данные, с которыми она работает, для того чтобы другие транзакции получали только согласованные данные;Надежность
Все внесенные изменения фиксируются в журнале транзакций и данные считаются надежными, если транзакция была успешно завершена. В случае сбоя SQL Server сверяет данные, записанные в базе данных, с журналом транзакций, если есть успешно завершенные транзакции, которые не закончили процесс записи всех изменений в базу данных, они будут выполнены повторно. Все действия, выполненные не подтвержденными транзакциями, отменяются.Совмещаемая (Shared) блокировка
Используются для операций считывания (SELECT) и предотвращают изменение (UPDATE, DELETE) заблокированного ресурса. Как следует из названия, данная блокировка может быть совмещена с другими блокировками. В зависимости от уровня изоляции транзакции, блокировка может быть снята, как только данные считаны (Read Committed), либо же удерживаться до конца транзакции (Repeatable Read и выше).
Монопольная (Exclusive) блокировка
Используется для операций изменения данных (UPDATE, DELETE). Данная блокировка не может быть установлена, если существуют какие-либо другие блокировки на ресурсе, т.е. команда будет ждать снятия всех существующих блокировок. Будучи успешно установленной, данная блокировка не позволяет установку новых блокировок любых типов, т.е. все запросы, пытающиеся получить доступ к заблокированному ресурсу, будут ожидать снятия монопольной блокировки.
Блокировка обновления (Update)
Он ведёт себя как что-то среднее между совмещаемой (Shared) и монопольной (Exclusive) блокировкой. «Монопольность» заключается в том, что на ресурсе может быть только одна блокировка обновления, а «совмещаемость» в том, что на этапе поиска данных блокировка может совмещаться с другими совмещаемыми блокировками.
Online DDL
Означает изменение схемы таблицы без блокировки доступа к ней. Это достигается путем использования специальных механизмов и алгоритмов, которые позволяют выполнять операции изменения схемы параллельно с другими операциями на таблице.Репликация
Изменение на реплике, позволяя основной базе данных продолжать работу.Разделение на столбцы
Разделение изменений на несколько этапов и применение их последовательно к отдельным столбцам или частям таблицы.Оптимизация процесса
Изучите текущую схему таблицы и процесс обновления, чтобы найти возможности оптимизации. Например, вы можете использовать параллельные процессы или батчевые операции для ускорения выполнения изменений.Оптимизировать структуру данных c целью уменьшения размера данных, пакетную вставку, компрессию данных
Масштабировать можно с помощью репликаций. Репликация для чтения - повышает отказаустойчивость.
Виды репликаций: синхронная и асинхоронная (когда запрос от клиента приходит к мастеру, мастер применяет и отвечает клиенту ок и после отправляет в реплику).
Когда работаем с балансом пользователей то нужна синхронная, чтоб данные были консистенты.
Преимущества синхронной downtime нулевой и сразу готовый мастер.
- Алгоритм забияки
Рассылается всем id, когда один понимает что один узел вышел из строя то рассылается сообщение каждому и тот у которого максимальное число говорит что он мастер
- Запись в БД строчки с expire
Когда перестаёт писать, то другой кто первый запишет строчку тот и мастер
- Определённие консенсуса
На основе голосований о приоритетах и свободных ресурсах выбирается мастер
Можно писать туда и туда.
- Высокая доступность
- Географическая репликация
- Распределенная обработка нагрузки
Недостатки:
- Конфликты идентификаторов
- Нужно синхронизовывать данные
- Range base диапазоны
- По полю
Представляется круг и зоны в которые попадают ключи, когда ключ попадает в область, мы смотрим какая шарда за этот диапозон отвечает. Проблема если она будет выходить из строя, то запросы будут идти от другой ноды - каскадный сбой. Решение использовать виртуальные ноды для каждой физической
- Записываем в новый шард, читаем со старого, но запрещаем update и delete
- Добавляем шард, ставим между ними репликацию, ждём когда засинхронизируются потом переводим трафик на новый шард а старый вырубаем
Отделение используемых записей от старых, по разным таблицам на одном сервере
В mysql регистро независимый поиск.