Принцип и применение индекса MySQL: тип индекса, структура хранения и блокировка

база данных MySQL

image.png

Эта статья взята изАрхитектура движка MySQL и оптимизация производительности https://url.wx-coder.cn/IF5HH, в справочной документации указано наAwesome MySQL List https://parg.co/htL.

Принцип и применение индекса MySQL: тип индекса, структура хранения и блокировка

существуетСтруктуры данных и алгоритмы — Указатель https://url.wx-coder.cn/O07eIВ этом разделе мы обсудили основные алгоритмы индексации файлов и полнотекстового индексирования, такие как B+Tree, LSM-Tree, а в этой статье мы обсудим практическое применение индексации файлов в реляционных базах данных.

Индекс (Index) — это структура данных, которая помогает системе базы данных эффективно получать данные, а индекс базы данных создается, по существу, за счет добавления дополнительных операций записи и пространства для хранения для поддержания структуры данных индекса и используется для повышения эффективности работы. поиск данных в базе данных структура данных. Индексы могут помочь нам быстро находить данные, не просматривая каждую строку в базе данных при каждом поиске. Конечно, чем больше и дольше будет установлен индекс, тем лучше, потому что помимо занимаемого места есть дополнительные операции по обновлению индекса для последующих добавлений, удалений и модификаций базы данных. Вообще говоря, полное сканирование таблицы выполняется быстрее для небольших таблиц, индексы используются для средних и больших таблиц, а индексы для очень больших таблиц в основном неэффективны.Возможно, нам потребуется использовать независимую систему полнотекстового индексирования, собственный полнотекстовый индекс MySQL. может использоваться только для InnoDB, MyISAM и может выполнять полнотекстовый поиск только на английском языке, как правило, с использованием механизмов полнотекстового индексирования, таких как ES и Solr.

тип индекса

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

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

Листовые узлы дерева MyISAM B+ хранят только адрес данных, поэтому он называется некластеризованным индексом. Механизм MyISAM использует B+Tree в качестве структуры индекса, а поле данных конечного узла хранит адрес записи данных; в MyISAM нет разницы в структуре между первичным индексом и вторичным ключом, но первичный индекс требует, чтобы ключ был уникальным, а ключ вторичного индекса можно повторять.

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

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

Строка данных не является наименьшей единицей хранения, управляемой подсистемой хранения. Индекс может помочь нам найти только определенную страницу данных. Наименьшая единица каждого чтения и записи на диск также является страницей данных, а страница данных хранит несколько строк данных. Вы должны понимать внутреннюю структуру страницы данных, чтобы знать, как подсистема хранения находит строку данных.Управление хранилищем MySQL https://url.wx-coder.cn/IF5HHряд.

индекс селективности

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

Index Selectivity = Cardinality / #T

Очевидно, необязательный диапазон значений(0, 1], значение индекса с более высокой селективностью больше, что определяется природой B+Tree. В реальной базе данных мы можем вычислить селективность столбца с помощью следующего оператора:

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM titles;

первичный ключ

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

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

  • Во-первых, есть ли в таблице непустой уникальный индекс (Unique NOT NULL), если да, то столбец является первичным ключом.
  • Если вышеуказанные условия не выполняются, механизм хранения InnoDB автоматически создает 6-байтовый указатель, который пользователь не может просмотреть или получить к нему доступ.

Выбор первичного ключа

