Что такое индекс?
Индексы в MySQL упорядоченно сортируют данные. Они создаются в столбцах, которые будут использоваться для фильтрации данных. Думайте об индексе как об отсортированном по алфавиту списке. Искать имена, которые были отсортированы в алфавитном порядке, легче, чем имена, которые не отсортированы.
Использование индекса для часто обновляемых таблиц может привести к снижению производительности. Это связано с тем, что MySQL создает новый индексный блок каждый раз, когда данные добавляются или обновляются в таблице. Как правило, индексы следует использовать в таблицах, данные которых не часто меняются, но часто используются в выбранных поисковых запросах.
Что использовать индекс?
Никто не любит медленные системы. Высокая производительность системы имеет первостепенное значение почти для всех систем баз данных. Большинство предприятий вкладывают значительные средства в оборудование, чтобы ускорить извлечение данных и манипуляции с ними. Но вложения в оборудование для бизнеса ограничены. Оптимизация базы данных - более дешевое и лучшее решение.
Медленное время отклика обычно связано с тем, что записи хранятся в таблицах базы данных случайным образом. Поисковые запросы должны перебирать все случайно сохраненные записи одну за другой, чтобы найти нужные данные. Это приводит к низкой производительности баз данных при извлечении данных из больших таблиц. Следовательно, используются индексы, которые сортируют данные, чтобы упростить поиск.
Синтаксис: Создать индекс
Индексы могут быть определены двумя способами
- Во время создания таблицы
- После создания таблицы
Для нашего myflixdb мы ожидаем большого количества поисков в базе данных по полному имени.
Мы добавим столбец «full_names» в индекс в новой таблице «members_indexed».
Показанный ниже сценарий помогает нам в этом.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Выполните приведенный выше сценарий SQL в рабочей среде MySQL для «myflixdb».
При обновлении myflixdb отображается вновь созданная таблица с именем members_indexed.
Таблица "members_indexed" имеет "full_names" в узле индексов.
По мере расширения базы членов и увеличения количества записей поисковые запросы к таблице members_indexed, использующие предложения WHERE и ORDER BY, будут намного быстрее по сравнению с запросами, выполненными для таблицы members без определенного индекса.
Добавить базовый синтаксис индекса
В приведенном выше примере индекс создавался при определении таблицы базы данных. Предположим, у нас уже есть определенная таблица, и поисковые запросы по ней очень медленные. Они слишком долго возвращают результаты. После исследования проблемы мы обнаруживаем, что можем значительно улучшить производительность системы, создав ИНДЕКС в наиболее часто используемом столбце в предложении WHERE.
Мы можем использовать следующий запрос для добавления индекса
CREATE INDEX id_index ON table_name(column_name);
Предположим, что поисковые запросы в таблице фильмов очень медленные, и мы хотим использовать индекс для «названия фильма», чтобы ускорить запросы, мы можем использовать следующий скрипт для этого.
CREATE INDEX `title_index` ON `movies`(`title`);
Выполнение вышеуказанного запроса создает индекс в поле заголовка в таблице фильмов.
Это означает, что все поисковые запросы в таблице фильмов с использованием «заголовка» будут выполняться быстрее.
Однако поисковые запросы по другим полям в таблице фильмов по-прежнему будут медленнее по сравнению с запросами, основанными на индексированном поле.
Примечание. При необходимости вы можете создать индексы для нескольких столбцов в зависимости от полей, которые вы собираетесь использовать для своей поисковой системы по базе данных.
Если вы хотите просмотреть индексы, определенные для конкретной таблицы, для этого можно использовать следующий сценарий.
SHOW INDEXES FROM table_name;
Теперь давайте посмотрим на все индексы, определенные в таблице фильмов в myflixdb.
SHOW INDEXES FROM `movies`;
Выполнение вышеуказанного сценария в рабочей среде MySQL для myflixdb дает нам результаты по созданному индексу.
Примечание . Первичный и внешний ключи в таблице уже проиндексированы MySQL. Каждый индекс имеет собственное уникальное имя, также отображается столбец, в котором он определен.
Синтаксис: Удалить индекс
Команда drop используется для удаления уже определенных индексов в таблице.
Могут быть случаи, когда вы уже определили индекс для часто обновляемой таблицы. Вы можете удалить индексы в такой таблице, чтобы повысить производительность запросов UPDATE и INSERT. Базовый синтаксис, используемый для удаления индекса в таблице, следующий.
DROP INDEX `index_id` ON `table_name`;
Давайте теперь посмотрим на практический пример.
DROP INDEX ` full_names` ON `members_indexed`;
Выполнение указанной выше команды удаляет индекс с идентификатором full_names из таблицы members_indexed.
Резюме
- Индексы очень эффективны, когда дело доходит до значительного повышения производительности поисковых запросов MySQL.
- Индексы могут быть определены при создании таблицы или добавлены позже, когда таблица уже была создана.
- Вы можете определить индексы более чем для одного столбца в таблице.
- SHOW INDEX FROM table_name используется для отображения определенных индексов в таблице.
- Команда DROP используется для удаления определенного индекса в данной таблице.