Создание индекса в MySQL: учебник по индексу MySQL [2022]
Опубликовано: 2021-01-01Data Scientist или программист должен будет работать с большими объемами данных и должен иметь возможность эффективно с ними работать для более быстрого выполнения. Им также необходимо знать, как организованы данные, и какие самые быстрые методы легко получить доступ к конкретным необходимым данным.
Поскольку MySQL — это система управления реляционными базами данных, которая имеет приложения в различных секторах, таких как веб-базы данных, приложения для электронной коммерции, социальные сети и т. д., специалистам по данным необходимо знать все методы, связанные с MySQL, одним из которых являются индексы. В статье описывается, как создать индекс в MySQL.
Оглавление
Что такое индекс?
В MySQL индексы используются для упорядоченной последовательной сортировки данных и эффективного и быстрого извлечения данных. Они создаются с использованием одного или нескольких столбцов, которые будут использоваться для фильтрации данных. Индексы — это тип таблицы, в которой хранится первичное или индексное поле и указатель на каждую запись в основной таблице.
Для лучшего понимания в качестве примера можно использовать указатель книг. Вместо того, чтобы искать страницу, просматривая все страницы в книге, можно проверить индекс книги, чтобы найти нужную страницу.
В большинстве систем баз данных требуется высокая и быстрая производительность. Многие предприятия вкладывают огромные суммы денег в аппаратное обеспечение для более быстрого поиска и обработки данных. Но за счет оптимизации базы данных затраты можно снизить.
Время отклика обычно больше, поскольку записи хранятся случайным образом, а поисковым запросам приходится находить нужные данные путем перебора всех сохраненных записей. Индексы делают этот поиск проще и быстрее.
Чтение: переименовать имя столбца в SQL
Создать индекс
Вы можете создать индекс в MySQL для таблицы, используя следующий базовый синтаксис.
CREATE INDEX имя_индекса имя_таблицы (имя_столбца);
Вы также можете создать уникальный индекс для таблицы. Это означает, что имя уникально, и никакие две строки не могут иметь одинаковое имя. Для этого можно использовать следующий синтаксис.
CREATE UNIQUE INDEX index_name ON table_name (column_name);
Если создается вторичный индекс для столбца, MySQL сохраняет значения в отдельной структуре данных, такой как B-Tree и Hash. В MySQL, если столбцы являются строковыми столбцами, вы можете создать индекс для ведущей части значений столбца строкового столбца, используя синтаксис:
имя_столбца (длина)
Чтобы создать часть ключа префикса столбца при создании таблицы, можно использовать следующий синтаксис:
СОЗДАТЬ ТАБЛИЦУ table_name(
столбец_список,
ИНДЕКС(имя_столбца(длина))
);
И чтобы добавить индекс к существующей таблице:
CREATE INDEX index_name ON имя_таблицы (имя_столбца (длина));
Длина здесь — это количество символов, если это недвоичный строковый тип, такой как CHAR, VARCHAR и TEXT, и количество байтов, если это двоичный строковый тип, такой как BINARY, VARBINARY и BLOB. Вы также можете создать ключевые части префикса столбца для столбцов CHAR, VARCHAR, BINARY и VARBINARY в MySQL. Необходимо указать ключевые части префикса столбца, если вы создали индексы для столбцов BLOB и TEXT.
В случае индексов FULLTEXT, которые поддерживаются только для таблиц InnoDB и MyISAM, они могут включать только столбцы CHAR, VARCHAR и TEXT. Он не поддерживает индексацию префикса столбца, и если указана любая длина префикса, она игнорируется.
Обязательно к прочтению: идеи проекта SQL
Изменение таблиц для добавления или удаления индекса
Для добавления индексов в таблицу можно использовать четыре типа операторов:
- Чтобы добавить первичный ключ:
ALTER TABLE имя_таблицы ДОБАВИТЬ ПЕРВИЧНЫЙ КЛЮЧ (список_столбцов);
Индексированные значения здесь должны быть уникальными и не могут быть NULL.
- Для индекса с уникальными значениями:
ALTER TABLE имя_таблицы ДОБАВИТЬ УНИКАЛЬНОЕ имя_индекса (список_столбцов);
Значения должны быть уникальными, за исключением NULL, который может встречаться несколько раз.
- Чтобы добавить обычный индекс:
ALTER TABLE имя_таблицы ДОБАВИТЬ ИНДЕКС имя_индекса (список_столбцов);
Здесь значения могут появляться более одного раза.
- Чтобы создать специальный FULLTEXT индекс:
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);
Индекс FULLTEXT можно использовать для поиска текста.
В существующих таблицах
Чтобы добавить индекс в любую существующую таблицу, можно использовать следующий синтаксис:
ALTER TABLE имя_таблицы ДОБАВИТЬ ИНДЕКС (имя_индекса);
Для удаления индекса в таблице используется оператор DROP INDEX:
ALTER TABLE имя_таблицы DROP INDEX (имя_индекса);
Показать индексную информацию
Чтобы вывести список всех индексов, связанных с какой-либо таблицей, используется команда SHOW INDEX.
ПОКАЗАТЬ ИНДЕКС ИЗ имя_таблицы\G
'\G' используется для создания списка в вертикальном формате, что позволяет избежать переноса длинных строк.
Читайте также: Вопросы и ответы на собеседовании по SQL
Нисходящие индексы
InnoDB может хранить записи в порядке убывания, когда индекс является нисходящим индексом. Итак, когда в запросе запрашивается убывающий порядок, оптимизатор выбирает этот индекс. Это более эффективно для запросов с предложениями ORDER BY. Они поддерживаются только механизмом хранения InnoDB.
Синтаксис для создания или добавления нисходящих индексов аналогичен синтаксису alter или create, использованному выше.
ALTER TABLE table_name ADD index_name (col1_name desc, col2_name asc);
CREATE INDEX index_name ON table_name (col1_name desc, col2_name asc);
Невидимые индексы
Чтобы пометить индексы как недоступные для оптимизатора запросов, можно использовать невидимые индексы. MySQL обновляет невидимые индексы при изменении данных в столбцах, связанных с индексом. Индексы видны по умолчанию, и чтобы сделать их невидимыми, вы должны явно объявить видимость во время создания или с помощью команды ALTER TABLE.
Для создания невидимого индекса используется следующий синтаксис:
CREATE INDEX index_name ON table_name (c1, c2…) INVISIBLE;
Для изменения видимости существующих индексов используется следующий синтаксис:
ALTER TABLE имя_таблицы ALTER INDEX имя_индекса [VISIBLE | НЕВИДИМЫЙ];
Запишитесь на онлайн-курсы по разработке программного обеспечения в лучших университетах мира. Участвуйте в программах Executive PG, Advanced Certificate Programs или Master Programs, чтобы ускорить свою карьеру.
Заключение
Производительность поисковых запросов MySQL может быть улучшена с помощью индексов. Их можно определить при создании таблицы или добавить позже, если таблица уже создана.
В этой статье было дано краткое описание того, что такое индекс и для чего он используется. В следующем разделе объясняется, как создать индекс в MySQL, как его можно добавить в существующую таблицу и как создать уникальный индекс.
Различные команды для изменения таблиц и создания или удаления индекса также были описаны вместе с отображением информации об индексе. Также были кратко объяснены нисходящие и невидимые индексы.
В целом, изучение и практическая работа со всеми базами данных, упомянутыми в этой статье, добавит вам опыта. Ознакомьтесь с обзором курса, обучением и возможностями трудоустройства, связанными с ним, — такие платформы, как upGrad, предлагают мощные курсы, разработанные авторитетными институтами, такими как IIIT-B, в программе Full Stack Development для руководителей высшего звена.
