MySQL Посібник з індексу – Створення, додавання та відкидання

Що таке індекс?

Індекси в 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 workbench проти “myflixdb”.

Оновлення myflixdb показує щойно створену таблицю під назвою members_indexed.

"Примітка" Members_indexed таблиця має “full_names” у вузлі indexes.

У міру розширення бази учасників і збільшення кількості записів пошукові запити в таблиці members_indexed, які використовують пропозиції WHERE і ORDER BY, будуть набагато швидшими порівняно з тими, що виконуються в таблиці members без визначеного індексу.

Додайте базовий синтаксис індексу

Наведений вище приклад створив індекс під час визначення таблиці бази даних. Припустимо, у нас уже визначена таблиця, і пошукові запити в ній виконуються дуже повільно. Вони надто довго повертають результати. Дослідивши проблему, ми виявили, що можемо значно покращити продуктивність системи, створивши INDEX для найбільш часто використовуваного стовпця в реченні 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 використовується для видалення визначеного індексу в даній таблиці.

Вам може сподобатися: