Як побачити, який індекс використовує запит?

Виконайте EXPLAIN перед оператором SELECT. Ключовий стовпець показує, який індекс вибрав оптимізатор, тоді як тип та рядки показують, чи є шлях доступу ефективним.

MySQL Індекс: Посібник зі створення, додавання та видалення

⚡ Розумний підсумок

MySQL У посібнику з індексування розглядається, як індекси сортують та швидко знаходять дані. Індекс – це відсортована структура пошуку, створена для одного або кількох стовпців; CREATE INDEX додає його, SHOW INDEXES перевіряє його, а DROP INDEX видаляє його, коли таблиці з великим обсягом запису переважають переваги читання.

📚 Обробляйте індекси як словник: Вони сортують значення стовпців, щоб пошуковий механізм міг знаходити рядки, не скануючи всю таблицю.
🛠️ Створіть за столом або після: Визначте індекс у CREATE TABLE або додайте його пізніше за допомогою CREATE INDEX у активній таблиці.
🔍 Перевірте за допомогою SHOW INDEXES: Використовуйте SHOW INDEXES FROM table_name, щоб перерахувати кожен індекс, ключову частину, потужність та прапорець унікальності.
🧹 Видалити, коли вартість запису занадто висока: Індекси уповільнюють INSERT та UPDATE — видаляйте невикористані за допомогою DROP INDEX, щоб відновити пропускну здатність запису.
🤖 Використовуйте штучний інтелект для розробки індексу: Помічники ШІ читають журнали повільних запитів, пропонують порядок стовпців для складених індексів та пояснюють плани EXPLAIN рядок за рядком.

Детальніше

Що таке? MySQL Індекс?

An індекс in MySQL — це структура даних, яка зберігає значення стовпців упорядковано, щоб пошуковий механізм міг швидко шукати рядки. Індекси створюються для стовпця або стовпців, які найчастіше використовуються для фільтрації даних. Уявіть собі індекс як список, відсортований в алфавітному порядку: набагато швидше знайти ім'я в відсортованому списку, ніж у несортованій купі.

Індекси мають певний компроміс — кожна операція INSERT або UPDATE має підтримувати індекс, тому додавання занадто великої кількості індексів до таблиці з великим обсягом запису може негативно вплинути на загальну продуктивність. Як правило, індексуйте стовпці, які з'являються в реченнях WHERE, JOIN та ORDER BY у таблицях, які зчитуються частіше, ніж записуються.

Навіщо використовувати індекс?

Ніхто не любить повільні системи. Висока продуктивність є головним пріоритетом майже для кожної програми, що базується на базах даних. Компанії витрачають значні кошти на обладнання для забезпечення швидкості запитів, але існує межа того, що може забезпечити саме лише обладнання. Оптимізація індексів — це дешевший та ефективніший важіль.

Повільний час відгуку зазвичай пов'язаний із тим, що рядки зберігаються на диску у фізичному порядку. Без індексу, MySQL повинен просканувати кожен рядок, щоб знайти ті, що відповідають предикату — «повне сканування таблиці». Індекси дозволяють MySQL перейти безпосередньо до відповідних рядків, що перетворює план запиту з O(n) приблизно на O(log n) для пошуку в B-дереві.

Синтаксис: створити індекс

Індекс можна визначити у двох місцях:

Під час створення таблиці.
Після того, як таблиця вже існує.

Приклад: Створення індексу всередині за допомогою CREATE TABLE

Для myflixdb У базі даних ми очікуємо багато пошуків у стовпці повного імені. Наведений нижче скрипт створює новий members_indexed таблиця з індексом на full_names .

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;

Виконайте скрипт у MySQL Верстак проти myflixdb , що постійно розширюється.

оновлення myflixdb побачити нове members_indexed таблиці. Файл full_names тепер колонка відображається під Індекси вузол.

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

Додати індекс після того, як таблиця вже існує

Ви часто виявляєте, що існуючій таблиці потрібен індекс — пошукові запити виконуються повільно, а план EXPLAIN показує повне сканування таблиці для стовпця, який відображається в WHERE. CREATE INDEX Оператор додає індекс без повторного створення таблиці.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Конкретний приклад — пришвидшення пошуку на title стовпець movies стіл:

CREATE INDEX `title_index` ON `movies` (`title`);

Кожен запит, який фільтрується за movies.title тепер підтримується новим індексом. Запити, які фільтрують інші стовпці, все одно сканують таблицю, якщо вони не мають власного індексу.

Примітка: Ви можете створити складений індекс для кількох стовпців, коли ваші запити завжди фільтрують або сортують за однією й тією ж комбінацією. Порядок має значення — провідний стовпець визначає, чи можна використовувати індекс.

Список індексів у таблиці

Скористайтеся кнопкою SHOW INDEXES щоб побачити кожен індекс, визначений у таблиці.

SHOW INDEXES FROM `table_name`;

Приклад — список індексів на movies стіл:

SHOW INDEXES FROM `movies`;

Виконайте оператор у MySQL Верстак проти myflixdb щоб побачити існуючі індекси та стовпці, які вони охоплюють.

Примітка: Первинні та зовнішні ключі індексуються автоматично MySQLКожен індекс має унікальну назву та містить список стовпців, які він охоплює.

Синтаксис: Видалити індекс

Скористайтеся кнопкою DROP INDEX видалити існуючий індекс з таблиці. Це корисно, коли таблиця з інтенсивним записом уповільнюється індексом, який більше не заробляє на собі гідність на стороні читання.

DROP INDEX `index_id` ON `table_name`;

Конкретний приклад — відкиньте full_names індекс з members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Види MySQL Індекси

MySQL підтримує кілька типів індексів, кожен з яких підходить для різного робочого навантаження.

тип	Мета
PRIMARY KEY	Унікальний ідентифікатор рядка; кластеризований з даними таблиці в InnoDB.
UNIQUE	Забезпечує унікальність, водночас виконуючи роль індексу.
ІНДЕКС (B-дерево)	Вторинний індекс за замовчуванням, який використовується для запитів діапазону та пошуку рівності.
ПОВНИЙ ТЕКСТ	Оптимізовано для пошуку тексту природною мовою за допомогою MATCH … AGAINST.
ПРОСТОРОВИЙ	Індекс R-дерева для типів даних ГІС, таких як ТОЧКА та ПОЛІГОН.
ХАШ	Пошук рівності за постійний час; використовується механізмом зберігання MEMORY.
Композитний (багатоколонковий)	Об'єднує кілька стовпців в один індекс; дотримується правила префікса крайнього лівого рядка.

Найкращі практики для MySQL Індекси

Наведені нижче звички забезпечують корисність індексів та запобігають їх перетворенню на мертвий вантаж.

Індекс шаблону запиту, а не назви стовпця: додайте індекси, що відповідають реальним реченням WHERE, JOIN та ORDER BY, а не «кожному стовпцю, який здається важливим».
Порядок спостереження за складеним індексом: Для використання індексу в запиті має бути присутній початковий стовпець.
Уникайте дублікатів індексів: Провідний префікс складеного індексу вже охоплює пошук в одному стовпці за цим префіксом.
Перевірте за допомогою ПОЯСНЕННЯ: підтвердити, що планувальник фактично вибирає новий індекс.
Видалити невикористані індекси: використання sys.schema_unused_indexes in MySQL 5.7+ для пошуку індексів, які нічого не зчитує.
Типи даних відповідності: Якщо речення WHERE порівнює стовпець VARCHAR з числом, індекс не може бути використаний через неявне приведення типів.

Поширені запитання

Первинний ключ однозначно ідентифікує кожен рядок і завжди індексується. Загальний ІНДЕКС пришвидшує пошук, але дозволяє дублікати значень. Кожен первинний ключ є індексом, але не кожен індекс є первинним ключем.

Уникайте індексів на дуже маленьких таблицях, на стовпцях з дуже невеликою кількістю різних значень (низька кардинальність) та на таблицях, в які записуються дані набагато частіше, ніж зчитуються. Кожен додатковий індекс уповільнює кожну операцію INSERT, UPDATE та DELETE.

Складений (багатостовпцевий) індекс охоплює більше одного стовпця в одному індексі. Він дотримується правила префікса крайнього лівого стовпця, тому може обробляти запити, які фільтрують перший стовпець, перші два стовпці тощо, але не лише другий стовпець.

прогін EXPLAIN перед оператором SELECT. ключ у стовпці показано, який індекс вибрав оптимізатор, тоді як тип та рядків підкаже вам, чи є шлях доступу ефективним.

Покривний індекс містить кожен стовпець, необхідний для запиту, тому механізм відповідає на запит лише з індексу, не читаючи таблицю. EXPLAIN повідомляє про помилку «Використання індексу», коли це трапляється.

Поширені причини включають обгортанняping стовпець у функції (WHERE YEAR(col) = …), неявні перетворення типів, дуже низька кардинальність та застаріла статистика. Запустити ANALYZE TABLE оновити статистику та перевірити EXPLAIN з справжньої причини.

Помічники ШІ отримують журнали повільних запитів, класифікують найдорожчі шаблони, пропонують одностовпцеві або складені індекси та пояснюють плани EXPLAIN простою мовою. Вони скорочують час налаштування з годин до хвилин для рутинних робочих навантажень.

Так. Інструменти штучного інтелекту перетворюють запит на кшталт «пришвидшити пошук клієнтів за електронною поштою та датою реєстрації» на робочий оператор CREATE INDEX, рекомендують порядок стовпців та пояснюють очікуваний вплив на пропускну здатність читання та запису.