Індекси в MySQL — чому вони потрібні та як з ними працювати

💡 Усі статті, обговорення, новини про DevOps — в одному місці. Приєднуйтесь до DevOps спільноти!

Всім привіт! Мене звати Кирило, я — PHP розробник в ІТ-компанії Quarks, що спеціалізується на створенні продуктів і технологій у сфері Social Discovery та дейтингу. Мені 25 років, з яких 6 у професії . Нещодавно у мене з’явилося бажання поділитися своїм досвідом при роботі з індексами у MySQL.

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

Ну що? Поїхали... 🚗

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

Для кращого розуміння розглянемо SQL-запит:

select * from cities where city_id = 10

Цей запит шукає запис в таблиці cities, де city_id дорівнює 10. Тепер уявімо, що дані для цієї таблиці MySQL зберігає в папці на диску під назвою cities. Усередині цієї папки перебувають файли city_1.txt, city_2.txt ... city_10.txt та інші, де кожен файл містить інформацію про одне місто.

Якщо в нас невелика кількість міст в таблиці, пошук ми виконаємо досить швидко. Але візьмемо ситуацію, коли в нас тисячі міст. Щоб відшукати необхідний запис, MySQL потрібно перевірити кожен файл у цій папці, поки він не знайде city_10.txt. Це може відбуватися дуже повільно.

На допомогу приходять індекси. Індекс для поля city_id можна уявити як асоціативний масив (або JSON-об’єкт), де ключем є city_id, а значенням — назва файлу, що зберігає це місто. Так, використовуючи індекс, MySQL може одразу звертатися до потрібного файлу, обійшовши всі інші.

А тепер настав час пограти в гру «Знайди десять відмінностей»:

{
  "1": "city_1.txt",
  "2": "city_2.txt",
  "3": "city_3.txt",
  "4": "city_4.txt",
  "5": "city_5.txt",
  "6": "city_6.txt",
  "7": "city_7.txt",
  "8": "city_8.txt",
  "9": "city_9.txt",
  "10": "city_10.txt",
  "11": "city_11.txt",
  "12": "city_12.txt",
  "13": "city_13.txt",
  "14": "city_14.txt",
  "15": "city_15.txt",
  "16": "city_16.txt",
  "17": "city_17.txt",
  "18": "city_18.txt",
  "19": "city_19.txt",
  "20": "city_20.txt"
}

Я вам підкажу: відмінностей майже немає! Фактично індекси — це як зміст у книзі. У реальному житті ми використовуємо зміст для того, щоб швидко знайти, на якій сторінці розташований потрібний нам розділ. Так само MySQL використовує індекси (у нашому прикладі), щоб швидше відшукати шлях до файлу, в якому зберігаються дані.

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

Хоча наш приклад і допомагає зрозуміти концепцію, слід пам’ятати, що реальна реалізація індексів у MySQL значно складніша та ефективніша цього спрощеного відображення. Але не переживайте! Детальний розбір реалізації індексів в MySQL ми розглянемо в наступному розділі.

Види індексів

У MySQL існує чотири види індексів:

• B-tree — один з найпопулярніших видів індексів, потрібний для пошуку за діапазоном або збігом.
• R-tree — потрібен для пошуку за координатами.
• Hash — не підтримує пошук за діапазоном. Цей індекс можна використати в таблицях типу Memory. Особливість такого типу полягає в тому, що дані зберігаються не на диску, а в оперативній памʼяті.
• FullText — цей тип індексів розв’язують проблеми повнотекстового пошуку. Простіше кажучи, це пошук за фразами, словам, входженнями частини слова і т. д.

Далі йтиметься саме про роботу B-tree-індексів. Цей вид індексів є одним з найпопулярніших у MySQL, і він часто використовується для оптимізації запитів до таблиць.

Однією з основних особливостей B-tree-індексів є те, що вони зберігаються в окремих файлах на диску. У цих файлах уже відразу міститься структура даних у вигляді бінарного дерева. Тобто СУБД може швидко добратися до потрібних даних без необхідності повного сканування таблиці. Такий підхід до зберігання індексів значно прискорює операції пошуку та вибірки.

Ця структура даних організована так, що для знаходження потрібного запису використовується бінарний пошук, завдяки чому пошук в B-tree-індексах відбувається дуже швидко.

Основна особливість полягає в структурі. Кожен вузол дерева має:

• Ключ. Це значення, за яким відбувається пошук.
• Вказівники на підвузли. Вони допомагають переходити до інших частин дерева.

• Посилання на дані. Адреса файлу та рядку, в якому зберігається інформація.

Погляньмо на візуальне зображення бінарного дерева для індексу для колонки city_id з 20 містами:

Наприклад, нам треба знайти місто, у якого city_id=17. Ось ці кружечки з номерами називаються вузлами. Ми починаємо з головного вузла та йдемо вниз дерева, виконуючи операції порівняння:

1. 17 >= 10? Так, отже йдемо в правий вузол.
2. 17 >= 15? Так, отже йдемо в правий вузол від 15.
3. 17 >= 18? Ні, отже йдемо в лівий вузол від 18.
4. 17 >= 16? Так, отже йдемо на рівень нижче.
5. Тут ми бачимо вузол з city_id=17.

Як ви можете зрозуміти, цей алгоритм знайшов потрібне місто за пʼять операцій порівняння.

Класифікація індексів

Індекси бувають двох класифікацій: кластерні та некластерні.

Кожна таблиця в InnoDB має кластерний індекс:

• Якщо в таблиці задано primary key — це він.
• Якщо первинний ключ відсутній, але є унікальний індекс — перший з них.
• У випадку відсутності обох InnoDB автоматично створює приховане поле з сурогатним ID розміром 6 байтів.

Різниця між кластерним і некластерним індексом полягає у способі зберігання посилань: перший прямо вказує на місце зберігання даних на диску, тоді як другий зберігає посилання на кластерний індекс.

Розгляньмо приклад на основі таблиці cities, де city_id виступає первинним ключем. Але ж жодне місто не може існувати без країни, тому додамо до таблиці колонку country_id з відповідним індексом:

create table cities (
    city_id int auto_increment,
    country_id int,
    name varchar(255) not null,
    latitude decimal(9,6),
    longitude decimal(9,6),
    primary key (city_id),
    index(country_id)
)

У таблиці cities city_id слугує як кластерний індекс, а індекс для country_id є некластерним. Зрештою некластерний індекс country_id вказує на city_id, який свідчить, де саме на диску зберігаються відповідні дані. Це можна зобразити так:

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

Отже, під час запиту:

select * from cities where country_id = 1

MySQL використовує алгоритм:

1. Звертається до індексу country_id, щоб знайти вузол з ключем «1».
2. Отримавши масив ідентифікаторів міст, переходить до індексу city_id, визначаючи розташування фізичного зберігання даних для кожного міста.
3. За допомогою визначених розташувань, MySQL швидко завантажує потрібні дані з диска.

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

Композитні (багатоколонні) індекси

Настав час розібратися, що це за істота така — композитні індекси. Скоріш за все, ви вже за назвою зрозуміли, що це індекси на декілька колонок одночасно. Забудьмо нашу таблицю міст і спробуємо розібратися на іншому прикладі. Уявімо, що ви є власником онлайн-магазину. І в ньому, логічно, є замовлення. Тобто нам треба таблиця, яка буде зберігати інформацію про замовлення:

create table orders (
    order_id int auto_increment primary key,
    customer_id int not null,
    product_id int not null,
    order_date date not null,
    total_price decimal(10, 2) not null,
    shipping_address text not null,
    order_status tinyint not null,
    tracking_number varchar(100),
    foreign key (customer_id) references customers(customer_id),
    foreign key (product_id) references products(product_id)
)

Наприклад, ми хочемо додати індекс на колонки customer_id та order_status.

Ми це можемо зробити, використавши такий SQL-запит:

alter table orders add index customer_status_idx (customer_id, order_status)

