Говоря о MySQL (индекс, блокировка)

1. Движок MySQL

mysql> show engines;

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

Для получения других сведений о двигателе перейдите по ссылке:Dev.MySQL.com/doc/Furious/…

InnoDB

InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.

перевести:

innodb: механизм хранения по умолчанию в mysql 5.7. InnoDB — это безопасный для транзакций (acid-совместимый) механизм хранения для MySQL с фиксацией, откатом и аварийным восстановлением для защиты пользовательских данных. Блокировка на уровне строки Innodb (не эскалированная до более крупномасштабных блокировок) и типы оракула последовательно используют чтение без блокировки для улучшения многопользовательского параллелизма и производительности. InnoDB хранит пользовательские данные в кластеризованном индексе, чтобы уменьшить ввод-вывод для общих запросов на основе первичных ключей. Для обеспечения целостности данных innodb также поддерживает внешние ключи. Для получения дополнительной информации об innodb см. Главу 14, Механизм хранения inodb.

Структура данных хранилища InnoDB — B+Tree

Зачем использовать B+Tree?

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

Характеристики хэш-индекса

Hash indexes have somewhat different characteristics from those just discussed:
Они используются только для сравнений на равенство, которые используют операторы = или (но очень быстро). Они не используются для операторов сравнения, таких как

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

The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)

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

MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.

MySQL не может приблизительно определить, сколько строк находится между двумя значениями (что используется оптимизатором диапазона, чтобы решить, какой индекс использовать). Если таблица MyISAM или InnoDB изменена на хэш-индексированную таблицу в памяти, это может повлиять на некоторые запросы.

Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

Только совпадение с равным значением может применить хеш-структуру для запроса строки. (В индексах B-дерева для запросов можно использовать крайний левый префикс.)

Преимущество хеш-таблицы в том, что запрос выполняется быстро, а недостаток в том, что запрос по диапазону неэффективен (из-за беспорядка). Подходит для запросов на равенство.

Характеристики индекса B-дерева

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

Индекс B-дерева можно применять к таким операторам, как =, >, >=,

Древовидная структура, преимущества упорядочены, а дерево с несколькими ответвлениями может уменьшить количество дисковых операций ввода-вывода.

B-дерево и B+дерево.

Во-первых, B-деревья читают B-деревья, а не B-минус-деревья. Из приведенного выше рисунка видно, что B-дерево может попасть в цель, не проходя листовые узлы, и каждый родительский узел B+-дерева появится в дочерних узлах (два узла 10 и 15 на рисунке также появятся в дочерних узлах). листовые узлы. ). Кроме того, места хранения спутниковых данных B-Tree и B+Tree отличаются.

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

Как промежуточные узлы, так и конечные узлы в B-дереве несут спутниковые данные. В B+Tree только конечные узлы имеют спутниковые данные, а промежуточные узлы имеют только индексы. Как показано ниже: Структура B-дерева аналогична структуре B+Tree, за исключением того, что неконечные узлы также хранят данные, в то время как B+Tree хранит данные только в конечных узлах, хотя B-дерево может возвращать данные при переходе ко второму узлу. слой. Однако, поскольку неконечные узлы также хранят данные, каждая страница данных хранит меньше индексов, что приводит к большой высоте дерева. Если данные, которые необходимо пройти, находятся в конечных узлах, это занимает очень много времени, поэтому не так стабилен, как B+Tree. Размер страницы данных в движках MySQL и InnoDB составляет 16 КБ, поэтому теоретически чем больше полезной информации хранит страница данных, тем ниже высота дерева, меньше операций ввода-вывода и выше эффективность поиска.

Индексная модель InnoDB

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `k` int not null,
  `name` varchar(16),
   index(k)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Вставьте (1,10,"Чжан Сан"),(2,20,"Ли Си"),(3,30,"Ван Ву") в таблицу. Индекс выглядит следующим образом

Листовые узлы индекса первичного ключа являются значением первичного ключа для листового узла индекса непервичного ключа.
В InnoDB индекс первичного ключа называется кластеризованным индексом или кластеризованным индексом, а индекс непервичного ключа называется вторичным индексом или вторичным индексом. В InnoDB таблицы хранятся в виде индексов в соответствии с порядком первичного ключа.Таблицы в этом методе хранения становятся таблицами, организованными по индексу. Каждый индекс соответствует дереву B+ в InnoDB, и данные расположены по порядку. Правила генерации кластерного индекса:

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

Определите первичный ключ с первичным ключом в качестве кластеризованного индекса.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

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

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

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

Запрос В чем разница на основе индекса первичного ключа и общего индекса?

Если sql select * from r, где id = 1, то есть запрос по первичному ключу, вам нужно только выполнить поиск в дереве B+ первичного ключа.
Если sql выбирает * из r, где k = 10, то есть запрос через обычный индекс, вам нужно сначала выполнить поиск в дереве B+ с обычным индексом k, получить первичный ключ id=1, а затем использовать id=1 для поиска B+ дерево индекса первичного ключа. Этот процесс называетсяформа возврата.

При анализе оператора sql: выберите * из r, где k от 8 до 22;

Найдите запись с k=10 в индексном дереве k и получите id=1;
Найдите соответствующие данные записи строки с id=1 в дереве индекса id (вернитесь к таблице);
Найдите запись с k=20 в индексном дереве k и получите id=2;
Найдите соответствующие данные записи строки с id=2 в дереве индекса id (вернитесь к таблице);
Берем следующее значение k=30 в дереве индексов k, если оно не удовлетворяет, цикл завершается.

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

индекс покрытия

Если оператор sql: выберите id из r, где k между 8 и 22, поскольку в это время нужно запрашивать только значение id, а значение id уже находится в индексном дереве k, нет необходимости возвращать таблицу запрос, а индекс k уже покрывает наши требования к запросам, называются покрывающими индексами.
Поскольку покрывающие индексы могут сократить количество операций поиска и значительно повысить производительность запросов, использование покрывающих индексов является распространенным методом оптимизации.
Сценарий: Предположим, что есть таблица граждан:

CREATE TABLE `citizen` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

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

крайний левый префикс

select * from citizen where name = "张三" ;

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

select * from citizen where name like '张%';

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

select * from tuser where name like '张%' and age=10 and ismale=1;

До MySQL 5.6 нужно было начинать возвращать таблицы одну за другой, чтобы проверить, удовлетворяет ли возраст 10 по словам человека с фамилией Чжан, но 5.6 ввелаОптимизация проталкивания индекса(проталкивание условия индекса), вы можете сначала оценить поля, содержащиеся в индексе, во время обхода, отфильтровать неудовлетворительные записи и уменьшить количество возвратов в таблицу. В следующих двух предложениях можно использовать индекс.

1. SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
2. SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

В первом предложении будут отфильтрованы только строки с 'Patrick' В следующих двух предложениях индексы не используются:

3. SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
4. SELECT * FROM tbl_name WHERE key_col LIKE other_col;

Поскольку третье предложение начинается с подстановочного знака, оно не соответствует принципу крайнего левого префикса, поэтому индекс не может быть применен. В четвертом предложении, поскольку параметр LIKE не является строковой константой, индекс не используется.
Если используется LIKE '%string%' и длина строки превышает 3, для повышения эффективности запроса будет использоваться алгоритм сопоставления строк BM.
Кроме того, если у столбца есть индекс, если значение пусто, используйтеwhere col_name IS NULLТакже возможна индексация.

Если бизнес считает поле уникальным, можно ли его считать первичным ключом?

Например, можно гарантировать уникальность номера удостоверения личности, поэтому следует ли использовать номер удостоверения личности в качестве первичного ключа для построения таблицы? Здесь это не рекомендуется.По представленной выше структуре кластеризованного индекса и вторичного индекса видно, что чем длиннее индекс первичного ключа, тем больше места требуется для создания вспомогательного индекса.Кроме того, для кластеризованный индекс, если индекс слишком длинный, первичный ключ. Высота дерева индекса становится выше, поскольку страница данных по умолчанию имеет размер 16 КБ, и чем длиннее индекс первичного ключа, тем меньше индекса может разместить страница данных. Идентификационный номер составляет 18 бит, и для его хранения в строке требуется 18 байтов, а если в качестве первичного ключа используется автоинкрементное значение long, остается всего 8 байтов. Еще одним преимуществом является то, что самоинкрементный первичный ключ может гарантировать, что вставка должна быть вставлена только в конце страницы данных и не должна быть вставлена в середине, а идентификационный номер может быть вставлен в среднюю позицию. по порядку, что приведет к заполнению страницы данных и разделению страницы данных и т. д. потребление.

