MYSQL: ключевое слово Index в таблице создания и когда его использовать

Что означает ключевое слово index и какую функцию он обслуживает? Я понимаю, что он предназначен для ускорения запросов, но я не очень уверен, как это можно сделать.

Как выбрать индекс для индексирования?

Пример использования индекса показан ниже в запросе таблицы создания:

CREATE TABLE `blog_comment`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `blog_post_id` INTEGER,
    `author` VARCHAR(255),
    `email` VARCHAR(255),
    `body` TEXT,
    `created_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `blog_comment_FI_1` (`blog_post_id`),
    CONSTRAINT `blog_comment_FK_1`
        FOREIGN KEY (`blog_post_id`)
        REFERENCES `blog_post` (`id`)
)Type=MyISAM

;

Ответ 1

Я бы рекомендовал прочитать Как MySQL использует индексы из Справочник по MySQL Руководство. Он утверждает, что используются индексы...

Чтобы быстро найти строки, соответствующие предложению WHERE.
Чтобы устранить строки из соображений.
Чтобы получить строки из других таблиц при выполнении объединений.
Чтобы найти значение MIN() или MAX() для определенного индексированного столбца.
Сортировка или группировка таблицы (при определенных условиях).
Чтобы оптимизировать запросы, используя только индексы, не обращаясь к строкам данных.

Индексы в базе данных работают как индекс в книге. Вы можете найти то, что ищете в книге быстрее, потому что индекс указан в алфавитном порядке. Вместо алфавитного списка MySQL использует B-tree для организации своих индексов, что быстрее для его целей (но потребуется намного больше времени для человек).

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

В вашем примере столбцы id и blog_post_id используют индексы (PRIMARY KEY также индекс), чтобы приложение могло быстрее их находить. В случае id, вероятно, это позволяет пользователям быстро или быстро удалять комментарий, а в случае blog_post_id, поэтому приложение может быстро найти все комментарии для данного сообщения.

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

Ответ 2

Это ключевое слово означает, что вы создаете индекс в столбце blog_post_id вместе с таблицей.

Подобные запросы:

SELECT *
FROM blog_comment
WHERE blog_post_id = @id

будет использовать этот индекс для поиска в этом поле и запускаться быстрее.

Кроме того, в этом столбце есть foreign key.

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

DELETE
FROM blog_post
WHERE ...

также будет работать быстрее.