существуетРаспределенный идентификатор https://url.wx-coder.cn/tQ5eHВ этой статье мы обсудили стратегию выбора распределенных идентификаторов в распределенных сценариях, и в базе данных у нас также будут такие соображения. Во-первых, у MySQL официально есть четкая рекомендация, что первичный ключ должен быть как можно короче, а UUID длиной 36 символов не соответствует требованиям; если первичный ключ представляет собой очень длинную строку и много обычных индексов встроенный, это приведет к тому, что обычный индекс будет занимать много места в физическом пространстве. И первичный ключ желательно увеличивать последовательно, иначе под движком InnoDB разброс UUID может вызвать частые изменения расположения данных, серьезно влияющие на производительность.

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

  • Уникальность: самоувеличивающиеся идентификаторы легко взломать методом грубой силы.При переносе данных, особенно при объединении таблиц, неизбежно возникают конфликты. UUID может гарантировать уникальность и полностью избежать конфликтов.
  • Длина ключа: длина поля автоинкремента намного меньше, чем UUID, что оказывает большее влияние на производительность поиска. Когда механизм Innodb выполняет извлечение данных, он сначала находит первичный ключ в соответствии с индексом, а затем находит запись в соответствии с первичным ключом; таким образом, когда длина первичного ключа короткая, производительность чтения будет выше. .
  • Параллелизм: в случае автоинкрементных идентификаторов и высокого параллелизма конкуренция за автоинкрементные блокировки снизит пропускную способность базы данных. UUID может генерировать UUID на уровне приложения для повышения пропускной способности базы данных.
  • Индекс базы данных: данные таблицы в InnoDB хранятся в порядке первичных ключей.Если при записи данных происходит случайный ввод-вывод, блоки диска будут часто перемещаться. Когда объем данных велик, короткая доска для письма будет очень очевидной. Новые данные в самоувеличивающемся идентификаторе могут быть упорядочены по умолчанию, что значительно повышает производительность; UUID не имеет порядка между первичными ключами.

Первичный ключ и уникальный индекс

Первичный ключ — это уникальный индекс, но уникальный индекс не обязательно является первичным ключом.Уникальный индекс может быть пустым, но может быть только одно пустое значение, а первичный ключ не может быть пустым. Для одностолбцового индекса требуется, чтобы все данные в столбце были разными, но допускаются значения NULL, для многостолбцового объединенного индекса требуется, чтобы комбинация этих столбцов была уникальной. Сам уникальный индекс может использоваться как индекс, а также может использоваться для создания ограничений данных на практике, чтобы предотвратить добавление или изменение одних и тех же данных, тем самым обеспечивая целостность данных.

Для строкового типа можно указать длину префикса индекса (это необходимо для параметра длины префикса BLOB/TEXT).В таблице InnoDB длина префикса составляет до 767 байт, а параметр M измеряется в байтах. Поэтому для слишком длинной строки построение индекса B+Tree является пустой тратой времени. В настоящее время это способ моделирования индекса HASH вручную, но этот метод не может гибко использовать метод префикса для запроса строки. (например, такие операции, как LIKE).

совместный индекс

Одностолбцовый индекс относится к индексу, установленному для поля в таблице.Как правило, выбор целого числа или меньшей строки фиксированной длины для создания индекса более способствует повышению эффективности. Федеративный индекс относится к индексу, в котором несколько полей организованы в определенном порядке. по индексу(name, city, gender)Например, сначала оно организовано в порядке поля имени, когда значение поля имени одинаково (например, Буш), оно организовано в порядке поля города, а когда значение поля города то же самое, оно организовано в гендерном поле. Поскольку индекс состоит из нескольких столбцов в объединенном индексе, иногда мы можем добавить поля, которые необходимо часто запрашивать, в объединенный индекс. Например, нам часто нужно найти возраст на основе имени. Мы можем создать совместный индекс имени и возраст.

К распространенным условным объединениям относятся условное объединение WHERE и условное объединение ORDER BY; так называемое условное объединение WHERE означает, что для эквивалентных условий в условии WHERE используемые поля согласуются с полями индекса объединения (порядок может быть несогласованным) .

Объединение ORDER BY означает, что если поле после ORDER BY является полем после индекса объединения, которое покрывает условие where, поскольку индекс уже находится в упорядоченном состоянии, MySQL будет напрямую читать упорядоченные данные из индекса, а затем читать их на диске. Данные упорядочиваются в таком порядке после извлечения, что снижает необходимость сортировки данных на диске. То есть для запросов, не покрывающих ORDER BY, есть Создание индекса сортировки, который является наиболее трудоемким для сортировки дисковых данных, для запросов, покрывающих ORDER BY, его сортировать не нужно, и его время- потребление в основном отражается на данных с диска.Процесс подтягивания данных.

индекс префикса

Индексы префиксов MySQL можно разделить на три категории: префиксы объединенных индексов, такие как префиксы, и префиксы строк.

Префикс объединенного индекса соответствует самому левому префиксу (самый левый префикс).

Префикс объединенного индекса означает, что при создании многостолбцового индекса все или часть столбцов индекса должны использоваться в порядке слева направо, чтобы можно было полностью использовать объединенный индекс, например:(col1, col2, col3)использовать(col1)、(col1, col2)、(col1, col2, col3)эффективный. Соответствует справа в операторе запроса, пока не встретится запрос диапазона(>,<,BETWEEN,LIKE)Просто прекратите сопоставление, и последующие индексированные столбцы не будут использовать индекс для оптимизации поиска.

от(name, city, interest)Возьмем в качестве примера индекс объединения трех полей, если условие запросаwhere name='Bush';Затем вам нужно только найти значение первого куста в поле имени в соответствии с деревом B+, а затем последовательно сканировать последующие данные, пока не будут найдены первые данные, которые не являются кустом, и идентификатор данных индексного среза записанный в процессе сканирования. , и, наконец, запросите кластеризованный индекс в соответствии с идентификатором, чтобы получить набор результатов. Аналогично, для условия запроса какwhere name='Bush' and city='Chicago';Запрос, MySQL может напрямую найти срез индекса в средней серой части в соответствии с объединенным индексом, затем получить идентификатор данных среза индекса и, наконец, запросить кластеризованный индекс в соответствии с идентификатором для получения набора результатов.

Отсюда мы можем обратить внимание на префикс совместного индекса:

  • Невозможно использовать совместный индекс для полей, таких какwhere name='Bush' and interest='baseball';, для этого запроса поле имени может использовать первое поле объединенного индекса для фильтрации большей части данных, но для поля интереса оно не может напрямую найти данные среза индекса третьего поля через характеристики дерева B+, такие как бейсбол здесь Могут быть разбросаны среди вторых и седьмых данных. В конце концов, поле интереса фактически выполняет сканирование покрывающего индекса.
  • Для неэквивалентных условий, таких как >, where name='Bush' and city>'Chicago' and interest='baseball';, для этого условия запроса можно сначала отфильтровать данные первого поля индексного среза, не относящиеся к Бушу, в соответствии с полем имени, а затем найти позицию индексного среза в Чикаго в соответствии со вторым полем объединенного индекса. это неэквивалентное условие, здесь MySQL будет сканировать от расположенного Чикаго вниз по порядку.Поскольку поле интереса может быть разбросано где угодно в третьем поле индекса, третье поле не может участвовать в фильтрации срезов индекса.

Поэтому порядок столбцов B-Tree очень важен, и приведенные выше правила использования связаны с порядком столбцов. Для практических приложений обычно необходимо создавать индексы с разными столбцами и разным порядком столбцов в соответствии с конкретными требованиями. Предположим, есть индексIndex(A,B,C):

# 使用索引
A>5 AND A<10 - 最左前缀匹配
A=5 AND B>6 - 最左前缀匹配
A=5 AND B=6 AND C=7 - 全列匹配
A=5 AND B IN (2,3) AND C>5 - 最左前缀匹配，填坑

# 不能使用索引
B>5 - 没有包含最左前缀
B=6 AND C=7 - 没有包含最左前缀

# 使用部分索引
A>5 AND B=2 - 使用索引 A 列
A=5 AND B>6 AND C=2 - 使用索引的 A 和 B 列

Чтобы использовать индекс для сортировки результатов, порядок индекса должен соответствовать порядку в предложении ORDER BY, а порядок возрастания и убывания всех столбцов согласован (ASC/DESC). Если запрос объединяет несколько таблиц, в столбце ORDER BY есть ссылка только на первую таблицу (требуется последовательное СОЕДИНЕНИЕ).

