Как работают индексы MySQL?

первое, что вы должны знать, это то, что индексы-это способ избежать сканирования полной таблицы для получения результата, который вы ищете.

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

InnoDB и индекс дерева B+

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

"проблема" с этим типом индекса заключается в том, что вам нужно запросить самое левое значение для использования индекса. Итак, если ваш индекс имеет два столбца, скажем 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";

по-прежнему стоит хэшировать вещи, даже если номер столкновения высок, потому что вы будете выполнять только второе сравнение (строковое) против повторных хэшей.

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

в основном индекс-это карта всех ваших ключей, которые отсортированы по порядку. Со списком в порядке, то вместо проверки каждого ключа, он может сделать что-то вроде этого:

1: Перейдите в середине списка - выше или ниже, чем то, что я ищу?

2: если выше, перейдите на полпути между серединой и дном, если ниже, середина и верх

3: выше или ниже? Перейти к средней точке снова и т. д.

используя эту логику, вы можете найти элемент в a сортировка списка примерно за 7 шагов, вместо проверки каждого элемента.

очевидно, что есть сложности, но это дает вам основную идею.

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

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

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

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

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

для создания индексов используется оператор CREATE INDEX. Ниже показан синтаксис CREATE INDEX заявление: Один Два 3

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],...)

во-первых, вы указываете индекс на основе типа таблицы или механизма хранения:

UNIQUE означает, что MySQL создаст ограничение, что все значения в индексе должны быть уникальными. Повторяющееся нулевое значение разрешено во всех подсистемах хранения, кроме базы данных BDB. Полнотекстовый индекс поддерживается только MyISAM storage engine и принимается только в столбце с типом данных CHAR, VARCHAR или TEXT. Пространственный индекс поддерживает пространственный столбец и доступен на MyISAM подсистема хранилища. Кроме того, значение столбца не должно быть NULL.

затем вы называете индекс и его тип после ключевого слова USING, такого как BTREE, HASH или RTREE, также основанного на механизме хранения таблицы.

вот механизмы хранения таблицы с соответствующими разрешенными типами индексов: Допустимые Типы Индексов Механизма Хранения MyISAM BTREE, RTREE InnoDB BTREE ПАМЯТЬ/КУЧИ ХЭШ, ДЕРЕВО NDB HASH

в-третьих, вы объявляете имя таблицы и столбцы списка вы хотите добавить в индекс. Пример создания индекса в MySQL

в образце базы данных можно добавить столбец officeCode таблицы employees в индекс с помощью инструкции CREATE INDEX следующим образом: 1

CREATE INDEX officeCode ON employees(officeCode)

Удаление Индексов

помимо создания индекса, вы также можете удалить индекс с помощью инструкции DROP INDEX. Интересно, что оператор DROP INDEX также сопоставляется с оператором ALTER TABLE. Ниже приведен синтаксис удаление индекса: 1

DROP INDEX index_name ON table_name

например, если вы хотите удалить индекс officeCode таблицы employees, которую мы создали выше, вы можете выполнить следующий запрос: 1

DROP INDEX officeCode ON employees

принимать по этой видео для получения более подробной информации об индексации

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

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

для создания индекса можно использовать один или несколько столбцов. Например, мы можем создать индекс tutorials_tbl С помощью tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

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

если вы хотите индексировать значения в столбце в порядке убывания, вы можете добавить зарезервированное слово 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

как они работают-это слишком широкая тема, чтобы охватить ее в одном сообщении SO.

здесь это одно из лучших объяснений индексов, которые я видел. К сожалению, это для SQL Server, а не MySQL. Я не уверен, насколько они похожи...