Начало работы с MySQL (1): внутренняя реализация запросов и обновлений

• Начало работы с MySQL (1): внутренняя реализация запросов и обновлений
• Начало работы с MySQL (2): индексы
• Начало работы с MySQL (3): изоляция транзакций
• Начало работы с MySQL (4): блокировки
• Начало работы с MySQL (5): репликация

Резюме

В MySQL с простым CURD легко начать работу.

Но понять, что происходит за CURD, особенно сложно.

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

1 структура MySQL

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

Сначала посмотрите на картинку:

Проще говоря:

1.1 Разъемы

Коннекторы отвечают за установление соединений с клиентами, получение разрешений, поддержание соединений и управление ими.

После того, как клиент введет учетную запись и пароль, если проверка учетной записи и пароля будет пройдена, соединитель установит TCP-соединение с клиентом. Соединение будет автоматически разорвано коннектором после длительного периода бездействия (по умолчанию 8 часов).

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

1.2 Анализатор

Анализатор выполняет две функции: лексический анализ и синтаксический анализ.

Для оператора SQL анализатор сначала выполняет лексический анализ,sqlОператор разделен, чтобы определить значение каждой строки.

Затем следует грамматический анализ, анализатор судит по определенным грамматическим правилам.sqlУдовлетворяет ли оператор синтаксису MySQL.

Итак, если мы увидимYou have an error in your SQL syntaxПо такому абзацу можно узнать, что анализатор возвращает эту ошибку.

1.3 Кэш

Кэш здесь сохранит предыдущийsqlЗаявление о запросе и результаты. Вы можете понять, что этоmap:keyзапрашиваетсяsqlутверждение,valueявляется результатом запроса.

А в официальном мануале есть такая фраза:

Queries must be exactly the same (byte for byte) to be seen as identical.

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

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

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

Однако после MySQL 8.0 функция кэширования была удалена.

1.4 Оптимизатор

Задача оптимизатора запросов состоит в том, чтобы найти наилучший способ выполнения SQL-запроса. Большинство оптимизаторов запросов, включая оптимизатор запросов MySQL, всегда ищут более или менее лучшее решение среди всех возможных решений для оценки запросов.

Проще говоря, оптимизатор ищет стратегию, которая может запросить данные как можно быстрее.

1.5 Приводы

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

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

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

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

2 запрос

Мы идем от этогоsqlНачать говорить:

select * from T where ID = 1；

2.1 Кэш поиска

Сначала вызывается анализатор для проведения лексического анализа.

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

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

Однако в книге «Высокопроизводительный MySQL» упоминается, что «путем проверки того, начинается ли оператор sql с select», поэтому я предполагаю, что кеш поиска должен сначала пройти простой лексический анализ.

Только после лексического анализа MySQL может узнать, является ли это выражение правильным.selectзаявление, вы также можете узнать, есть ли в этом заявлении какие-то неопределенные данные (например, текущее время и т. д.).

2.2 Промахи кеша

На этом этапе, если кеш отсутствует, продолжайте синтаксический анализ с помощью синтаксического анализатора. Затем, согласно этому синтаксическому дереву, судить об этомsqlСоответствует ли оператор синтаксису MySQL.

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

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

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

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

2.3 Структура данных

На последнем шаге мы используемАктуаторПри чтении и записи данных он фактически вызывает механизм хранения в MySQL для чтения, записи и записи данных.

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

Вот добавлю немного знаний:

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

В этой структуре есть характеристика, называемая принципом локальности.

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

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

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

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

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

Наконец, страница данных записывается обратно на диск, когда это необходимо. Что касается того, когда и как производить обратную запись на диск, давайте посмотрим вниз.

3 Обновить

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

• updateкак выполняется заявление
• Как сохранить новые данные после выполнения на диске

Это интересный вопрос, давайте предположим два сценария:

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

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

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

Теперь вернемся к этому утверждению:

update T set a = a + 1 where ID = 0;

Предполагая, что этот оператор sql верен, существует файл с именемID,aперечислены в таблицеTв, и существуетID0 данных.

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

ты можешь найти,updateЗаявление также проходит тот же процесс.

Затем наступает момент, мы должны представить, как MySQL гарантирует согласованность данных.

3.1 Журнал повторов

Здесь, чтобы представить очень важный модуль журнала, называемыйredo log(журнал повторов).

Обратите внимание, что журналы повторного выполнения специфичны для механизма InnoDB.

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

В MySQL есть существительное, называемоетехнология WAL, Полное название WAL Write-Ahead-Logging.Его ключевой момент заключается в том, чтобы сначала записать лог, а потом записать на диск.То есть до тех пор, пока лог гарантированно помещается на диск, данные должны быть правильными. В это время, пока журнал сохраняется, даже если MySQL в это время не работает и страницы данных не записываются обратно на диск, журнал можно использовать для восстановления позже.

Тем не менее, InnoDBredo logдаисправленный размерДа, например, его можно настроить как набор из 4-х файлов, каждый размером 1 Гб. Фиксированный размер также создает проблему,redo logбудетполныйиз.

Поэтому InnoDB зациклилась на записи. Обратите внимание, что здесь есть два указателя.write_posУказывает текущую позицию записи, пока запись обновляется,write_posбудет двигаться назад. а такжеcheck_pointПредставляет контрольную точку, пока InnoDB будетcheck_pointЗапись модификации, на которую указывает, обновляется на диск,check_pointбудет двигаться назад.

Другими словами, возьмите наше простоеupdate T set a = a + 1 where ID = 0;Например, если мы обновим страницу памяти, где находится эта строка данных, и напишемredo log, будет возвращено сообщение об успешной модификации. затем вredo logВ таблице записывается запись обновления определенной страницы памяти.

Обратите внимание, что в этот момент на диске данныеaбез изменений, в памяти,aизменился наa+1,существуетredo logЗапись обновления этой страницы памяти записывается вwrite_posВернуться назад.

В этот момент, еслиcheck_pointДвигайтесь назад, тогда он должен сохранить обновление этой страницы памяти в записи на диск, то естьa+1Напишите обратно на диск, в это время, будь то диск или память,aданныеa+1. Только успешно записано обратно на диск,check_pointдвигаться назад. Этот дизайн делаетredo logможно использовать повторно неограниченное количество раз.

Итак, вот вопрос, мы просто знаем сейчасwrite_posбудет двигаться назад после обновления данных, затемcheck_pointКогда он будет двигаться?

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

Затем проблема возникает снова, если в базе данных в это время выполняется большое количество операций обновления, иcheck_pointСкорость продвижения постоянна, тогдаwrite_posПродолжайте двигаться вперед, и вы будете сыты. Этой ситуации следует избегать InnoDB. Потому что, когда это произойдет, вся система больше не сможет принимать обновления, и все обновления будут заблокированы. Если вы посмотрите на мониторинг, количество обновлений упадет до 0 в это время. Что касается того, как избежать этой ситуации, я хочу дождаться времени настройки, чтобы поговорить об этом, здесь мы просто знаем, что такая ситуация будет.

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

Здесь мы добавляем понятие,чистая страницаа такжегрязные страницы. Чистая страница относится к чтению с диска в память и не была изменена, вы можете понять, что она только запрашивается ине обновляетсяпрошлые страницы данных. Грязные страницы — это страницы данных, которые отличаются от данных на диске.модифицированный. Если в это время выполняется большое количество операций запроса или обновления, то требуется большой объем памяти, а в это время в памяти уже есть различные страницы данных. так что нам делать?

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

Тогда проблема возникает снова, если мы записываем эту грязную страницу обратно на диск из-за нехватки памяти, но обновление этой грязной страницы записывается вredo logразных местах, затем вredo logЧто мне делать, если мне нужно обновить эту страницу? Нам не нужно обновлять грязную страницу,redo logТакже удалите соответствующую запись или как сделать?

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

3.2 Архивные журналы

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

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

Вторая причина заключается в том, чтоredo logОн уникален для движка InnoDB, в других движках нет журнала повторов.

Итак, здесь мы говорим об архивных журналах, которые должны быть на уровне движка.binlog.

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

Другими словами, при восстановлении после сбоя вы можете использоватьbinlogОдна за другой записи восстановления.

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

или сupdate T set a = a + 1 where ID = 0;Например:

Объяснение: Вплоть до обновления страницы данных в памяти, об этом было сказано выше. Затем записывается обновление страницы данныхredo logсередина.

Уведомление, здесь написаноredo log, не написаноredo log, вместо этого записывает файл с именемredo logВ буфере, то есть дисковый ввод-вывод в это время не используется, что не вызовет снижения производительности.

Затем введитеprepareсцена.

Затем написатьbin log.Уведомление, тут сказали написатьbin log, также не является постоянным и также записывается в буфер.

Только когда оба они будут успешно записаны, будет достигнута фаза фиксации транзакции.

Тогда есть два параметраОчень важный.

Эти два параметра определяют, следует ли ждать, покаredo logа такжеbin logПосле постоянного обратно.

sync_binlogа такжеinnodb_flush_log_at_trx_commit.

Скажи это первымinnodb_flush_log_at_trx_commit:

• Когда для параметра установлено значение 1 (по умолчанию 1), это означает, что операция fsync должна вызываться один раз при фиксации транзакции, что является самой безопасной конфигурацией для обеспечения устойчивости.
• Когда для параметра установлено значение 2, при фиксации транзакции выполняется только операция записи, и гарантируется запись только буфера журнала повторов в кэш страниц системы, а операция fsync не выполняется. База данных MySQL выйдет из строя, транзакция не будет потеряна, но простои операционной системы могут привести к потере транзакций.
• Когда параметр установлен в 0, это означает, что операция журнала повторов не выполняется, когда транзакция зафиксирована.Эта операция завершается только в главном потоке, а операция fsync журнала повторов выполняется каждую 1 секунду в главном потоке. поток, поэтому сбой экземпляра теряется не более чем транзакциями за 1 секунду. (Главный поток отвечает за асинхронный сброс данных из пула буферов на диск для обеспечения согласованности данных).

То есть, если мы установим его в 1, при последнем коммите он вызоветfsyncждатьredo logУпорствовать, только чтобы вернуться.

скажи это сноваsync_binlog:

• Когда sync_binlog=0, это означает, что каждый раз при отправке транзакции выполняется только запись, а не fsync.
• Когда sync_binlog=1, это означает, что fsync будет выполняться каждый раз при отправке транзакции.
• Когда sync_binlog=N (N>1), это означает, что транзакция записывается каждый раз, когда она отправляется, но синхронизируется только после накопления N транзакций. Но если он выйдет из строя, последние N операторов могут быть потеряны. То есть, если мы установим его в 1, окончательный вариант отправки будет таким же, как указано выше, в ожидании системы.fsync.

Итак, зачем нам нужна двухэтапная фиксация для обеспечения согласованности данных?

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

Сделайте еще одно предположение, мы уже писалиbin log, время простоя, MySQL оценит после перезапускаredo logУ него уже естьcommitОпределяет, если есть, представить; в противном случае идти на судbin logПолный ли он, если да, то коммит, иначе откат.

Так что же произойдет, если мы не зафиксируем сцену?

Предположим, мы сначала фиксируемredo log, отправить еще разbin log，此时逻辑和两阶段提交一样，但是没有了两次验证。 то если мыredo logПосле подачи выключения, затем после перезапуска мы можемredo logдля восстановления данных. Но поскольку мыbin logне обновлялся в будущем, поэтому в будущем при использованииbin logПри восстановлении или синхронизации ведомой библиотеки это вызоветнесогласованность данных. (Проблема синхронизации master-slave будет объяснена в следующей статье)

Сделайте еще одно предположение, сначала отправьтеbin log, отправить еще разredo log. Тогда данные не обновляются при восстановлении, а будут использоваться в дальнейшемbin log, вы найдете здесьнесогласованность данных.

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

напиши в конце

Прежде всего, спасибо, что вы здесь.

Я надеюсь, что эта статья может помочь вам и углубить ваше понимание MySQL. Конечно, объем статьи ограничен, и уровень автора тоже ограничен, и детали многих мест в статье не обсуждаются. Многие точки знаний будут постоянно пополняться в будущих статьях. Кроме того, если вы обнаружите, что у автора что-то не так, пожалуйста, не стесняйтесь меня поправлять, спасибо!

Во-вторых, я хотел бы поблагодарить брата Сюн, который оказал мне большую помощь! Кроме того, я также хотел бы поблагодарить г-на Дин Ци, Я выбрал «45 практических лекций по MySQL» в качестве основного направления обучения.

PS: Если у вас есть другие вопросы, вы также можете найти автора на официальном аккаунте. Кроме того, все статьи будут обновлены в общедоступном аккаунте как можно скорее, добро пожаловать, чтобы поиграть с автором~