Поки виглядає нескладно, згодні зі мною? 🙂

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

Це можна уявити як бінарне дерево:

Але перш ніж, як переходити до деталей, підкреслимо один критичний момент: порядок колонок під час створення складного індексу має велике значення. У MySQL є таке поняття як селективність. А якщо говорити простою мовою, то це унікальність колонки. На першому місці в багатоколонному індексі треба вказувати найбільш унікальне поле.

Розгляньмо таблицю замовлень вашого онлайн-магазину. Як правило, один користувач робить обмежену кількість замовлень. А середня кількість замовлень з певним статусом є набагато вищою, ніж середня кількість замовлень одного користувача. Отже, оптимально створити індекс (customer_id, order_status).

Для визначення унікальності поля в таблиці можна скористатися таким SQL-запитом:

select avg(count_records)
from (
  select count(*) as count_records
  from [назва таблиці]
  group by [назва колонки]
) as subquery

Цей запит групує дані за вказаною колонкою, рахуючи кількість рядків для кожного її значення. Зрештою ми отримаємо середню кількість рядків на кожне унікальне значення в цій колонці, що допоможе нам оцінити унікальність поля.

Покриваючі індекси

Часто, коли ми говоримо про індекси, ми розуміємо їх як спосіб прискорення пошуку даних в таблиці. Але індекси можуть не лише прискорювати пошук, але й зменшувати необхідність звертання до основних даних таблиці.

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

Повернімося до таблиці orders з онлайн-магазину. На сторінці користувача ми хочемо відобразити замовлення за певним індифікатором клієнта:

select product_id, order_status from orders where customer_id=142

Для оптимізації цього запиту додамо індекс:

alter table orders add index order_status_date_idx (customer_id, product_id, order_status)

Під час створення індексу були вказані всі поля, які використовуються в запиті. А це означає, що під час виконання цього запиту в MySQL немає потреби отримувати значення product_id та order_status з бази, оскільки вся інформація вже зберігається в індексі.

Розглянемо ще декілька цікавих кейсів:

1. Виконаємо такий запит

select product_id from orders where customer_id=142

Як ви думаєте, чи спрацює у цьому випадку покриваючий індекс? Так, він спрацює! Це називається частковим індексом. Ви можете уникати вказівки деяких полів індексу в запиті, якщо вони вам не потрібні.

Але, але! Увесь час є якесь «але». Якщо в цьому випадку замість product_id буде потреба, щоб запит повернув значення стовпчика order_status:

select order_status from orders where customer_id=142

То індекс спрацює тільки для customer_id, а order_status буде отримано з бази.

2. Розглянемо ось такий запит

select order_id, product_id, order_status from orders where customer_id=142

У нашому випадку order_id є первинним ключем, а тому слугує кластерним індексом. Індекс order_status_date_idx є некластерним і відображає посилання на кластерний індекс. Отже, він зберігає всю необхідну інформацію для цього запиту, роблячи його оптимальним для виконання.

Для чого потрібна команда explain

Explain — це команда, яка використовується для отримання плану виконання запиту. Зокрема, вона може показувати, які індекси та який тип з’єднання будуть використані, дає оцінку кількості рядків, які буде оброблено та інше. Синтаксис цієї команди простий: вам лише потрібно додати слово explain на початку SQL-запиту.

Уявімо, що ви — власник мережі барбершопів. Для відстежування відвідувань у вас у системі існує таблиця visits:

create table visits (
    visit_id int auto_increment primary key,
    service_id int not null,
    date_book datetime not null,
    user_id int not null,
    location_id int not null,
    status_id int not null,
    notes text,
    foreign key (service_id) references services(service_id),
    foreign key (user_id) references users(user_id),
    foreign key (location_id) references locations(location_id)
) engine=innodb;

Припустимо, ви хочете дізнатись, яка з локацій має найбільший попит. Для цього виконаємо наступний SQL-запит і на початку додамо explain:

explain select location_id, count(*) as count from visits group by location_id order by count desc limit 1

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

Настав час подивитися, що буде результатом цього запиту:

Маємо ось таку цікаву табличку з тим, що буде відбуватися під час виконання цього запиту. Значення деяких стовпчиків, я думаю, вам й так зрозумілі (наприклад, id та table).

Тому розглянемо тільки основні, які в майбутньому вам знадобляться для розуміння ефективності виконання запитів:

Explain format = JSON

Для тих, хто віддає перевагу роботі з даними у форматі JSON, команда розробників MySQL створила цікавий інструмент. Замість традиційного табличного відображення плану виконання запиту, ви можете отримати цю інформацію як JSON-об’єкт. Щоб зробити це, вам треба на початку запиту вказати explain format=json.

Додамо спочатку новий індекс до таблиці visits:

alter table visits add index user_status_idx (user_id, status_id)

А тепер розглянемо план виконання запиту у JSON-форматі:

explain format=json select * from visits where user_id=133

Результатом цього запиту буде ось такий JSON-обʼєкт:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "table": {
      "table_name": "visits",
      "access_type": "ref",
      "possible_keys": [
        "user_status_idx"
      ],
      "key": "user_status_idx",
      "used_key_parts": [
        "user_id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 20,
      "rows_produced_per_join": 20,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "5.00",
        "eval_cost": "2.00",
        "prefix_cost": "7.00",
        "data_read_per_join": "800"
      },
      "used_columns": [
        "visit_id",
        "service_id",
        "date_book",
        "user_id",
        "location_id",
        "status_id",
        "notes"
      ]
    }
  }
}

Тут є багато додаткової інформації, якої не було у дефолтному (табличному) варіанті роботи цієї команди. Я послуговуюсь цим форматом скоріше для того, щоб зрозуміти, повністю або частково був використаний індекс. Щоб зрозуміти це, я дивлюся на значення key та used_key_parts.

Як ми можемо побачити, індекс user_status_idx використаний частково.

Тепер виконаємо запит, де в нас буде вказаний користувач та статуси відвідувань:

explain format=json select * from visits where user_id=133 and status_id in (3,4,5)

Отримаємо ось такий результат:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.36"
    },
    "table": {
      "table_name": "visits",
      "access_type": "range",
      "possible_keys": [
        "user_status_idx"
      ],
      "key": "user_status_idx",
      "used_key_parts": [
        "user_id",
        "status_id"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 8,
      "filtered": "100.00",
      "index_condition": "((`edusson`.`visits`.`user_id` = 133) and (`edusson`.`visits`.`status_id` in (3,4,5)))",
      "cost_info": {
        "read_cost": "3.56",
        "eval_cost": "0.80",
        "prefix_cost": "4.36",
        "data_read_per_join": "320"
      },
      "used_columns": [
        "visit_id",
        "service_id",
        "date_book",
        "user_id",
        "location_id",
        "status_id",
        "notes"
      ]
    }
  }
}

Бачимо, що індекс був використаний повністю — із чим я вас і вітаю! 🎉

Інструмент Explain analyze

Якщо ви маєте щастя користуватися версією MySQL 8.0.18 або новішою, вам відкритий доступ до цього чудового формату команди explain.
Explain analyze — це інструмент у MySQL, який допомагає зрозуміти, як саме база даних виконує конкретний SQL-запит. Він не просто «гадає» або робить припущення, як це працює у стандартному варіанті роботи команди explain, а справді виконує запит, збираючи інформацію про кожну окрему операцію.

У випадку, якщо буде виконано запит на зміну даних (update, delete, insert), MySQL поверне вам повідомлення про те, що запит не був виконаний.

Виконаємо такий запит:

explain analyze

select

  locations.name, 

  count(*) as count_visits

from

  visits

  inner join locations

    using(location_id)

group by

  location_id

Памʼятаємо, зараз у таблиці visits десь 200 тис. записів. Вони ± рівномірно розподілені між усіма локаціями. Загальна кількість локацій дорівнює 50.

Результат цього запиту:

Table scan on <temporary> (actual time=0.011..0.057 rows=50 loops=1)
  -> Aggregate using temporary table (actual time=1022.395..1022.459 rows=50 loops=1)
      -> Nested loop inner join  (cost=19921.16 rows=198550) (actual time=1.436..346.375 rows=200000 loops=1)
          -> Table scan on locations (cost=5.25 rows=50) (actual time=0.715..0.884 rows=50 loops=1)
          -> Index lookup on visits using location_id (location_id=locations.location_id) (cost=9.16 rows=3971) (actual time=0.106..5.262 rows=4000 loops=50)

Вау... Тут прям якась магія відбувається 🪄

Для початку розберемось, щось означає кожен з параметрів у дужках:

Зрозумівши значення параметрів, наступним кроком детально розглянемо порядок операцій, в якому виконувався цей запит:

Отже, аналізуючи часову статистику, можемо визначити:

• Наш запит виконався протягом 1,369 секунд (або 1368,891 мс), враховуючи суму часу операцій 346,375 мс + 1022,459 мс + 0.057 мс.
• Aggregate using temporary table є найповільнішою операцією, яка виконується 1.02 с. Ця операція відповідає за групування результату за location_id.
• Table scan on <temporary> є найшвидшою операцією, під час якої MySQL зчитує дані з тимчасової таблиці, де зберігається результат запиту.

Побудова запитів у MySQL з використанням індексів

У цьому розділі поговоримо про особливості побудові запитів з використанням індексів. Розглянемо їх на дуже просто прикладі:

create table tbl (
    a int,
    b int,
    c int,
    d varchar(120),
    index abc_idx(a,b,c)
);

Таблиця tbl, в якій маємо лише один індекс за стовпчиками a, b, с.

Запити на пошук

Приклади хороших запитів where:

• a = {n}
• a = {n} and b = {n}
• a = {n} and b = {n} and c > {n}
• a = {n} and b in ({1,2...n}) and c > {n}
• a > {n} — інколи індекс abc_idx може бути не використаний для такої умови, якщо {n} буде дуже маленьким числом. Замість цього MySQL може вирішити, що йому буде вигідніше просканувати всю таблицю.

Приклади поганих запитів where:

• b = {n} — індекс abc_idx починається з колонки a, тому оптимізатор не використовує його для фільтрації лише за колонкою b;
• a > {n} and b = {n} — індекс abc_idx може бути частково використаний для колонки a, але якщо діапазон для a досить широкий, оптимізатор може вирішити, що повне сканування таблиці буде швидше;
• a = {n} and b >= {n} and c = {n} — індекс abc_idx буде використаний для a, але можливі проблеми з використанням для b, якщо для b використовується діапазон. Якщо оптимізатор вважає, що діапазон b занадто великий, він може пропустити індекс для наступних умов;
• a = {n} and c = {n} — індекс abc_idx може бути використаний для a, але оптимізатор може пропустити індекс для c, оскільки він іде після b в індексі, і b не вказано в умові.

Запити на сортування та групування

Використаємо також таблицю tbl, у якій є індекс abc_idx (a, b, c).

Приклади хороших запитів select * from tbl:

• where a>={n} order by a limit {n}
• order by a desc, b desc limit {n}
• group by a, b, c limit {n}
• where a = {n} and b = {n} group by a
• where a in ({1,2…n}) group by a

Приклади поганих запитів select * from tbl:

• where a = {n} order by b — буде використано частковий індекс за стовпичком a, тому що після відбору записів за умовою a = {n} записи можуть бути вже впорядковані за b (завдяки порядку в індексі). Однак MySQL може вирішити не використовувати індекс для сортування, якщо він вважає, що перебіг за таблицею буде швидшим. Це може статися, якщо умова a = {n} відбирає велику частину таблиці;
• order by a asc, b desc — з різними напрямками сортування для a та b, індекс не може бути використаний оптимально, тому він ігнорується;
• group by b — Оскільки стовпчик b є другою колонкою в індексі та не вказано умов для стовпчика a, індекс не може бути використаний для групування;
• where a = {n} group by b, c — буде також використано частковий індекс за а, але індекс може бути використаний для групування за b, c після відбору записів за a = {n}. Проте, якщо умова a = {n} відбирає значну частину таблиці, оптимізатор може вважати, що повний перебіг таблиці без використання індексу буде швидшим.

Завжди важливо пам’ятати, що оптимізатор запитів MySQL робить свої висновки на основі статистики про дані та індекси, й іноді його вибір може бути не зрозумілим інтуїтивно. Тому завжди корисно перевіряти реальну поведінку за допомогою команди explain.

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

Можливо в деяких з вас могло б скластися враження, що B-tree індекси можна додавати тільки для числових полів. Але їх також можна додавати й для строкових полів.
Додамо індекс на наше строкове поле d в таблиці tbl:

create index d_idx on tbl(d)

У цьому разі індекс спрацює тільки під час пошуку за префіксом d like ’test%, але не допоможе з пошуком по суфіксу d like ’%test’.

Врахування усіх запитів

Якщо ми хочемо прискорити роботу цих запитів за рахунок використання індексів:

select * from tbl where a = {n} and b = {n};

select * from tbl where a > {n} and b = {n};

То найкраще додати індекс за стовпчиками (b, a). Оскільки в другому запиті є пошук за діапазоном стовпчика a (а це означає, що якби був доданий індекс за стовпчиками (a,b)), то для цього запиту індекс був би використаний частково, лише за колонкою a.

Цікаві можливості під час роботи з індексами

У цьому розділі поговоримо про деякі маніпуляції з індексами в таблиці, що вже існують.

alter index ... invisible/visible

У MySQL є можливість зробити індекс невидимим для запитів, які виконуються. Наприклад, ви не можете вирішити, який з двох індексів краще додати і який з них буде найбільше пришвидшувати запит. Ви додали обидва індекси й хочете, щоб MySQL тимчасово знав про існування тільки одного з них. Тоді ви можете зробити запит, після якого MySQL буде ігнорувати ваш індекс. Для цього треба виконати такий запит:

alter table {назва таблиці} alter index {назва індексу} invisible

А для того, щоб індекс знову був видимим, треба виконати зворотню команду:

alter table {назва таблиці} alter index {назва індексу} visible

force index

У MySQL є можливість «насильно» використовувати індекс. Але попереджаю, що переважно MySQL ігнорує ваш індекс не просто так: у 99% випадках з ним запит працюватиме повільніше. Але якщо вам все ж таки цікаво перевірити, наскільки повільніше працюватиме запит з вашим індексом, то синтаксис такої операції буде виглядати так:

select ... from {назва таблиці} force index({назва індексу}) where ...

use index

Ці «підказки» акцентують на тому, як оптимізатор повинен використовувати (або не використовувати) певні індекси під час виконання запиту.

Конструкція use index({перелік індексів}) говорить оптимізатору, що слід розглядати лише індекси, вказані в дужках. Якщо жоден з індексів не може бути використаний для виконання запиту, оптимізатор може вибрати повний перебіг таблиці. Приклад використання такої підказки в запиті:

select ... from {назва таблиці} use index({перелік індексів}) where ...

ignore index

Як ви вже могли здогадатися, конструкція ignore index({перелік індексів}) працює у зворотному напрямку. Ця підказка забороняє оптимізатору використовувати зазначені індекси для таблиці. Оптимізатор буде розглядати всі інші доступні індекси для таблиці, але не буде використовувати ті, які були зазначені в дужках. Якщо інші доступні індекси не є оптимальними для запиту, оптимізатор може вибрати повний перебіг таблиці. Також приклад використання цієї конструкції:

select ... from {назва таблиці} ignore index({перелік індексів}) where ...

Попередження! Під час використання конструкцій force index, use index та ignore index слід бути особливо обережним. Ці «підказки» для оптимізатора можуть змусити MySQL використовувати або ігнорувати певні індекси, що, своєю чергою, суттєво впливає на продуктивність запиту. Тому рекомендується користуватися ними дуже уважно та перевіряти вплив кожного зміненого запиту.

Недоліки індексів

Як ми вже зрозуміли, Індекси дуже крутий інструмент для прискорення запитів, але є і деякі негативні моменти у їх використанні.

По-перше, вони займають додатковий простір на диску. Індекси, створені для покращення продуктивності запитів, додатково використовують простір на диску. Чим більше індексів у вашій базі даних, тим більше місця вони займають. У разі великих таблиць може виникнути значне збільшення обсягу використаного дискового простору. Це особливо важливо враховувати, коли ресурси зберігання обмежені.

По-друге, вони впливають на час виконання запитів на зміну даних. Хоча індекси прискорюють читання даних, вони можуть уповільнювати операції запису. Кожен раз, коли ви додаєте, оновлюєте або видаляєте запис у таблиці, відповідні індекси також потребують оновлення.

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

Тести швидкості

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

Тести швидкості роботи запитів будемо проводити на таблиці product_reviews. Як ви вже могли здогадатися, ця таблиця зберігає інформацію про відгуки користувачів, їхні оцінки за продукти нашого інтернет-магазину.

Структура цієї таблиці виглядає так:

create table product_reviews

(

    review_id int auto_increment primary key,

    product_id int not null,

    user_id int not null,

    rating int not null,

    review_text text,

    review_date datetime not null,

    foreign key (product_id) references products(product_id),

    foreign key (user_id) references users(user_id)

)

Я виконав PHP-скрипт, який додав 500 тис. тестових записів у цю таблицю. Для кожного відгуку рандомно обирався user_id (у таблиці users було 10 тис. записів) та

product_id (у таблиці products було 10 тис. записів). Для зручності час виконання запитів відображатиметься у секундах.

Агрегатні функції

select

  avg(rating)

from

  product_reviews

where

  product_id = 205

select 

  product_id,

  count(review_id) as reviews_count

from

  product_reviews

group by

  product_id

order by

  reviews_count desc

limit

  1

Покриваючі індекси

select

  user_id,

  rating 

from

  product_reviews

where

  product_id = 503

select

  * 

from 

  product_reviews

where

  product_id = 491

order by 

  review_date desc

Запити на вибірку

select

  product_id

from

  product_reviews

group by

  product_id

having

  avg(rating) > 3.5

  and count(review_id) > 50

select

  *

from

  product_reviews

where

  rating in (1,2)

  and review_date

  between '2023-08-24 00:00:00' and '2023-09-24 23:59:59'

Запити на зміну даних

Перевіримо, наскільки уповільнюється запити на зміну даних. Зараз в таблиці є такі індекси:

• rating_date_idx (rating, review_date);
• product_review_date_idx (product_id, review_date);
• product_idx (product_id);
• product_rating_idx (product_id, rating);
• product_user_rating_idx (product_id, user_id, rating);
• primary (перинний ключ).

insert into 

  product_reviews 

  (product_id, user_id, rating, review_text, review_date)

value 

  (1012, 8731, 4, 'Текст відгука', '2023-09-19 10:00:00')

update

  product_reviews

set 

  review_text = 'Новий текст відгука'

where

    review_id=500002

delete from

  product_reviews

where

  review_id=500002

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

Висновки

Дякую кожному та кожній, хто дочитав до цього моменту! Це моя перша стаття на цю тему, і я буду вдячний за ваш конструктивний зворотний зв’язок.

Хочу наголосити, що індекси — це не панацея. Вони лише один з численних інструментів, які допомагають пришвидшити виконання запитів. Існують й інші способи оптимізації, наприклад:

• кешування запитів, якщо це можливо;
• обмеження кількості рядків, які повертає запит — але тут все залежить від бізнес-логіки вашого застосунку;
• розбиття запиту на декілька частин і їхнє паралельне виконання;
• використання інших баз даних, які є більш ефективні для запитів на вибірку.

Кожен підхід має свої переваги та недоліки. Головне — знати, який інструмент потрібно застосовувати в конкретних випадках.