Как работают индексы MySQL?
Я действительно заинтересован в том, как работают индексы MySQL, в частности, как они могут возвращать запрошенные данные без сканирования всей таблицы?
Это не по теме, я знаю, но если бы кто-то мог объяснить мне это подробно, я был бы очень, очень благодарен.
11 ответов
В основном индекс таблицы работает как индекс в книге (отсюда и название):
Допустим, у вас есть книга о базах данных, и вы хотите найти некоторую информацию, скажем, о хранилище. Без индекса (при условии отсутствия другой помощи, такой как оглавление), вам придется просматривать страницы по одной, пока вы не найдете тему (это full table scan). С другой стороны, в индексе есть список ключевых слов, поэтому вы должны просмотреть его и увидеть, что storage упоминается на страницах 113-120,231 и 354. Затем вы можете переходить на эти страницы напрямую, без поиска (это поиск с индексом, несколько быстрее).
Конечно, насколько полезным будет индекс, зависит от многих вещей - несколько примеров, используя приведенное выше сравнение:
- если бы у вас была книга о базах данных и было проиндексировано слово "база данных", вы бы увидели, что она упоминается на страницах 1–59,61–290 и 292–400. В этом случае индекс не очень полезен, и он может Быстрее проходить страницы по очереди (в базе данных это "плохая избирательность").
- Для 10-страничной книги не имеет смысла создавать индекс, поскольку в итоге вы можете получить 10-страничную книгу с префиксом 5-страничного индекса, что просто глупо - просто отсканируйте 10 страниц и покончите с этим,
- Индекс также должен быть полезен - обычно нет смысла индексировать, например, частоту буквы "L" на странице.
Первое, что вы должны знать, это то, что индексы - это способ избежать сканирования всей таблицы, чтобы получить результат, который вы ищете.
Существуют разные виды индексов, и они реализованы на уровне хранилища, поэтому между ними нет стандарта, и они также зависят от используемого вами механизма хранилища.
InnoDB и индекс дерева B +
Для InnoDB наиболее распространенным типом индекса является индекс на основе B+Tree, в котором элементы хранятся в отсортированном порядке. Кроме того, вам не нужно обращаться к реальной таблице, чтобы получить индексированные значения, что ускоряет возврат вашего запроса.
"Проблема" в этом типе индекса заключается в том, что вы должны запросить крайнее левое значение, чтобы использовать индекс. Итак, если в вашем индексе есть два столбца, скажем, last_name и first_name, порядок, в котором вы запрашиваете эти поля, имеет большое значение.
Итак, с учетом следующей таблицы:
CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);
Этот запрос будет использовать индекс:
SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"
Но следующий не будет
SELECT last_name, first_name FROM person WHERE first_name = "Constantine"
Потому что вы запрашиваете first_name первый столбец, и это не самый левый столбец в индексе.
Этот последний пример еще хуже:
SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"
Потому что теперь вы сравниваете самую правую часть самого правого поля в индексе.
Индекс хеша
Это другой тип индекса, который, к сожалению, поддерживает только серверная часть памяти. Это молниеносно, но полезно только для полного поиска, что означает, что вы не можете использовать его для таких операций, как >, < или же LIKE,
Так как он работает только для памяти, вы, вероятно, не будете использовать его очень часто. Основной случай, о котором я могу подумать сейчас, - это то, что вы создаете временную таблицу в памяти с набором результатов из другого выбора и выполняете много других выборов в этой временной таблице, используя хэш-индексы.
Если у вас есть большой VARCHAR поле, вы можете "эмулировать" использование хеш-индекса при использовании B-дерева, создав другой столбец и сохранив на нем хеш большого значения. Допустим, вы храните URL-адрес в поле, а значения довольно большие. Вы также можете создать целочисленное поле с именем url_hash и использовать хэш-функцию, такую как CRC32 или любая другая хеш-функция для хеширования URL при вставке. И затем, когда вам нужно запросить это значение, вы можете сделать что-то вроде этого:
SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");
Проблема с приведенным выше примером заключается в том, что, поскольку CRC32 Функция генерирует довольно маленький хеш, в результате вы получите множество коллизий в хеш-значениях. Если вам нужны точные значения, вы можете решить эту проблему, выполнив следующие действия:
SELECT url FROM url_table
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";
Все еще стоит хэшировать вещи, даже если число коллизий велико, потому что вы будете выполнять только второе сравнение (строковое) с повторяющимися хешами.
К сожалению, используя эту технику, вам все равно нужно попасть в таблицу, чтобы сравнить url поле.
Заворачивать
Некоторые факты, которые вы можете учитывать каждый раз, когда хотите поговорить об оптимизации:
Целочисленное сравнение намного быстрее, чем сравнение строк. Это можно проиллюстрировать на примере об эмуляции хеш-индекса в
InnoDB,Возможно, добавление дополнительных шагов в процесс делает его быстрее, а не медленнее. Это можно проиллюстрировать тем, что вы можете оптимизировать
SELECTразделив его на два этапа, сделав первый, сохраните значения во вновь созданной таблице в памяти, а затем выполните более тяжелые запросы для этой второй таблицы.
В MySQL есть и другие индексы, но я думаю, что B+Tree один из наиболее используемых когда-либо, и хэш-это полезно знать, но другие можно найти в документации MySQL.
Я настоятельно рекомендую вам прочитать книгу "High Performance MySQL", ответ на которую был определенно основан на главе об индексах.
По сути, индекс - это карта всех ваших ключей, отсортированная по порядку. Имея список по порядку, вместо проверки каждого ключа он может сделать что-то вроде этого:
1: Перейти к середине списка - выше или ниже того, что я ищу?
2: Если выше, перейдите к половине пути между серединой и дном, если ниже, посередине и сверху
3: выше или ниже? Снова перейти к средней точке и т. Д.
Используя эту логику, вы можете найти элемент в отсортированном списке примерно за 7 шагов, вместо проверки каждого элемента.
Очевидно, что есть сложности, но это дает вам основную идею.
Индекс базы данных, или просто индекс, помогает ускорить извлечение данных из таблиц. Когда вы запрашиваете данные из таблицы, сначала MySQL проверяет, существуют ли индексы, затем MySQL использует индексы для выбора точных физических соответствующих строк таблицы вместо сканирования всей таблицы.
Индекс базы данных аналогичен индексу книги. Если вы хотите найти тему, вы сначала просматриваете индекс, а затем открываете страницу с темой, не сканируя всю книгу.
Настоятельно рекомендуется создать индекс по столбцам таблицы, из которой вы часто запрашиваете данные. Обратите внимание, что все столбцы первичного ключа автоматически включаются в первичный индекс таблицы.
Если индекс помогает ускорить запрос данных, почему бы нам не использовать индексы для всех столбцов? Если вы создаете индекс для каждого столбца, MySQL должен создать и поддерживать таблицу индексов. Всякий раз, когда вносятся изменения в записи таблицы, MySQL должен перестраивать индекс, что требует времени, а также снижает производительность сервера базы данных. Создание индекса MySQL
Вы часто создаете индексы, когда создаете таблицы. MySQL автоматически добавляет в индекс любой столбец, объявленный как PRIMARY KEY, KEY, UNIQUE или INDEX. Кроме того, вы можете добавить индексы к таблицам, в которых уже есть данные.
Для создания индексов вы используете оператор CREATE INDEX. Ниже показан синтаксис оператора CREATE INDEX: 1 2 3
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],...)
Сначала вы указываете индекс на основе типа таблицы или механизма хранения:
UNIQUE означает, что MySQL создаст ограничение, согласно которому все значения в индексе должны быть уникальными. Дублирующее значение NULL допускается во всех механизмах хранения, кроме BDB. Индекс FULLTEXT поддерживается только механизмом хранения MyISAM и принимается только для столбца с типом данных CHAR, VARCHAR или TEXT. SPATIAL index поддерживает пространственный столбец и доступен в подсистеме хранения MyISAM. Кроме того, значение столбца не должно быть NULL.
Затем вы называете индекс и его тип после ключевого слова USING, такого как BTREE, HASH или RTREE, также в зависимости от механизма хранения таблицы.
Вот механизмы хранения таблицы с соответствующими разрешенными типами индексов: допустимые типы индексов механизма хранения MyISAM BTREE, RTREE InnoDB BTREE MEMORY/HEAP HASH, BTREE NDB HASH
В-третьих, вы объявляете имя таблицы и столбцы списка, которые хотите добавить в индекс. Пример создания индекса в MySQL
В примере базы данных вы можете добавить столбец officeCode таблицы сотрудников в индекс с помощью инструкции CREATE INDEX следующим образом: 1
CREATE INDEX officeCode ON employees(officeCode)
Удаление индексов
Помимо создания индекса, вы также можете удалить индекс с помощью оператора DROP INDEX. Интересно, что оператор DROP INDEX также сопоставлен с оператором ALTER TABLE. Ниже приведен синтаксис удаления индекса: 1
DROP INDEX index_name ON table_name
Например, если вы хотите удалить индекс officeCode таблицы сотрудников, которую мы создали выше, вы можете выполнить следующий запрос: 1
DROP INDEX officeCode ON employees
В MySQL InnoDB есть два типа индекса.
Первичный ключ, который называется кластеризованным индексом. Ключевые слова индекса хранятся с реальными данными записи в листовом узле B+Tree.
Вторичный ключ, который не является кластеризованным индексом. Эти индексы хранят только ключевые слова первичного ключа вместе со своими собственными ключевыми словами индекса в листовом узле B+Tree. Поэтому при поиске по вторичному индексу он сначала находит ключевые слова индекса первичного ключа и сканирует первичный ключ B+Tree, чтобы найти записи реальных данных. Это замедлит вторичный индекс по сравнению с поиском первичного индекса. Однако если
selectвсе столбцы находятся во вторичном индексе, поэтому нет необходимости снова искать первичный индекс B+Tree. Это называется индексом покрытия.
Возьмите это видео для более подробной информации об индексировании
Простое индексирование Вы можете создать уникальный индекс для таблицы. Уникальный индекс означает, что две строки не могут иметь одинаковое значение индекса. Вот синтаксис для создания индекса на таблице
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);
Вы можете использовать один или несколько столбцов для создания индекса. Например, мы можем создать индекс tutorials_tbl используя tutorial_author.
CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)
Вы можете создать простой индекс для таблицы. Просто пропустите ключевое слово UNIQUE из запроса, чтобы создать простой индекс. Простой индекс позволяет дублировать значения в таблице.
Если вы хотите проиндексировать значения в столбце в порядке убывания, вы можете добавить зарезервированное слово DESC после имени столбца.
mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)
Взгляните на эту ссылку: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
То, как они работают, слишком широко, чтобы освещать это в одном посте.
Вот одно из лучших объяснений индексов, которые я видел. К сожалению, это для SQL Server, а не MySQL. Я не уверен, насколько похожи эти два...
Добавление визуального представления в список ответов. 
MySQL использует дополнительный уровень косвенности: записи вторичного индекса указывают на записи первичного индекса, а сам первичный индекс содержит расположения строк на диске. Если смещение строки изменяется, необходимо обновить только первичный индекс.
Предостережение: структура данных диска выглядит плоской на диаграмме, но на самом деле представляет собой дерево B+.
Источник: ссылка
Я хочу добавить свои 2 цента. Я далек от того, чтобы быть экспертом по базам данных, но недавно я немного прочитал эту тему; достаточно для меня, чтобы попытаться дать ELI5. Итак, вот объяснение для неспециалистов.
Я так понимаю, что индекс подобен мини-зеркалу вашей таблицы, почти как ассоциативный массив. Если вы передадите ему соответствующий ключ, вы можете просто перейти к этой строке в одной "команде".
Но если у вас не было этого индекса / массива, интерпретатор запросов должен использовать цикл for, чтобы пройти по всем строкам и проверить совпадение (сканирование полной таблицы).
Наличие индекса имеет "обратную сторону" дополнительного хранилища (для этого мини-зеркала) в обмен на "обратную сторону" поиска контента быстрее.
Обратите внимание, что (в зависимости от вашего движка БД) создание первичных, внешних или уникальных ключей автоматически устанавливает соответствующий индекс. Тот же принцип в основном, почему и как эти ключи работают.
Предположим, у вас есть книга, возможно, роман, толстая, в которой много чего нужно прочитать, а значит, и слов. Теперь, гипотетически, вы принесли два словаря, состоящие только из слов, которые используются в романе хотя бы один раз. Все слова в этих двух словарях хранятся в обычном алфавитном порядке. В гипотетическом словаре A слова печатаются только один раз, в то время как в гипотетическом словаре B слова печатаются столько раз, сколько они напечатаны в романе.. Помните, что слова в обоих словарях отсортированы по алфавиту. Теперь вы застряли в какой-то момент при чтении романа и вам нужно найти значение этого слова в любом из этих гипотетических словарей. Что ты будешь делать? Несомненно, вы перескочите к этому слову за несколько шагов, чтобы найти его значение, лучше ищите значение каждого слова в романе, от начала, до того момента, пока вы не дойдете до этого подслушивающего слова.
Так работает индекс в SQL. Рассмотрите Словарь A как ПЕРВИЧНЫЙ ИНДЕКС, Словарь B как КЛЮЧЕВОЙ / ВТОРИЧНЫЙ ИНДЕКС, а ваше желание узнать значение слова как ЗАПРОС / ВЫБОР ЗАЯВЛЕНИЯ. Индекс поможет получать данные с очень высокой скоростью. Без индекса вам придется искать данные с самого начала, излишне трудоемкой и дорогостоящей задачи.
Более подробной информации о индексах и типах, смотрите это.
Индексы используются для быстрого поиска строк с определенными значениями столбца. Без индекса MySQL должен начать с первой строки, а затем прочитать всю таблицу, чтобы найти соответствующие строки. Чем больше стол, тем больше это стоит. Если в таблице есть индекс для рассматриваемых столбцов, MySQL может быстро определить позицию для поиска в середине файла данных, не просматривая все данные. Это намного быстрее, чем последовательное чтение каждой строки.
Индексация добавляет структуру данных со столбцами для условий поиска и указателем
Указатель — это адрес на диске памяти строки с остальной
информациейСтруктура данных индекса отсортирована для оптимизации эффективности запросов.
Запрос ищет определенную строку в индексе; индекс относится к указателю, который найдет остальную информацию.
Индекс уменьшает количество строк, которые должен искать запрос, с 17 до 4.