Що таке індекс?
Індекси в MySQL сортують дані впорядковано послідовно. Вони створюються в стовпці (колонках), які будуть використовуватися для фільтрування даних. Подумайте про індекс як про алфавітно відсортований список. Простіше шукати імена, відсортовані в алфавітному порядку, ніж ті, що не відсортовані.
Використання індексу в таблицях, які часто оновлюються, може призвести до низької продуктивності. Це тому, що MySQL створює новий блок індексу кожного разу, коли дані додаються або оновлюються в таблиці. Як правило, індекси слід використовувати в таблицях, дані яких не змінюються часто, але часто використовуються у вибраних пошукових запитах.
Що використовувати індекс?
Ніхто не любить повільних систем. Висока продуктивність системи має першочергове значення майже у всіх системах баз даних. Більшість підприємств інвестує значні кошти в апаратне забезпечення, щоб пошук та маніпуляції з даними могли бути швидшими. Але існує обмеження на інвестиції в обладнання, які може зробити бізнес. Оптимізація бази даних є більш дешевим та кращим рішенням.
Повільність часу відгуку, як правило, обумовлена тим, що записи зберігаються випадковим чином у таблицях бази даних. Пошукові запити повинні циклічно переглядати всі випадково збережені записи один за одним, щоб знайти потрібні дані. Це призводить до низької продуктивності баз даних, коли справа доходить до отримання даних з великих таблиць. Отже, використовуються індекси, які сортують дані, щоб полегшити пошук.
Синтаксис: Створення індексу
Індекси можна визначити двома способами
- На момент створення таблиці
- Після створення таблиці
Для нашого myflixdb ми очікуємо багато пошуків у базі даних із повним ім'ям.
Ми додамо стовпець "повні імена" до Індексу в новій таблиці "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 має "повні_імена" у вузлі індексів.
У міру розширення бази членів та збільшення кількості запитів пошукові запити в таблиці members_indexed, що використовують речення WHERE та ORDER BY, будуть набагато швидшими порівняно з тими, що виконуються в таблиці членів без індексу.
Додайте основний синтаксис індексу
У наведеному вище прикладі створено індекс під час визначення таблиці бази даних. Припустимо, у нас уже визначена таблиця, і пошукові запити в ній дуже повільні. Вони надто довго повертають результати. Дослідивши проблему, ми виявляємо, що ми можемо значно покращити продуктивність системи, створивши 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`;
Виконання наведеної команди скидає індекс з ідентифікатором `повні_імена` з таблиці members_indexed.
Резюме
- Індекси дуже потужні, коли йдеться про значне покращення продуктивності пошукових запитів MySQL.
- Індекси можна визначити під час створення таблиці або додати пізніше після того, як таблиця вже створена.
- Ви можете визначити індекси в декількох стовпцях таблиці.
- SHOW INDEX FROM table_name використовується для відображення визначених індексів у таблиці.
- Команда DROP використовується для видалення визначеного індексу в заданій таблиці.