предисловие

Я имею дело с базой данных каждый день и могу писать десятки операторов SQL в день, но знаете ли вы, как наша система взаимодействует с базой данных? Как 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 написан для нас, откуда машина знает, о чем вы говорите? Еще более преувеличенным является то, что машина знает только 0 и 1. В настоящее время解析器на поле. Он будет анализировать оператор 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 примерно такие:

1. Механизм хранения innodb будет искать наличие этих данных с id=1 в пуле буферов.
2. Если он не существует, он загрузит его с диска и сохранит в пуле буферов.
3. К записи будет добавлена ​​эксклюзивная блокировка (вы не можете изменить ее, пока ее изменяют другие. Этот механизм не будет представлен в этой статье, и будет специально написан в будущем, чтобы объяснить его подробно)

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

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

Мы только что сказали, что когда оператор готов к обновлению, оператор был загружен в буферный пул, на самом деле есть такая операция, то есть, когда оператор загружается в буферный пул, происходит вставка журнала. в файл журнала отмены, то есть записать исходное значение записи с id=1.

Какова цель этого?

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

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

В этот момент наш выполненный оператор SQL был загружен в буферный пул, и затем мы начинаем обновлять этот оператор.Обновленная операция фактически выполняется в буферном пуле.Затем возникает проблема, согласно набору, который мы обычно разрабатываем.теория缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据, то не являются ли данные в буферном пуле грязными данными? Правильно, текущие данные — это грязные данные, а запись в пуле буферов — это小强Записи в базе данных есть旺财, Как MySQL справляется с этой ситуацией?

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

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

Голос за кадром: Файл журнала повторов уникален для InnoDB, он находится на уровне механизма хранения, а не на уровне MySQL.

Redo означает «готов к выполнению», «будет делать», журнал повторов — это запись некоторых операций, которые необходимо выполнить, например, что будет сделано в это время.update students set stuName='小强' where id=1;Тогда эта операция будет записана в буфер журнала повторов, что? Как придумать буфер журнала повторов Это очень просто, для повышения эффективности MySQL помещает все эти операции в память для завершения, а затем они будут выполняться в памяти.когда-тоСохраните его на диск.

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

1. Подготовьтесь к обновлению оператора SQL
2. MySQL (innodb) сначала отправится в буферный пул (BufferPool) для поиска данных, если не будет найден, он отправится на диск для поиска, если найдет, загрузит данные в буферный пул (BufferPool)
3. При загрузке в буферный пул исходная запись этих данных будет сохранена в файле журнала отмены.
4. innodb будет выполнять операции обновления в пуле буферов.
5. Обновленные данные будут записаны в буфер журнала повторов.

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

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

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

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

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

Если сервер базы данных выходит из строя после того, как буфер журнала повторов сбрасывается на диск, что нам делать с обновленными данными? В этот момент данные находятся в памяти, не потеряны ли данные? Нет, на этот раз данные не будут потеряны, потому что данные в буфере журнала повторов были записаны на диск и сохранены.Даже если база данных выйдет из строя, 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, в основном то же самое, но это еще не конец. Следующее также необходимо представить файлы журнала уровня MySQLbin log

bin log файл журнала: записывайте весь процесс работы

упомянутый вышеredo logэто файл журнала, специфичный для механизма хранения InnoDB, аbin logОтносится к журналам уровня MySQL.redo logЗаписанные вещи смещены в сторону физических свойств, таких как: «какие данные, какие модификации были сделаны».bin logИмеет уклон в сторону логики, по аналогии: "Операция обновления сдается записи с id 1 в таблице студентов" Основные черты обоих сводятся к следующему:

природа	redo Log	bin Log
Размер файла	Размер журнала повторов фиксированный (его также можно задать в конфигурации, обычно достаточно значения по умолчанию)	bin log можно настроить по параметрамmax_bin log_sizeустановить каждыйbin logРазмер файла (но обычно его изменять не рекомендуется).
Метод реализации	redo logдаInnoDBРеализуется на уровне движка (то есть он уникален для хранилища Innodb)	bin logОн реализован на уровне MySQL и может использоваться всеми движками.bin logбревно
Метод записи	Журнал повторов записывается в циклическом режиме записи. Когда запись достигает конца, он возвращается к началу для циклической записи журнала.	Журнал bin записывается путем добавления.Когда размер файла превышает заданное значение, последующие журналы будут записываться в новый файл.
сцены, которые будут использоваться	redo logПодходит для защиты от сбоев (на самом деле это очень похоже на функцию сохранения Redis).	bin logПодходит для репликации master-slave и восстановления данных

** Как файл журнала bin сбрасывается на диск?**

Существуют связанные стратегии для очистки журнала бункера, и эту стратегию можно передать черезsync_bin logДля изменения значение по умолчанию равно 0, что означает, что кеш ОС записывается первым, то есть при отправке транзакции данные не будут поступать непосредственно на диск, так что в случае сбоя машиныbin logДанные все равно будут потеряны. Поэтому рекомендуетсяsync_bin logУстановите на 1 дляЗапись данных напрямую на дискв файле.

Существуют следующие режимы очистки бин-лога

1. ЗАЯВЛЕНИЕ

Репликация на основе операторов SQL (SBR), каждый оператор SQL, который изменяет данные, будет записан в журнал bin.

[Преимущества]: нет необходимости записывать изменения каждой строки, уменьшая количество журналов бинарных журналов, экономя ввод-вывод, тем самым повышая производительность.

[Недостатки]: в некоторых случаях данные master-slave будут несогласованными, например, при выполнении sysdate(), sleep() и т. д.

2. РЯД

Репликация на основе строк (RBR) не записывает контекстную информацию каждого оператора SQL, необходимо только записать, какие данные были изменены.

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

[Недостаток]: будет создано большое количество журналов, особенно когда изменение таблицы вызовет стремительный рост журнала.

3. СМЕШАННЫЙ

Смешанная репликация (MBR) на основе режимов STATMENT и ROW, обычная репликация использует режим STATEMENT для сохранения журнала бинов, для операций, которые нельзя реплицировать в режиме STATEMENT, используйте режим ROW для сохранения бинарного журнала

С того времениbin logЭто также файл журнала, так что же он записывает данные?

На самом деле, когда MySQL фиксирует транзакцию, она не только записывает данные из буфера журнала повторов вredo logизмененные данные также будут записаны в файл журнала bin, а измененные данные также будут записаны в файл журнала bin.bin logИмя файла и измененное содержимое находятся вbin logМесто в записи дляredo log, и, наконец, вredo logНаконец, напишите тег фиксации, который означает, что транзакция была успешно зафиксирована.

Если база данных выйдет из строя сразу после того, как данные будут записаны в файл журнала bin, будут ли данные потеряны?

Во-первых, несомненно, что до тех пор, покаredo logВ конце нет метки фиксации, что указывает на то, что эта транзакция не удалась. Но данные не теряются, потому что они были записаны наredo logв файле диска. Когда MySQL перезапустится, онredo logДанные при восстановлении (загрузке) вBuffer Poolсередина.

Хорошо, до сих пор мы в основном ввели операцию обновления, но вы чувствуете, что чего-то не хватает, что не было сделано? Вы также обнаружили, что обновленная запись выполняется только в памяти в это время, даже если она дает сбой и восстанавливается, она просто загружает обновленную запись вBuffer PoolВ настоящее время запись в базе данных MySQL по-прежнему имеет старое значение, то есть данные в памяти, по нашему мнению, все еще являются грязными данными Что нам делать в это время?

На самом деле у MySQL будет фоновый поток, который будет отправлять намBuffer PoolГрязные данные в базе данных сбрасываются в базу данных MySQL, так что данные в памяти и в базе данных унифицированы.

Резюме этой статьи

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

Давайте рассмотрим 1. Буферный пул — очень важный компонент MySQL, потому что все добавления, удаления и модификации базы данных выполняются в буферном пуле. 2. Журнал отмены записывает, как он выглядел до манипуляции с данными. 3. Журнал повторов записывает, как выглядят данные после обработки (журнал повторов уникален для механизма хранения Innodb). 4.bin журнал записывает всю запись операции (это очень важно для репликации master-slave)

Описание процесса от подготовки к обновлению части данных до совершения транзакции.

1. Сначала исполнитель запрашивает данные согласно плану выполнения MySQL. Сначала он запрашивает данные из кэш-пула. Если запроса нет, он переходит к базе данных для выполнения запроса. Если запрос найден, он быть помещены в пул кеша. 2. Когда данные кэшируются в пуле буферов, будет записан файл журнала отмены. 3. Действие обновления выполняется в BufferPool, и обновленные данные будут одновременно добавлены в буфер журнала повторов. 4. Транзакция может быть отправлена ​​после завершения, и при ее отправке будут выполнены следующие три действия. 5. (Первое) сбросить данные из буфера журнала повторов в файл журнала повторов. 6. (Второе) Запишите запись об этой операции в файл журнала bin. 7. (Третье) Запишите имя файла бинарного журнала и местоположение обновленного содержимого в бинарном журнале в журнале повторов и добавьте отметку фиксации в конце журнала повторов.

На данный момент вся транзакция обновления завершена

заключительные замечания

До сих пор то, как система взаимодействует с базой данных MySQL, отправляет обновленный оператор SQL в MySQL, какие процессы выполняет MySQL и что она делает, было объяснено макроскопически. Более подробная информация о буферном пуле будет объяснена в последующих статьях.