Что такое индекс?
«Индекс» должен иметь возможность быстрее запрашивать данные. Например, оглавление книги — это указатель содержания книги. Читатели могут быстро найти нужное содержание в оглавлении, а затем найти определенные главы по номеру страницы.
То же самое верно и для базы данных.Если оператор запроса использует индекс, он сначала обращается к индексу для запроса, получает физический адрес строки, в которой расположены данные, а затем обращается к данным.
Преимущества и недостатки индексов
Преимущества: быстрый поиск, сокращение времени ввода-вывода и ускорение поиска, группировка и сортировка по индексам могут ускорить группировку и сортировку;
Недостаток: индексы сами по себе являются таблицами, поэтому они занимают место для хранения. Обслуживание и создание индексов требует временных затрат, и эти затраты увеличиваются с увеличением объема данных; построение индекса снизит эффективность операций модификации (удаления, добавления, модификации) таблицы данных, поскольку при изменении данных таблица, она также должна быть изменена диаграммы направленности.
Классификация индексов
В MySQL распространенными типами индексов являются: индекс первичного ключа, уникальный индекс, общий индекс, полнотекстовый индекс и составной индекс. Синтаксис создания:
Среди них комбинированный индекс также называется индексом с несколькими столбцами Последний пример в приведенном выше коде — создание индекса с 3 столбцами. Когда MySQL запрашивает по индексу, он будет следовать "Крайний левый матч"принцип, то есть сначала согласноcol1условиях, а затем в соответствии сcol2условиях, а затем на основеcol3условия для проверки.
Если вы пропустите столбец и будете искать следующие столбцы напрямую, например следующий оператор, вы не сможете использовать индекс, созданный выше:
Вот небольшая хитрость: если ваш предыдущий столбец представляет собой простой тип перечисления, например пол и т. д., вы можете использовать оператор where, чтобы добавить столбец col1 в (MALE, FEMALE), чтобы «пропустить» столбец col1 и использовать указанный выше индекс.
Если столбец представляет собой строку и является относительно длинным (например, UUID), рекомендуется использовать префиксный индекс, то есть совпадать с первыми n символами. Конкретное значение n зависит от ваших данных. «Высокопроизводительный MySQL» предлагает хитрость: с помощьюLEFTДля функционального запроса начните с 1 и непрерывно увеличивайте значение n, пока количество строк результата запроса не приблизится к количеству строк результата запроса полного столбца, что является подходящим значением n.
Как работает индекс
Индексы MySQL создаютсямеханизм хранениябыть реализованным.由于存储引擎不同，所以具有不同的索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等。这里由于主要介绍BTree索引和B+Tree索引，我们平时使用最多的InnoDB引擎就是基于B+Tree索引的。
Текущая версия движка MySQL InnoDB уже поддерживает полнотекстовое индексирование.Начиная с MySQL 5.7.6, MySQL имеет встроенный полнотекстовый синтаксический анализатор ngram для поддержки сегментации слов на китайском, японском и корейском языках.
Начните с бинарного дерева поиска
Друзья, которые узнали о структурах данных, должны знать структуру данных, называемую бинарным деревом. В зависимости от использования бинарные деревья имеют разные варианты, такие как кучи, такие как бинарные деревья поиска.
В бинарном дереве поиска, чтобы экстремальная высота дерева не влияла на эффективность запроса, выводятся некоторые сбалансированные бинарные деревья поиска, наиболее типичными из которых являются AVL и красно-черные деревья.
Однако, когда объем данных велик, глубина бинарного дерева слишком велика, что не подходит для запроса базы данных, поэтому база данных использует дерево с несколькими ответвлениями.
BTree
BTree (также известный как BTree) — этоСбалансированный поиск по нескольким деревьям. Структура BTree выглядит следующим образом:
Если предположить, что степень дерева равна 2d (d>1), а высота равна h, то BTree обладает следующими свойствами:
- Высота каждого листового узла одинакова, равна h;
- Каждый нелистовой узел состоит из n-1 ключей и n указателей, ключи и указатели изолированы друг от друга, и оба конца узла должны быть ключами;
- Указатель конечного узла имеет значение null;
- Ключами нелистовых узлов являются все кортежи [key, data], где ключ представляет собой ключ, используемый в качестве индекса, а данные — это данные других столбцов в строке, где находится значение ключа;
В BTree индексные столбцы хранятся последовательно, поэтому он очень удобен для поиска данных диапазона иORDER BYработать.
B+Tree
B+Tree — это вариант BTree. Основное различие между B+Tree и BTree заключается в следующем:
- Неконечные узлы в B+Tree не хранят данные, только ключевые значения;
- Листовой узел B+Tree не имеет указателя, все значения ключа будут отображаться на листовом узле, а значение ключа, хранящееся в ключе, соответствует физическому адресу данных данных;
- Каждый нелистовой узел B+Tree состоит из n ключей и n указателей;
Структурная схема:
Преимущества B+Tree по сравнению с BTree:
Вообще говоря, B+Tree больше подходит для реализации индексной структуры внешней памяти, чем BTree, потому что специалисты по проектированию подсистемы хранения разумно используют структуру хранения внешней памяти (диска).
Минимальной единицей хранения диска является сектор (sector), а блок (block) операционной системы обычно является целым кратным сектору.Операционная система управляет памятью в единицах страниц (pages), а страница ( страница) обычно по умолчанию имеет размер 4 КБ. Базы данных Страница узла обычно устанавливается на целое число, кратное странице операционной системы, поэтому узел структуры индекса рассчитан на размер страницы, а затем «предварительно прочитанное "используется принцип внешней памяти, и каждый раз, когда она читается, считываются данные всего узла. Получить их в памяти и искать их в памяти.
Известно, что скорость чтения памяти в сотни раз выше, чем скорость чтения ввода-вывода внешней памяти, поэтому ключом к повышению скорости поиска является максимальное сокращение дискового ввода-вывода, тогда вы можете знать, что количество ключей в каждом узле Чем больше, чем меньше высота дерева, тем меньше требуется операций ввода-вывода, поэтому в целом B+Tree быстрее, чем BTree, потому что B+Tree не хранит данные в нелистовые узлы, он может хранить больше ключей.
B+дерево с последовательным индексом
Структуры B+Tree, обычно используемые в системах баз данных или файловых системах, оптимизированы на основе классических B+Tree с добавлением последовательных указателей доступа.
B+дерево с последовательными указателями доступа формируется путем добавления указателя на соседний конечный узел в каждом конечном узле B+дерева. Целью этой оптимизации является повышение производительности интервального доступа.Например, если вы хотите запросить все записи данных с ключами от 18 до 49, когда 18 найдено, вам нужно только пройти узлы и указатели, чтобы получить доступ все данные одновременно.Узлы не нужно запрашивать с нуля, что значительно повышает эффективность интервальных запросов.
Кластеризованные и некластеризованные индексы
Двумя наиболее распространенными механизмами хранения в MySQL являются MyISAM и InnoDB, которые реализуют некластеризованные и кластеризованные индексы соответственно.
Некоторое время назад я видел вопрос: «Знаете ли вы, почему индексы InnoDB, не являющиеся первичными ключами, обычно медленнее, чем индексы первичного ключа?» Ответ заключается в том, что InnoDB использует кластеризованные индексы, а индекс первичного ключа необходимо запрашивать один раз, в то время как индекс непервичного ключа необходимо запрашивать дважды.
Почему индексы, не являющиеся первичными ключами, необходимо запрашивать дважды? И смотрите, что следует.
Первичные и вторичные индексы
Во-первых, давайте познакомимся с основными понятиями. В классификации индекса мы можем разделить его на «первичный индекс» и «вспомогательный индекс» в зависимости от того, является ли ключ индекса первичным ключом или нет. Следовательно, может быть только один первичный индекс и может быть много вторичных индексов.
Зачем нужен вторичный индекс? Поскольку мы представили ранее, если оператор запроса хочет использовать индекс, он должен соответствоватьКрайний левый матчпринципиальный. Иногда наш запрос не использует столбец первичного ключа, поэтому нам нужно построить индексы по другим столбцам, то есть вспомогательные индексы.
некластеризованный индекс
Первичный и вторичный индексы некластеризованного индекса почти одинаковы, за исключением того, что первичный индекс не допускает дубликатов или нулевых значений, а все ключи их конечных узлов хранят физические адреса, указывающие на данные, соответствующие ключу. ценности.
Таблица данных и индексная таблица некластеризованного индекса хранятся отдельно. Данные в некластеризованном индексе хранятся в соответствии с порядком вставки данных. Так что некластеризованный индекс больше подходитодиночный запрос данных. На порядок размещения не влияют ключевые значения.
кластеризованный индекс
Листовой узел первичного индекса кластеризованного индекса хранит данные, соответствующие самому значению ключа, а листовой узел вспомогательного индекса хранитПервичный ключ данных, соответствующий значению ключаключевое значение. Следовательно, чем меньше длина значения первичного ключа, тем лучше, а чем проще тип, тем лучше.
Данные кластеризованного индекса хранятся вместе с индексом первичного ключа.
Данные кластеризованного индекса хранятся в соответствии с порядком первичного ключа. Следовательно, он подходит для интервального поиска, индексируемого по первичному ключу, который может иметь меньше дисковых операций ввода-вывода и ускорить запрос. Но и по этой причине порядок вставки кластеризованного индекса лучше всего вставлять в монотонный порядок первичного ключа, иначе это будет вызывать частые разбиения страниц (операция при вставке BTree), что серьезно скажется на производительности.
В InnoDB, если вам нужно найти только индексированный столбец, старайтесь не добавлять другие столбцы, что повысит эффективность запроса.
Диаграмма, иллюстрирующая разницу между кластеризованным индексом и некластеризованным индексом: