Детка, давай что-нибудь вместе

1. Базовая структура данных и алгоритм индекса MYSQL.

Мы часто думаем о добавлении индексов, когда дело доходит до оптимизации SQL. Какой к черту индекс?

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

Мы знаем, что MYSQL может использоватьВ+ деревоа такжеhashДля поддержания индекса хеш-структура обычно не используется, хотя ее можно быстро направить к соответствующим данным через хеш-алгоритм, но для сортировки хэш кажется очень безвкусным.

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

Например:

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

красно-черное дерево: Его также называют бинарным сбалансированным деревом.Хотя спин и изменение цвета решают проблему вырождения в связанный список, в определенной степени проблема увеличения высоты дерева не решена.

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

В+ дерево: Это эквивалентно эволюционной версии B-дерева, от хранения данных в каждом узле до хранения данных только в конечных узлах, и все данные этого дерева фактически находятся в конечных узлах, а дерево B+ находится в листья.Узел поддерживает однонаправленный указатель (MYSQL был оптимизирован и превратился в двусторонний указатель), этот указатель во многом решает нашу проблему сортировки полей индекса

К настоящему времени я, должно быть, понял лучше.Индекс — это отсортированная структура данных."

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

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

Это включает механизмы хранения MYSQL, такие как Innodb, MyISAM и Memory. Поговорим об общемInnodbа такжеMyISAM

PS: структура данных индекса хранится в папке /data в каталоге установки MYSQL.

MyISAM: Дерево индексов и файл данных разделены (не объединены).Данные в дереве индексов поддерживают только адрес памяти соответствующего файла данных, и соответствующие данные находятся по адресу памяти.

Innodb: Он разделен на два случая, один из которыхдерево индексов первичного ключа(или уникальное индексное дерево), другойОбычное индексное дерево (некластеризованное). Данные в дереве индекса первичного ключа поддерживают данные соответствующей строки, в то время как обычное дерево индекса поддерживает идентификатор первичного ключа соответствующей строки.Через этот идентификатор первичного ключа перейдите к дереву индекса первичного ключа для обработки.запрос формы возврата, чтобы найти соответствующие данные.

PS: Предположим, MYSQLсовместный индексКак сравнить размер для формирования дерева? Например, два поля (имя, возраст) образуют совместный индекс, тогда размер будет сравниваться по ASCII-коду имени, если размер был определен по имени, нет необходимости продолжать чтобы сравнить размер возраста. Только когда коды ASCII имен совпадают, размер возраста используется для сравнения.

Во-вторых, анализ плана выполнения SQL.

Анализ плана выполнения играет важную роль в настройке SQL.Через Объяснение + наш пользовательский SQL можно получить план выполнения SQL следующим образом:

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

идентификатор столбца: это порядковый номер выборки. Существует несколько идентификаторов для нескольких выборок, и порядок идентификаторов увеличивается в соответствии с порядком, в котором появляются выборки. Чем больше столбец идентификатора, тем выше приоритет выполнения. Если идентификатор тот же, он будет выполняться сверху вниз. Если идентификатор равен NULL, он будет выполнен последним.
столбец таблицы: таблица, соответствующая выбранному
тип столбца: В этом столбце указана степень оптимизации SQL, от лучшего к худшему:system>const>eq_ref>ref>range>index>ALLВообще говоря, необходимо следить за тем, чтобы запрос достиг уровня диапазона, желательно ref
ключевой столбец: индекс фактической прогулки
столбец строк: количество результатов или сканирований, оцененное внутренне mysql.
Дополнительный столбец: В этом столбце отображается дополнительная информация, наиболее важными из которых являются следующие:

a. Usingindex: Указывает, что используется покрывающий индекс (покрывающий индекс означает, что запрашиваются только поля в дереве индексов, что сокращает операцию возвращаемой таблицы, тем самым повышая скорость)

b. Usingwhere: оператор where используется для обработки результатов, а запрошенные столбцы не покрываются индексом.

c. Usingindexcondition: Столбцы запроса не полностью покрываются индексом, а условие where представляет собой диапазон ведущих столбцов.

d. Usingtemporary: mysql необходимо создать временную таблицу для обработки запроса. Когда это происходит, как правило, необходимо оптимизировать.Во-первых, нужно подумать об использовании индексов для оптимизации.

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

3. Как выполняется SQL в MYSQL?

Чтобы понять эту проблему, мы должны сначала понять внутреннюю структуру MYSQL, а именно:

Процесс выполнения SQL выглядит следующим образом:

MYSQL как сервер, наша программа как клиент черезTCPПоддерживайте длительное соединение с MYSQL.
MYSQL использует наш SQL в качестве ключа для запроса в кеше (кеш MYSQL используетАлгоритм устранения LRUЧтобы реализовать механизм устранения кеша), определите, попал ли кеш, если попадется, данные будут возвращены напрямую. Если не попал, продолжите следующий процесс
MYSQL реализует наборпарсер(написано на языке C), через этот анализатор судить о правильности нашего синтаксиса SQL.
После того, как синтаксис правильный, то через внутреннюю реализациюоптимизаторВыполните некоторые оптимизации нашего SQL, включая расчет стоимости и т. д. (поэтому мы думаем, что определенный SQL теоретически пройдет через индекс, но план выполнения показывает, что он не проходит через индекс), и, наконец, сгенерируйте выполнение план нашего SQL. Конечно, мы также можемFORCE_INDEX(....) Индекс вынужденного обхода
После завершения оптимизации он войдет во внутренний MYSQL.Актуатор, а затем вызывать через исполнителя соответствующий нашей таблице движок хранилища, такой как Innodb, MyISAM, Memory и т.д.
Механизм выполнения найдет соответствующие данные в соответствующем дереве индексов через оптимальный индекс в соответствии с результатом, проанализированным оптимизатором, и в то же время будет поддерживать дерево индексов.

В-четвертых, уровень изоляции блокировок и транзакций MYSQL.

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

Во-первых, давайте посмотрим на транзакции и свойства ACID транзакций.

Итак, что такое транзакция? Транзакция — это набор операторов SQL, которые либо все выполняются успешно, либо все терпят неудачу.

ACID свойства транзакций:

атомарность(Атомарность): транзакция — это атомарная единица операции, и ее модификации данных либо все, либо ни одна из них.
последовательность(Непротиворечивый): данные должны оставаться в согласованном состоянии как в начале, так и при завершении транзакции. Это означает, что все соответствующие правила данных должны применяться к модификации транзакции для поддержания целостности данных.
изоляция(Изоляция): система базы данных предоставляет определенный механизм изоляции, чтобы гарантировать выполнение транзакций в «независимой» среде, на которую не влияют внешние параллельные операции. Это означает, что промежуточное состояние в процессе транзакции невидимо для внешнего мира, и наоборот.
Упорство(Продолжительный): после завершения транзакции ее изменения в данных являются постоянными и могут сохраняться даже в случае сбоя системы.

Проблемы, вызванные одновременными транзакциями:

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

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

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

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

Часто смотрите на уровень изоляции транзакций текущей базы данных:show variables like '%isolation%';

Установить уровень изоляции транзакций:set transaction_isolation='REPEATABLE-READ';

Уровень изоляции транзакций Mysql по умолчанию - повторяемое чтение, При разработке программы с помощью Spring, если уровень изоляции не установлен, по умолчанию используется уровень изоляции, установленный Mysql.Если установлен Spring, используется установленный уровень изоляции.

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

Классификация замков:

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

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

Между InnoDB и MYISAM есть два основных различия:

InnoDB поддерживает транзакции
InnoDB поддерживает блокировку на уровне строк.

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

1. Прочитайте незафиксированные

Сначала установите уровень изоляции для чтения незафиксированныхset transaction_isolation='read-uncommitted';, Затем откройте два сеанса, как показано ниже:

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

2. Читать отправлено

Сначала установите уровень изоляции для чтения незафиксированныхset transaction_isolation='read-committed';, Затем откройте два сеанса, как показано ниже:

Транзакция A может считывать только данные, отправленные транзакцией B, которые нельзя читать повторно, и могут возникать фантомные чтения.

повторяемое чтение

Согласно порядку серийных номеров на приведенном выше рисунке, от 1 до 6, при запросе 6 обнаруживается, что вновь добавленные данные транзакции B действительно не найдены, поэтому фантомного чтения действительно нет. Однако, если вы снова выполните 7 и 8, вы обнаружите, что в 8 происходит фантомное чтение.

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

сериализовать

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

PS: Уровень Mysql по умолчанию — повторяемое чтение.Есть ли способ решить проблему фантомного чтения?гэп замокВ некоторых случаях фантомное чтение может быть разрешено.

Блокировка пробела блокирует разрыв между двумя значениями.

Тогда промежуток имеет три интервала с id (3,10), (10,20), (20, положительная бесконечность).Выполнить набор учетной записи обновления name='zhuge', где id>8 и idГэп-блокировки действуют только при уровне изоляции повторяющегося чтения.

Пять, глубокое понимание MVCC

Механизм управления многоверсионным параллелизмом MVCC, Mysql реализует механизм MVCC как на уровне фиксации чтения, так и на уровне изоляции повторяемого чтения.

Самое большое преимущество MVCC: чтение без блокировки, чтение и запись без конфликтов. В приложении, где нужно больше читать и меньше писать, очень важно, чтобы чтение и запись не конфликтовали, что значительно увеличивает одновременную производительность системы.

