После определения индекса MySQL выполнит оставшуюся часть работы? [Дубликат]

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

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

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)

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

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

InnoDB и индекс B + Tree

Для 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-Tree, создав другой столбец и сохранив хэш большого значения на нем. Предположим, вы храните 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";

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

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

Завершить

Некоторые факты, которые вы можете рассмотреть каждый раз, когда хотите поговорить об оптимизации:

1. Целочисленное сравнение намного быстрее, чем сравнение строк. Это можно проиллюстрировать на примере эмуляции хэш-индекса в InnoDB.
2. Возможно, добавление дополнительных шагов в процесс делает его быстрее, а не медленнее. Это можно проиллюстрировать тем фактом, что вы можете оптимизировать SELECT, разделив его на два шага, сделав первые значения хранилища во вновь созданной таблице в памяти, а затем выполните более тяжелые запросы в этой второй таблице.

У MySQL также есть и другие индексы, но я думаю, что B + Tree один из самых используемых когда-либо, а хэш - хорошая вещь, но вы можете найти другие в Документация по MySQL .

Я настоятельно рекомендую вам прочитать книгу «Высокая производительность MySQL», ответ выше определенно основывался на ее главе об индексах.

Взгляните на эту ссылку: http://dev.mysql.com/doc/refman/5.0/ru/mysql-indexes.html

Как они работают слишком широк для объекта для покрытия в одном сообщении SO.

Здесь является одним из лучших объяснений индексов, которые я видел. К сожалению, это для SQL Server, а не для 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 поддерживает пространственный столбец и доступен для механизма хранения MyISAM. Кроме того, значение столбца не должно быть NULL.

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

Ниже приведены механизмы хранения таблицы с соответствующими разрешенными типами индексов: Поддерживаемые типы индексов хранения MyISAM BTREE, RTREE InnoDB BTREE MEMORY / HEAP HASH, BTREE NDB HASH

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

В примерной базе данных вы можете добавить столбец OfficeCode таблицы employee в индекс с помощью инструкции 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