Некоторые проблемы

1. Почему не рекомендуется использовать номер заказа в качестве первичного ключа?
2. Зачем добавлять индекс к полю, которое нужно отсортировать?
3. Не существует ли запись для обновления, которая приведет к блокировке всей таблицы?
4. В чем разница между редологом и бинлогом?
5. Как MySQL откатывает sql?
6. Одинаков ли эффект char(50) и varchar(50)?

Обзор знаний индекса

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

Массив должен быть упорядочен при вставке, чтобы можно было использовать метод бинарного поиска для достижения временной сложности O(log(N)) при поиске, а поддержка запроса диапазона также очень хороша, но если вставка не находится в конце массива, ему нужно переместить все следующие данные, а временная сложность - O (N). Таким образом, упорядоченные массивы подходят только для хранения статических данных, таких как данные конфигурации, которые редко изменяются, или исторические данные. Здесь должны быть некоторые сомнения: можно ли заменить массив другим связанным списком с линейной структурой данных, чтобы решить проблему слишком медленной работы массива из-за перемещения данных?Чтобы ответить на этот вопрос, нам нужно понять процесс чтения операционной системы файлов , дисковый ввод-вывод - относительно медленная операция. Чтобы улучшить скорость чтения, мы должны попытаться уменьшить дисковые операции ввода-вывода. Операционная система обычно считывает данные с 4 КБ в качестве страницы данных, в то время как MySQL обычно использует 16 КБ в качестве блока данных. Считанные блоки данных будут кэшироваться в памяти.Если в одном и том же блоке данных выполняется несколько операций чтения данных, требуется только один дисковый ввод-вывод, а если последовательно согласованные записи также хранятся последовательно в файле, несколько данных могут быть прочитаны одновременно. Таким образом, скорость запроса диапазона также может быть значительно улучшена.Очевидно, что связанный список не имеет этого преимущества.

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

В крайних случаях бинарное дерево станет линейной структурой, то есть каждый узел имеет только левые дочерние узлы или только правые дочерние узлы, поэтому невозможно использовать бинарный поиск для обхода назад от первого узла, поэтому для сохранения O (log(N)) временная сложность, нам нужно скорректировать узлы при вставке узлов, чтобы обеспечить баланс дерева, поэтому временная сложность вставки сбалансированного бинарного дерева также равна O(log(N)), бинарное дерево имеет только два дочерних узла, если объем данных большой, дерево очень высокое.Каждый слой дерева обычно не хранится в одном и том же блоке данных.Чтобы свести к минимуму количество операций чтения и записи на диск, мы используем N -арное дерево вместо двоичного дерева. В MySQL это N обычно равно 1200, поэтому, если высота дерева равна 4, оно может хранить миллиарды данных, а первые два слоя дерева обычно находятся в памяти. Дерево, используемое в MySQL, обычно использует неконечные узлы для построения индексов, а конечные узлы используются для сохранения определенного значения.

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

Согласно приведенному выше описанию, мы можем получить следующую информацию:

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

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

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

• Если функция используется для индекса при запросе, индекс, построенный на основе значения предыдущего столбца, не может быть использован.Индекс следует принципу соответствия по самому левому краю, поэтому, если вам нужно запросить, содержит ли значение столбца определенную строку , не получится использовать индекс, если есть такая необходимость, можно использовать полнотекстовый индекс, а если запрос начинается с определенной строки, то совместный индекс может использовать индекс на основе первого столбца запроса, а только на основе второго столбца будет недоступен.К индексу эффективность использования ИН выше, чем НЕ= при запросе. Также рекомендуется установить индексируемый столбец ненулевым, что связано с хранением полей NULL, которые будут проанализированы ниже.

формат хранения

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

Табличное пространство является высшим уровнем структуры хранения, и все данные хранятся в табличном пространстве.По умолчанию используется общее табличное пространство ibdata1.Если включен innodb_file_per_table, данные каждой таблицы будут храниться в отдельной таблице пространство, то есть каждое табличное пространство будет храниться в отдельном табличном пространстве.Каждая таблица будет иметь файл,

Табличное пространство состоит из различных сегментов.Механизм хранения InnoDB организован по индексу, а конечные узлы в индексе используются для записи данных и хранения в сегменте данных, а неконечные узлы используются для построения индекса. и хранятся в сегменте индекса.Я болтаю, когда буду анализировать блокировку позже.

Экстент состоит из последовательных страниц, в любом случае экстент составляет 1 МБ,

В области может быть несколько страниц, и каждая страница по умолчанию имеет размер 16 КБ, поэтому по умолчанию область может содержать последовательных страниц 64. Размер страницы можно установить с помощью innodb_page_size, а записи конкретных строк хранятся на странице. . Строка записей окончательно хранится в файле в двоичном виде. Нам нужно иметь возможность анализировать значение каждого столбца в строке записей. При сохранении нам нужно иметь фиксированный формат. По крайней мере, нам нужно знать, как много места занимает каждый столбец, и MySQL определяет некоторые типы данных фиксированной длины, такие как int, tinyint, bigint, char array, float, double, date, datetime, timestamp и т. д., нам нужно только прочитать байты соответствующего длина для этих полей, а затем анализировать их в соответствии с типом. Для полей переменной длины, таких как varchar, varbinary и т. д., должно быть место для хранения фактической длины поля отдельно и, конечно, информации заголовка необходим для хранения метаданных, таких как тип записи, расположение следующей записи и т. д. Давайте проанализируем, как строка данных хранится в InnoDB в формате строки Compact.

• Список длины поля переменной длины, это местоположение используется для хранения списка длин, фактически занятых непустыми полями в объявленных полях переменной длины. Например, есть 3 непустых поля, из которых длина первого Поле 3 - 3, а второе поле пустое, длина третьего поля 1, она будет представлена ​​на 01 03, а пустое поле будет помечено в следующей позиции. Длина поля переменной длины не может превышать 2 байта, поэтому максимальная длина varchar составляет 65535.

• Бит флага NULL занимает 1 байт.Если соответствующий столбец пуст, соответствующий бит устанавливается в 1, иначе он равен 0. Поскольку этот бит флага занимает один байт, количество столбцов не может превышать 255. Если поле пусто, оно не будет записано в следующих конкретных данных столбца. Этот подход также приводит к дополнительным операциям при работе с пустыми полями индекса.

• Информация заголовка записи, которая занимает 5 байт, включая позицию следующей записи, общую длину записи, тип записи, удалена ли она, информацию о соответствующем слоте и т. д.

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

Далее мы анализируем конкретную структуру хранения следующей строки записей в табличном пространстве на примере второго издания «MySQL Technology Insider».

CREATE TABLE mytest(
    t1 varchar(10),
    t2 varchar(10),
    t3 char(10),
    t4 varchar(10)
) engine = innodb;

insert into mytest VALUES('a','bb','bb','ccc');
insert into mytest VALUES('d',NULL,NULL,'fff');

Таблица определяет 3 поля переменной длины и 1 поле фиксированной длины, а затем вставляет две строки записей. Вторая строка записей содержит нулевые значения. Мы открываем файл табличного пространства mytest.ibd, преобразуем его в шестнадцатеричный формат и находим следующие содержание:

// 第一行记录
03 02 01 为变长字段长度列表，这里是倒序存放的，分别对应 ccc、bb、a 的长度。
00 表示没有为空的字段
00 00 10 00 2c 为记录头
00 00 00 2b 68 00 没有申明主键，维护内部 ID
00 00 00 00 06 05 事务ID
80 00 00 00 32 01 10 回滚指针
61 第一列 a 的值
62 62 第二列 bb 的值
62 62 20 20 20 20 20 20 20 20 第三列 bb 的值，固定长度 char(10) 以20进行填充
63 63 63 第四列 ccc 的值

// 第二行记录
03 01 为变长字段长度列表，这里是倒序存放的，分别对应 fff、a 的长度，第二列位空。
06 转换为二进制为 00000110 表示第二列和第三列为空
00 00 20 ff 98 为记录头
00 00 00 2b 68 01 没有申明主键，维护内部 ID
00 00 00 00 06 06 事务ID
80 00 00 00 32 01 10 回滚指针
64 第一列 d 的值
65 65 65 第四列 fff 的值

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