Реализация механизма MVCC осуществляется черезReadViewа такжецепочка версий undo_log, чтобы разные транзакции считывали разные версии одних и тех же данных в цепочке версий в соответствии с правилами сравнения цепочек версий данных.

Давайте посмотрим на цепочку версий undo_log:

Цепочка версий undo_log означает, что после того, как строка данных была изменена несколькими транзакциями по очереди, Mysql сохранит журнал отката данных перед модификацией и использует два скрытых поля trx_id и roll_pointer для объединения этих журналов отмены для формирования цепочка версий истории. Как показано ниже:

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

Говоря о цепочке версий, давайте взглянем на ReadView.Разница между фиксированным чтением и повторяемым чтением заключается в том, что у них разные стратегии создания ReadView., новый ReadView создается для каждого запроса в транзакции чтения-фиксации, но не в транзакции повторного чтения.

Так что же такое ReadView?

С точки зрения непрофессионала, при уровне изоляции фиксированного чтения или повторяемого чтения, когда мы выполняем запрос, MYSQL будет поддерживать для нас внутреннюю копию.список активных транзакций, то есть идентификатор транзакции, которая началась, но еще не зафиксирована. Например, если ReadView равен [30,60], это означает, что транзакции до 30 были зафиксированы, а транзакции после 60 запускаются после текущей Создается ReadView.

Например ,на уровне фиксации чтения:

Например, в это время есть транзакция с идентификатором транзакции 100, и имя изменено так, чтобы имя было равно Xiaoming 2, ноТранзакция не была совершена. Тогда цепочка версий на данный момент такова:

В это время другая транзакция инициирует оператор select для запроса записи с идентификатором 1, а сгенерированный список ReadView в это время составляет только [100]. Затем перейдите в цепочку версий, чтобы найти ее.Прежде всего, вы должны найти самую последнюю.Обнаружено, что trx_id равен 100, то есть запись, имя которой Xiaoming 2. Она находится в списке, поэтому она невозможно получить доступ. В это время продолжайте находить следующую запись по указателю, имя — Xiaoming 1, и обнаружите, что trx_id равен 60, что меньше наименьшего идентификатора в списке, поэтому к нему можно получить доступ, и результат прямого доступ Сяомин 1.

В это время мы зафиксировали транзакцию с идентификатором транзакции 100, создали новую запись с идентификатором транзакции 110 и изменили запись с идентификатором 1 и не зафиксировали транзакцию.

На данный момент цепочка версий

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

Здесь наступает решающий момент

Если вы зафиксировали уровень изоляции чтения, вы повторно прочитаете ReadView в это время, и значение в вашем списке активных транзакций изменится на [110].

Согласно приведенному выше утверждению, вы переходите к цепочке версий и через сравнение trx_id находите соответствующий результат — Xiaomiing 2.

Если вы находитесь на уровне изоляции повторяемого чтения, ваш ReadView по-прежнему является ReadView, сгенерированным при первом его выборе, то есть значение списка по-прежнему [100]. Таким образом, результатом выбора является Xiaoming1. Таким образом, результат второго выбора такой же, как и в первый раз, поэтому он называется повторяемым чтением!

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

Это MVCC Mysql через цепочку версий для достижения нескольких версий, одновременного чтения-записи, записи-чтения. Различные уровни изоляции достигаются различными стратегиями генерации ReadView.

В-шестых, основной механизм кэширования BurfferPool от Innobd.

Почему Mysql не обновляет данные на диске напрямую, а устанавливает такой набор механизмов BurfferPool для выполнения SQL?

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

Давайте посмотрим на блок-схему механизма кэширования BurfferPool:

Например, наш SQL является оператором обновления, фактически MYSQL будет запрашивать перед обновлением. После оптимизации оптимизатором MYSQL получается окончательный план выполнения, и тогда исполнитель сначала пойдет на диск, чтобы найти соответствующие данные, потому что данные хранятся в виде страниц на диске. функция операционной системы, a Вся страница данных хранится в пуле буферов BufferPool.
Запишите данные перед обновлением в журнал undo_log, чтобы упростить операции отката.
После успешной записи журнала отмены данные обновляются в пуле буферов BufferPool.
После успешного обновления данных BufferPool последние данные будут записаны в Redo_log_buffer.
При фиксации транзакции данные в Redo_log_buffer будут записываться в Redo_log, основная функция - восстановление данных BufferPool
Во время выполнения операции 5 будет асинхронно открыт поток для записи журнала bin_log в файл bin_log.
После успешной записи файла bin_log соответствующие данные в файле redo_log будут помечены фиксацией, чтобы обеспечить согласованность данных между redo_log и bin_log после фиксации транзакции.