Есть чувства, есть галантерейные товары, поиск в WeChat【Третий принц Ао Бин] Обратите внимание на этого другого программиста.
эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.
Мне было скучно дома во время Национального дня, поэтому я небрежно листал дома книги, связанные с базой данных, и я был пристрастился к чтению, потому что я забыл некоторые парадигмы баз данных, с которыми я был знаком в университете, поэтому я смотрел небольшой Национальный день из любопытства.
В процессе чтения я также сделал несколько небольших заметок, которые может быть не так интересны, как мои предыдущие системные статьи, но они определенно полны галантереи.Подойдет для обзора или неожиданного интервью для всех.Я не много говорить о размещении фотографий в первую очередь.
механизм хранения
InnoDB
InnoDB является механизмом хранения транзакций MySQL по умолчанию, и другие механизмы хранения следует рассматривать только тогда, когда требуются функции, которые он не поддерживает.
InnoDB использует MVCC для поддержки высокого параллелизма и реализует четыре стандартных уровня изоляции (незафиксированное чтение, зафиксированное чтение, повторяемое чтение, сериализуемое). Уровень по умолчанию — ПОВТОРЯЕМОЕ ЧТЕНИЕ.При уровне повторяемого чтения фантомное чтение предотвращается с помощью MVCC + блокировка следующего ключа.
Когда первичный индекс является кластеризованным индексом, данные сохраняются в индексе, чтобы избежать прямого чтения диска, поэтому он имеет высокую производительность для запроса первичного ключа.
В InnoDB реализовано множество внутренних оптимизаций, в том числе предсказуемое чтение при чтении данных с диска, адаптивные хеш-индексы, которые могут автоматически создавать хеш-индексы в памяти для ускорения операций чтения, и буферы вставки, которые могут ускорить операции вставки и т. д.
InnoDB поддерживает настоящее оперативное резервное копирование в оперативном режиме. Другие механизмы хранения MySQL не поддерживают оперативное резервное копирование в оперативном режиме. Чтобы получить согласованное представление, необходимо прекратить запись во все таблицы. В сценарии смешанного чтения и записи прекращение записи может также означать прекращение чтения.
MyISAM
Дизайн прост, а данные хранятся в компактном формате. Его по-прежнему можно использовать для данных, доступных только для чтения, или если таблица достаточно мала, чтобы допускать операции восстановления.
Предоставляет большое количество функций, включая сжатые таблицы, индексы пространственных данных и многое другое.
Транзакции не поддерживаются.
Блокировки на уровне строк не поддерживаются, и можно заблокировать только всю таблицу, при чтении добавит общие блокировки ко всем таблицам, которые необходимо прочитать, а при записи добавит монопольные блокировки к таблице. Однако пока в таблице есть операции чтения, новые записи также могут быть вставлены в таблицу, что называется параллельной вставкой (CONCURRENT INSERT).
Операции проверки и исправления могут выполняться вручную или автоматически, но в отличие от восстановления транзакций и аварийного восстановления может произойти некоторая потеря данных, а операции восстановления выполняются очень медленно.
Если указана опция DELAY_KEY_WRITE, измененные данные индекса не будут записываться на диск сразу же после завершения выполнения каждой модификации, а будут записываться в буфер ключей в памяти только при очистке буфера ключей или закрытии таблицы. соответствующий индексный блок будет записан на диск. Этот метод может значительно повысить производительность записи, но при сбое базы данных или хоста индекс будет поврежден, и потребуется выполнить операцию восстановления.
Сравнение InnoDB и MyISAM
-
Транзакции: InnoDB является транзакционной и может использовать операторы Commit и Rollback.
-
Параллелизм: MyISAM поддерживает блокировки только на уровне таблицы, а InnoDB также поддерживает блокировки на уровне строк.
-
Внешние ключи: InnoDB поддерживает внешние ключи.
-
Резервное копирование: InnoDB поддерживает горячее резервное копирование в режиме онлайн.
-
Восстановление после сбоя: MyISAM гораздо более подвержен повреждению после сбоя, чем InnoDB, и восстановление происходит медленнее.
-
Другие функции: MyISAM поддерживает сжатые таблицы и индексы пространственных данных.
показатель
Принцип дерева B+
структура данных
B-дерево относится к дереву баланса, которое является сбалансированным деревом Сбалансированное дерево представляет собой дерево поиска, и все конечные узлы расположены на одном уровне.
B+ Tree — это вариант B-дерева, который реализован на основе указателей последовательного доступа B-дерева и листового узла и обычно используется в файловых системах баз данных и операционных систем.
Дерево B+ имеет два типа узлов: внутренние узлы (также называемые индексными узлами) и листовые узлы. Внутренние узлы не являются листовыми узлами. Внутренние узлы не хранят данные, а хранят только индексы. Все данные существуют в листовых узлах.
Ключи во внутренних узлах расположены в порядке возрастания, для ключа во внутреннем узле все ключи в левом поддереве меньше его, в правом поддереве все ключи больше или равны ему, а записи листовых узлов также расположены в порядке от наименьшего к наибольшему.
Каждый листовой узел хранит указатели на соседние конечные узлы.
действовать
найти
Поиск выполняется обычным способом, похожим на двоичное дерево поиска. Начиная с корневого узла, просматривайте дерево сверху вниз, выбирая дочерние указатели, значение разделения которых находится по обе стороны от искомого значения. Типичное использование внутри узла — это бинарный поиск для определения этой позиции.
вставлять
-
Perform a search to determine what bucket the new record should go into.
-
Если ведро не заполнено (не более b - 1 записей после вставки, b - количество элементов в узле, обычно кратное количеству страницы), добавьте эту запись.
-
Otherwise,before inserting the new record
- split the bucket.
- исходный узел имеет элементы "(L+1)/2"
- новый узел имеет элементы "(L+1)/2"
- Переместите "(L+1)/2"-й ключ к родителю и вставьте новый узел в родителя.
- Repeat until a parent is found that need not split.
- split the bucket.
-
If the root splits,treat it as if it has an empty parent ans split as outline above.
B-trees grow as the root and not at the leaves.
Удалить
Подобно вставке, но операция слияния снизу вверх.
Общие свойства деревьев.
АВЛ-дерево
Сбалансированное бинарное дерево обычно определяется разницей коэффициента баланса и реализуется вращением.Разница высот между левым и правым поддеревьями не превышает 1, поэтому по сравнению с красно-черным деревом это строго сбалансированное бинарное дерево, и условия баланса очень строгие (разница в высоте дерева всего 1), пока вставка или удаление не соответствует вышеуказанным условиям, необходимо поддерживать баланс путем вращения. Так как ротация занимает очень много времени. Поэтому AVL-дерево подходит для сценариев, когда количество вставок/удалений относительно невелико, а поисков много.
красно-черное дерево
Ограничивая цвет каждого узла на пути от корневого узла к конечному узлу, гарантируется, что ни один путь не будет в 2 раза длиннее другого и, таким образом, будет приблизительно сбалансирован. Следовательно, по сравнению с деревом AVL, которое строго требует баланса, его ротация реже поддерживает баланс. Подходит для сценариев с небольшим количеством поисков и большим количеством вставок/удалений. (Теперь в некоторых сценариях для замены красно-черных деревьев используются списки пропусков, вы можете выполнить поиск «Почему Redis использует списки пропусков вместо красно-черных?»)
B/B+ дерево
Многостороннее дерево поиска, высокая степень выхода, малый дисковый ввод-вывод, обычно используется в системах баз данных.
Сравнение деревьев B+ и красно-черных деревьев
Сбалансированные деревья, такие как красно-черные деревья, также могут использоваться для реализации индексов, но файловые системы и системы баз данных обычно используют дерево B+ в качестве структуры индекса по следующим двум причинам:
(1) Время дискового ввода-вывода
Один узел дерева B+ может хранить несколько элементов, при этом высота дерева ниже, чем у красно-черного дерева, и количество дисковых операций ввода-вывода меньше.
(2) Функция упреждающего чтения с диска
Чтобы сократить количество дисковых операций ввода-вывода, диски часто не считываются строго по требованию, а считываются каждый раз заранее. Во время упреждающего чтения диск читается последовательно, а последовательное чтение не требует поиска на диске. Каждый раз считываются целые числа страниц.
Операционная система обычно делит память и диск на блоки фиксированного размера, каждый из которых называется страницей, а память и диск обмениваются данными в единицах страниц. Система базы данных устанавливает размер узла индекса равным размеру страницы, чтобы один ввод-вывод мог полностью загрузить узел.
Сравнение дерева B+ и дерева B
Нижний дисковый ввод-вывод для деревьев B+
Внутренние узлы дерева B+ не имеют указателей на конкретную информацию о ключевых словах. Следовательно, его внутренние узлы меньше, чем B-деревья. Если все ключевые слова одного и того же внутреннего узла хранятся в одном блоке диска, тем больше ключевых слов может содержать блок диска. Чем больше ключевых слов нужно искать, тем больше считывать в память за один раз. Условно говоря, количество операций чтения и записи ввода-вывода также уменьшается.
Эффективность запросов дерева B+ более стабильна
Потому что нелистовой узел — это не тот узел, который окончательно указывает на содержимое файла, а только индекс ключевого слова в листовом узле. Таким образом, любой поиск по ключевому слову должен проходить по пути от корневого узла к конечному узлу. Длина пути всех запросов с ключевыми словами одинакова, что обеспечивает одинаковую эффективность запроса для всех данных.
Обход элементов дерева B+ эффективен
B-деревья улучшают производительность дискового ввода-вывода, не решая проблему неэффективного обхода элементов. Именно для решения этой проблемы и появилось дерево B+. Дерево B+ может проходить через все дерево, если оно проходит через конечные узлы. А запросы на основе диапазона очень часто встречаются в БД, а B-деревья не поддерживают такие операции (или слишком неэффективны).
индексы MySQL
Индекс реализован на уровне механизма хранения, а не на уровне сервера, поэтому разные механизмы хранения имеют разные типы индексов и реализации.
Индекс дерева B+
является типом индекса по умолчанию для большинства механизмов хранения MySQL.
-
Поскольку полное сканирование таблицы больше не требуется, требуется только поиск по дереву, поэтому поиск выполняется намного быстрее.
-
Благодаря упорядоченности B+ Tree, помимо поиска, его также можно использовать для сортировки и группировки.
-
Несколько столбцов могут быть указаны как столбцы индекса, и несколько столбцов индекса вместе образуют ключ.
-
Применимо к полному значению ключа, диапазону значений ключа и поиску префикса ключа, где поиск префикса ключа применим только к поиску самого левого префикса. Индекс нельзя использовать, если поиск не соответствует порядку индексированных столбцов.
Индексы InnoDB B+Tree делятся на первичные и вторичные индексы. Поле данных листового узла основного индекса записывает полные записи данных.Этот метод индексирования называется кластерным индексом. Поскольку нет возможности хранить строки в двух разных местах, таблица может иметь только один кластеризованный индекс.
Поле данных листового узла вспомогательного индекса записывает значение первичного ключа, поэтому при использовании вспомогательного индекса для поиска сначала необходимо найти значение первичного ключа, а затем искать первичный индекс. также называется таблицей возврата.
хэш-индекс
Хэш-индекс можно искать за время O(1), но теряется порядок:
- Нельзя использовать для сортировки и группировки;
- Поддерживает только точный поиск, не может использоваться для частичного поиска и поиска по диапазону.
Механизм хранения InnoDB имеет специальную функцию, называемую «адаптивный хеш-индекс».Когда значение индекса используется очень часто, хеш-индекс будет создан поверх индекса B+Tree, так что будет создан индекс B+Tree. Индексы обладают некоторыми преимуществами хеш-индексов, такими как быстрый поиск хэшей.
полный текстовый указатель
Механизм хранения MyISAM поддерживает полнотекстовое индексирование, которое используется для поиска ключевых слов в тексте, а не для прямого сравнения на равенство.
Условия поиска используют MATCH AGAINST вместо простого WHERE.
Полнотекстовое индексирование реализовано с помощью инвертированного индекса, который фиксирует сопоставление ключевых слов с их документами.
Механизм хранения InnoDB также поддерживает полнотекстовое индексирование в MySQL 5.6.4.
Индекс пространственных данных
Механизм хранения MyISAM поддерживает индексацию пространственных данных (R-Tree), которую можно использовать для хранения географических данных. Пространственные данные индексируют данные из всех измерений и могут эффективно использовать любое измерение для комбинированных запросов.
Данные должны поддерживаться с использованием функций, связанных с ГИС.
Оптимизация индекса
независимый столбец
При выполнении запроса индексируемый столбец не может быть частью выражения или параметром функции, иначе нельзя будет использовать индекс.
Например, следующий запрос не может использовать индекс столбца act_id:
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
многоколоночный индекс
Когда запросу необходимо использовать несколько столбцов в качестве условия, использование индекса с несколькими столбцами работает лучше, чем использование нескольких индексов с одним столбцом. Например, в следующем операторе лучше установить act_id и film_id как индексы с несколькими столбцами.
SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;
порядок столбцов индекса
Поместите наиболее избирательный столбец индекса первым.
Селективность индекса относится к отношению уникальных значений индекса к общему количеству записей. Максимальное значение равно 1, после чего каждая запись имеет соответствующий ей уникальный индекс. Чем выше селективность, тем выше дискриминация каждой записи и тем выше эффективность запроса.
Например, в приведенных ниже результатах столбец customer_id является более избирательным, чем staff_id, поэтому лучше поместить столбец customer_id перед многостолбцовым индексом.
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
индекс префикса
Для столбцов BLOB, TEXT и VARCHAR необходимо использовать индекс префикса, чтобы индексировать только начальную часть символа.
Выбор длины префикса должен определяться в соответствии с избирательностью индекса.
индекс покрытия
Индекс содержит значения всех полей, которые необходимо запросить.
Имеет следующие преимущества:
- Индекс обычно намного меньше размера строки данных, и только чтение индекса может значительно сократить объем доступа к данным.
- Некоторые механизмы хранения (такие как MyISAM) кэшируют только индексы в памяти, а кэширование данных зависит от операционной системы. Таким образом, просто получить доступ к индексу можно без использования системных вызовов (которые обычно занимают много времени).
- Для механизма InnoDB нет необходимости обращаться к первичному индексу, если вторичный индекс может покрыть запрос.
Преимущества индексации
-
Значительно сокращает количество строк данных, которые сервер должен сканировать.
-
Помогите серверу избежать сортировки и группировки, а также избежать создания временных таблиц (индексы B+Tree упорядочены и могут использоваться для операций ORDER BY и GROUP BY. Временные таблицы в основном создаются в процессе сортировки и группировки и не требуют сортировки и группировка, нет необходимости создавать временную таблицу).
-
Превратите случайный ввод-вывод в последовательный ввод-вывод (индексы B+Tree упорядочены и хранят смежные данные вместе).
Условия использования индекса
-
Для очень маленьких таблиц в большинстве случаев простое полное сканирование таблицы более эффективно, чем индексация;
-
Для средних и больших таблиц индексы очень эффективны;
-
Но для очень больших таблиц стоимость создания и обслуживания индексов соответственно возрастет. В этом случае необходимо использовать технологию, которая может напрямую различать набор запрашиваемых данных, вместо сопоставления запись за записью, например, можно использовать технологию секционирования.
Почему в большинстве случаев для очень маленьких таблиц простое полное сканирование таблицы более эффективно, чем индексация?
Если таблица относительно мала, то очевидно, что быстрее пройти по таблице напрямую, чем по индексу (поскольку таблицу нужно возвращать).
Примечание. Прежде всего следует отметить, что неявным условием этого ответа является то, что запрошенные данные не являются частью индекса, и не требуется никаких операций с таблицей возврата. Во-вторых, условие запроса не является первичным ключом, иначе данные можно получить напрямую из кластеризованного индекса.
Оптимизация производительности запросов
Используйте объяснение для анализа оператора выбора запроса
Объяснение используется для анализа операторов запроса SELECT, и разработчики могут оптимизировать операторы запроса, анализируя результаты объяснения.
select_type
Обычно используются простой запрос SIMPLE, запрос объединения UNION, подзапрос SUBQUERY и т. д.
table
таблица для запроса
possible_keys
The possible indexes to choose
выбираемый индекс
key
The index actually chosen
фактически используемый индекс
rows
Estimate of rows to be examined
количество отсканированных строк
type
Типы запросов индекса, часто используемые типы запросов индекса:
const: только одна строка соответствует при запросе с использованием первичного ключа или уникального индекса ссылка: использовать неуникальный индекс диапазон: запрос диапазона с использованием первичного ключа, вторичный индекс для одного поля, последнее поле вторичного индекса для нескольких полей Разница между index: и all в том, что индексное дерево сканируется all: Сканировать всю таблицу:
system
Условие срабатывания: в таблице есть только одна строка, что является частным случаем константного типа.
const
Условие срабатывания: при запросе с использованием первичного ключа или уникального индекса совпадает только одна строка.
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
eq_ref
Условие срабатывания: при выполнении запроса на соединение используйте первичный ключ или уникальный индекс и сопоставьте только одну строку записей.
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref
Условие срабатывания: использовать неуникальный индекс
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
range
Условие срабатывания: запрос диапазона выполняется только с использованием первичного ключа, вторичного индекса одного поля и последнего поля вторичного индекса нескольких полей.
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index
The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:
Условия срабатывания:
сканировать только дерево индексов
1) Запрашиваемое поле является частью индекса, охватывающего индекс. 2) Сортировка по первичному ключу
all
Условие срабатывания: полное сканирование таблицы, без индекса
Оптимизируйте доступ к данным
Уменьшить объем запрашиваемых данных
- Возвращайте только необходимые столбцы: лучше не использовать операторы SELECT *.
- Возвращайте только необходимые строки: используйте оператор LIMIT, чтобы ограничить возвращаемые данные.
- Кэширование данных, которые повторно запрашиваются. Использование кэширования позволяет избежать запросов к базе данных, особенно когда запрашиваемые данные часто запрашиваются повторно, улучшение производительности запросов, вызванное кэшированием, будет очень очевидным.
Уменьшите количество сканируемых строк на стороне сервера
Самый эффективный способ — использовать индекс для покрытия запроса.
Рефакторинг метода запроса
Разделить большие запросы
Если большой запрос выполняется одновременно, он может заблокировать сразу много данных, занять весь журнал транзакций, исчерпать системные ресурсы и заблокировать множество мелких, но важных запросов.
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0
Декомпозиция больших запросов на соединение
Разложение большого запроса на соединение в запрос к одной таблице для каждой таблицы, а затем выполнение ассоциации в приложении дает следующие преимущества:
- Сделайте кэширование более эффективным. Для запросов на соединение при изменении одной из таблиц весь кэш запросов становится недоступным. Для нескольких запросов после декомпозиции, даже если одна из таблиц изменится, можно будет использовать кеш запросов для других таблиц.
- Кэшированные результаты этих запросов к одной таблице, разложенные на несколько запросов к одной таблице, с большей вероятностью будут использоваться другими запросами, тем самым уменьшая количество запросов избыточных записей.
- уменьшить конкуренцию за блокировку;
- Подключение на уровне приложения упрощает разделение базы данных, что упрощает достижение высокой производительности и масштабируемости.
- Эффективность самого запроса также может быть повышена. Например, в приведенном ниже примере использование IN() вместо запроса соединения позволяет MySQL выполнять запросы в порядке идентификаторов, что может быть более эффективным, чем случайное соединение.
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
дела
Транзакция относится к набору операций, удовлетворяющих характеристикам ACID.Транзакция может быть отправлена через Commit или отменена с помощью Rollback.
ACID
Основными из транзакций являются четыре характеристики ACID.Этими четырьмя характеристиками являются:
- Атомарность: Атомарность
- Консистенция: Консистенция
- Изоляция: изоляция
- Прочность: Стойкость
атомарность
Транзакция рассматривается как неделимая наименьшая единица, и все операции транзакции либо завершаются успешно, либо терпят неудачу и откатываются.
последовательность
База данных поддерживает согласованное состояние до и после выполнения транзакции.В согласованном состоянии все транзакции считывают одни и те же данные для получения одного и того же результата.
изоляция
Изменения, сделанные одной транзакцией, не видны другим транзакциям, пока они не будут окончательно зафиксированы.
Упорство
Как только транзакция фиксируется, ее модификации постоянно сохраняются в базе данных. Даже в случае сбоя системы результат выполнения транзакции не может быть потерян.
Связь между КИСЛОТАМИ
Концепция ACID-характеристик транзакций очень проста, но сложна для понимания, главным образом потому, что эти характеристики не являются одноуровневыми отношениями:
- Результат транзакции является правильным только в том случае, если соблюдается непротиворечивость.
- При отсутствии параллелизма транзакции выполняются последовательно, и должна быть обеспечена изоляция. В настоящее время, пока может быть удовлетворена атомарность, должна быть удовлетворена согласованность. В случае параллелизма несколько транзакций выполняются параллельно, и транзакции должны не только удовлетворять требованиям атомарности, но и изоляции для обеспечения согласованности.
- Транзакции удовлетворяют требованиям устойчивости, чтобы справиться со сбоями базы данных.
уровень изоляции
ЧИТАТЬ БЕЗ ЗАЯВЛЕНИЙ
Изменения внутри транзакции, даже если они не зафиксированы, видны другим транзакциям.
ПРОЧИТАТЬ СОВЕРШЕНО
Транзакция может только считывать изменения, сделанные зафиксированными транзакциями. Другими словами, изменения, сделанные одной транзакцией, не видны другим транзакциям, пока они не будут зафиксированы.
ПОВТОРЯЕМОЕ ЧТЕНИЕ
Гарантируется, что результат многократного чтения одних и тех же данных в одной и той же транзакции будет одним и тем же.
Сериализуемый (SERIALIZABLE)
Принудительное выполнение транзакций последовательно.
Требуется реализация блокировки, а другие уровни изоляции обычно не требуются.
| уровень изоляции | грязное чтение | неповторяемое чтение | Фантомное чтение |
|---|---|---|---|
| незафиксированное чтение | √ | √ | √ |
| Отправить для чтения | × | √ | √ |
| повторяемое чтение | × | × | √ |
| Сериализуемый | × | × | × |
Замок
Блокировки — ключевая функция, отличающая системы баз данных от файловых систем. Механизмы блокировки используются для управления одновременным доступом к общим ресурсам.
тип замка
Общий замок (S Lock)
Позволяет транзакции читать строку данных
Эксклюзивный замок (X Lock)
Позволяет транзакциям удалять или обновлять строку данных
Общая блокировка намерения (блокировка IS)
Транзакция хочет получить общую блокировку определенных строк в таблице.
эксклюзивная блокировка намерения
Транзакция хочет получить эксклюзивные блокировки для определенных строк в таблице.
MVCC
Multi-Version Concurrency Control (MVCC) — это особый способ для механизма хранения MySQL InnoDB реализовать уровни изоляции, которые используются для реализации двух уровней изоляции фиксированного чтения и повторяемого чтения. Уровень изоляции незафиксированного чтения всегда считывает последнюю строку данных без использования MVCC. Сериализуемый уровень изоляции требует, чтобы все прочитанные строки были заблокированы, чего нельзя добиться с помощью одного лишь MVCC.
Базовые концепты
номер версии
- Номер версии системы: это возрастающее число.Каждый раз, когда начинается новая транзакция, номер версии системы будет автоматически увеличиваться.
- Номер версии транзакции: номер версии системы на момент запуска транзакции.
скрытый столбец
MVCC хранит два скрытых столбца за каждой строкой записей, используемых для хранения двух номеров версий:
- Создать номер версии: указывает номер версии системы при создании моментального снимка строки данных;
- Номер версии удаления: если номер версии удаления моментального снимка больше, чем номер текущей версии транзакции, моментальный снимок действителен; в противном случае снимок был удален.
Журнал отмены
Моментальные снимки, используемые MVCC, хранятся в журнале отмены, который связывает все моментальные снимки строки данных (Record) через указатель отката.
Процесс реализации
Следующая реализация относится к уровню изоляции Repeatable Read.
Важно понимать, что при запуске транзакции номер версии транзакции должен быть больше, чем номер версии текущего моментального снимка всех строк данных. Номер версии создания моментального снимка строки данных – это номер версии системы при создании моментального снимка строки данных. Номер версии системы увеличивается при создании транзакции. Поэтому при создании новой транзакции номер версии системы этой транзакции больше, чем предыдущий номер версии системы, то есть он больше, чем номер версии создания всех моментальных снимков строки данных.
SELECT
Несколько транзакций должны считывать моментальный снимок одной и той же строки данных, и этот моментальный снимок является самым последним действительным моментальным снимком. Но есть исключения, если транзакция модифицирует строку данных, то она может прочитать изменения, сделанные самой транзакцией, и не согласующиеся с результатами чтения других транзакций.
Транзакция, которая не изменяет строку данных, называется T. Номер версии создания моментального снимка строки данных, который должен быть прочитан T, должен быть меньше или равен номеру версии T, потому что, если он больше, чем номер версии T, это означает, что моментальный снимок строки данных является другой последней модификацией транзакции, поэтому его невозможно прочитать. Кроме того, удаленный номер версии моментального снимка строки данных, который должен быть прочитан T, должен быть не определен или больше номера версии T, потому что, если номер версии меньше или равен номеру версии T, это означает, что Снимок строки данных был удален. Вы должны прочитать его.
INSERT
Используйте текущий номер версии системы в качестве номера версии для создания моментального снимка строки данных.
DELETE
Используйте номер текущей версии системы в качестве номера версии для удаления моментального снимка строки данных.
UPDATE
Текущий номер версии системы используется в качестве номера версии удаления моментального снимка строки данных перед обновлением, а номер текущей версии системы используется в качестве номера версии создания моментального снимка строки данных после обновления. Это можно понимать как выполнение сначала DELETE, а затем выполнение INSERT.
Чтение моментального снимка и текущее чтение
На уровне повторяемого чтения, через механизм MVCC, хотя данные становятся повторяемыми и читаемыми, данные, которые мы читаем, могут быть историческими данными, несвоевременными данными, а не текущими данными базы данных! Это может вызвать проблемы в некоторых компаниях, особенно чувствительных к своевременности данных.
Этот способ чтения исторических данных называется чтением моментального снимка, а способ чтения текущей версии базы данных называется текущим чтением. Очевидно, в MVCC:
Снимок прочитан
Операция SELECT MVCC — это данные в моментальном снимке, и ее не нужно блокировать.
select * from table ….;
текущее чтение
Другие операции MVCC, которые изменят базу данных (INSERT, UPDATE, DELETE), должны быть заблокированы для чтения последних данных. Видно, что MVCC вообще не нуждается в блокировке, а только избегает блокирующей операции SELECT.
INSERT;
UPDATE;
DELETE;
При выполнении операции SELECT можно принудительно указать операцию блокировки. Для первого оператора ниже требуется блокировка S, а для второго — блокировка X.
- select * from table where ? lock in share mode;
- select * from table where ? for update;
Уровень изоляции транзакции на самом деле является определенным уровнем текущего чтения.Чтобы сократить время обработки блокировки (включая ожидание других блокировок) и улучшить параллелизм, MySQL вводит концепцию чтения моментального снимка, так что выбору не нужно быть запертым. Изоляция «текущего чтения», такого как обновление и вставка, должна быть достигнута путем блокировки.
алгоритм блокировки
Record Lock
Блокирует индекс записи, а не саму запись.
Если таблица не проиндексирована, InnoDB автоматически создаст скрытый кластеризованный индекс по первичному ключу, поэтому можно будет использовать блокировку записей.
Gap Lock
Блокирует промежутки между индексами, но не сами индексы. Например, когда одна транзакция выполняет следующий оператор, другие транзакции не могут вставить 15 в t.c.
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
Next-Key Lock
Это комбинация блокировок записей и блокировок промежутков, которая не только блокирует индекс записи, но также блокирует разрыв между индексами. Например, если индекс содержит следующие значения: 10, 11, 13 и 20, необходимо заблокировать следующие диапазоны:
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
В подсистеме хранения InnoDB проблема неповторяющегося чтения операции SELECT решается с помощью MVCC, проблема неповторяющегося чтения операций UPDATE и DELETE решается с помощью блокировки записи, а проблема неповторяющегося чтения операции INSERT решается с помощью Next. - Блокировка клавиш (блокировка записи + блокировка промежутка) решена.
проблема с замком
грязное чтение
Грязное чтение относится к тому факту, что при разных транзакциях текущая транзакция может считывать незафиксированные данные другой транзакции.
Например:
T1 изменяет часть данных, а T2 затем считывает эти данные. Если T1 отменяет эту модификацию, то данные, считанные T2, являются грязными данными.
неповторяемое чтение
Неповторяющееся чтение относится к ситуации, когда один и тот же набор данных считывается несколько раз в рамках одной и той же транзакции, а данные считываются по-разному.
Например:
T2 читает данные, а T1 изменяет данные. Если T2 снова считывает эти данные, результат, считанный в это время, отличается от результата первого чтения.
В подсистеме хранения InnoDB проблема неповторяющегося чтения операции SELECT решается с помощью MVCC, проблема неповторяющегося чтения операций UPDATE и DELETE решается с помощью блокировки записи, а проблема неповторяющегося чтения операции INSERT решается с помощью Next. -Key Lock (Блокировка записи + Блокировка промежутка).
Фантомная проблема (фантомное чтение)
Так называемая фантомная проблема возникает в транзакции, когда один и тот же запрос создает разные наборы строк в разное время.Например, если SELECT выполняется дважды, но во второй раз возвращает строку, которая не была возвращена в первый раз, строка является «фантомной» строкой.
Фантомная проблема означает, что в одной и той же транзакции выполнение одного и того же SQL-оператора дважды подряд может возвращать разные результаты, а второй SQL-оператор может возвращать строки, которых раньше не было.
Фантомные чтения — это особый вид проблемы неповторяемого чтения.
потерянное обновление
Операция обновления одной транзакции перезаписывается операцией обновления другой транзакции.
Например:
Обе транзакции T1 и T2 изменяют часть данных, T1 изменяет сначала, T2 изменяет позже, а модификация T2 охватывает модификацию T1.
Этот тип проблемы можно решить, добавив эксклюзивную блокировку в операцию SELECT, но это может привести к проблемам с производительностью, в зависимости от бизнес-сценария.
Сегментация базы данных и табличных данных
Горизонтальная сегментация
Горизонтальное сегментирование, также известное как сегментирование, заключается в разделении записей в одной таблице на несколько таблиц с одинаковой структурой.
Когда объем данных в таблице увеличивается, неизбежным выбором является шардинг, который может распределять данные по разным узлам кластера, тем самым буферизируя давление одной базы данных.
вертикальный срез
Вертикальная сегментация заключается в разделении таблицы на несколько таблиц по столбцам, обычно в соответствии со степенью плотности взаимосвязи столбцов, или можно использовать вертикальную атмосферу для разделения часто используемых столбцов и редко используемых столбцов на разные в таблице.
На уровне базы данных вертикальная сегментация используется для развертывания таблиц в базе данных в разных базах данных в соответствии с плотностью таблиц.Например, исходная база данных развертывания данных электронной коммерции вертикально сегментирована на товарную базу данных, пользовательскую базу данных, и так далее.
Стратегия шардинга
- Хэш по модулю: хэш(ключ)%N
- Диапазон: может быть диапазоном идентификаторов или временным диапазоном.
- Таблица сопоставления: используйте отдельную базу данных для хранения отношения сопоставления.
Проблемы с шардингом
деловая проблема
Используйте для решения распределенные транзакции, такие как интерфейс XA
соединять
Исходное соединение может быть разложено на несколько запросов к одной таблице, а затем объединено в пользовательской программе.
уникальность
- Используйте глобально уникальный идентификатор (GUID)
- Укажите диапазон идентификаторов для каждого сегмента
- Распределенные генераторы идентификаторов (например, алгоритм Twitter Snowflake)
копировать
репликация master-slave
В основном задействованы три потока: поток binlog, поток ввода-вывода и поток SQL.
- поток binlog: отвечает за запись изменений данных на главном сервере в бинарный журнал (Binary log).
- Поток ввода-вывода: отвечает за чтение двоичного журнала с главного сервера и запись в журнал ретрансляции подчиненного сервера.
- Поток SQL: отвечает за чтение журнала ретрансляции, анализ изменений данных, которые были выполнены главным сервером, и воспроизведение (Replay) на подчиненном сервере.
разделение чтения-записи
Главный сервер обрабатывает операции записи и чтения с высокими требованиями к реальному времени, а подчиненный сервер обрабатывает операции чтения.
Причины, по которым разделение чтения и записи может улучшить производительность:
- Главный и подчиненный серверы сами несут ответственность за чтение и запись, что значительно снижает конфликт блокировок;
- Подчиненный сервер может использовать MyISAM для повышения производительности запросов и снижения нагрузки на систему;
- Увеличьте резервирование и улучшите доступность.
Разделение чтения и записи обычно реализуется прокси-сервером, который получает запросы на чтение и запись от прикладного уровня, а затем решает, на какой сервер перенаправить.
JSON
Тип данных JSON часто используется в реальном бизнесе, и в процессе запроса есть два основных требования к использованию:
- В условии where необходимо отфильтровать возвращаемый результат по полю в json
- Запросите некоторые поля в поле json в качестве возвращаемых результатов (уменьшите использование памяти)
JSON_CONTAINS
JSON_CONTAINS(target, candidate[, path])
Если кондидат целевого значения найден по пути к местоположению, указанному целью поля json, вернуть 1, в противном случае вернуть 0
Чтобы просто проверить, существуют ли данные по указанному пути, используйте JSON_CONTAINS_PATH().
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+
JSON_CONTAINS_PATH
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
Возвращает 1, если данные существуют по указанному пути, в противном случае возвращает 0
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+
фактическое использование:
$conds = new Criteria();
$conds->andWhere('dept_code', 'in', $deptCodes);
if (!empty($aoiAreaId)) {
$aoiAreaIdCond = new Criteria();
$aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(new_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
$aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(old_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
$conds->andWhere($aoiAreaIdCond);
}
столбец->путь, столбец->>путь
Получить значение указанного пути
-> vs ->>
Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.
mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c | g |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} | 3 |
| {"id": "4", "name": "Betty"} | 4 |
+-------------------------------+------+
2 rows in set (0.01 sec)
mysql> SELECT c->'$.name' AS name
-> FROM jemp WHERE g > 2;
+----------+
| name |
+----------+
| "Barney" |
| "Betty" |
+----------+
2 rows in set (0.00 sec)
mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
-> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)
mysql> SELECT c->>'$.name' AS name
-> FROM jemp WHERE g > 2;
+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)
фактическое использование:
$retTask = AoiAreaTaskOrm::findRows(['status', 'extra_info->>"$.new_aoi_area_infos" as new_aoi_area_infos', 'extra_info->>"$.old_aoi_area_infos" as old_aoi_area_infos'], $cond);
Теория проектирования реляционных баз данных
функциональные зависимости
Обозначение A->B означает, что функция A определяет B, или можно сказать, что функция B зависит от A.
Если {A1, A2,..., An} есть набор из одного или нескольких атрибутов отношения, функция которого определяет все остальные атрибуты отношения и является наименьшим, то такой набор называется ключом.
Для A->B, если можно найти правильное подмножество A' в A, такое что A'->B, то A->B является частичной функциональной зависимостью, в противном случае это полная функциональная зависимость.
Для A->B, B->C, тогда A->C является зависимостью передаточной функции
аномальный
Функциональная зависимость следующего отношения «студент-курс»: {Sno, Cname} -> {Sname, Sdept, Mname, Grade}, а ключевой код — {Sno, Cname}. То есть, как только студенты и курсы определены, может быть идентифицирована другая информация.
| Sno | Sname | Sdept | Mname | Cname | Grade |
|---|---|---|---|---|---|
| 1 | студент-1 | Колледж-1 | Декан-1 | 1 курс | 90 |
| 2 | студент-2 | Академия-2 | Декан-2 | Курс-2 | 80 |
| 2 | студент-2 | Академия-2 | Декан-2 | 1 курс | 100 |
| 3 | студент-3 | Академия-2 | Декан-2 | Курс-2 | 95 |
Отношения, которые не соответствуют парадигме, будут генерировать множество исключений, в основном включая следующие четыре исключения:
- Избыточные данные: например.
学生-2появлялся дважды. - Исключение модификации: информация в одной записи изменена, но та же информация в другой записи не изменена.
- Удалить исключение: удалите сообщение, тогда остальная информация также будет потеряна. например удалил
课程-1Первую и третью строки нужно удалить, затем学生-1информация будет потеряна. - Исключение при вставке: например, если вы хотите вставить информацию об учащемся, если учащийся не выбрал курс, то ее нельзя будет вставить.
Парадигма
Теория парадигмы должна решить вышеупомянутые четыре вида аномалий.
Парадигмы высокого уровня зависят от парадигм низкого уровня, а 1NF является парадигмой самого низкого уровня.
Первая нормальная форма (1NF)
Свойства неразделимы.
Вторая нормальная форма (2NF)
Каждое неосновное свойство полностью функционально зависит от кода ключа.
можно удовлетворить разложением.
Перед разложением
| Sno | Sname | Sdept | Mname | Cname | Grade |
|---|---|---|---|---|---|
| 1 | студент-1 | Колледж-1 | Декан-1 | 1 курс | 90 |
| 2 | студент-2 | Академия-2 | Декан-2 | Курс-2 | 80 |
| 2 | студент-2 | Академия-2 | Декан-2 | 1 курс | 100 |
| 3 | студент-3 | Академия-2 | Декан-2 | Курс-2 | 95 |
В вышеприведенных отношениях студенческого курса {Sno, Cname} является кодом ключа, который имеет следующие функциональные зависимости:
- Sno -> Sname, Sdept
- Sdept -> Mname
- Sno, Cname-> Grade
Полноценная функция «Оценка» основана на кодах клавиш, в ней нет избыточных данных, и у каждого учащегося есть своя оценка по каждому курсу.
Sname, Sdept и Mname частично полагаются на коды клавиш, и когда учащийся проходит несколько курсов, эти данные появляются несколько раз, что приводит к большому количеству избыточных данных.
После разложения
отношения-1
| Sno | Sname | Sdept | Mname |
|---|---|---|---|
| 1 | студент-1 | Колледж-1 | Декан-1 |
| 2 | студент-2 | Академия-2 | Декан-2 |
| 3 | студент-3 | Академия-2 | Декан-2 |
Имеет следующие функциональные зависимости:
- Sno -> Sname, Sdept
- Sdept -> Mname
отношения-2
| Sno | Cname | Grade |
|---|---|---|
| 1 | 1 курс | 90 |
| 2 | Курс-2 | 80 |
| 2 | 1 курс | 100 |
| 3 | Курс-2 | 95 |
Имеет следующие функциональные зависимости:
- Sno, Cname -> Grade
Третья нормальная форма (3NF)
Непервичные свойства не передают функции, зависящие от кода ключа.
В отношении 1 выше существуют следующие передаточные функциональные зависимости:
- Sno -> Sdept -> Mname
Возможны следующие разложения:
Отношения-11
| Sno | Sname | Sdept |
|---|---|---|
| 1 | студент-1 | Колледж-1 |
| 2 | студент-2 | Академия-2 |
| 3 | студент-3 | Академия-2 |
Отношения-12
| Sdept | Mname |
|---|---|
| Колледж-1 | Декан-1 |
| Академия-2 | Декан-2 |
ER-диаграмма
Entity-Relationship состоит из трех компонентов: сущности, атрибуты и отношения.
Концептуальное проектирование систем реляционных баз данных.
три типа подключения
Включая один-к-одному, один-ко-многим и многие-ко-многим три.
- Если отношение A к B является отношением «один ко многим», нарисуйте отрезок со стрелкой, указывающей на B;
- Если он один к одному, нарисуйте два отрезка со стрелками;
- Если это «многие ко многим», нарисуйте два отрезка без стрелок.
Курс и Студент на рисунке ниже имеют отношение «один ко многим».
Представляет связь, которая повторяется несколько раз
Когда сущность появляется в отношениях несколько раз, для ее соединения необходимо использовать несколько линий.
На следующем рисунке показано отношение предварительных условий курса. В отношении предварительных условий есть две сущности курса. Первый — это обязательный курс, а второй — курс последипломного образования. Поэтому для представления этого отношения необходимы две строки.
разнонаправленность контакта
Хотя учитель может преподавать несколько курсов и обучать нескольких студентов, существует только один учитель, преподающий конкретного студента и курс, что представляет собой троичную связь.
Представляет подкласс
Треугольник и две линии используются для соединения класса и подкласса, свойства и связи, относящиеся к подклассу, связаны с подклассом, а свойства и связи, относящиеся как к родительскому классу, так и к подклассу, связаны с родительским классом.
использованная литература
- Цзян Чэнъяо, Технология MySQL изнутри: механизм хранения InnoDB [M], Machinery Industry Press, 2011.
- CS-Notes-MySQL
- B+ tree
- Красно-черное дерево, дерево B(+), таблица пропуска, AVL и другие структуры данных, сценарии и анализ приложений, а также некоторые аббревиатуры на английском языке
- Сравнение B-дерева, дерева B+, красно-черного дерева, дерева AVL
- 8.8.2 EXPLAIN Output Format
- Самая официальная интерпретация поля типа объяснения mysql
- 12.18.3 Functions That Search JSON Values
Суммировать
Это основы, я не ожидал, что оглянусь назад и забуду большинство из них, и мне повезло, что у меня снова есть такой отпуск.
Честно говоря, я потратил гораздо меньше времени на зарядку и исследование глубины стека технологий после того, как начал работать над We Media, я также долго думал об этом во время праздника Национального дня, и я планирую продолжать сжимать свой свободное время позже, например, сжатие времени просмотра мобильных телефонов и просмотра станции B. Теперь вам все еще нужно вовремя заряжать. В настоящее время работа и отдых довольно регулярны. Я сделал это рано, чтобы лечь спать и рано вставать. Давайте работать вместе.
Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.