предисловие
Я имею дело с базой данных каждый день и могу писать десятки операторов SQL в день, но знаете ли вы, как наша система взаимодействует с базой данных? Как MySQL помогает нам хранить данные и как он помогает нам управлять транзакциями? .... вы чувствуете, что ваш разум в основном пуст, за исключением того, что вы написали несколько «выбрать * из двойного»? Gold Three Silver Four Преимущества для читателей: ОрганизованоБоевые заметки MySQL,Золото три серебра четыре коллекции материалов интервью. Эта статья познакомит вас с миром MySQL, позволит вам полностью понять, как система взаимодействует с MySQL и что делает MySQL, когда получает отправленный нами оператор SQL.
Драйвер MySQL
Когда наша система общается с базой данных MySQL, невозможно принимать и отправлять запросы без причины.Даже если вы ничего не делаете, должны быть другие «люди», которые что-то для нас сделали. используемая база данных MySQL более или менее будет знать концепцию драйвера MySQL. Именно этот драйвер MySQL помогает нам подключаться к базе данных на нижнем уровне, и только после установления соединения мы можем иметь последующие взаимодействия. См. рисунок ниже
Таким образом, до того, как система взаимодействует с MySQL, драйвер MySQL поможет нам установить соединение, а затем нам нужно будет только отправить операторы SQL для выполнения CRUD. Один запрос SQL установит соединение, а несколько запросов установят несколько соединений.Тогда проблема в том, что наша система точно не используется одним человеком.Иными словами, должно быть несколько запросов, конкурирующих за соединения одновременно. . Наши веб-системы обычно развернуты в контейнерах tomcat, а tomcat может обрабатывать несколько запросов одновременно, что приводит к тому, что несколько запросов устанавливают несколько соединений, а затем закрывают их после использования.В чем проблема? Как показано ниже
Система Java основана на протоколе TCP/IP при подключении к базе данных MySQL через драйвер MySQL, поэтому, если каждый запрос должен создавать соединение и разрушать соединение, это неизбежно приведет к ненужным потерям и снижению производительности, то есть Очевидно, что нецелесообразно часто создавать и уничтожать соединения во время многопоточного запроса, описанного выше. Это неизбежно сильно снизит производительность нашей системы, но если вы предоставите какие-то фиксированные потоки для соединения, разве вам не нужно постоянно создавать и уничтожать соединения? Я верю, что знающие друзья понимающе улыбнутся, да, они говорят о пуле соединений с базой данных.
Пул соединений с базой данных: поддерживать определенное количество соединений, чтобы облегчить системе получение соединений, переходить к пулу, чтобы получать их, когда они используются, и возвращать их обратно, когда они израсходованы Нам не нужно заботиться о создании и уничтожение соединений, ни то, как поддерживается пул потоков, они связаны.
Общие пулы соединений с базами данных включают Druid, C3P0 и DBCP. Принцип реализации пулов соединений здесь подробно обсуждаться не будет. Использование пулов соединений значительно снижает стоимость непрерывного создания и уничтожения потоков. Это знаменитая идея «объединения». , будь то это можно увидеть в пуле потоков или пуле HTTP-соединений.
пул соединений с базой данных
Пока что мы уже знаем, что когда наша система обращается к базе данных MySQL, установленное соединение не создается каждый раз при выполнении запроса, а получается из пула соединений с базой данных, что решает проблему повторного создания и потери производительности. вызванное разрушением соединения, пропало. Однако здесь есть небольшая проблема: бизнес-система параллельна, и MySQL может принимать запросы только в одном потоке?
Фактически система архитектуры MySQL также предоставила такой пул, который также является пулом соединений с базой данных. Обе стороны управляют каждым соединением через пул соединений с базой данных, поэтому, с одной стороны, потоку не нужно конкурировать за соединение перед этим, и, что более важно, ему не нужно повторно создавать и уничтожать соединение.
До сих пор проблема соединения между системой и базой данных MySQL была ясно объяснена. Так как же обрабатываются эти соединения в базе данных MySQL и кто их обрабатывает?
Сетевые соединения должны обрабатываться потоками
Студенты, которые немного разбираются в основах вычислительной техники, знают, что соединения в сети обрабатываются потоками.Так называемое сетевое соединение представляет собой запрос, и каждый запрос будет обрабатываться соответствующим потоком. Другими словами, запросы на операторы SQL обрабатываются в MySQL один за другим.
Как эти потоки будут обрабатывать эти запросы? Что будет сделано?
SQL-интерфейс
Поток, который обрабатывает запрос в MySQL, получает оператор SQL после получения запроса и передает его интерфейсу SQL для обработки.
анализатор запросов
Если есть такой SQL сейчас
SELECT stuName,age,sex FROM students WHERE id=1
Но этот SQL написан для нас, людей, откуда машина узнает, о чем вы говорите? Здесь в игру вступает парсер. Он будет анализировать оператор SQL, переданный интерфейсом SQL, и переводить его на язык, который может понять MySQL.Что касается того, как его анализировать, нет необходимости вникать в это, это не что иное, как его собственный набор связанных правил.
Теперь, когда SQL разобран на то, что распознает MySQL, следующим шагом будет его выполнение? Теоретически это так, но MySQL гораздо мощнее, и это также поможет нам выбрать оптимальный путь запроса.
Каков оптимальный путь запроса? То есть MySQL выполнит запрос наиболее эффективным образом.
Как это делается? Речь идет об оптимизаторе запросов MySQL.
Оптимизатор запросов MySQL
Нам не нужно заботиться о том, как оптимизатор запросов реализован внутри. Мне нужно знать, что MySQL поможет мне оптимизировать этот оператор SQL наилучшим образом, и сгенерирует часть плана выполнения, такого как вы создали несколько индексов, MySQL будетпринцип наименьших затратЧтобы выбрать использование соответствующего индекса, стоимость здесь в основном включает два аспекта: стоимость ввода-вывода и стоимость ЦП.
Стоимость ввода-вывода: То есть стоимость загрузки данных с диска в память.По умолчанию IO стоимость чтения страницы данных равна 1. MySQL читает данные в виде страниц, то есть при использовании определенных данных он не будет только при чтении этих данных соседние данные этих данных также будут считываться в память вместе.Это известный принцип локальности программы, поэтому MySQL каждый раз будет читать целую страницу, а стоимость одной страницы равна 1. Таким образом, стоимость IO в основном связана с размером страницы.
Стоимость процессора: После считывания данных в память также необходимо определить, соответствуют ли данные условиям и стоимости операций ЦП, таких как сортировка.Очевидно, что это связано с количеством строк.По умолчанию стоимость обнаружения записей составляет 0,2.
Оптимизатор MySQL рассчитает индекс с наименьшей стоимостью «затраты на ввод-вывод + ЦП» для выполнения.
После того, как оптимизатор выполнит такие шаги, как выбор оптимального индекса, он вызовет интерфейс механизма хранения и начнет выполнять операторы SQL, проанализированные и оптимизированные MySQL.
механизм хранения
Оптимизатор запросов вызовет интерфейс механизма хранения для выполнения SQL, то есть фактическое выполнение SQL выполняется в механизме хранения. Данные хранятся в памяти или на диске (механизм хранения — очень важный компонент, о котором будет подробно рассказано позже)
Актуатор
Исполнитель является очень важным компонентом, потому что операции предыдущих компонентов в конечном итоге должны выполняться путем вызова интерфейса механизма хранения через исполнителя. Наконец, исполнитель вызывает интерфейс механизма хранения в соответствии с серией планов выполнения для завершения выполнения SQL.
Знакомство с механизмом хранения
Мы проиллюстрируем обновленным оператором SQL, SQL выглядит следующим образом
UPDATE students SET stuName = '小强' WHERE id = 1
Когда наша система отправит такой запрос в MySQL, MySQL, наконец, вызовет механизм хранения через исполнителя в соответствии с рядом процессов, которые мы представили выше. При выполнении этого SQL данные, соответствующие оператору SQL, находятся либо в памяти, либо на диске, если он работает непосредственно на диске, скорость такого случайного чтения и записи IO определенно неприемлема, поэтому каждый раз в When SQL выполняется, его данные загружаются в память, что является очень важным компонентом в InnoDB:буферный пул Buffer Pool
Buffer Pool
Буферный пулInnoDBОчень важная структура памяти в механизме хранения, как следует из названия, буферный пул на самом деле является функцией, похожей на Redis, которая действует как кеш, потому что мы все знаемMySQLДанные в итоге хранятся на диске, если нет буферного пула, то каждый запрос к БД будет искаться на диске, поэтому должны быть операции ввода-вывода, что однозначно недопустимо. Но с буферным пулом мы будем хранить результаты запроса в буферном пуле в первый раз, когда мы запрашиваем, чтобы при последующем запросе мы сначала запросили из буферного пула, а затем поместили его в буферный пул. , как показано ниже
Согласно приведенному выше рисунку, шаги выполнения этого оператора SQL примерно такие:
- Механизм хранения innodb будет искать наличие этих данных с id=1 в пуле буферов.
- Если он не существует, он загрузит его с диска и сохранит в пуле буферов.
- К записи будет добавлена эксклюзивная блокировка (вы не можете изменить ее, пока ее изменяют другие. Этот механизм не будет представлен в этой статье, и будет специально написан в будущем, чтобы объяснить его подробно)
файл журнала отмены: запись того, как выглядели данные до того, как они были изменены
Отменить, как следует из названия, означает, что ничего не сделано, ничего не произошло. журнал отмены - это некоторый журнал, в котором ничего не произошло (то, что было в оригинале)
Мы только что сказали, что когда оператор готов к обновлению, оператор был загружен в буферный пул, на самом деле есть такая операция, то есть, когда оператор загружается в буферный пул, происходит вставка журнала. в файл журнала отмены, то есть записать исходное значение записи с id=1.
Какова цель этого?
Самой большой особенностью движка хранения Innodb является то, что он поддерживает транзакции.Если обновление не удается, то есть происходит сбой фиксации транзакции, то все операции в транзакции необходимо откатить до состояния перед выполнением.То есть, когда транзакция не удалась, это не повлияет на исходные данные, посмотрите на картинку и поговорите
Здесь лишнее слово, на самом деле MySQL — это тоже система, как и функциональная система java, которую мы обычно разрабатываем. MySQL использует набор систем, разработанных на своем собственном соответствующем языке. , поскольку что он может делать, он должен быть определен дизайнерами в начале или развиваться в соответствии с фактическими изменениями сцены. Так что все успокойтесь и принимайте MySQL как систему, чтобы понять и ознакомиться с ней.
В этот момент наш исполняемый оператор SQL был загружен в буферный пул, а затем мы начинаем обновлять этот оператор.Обновленная операция фактически выполняется в буферном пуле.Затем возникает проблема в соответствии с набором, который мы обычно разрабатываем.Когда данные в теоретическом пуле буферов несовместимы с данными в базе данных, мы считаем данные в кеше грязными данными, тогда не являются ли данные в пуле буферов грязными данными? Правильно, в настоящее время эти данные являются грязными данными, запись в буферном пуле — Xiaoqiang, а запись в базе данных — Wangcai.Как MySQL справляется с этой ситуацией?Продолжить чтение
файл журнала повторов: запись того, как выглядят данные после их изменения.
Помимо загрузки файлов с диска и сохранения записей перед операцией в файл журнала отмены, в памяти выполняются и другие операции.Характеристикой данных в памяти является то, что они теряются после сбоя питания. Если в это время сервер, на котором находится MySQL, выйдет из строя, все данные в буферном пуле будут потеряны. В это время файл журнала повторов должен показать свое волшебство.
Голос за кадром: Файл журнала повторов уникален для InnoDB, он находится на уровне механизма хранения, а не на уровне MySQL.
redo записывает значение после изменения данных, независимо от того, была ли транзакция зафиксирована или нет, оно будет записано.Например, что будет сделано в это время, это обновить учащиеся, установленные stuName='Xiaoqiang', где id=1; затем эта операция будет записываться в буфер журнала повторов, что? Как придумать буфер журнала повторов Это очень просто, для повышения эффективности MySQL сначала помещает все эти операции в память для завершения, а затем они будут выполняться в памяти.когда-тоСохраните его на диск.
На данный момент мы все должны быть знакомы с тем, как исполнитель MySQL вызывает механизм хранения, чтобы загрузить часть SQL в пул буферов и записать, какие журналы.Процесс выглядит следующим образом:
- Подготовьтесь к обновлению оператора SQL
- MySQL (innodb) сначала отправится в буферный пул (BufferPool) для поиска данных, если не будет найден, он отправится на диск для поиска, если найдет, загрузит данные в буферный пул (BufferPool)
- При загрузке в буферный пул исходная запись этих данных будет сохранена в файле журнала отмены.
- innodb будет выполнять операции обновления в пуле буферов.
- Обновленные данные будут записаны в буфер журнала повторов.
Упомянутые выше шаги выполняются при нормальных обстоятельствах, но разработка и оптимизация программ выполняются не только для этих нормальных обстоятельств, но и с цельюПроблемы, возникающие в этих критических секциях и крайних случаяхоптимизировать дизайн
В это время, если сервер выходит из строя, данные в кеше все равно теряются. Это очень раздражает, хотя данные всегда теряются, можно ли их сохранить прямо на диск, а не в память? Очевидно, что нет, потому что это было введено выше, целью работы в памяти является повышение эффективности.
В настоящее время, если MySQL действительно не работает, это не имеет значения, потому что MySQL будет считать эту транзакцию неудачной, поэтому данные останутся такими же, как и до обновления, и это не окажет никакого влияния.
Ну и выписка тоже обновляется, поэтому нужно подать обновленное значение, то есть нужно отправить эту транзакцию, потому что пока транзакция будет успешно отправлена, последние изменения будут сохранены в базе данных.перед совершением сделкибудут по-прежнему связаны другие действия
Чтобы сохранить данные в буфере журнала повторов на диск, нужно записать данные из буфера журнала повторов в файл журнала повторов на диске. диск (Конкретные стратегии кратко изложены в следующем небольшом резюме и будут представлены подробно.), выше
Если сервер базы данных выходит из строя после того, как буфер журнала повторов сбрасывается на диск, что нам делать с обновленными данными? В этот момент данные находятся в памяти, не потеряны ли данные? Нет, на этот раз данные не будут потеряны, потому что данные в буфере журнала повторов были записаны на диск и сохранены.Даже если база данных выйдет из строя, MySQL сохранит файл журнала повторов при следующем перезапуске. Содержимое восстанавливается в буферный пул (насколько я понимаю, это похоже на механизм сохраняемости Redis. Когда Redis запускается, он проверяет rdb или aof или оба и восстанавливает данные в соответствии с постоянным файлом. в память)
Что было сделано до сих пор, вызывая интерфейс механизма хранения из исполнителя?
1. Подготовьтесь к обновлению оператора SQL
2. MySQL (innodb) сначала обратится к пулу буферов (BufferPool), чтобы найти эти данные.Если он не будет найден, он отправится на диск, чтобы найти его.Если он будет найден, он загрузит данные.
3. При загрузке в буферный пул исходная запись этих данных будет сохранена в файл журнала отмен.
4.Innodb будет выполнять операции обновления в буферном пуле.
5. Обновленные данные будут записаны в буфер журнала повторов.
---Это то, что было подытожено ранее---
6. Когда MySQL фиксирует транзакцию, он записывает данные из буфера журнала повторов в файл журнала повторов.Очистить диск можно с помощью параметра innodb_flush_log_at_trx_commit.
Значение 0 означает, что не сбрасывать на диск.
Значение 1 означает немедленное сброс на диск.
Значение 2 означает, что сначала нужно сбросить в кеш ОС.
7. Когда myslq перезапустится, он восстановит журнал повторов в буферном пуле.
До сих пор, когда исполнитель MySQL вызывает интерфейс механизма хранения для выполнения SQL, предоставленного [планом выполнения], то, что делает InnoDB, в основном то же самое, но это не конец. Следующее также должно представить файл журнала уровня MySQL bin log
bin log файл журнала: записывайте весь процесс работы
Описанный выше журнал повторов представляет собой файл журнала, специфичный для механизма хранения InnoDB, а журнал bin — это журнал уровня MySQL. Вещи, записанные в журнале повторов, смещены в сторону физических свойств, таких как: «какие данные были изменены?» Журнал корзины имеет смещенный логический характер, аналогичный: «обновил запись с идентификатором 1 в таблице студентов». Основные особенности двух из них резюмируются следующим образом:
Как файл журнала bin сбрасывается на диск?
Очистка бинарного журнала имеет связанную стратегию. Стратегию можно изменить с помощью sync_bin log. Значение по умолчанию – 0, что означает, что кеш ОС записывается первым. Данные бинарного журнала простоя все равно будут потеряны. Поэтому рекомендуется установить для журнала sync_bin значение 1, чтобы указатьЗапись данных напрямую на дискв файле.
Существуют следующие режимы очистки бин-лога
1. ЗАЯВЛЕНИЕ
Репликация на основе операторов SQL (SBR), каждый оператор SQL, который изменяет данные, будет записан в журнал bin.
[Преимущества]: нет необходимости записывать изменения каждой строки, уменьшая количество журналов бинарных журналов, экономя ввод-вывод, тем самым повышая производительность.
[Недостатки]: в некоторых случаях данные master-slave будут несогласованными, например, при выполнении sysdate(), sleep() и т. д.
2. РЯД
Репликация на основе строк (RBR) не записывает контекстную информацию каждого оператора SQL, необходимо только записать, какие данные были изменены.
[Преимущества]: Нет проблем с тем, что вызов и запуск хранимых процедур, функций или триггеров не могут быть правильно скопированы при определенных обстоятельствах.
[Недостаток]: будет создано большое количество журналов, особенно когда изменение таблицы вызовет стремительный рост журнала.
3. СМЕШАННЫЙ
Смешанная репликация (MBR) на основе режимов STATMENT и ROW, обычная репликация использует режим STATEMENT для сохранения журнала бинов, для операций, которые нельзя реплицировать в режиме STATEMENT, используйте режим ROW для сохранения бинарного журнала
Итак, поскольку bin log также является файлом журнала, куда он записывает данные?
Фактически, когда MySQL фиксирует транзакцию, она не только записывает данные из буфера журнала повторов в файл журнала повторов, но также записывает измененные данные в файл журнала bin, а также записывает измененный файл журнала bin. имя файла журнала и измененное содержимое журнала bin записываются в журнал повторов, и, наконец, в конце журнала повторов записывается отметка фиксации, что означает, что транзакция была успешно зафиксирована.
Если база данных выйдет из строя сразу после того, как данные будут записаны в файл журнала bin, будут ли данные потеряны?
Прежде всего, несомненно, что если в конце журнала повторов нет метки фиксации, эта транзакция должна была завершиться неудачно. Но данные не теряются, потому что они были записаны в файл журнала повторов на диске. Когда MySQL перезапустится, данные в журнале повторов будут восстановлены (загружены) в пул буферов.
Хорошо, до сих пор мы в основном ввели операцию обновления, но вы чувствуете, что чего-то не хватает, что не было сделано? Вы также обнаружили, что обновленная запись выполняется только в памяти в это время, даже если она дает сбой и восстанавливается, она просто загружает обновленную запись в пул буферов, В это время запись в базе данных MySQL все еще имеет старое значение , то есть данные в памяти, по нашему мнению, все еще являются грязными данными, что нам делать в это время?
На самом деле у MySQL будет фоновый поток, который в определенное время будет сбрасывать грязные данные из нашего буферного пула в базу данных MySQL, чтобы память и данные базы данных были унифицированы.
Резюме этой статьи
На данный момент концепции и взаимосвязи буферного пула, журнала повторов, буфера и журнала отмены, журнала повторов и журнала корзины в основном одинаковы.
Давайте рассмотрим
- Буферный пул — очень важный компонент MySQL, потому что все добавления, удаления и модификации базы данных выполняются в буферном пуле.
- Журнал отмены записывает, как выглядели данные до операции.
- Журнал повторов записывает, как выглядят данные после манипулирования ими (журнал повторов уникален для механизма хранения Innodb).
- Журнал bin записывает всю запись операции (это очень важно для репликации master-slave)
Описание процесса от подготовки к обновлению части данных до совершения транзакции.
- Сначала исполнитель запрашивает данные согласно плану выполнения MySQL.Сначала он запрашивает данные из кэш-пула.Если запроса нет, то он переходит к базе данных для выполнения запроса.Если запрос найден, он будет размещен в кэш-пуле.
- Пока данные кэшируются в буферном пуле, файл журнала отмены будет записываться
- Действие обновления выполняется в BufferPool, и обновленные данные будут одновременно добавлены в буфер журнала повторов.
- После того, как транзакция завершена, транзакция может быть отправлена, и при отправке будут выполнены следующие три действия.
- (Первое) сбросить данные из буфера журнала повторов в файл журнала повторов.
- (Второе) Запишите запись об этой операции в файл журнала bin.
- (Третье) Запишите имя файла журнала bin и местоположение обновленного содержимого в журнале bin в журнале повторов и добавьте отметку фиксации в конце журнала повторов.
На данный момент вся транзакция обновления завершена
Суммировать
На этом статья заканчивается.Как система взаимодействует с базой данных MySQL, отправляет обновленный оператор SQL в MySQL, какие процессы выполняет MySQL и что она делает, было объяснено с точки зрения макросов. Дополнительные сведения о буферном пуле будут подробно описаны в следующих статьях.Боевые заметки MySQL,Золото три серебра четыре коллекции материалов интервью.