# 使用索引排序
ORDER BY A - 最左前缀匹配
WHERE A=5 ORDER BY B,C - 最左前缀匹配
WHERE A=5 ORDER BY B DESC - 最左前缀匹配
WHERE A>5 ORDER BY A,B - 最左前缀匹配

# 不能使用索引排序
WHERE A=5 ORDER BY B DESC,C ASC - 升降序不一致
WHERE A=5 ORDER BY B,D - D 不在索引中
WHERE A=5 ORDER BY C - 没有包含最左前缀
WHERE A>5 ORDER BY B,C - 第一列是范围条件，无法使用 BC 排序
WHERE A=5 AND B IN(1, 2) ORDER BY C - B 也是范围条件，无法用 C 排序

как префикс

Для аналогичного префикса это означает, что при использовании аналогичного запроса, если используемое выражениеfirst_name like 'rMq%';Затем его можно использовать для индексации поля first_name. Но дляfirst_name like '%Chu%';, он не может использовать индекс first_name. Для подобного префикса MySQL фактически использует стратегию завершения для использования индекса внизу, например здесьfirst_name like 'rMq%';, MySQL дополнит его двумя частями данных: rMqAAAAA и rMqzzzzz, а длина завершенной части будет максимальной длиной текущего поля. При использовании индексного запроса MySQL использует эти две части данных для позиционирования индекса, и требуемый окончательный результирующий набор — это данные в средней части этих двух точек позиционирования. Ниже приведена схема использования префикса like:

префикс строки

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

  • Избирательность части префикса поля относительно высока;
  • Общая селективность поля не слишком велика (если общая селективность поля относительно велика, можно использовать хэш-индекс).

Например, для поля first_name устанавливается префиксный индекс длины 4. Вы можете видеть, что если запрос используетwhere first_name='qWhNIZqxcbD';, то MySQL сначала перехватит первые четыре символа эквивалентного условия, а затем сравнит его с индексом префикса строки, чтобы найти фрагмент индекса с префиксом «qWhN», затем получит данные диска, соответствующие фрагменту индекса, и наконец, поле first_name полученных дисковых данных сравнивается со значением условия эквивалентности запроса для получения набора результатов.

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

select count(*) as cnt, first_name as perf from actor group by perf ORDER BY cnt desc limit 10;	-- 0
select count(*) as cnt, left(first_name, 2) as perf from actor group by perf ORDER BY cnt desc limit 10;	-- 2
select count(*) as cnt, left(first_name, 3) as perf from actor group by perf ORDER BY cnt desc limit 10;	-- 3
select count(*) as cnt, left(first_name, 4) as perf from actor group by perf ORDER BY cnt desc limit 10;	-- 4

другие индексы

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

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

select a, b, c from t where a='a' and b='b';

Если в этом запросе установлен совместный индекс (a, b, c), то это индекс с использованием покрывающего сканирования, поскольку для этого запроса первые два поля a и b индекса могут использоваться для фильтрации срезов индекса. в соответствии с условием where. Для среза отфильтрованного индекса вы можете напрямую считывать значения трех полей a, b и c в индексе без необходимости сканирования таблицы.

Индекс Samsung

Индекс Samsung относится к тому, что для запроса устанавливаются три общих условия индекса для удовлетворения условий Установленный индекс удовлетворяет каждому условию для конкретного запроса, что означает, что индекс получает одну звезду, а когда индекс получает три звезды, он is Указывает, что индекс является индексом Samsung для этого запроса. Индекс Samsung является оптимальным индексом для конкретного запроса.Условия создания индекса Samsung следующие:

  • Получить все столбцы предиката равенства（WHERE COL=…）как столбец в начале индекса;
  • Добавьте столбцы в ORDER BY в индекс;
  • Добавьте оставшиеся столбцы в операторе запроса к индексу и поместите столбцы переменных в конец, чтобы уменьшить стоимость обновления.

