Два основных артефакта базы данных [индекс и блокировка]

задняя часть база данных MySQL HTTPS

предисловие

Только лысина может стать сильнее

Индексы и блокировкиМожно сказать, что это очень важная точка знаний в базе данных, и ее часто задают на собеседованиях.

эта статьяСтремитесь объяснить каждый пункт знаний просто, я надеюсь, что каждый сможет что-то получить после прочтения

Отказ от ответственности: если не указаны конкретная база данных и механизм хранения,Значение по умолчанию относится к механизму хранения InnoDB в MySQL.

1. Индекс

Раньше у меня было следующее понимание индексов:

  • Индексы могут ускорить поиск в базе данных
  • поверхностьчастопровестиINSERT/UPDATE/DELETEНе создавайте индекс для операции, другими словами:индекс уменьшитсяСкорость выполнения задач обслуживания, таких как вставка, удаление, модификация и т. д.
  • требуется индексЗанимает физическое пространство и пространство данных.
  • Поймите самый левый принцип сопоставления индекса
  • Знать классификацию индексов: кластеризованные и некластеризованные индексы.
  • Mysql поддерживает как хэш-индекс, так и индекс дерева B+.

Похоже, вы все знаете, но когда интервью просит вас сказать это, это может быть ГГ:

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

1.1 Расскажите об основах индексации

Прежде всего, основная структура хранения Mysql:Страница(Записи хранятся на странице):

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

Итак, если мы напишемselect * from user where username = 'Java3y'Таким образом, оператор sql без какой-либо оптимизации будет делать это по умолчанию:

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

Очевидно, что в случае большого количества данных такой поиск будеточень медленно!

1.2 Индекс повышает скорость поиска

Что делает индекс для ускорения наших запросов?

На самом деле этоПревратите неупорядоченные данные в упорядоченные (относительные):

Чтобы найти запись с id 8 коротких шагов:

Очевидно, что:нет индексанам нужноОбход двусвязного спискаЧтобы найти соответствующую страницу, теперь вы можете быстро найти соответствующую страницу через **"Содержание"**!

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

Использованная литература:

1.3 Индекс снижает скорость добавления, удаления и модификации

B+ деревоСбалансированное деревотипа.

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

Если обычное деревокрайнийв случаевыродиться в связанный список(добро дерева ушло)

Дерево B+ — это тип сбалансированного дерева, оно не вырождается в связанный список, а высота дерева относительно мала (в основном соответствуетКоренастая (сбалансированная) структура) [Таким образом, временная сложность нашего поиска составляет O(logn)]! Из рисунка в предыдущем разделе также видно, что построение индекса на самом деле является построением дерева B+.

  • Дерево B+ — это сбалансированное дерево, если мы добавим, удалим или изменим это дерево, оно обязательноразрушить его первоначальную структуру.
  • Чтобы сохранить сбалансированное дерево, необходимо выполнить дополнительную работу.. Из-за этой дополнительной работынад головой, из-за чего индекс замедляет скорость добавления, удаления и изменения

Подробнее об удалении и модификации дерева B+ см.:

1.4 Хэш-индекс

Помимо дерева B+, есть общее — хэш-индекс.

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

  • По сутиПреобразование значения ключа в новое значение хеш-функции,согласно этомуХэш-значение для поиска.

Кажется, что хеш-индекс очень мощный, но на самом деле хэш-индекс имеет несколько ограничений (согласно его основному принципу):

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

Использованная литература:

1.5 Поддерживает ли InnoDB хеш-индексы?

Мейнстрим по-прежнему используетСуществует множество индексов деревьев B+., для хэш-индексов,InnoDB — это адаптивный хэш-индекс.Да (создание хеш-индекса автоматически оптимизируется механизмом хранения InnoDB, и мы не можем вмешиваться)!

Использованная литература:

1.6 Кластеризованные и некластеризованные индексы

Краткое содержание:

  • Кластерный индекспервичный ключиндекс создан
  • Некластеризованный индексне первичный ключиндекс создан

разница:

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

Некластеризованный индекс также называется вторичным индексом., вам не нужно путать столько существительных, просто сделайте их эквивалентными~

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

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

В создании многоколоночного индекса также участвует специальный индекс -->индекс покрытия

  • Как мы знаем ранее, если это не кластеризованный индекс, конечный узел хранит первичный ключ + значение столбца.
  • В конце необходимо «вернуть таблицу», то есть передать первичный ключСноваНайдите его один раз. Это будет медленнее
  • Индекс покрытия долженЗапрошенные столбцы и индексы соответствуют, не делайте операцию "обратно к таблице"!

Например:

  • Теперь я создал индекс(username,age), при запросе данных:select username , age from user where username = 'Java3y' and age = 20.
  • Очевидно, что наш запрос выше проиндексирован, иЗапрашиваемые столбцы существуют в листовых узлах.! Так что не нужно возвращать форму~
  • Итак, если вы можете использовать покрывающий индекс, попробуйте использовать его~

1.7 Принцип сопоставления с крайним левым индексом

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

  • Индекс может быть таким же простым, как столбец(a), который также может быть таким сложным, как несколько столбцов(a, b, c, d),Сейчассовместный индекс.
  • Если это совместный индекс, ключ также состоит из нескольких столбцов, при этом индекс можно использовать только для того, чтобы узнать, является ли ключсуществуют (равные), встречая запрос диапазона(>、<、between、likeлевое совпадение) и так далеебольше нет совпадений, а затем вырождается в линейный поиск.
  • следовательно,Порядок столбцов определяет количество столбцов, которые могут попасть в индекс..

пример:

  • если проиндексирован(a, b, c, d),Условия запросаa = 1 and b = 2 and c > 3 and d = 4, он последовательно попадет в a, b и c в каждом узле и не попадет в d. (Все просто: попадание в индекс может быть толькоравныйслучай, не может быть совпадением диапазона)

1.8=, в порядке автоматической оптимизации

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

пример:

  • если проиндексирован(a, b, c, d),Условия запросаc > 3 and b = 2 and a = 1 and d < 4иa = 1 and c > 3 and b = 2 and d < 4Порядок в порядке, MySQL автоматически оптимизируетa = 1 and b = 2 and c > 3 and d < 4, последовательно нажмите a, b, c.

1.9 Резюме указателя

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

  • 1,Принцип сопоставления крайнего левого префикса. Это очень важный, очень важный, очень важный (важно сказать три раза) принцип, MySQL всегда будет соответствовать правому краю, пока не столкнется с запросом диапазона.（>,<,BETWEEN,LIKE）просто перестань соответствовать.
  • 3, попробуйте выбратьСтолбцы с высокой дискриминацией используются в качестве индексов., формула дискриминацииCOUNT(DISTINCT col) / COUNT(*). Указывает соотношение не дублирующихся полей, чем больше соотношение, тем меньше записей мы сканируем.
  • 4,Столбцы индекса не могут участвовать в вычислениях, старайтесь держать столбцы «чистыми».. Например,FROM_UNIXTIME(create_time) = '2016-06-06'Вы не можете использовать индекс по той простой причине, чтоВсе значения полей в таблице данных хранятся в дереве B+, но при извлечении нужно применить функцию ко всем элементам для сравнения, что явно слишком дорого. Таким образом, заявление должно быть написано так:create_time = UNIX_TIMESTAMP('2016-06-06').
  • 5. Как можно большерасширенный индекс, не создавайте новый индекс. Например, в таблице уже есть индекс a, и теперь вам нужно добавить индекс (a, b), тогда вам нужно только изменить исходный индекс.
  • 6. Эффект извлечения одного составного индекса с несколькими столбцами и нескольких индексов с одним столбцом различен, потому что при выполнении SQLMySQL может использовать только один индекс, выбирает наиболее строгий индекс из нескольких одностолбцовых индексов.

Использованная литература:

2. Блокировка

заблокировать в mysqlвыглядитсложно, потому что естьмного вещей и существительных: монопольная блокировка, совместная блокировка, блокировка таблицы, блокировка страницы, блокировка промежутка, намеренная монопольная блокировка, намеренная совместная блокировка, блокировка строки, блокировка чтения, блокировка записи, оптимистическая блокировка, пессимистическая блокировка, взаимоблокировка. Некоторые блоги этих терминов прямо пишут английскую аббревиатуру блокировки ---> X lock, S lock, IS lock, IX lock, MMVC...

Знание блокировок связано с уровнем изоляции механизмов хранения, индексов и транзакций. …

Это приносит много неприятностей новичкам в блокировках баз данных~~~ Итак, я кратко разберу точки знаний о блокировках баз данных, надеюсь, вам будет полезно их прочитать.

2.1 Зачем вам нужно изучать знание блокировки базы данных

Когда многие люди развиваются, они должныредко замечаюЭти проблемы с блокировкой редко блокируют программы (за исключениемв наличииОни требуют чрезвычайно высокой количественной точности)

Как правило, я слышал об оптимистичных и пессимистичных замках, но после понимания основного смысла они исчезли~~~

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

  • заUPDATE、DELETE、INSERTутверждение,InnoDBвстречаавтоматическийДобавить эксклюзивную блокировку к задействованному набору данных (X)
  • MyISAMпри выполнении запросаSELECTраньше, будетавтоматическийДобавить ко всем задействованным таблицамблокировка чтения, после выполнения операции обновления (UPDATE、DELETE、INSERTи т.д.) прежде, будетавтоматическийдобавить к задействованным таблицамблокировка записи, этот процесс неВмешательство пользователя не требуется

Требуется только в некоторых конкретных сценарияхруководствоБлокировка, изучение знаний блокировки базы данных заключается в следующем:

  • позволяет нам быть полезными в определенных сценариях
  • лучшеУправляйте своими программами
  • Говоря с другими о технологии баз данных, вы можете наверстать упущенное в нескольких словах.
  • Создайте свою собственную систему базы знаний! Будьте честны во время интервью

2.2 Краткое введение в блокировку таблицы

Во-первых, по степени детализации замков мы можем разделить их на две категории:

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

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

  • Поддерживаются блокировки строк и таблиц InnoDB.!
  • MyISAM поддерживает только блокировки таблиц!

InnoDB только проходитсостояние индексаполучить данныеблокировка на уровне строки, иначе InnoDB будет использоватьблокировка стола

  • Это,Блокировки строк InnoDB основаны на индексах.!

Под замком стола есть два режима:

  • Блокировка чтения таблицы
  • Блокировка записи таблицы
  • Как хорошо видно из следующего рисунка, в среде блокировки чтения таблицы и блокировки записи таблицы:Чтение и чтение без блокировки, чтение и запись с блокировкой, запись и запись с блокировкой!
    • Чтение и чтение без блокировки: текущий пользователь читает данные, другие пользователи также читают данные и не будут блокироваться.
    • Блокировка чтения и записи: текущий пользователь читает данные, другие пользователиДанные, прочитанные текущим пользователем, не могут быть изменены, будет заблокирован!
    • Запись и блокировка записи: текущий пользователь изменяет данные, другие пользователиДанные, изменяемые текущим пользователем, не могут быть изменены, будет заблокирован!

Как вы видели выше:Блокировки чтения и записи являются взаимоисключающими, а операции чтения и записи выполняются последовательно..

  • Если процесс хочет получить блокировку чтения,в то же времяДругой процесс хочет получить блокировку записи. Внутри mysql,Блокировки записи имеют приоритет над блокировками чтения!
  • Проблема блокировки записи и приоритета блокировки чтения настраивается параметрами:max_write_lock_countиlow-priority-updates

Стоит отметить, что:

Модификатор LOCAL позволяет выполнять неконфликтующие операторы INSERT (одновременные вставки) другими сеансами, пока блокировка удерживается (см. Раздел 8.11.3, «Параллельные вставки».) Однако READ LOCAL нельзя использовать, если вы собираетесь манипулировать базой данных. использование процессов, внешних по отношению к серверу, пока вы держите блокировку.For InnoDB tables, READ LOCAL is the same as READ

  • MyISAM можетПоддержка операций запроса и вставкипараллелизмпровести. через системные переменныеconcurrent_insertуказать, в каком режимеMyISAMПо умолчанию: если в таблице MyISAM нет дыр (то есть в середине таблицы нет удаленных строк), MyISAM позволяет одному процессу читать таблицу, пока другой процесс читает из таблицы.нижний колонтитулВставить запись.
  • ноМеханизм хранения InnoDB не поддерживается!

Использованная литература:

2.2 Детали блокировки строк

Вышеприведенное кратко объяснило соответствующие знания о блокировках таблиц.Мы обычно используем механизм хранения InnoDB при использовании Mysql. Между InnoDB и MyISAM есть два существенных различия:

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

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

мы должныУделите больше внимания содержимому блокировок строк, потому что основная функция InnoDB — поддержка блокировки строк!

InnoDB реализует следующеедваТип блокировки строки.

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

Прочитав вышесказанное, вы обнаружили, что то, что я сказал в начале: блокировка X, блокировка S, блокировка чтения, блокировка записи, общая блокировка и эксклюзивная блокировка, на самом делевсего два замка, но ониЕсть несколько имен~~~

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

Кроме того,Чтобы разрешить сосуществование блокировок строк и таблиц, реализуйте механизм блокировок с множественной грануляцией., InnoDB также имеет две внутренние блокировки намерения (Intention Locks), обе из которыхблокировка стола:

  • Преднамеренная общая блокировка (IS): транзакция намеревается добавить общую блокировку строки к строке данных.Транзакция должна сначала получить блокировку IS таблицы, прежде чем добавлять общую блокировку к строке данных.
  • Преднамеренная монопольная блокировка (IX): транзакция намеревается добавить монопольную блокировку строки к строке данных.Транзакция должна получить блокировку IX таблицы, прежде чем добавлять монопольную блокировку к строке данных.
  • Блокировка намерения также неявно выполняется за нас базой данных.Программистам не о чем беспокоиться!

Использованная литература:

2.2.1 MVCC и уровень изоляции транзакций

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

MVCC (Multi-Version Concurrency Control) управление многоверсионным параллелизмом можно просто рассматривать как:MVCC — это вариант блокировки на уровне строк (обновленная версия)..

  • Уровень изоляции транзакциичерез запорный механизм,ТолькоСкрыть сведения о замке

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

  • Реализовано MVCCЧитать и писать без блокировкиКак его название:несколько версийПараллельный контроль ---> генерировать запрос данных через определенный механизмМоментальный снимок согласованных данных на момент времени (моментальный снимок), и использовать этот снимок для обеспечения определенного уровня (уровень оператора или уровень транзакции)изпоследовательное чтение. С точки зрения пользователя кажется, чтоБазы данных могут предоставлять несколько версий одних и тех же данных..

Снимки имеютдва уровня:

  • уровень заявления
    • Таргетинг наRead committedуровень изоляции
  • уровень транзакции
    • Таргетинг наRepeatable readуровень изоляции

Мы уже знали в начале, что уровень изоляции транзакций4 вида:

  • Read uncommitted
    • Произойдут грязные чтения, неповторяемые чтения и фантомные чтения.
  • Read committed
    • Будут неповторяющиеся чтения, фантомные чтения
  • Repeatable read
    • Будут фантомные чтения (но повторяющееся чтение, реализованное в Mysql с блокировкой промежутка, не будет иметь фантомных чтений!)
  • Serializable
    • Серийный, избегайте описанной выше ситуации!

Read uncommittedЯвление, которое появится ---> Грязное чтение:Транзакция считывает незафиксированные данные из другой транзакции

  • Пример: A переводит деньги B,A выполняет заявление о переводе, но A еще не отправил транзакцию, B считывает данные и обнаруживает, что на его счету больше денег.! Б сказал А, что я получил деньги. А откатывает транзакцию [rollback], а когда Б снова проверяет деньги на счету, то обнаруживает, что денег мало.
  • Природа грязных чтений заключается в том, чтоПосле завершения операции (модификации) данных блокировка сразу снимается, в результате чего прочитанные данные становятся бесполезными илиневерные данные.

Read committedИзбегайте грязного чтенияНа самом деле подход очень простой:

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

ноRead committedФеномен ---> неповторяемое чтение:Транзакция считывает данные, которые были зафиксированы другой транзакцией, что означает, что транзакция может видеть изменения, сделанные другими транзакциями.

  • Примечание:A запрашивает базу данных для получения данных, а B изменяет данные в базе данных, что приводит к различным результатам нескольких запросов A к базе данных. 】

Также сказано выше,Read committedдауровень заявленияСнимок!Каждый раз, когда вы читаете последнюю версию!

Repeatable readИзбегать неповторяющихся чтений дауровень транзакцииСнимок! Каждое чтение — это версия текущей транзакции, даже если она будет изменена, будут прочитаны только данные текущей версии транзакции.

Эээ... Если все еще непонятно, давайте взглянем на MVCC InnoDB (отрывок из "High Performance MySQL")

Что касается виртуального чтения (фантомного чтения):Это означает, что данные, вставленные другими транзакциями, считываются внутри транзакции, что приводит к непоследовательному чтению до и после.

  • Примечание:Подобно неповторяемому чтению, но виртуальное чтение (фантомное чтение) будет считывать данные, вставленные другими транзакциями, что приводит к непоследовательному чтению до и после
  • MySQLRepeatable readУровень изоляции плюс блокировка разрыва GAPС фантомным чтением разобрались.

Использованная литература:

Дальнейшее чтение:

2.3 Оптимистическая блокировка и пессимистическая блокировка

Будь тоRead committedвсе ещеRepeatable readУровень изоляции, все решитьчитать и писать конфликтыПроблема.

просто вRepeatable readНа уровне изоляции рассмотрим проблему:

В этот момент работа пользователя Li Si теряется:

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

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

решение:

  • С уровнем изоляции Serializable транзакции выполняются последовательно!
  • оптимистическая блокировка
  • пессимистический замок
  1. Идея оптимистической блокировки, конкретная реализация в том, что в таблице есть поле версии, и это поле получается при первом чтении. После обработки бизнес-логики и начала обновления нужно еще раз проверить, совпадает ли значение поля с первым. Если же обновить, иначе отклонить. Причина, по которой он называется оптимистичным, заключается в том, что этот режим не заблокирован от базы данных, и он будет ждать, пока она не будет обновлена, чтобы определить, можно ли ее обновить.
  2. Пессимистичные блокировки — это блокировки на уровне базы данных, которые блокируют ожидание блокировок.

2.3.1 Пессимистический замок

Итак, следуйте примеру выше. Если мы используем пессимистическую блокировку, это на самом деле очень просто (вручную добавить блокировки строк):

  • select * from xxxx for update

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

  • То есть, если Чжан Сан используетselect ... for update, Ли Си не может изменить запись~

2.3.2 Оптимистическая блокировка

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

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

Чжан Санselect * from table---> Будет запрошена запись, и будет поле версии

Ли Сиselect * from table---> Будет запрошена запись, и будет поле версии

Ли Си изменил эту запись:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}, сравнить запрошенную ранее версию с версией текущих данных,В то же время поле версии будет обновлено.

На данный момент записи базы данных выглядят следующим образом:

Чжан Сан также изменил эту запись:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}, но не удалось! так какВерсия в текущей базе данных несовместима с запрошенной версией!

Использованная литература:

2.4 Гэп-замок GAP

когда мыПолучить данные с условиями диапазонаВместо того, чтобы извлекать данные с условиями равенства и запрашивать общую или эксклюзивную блокировку, InnoDB выдастЗаблокируйте записи указателя существующих записей данных, которые соответствуют условиям диапазона; Для записей, значение ключа которых находится в пределах диапазона условия, но не существует, он называется «GAP». InnoDB также заблокирует этот «пробел», и этот механизм блокировки является так называемой блокировкой промежутка.