• Заголовок файла (File Header) фиксированный 38 байт (позиция страницы, позиция Next, контрольная сумма, LSN)
• Заголовок страницы данных (Page Header) имеет фиксированный размер 56 байт, включая количество слотов, начальный адрес повторно используемого пространства, адрес первой записи, количество записей, максимальный идентификатор транзакции и т. д.
• Виртуальная максимальная минимальная запись (Infimum + Supremum Record)
• Пользовательские записи (User Records) Содержит удаленные записи для формирования многократно используемого пространства в виде связанного списка
• Свободное пространство Нераспределенное пространство
• Информация о слоте каталога страниц (каталог страниц), представленная отдельно ниже
• File Trailer (Файл Трейлер) Фиксированные 8 байт, используемые для обеспечения целостности страницы

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

Сортировать

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

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from person where city='武汉' order by name limit 100  ;

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

1. Инициализировать буфер сортировки для хранения результирующего набора
2. Найдите индекс города, найдите первую запись, город которой равен Ухань, получите индекс первичного ключа ID3.Найдите соответствующую запись в индексе первичного ключа по идентификатору, выньте поля город, имя, возраст и поместите их в буфер сортировки
3. В указателе городов взять идентификатор первичного ключа записи, город которой равен Ухань.
4. Повторяйте вышеуказанные шаги, пока все записи с городом, равным Ухань, не будут помещены в буфер сортировки.
5. Быстро сортировать данные в буфере сортировки по имени
6. По результату сортировки взять первые 1000 предметов и вернуть

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

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

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

alter table person add index city_user(city, name);

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

1. В соответствии с общим индексом города, найдите первую запись, чей город равен Ухань, и получите идентификатор индекса первичного ключа.
2. По идентификатору перейдите в индекс первичного ключа, чтобы найти соответствующую запись, выньте поля города, имени, возраста и верните его как набор результатов.
3. Продолжайте повторять вышеуказанные шаги до тех пор, пока город не будет равен Ухань или количество баров не превысит 1000.

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

Подводя итог, когда у нас есть операция сортировки, лучше всего построить индекс на поле сортировки, Кроме того, поскольку мы запрашиваем первую сотню записей из миллионной, нам нужно отфильтровать первый миллион записей, даже если мы используем Индекс также очень медленный, поэтому его можно отличить по идентификатору, Во время обхода страниц каждый раз кэшируется идентификатор последней записи предыдущего результата запроса, а следующий запрос добавляет id> xxxx limit 0, 1000, чтобы избежать результатов сканирования ранее.

Модель хранения InnoDB

InnoDB асинхронно обрабатывает связанные операции с помощью серии фоновых потоков, как показано на следующем рисунке, и использует пул буферов для уменьшения разницы в скорости процессора и диска. При запросе он сначала найдет соответствующую страницу данных через индекс, а затем проверит, находится ли страница данных в пуле буферов. Если это так, она вернется напрямую. Если нет, она перейдет к кластеризованному индексу для прочитать соответствующую страницу данных через дисковый ввод-вывод и поместить ее в пул буферов. Страница данных будет содержать несколько строк данных. Буферный пул управляет страницами данных с помощью алгоритма LRU, то есть наиболее часто используемые страницы данных помещаются в начало списка, а редко используемые страницы данных — в конец очереди.Когда буферный пул заполнен, страницы данных в конце очереди будут удалены. Только что считанные с диска страницы данных помещаются не в начало очереди, а в среднее положение, которое можно изменить с помощью параметров. Также можно задать несколько экземпляров буферного пула, и страницы данных определяются в каком буферном пуле в соответствии с алгоритмом хеширования.

InnoDB будет использовать технологию WAL при обновлении данных, то есть Write Ahead Logging, Этот журнал перелогируется, чтобы гарантировать, что файл может быть восстановлен после того, как база данных не работает. Обычно этот файл записывается только последовательно, а файл повторного журнала читается только при запуске базы данных, чтобы увидеть, нужно ли ее восстанавливать. Этот файл записывает физическую операцию на странице данных.Например, если SQL изменяет значение столбца в строке на 10, соответствующий формат файла повторного журнала может быть: изменить смещение на пятой странице данных на 99 Записать значение из 10 до места. redolog не бесконечен, его размер можно настраивать и использовать циклически, например, размер конфигурации 4G, всего 4 файла, каждый файл 1G. Сначала записывайте последовательно из первого файла, а затем записывайте из первого файла после записи в четвертый файл, аналогично кольцу, используйте фоновый поток для синхронизации данных в редологе со страницей данных в кластеризованном индексе. При записи в redolog записи, не синхронизированные со страницей данных, не могут быть перезаписаны, если это произойдет, то он сначала остановится для синхронизации страницы данных, а затем продолжит запись в redolog. Кроме того, при выполнении операции обновления сначала будут обновлены страницы данных в буферном пуле, а затем записаны в редолог. страницы данных в пуле буферов несовместимы с диском в это время. Страница данных называется грязной страницей. Когда грязную страницу необходимо удалить из-за нехватки памяти или по другим причинам, журнал повторов, соответствующий грязной странице, должен быть очищен на реальную страницу данных на диске, иначе редолог не будет синхронизирован в следующем запросе.На диск, а запрос напрямую находит страницу данных через индекс, будут запрашиваться грязные данные.

При обновлении сначала прочитайте соответствующую страницу данных с диска или буферного пула, затем измените данные на странице данных и сгенерируйте повторный журнал в соответствующий буферный пул (буфер повторного журнала) для кэширования и запишите кэш при фиксации транзакции. файл  redolog  на физическом диске. Здесь, поскольку файл операционной системы записывается в InnoDB, а не записывается напрямую в файл с помощью O_DIRECT, для обеспечения производительности он сначала записывается в кэш операционной системы, а затем выполняется сброс. когда транзакция зафиксирована, InnoDB необходимо один раз вызвать систему fsync, чтобы убедиться, что данные размещены на диске. Чтобы повысить производительность, InnoDB может использовать параметр innodb_flush_log_at_trx_commit, чтобы управлять принудительной очисткой диска при фиксации транзакции.

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

• 0 означает, что запись файла redolog не будет вызываться при фиксации транзакции, а будет синхронизироваться один раз в секунду через фоновый поток.

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

• 2 означает, что файл будет записан при фиксации транзакции, но гарантированно будет записан только кеш операционной системы, а операция fsync не выполняется. Файл redolog будет записываться только последовательно, поэтому скорость работы с диском не будет слишком низкой,

При выполнении логики обновления записывается другой журнал: undolog. Этот файл хранится в общем табличном пространстве, то есть, даже если параметр innodb_file_per_table включен, все отмены таблицы хранятся в одном и том же файле. Этот файл в основном используется для отката транзакций и MVCC. undolog — это логический журнал, то есть в нем не фиксируется восстановление страниц физических данных до предыдущего состояния, а фиксируется противоположный sql исходному sql, например, вставка соответствует удалению, удаление соответствует вставке, а обновление соответствует к другому обновлению . Откат транзакции хорошо понимается, выполняя противоположную операцию для возврата к предыдущему состоянию, а MVCC относится к зеркальному чтению, когда транзакции необходимо запросить запись, а запись была изменена другими транзакциями, но транзакция не была изменена. commit, Текущая транзакция может быть рассчитана до предыдущего значения с помощью отмены журнала. Здесь нам нужно только знать, что, как и redolog, undolog также необходимо записать в файл до того, как транзакция будет зафиксирована при выполнении оператора обновления. Кроме того, запись undolog также будет иметь соответствующий redolog, потому что undolog также должен быть постоянным, а эффективность можно повысить с помощью WAL. Отсюда можно сделать вывод, что при совершении транзакции необходимо следить за тем, чтобы редолог был записан в файл, и этот редолог включает в себя модификацию страницы данных по первичному ключевому индексу и вставку ундолога в откат сегмент общего табличного пространства. Кроме того, очистка ундлога регулярно обрабатывается фоновым потоком, при очистке необходимо судить, не будут ли использованы все транзакции ундлога.

Любой, кто знаком с MySQL, знает, что он использует binlog для достижения высокой доступности, то есть для синхронизации данных с другими экземплярами MySQL в кластере через binlog. Отличие binlog от redolog в том, что он пишется на верхнем серверном слое движка хранилища, и записывает логические операции, то есть соответствующий sql, а redolog записывает физическую работу определенной страницы данных внизу, redolog пишется в цикле, а бинлог пишется дополнительно, и не перезапишет ранее записанные данные. И бинлог тоже нужно записать в файл до того, как транзакция будет зафиксирована. Запись страницы binlog должна быть гарантирована fsync. Чтобы улучшить tps, MySQL может использовать параметр   sync_binlog для управления необходимостью синхронного обновления.Эта стратегия повлияет, когда основная база данных не работает, данные резервной базы данных могут не быть полностью синхронизированными с данными основной базы данных. Из-за атомарного характера транзакции необходимо обеспечить успешную запись как redolog, так и binlog, когда транзакция зафиксирована, поэтому уровень выполнения MySQL принимает двухэтапную фиксацию, чтобы гарантировать, что и redolog, и binlog успешно записаны. перед фиксацией. Если одна из сторон выйдет из строя, будет выполнен откат.

Давайте посмотрим на процесс выполнения оператора  update:

update person set age = 30 where id = 1;

1. Выделите идентификатор транзакции, откройте транзакцию, получите блокировку и подождите, если блокировка не будет получена.
2. Исполнитель сначала находит страницу данных с id=1 через движок хранилища.Если есть буферный пул, то он вынимается напрямую.Если нет, то он обращается к индексу первичного ключа, чтобы вынуть соответствующую страницу данных и положить его в буферный пул.
3. Находим в странице данных строку с id=1, вынимаем, меняем возраст на 30 и записываем в память
4. Генерация повторного журнала отмены в память, состояние повторного журнала — подготовка
5. записать redolog undolog в файл и вызвать fsync
6. Уровень сервера генерирует binlog и записывает в файл для вызова fsync.
7. Зафиксируйте транзакцию, измените статус повторного журнала на зафиксированный, чтобы снять блокировку.

Замок

Базы данных используют блокировки для управления одновременным доступом к общим ресурсам для обеспечения целостности и согласованности данных. Минимальная степень детализации блокировок в InnoDB — строка.Как и ReadWriteLock в jdk, InnoDB предоставляет разделяемые блокировки и эксклюзивные блокировки для чтения и записи соответственно. Общие блокировки совместимы, другие взаимоисключающие. По объему блокировки ее можно разделить на глобальную блокировку, блокировку на уровне таблицы и блокировку строки. Глобальная блокировка заблокирует весь экземпляр базы данных.Команда очистки таблиц с блокировкой чтения переведет базу данных в состояние только для чтения, а другие операторы записи данных и изменения структуры таблицы будут заблокированы.Как правило, она используется для глобального резервного копирования на резервная база данных. Существует два типа блокировок на уровне таблицы: одна — блокировка таблицы, команда — блокировка таблицы с чтением/записью, которая аналогична блокировке чтения-записи, а другая — блокировка метаданных, также называемая блокировкой намерения, которая не блокируется. необходимо отображать и объявлять.Для структуры таблицы блокировки записи метаданных добавляются автоматически при добавлении индексов, а блокировки чтения метаданных добавляются при добавлении, удалении, изменении и запросе таблиц. Таким образом, когда блокировки метаданных между транзакциями двух операторов модификации являются блокировками чтения, они не являются взаимоисключающими, но когда структура таблицы изменяется, обновление должно быть заблокировано из-за взаимного исключения. Существует также блокировка на уровне строки, называемая блокировкой промежутка, которая блокирует промежутки между двумя записями, чтобы предотвратить вставку данных в этот промежуток другими транзакциями.Блокировка промежутка — это неявная блокировка, которая добавляется самим механизмом хранения.

неблокирующее чтение

Обычные операции выбора — это неблокирующие операции чтения. В случае конфликта транзакций журнал отмены будет использоваться для получения зеркального возврата перед новой операцией транзакции. При уровне изоляции «чтение-фиксация» последние зафиксированные данные до изменения новой транзакции При уровне изоляции Repeatable Read читается версия данных в начале транзакции. Когда несколько транзакций обрабатывают одну и ту же строку записей одновременно, запись будет иметь несколько журналов отмены одновременно, каждый журнал отмены является версией, этот режим называется многоверсионным управлением параллелизмом (MVCC), что может значительно повысить производительность базы данных. , подумайте об этом, если он контролируется на основе блокировок, когда запись изменяется, другой транзакции нужно будет ждать, будь то чтение или запись, MVCC все еще может читать, когда разрешает операции записи, что значительно улучшает tps для приложений это в основном операции запроса.

блокировка чтения

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

самовозрастающий замок

Когда первичный ключ настроен на автоинкремент, таблица будет поддерживать счетчик. Каждая операция вставки сначала получит текущее значение счетчика, а затем добавит 1 в качестве нового первичного ключа. Очевидно, что этот счетчик является общей переменной и требует эксклюзивной блокировки. И эту блокировку не нужно снимать после фиксации транзакции. Она будет снята после вставки оператора sql. Новая версия innoDB использует мьютекс для повышения скорости вставки.

проблема с замком

• грязное чтение
• неповторяемое чтение
• потерянное обновление
• Взаимоблокировки и горячие точки

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

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

Потерянное обновление означает, что две транзакции обновляют запись одновременно, что приводит к сбою обновления одной из транзакций.Теоретически потеря обновления не произойдет ни на одном уровне изоляции, потому что при обновлении будет добавлена ​​эксклюзивная блокировка, но это часто случается Например, в приложении-счетчике транзакция A запрашивает значение счетчика v=5, добавляет в память 1 и записывает его в базу данных.Перед записью другая транзакция считывает значение счетчика v=5, а потом добавляет в базу 1, так что Изначально должно было быть 7, а сейчас 6. Это потому что мы сначала читаем и пишем, а чтение и запись это две операции для БД, а не атомарная операция Плюс эксклюзивная блокировка выберите для обновления, чтобы предотвратить потерю обновлений. Конечно, вы можете напрямую изменить sql на v = v + 1 здесь.

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

процесс блокировки

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

select * from trade_order where order_no = '201912102322' for update; 

• order_no — это индекс первичного ключа, в этом случае эксклюзивная блокировка будет добавлена ​​к записи order_no = '201912102322' в индексе первичного ключа.
• order_no — это обычный индекс, и это единственный индекс, который добавит эксклюзивную блокировку к соответствующему набору записей в обычном индексе и добавит эксклюзивную блокировку к соответствующим записям в индексе первичного ключа.
• order_no — это общий индекс, а не уникальный индекс. Он заблокирует одну или несколько записей с order_no = '201912102322' в общем индексе и заблокирует записи в индексе первичного ключа, соответствующие этим записям. В дополнение к блокировкам строк сюда добавляются блокировки пробелов, чтобы другие вещи не могли вставлять записи с  order_no = '201912102322', но если это уникальный индекс, блокировки пробелов не требуются, блокировки строк подходят.

• Индекса для order_no нет, и innoDB выполнит полное сканирование таблицы по индексу первичного ключа. Блокировки таблицы нет, но ко всем записям добавляются эксклюзивные блокировки на уровне строк. На самом деле, innoDB была оптимизирована внутри. сканируется ряд записей Если обнаруживается несоответствие, блокировка снимается, что, конечно, нарушает принцип 2PL и снимается при фиксации транзакции. В дополнение к блокировке записей, разрыв между каждыми двумя записями также будет заблокирован, поэтому все блокировки пробелов и блокировки строк с order_no = '201912102322' в конечном итоге будут сохранены.

order_no = '201912102322' Если эта запись не существует, если order_no является индексом первичного ключа, будет добавлена ​​блокировка пробела, и этот пробел будет первой записью, чей order_no меньше 201912102322, к первой записи, чей order_no больше 201912102322 в индексе первичного ключа. Представьте, если нет блокировки пробела, если другие вещи вставляют запись с order_no = '201912102322', так как выбор для обновления является текущим чтением, даже если вышеуказанная вещь не отправлена, если вещь повторно запрашивается один раз, фантом произойдет чтение.

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

дела

Транзакции механизма хранения InnoDB должны быть полностью совместимы с ACID. Давайте посмотрим, что делает InnoDB.

• Атомарность: это означает, что все операции в транзакции либо выполняются успешно, либо все терпят неудачу, а запись повторного и бинарного журналов в базу данных использует двухэтапную фиксацию для обеспечения атомарности транзакции. Кроме того, поскольку InnoDB хранится в страницах, размер каждой страницы составляет 16 КБ, а операционная система обычно считывает 4 КБ как страницу, поэтому может возникнуть ситуация, когда записывается только часть страницы данных InnoDB. Чтобы этого не произошло, InnoDB использует механизм двойной записи.Помимо записи страниц данных на диск, она также записывает в разделяемое пространство. Redolog хранится блоками, каждый блок 512 байт, что точно соответствует размеру сектора, поэтому можно гарантировать атомарность и не требуется двойная запись.
• Непротиворечивость: убедитесь, что данные на диске и в кэше согласованы, а данные binlog согласованы с данными в основной базе данных.
• Изоляция: по умолчанию используется повторяющееся чтение, реализованное с помощью undolog.
• Постоянство: после того, как транзакция зафиксирована, результат является постоянным, redolog необходимо сбросить перед транзакцией, а диск использует RAID и т. д.

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