Как должны быть проиндексированы строки?

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

alter table user add index index(email(5));

Здесь установка первых 5 символов слева от электронной почты в качестве индекса может сузить диапазон, но если первые 5 символов могут содержать много повторяющихся данных, таких какzhangsan@XX.com,zhangsi@XX.com,zhangwu@XX.com,zhangliu@XX.com,zhangqi@XX.comЕго будут искать и проходить, а разница слишком мала.Один из принципов возобновления индексации в поле — это разница этого поля, поэтому разница в индексации слишком мала. Таким образом, должен получиться крайний левый префикс с приемлемой степенью дискриминации.

select count(distinct email) as L from user;（查询总数）

Затем выполните следующую инструкцию, чтобы увидеть разницу каждого индекса длины префикса и найти приемлемую длину.Например, если ваше требование состоит в том, что разница превышает 95%, вы можете рассчитать, как долго префикс соответствует вашим требованиям. разница = L(n)/L.

select
count(distinct left(email,4) as L4,
count(distinct left(email,5) as L5,
count(distinct left(email,6) as L6,
count(distinct left(email,7) as L7,
from user;

Сценарий 2, или сценарий удостоверения личности, как построить индекс для эквивалентного запроса на основе удостоверения личности?

Доступны два варианта:

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

select field_list from t where id_card = reverse('input_id_card_string');

Во-вторых, использовать хэш для создания хэш-поля идентификатора и использовать это поле в качестве индекса.

alter table t add id_card_crc int unsigned, add index(id_card_crc);

Используйте следующий оператор при запросе:

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string');

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

Уровни изоляции

Незафиксированное чтение (READ UNCOMMITTED)

Read uncommitted означает, что когда транзакция не зафиксирована, ее изменения могут быть видны другим транзакциям.

ПРОЧИТАТЬ СОВЕРШЕНО

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

ПОВТОРЯЕМОЕ ЧТЕНИЕ

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

Сериализация (SERIALIZABLE)

Сериализация, как следует из названия, предназначена для одной и той же строки записей: «запись» добавит «блокировку записи», «чтение» добавит «блокировку чтения». Когда возникает конфликт блокировки чтения-записи, транзакция, к которой был получен доступ позже, должна дождаться завершения выполнения предыдущей транзакции, прежде чем продолжить.

грязное чтение

Читайте незафиксированные результаты для других вещей.

виртуальное чтение

Нет результата обновления UPDATE, отправленного другими транзакциями в транзакции.

фантомное чтение

В вещах без чтения INSERT обновите результаты, представленные другими вещами.

Уровень MySQL по умолчанию — повторяемое чтение, уровень Oracle по умолчанию — зафиксированное чтение.

Чтение моментального снимка и текущее чтение

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

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `v` int not null
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  insert into r values (1,1);

сцена первая:

вещь А	вещь Б
begin;
1, select * from r;	begin;
	update r set v=v+1 where id =1;
	commit;
2, select * from r;
3, select * from r for update;
commit;
1,2,3 предложение, значение идентификатора которого равно количеству столбцов v 3 равны?

Сценарий второй:
сначала восстановить значение,

update r set v = 1 where id =1;

вещь А	вещь Б
begin;
1, select * from r;	begin;
	update r set v=v+1 where id = 1;
	commit;
2, select * from r;
update r set v=v+1 where id = 1;
3, select * from r;
commit;

Каково на данный момент значение v в результатах запроса операторов 1, 2 и 3?

Снимок прочитан

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

select * from table where ?;

текущее чтение

Специальные операции чтения, операции вставки/обновления/удаления относятся к текущему чтению и требуют блокировки.

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

Все приведенные выше операторы относятся к текущей прочитанной и прочитанной последней версии записи. Более того, после чтения также необходимо убедиться, что другие параллельные транзакции не смогут модифицировать текущую запись и заблокировать прочитанную запись. Среди них, в дополнение к первому оператору, блокировка S (общая блокировка) добавляется к записи чтения, а блокировка X (монопольная блокировка) добавляется для других операций.
Почему параллельное чтение не требует блокировки?
MVCC (см. Высокопроизводительный MySQL)
Вот краткое введение в MVCC (управление многоверсионным параллелизмом).Реализация MVCC достигается путем сохранения моментальных снимков данных в определенный момент времени. Различные механизмы хранения реализованы по-разному. Вот краткое введение в упрощенное поведение InnoDB, чтобы проиллюстрировать, как работает MVCC.
MVCC InnoDB достигается путем хранения двух скрытых столбцов за каждой строкой записей. Один из двух столбцов содержит время создания строки, а другой — время истечения срока действия строки. На самом деле здесь сохраняется не конкретное время, а номер версии системы. Каждый раз, когда открывается новая вещь, номер версии системы будет автоматически увеличиваться, а номер версии системы в начале вещи будет использоваться как номер версии вещи, который используется для сравнения с версией каждой строки. запрошенных записей.
Давайте посмотрим, как MVCC работает на уровне изоляции InnoDB, REPEATABLE READ.

SELECT

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

б) Удаленная версия строки либо не определена, либо больше, чем номер версии текущей вещи. Это гарантирует, что строки, прочитанные текущей транзакцией, не будут удалены до начала транзакции. 2. ВСТАВИТЬ

Сохраните номер текущей версии системы в качестве номера версии строки для каждой новой вставленной строки данных.

DELETE

Сохраните номер текущей версии системы в качестве флага удаления строки для каждой удаленной строки.

UPDATE

А. Вставьте строку данных и сохраните текущий номер версии системы в качестве номера версии строки.

Б. Добавьте номер текущей версии системы в качестве тега удаления исходных данных.

Innodb-lock.

Общие блокировки и эксклюзивные блокировки

Общий (S) замок

Транзакция, удерживающая блокировку чтения записи строки, позволяет прочитать запись строки.

Эксклюзивная блокировка, блокировка записи (эксклюзивная (X) блокировка)

Транзакция, удерживающая блокировку записи строки, позволяет обновлять или удалять запись строки.

блокировка стола

РЕЦИДНЫЕ ЗАМКИ

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Блокировки строк применяются к записям индекса. Например, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE предотвратит добавление, удаление и изменение t.c1=10 другими вещами.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

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

Гэп-замки

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Гэп-блокировки — это блокировки, действующие между записями индекса, перед первой записью индекса или после последней записи индекса.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

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

Почему есть замок пробелов?

Предотвращение фантомных чтений. (В настоящее время читаю)

Какую блокировку добавляет следующий оператор? Известные условия Версия MySQL 5.7, механизм InnoDB, уровень изоляции RR.

delete from t where id = 10;

Добавляем блокировку записи на запись с id=10 (берём индекс первичного ключа).Этот ответ правильный?
Это может быть правильно или неправильно, потому что условия недостаточно известны. Отсутствуют следующие условия:
1. Является ли идентификатор первичным ключом?
2. Если столбец идентификатора не является первичным ключом, есть ли индекс по идентификатору?
3. Если в столбце id есть вторичный индекс, является ли этот индекс уникальным?
4. Существует ли запись с id=10?
Добавленный замок оценивается по следующей комбинации:

Столбец id является первичным ключом, и к записи с id = 10 добавляются только блокировки X.

2. id является уникальным индексом, и запись с id = 10 существует, добавьте блокировку строки к записи с B+Tree id = 10 уникального идентификатора индекса и добавьте X к первичному ключу b, соответствующему id = 10 по кластеризованному индексу (индексу первичного ключа).3. id является единственным индексом, но запись с id = 10 не существует, добавьте блокировку промежутка в промежуток B+Tree id = 10 уникального идентификатора индекса, чтобы предотвратить вставку новых квалифицированных записей, что приводит к фантомным чтение, то есть блокировка Пробел между (8,b) и (15,c) в дереве индексов id.

4. Для неуникальных индексов id сначала добавьте блокировки X к записям дерева индексов id, которые соответствуют id = 10, и добавьте блокировки X к записям индекса первичного ключа, которые соответствуют id = 10, а также вставьте id = 10 в дерево индексов идентификаторов.Позиции добавляются к блокировкам пробелов, а именно (6,a) до (10,b), (10,b) до (10,c), (10,c) до (17,d).

5. Нет индекса для id.Это самая ужасная ситуация.Добавьте блокировки X ко всем записям строки в индексе первичного ключа, и добавьте блокировки пробела ко всем пробелам.Хотя MySQL имеет некоторые оптимизации для этого, этот метод не рекомендуется , особенно для длинных вещей.Остановите все службы, кроме чтения снимков.

Взгляните на следующие примеры:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t values(2,2),(6,6);

Версия MySQL 5.7, механизм InnoDB, когда уровень изоляции RR, создайте таблицу t, где id — первичный ключ, а k — общий индекс.

Вопрос 1: Можно ли вставить утверждения 1, 2 и 3?

| Вещь A | Вещь B | Вещь C | Вещь D |--|--|--|--|--| начать;| выберите * из t, где k=4 для обновления |||| ||1, вставить в t значений (3,3);|| |||2, вставить в t значений (4,4);| ||||3, вставить в t значений (5,5); |совершить;

Его нельзя вставить, так как k — обычный индекс, поэтому между k=2 и k=6 будет добавлена блокировка промежутка в B+-дереве индекса k для предотвращения фантомных чтений.

Вопрос 2: Можно ли вставить утверждения 4, 5, 6, 7?

вещь А	вещь Б	вещь С
begin;
select * from t where k=4 for update;
	4, insert into t values (1,2);
	5, insert into t values (3,2);
		6, insert into t values (5,6);
		7, insert into t values (7,6);
commit;

Заявления 4 и 7 могут быть вставлены, а заявления 5 и 6 не могут быть вставлены.

Проанализируйте причины в соответствии с предыдущим блокирующим механизмом:
Во-первых, выберите * из t, где k = 4 для обновления, поскольку k является общим индексом, в механизме InnoDB, в случае уровня изоляции по умолчанию RR, будет добавлена блокировка пробела для предотвращения вставки новых данных и фантомного чтения. Метод блокировки следующий:

Если утверждения 4, 5, 6, 7 можно вставить, то куда их следует вставить?
Пожалуйста, посмотрите на изображение ниже:

Поскольку индексы дерева B+ расположены упорядоченно, если можно вставить 4, 5, 6 и 7, позиция должна быть вставлена в дерево B+ с обычным индексом k, как показано на рисунке выше. С позиции вставки данных на приведенном выше рисунке очевидно, почему операторы 5 и 6 не могут быть вставлены. Поскольку для индексного дерева K листовые узлы хранят (k, id), в дополнение к сортировке по значению k, но также и сортировке по id, поэтому вставляем в t значения (3, 2) и вставляем в t значения (5, 6) ) Позиция вставки попадает в диапазон блокировки пробела, поэтому она не может быть вставлена, а позиция вставки вставки в значения t (1,2) и вставка в значения t (7, 6) выходит за пределы диапазона блокировки зазора, поэтому его можно вставить.

Что именно блокирует гэп-лок?

Из вышеприведенного примера видно, что блокировка пробела не полностью блокирует вставку k=2 и k=6 записей, то есть блокирует не конкретное значение, а все позиции, где может появиться новое значение k=4. В этом примере это позиция между (2,2) и (6,6), потому что индекс расположен по порядку, поэтому k=4 можно вставить только между (2,2) и (6, 6) .

При каких обстоятельствах возникает гэп-лок?

При использовании ограничения неиндексированного столбца после условия where.
После where используйте неуникальные ограничения столбца индекса (эквивалентный поиск, крайний левый префикс строкНРАВИТСЯ 'Чжан%')когда.
При использовании уникального индекса и обычного индекса для запроса диапазона после условия where (, >=, между и).
Для уникальных индексов с объединением нескольких столбцов также генерируются гэп-блокировки, если в запросе нет равнозначного запроса, включающего все столбцы. Например, совместный уникальный индекс совместного уникального индекса column_1, cloumn_2, column_3, если текущий оператор чтения равен ..., где column_1 = value_1 и cloumn_2 = value_2, будет сгенерирована блокировка пробела, поскольку столбец_3 не включен.
По уникальному индексуdelete,update,select ··· lock in share modeилиselect ··· for updateКогда условие эквивалентности ограничивает столбец, которая не существует.