В MySQL индексы относятся к понятию уровня механизма хранения.Разные механизмы хранения реализуют индексы по-разному.В этой статье в основном обсуждаются методы реализации индексов механизмов хранения MyISAM и InnoDB.
Реализация индекса MyISAM
Механизм MyISAM использует B+Tree в качестве структуры индекса.
MyISAM присваивает номера строк в том порядке, в котором данные были вставлены, начиная с 0, а затем сохраняются на диске в том порядке, в котором данные были вставлены. Поскольку строки имеют фиксированную длину, нужную строку можно найти, пропустив соответствующие байты с начала таблицы.
Индекс первого уровня MyISAM (индекс первичного ключа), узел содержит несколько внутренних узлов, и каждый конечный узел в индексе содержит «номер строки». Предполагая, что мы используем col1 в качестве первичного ключа, следующий рисунок является схематическим представлением первичного ключа таблицы MyISAM.
Видно, что индексный файл MyISAM сохраняет только записи данныхномер строки, а затем вернуть таблицу для запроса необходимых данных по этому номеру строки.
Как насчет индекса (вспомогательного индекса) в столбце col2? Есть ли что-то особенное? Ответ — нет, и он ничем не отличается от первичного индекса (primary key index). В MyISAM нет разницы в структуре между первичным индексом и вторичным ключом (Secondary key), но первичный индекс требует, чтобы ключ был уникальным, в то время как ключ вторичного индекса может повторяться. Если мы построим вторичный индекс на col2, структура этого индекса будет следующей:
Таким образом, алгоритм поиска индекса в MyISAM заключается в том, чтобы сначала искать индекс в соответствии с алгоритмом поиска B + Tree.Если указанный ключ существует, значение поля данных извлекается, а затем считывается соответствующая запись данных со значением поля данных в качестве адреса. Индекс режима индекса MyISAM и хранилище данных являются отдельными, некластеризованными», поэтому его также называют некластеризованным индексом.
Реализация индекса InnoDB
Хотя InnoDB также использует B+Tree в качестве структуры индекса, конкретная реализация полностью отличается от MyISAM. Поскольку InnoDB поддерживает кластеризованные индексы (индексы первичного ключа), кластеризованные индексы представляют собой таблицы, поэтому InnoDB не требует отдельного хранилища строк, такого как MyISAM. Другими словами, файлы данных InnoDB сами по себе являются индексными файлами.
Каждый конечный узел кластеризованного индекса содержит значение первичного ключа, идентификатор транзакции, указатели отката для транзакций и MVCC, а такжевсе остальные столбцы. Предполагая, что мы используем col1 в качестве первичного ключа, следующий рисунок представляет собой схематическое представление кластеризованного индекса (первичный ключ) таблицы InnoDB.
В отличие от MyISAM, вторичные и кластерные индексы InnoDB очень разные.Листовой узел вторичного индекса InnoDB хранит не номер строки (указатель строки), а столбец первичного ключа.. Недостатком этой стратегии является то, что для вторичного индекса требуется два поиска индекса: первый раз для поиска первичного ключа во вторичном индексе и второй раз для поиска необходимой строки данных по первичному ключу в кластеризованном индексе.
Голос за кадром: Вы можете использоватьпокрытие индексаЧтобы избежать обратного запроса к таблице, требуется только один обратный запрос.Для InnoDB требуется только один поиск по индексу для запроса необходимых записей данных, поскольку требуемые записи данных были проиндексированы во вторичный индекс напрямую. можно найти.
Преимущество состоит в том, что InnoDB не нужно обновлять этот «указатель» в первичном индексе при перемещении строк, потому что первичный ключ не меняется, а изменяется указатель строки.
Вторичный индекс InnoDB показан на рисунке:
Что вы должны знать об использовании первичных ключей InnoDB
Поскольку индексный метод InnoDB агрегирует данные с помощью первичного ключа, он сильно зависит от первичного ключа. Индекс Если первичный ключ не определен, InnoDB вместо этого выберет уникальный ненулевой индекс. Если такого индекса нет, InnoDB неявно определит первичный ключ как кластеризованный индекс.
Преимущества кластерных индексов:
1. Связанные данные можно хранить вместе, чтобы уменьшить дисковый ввод-вывод во время запроса данных.
2. Доступ к данным быстрее, потому что кластеризованный индекс представляет собой таблицу, а индекс и данные хранятся в B+Tree.
3. Значение первичного ключа в узле страницы можно использовать напрямую при использовании запроса, охватываемого индексом.
Недостатки кластерных индексов:
1. Скорость вставки сильно зависит от порядка вставки
2. Обновление столбца кластеризованного индекса обходится дорого, потому что оно заставляет InnoDB перемещать обновленный столбец в новое место.
3. Таблица, основанная на кластеризованном индексе, может вызвать «разрыв страницы» при вставке новой строки или при обновлении первичного ключа и необходимости перемещения строки. Когда значение первичного ключа строки требует, чтобы строка была вставлена в полную страницу, механизм хранения разделит страницу на две страницы, чтобы разместить строку.Это операция разделения страницы, которая приведет к тому, что таблица займет больше места. места для хранения.
Голос за кадром: ОСтраница, о котором мы также упоминали в предыдущей статье. Страница — это логический блок памяти управления компьютером.Оборудование и операционные системы часто делят основную память и дисковое хранилище на последовательные блоки одинакового размера, и каждый блок называется страницей. Память и диск обмениваются данными в единицах страниц.Разработчик системы базы данных умело использовал принцип упреждающего чтения с диска, чтобы установить размер узла равным одной странице, чтобы каждый узел мог быть полностью загружен только одним дисковым вводом-выводом..
Основываясь на приведенных выше характеристиках кластеризованного индекса,В InnoDB мы должны попытаться использовать независимые от приложения первичные ключи, такие как самоинкрементные первичные ключи, чтобы гарантировать, что строки данных записываются по порядку.. Вместо использования GUID, UUID для генерации случайного первичного ключа.
Вставьте последовательные значения индекса в кластеризованный индекс:
Каждая новая запись всегда вставляется после предыдущей записи:
Когда страница заполнена, продолжайте вставлять на новую страницу:
Вставьте случайные значения индекса в кластеризованный индекс:
Новая запись может быть вставлена в середину предыдущей записи, что приведет к принудительному перемещению предыдущей записи:
Заполненные страницы, которые были сброшены на диск, могут быть повторно прочитаны для повторной вставки, а также требуется разделение страниц:
Суммировать
Хотя индексы двух механизмов хранения, MyISAM и InnoDB, используют структуру данных B+Tree, все же существуют некоторые различия в конкретной реализации. InnoDB поддерживает кластеризованные индексы, которые представляют собой таблицы, поэтому InnoDB не требует отдельного хранилища строк, как MyISAM. Другими словами, файлы данных InnoDB сами по себе являются индексными файлами. Файлы данных MyISAM и индексные файлы хранятся отдельно. Быстрому пониманию может помочь абстрактная схема того, как MyISAM и InnoDB хранят таблицы.
Распределение таблиц InnoDB (кластеризованное):
Распределение таблиц MyISAM (некластеризованных):
Ссылаться на
-
«Высокопроизводительный MySQL»
Рекомендуемое чтение
MySQL - Анализ плана выполнения SQL через EXPLAIN
MySQL — оптимизация индексов на практике
Структура данных, лежащая в основе индексов базы данных
Что влияет на выбор индекса базы данных?