показатель

Распространенные типы индексов

Распространенные типы индексов:Хэш-индекс, индекс отсортированного массива, индекс двоичного дерева, список пропускаи Т. Д. Эта статья посвящена механизму хранения MySQL по умолчанию.InnoDBструктура индекса.

Индексная структура InnoDB

В InnoDB это через дерево поиска с несколькими путями -В+ деревоРеализуйте структуру индекса. В дереве B+ этоТолько конечные узлы будут хранить данные,иВсе листовые узлы образуют связанный список. В InnoDB поддерживается двусвязный список.

У вас может возникнуть вопрос, зачем использовать дерево B+ вместо бинарного дерева или дерева B?

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

Вы можете добавить несколько индексов при построении таблицы, и InnDBСоздайте дерево B+ для каждого индекса для хранения индекса.

Например, в это время мы создали простую тестовую таблицу

create table test(
  id int primary key,
  a int not null,
  name varchar,
  index(a)
)engine = InnoDB;

На данный момент InnDB построит для нас два индексных дерева B+.

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

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

форма возврата

Итак, появилась концепция под названиемформа возврата, например, в это время мы выполняем операцию запроса

select name from test where a = 30;

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

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

Обслуживание индекса

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

Например, если мы используем идентификационный номер в качестве первичного ключа в пользовательской таблице, конечный узел каждого вторичного индекса занимает около 20 байт, а если в качестве первичного ключа используется целое число, требуется только 4 байта. длинное целое ( bigint ) составляет 8 байт. То есть, если я поддерживаю список индексов 4 г после целого числа, тогда идентификатор будет 20 г.

так что мы можемУменьшите пространство индекса, уменьшив размер индекса.

Конечно, чтобы поддерживать порядок индекса, дерево B+ будет выполнять необходимое обслуживание во время удаления и вставки (удаление в InnoDB пометит узел как «повторно используемый», чтобы уменьшить изменения в структуре).

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

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

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

Например, в это время мы выполняем операцию выбора

select id from test where a = 1;

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

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

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

На этот раз мы создаем новую таблицу учеников

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `class` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_name` (`class`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Мы используем класс (номер класса) и имя, чтобы сделатьсовместный индекс, вы можете спросить, какая польза от этого совместного индекса? Мы можем объединить вышеперечисленноеиндекс покрытияЧтобы понять, например, в это время у нас есть потребность,Нам нужно найти соответствующее имя ученика по номеру класса.

select name from stu where class = 102;

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

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

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

этосовместный индексВ основе лежит совместное правило сопоставления индексов.

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

select class from stu where name = '张三';

В настоящее времяВместо обхода нашего индекса объединения мы выполняем полное сканирование таблицы..

Зачем? так какКрайний левый принцип соответствия. Мы можем нарисовать простую схему, чтобы понять.

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

Конечно, самый левый принцип соответствия также имеет эти правила.

Когда все значения будут сопоставлены, оптимизатор изменит порядок, то есть порядок вашего полного сопоставления значений несовместим с исходным порядком объединенного индекса, и оптимизатор отрегулирует его для вас.
индекс соответствует отначни с крайнего левого, если нет, будет выполнено полное сканирование таблицы Например, если вы разрабатываете совместный индекс (a,b,c), то вы можете использовать (a),(a,b),(a,b,c ) и вы используете (b), (b, c), (c) не будете использовать индекс.
Обнаружено совпадение диапазона для деиндексации. Например, в это время вы выполняете операцию выбора, подобную этой

select * from stu where class > 100 and name = '张三';

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

выталкивание индекса

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

select * from stu where class > 100 and name = '张三';

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

Но свыталкивание индексаПосле этого стало так: на этот раз два «Ли Си» и «Сяо Мин» не вернутся за стол.

Поскольку здесь сопоставляется следующее имя = Zhang San, то есть, если самый левый принцип сопоставления прерывается из-за запроса диапазона, InnoDB все равно будетвыталкивание индексадля оптимизации производительности.

некоторые лучшие практики

В каких ситуациях вам нужно создать индекс?

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

В каких случаях не нужно создавать индекс

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

разное

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

Замок

глобальная блокировка

MySQL предоставляет способ добавить глобальную блокировку чтения, команда Flush table with read lock (FTWRL). Когда вам нужно сделать всю библиотеку в состоянии только для чтения, вы можете использовать эту команду, и тогда следующие операторы других потоков будут заблокированы: операторы обновления данных (добавление, удаление и изменение данных), операторы определения данных (включая создание таблиц, изменение структур таблиц и т. д.) и операторы фиксации для транзакций, подобных обновлению.

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

Приобретено в MVCCПросмотр согласованностиОперация резервного копирования делает резервное копирование очень простым.Если вы хотите узнать о MVCC, вы можете обратиться к другой моей статье.Вы действительно понимаете MVCC? Давайте потренируемся вручную?.

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

Блокировка метаданных MDL (блокировка метаданных)

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

как сказать? молдавские леи делятся наMDL блокировка записииБлокировка чтения MDL, правила блокировки такие

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

lock tables xxx read/write;

Это команда для установки блокировки чтения и записи в таблице.Если блокировка таблиц t1 читается, t2 пишется, оператор выполняется в потоке A, операторы других потоков, записывающих t1 и читающих и записывающих t2, будут заблокированы. . В то же время перед выполнением таблиц разблокировки поток А может выполнять только операции чтения t1 и чтения и записи t2. Даже запись t1 не разрешена, и, естественно, он не может получить доступ к другим таблицам.

Эта блокировка таблицы — способ борьбы с параллелизмом, но обычно используемый в InnoDB — этоблокировка строки.

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

Мы знаем, что механизмом хранения MySQL по умолчанию до версии 5.5 является MyISAM, и самая большая разница между MyISAM и InnoDB составляет два

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

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

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

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

И блокировка строки тоже будет вызывать очень головную боль, т.е.тупик.

Если транзакция A имеет блокировку записи для строки 100, а транзакция B имеет блокировку записи для строки 101, то транзакция A хочет изменить строку 101, а транзакция B хочет изменить строку 100, поэтому владение и ожидание приведут к проблеме взаимоблокировки. Перед лицом проблем взаимоблокировки есть только обнаружение и предотвращение.

блокировка следующего ключа

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

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

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

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

Суммировать

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

Для блокировок MySQL, в основном с точки зрения блокировок строк, InnoDB на самом делеИспользование блокировок строк, MVCC и блокировок следующего ключа для обеспечения контроля параллелизма транзакций..

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