Что касается содержимого, связанного с индексами MySQL, это всегда было головной болью, особенно для новичков. Автор долго этим увлекался, не в силах различить«Покрывающий индекс, вспомогательный индекс, уникальный индекс, хэш-индекс, индекс B-Tree...» Что это такое?, что приводит к довольно неловкой ситуации в процессе собеседования.
Многие люди могут жаловаться"На собеседовании ракету строишь, на работе гайку закручиваешь, к собеседованию узнаешь много знаний, а на работе вообще не используешь!". К счастью, индексы в MySQL - это не только необходимые знания для собеседований, но и наиболее часто используемые необходимые навыки в работе. По мнению автора, индексы - этоСамая экономичная часть MySQL.
Поскольку MySQL поддерживает несколько механизмов хранения, существуют небольшие различия в реализации разных механизмов хранения.Если в следующем указателе нет специального оператора, значение по умолчанию относится к механизму хранения InnoDB.
Во-первых, базовая структура данных
первый,Индекс — это структура данных для эффективного извлечения данных.. Подобно каталогу в книге, мы можем быстро определить местонахождение данных, тем самым повысив эффективность запросов к данным.
В MySQL есть много существительных и понятий об индексе, в которых новичкам легко запутаться. Чтобы облегчить понимание, я составил таблицу, пытаясь узнать, что это за понятия в конкретном случае.
Хэш-индекс
Как упоминалось выше, индекс — это структура данных, повышающая эффективность запроса, и существует множество структур данных, способных повысить эффективность запроса, например бинарное дерево поиска, красно-черное дерево, таблица переходов, хеш-таблица (хеш-таблица) и т. д. в то время как MySQL B + Tree и хеш-таблица (Hash table) используются в качестве базовой структуры данных индекса.
Обратите внимание, что MySQLХэш-индекс явно не поддерживается, но в качестве внутренней оптимизации хэш-индекс будет автоматически генерироваться для горячих данных, также называемый адаптивным хэш-индексом..
Хэш-индекс может найти данные с временной сложностью O(1) в эквивалентном запросе, что очень эффективно, но не поддерживает запрос диапазона. Эта структура данных используется во многих языках программирования и базах данных, таких как структура данных Hash, поддерживаемая Redis. Конкретная структура выглядит следующим образом:
B+древовидный индекс
Когда дело доходит до B+Tree, мы должны в первую очередь упомянутьB-Tree, B-Tree (многоходовое дерево поиска, а не бинарное) — обычная структура данных. Использование структуры B-дерева может значительно сократить промежуточный процесс поиска записей, тем самым ускорив доступ.
В+ деревоЭто модернизированная древовидная структура данных на основе B-Tree, которая обычно используется в файловой системе базы данных и операционной системы. Характеристика дерева B+ заключается в том, что оно может поддерживать стабильность и упорядоченность данных, а его вставка и модификация имеют относительно стабильную логарифмическую временную сложность. Элементы дерева B+ вставляются снизу вверх, что является полной противоположностью двоичному дереву.
Реализация индексов MySQL также основана на этой эффективной структуре данных. Конкретная структура данных выглядит следующим образом:
Прежде всего, я хотел бы заявить, что не будуB, B-дерево и B+деревосмущать. Во-первых, B-Tree — это B-дерево, а «-» посередине — это прочерк, а не минус, и такой структуры данных, как «B-минус-дерево», не существует. Во-вторых, есть два различия между B+Tree и B-Tree при реализации индексов, как показано на следующем рисунке.
①B+Tree хранит данные только в листовых узлах, тогда как данные B-Tree хранятся в каждом узле.
② Листовые узлы B+Tree связаны указателями, и все данные могут быть получены путем обхода конечных узлов.
B+Tree — это волшебная структура данных. Она может быть немного трудоемкой с точки зрения языка. Заинтересованные студенты могут нажать на инструмент визуализации структуры данных в конце статьи. После некоторых операций они обязательно что-то получат. Следующий рисунок является авторской демонстрацией метода вставки данных B+ Tree (снизу вверх).
Во-вторых, способ организации данных
В зависимости от организации данных их можно разделить на кластеризованный индекс и некластеризованный индекс (также называемый кластеризованным индексом и некластеризованным индексом). Кластерный индекс предназначен для построения B+Tree в соответствии с первичным ключом каждой таблицы, и в то же времяЛистовой узел хранит данные записи строки всей таблицы..
в InnoDBКластерные и первичные ключевые индексыКонцепция аналогична MySQL предусматривает, что каждая таблица должна иметь индекс первичного ключа.Может быть только один индекс первичного ключа, который не может быть нулевым и должен обеспечивать уникальность.. Если индекс первичного ключа не указан при создании таблицы, скрытое поле будет автоматически сгенерировано в качестве индекса первичного ключа.
Соответствующий некластеризованный индекс,Некластеризованный индекс также можно назвать индексом без первичного ключа, вторичным индексом, вторичным индексом.. Листовые узлы индекса первичного ключа хранят полные строки данных, в то время какЛистовой узел индекса непервичного ключа хранит значение индекса первичного ключа., при запросе данных через индекс, не являющийся первичным ключом, сначала будет найден индекс первичного ключа, а затем соответствующие данные будут найдены в индексе первичного ключа.Этот процесс называетсяформа возврата(будет упоминаться снова ниже).
Нужно добавить, что файлы индексов и данных в Myisam хранятся отдельно, и все индексы являются некластеризованными индексами. B + Листовой узел дерева сохраняетсяАдрес, где хранятся данные, а не конкретные данные.
Три, содержит количество полей
Чтобы удовлетворить различные потребности в поиске данных, индекс может содержать только одно поле или несколько полей одновременно. Индекс, состоящий из одного поля, можно назвать однозначным индексом, иначе его называют составным индексом (или составным индексом, или многозначным индексом). Все приведенные выше демонстрации представляют собой однозначные индексы, поэтому давайте для сравнения покажем составные индексы.
Порядок данных индекса составного индекса связан с порядком полей.В индексе, содержащем несколько значений, если значения предыдущих полей повторяются, они будут отсортированы в соответствии со следующими значениями.
В-четвертых, другие категории
уникальный индекс
Уникальный индекс, который запрещает строки с одинаковым значением индекса, тем самым запрещая повторяющиеся значения индекса или ключа. Система проверяет наличие повторяющихся значений ключа при создании индекса и проверяет каждый раз, когда данные добавляются с помощью инструкции INSERT или UPDATE.Если есть повторяющиеся значения, операция завершается неудачно и выдается исключение.
Следует отметить, что индекс первичного ключа должен быть уникальным индексом, а уникальный индекс не обязательно является индексом первичного ключа.Уникальный индекс можно понимать как просто установку индекса на уникальный атрибут..
индекс покрытия
Концепция таблицы возврата упоминалась выше.При запросе данных через индекс, не являющийся первичным ключом, сначала запрашивается значение индекса первичного ключа, а затем запрашиваются конкретные данные в индексе первичного ключа. процесс должен просмотреть индекс дважды Очевидно, что возврат к таблице — это трудоемкая операция.
Чтобы уменьшить количество возвратов к таблице, при разработке индекса мы можемПусть индекс содержит результаты, которые нужно запросить, после извлечения данных из вспомогательного индекса вернуть их напрямую, без необходимости возвращать таблицу.
Однако следует отметить, что предпосылка использования покрывающего индекса заключается в том, что длина поля относительно мала. Использование покрывающего индекса для поля с большой длиной значения не подходит. На это есть много причин. Например, index обычно хранится в памяти. Может загружаться с диска, что влияет на производительность. Конечно, есть и другие причины, конкретные обстоятельства будут представлены в следующей статье.
6. Резюме
В этой статье представлены индексы в MySQL из разных измерений.Индексы могут иметь много имен из разных измерений, но необходимо прояснить один вопрос:Суть индекса — это структура данных, а деление остальных индексов носит практический характер. Конкретные категории показаны на рисунке ниже:
Цель состоит в том, чтобы дать каждому предварительное и четкое представление об индексе, решить проблемуWhatЭта проблема. ПослеWhyтак же какHow, для углубленного обсуждения, конечно, в первую очередь выделить концептуальные вопросы, затронутые в этой главе.
Инструмент визуализации структуры данных: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html.
7. Вопросы и ответы
1. Почему индексы MySQL используют реализацию B + Tree вместо поиска в бинарном дереве, красно-черном дереве или таблице переходов?
Это всеобъемлющая проблема, и друзья могут сделать гораздо больше, чем это может показаться простым.Напишите ответ в поле для комментариевДавайте исследуем вместе, и снова я сосредоточусь на том, почему и как правильно использовать индексы, в следующей статье.