Стоит отметить: замки Gap будут работать только вRepeatable readИспользовать на уровне изоляции~

Пример: Если в таблице emp всего 101 запись, значения empid равны 1, 2, ..., 100, 101 соответственно.


Select * from  emp where empid > 100 for update;

Выше приведен запрос диапазона, InnoDBне толькоЗаблокирует запись с квалифицированным значением empid 101, а также заблокирует записьблокировки "пробел" с empid больше 101 (эти записи не существуют).

InnoDB использует гэп-блокировки для двух целей:

  • Чтобы предотвратить фантомное чтение(Как указано выше,Repeatable readФантомного чтения можно избежать с помощью блокировки GAP на уровне изоляции)
  • Удовлетворение потребностей в восстановлении и репликации
    • Механизм восстановления MySQL требует:До того, как транзакция будет зафиксирована, другие параллельные транзакции не могут вставлять какие-либо записи, соответствующие их условиям блокировки, то есть фантомное чтение не разрешено.

2.5 Тупик

Проблема параллелизма неизбежно заходит в тупик, и в MySQL также будет проблема тупика.

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

  • 1) сфиксированный порядокДоступ к таблицам и строкам. Например, в случае пакетного обновления двух заданий простой метод состоит в том, чтобы сначала отсортировать список идентификаторов, а затем выполнить его, что позволяет избежать ситуации перекрестного ожидания блокировок; последовательный также может избежать взаимоблокировок.
  • 2)Большой бизнес распадается. Крупные транзакции более подвержены взаимоблокировкам.Если бизнес позволяет, разделите большие транзакции на мелкие.
  • 3) В одной и той же транзакции сделать как можно большеодноразовый замокВсе ресурсы, необходимые для снижения вероятности взаимоблокировки.
  • 4)более низкий уровень изоляции. Если бизнес позволяет, также рекомендуется понизить уровень изоляции.Например, изменение уровня изоляции с RR на RC может избежать многих взаимоблокировок, вызванных гэп-блокировками.
  • 5)Добавьте разумный индекс в таблицу. Видно, что если индекс не использовать, то к каждой строке таблицы будет добавлена ​​блокировка, и вероятность взаимоблокировки сильно возрастет.

Использованная литература:

2.6 Сводная информация о блокировке

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

На самом деле, наши программисты редко заботятся о блокировках таблиц:

  • В механизме хранения MyISAM он автоматически добавляется при выполнении инструкции SQL.
  • В механизме хранения InnoDB блокировки таблиц добавляются автоматически, если индексы не используются.

Теперь большая часть нашего использования MySQL заключается в использовании InnoDB, InnoDB поддерживает блокировки строк:

  • Общая блокировка -- блокировка чтения -- блокировка S
  • Эксклюзивная блокировка -- блокировка записи -- блокировка X

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

  • Общий замок(и):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE.
  • Эксклюзивный замок (X):SELECT * FROM table_name WHERE ... FOR UPDATE.

InnoDBблокировка на основе строкиОн также реализует многоверсионный контроль параллелизма MVCC, MVCC на уровне изоляции.Read committedиRepeatable readработать под. MVCC может достичьЧитать и писать без блокировки!

Реализован InnoDBRepeatable readУровень изоляции и блокировка GAP позволили избежать фантомного чтения!

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

Использованная литература:

3. Резюме

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

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

Я просто записывал проблемы, с которыми столкнулся в процессе обучения, и разбирал их, надеясь всем помочь. Если в статье что-то не так, я надеюсь, вы сможете исправить это в комментариях и учиться и общаться вместе~

Использованная литература:

  • Третье издание высокопроизводительного MySQL

Если в статье есть какие-либо ошибки, пожалуйста, поправьте меня, и мы сможем общаться друг с другом. Учащиеся, привыкшие читать технические статьи в WeChat и желающие получить больше ресурсов по Java, могутОбратите внимание на публичный аккаунт WeChat: Java3y.

Оглавление Навигация по статьям:

Категории