Например, для следующего запроса индекс(first_name, last_name, email)Просто индекс Samsung:

SELECT first_name, last_name, email FROM user WHERE first_name = 'aa' ORDER BY last_name;

В процессе создания индекса Samsung можно найти следующие правила:

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

структура хранения индексов

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

существуетСтруктура хранилища MySQLВ этой статье мы обсудили структуру хранения страниц данных MySQL.

Архитектура памяти Архитектура памяти

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

Буферный пул занимает самый большой блок памяти и используется для кэширования соответствующих данных.Файлы данных считываются в буферный пул постранично (16 КБ на страницу), а кэшированные данные сохраняются в соответствии с алгоритмом наименее недавно использовавшегося (LRU). ). Типы данных буферного пула buffer: страница данных, страница индекса, буфер вставки, адаптивный хэш-индекс, информация о блокировке, информация словаря данных и т. д., среди которых страницы данных и страницы индекса занимают большую часть памяти. Буфер журнала сначала помещает информацию журнала повторов в этот буфер, а затем с определенной периодичностью (по умолчанию 1 с) сбрасывает ее в файл журнала повторов.

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

Архитектура хранения |

Логическая структура хранилища механизма хранения InnoDB примерно такая же, как у Oracle.Все данные логически хранятся в пространстве, которое мы называем табличным пространством. Табличное пространство состоит из сегментов, экстентов и страниц. Страницы иногда называют блоками в некоторых документах, 1 экстент = 64 страницы, логическая структура хранилища механизма хранения InnoDB примерно такая, как показано на рисунке:

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

Табличное пространство состоит из различных сегментов.Механизм хранения InnoDB организован по индексу, а конечные узлы в индексе используются для записи данных и хранятся в сегменте данных, а неконечные узлы используются для построения индекса. и хранится в индексном сегменте. Область состоит из последовательных страниц. В любом случае область составляет 1 МБ. В области может быть несколько страниц. Каждая страница по умолчанию имеет размер 16 КБ, поэтому по умолчанию область может содержать 64 последовательных страницы. Размер можно установить с помощью innodb_page_size, а записи конкретных строк хранятся на странице. Строка записей в конечном итоге хранится в файле в двоичной форме.

С физической точки зрения таблица InnoDB состоит из общего табличного пространства, группы файлов журнала (точнее, это должна быть группа файлов повторов) и файла определения структуры таблицы. Если для innodb_file_per_table установлено значение on, каждая таблица будет генерировать файл табличного пространства независимо, оканчивающийся на ibd, а данные, индексы и внутренняя информация словаря данных таблицы будут сохранены в этом отдельном файле табличного пространства. Файл определения структуры таблицы заканчивается на frm, который не зависит от механизма хранения.Файл определения структуры таблицы любого механизма хранения аналогичен файлу .frm.

Архитектура процессов Архитектура процессов

По умолчанию InnoDB имеет 7 фоновых потоков, включая 4 потока ввода-вывода, 1 главный поток, 1 поток монитора блокировки и один поток монитора ошибок. Основная работа InnoDB выполняется в одном главном потоке. Основной поток имеет наивысший приоритет и в основном делится на следующие циклы: основной цикл (цикл), фоновый цикл (фоновый цикл), цикл обновления (цикл сброса) и цикл приостановки (цикл приостановки).

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

void master_thread() (
    loop:
    for (int i =0; i <10; i++){
        do thing once per second
        sleep 1 second if necessary
    }
    do things once per ten seconds
    goto loop;
}
  • К операциям, выполняемым раз в секунду, относятся: очистка буфера журнала (всегда), слияние буфера вставки (возможно), очистка до 100 грязных страниц (возможно) и переключение на фоновый цикл (возможно), если нет текущего пользователя. Мероприятия.
  • Операции каждые 10 секунд включают в себя: слияние до 5 буферов вставки (всегда), сброс буферов журнала (всегда), сброс 100 или 10 грязных страниц на диск (всегда), создание контрольной точки (всегда) ), удаление бесполезных страниц отмены (всегда). ).
  • Фоновый цикл, если нет активности пользователя или база данных закрыта, он переключится на этот цикл для выполнения следующих операций: удалить бесполезные страницы отмены (всегда), объединить 20 буферов вставки (всегда), вернуться к основному циклу ( всегда ) и непрерывно обновлять 100 страниц, пока условие не перейдет в цикл сброса (возможно).
  • Если в цикле сброса делать нечего, переключитесь на цикл приостановки и приостановите основной поток.

Индексы и блокировки

MySQL предоставляет нам три уровня блокировок: блокировки строк, блокировки таблиц и блокировки страниц.Среди них блокировки таблиц имеют низкие накладные расходы и быструю блокировку, отсутствие взаимоблокировок, высокую силу блокировки, высокую вероятность конфликтов блокировок и наименьший параллелизм. Накладные расходы на блокировку строк высоки, блокировка выполняется медленно, возникнет тупиковая ситуация, степень детализации блокировки мала, вероятность конфликта блокировок низка, а параллелизм высоки, накладные расходы на блокировку страниц и скорость блокировки находятся между блокировками таблицы и блокировками строк, возникнут тупиковые блокировки; Степень детализации блокировки находится между блокировками таблиц и блокировками строк, а степень параллелизма является общей. У каждого механизма хранения может быть своя собственная стратегия блокировки.Например, механизм MyISAM поддерживает только блокировки на уровне таблицы, в то время как механизм InnoDB поддерживает блокировки на уровне строк (по умолчанию) в дополнение к блокировкам на уровне таблицы.

блокировка строки блокировка стола блокировка страницы
MyISAM √
BDB √ √
InnoDB √ √

Блокировки строк InnoDB реализуются путем блокировки элементов индекса в индексе, что отличается от MySQL и Oracle, который реализуется путем блокировки соответствующих строк данных в блоке данных. Функция реализации блокировки строк в InnoDB означает, что InnoDB использует блокировки на уровне строк только тогда, когда данные извлекаются с помощью условий индекса, в противном случае InnoDB будет использовать блокировки таблиц.for updateЗапись не существует приведет к блокировке всей таблицы. Когда таблица имеет несколько индексов, разные транзакции могут использовать разные индексы для блокировки разных строк.Кроме того, независимо от того, используете ли вы индекс первичного ключа, уникальный индекс или общий индекс, InnoDB будет использовать блокировки строк для блокировки данных.

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

Например дляselect * from o_order where order_sn = '201912102322' for update;Этот оператор SQL имеет разные стратегии блокировки для разных индексов:

  • order_sn — индекс первичного ключа, в этом случае будет индекс первичного ключаorder_sn = 201912102322Добавьте монопольную блокировку к этой записи.

  • order_sn — это общий индекс и уникальный индекс, который добавит эксклюзивную блокировку к соответствующей записи в общем индексе и добавит эксклюзивную блокировку к соответствующей записи в индексе первичного ключа.

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

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

  • order_sn = 201912102322Если эта запись не существует, если order_sn является индексом первичного ключа, будет добавлена ​​блокировка пробела, и этот пробел будет первой записью, чей order_sn меньше 201912102322, до первой записи, чей order_sn больше 201912102322 в индексе первичного ключа. . Только представьте, если нет гэп-лока, если что-то еще вставитorder_sn = 201912102322Для справки, поскольку select for update является текущим чтением, даже если вышеуказанная вещь не отправлена, если вещь будет повторно запрошена один раз, произойдет фантомное чтение.

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

дальнейшее чтение

В этой статье не рассматривается соответствующее содержание оптимизации индексов в MySQL, вы можете обратиться кАрхитектура движка MySQL и оптимизация производительности https://url.wx-coder.cn/IF5HHСоответствующие главы по оптимизации производительности в серии.

Категории